As a multi-channel digital marketing agency, we spend a lot of time working across SEO, PPC, Display, and Social, with the aim of having these channels work together harmoniously to drive the best possible results.

But how do we measure these results?

This used to require opening up several tabs and navigating across several platforms to download a variety of different reports, to obtain the data from each of these channels and combine it into a spreadsheet that would act as the single source of truth… only to have to do the exact same thing tomorrow or next week when the next Report was due.

It was time-consuming and even a small oversight such as an incorrect filter would result in errors in your report data (an easy mistake to make before your morning coffee has had a chance to take effect!).

Thankfully, there are alternatives to this process which can help you save time (and therefore money through better use of resource) to allow you to focus on analysing data rather than retrieving it.

While Web Query has long been a favourite, it has its limitations, the main one being the fact that it is only natively supported by a few platforms. Third-Party tools like DoubleClick and Marin are able to use this, but there doesn’t seem to be a way to cleanly utilise this across Google Analytics, AdWords, Bing Ads, or several other platforms.

Enter Supermetrics, a plug-in for Google Sheets (which can then be easily visualised in other ways such as dashboards like Google Data Studio). Though this is a paid tool (more on that later), it offers a free 30-day trial for you to get all your ducks in a row and ensure that it is the right tool for your needs.

First Things First

Enable the Supermetrics Add-On in Google Drive.

This is a rather straightforward process, and it also allows you to sign up for your 30-day free trial.

One caveat here is that the trial and (should you choose to stick with it) paid subscription will be linked to the Google Account you use. While it is possible to change this to a different user, we’d recommend getting this right from the start.

We’d suggest, if you have multiple users who may be updating and altering queries, using some shared ‘Reporting’ Google Account.

Once enabled, open any Google Sheet, and from the ‘Add Ons’ Menu, go to Supermetrics > Launch Sidebar.

From here, you can see the services you can access using the tool under ‘Data Sources’.

Note that the free subscription only allows you to access Google Analytics, and only 50 rows at a time.

Once you select the service you want to access, you will be prompted to log in to the account and provide Supermetrics with the relevant permissions to access your data.

Pulling your first report

For this example, we will be using AdWords, Bing Ads, and Google Analytics to pull a simple report showing daily activity over the past 14 days. This will use Google Sheets, Supermetrics, and a few of my favourite formulas (admit it, everybody has some…right?), namely SUMIFS and IFERROR.

To start, let’s pull some data from Google AdWords.

Firstly, we choose the account we are pulling the data from. You can also combine multiple accounts into a single report for those clients who run activity from several AdWords accounts at once.

Next, we select our metrics. We will be retrieving Impressions, Clicks, Cost and Conversions (as we can then calculate CTR, CPC, CPA, and Conversion Rate from these), and let’s segment these by Date and Campaign, going back 14 days (make sure you click ‘Including Today’).

Now that we have selected all this, we click ‘Get Data to Table’ and Supermetrics will query the system and retrieve the data and place it in a table on the active sheet. We will name this ‘AdWords Data’.

Note: Supermetrics is also capable of creating graphs, but we prefer to pull data into tables instead, allowing us to manipulate the data to get better insight.

Next, we open a fresh page in the same workbook and do the same for Bing, naming that sheet ‘Bing Data’.

Finally, we open another fresh sheet and we query Google Analytics. This time, we will pull whatever our chosen Goal Completions are (or eCommerce Transactions, if relevant) and segment by Date, Campaign and Source/Medium and we set a filter to only pull data where the medium is ‘CPC’.

If you have other channels using this ‘CPC’ medium (e.g. affiliate activity), you may still need to filter manually when displaying the data in the report (using some more parameters in your SUMIFS). We’ll name this sheet (you guessed it) ‘Analytics Data’.

Note that if you want to alter these queries at any time, you can go to ‘Add-ons’ > Supermetrics > Manage Queries, which will take you to the ‘SupermetricsQueries’ sheet. From there, you highlight the relevant QueryID and launch the sidebar, which will allow you to update the query and change date ranges, filters, segments, metrics, and even which account you get the data from.

Building the Report

Now that we have our raw data in the sheet, let’s build a basic framework for our report. As previously mentioned, we’ll be doing a topline report showing our metrics broken out by day for the past 2 weeks.

Let’s open up a new sheet and call it ‘Report’.

