5 Simple Steps to Optimising Your Monthly Analytics Reporting – John Warner’s Benchmark 2018 talk review

Oct 16th, 2018

Making his Benchmark debut as a speaker, Click’s John Warner set the theme for his talk with a curious anecdote from Italian author and organic chemist Primo Levi about a varnish factory…

In post war Italy, Levi had been tasked with finding a way to recover a batch of the business’ spoiled varnish, which he remedied with the addition of chromium. This was a solution to the problem in hand, as it addressed the fact that the problem with this particular batch was caused by an excess of lead oxide.

However, a chance conversation Levi had with other chemists decades later revealed that the factory had continued to add chromium to its varnishes, despite the fact that a surplus of lead oxide was no longer an issue; the practice was now at best useless and at worst harmful.

The lesson from the tale?

Reporting and many other processes can be similar to this – especially at larger brands. Someone, at some point, has found the solution to a problem and that solution persists long after the problem has ceased to be a problem

This led John to the crux of his talk:

Stop wasting time! There must be a better way…

John illustrated this point with a well-known, but often misattributed quote:

I will always choose a lazy person to do a difficult job, because a lazy person will find an easy way to do it


Frank Gilbert, US politician, 1920s

When it came to Google Analytics reporting, John declared he was just the lazy man the situation required, and joking that he learned most of this life skills from the film The Big Lebowski’s ‘The Dude’


Which is why he was eager to share his streamlined – though nonetheless effective – process for Google Analytics reporting.

Five steps to Google Analytics (GA) automation

Step 1 – Gather your tools

The tools John uses are all free Google properties. These are:

  • Google Sheets – Google’s answer to Excel.
  • Google Analytics ‘add on’ – which will connect your sheet to your analytics account.
  • Google Data Studio – Google’s data visualiser.

Step 2 – Start at the very beginning

Examine what your reporting does versus what it should do. You’ll need to ask yourself what you need to measure and what you want to achieve – reporting isn’t, after all, about simply highlighting your wins, it’s about telling a story to stakeholders using data – stakeholders that often aren’t intimately familiar with your department’s KPIs.

Step 3 – Set up reports

By accessing the GA add on through Sheets (in the add on menu in the toolbar), you’ll be able to select ‘Google Analytics’ then ‘create report’ which will launch a wizard. Here you can sketch out the basics for your report.

John’s bonus tip: you need to name your sheets in a clear and differentiated manner – the reports can add up quickly, and if you leave the sheets with generic names, then you can find yourself in a bit of a muddle.

In addition, you’ll need to select the right analytics view (during set up, you’ll connect the ‘add on’ to your analytics account, but you’ll need to add the view ID to your report which can be found in the Admin section of each view).

After that, you can begin to add dimensions and metrics – there is a search function, and the metrics and dimension names are the same as in GA, so you should be able to find them reasonably easily.

Once you’ve done this, you can then amend the report in the configuration sheet that the wizard generates. You’ll be able to see the details you filled in, in addition to other options like report period, ‘order’ – which allows you to determine how reports are structured (though I’d recommend the creation of summary sheets, so this isn’t vital), you then have:

Filters which allow you to restrict the report – say to a specific subdomain like a blog or resource section.

Segments which makes it easier to restrict data to specific geolocations for example – as the formula here does for the UK. For example, if you needed to limit specific country, you could use:


Limit restricts the data to sample sizes – useful for large eCommerce sites, for example, where reporting on millions of pages is unfeasible.

Spreadsheet URL which allows you to export the data to a secondary sheet – not usually necessary – that you have edit permissions for.

John then went into more detail on session level and page level reporting.

Session level metrics

These reports are generally of the bird’s eye view variety – giving you insight in to the overall performance of the site. These are best given a timed dimension – while you may not use it, Data Studio requires dimensions for most chart types, and date ranges are the most generally applicable.

You can then look at tracking things like ‘sessions’ which will give you an indication of the sessions for the applied time limit (or at least the data for the length of time the view has existed, where this is less than the duration set).

Users, which will tell you how many individual users have generated your sessions.

Percentage of new sessions, which can show you how well you’re retaining your audience and how many new users are visiting the site for the first time.

Session duration can indicate how well you’re holding the attention of your visitors (though it’ll need converting, which I’ll go in to in a moment).

Page views will show you how many of your site’s pages have been viewed within the selected timeframe, while Page views per session is the average number of pages viewed in each session on the site.

Goal completions, which you can track either as a group for the view, or individually using either the completions all or the goalXX – where XX is the ID number of the goal you can find in the Admin section in GA.

Page level metrics

This allows you to be a little more granular and while it will probably use many of the same metrics and dimensions, you can look to track performance at a page level.

The dimension John finds most useful is page title, which allows you to monitor performance page by page for your site.

The metrics, however, remain much the same – with sessions returning the sessions for the stated page, users telling you the same, while the percentage of new sessions can tell you whether your page is being visited often as a resource, or is only answering a query for new users.

Session duration gives you the average length of a session including the designated page.

Page views will tell you how many additional pages to that specified a user has visited, while page views per session tells you the number of pages visited in a session including the correlated page.

Unique views will also give you an idea of how visible a certain page is – potentially revealing keywords for which you are ranking, but which you may not have been reporting on.

Average time on page can be a great indication at page level of how good your content is – whether your visitors are reading to the end of the piece or abandoning it after a matter of seconds, giving you a good idea of where improvements may be necessary.

You can again track either the total completions or individual completions per page in the same way for page level as for session level – giving you information on the performance of landing pages, for example.

A full list of metrics and dimensions are avaialble on the Google developer site.

Step 4 – Create summaries

While it’s possible to import straight from GA to Data Studio, John recommends data manipulation to be a lot easier (and familiar) in Sheets. He then couldn’t resist another literary quote that sumed up the aim of this step:

Hopefully revealing that I’m more of a word than numbers person, which should give you hope if you’re worried about the maths

Portuguese Nobel Prize winner Jose Saramago

Some basic formulae for summarising data

Duration – as mentioned before, isn’t a legible time when you pull it from analytics, but you can resolve this by simply dividing the number by 86,400 (the number of seconds in a day) and setting your cell to return as a time display. Formula:


Reporting period can either be manually imputed, or set to update automatically using the formula:

reporting period

Finally, there are sumif formulae – which you can use to look up specific text strings and count their associated figures (if you’re looking at visits from a specific social media platform, for example). Formula:

sumifThese formulae are split into three :

  1. where to look for the text string
  2. what text to look for
  3. where to look for the numbers to add up

Step 5 – Import data to Data Studio

According to John, this final step is the easiest – as long as you’ve got the previous four right!

In the program:

  1. create a data source
  2. select Google Sheets
  3. use your summary sheets and the wizard to create a range of easy to understand charts and tables

John explains that he carried out this process on Click Consult’s own internal analytics reporting – and the results were pretty good:

I condensed 49 measurements (which has risen to almost 60) into eight charts and tables which saves more than ten hours per month (or 15 full working days per year), but more importantly I got an article and a talk out of it – and saved time

If this summary has piqued your interest, dive a bit deeper into John Warner’s 5 Simple Steps to Optimising Your Monthly Analytics Reporting by viewing his slides, or the video from his talk:

Read summaries of all of the Benchmark 2018 talks, and ensure you’re up to date with all things search by signing up to our blog. Alternatively, see how we can help to build your brand online by contacting us today.

Facebook Twitter Instagram Linkedin Youtube