The following blog is not sponsored by Supermetrics.
I will, however, immediately sell myself out to sponsorship at any opportunity.
So, Supermetrics. What is it and how does it help me?
Supermetrics is a sweet Google Sheets add on/Data Studio source that can pull metrics in any combinations and sizes from a variety of marketing platforms. Once we’ve decided on what data we want to see and from where, it’s possible to build some pretty rad dashboards that are great for both reporting and analysis purposes. In this blog, I’ll be looking at how to set up a query from Google Ads and Google Analytics, and then how we can use that data to make a simple automated hourly performance dashboard.
There’s a number of different dashboards I have in my supermetrics arsenal that all serve a different purpose, and I’m sure you, the reader, will use supermetrics in a dashboard of your own design, you clever thing. But for now, here’s a couple of examples of what we can create:
Automated Daily Dashboards
A nice time saver, and a good one to share with any of your needy stakeholders. I’ll take you through how to build one of these that updates hourly further down.
Lead Feedback Dashboards
Quality more important than quantity? This is a good one to share when lead volume is low, but quality feedback is essential to how the account is managed.
Campaign/Label splits on a monthly/weekly/daily basis.
This will make your life so much easier if you’ve got a big account with several budget areas.
Keep an eye on all your accounts metrics and KPIs. This is a big’un, but it is possible, and a big help.
Enough Art Attack moments (here’s one i made earlier!), let’s get down to business. Here’s how to set up an Analytics query, which you get access to via the free version. Everything else comes from the paid version, which I’d recommend you get if you can justify it (again, not sponsored).
Get the add on, obviously.
Launch that bad boi.
Choose your data source and the account view.
Here, we’re looking at Impression in Analytics
Choose your date range
Are we looking to create a dashboard for the previous month’s performance? This year to date? If you’re wanting to split date ranges up, I’d recommend having a custom date range up until today, you can split them out using formulas later (eg. in my performance dashboard template, things are split out by day). Here, I want to see performance since the start of 2017.
Choose your data metrics
What do we want to see? This will change depending on what you’re analysing eg. landing page metrics like bounce rate and duration, types of goal, assisted conversions etc.
The metrics available will also change depending on the data source. Eg. Cost in a Google Ads query
In this example, I’m keeping it simple and just looking at all goals.
Next up: Our Dimensions
Again, this depends on what we’re analysing, and it’s important to note that we can layer things here. Device Performance? Performance by campaign/keyword? Performance by page? Do we want to split date ranges down? If so, add date dimensions in. That’s what I’m doing here. I’ll also be adding in source / medium dimensions too, so I can see where those tasty conversions are coming from.
Nearly there, lets filter.
I shouldn’t need to explain this, just choose what you want to filter by! Here, I only want to see conversions from cpc, because PPC is love, PPC is life.
Sweet, we’ve got all our data down. A couple of other important things to do first though, if you want this dashboard to be automated, which you do.
Apply a schedule for refreshing depending on date ranges your analysing.
I’m wanting my dashboard to update hourly.
If you dashboard is daily, then change the “end date” in the Supermetrics queries sheet (Initially hidden) to =today(). This will ensure the queries are up to date.
Awesome, we’ve got all our data. Now it’s up to your Google Sheets knowledge to get this to look presentable. I wouldn’t take you through all of this with no end product though, so here’s a template for the Daily Performance dashboard we’ve been building together, along with the exact queries I’ve been pulling.
Nb. Start different queries in different sheets and reference all of them in the dashboard!
In the AdWords sheet:
In the Analytics sheet:
Thanks for reading. It’s been a rollercoaster, guys, I’m just glad I was tall enough to ride.