What problem is this solution for?

What is the Solution?

We can use Webhook to push leads to Google Sheets via Zapier, and from Google Sheets we can push data to Google Analytics again using Zapier.

Now, Let's Do a Step-By-Step Turn-Key Set Up

Go over the logic

What sources of leads do we have?

• Website forms
• Call tracking
• Live chats
• Call-backs

80% of the services have a function called Webhook. It works like this: When a dynamic number is called, the call tracking sends the visitor's data to the URL we specify.

What URL should we specify? — We need a mechanism that can receive data, select what we need from it, and insert it into Google Sheets as a new row. This task will be performed by Zapier service, acting like a connector.

Next, we need a mechanism that would check the changes in Google Sheets and, when changes are found, send those rows to Google Analytics.

All in all

Source > Zapier > Google Sheets > Zapier > Google Analytics

The most logical way is to start with sources, let's take a real example to implement a link. In our example, the sources are Roistat calltracker and forms on the website.

Let's start with Source > Zapier > Google Sheets

We go to zapier.com, register there and press Make a Zap!

Let's look at the interface of the working window that will open.

1. Home page where you have all Zap's (which we will call "connectors")
2. Connector's logic, for example, is: 1 — receive, 2 — pull out the values, 3 — send
3. The working window of the tab selected on the left – now we asked to select the first widget
4. Connector enabled/disabled

Create a connector for Roistat

Select the built-in connector

Press Catch Hook and Save + Continue:

Go to the tab Set Up Webhook and copy the URL to receive Webhook, we'll keep it in the clipboard for now.

Go to Set Up Options and click Continue:

Now we are asked to make a test query for Webhook, we open ROIstat in parallel and go to the call tracker configuration.

Inside, we insert our URL from clipboard, apply and save the configuration, now we need to go to the website and make a test call. Let's assume your call tracking works correctly, because setting up call tracking is beyond the scope of this article.

According to the ROISTAT documentation, the Webhook forwards the following data

After the call, you should see in Zapier that it received the Webhook; click Continue and let's make the next widget.

Let's name our connector Get from ROISTAT, before we forget. Then, we search for and select Google Sheets

Next, on the Find or Create Row tab, select Lookup Spreadsheet Row

Then, we connect our Gmail account

In Google Drive, we create a new table where we will store all the data and enter the names of the columns, which in our case will be

• Lead Source
• Date
• First Name
• Contact
• Email
• Google Client ID
• Source
• URL
• Revenue
• Transaction Status
• Lead Quality
• Comment (if any)

Next, in Zapier, in the Edit Options tab, we select the name of the created table and the name of internal tab in it.

We need to understand that we may get repeated calls in call tracking and it would be incorrect to create new leads (rows) that duplicate the previous ones. To avoid this, before adding a new row (lead), we make a search in Contact column (phone number) to check whether such phone number is already there. Remember, we already received the phone number via Webhook.

Next, in the Lookup Column field, we specify the name of the column to search for a duplicate, and in the Lookup Value field, we select the value we search for.

Tick the box against Create Google Sheets Spreadsheet Row if it doesn’t exist yet?

Now we automatically get column names from the Google Sheets table we created earlier, and for each column we need to select a value from Webhook that we received earlier from ROIstat. These values will be inserted into the columns we now choose.

By clicking on the field, we get a drop-down list with the name of the field and the value that contains the Webhook from ROIstat.

For example, we want to insert the Date value from Webhook in to the Date column. This needs to be done for each field.

In the end we get correspondence as in the table below, click Continue and send the test row Fetch & Continue

That's it, now Zapier suggests we activate the connector, which we gladly do – the first connector is ready.

Client Interaction with the Table

We have a table that receives new leads, now we need to teach the client to interact with it and record the sales.

This is why we created 4 columns before.

Revenue is self-explanatory. How to make a fixed set of values? For this we create a separate sheet and for each field we make a set of values for selection.

Right-click on the status cell and select Data validation.

A window with settings appears, where we click on the table to choose the values for selection.

We choose a range of statuses

That's it, we now have a fixed set in status field.

We do the same for the Lead Quality column and select a lead rating – it is better to stick to numbers for this.

Then, we send the client this instruction on interaction with the table.

Let's implement the link Google Sheets > Zapier > Google Analytics

To do this, we create a new connector Make a Zap!

We name this connector Google Sheets to Google Analytics, we select Google Sheets as a widget as we did before, except now choose not to create a new row but New or Updated Spreadsheet Row (Check for new or updated rows).

