Client: we cannot increase the contextual advertising budget because we do not see the cost/benefit and not all leads are converted to sales.
Agency: implement AmoCRM, we will integrate it with Google Analytics
Client: we don't have the needed number of leads, this is too expensive, time-consuming and complicated.
Agency: Ok, then there is nothing that we can do.
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.
What sources of leads do we have?
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.
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.
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
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
|Column|| Webhook |
| Lead Source ||Call (this is a constant)|
| Date ||Date|
| First Name || |
| Contact ||Caller|
|Google Client ID||google_client_id|
| Source ||marker|
| Revenue |
| Transaction Status |
| Lead Quality |
|Comment (if any)|
That's it, now Zapier suggests we activate the connector, which we gladly do – the first connector is ready.
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.
|Transaction Status|| Fixed set of statuses |
|Lead Quality|| Fixed set of values |
| Comment (if any) ||Arbitrary field|
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.
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
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
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 script can be installed on the site via GTM as a Custom HTML tag or assigned to the project developer to implement.
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
Copy the query
Go to Hit Builder, paste the query and click Validate Hit
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.
Create a report
Note that the events in the report will be displayed with a delay of 15 minutes.