Pulling Google Analytics into Google Docs - automated template driven reporting
The Google Docs library for the analytics API provides a great tool for managing complex or repetitive reporting requirements, but it can be tricky to use. It would be great if it was a simple as dropping a spreadsheet formula on a page, but Google’s library stops a few steps short of that - it needs some script around it. This sheet closes that gap, providing a framework for template driven analytics reports in Google Docs. With it you can set up a report template, and click a menu to populate it with your analytics results and run your calculations - without needing to write a line of script - the code is there if you want to build on it, but you can get useful reports without writing a line of script. Prerequisites While you don't have to write code to use this, there are some technical requirements. To get the most out of it you'll need to have: your Google analytics tagging and views set up familiarity with Google’s reporting API familiarity with Google Docs spreadsheets - some knowledge of Google apps scripting is an advantage If you are looking for something more user-friendly or tailored to your needs, contact us and book a consultation to discuss - we can help with your analytics setup and bespoke reporting solutions. Getting started Setting this up takes a few steps, but you only need to do this once: Open the shared Google spreadsheet Make a copy Enter a view ID in the settings sheet - get this from the Google Analytics admin page. Authorise the script Authorise the API - in the API console - this is the only time you need to go into the script view using Tools|Script Editor Once in script editor select Resources|Advanced Google services On the bottom of the Advanced Google services dialogue is a link to the Google Developers Console, follow this and ensure that Google analytcs API is set to On You're done. You can go back to the spreadsheet and run the report (on the Analytics menu). From now on all you need to do is tweak any settings on the template and run the report. Setting up your own report template You can explore how the template works using the example. Anywhere you want to retrieve value(s) from Google Analytics, place this spreadsheet function on the template: = templateShowMetric(profile, metric, startdate, enddate, dimensions, segment, filters, sort, maxresults) This works as a custom spreadsheet function, for example =templateShowMetric(Settings!$B$2,$B7,Settings!$B$3,Settings!$B$4,$C7,$D7,$E7,$F7,$G7) Note that in the example, several of the references are to the settings sheet, but they don't have to be, you can use any cell or literal value in the formula - it's just a spreadsheet function. To get the values for the API query, I'd suggest using Google’s query explorer. To set this up for a weekly report, say, you would have all the queries reference a single pair of cells with start and end dates. Each week you would change the date cells run the report again - all queries will be run exactly as before, but for the new dates. Using spreadsheet references for query parameters is key. This opens up use of relative and absolute references - for example if you need to run the same query against 50 segments, you list your segments down a column, set up segment as a relative reference, and copy the formula down spreadsheet style. You can use this to do calculations on the sheet and use results in the analytics API, for example you might calculate start and end dates relative to current date. Future posts will cover setting up templates in more detail. Under the hood The templateShowMetric function generates a JSON string. When you trigger the script, the report generator copies everything on the template to the report sheet and: runs any analytics queries specified by a templateShowMetric function removes any formulas that reference the settings sheet (so you can use the settings sheet to pass values to the template, but your reports are not dependant on the settings staying the same)
Analytics showing wrong numbers for yesterday's visits
We've noticed a few issues with clients using Universal Analytics this last month, when visits for the last day have been double the normal trend. It then corrects itself a few hours later - so seems to be just a blip with the data processing at Google. Others have noticed the same problem. The temporary fix is to only generate reports with time series ending the day before yesterday. i.e. ignore yesterday's data. Now Google have officially acknowledged the problem Looking forward to seeing that one fixed!
Measuring screen resolution versus viewport size
There’s a difference between the ‘screen size’ measured as standard in Google Analytics and the ‘browser size’ or ‘browser viewport’. Especially on mobile devices, there are pitfalls comparing the two. Browser viewport is the actual visible area of the HTML, after the width of scroll bars and height of button, address, plugin and status bars has been allowed for. Desktop computer screens have got much bigger over the last decade, but browser viewports (the visible area within the browser window) are not. The CSS tricks site found only 1% of users have their browser viewing in the full screen. While only 9% of visitors to his site had a monitor less than 1200px wide in 2011, around 21% of users have a browser viewport of less than that width. Simply put, on a huge monitor you don’t browse the web using your full screen. Therefore, 'screen resolution' may be much larger than 'viewport size'. The best solution is to post browser viewport size to GA as a custom dimension. P.S. Google Analytics does have a feature within In Page Analytics (under Behaviour section) to overlay Browser Size, but it doesn’t work for any of the sites I look at.
How many websites use Google Analytics?
Google Analytics is clearly the number one web analytics tool globally. From a meta-analysis of different surveys, we estimate it is currently installed on over 50% of all websites or 80% of operational websites using any kind of analytics tracking. We looked at the following sources for this chart: Datanyze survey of Alexa top 1m sites (04/2014) BuiltWith survey of all websites (04/2014) MetricMail survey of Alexa top 1m sites Pingdom survey of Alexa top 10k sites (07/2012) W3Techs survey of their own sites (04/2014) LeadLedger survey of Fortune 500 sites (04/2014)
What's included in Analytics traffic sources?
The Channel report in Google Analytics (under 'Acquisition' section) splits out into 6 or more types of visit channel: Direct Where a visitor has: typed the URL into the address bar clicked on a link which is NOT in another web page (e.g. in a mobile app) visited a bookmarked link Organic Search All visits from search engines (i.e. Google, Bing, Yahoo) which were not an advertisement. You used to be able to filter out people searching for your brand (which are more like Direct visits), but now the search terms are not provided. Paid Search Visits from search engines where the visitor clicked on an advert. Referral Where a visitor has clicked on a link in another website (not your own domain), but not including search engines or social networks. Social Networks Specifically links from known social network websites (including Facebook, Twitter, LinkedIn etc) Email From links tagged as medium = 'email'. Your email software needs to be configured correctly to add this tag. Display Links tagged as 'display' or 'cpm'. FAQs Can I change the channel groupings? Yes, you can change this under Admin .. (Selected View).. Channel Grouping. But we recommend you don't do this for your default view, as you won't be able to compare the historical data.
Subscribe to Littledata news
Insights from the experts in ecommerce analytics
Try the top-rated Google Analytics app for Shopify stores
Get a 30-day free trial of Littledata for Google Analytics or Segment