From here, we will set our column headers across the top of the page (the old standards of Impressions, Clicks, CTR, CPC, Cost, Conversions, etc) and put our dates on the left hand side (starting in B2).

We can set the top (for today) using the TODAY() formula, subsequent days by subtracting 1 from the previous day (e.g. ‘=B3-1’, etc).

Now that we have the framework for the report, let’s get to the fun part of putting it together for a readable report.

Under the ‘Clicks’ heading, we’ll create a SUMIFS formula which will look at the AdWords Data Report and pull all the clicks for a given day. This may look something like this:

=SUMIFS(‘AdWords Data’!C:C,’AdWords Data’!$A:$A,$B2)

Where your click data is in column C, your Dates are in Column A, and the date you are reporting on (in the current, ‘Report’ sheet) is Cell B2.

Now, if you are looking for a holistic number combining Bing and Google to see your total daily activity, you can tack on another SUMIFS formula at the end (adding to the AdWords number using a +) which pulls the same data for Bing so that the formula looks like this:

=SUMIFS(‘AdWords Data’!C:C,’AdWords Data’!$A:$A,$B2) + SUMIFS(‘Bing Data’!C:C,’Bing Data’!$A:$A,$B2)

This can then be done for Impressions and Cost, and we can then easily calculate CTR, CPC, Conversion Rate, CPA (wrapping these in an IFERROR() formula to prevent any nasty #DIV/0 errors in the case of empty data).

When looking at conversions, depending on your set-up, it may be better to pull these directly from Analytics (as reporting lag pushing from GA to AdWords can sometimes lead to under-reporting if you are looking at same-day data).

To do this, it’s a pretty similar process to above, but depending on what channels you run, and how these are labelled in GA, you may need to add another parameter to ensure your ‘source / medium’ is correct.

Now that you’ve got the first row completed, just copy it down and like magic… 14 days worth of topline data, showing your engine metrics alongside your Google Analytics conversion data. This allows you to identify trends, monitor pacing, and create graphs to your heart’s content.

Since we’ve pulled the data at a campaign level, this can then be broken down to report even more granularly, allowing you to track different campaign types (like Brand vs Generic) or track performance of a specific campaign. You can also pull keyword and ad group level reports to drill down even further.

Upping the Automation

That’s all well and good for today, but when you log into this tomorrow, how do you get the most up to date data?

Easy. Go to your Add-Ons menu, and go to Supermetrics > Refresh All.

Of course, there is an even better option.

Under ‘Add-ons’, go to Supermetrics > Schedule Refresh & Emailing

From here, you can set the document to refresh at a specific time each day, week or month, or even to refresh every hour (for those obsessive data geeks like myself or an enthusiastic C-Suite!). In addition to this, you can opt to get emails every time this refresh is done

Multiple Channels, One Report

If, like us, you are running more than just PPC activity, such as display (via Doubleclick or AdRoll), social (via Facebook, Instagram, LinkedIn, Twitter, etc) or video (via Youtube); Supermetrics has an exhaustive list of pre-configured connectors to connect to these platforms too.

This allows you to get a holistic view across all channels in a single report and, with the automation triggers, spend less time pulling the data and more time analysing performance and optimising activity to drive better results.

Of course, Google Sheets will only take you so far. Sometimes you need something a little more visual and ‘polished’ or with more interactivity than a simple Google Sheet.

We briefly mentioned Google Data Studio earlier in this article to help visualise your data. Well, Supermetrics can also be used here to facilitate connections which aren’t native to the platform, like Bing Ads and other third-party platforms, though this requires a separate licence.

Is Supermetrics Right for You?

Ultimately, it’s your call as to whether this is suitable for your needs. Yes there will undoubtedly be an initial time investment and a learning curve but assuming this is complete, I like to use the answers to the below questions as part of a business case assessment:

1. How much time do you currently spend sourcing, aggregating and building reports for your data each month?
2. How much is this time worth to you or your company if most of it could be automated?

Once you know these numbers, you’ll know how much value Supermetrics could provide to you and whether it is worth the subscription and initial setup time investment.

Keep an eye out for future blog posts around Data Studio, as well as a post around using formulas to make sense of your data, including everyone’s favourite formula, SUMIFS.

The post How to Automate Reports with SuperMetrics & Google Sheets appeared first on Reef Digital Agency.