Last week I did a tweet with this image in it:
It shows a Google Sheet with some data per AdWords campaign and a little sparkline next to the numbers. It got much attention, and I got many requests to explain what I did there. So here it goes, I will explain some things about the Google Analytics API, how you use it in a Google Sheet and how to extract and query data from it. And last but not least: how to create the little sparklines.
The Google Analytics API
Google Analytics provides an API that is really powerful, it has more options to retrieve data than the normal web interface. For instance, it can get you a table with 7 dimension at once:
Left of the blue line are all dimensions, right of the blue line is a metric. To learn the possibilities of the API I recommend this tool:
Authorize it, and play around with all the dimensions and metrics that are available. You will use this knowledge later on in the Google Sheet. If you hover over the several fields you see a little link with the text "Read the reference for [fieldname]". There you can read all about the possibilities per field. You will learn for example that you can use "30daysAgo" as start-date in stead of a fixed date like "2014-06-25" that the tool 'forces' you to fill in.
I use this tool as a Google Analytics extension tool for some quick analysis if I need some more dimension or options than the web interface can provide.
Google Sheets and Google Analytics
The next step to create a dashboard is to create a new blank Google Sheet. Click on the "Add-ons" menu and "Get add-ons":
Find the Google Analytics add-on and install it. The next time you open a new sheet you wil see Google Analytics as a default add-on. Click on the option "Create a New Report" in the Google Analytics add-on menu. On the right side you will see a form like this:
Looks a bit like the options in the tool I mentioned before. Create the report with the numbers you want and save it. After creating a few reports you get some extra tabs in your sheet: one with the configuration of all the queries, and a tab per query for the output/data that is generated by those queries.
As an example we're going to create the report I mentioned in my Tweet. To get the right data you need this setup:
Two reports with the total data per period and 2 reports with data per week or day. It's possible to skip the two left ones because you could add-up the data in the two right ones to get the totals. But in this example we'll use al four of them. The easiest way is to create 4 reports with 1 medium and 1 metric, and then just fill in the sheets manually by copy/pasting metric and dimension names to the other cells. You can even put cell references in the other cells, so when you change the "Start Date" for "Period 1" you also change the dates for "Period 1 and 2 per day/week". The "Start Date" for "Period 1" is in cell B5, then put "=B5" in the "Start Data" cell for the other reports to link them. I choose to use "XXdaysAgo" as date parameters, that way you can run the reports again and get fresh data for the last period.
I chose the dimension "campaign" to do my reports on, but you can select whatever dimension you want: keyword, country, etc.
When you're ready, go the the Google Analytics add-on menu and run the reports.
Creating the reports with the sparklines
If all went well you should have 5 tabs at least: 1 configuration tab and 4 tabs with the output. Create a new blank one to build the dashboard on. This is the basis I have right now:
And here are the formulas per cell:
- B2: =sumif('Period 2'!A:A,if(A2 = "", , "*"&A2&"*"),'Period 2'!B:B)
- C2: =sumif('Period 1'!A:A,if(A2 = "", , "*"&A2&"*"),'Period 1'!B:B)
- D2: =if(B2>0, C2/B2-1, 0)
- E2: =if(B2+C2>0, sparkline(query('Period 1 and 2 per day'!$A$15:$G,"select sum(C) where A like '%"&$A2&"%' group by B")),"")
- F2: =if(B2+C2>0, sparkline(query('Period 1 and 2 per week'!$A$15:$G,"select sum(C) where A like '%"&$A2&"%' group by B")),"")
With an explanation (you need some basic Excel/Google Sheet skills) per cell:
- B2: this cell is a simple sumif formula that will add-up all cells you're looking for. The first parameter "Period 2'!A:A" is the data you are looking in. The next parameter is the data you're looking for: "if(A2 = "", , "*"&A2&"*")". It uses the value from cell A2 to search for. As you can see I put a * on both sides so that it's looking for all campaigns that contain the text from cell A2. And if A2 is empty than don't search. The last parameter is the column that contains the metric you want to add-up. In this example column B is "ga:adClicks". You can select any other column you want to add-up.
- C2: it's the same as B2 but with the data from Period 1.
- D2: first a check if B2 is bigger than 0 otherwise you get the "can't divide by zero" error. The "C2/B2-1" is the formula to get the change in both columns in a percentage. Don't forget to set the format of this column to percentage:
And add some green/red style to it by selecting "Conditional formatting" in the "Format" menu:
- E2: This is where the magic happens. First a check if the found numbers are greater than zero. Then the query part, it has 2 parameters. The first one "'Period 1 and 2 per day'!$A$15:$G" is the range you are searching in. The second parameter is a query some people will recognize if ever worked with SQL databases, more explanation about "query" in the Google Sheets help section. The query in this example wants to add-up (SUM) column C, that is where the clicks are, where column A contains the text from cell A2. Note that the % in a query is the same as a * in a normal cell, a wildcard. And at last we need to group all the found data together based on column B, that's the date. If you put this query formula in a separate cell it will generate a new table based on the found data. But in this case you can surround it with the sparkline function so it won't generate a table but a sparkline whit the data.
- F2: Is the same as E2 only you use the data from the "per week" report and group it per week. For long periods it gives you a better insight than the data per day.
Another trick to get average conversionpercentages per day or week is to get the amount of sessions and absolute conversions per day/week and divide those like this:
- F2: =if(B2+C2>0, sparkline(query('Period 1 and 2 per week'!$A$15:$G,"select sum(C)/sum(D) where A like '%"&$A2&"%' group by B")),"")
Where column C is sessions and D is conversions.
That's it. Looks cool right, and even for someone with basic knowledge reasonable easy to create by smart copy/pasting and changing the report/columns references. Show me what you got in the comments. And if you need help debugging your sheet, try the Google Docs help center or the Google Docs help forum.