Google Analytics out-of-the box dashboards allow you to create good overviews, but didn’t you ever feel frustrated because you needed something more?
In this blog post I will show you concretely how to put together Google Analytics, Google Spreadsheets and Google Sites to overcome this limit and plot multiple dimensions and artificial metrics in plots that are not (yet?) available in the out-of-the-box Google Analytics dashboards.
Before going through this tutorial, make sure you have read this article and understood what we are building 😉
You need to track user engagement on your enterprise blog to evaluate which kind of article is most appreciated by your visitors and you want to display on the same graph the following KPIs
- User has read the article from the top to the bottom
- User has taken enough time to read the article (opposed to a “scanner”, he is a “reader”)
- User has commented, shared or liked the article
Let’s define our artificial engagement KPI as the amount of social actions per pageview.
Google Analytics Pre-requisites
- For KPIs (1) and (2) you should setup scroll-tracking: read this article to learn how to do that. This implies having set up
- A custom dimension called “Reader Type”.
- An event of category “Page Interaction” and action “Scroll Down” getting triggered every time a quarter of page (25%, 50%, 75%, 100%) is scrolled down by a visitor.
- For the engagement metric you should track social actions. If you don’t know how to do it, I suggest you to read this great article be Eric Fettman.
- In order to segment our articles by “Topic” you should track this information as a custom dimension. There are number of ways to do that. E.g. with GTM you would just need to create a custom script reading it from the article page and pushing it to the datamodel. Something similar is described in this old but good post by Lunametrics.
Google Spreadsheet Setup
Step 1 – Copy the Google Spreadsheet
Get yourself a copy of the pre-configured Google Spreadsheet by providing your email in the below form.
Step 2 – Configure the Settings Sheet
Once you have created the 2 required custom dimensions in Google Analytics (Reader Type and Topic) and you are populating them via Google Tag Manager, which should also be configured to fire scroll depth events every 25% of page, you are ready to configure the spreadsheet.
First go to your Google Analytics account, under Admin > View Settings > View ID
Copy that number in the first yellow cell right after “ga:”.
Fill in the rest of the form as explained below each cell.
Step 2 – Install the Google Analytics Add-on
In the spreadsheet go to Add-ons > Get Add-ons > Search for “Google Analytics” and then install it.
Step 3 – Run the Report
You are almost done: amazing, isn’t it? Just go to Add-ons > Google Analytics > Run reports
If you did everything correctly your green-labelled sheets should get populated with analytics data.
Reading the Reports
Ok maybe it’s worth explaining you what this spreadsheet is made of. Beside the Settings and Report Configuration sheets, we have 5 sheets containing the raw report data (which you actually should not have to change)
- Topics: lists all available topics (we don’t care much about the metrics here)
- Page Readers: collects the amount of Readers & Scanners for each post
- Page Scrolling: collects the information about scroll depth for each post
- Page Report: collects the relevant metrics for each blog post (unique pageviews, time on page and and exit rate)
- Social Shares: collects all social actions on your blog posts
And finally, the reporting tabs, where everything comes together:
- Page Summary: aggregates all collected data in a single table to display
- Page path
- Page title
- Unique pageviews
- Avg. time on page
- % of time the page has lead to another page
- #Shares on each social network (Facebook, Twitter, Google, Instagram, Linkedin)
- Scroll depth for each 25%
- Engagement: this sheet aggregates everything in a single plot
- Bubbles: 1 per topic
- X-axis: % of readers (100% is great, 0% sucks)
- Y-axis: % average scroll depth (100% is great, 0% sucks)
- Bubble size+Label: Amount of shares per post (100% is great, 0% sucks)
- Legend: Topic name (and amount of unique pageviews)
That’s what I love about bubble charts: in a single plot we have our 3 KPIs and the segmentation (Topics) which allows us to compare them one against the other!
Publishing your Charts
You don’t want your CMO to open spreadsheets and see big tables, do you? Here is why you still need a last little step to publish your charts on a website. You have 2 options here
- Simply publish your chart and embed the generated HTML snippet into any page (but your chart will be accessible to anyone that knows the URL)
- Create a simple site with Google Sites and embed the chart, assigning user access to whoever you want (the chart will be safe)
Click on the top right arrow on the chart and select “Publish chart”.
Then you just have to choose if you want to embed or share the link to it and hit “Publish”.
Google Sites Version
Create your own Google Site. No tutorial for this, it’s no rocket science 😉 You can get some help here.
Once you have created your report page you can just go to Insert > Chart and select the Spreadsheet you just saved into your Google Drive.
Pick the bubble chart. You just need to specify title and size and you are done!
That was easy, wasn’t it?
This tutorial was supposed to cover a pretty simple (and standard) usecase. If you need to implement something more complex you might want to get in touch with us to know more about our services!
Read all our Web Analytics post
Never miss an update by following us and subscribing to our monthly newsletter!
I have over 8 years of experience in consulting and hands-on experience in Web Analytics, especially with Google Analytics (10+ years), Google Tag Manager (3+ years), Google Data Studio (1+ years) and Microsoft Power BI (2+ years).
I am also a proud dad, a passionate football fan (AC Milan) and I like to spend all my free time writing on my food-blog (www.cucina.li). What can I cook for you? 🙂
Latest posts by Andrea Rapanaro (see all)
- Break data silos with Google Data Studio and Supermetrics - April 25, 2017
- The Conversion Summit 2016 Highlights - September 2, 2016
- How Google Data Studio can help your Business - August 5, 2016