Google Sheets formulae and tips for reporting

I spoke at Ignite B2B a few weeks ago – about how and why brands should be automating their Analytics reporting – and while I was reasonably pleased with the talk, there were a few people at the end that were asking about specific formulae I’d referred to in passing. One of those people gave me his email address only for my phone to break later that day – so this is blog is, in part, for that guy! Sorry that guy!

I made the mistake of assuming a higher level of knowledge than some of my audience had (a failing of mine rather than theirs) and whizzed past some information that would have been useful – so I’m going to address that here.

The methods of report automation can be found in detail in another blog I wrote, but I’ll cover bits and pieces here to illustrate the use of some of the formulae.

I’ll start simple, giving an overview of a few formulae and then run through the creation of a couple of the summary sheets and the creation of a long formula string using multiple cells.

Duration

The number that pulls from Google Analytics, for things like session duration, bears seemingly little relation to any duration you might be familiar with – however, if you divide the output from Analytics by 86400 (the number of seconds in a day), and display in time format, you will get an easier to digest duration result for reporting.

Formula

=SUM(**CELL WITH DURATION FROM ANALYTICS**/86400)

image showing result of time formula

Reporting period

The second lot of time-based formulae are those used for setting the period of the report. While it’s not overly strenuous to change a few dates each month, when you’re working on 50+ reports it gets a little more time-consuming, so you can use a few formulae to automatically update the reporting period.

Formulae

Today’s date: – =TODAY()
Start of the month: – =EOMONTH(**CELL WITH THE TODAY FORMULA**, -1)+1
End of the month: – =EOMONTH(**CELL WITH THE TODAY FORMULA**, 0)

image showing example of date formula

Conditional counting

A few of the summary tables I use for reporting use the same conditional count formula which is the ‘SUMIF’ – this allows you to count or total a column using only the figures that match specific conditions (in this case, generally, a character string). In this manner, when looking for goal completions with a specific social media source I’ll ask the spreadsheet to look for text strings containing the name of the platform and to count the attributable conversions.

Formula

=SUMIF(**LOCATION OF TEXT STRINGS**, “*TEXT STRING*”, **LOCATION OF NUMBERS**)

This breaks down into three sections – where to look for the text string, what text to look for, and where to look for the numbers to sum. I tend to use a ‘containing’ search for this (hence the asterisks in the string) as the sources that come from Google Analytics will include a number of different TLDs (facebook.au, .com, .co.uk etcetera).

image showing example of sumif formula

Concatenate

For API requests and some of the social follower lookups, I’ll use a series of concatenate formulae in order to build up a request or add one text string to another. While this can be a bit time consuming to set up, if your reports change in size from month to month (with greater or fewer referral sources or different page titles in different positions), using concatenate can help you avoid having to start from scratch every month.

The way this works is essentially to insert a changeable text string in to a static formula – a simple example is when using concatenate to create the URL for looking up twitter follower numbers (you can find that here), while a more complicated version would be, for example, creating an Ahrefs API request – which uses the same technique but over multiple cells.

Formulae

=CONCAT(“FIRST PART OF TEXT STRING”,**LOCATION OF CHANGEABLE TEXT STRING**)

image showing example of concatenate formula

General formulae

The rest of the formula I use to automate various parts of the Analytics report are general arithmetical formula (=SUM and similar), which are fairly easy to implement and information on them can be located – if more details are required – in this full list of functions.

Creating a summary sheet

It’s perfectly possible to create reports from the raw data – or by importing data directly in to Data Studio but, frankly, I prefer the UX of Google Sheets (possibly because I’m old and just more used to the spreadsheet format), so I tend to create Data Studio reports from a set of summary sheets, allowing me to manipulate the data and pull in different metrics from a number of different report types before using Data Studio as a visualiser.

I’m going to give a couple of examples in the hope of giving a bit of a working example for above formulae. Provided you have your Analytics add on and Google Sheets open, you’ll be familiar with the report configuration tab below. We’re going to look at two of the reports I have running – a blog performance and a referral source report – and we’ll look at the summaries taken from them.

Referral sources

referral sources

In the first of these reports, we’re pulling just the sessions per source (there are plenty of things you can do in addition, but for the purposes of brevity). The reason I create a summary sheet is because this pulls all of the various URLs associated with a platform and they come in multiple cases.

referral sources

So, in order to total all of the sessions per source, we can use a partial text match lookup (as above) which will look for text strings containing the starred value in all its various forms.

sources total

This will then give you a total number for the referrals which can be used in Data Studio as is, or combined with various other metrics (conversions per source, for example, in order to give a goal conversion rate for the source) to give a more in depth view of performance.

Blog performance

blog performance

This report takes in a few metrics, filtered by the ‘blog’ path – again, this is a simplified report – you can add up to ten metrics per report, and in the summary pages you can combine them in various interesting ways, but this report gives us a few of the main metrics we want to monitor – including the time on page, which I won’t repeat as the calculation and formula is fairly simple – and gives the URL, which we can use for concatenation in a moment.

Because the position of these URLs change from month to month, to save having to manually input over a thousand formulae, I instead use a series of concatenate cells so that it can update automatically. The following is the concatenate as it looks, followed by the formula (I apologise that they’re out of order, I hide them from sight ordinarily so people can’t see my messy column organisation).

concat

concat formula

As you can see from the formulae in the second image, I piece together various pieces of a formula using what is essentially a one potato, two potato method – one cell building on the next; this is with the exception of the “* cells – which have to be added separately as they are otherwise misread by the cell. Once this is set up, you can then record a macro which has three steps – copy the column, paste special (values only) into the next, then find and replace the equal sign with another equal sign – this activates the formula so that, again, you don’t have to do this manually, it’ll do it for you whenever you open the sheet.

Hopefully this will cover the gaps in that presentation, but if I’ve missed anything – feel free to ask me in the comments. I’ll leave you with this…

Final thoughts

If you’re already an expert in Google Sheets and have made it this far – this advice is for you: don’t assume knowledge in your audience; you’ll find yourself speaking to people of various levels of experience, and it’s better to give people information that they don’t need than to risk not delivering the information they do.

John Warner

About John Warner

John is an internal marketer at Click Consult where he spends his time accruing industry certifications, tinkering with code, plotting strategy and writing articles on all aspects of search marketing. He also contributes to free quarterly search magazine 'Go Viral' and is the occasional host of the on.click podcast (available on iTunes and Stitcher).