Pavel Koryakin , 28 марта 2018

Free End-to-End Analytics in 20 minutes: Google Sheets + Google Analytics + Zapier

We have figured out an end-to-end analytics solution for small companies. This article is meant for all internet marketing professionals.


What problem is this solution for?


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.

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!

End-to-End Analytics

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

End-to-End Analytics

  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

End-to-End Analytics

Press Catch Hook and Save + Continue:

End-to-End Analytics

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

End-to-End Analytics

Go to Set Up Options and click Continue:

End-to-End Analytics

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

End-to-End Analytics

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

End-to-End Analytics

End-to-End Analytics

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

End-to-End Analytics

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

End-to-End Analytics

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

End-to-End Analytics

Then, we connect our Gmail account

End-to-End Analytics

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.

End-to-End Analytics

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.

End-to-End Analytics

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.

End-to-End Analytics

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
Email  
Google Client ID  google_client_id
Source
marker
URL landing_page
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.

End-to-End Analytics

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.

Column Description
Revenue Transaction amount
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.

End-to-End Analytics

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

End-to-End Analytics

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

End-to-End Analytics

We choose a range of statuses

End-to-End Analytics

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

End-to-End Analytics

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).

End-to-End Analytics

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.

End-to-End Analytics

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

End-to-End Analytics

We select Filter as the next widget.

End-to-End Analytics

Select Only continue if..

End-to-End Analytics

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

So, we have created a condition.

   If a new or updated row has a value in the Transaction Status column contains the word Sold, then we send the data to Google Analytics, and if not, we do nothing.

End-to-End Analytics

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.

End-to-End Analytics

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.

End-to-End Analytics

Our next widget is Google Analytics

End-to-End Analytics

We select Create a Measurement

End-to-End Analytics

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

End-to-End Analytics

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.

End-to-End Analytics

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.

End-to-End Analytics

We enable the connector

End-to-End Analytics

Test the connector

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

End-to-End Analytics

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

End-to-End Analytics

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

End-to-End Analytics

Let's check that GA got these events

End-to-End Analytics

Create a Goal in GA for Transaction Status event = Sold

End-to-End Analytics

End-to-End Analytics

Create similar goals for other statuses

End-to-End Analytics


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.

End-to-End Analytics

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.

End-to-End Analyticsа

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.

End-to-End Analytics

Below we get

End-to-End Analytics

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

End-to-End Analytics

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

End-to-End Analytics

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.

End-to-End Analytics

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

End-to-End Analytics

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

End-to-End Analytics

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.

End-to-End Analytics

Google Analytics

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

Create a report

End-to-End Analytics

Customize

End-to-End Analytics

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

My GA account is in dollars

End-to-End Analytics

How to see if the goals are working in GA?

End-to-End Analytics

Subscribe to Facebook, so you don't miss new articles!
Best articles
Similar articles
9 Case Studies of Online Store Google Ads Automated Bidding Strategies: from 4-fold Revenue Growth to Drop to Zero
9 Case Studies of Online Store Google Ads Automated Bidding Strategies: from 4-fold Revenue Growth to Drop to Zero
We look at successful and failed cases of our clients to understand when automated bidding strategies can boost ROAS by 30%, increase orders and revenue by 4 times, reduce the cost per lead by 2.3 times or conversely sink all indicators.
Impact of Google Ads Withdrawal from Russia on Contextual Advertising in E-Commerce: So Far Not So Bad. But that’s for now. NEWS
Impact of Google Ads Withdrawal from Russia on Contextual Advertising in E-Commerce: So Far Not So Bad. But that’s for now.
It seemed that after Google’s withdrawal, the advertising market in Russia should collapse. But so far, not everything is so definite: some of our projects enjoy revenue growth. In this article we are looking into why this is so and which tools work best now.
Integration of the client and the agency HOWTO
Integration of the client and the agency
In the article we will tell you about our integration setup between the client and the agency, and how communications are built.
How to do web analytics for SaaS through Google Analytics: introducing and tracking funnels HOWTO
How to do web analytics for SaaS through Google Analytics: introducing and tracking funnels
I found that in several SasS projects, proper analytics were not set up to track and maintain funnels. To solve these problems, we chose Google Analytics.
Free End-to-End Analytics in 20 minutes: Google Sheets + Google Analytics + Zapier HOWTO
Free End-to-End Analytics in 20 minutes: Google Sheets + Google Analytics + Zapier
Hi! We have figured out an end-to-end analytics solution for small companies. This article is meant for all internet marketing professionals.
Script for substitution of content on the site through UTM tags HOWTO
Script for substitution of content on the site through UTM tags
How to set up a dynamic replacement of almost any element on the site. Just using a simple script and Google Tag Manager.
to read our blog

Get your Google Ads audit

After you leave a request: interview ~15 minutes → guest audit access ~15 minutes → audit within 2-4 days → proposal approval → first iteration start. In our experience, it is realistic to get the process rolling in 2-3 days.

Project manager Alex
Alex

Project manager



Write to — info@1jamagency.com or skype —