We select an account, as we did before, let's not repeat, we select the same document on Google Drive, the sheet and Trigger Column. The idea is that the connector checks the sheet every 15 minutes and looks at the trigger-column, in our case, this is the Transaction Status, and if a value in it has changed, then it sends data to Google Analytics.

For example, we had a lead, we added the Sold status for this lead, the connector sees it and sends data to Analytics. Ok, but how to make it so that the data is sent only for Sold status and not for In Processing?

We need to make conditions and filters.

We test the widget, click Fetch & Continue to get the data to create the next widget.

We select Filter as the next widget.

Select Only continue if..

In the first field, we select the Transaction Status column, then (Text) Contains and Sold.

So, we have created a condition.

However, we need to send data to Google Analytics in 3 cases: Refused, Sold and Thinking. Now we send data only in case Transaction Status = Sold, so we should add 2 more OR conditions for Refused and Thinking statuses.

We press Fetch & Continue and see that it checked the last row and found that it does not fit any of the conditions, which is what we need, since we do not have a condition for In Processing status.

Our next widget is Google Analytics

We select Create a Measurement

We get authorization through the account, then select your Account and Property (the counter), for Type we select Event Tracking.

Next, set the event parameters

Event Category — we write constant Transaction Status

Event Action — pass the value

Event Label (just one more field) — pass the Lead Quality rating

Event Value (amount delivered by the event) — just a number, in our case, we want to send the transaction amount.

Custom User ID (Google Client ID) — without this value GA will not understand which channel the client came from, that is, we will not be able to understand that the client originally came from the contextual advertising channel.

We click Continue and Send Test To Google Analytics. It's important to note that we will not see the event in Google Analytics, since the connector sends the query in the debug mode, which is like GA test mode not to spoil the real statistics with tests.

In response, we will get this window. It is important for us that Valid should be True, which means that the query to GA is valid.

If there are any problems, we can search for them in hit, which is the query that was compiled.

We enable the connector

Test the connector

Change the value of the last row in the table to Sold

We go to the Dashboard home page in Zapier and click Run on our new connector.

We will be notified that the connector has detected notifications in 2 rows and sent 2 events to GA

Let's check that GA got these events

Create a Goal in GA for Transaction Status event = Sold

Create similar goals for other statuses

Website Forms > Google Sheets

Now we have completed the cycle and created end-to-end analytics, but we still do not have analytics for the leads from the website forms.

To do this, we give a task (see example at the link) to the project developer to make a webhook when forms are submitted on the website – this is a simple task that should not take much time.

Before giving this task, we need to understand a few points.

• The site must have Google Analytics installed to transfer Google Client ID
• By default, when sending webhook we do not receive UTM-codes indicating the source where the lead originally came from, which is why we wrote a small script on Github that uses in Cookie to store UTM-codes from the visitor's first visit to be used when submitting the form.

  The script can be installed on the site via GTM as a Custom HTML tag or assigned to the project developer to implement.

• Before setting the task, we need to create a connector that will accept the developer's webhook and send data to Google Sheets. It is better to tell the developer from the start what fields and what forms you want to get into the table.

Transfer Forms from the Website to Google Sheets

The procedure is similar to the configuring the connector for ROIStat, so we will try to do without detailed descriptions. We create a new Make a Zap connector and select a Webhooks widget, and select Catch Hook in it.

We give the link for receiving Webhooks to the developer, click Continue and again also Continue in the Edit Options section, then we are asked to test the URL, for this we need to somehow emit a webhook - we go to hurl.it

Paste the URL into the Destination field and select POST from the drop-down list.

Next, in the Parameters block, we click Add Parameters and add the parameters (columns) that we want to transmit, then in the values, we specify the test values and click Launch Request.

Below we get

This means that the request was sent and received. Now in Zapier we click OK, I Did This

The next widget is Google Sheets, where we select Create Spreadsheet Row.

Next, we select the Gmail account, the document and the sheet inside, as we did before. In the Edit Template tab, we specify the correspondence between the incoming query and the columns in the table.

Further we test and see if a new row with a lead is created in GS. After we made sure that the connector is working, you can send the Webhook again from hurl.it

Troubleshooting

Zapier

How to test whether rows are added to Google Sheets and whether connector works correctly when Webhooks are sent to its URL.

hurl.it

Invalid request to GA

Copy the query

Go to Hit Builder, paste the query and click Validate Hit

Field settings in connectors get broken

When a new column is added, all the settings are in widgets get messed up. You can see the connectors work log in Task History.

Google Analytics

How to see the composition of events that received in GA?

Create a report

Customize

Note that the events in the report will be displayed with a delay of 15 minutes.

My GA account is in dollars

How to see if the goals are working in GA?