A Roll-up GA dashboard using Google Sheets & Apps Script

5 minute read

If you manage a lot of web properties then you surely have run into the problem of replicating the same report across all of the web properties. While, having the ability to create an aggregation of web properties is the most convenient (a roll-up account) there are cases where you cannot actually implement it or the implementation of such a solution will either require significant cut back on data points sent to the roll-up as Google Analytics comes with limitations to quota usage. It always advisable to check your current utilization, you can do it using this tool. As always, reality is not ideal so a roll-up account might not be available. In this case our fallback data retrieval mechanism is the Google Analytics API. We have described ways to access the API using R in two posts : Google Analytics + R = FUN! & Basic A/B Testing plots and stats with R. In the context of our post we need to create a simple dashboard that tracks the conversion rate of our handful of web properties across channels, so this is something that we would pursue using the Google Apps Script under the notorious name Magic Script.

Getting the Magic Script

Open the Script Editor in a Google Spreadsheet clear the code appeared and paste the Magic Script code found in this gist. Note : Use this gist because it contains some modifications compared to the initial Magic Script from the GA team.

If you haven’t used the Google APIs in apps scripts before go through the following steps to enable the Google Analytics API:

  • In the script editor go to: Resources > Use Google APIs
  • Turn ‘Google Analytics API’ to ON
  • Click the link to Google APIs Console
  • Turn ‘Google Analytics API’ to ON
  • Accept the terms of service
  • Close the ‘Google APIs Console’ window
  • Click OK in the script editor window and close that window
  • Back in the spreadsheet, the ‘Google Analytics’ menu should now be working. (You may need to re-authenticate.)

Now, a new tab should appear next to ‘Help’ names Google Analytics. If you click it you will see that you can create not only basic reports configuration but Multi-channel funnel reports as well.

Define a report

We will track conversion rate for the last 30 days. This means that we will not store data but we will only use the fact that the API can query based on relative dates. Let’s create a core report

query1 value1
type core
ids ga:XXXXX
start-date
end-date
last-n-days 30
metrics ga:Transactions,ga:visits
dimensions ga:date
sort
filters
segment
start-index
max-results
sheet-name Website_A_data

The report you will define here will be replicated for all web properties you have. To replicate the report you have to simply drag the first row until the aut0 increment of queryX,valueX reaches the number of web properties. Also, name the target sheet accordingly to reflect the web property. Then you need to create a intermediate sheet to aggregate the data (as the data will be refreshed on the target sheet hence every run will clear all contents of the sheet) plus in the future you might need to add more dimensions to the query that will result to more columns added). Essentially, in a new sheet we aggregate data from the queries’ fed sheets, like below.  

Tip : Create the first formula and then select Show all formulas from the View menu. Then simply Find & replace the sheet name in the respective web property column and you are done!

=UNIQUE(website_A_data!$A$12:$A$150) website_A website_B website_N Total
1/31/2014 =sum(website_A_data!B13) =sum(website_B_data!B13)  … =sum(website_N_data!B13) =sum(B2:G2)
2/1/2014 =sum(website_A_data!B14) =sum(website_B_data!B14) =sum(website_N_data!B14) =sum(B3:G3)
2/2/2014 =sum(website_A_data!B15) =sum(website_B_data!B15) =sum(website_N_data!B15) =sum(B4:G4)
2/3/2014 =sum(website_A_data!B16) =sum(website_B_data!B16) =sum(website_N_data!B16) =sum(B5:G5)

Create blocks of the above defined by the metrics you have defined in the report. this means you will make another table as above with source data the sheet-name!C13 which will give you a table with the visits. Then creating row totals will give us daily totals for all web properties reported and column totals will  give period wide totals for each web property. Last a sum of all cells in a block will give the total visits/conversions/dimensionX for the period summing over web properties. The division of totals gives period wide conversion rate for all wep properties and a cell by cell division provides the conversion rate for a specific web property for a day.

How to fight Execution Errors

Google spreadsheets have a time limit of 5 minutes when running scripts. So, if you have a heavy on queries spreadsheet you might get errors like that. The solution to this is to break down queries to more spreadsheets and then import data into your dashboard (the master spreadsheet) using the importrange() function which is magical by all means! However, in our case here if an error occurs (lets assume that it will occur in the last query) you can let viewers of the dashboard know using a message in cell using the next if() statement.

=if(now()-B2>0.019,"Beware : Execution Error in the last run!","")

Dashboard without sparklines?

No sir! In case you didn’t know Google Spreadsheets have a sparkline() function to use for the dashboards you are designing which are a great source of eye-picking information for spotting ups and downs in the time trends of your metrics and KPIs. Make sure that you won’t exaggerate like me with the use of sparklines…

Sparkline of Type I : Line

Line will show you time evolution, for your conversion rates, traffic, qualified visits etc. Look for peaks and lows or relative flat linings

Sparkline of Type II : Bar

Really nice to show you relative performance, say today vs yesterday (or vs this day last week). To use this the formula is simply

=SPARKLINE(B6:C6,{"charttype","bar"})

In the end

The following is a sample dashboard with a few runs on my personal site and some other blogs I have access to. It’s not really e-commerce rate but rather goal conversion rate for a contact form submission. Take a look at this and get some ideas on how to create more for your personal dashboard.

There is no end, if there is no tracking code added…

The Magic Script you got above is modified and enables you to track the usage of the Spreadsheet (using pageviews) and the execution of the getData function with event tracking. simply open the Script Editor and replace your UA in the script. I suggest having a separate Google Apps web property and use a content grouping per Google App (spreadsheets, docs, forms etc) to track utilisation of your drive creations. This is really exciting to see how (and if!) people are actually using the docs you shared like the screenshot below.

Happy dashboarding!

 

Leave a Comment