How to create auto-updating PPC dashboards that give you a snapshot of performance

12-MINUTE READ · By Rashed Khan

As a PPC manager, I spend a lot of time knee deep in PPC marketing data. Whether it be identifying optimization areas or reporting back to the person or people I’m responsible to.

I’ve noticed that in a PPC account, there are often a number of key keywords that bring in the most traffic or conversions. These are the keywords that need to be monitored the closest. When I’m very busy, I’ve often wanted to just look at the areas that are affecting the PPC account the most.   

This is why I created a number of top 10 tables to make it easier to identify the main takeaways within an account. We all love top ten tables. They help us to see just the main areas that we need to focus on and reduce the noise.

Top ten dashboards can be a good way to get a quick snapshot on performance. They’re particularly beneficial for those times when you really don’t have time to look into a particular area in any great detail because you have a handful of other more pressing issues to look into. They can also be useful to show to a client or manager, who just wants to see the few areas that are having the biggest impact on the account.

Supermetrics can provide a data dump that you can easily schedule to update either daily, weekly or monthly. This ensures that your data is always up to date. We can combine this with the Query function in Google Sheets to manipulate the data and show only what we may find interesting. 

In this article we’ll show you how to create a set of top ten PPC tables you can use in your own marketing reporting.

What is the Query function?

Arguably the most powerful function in Google Sheets, the Query function allows you to use an SQL like language to manipulate and filter data. SQL (or Structured Query Language) is used in databases and is extremely powerful when it comes to data manipulation.  

The basic format of the Query function involves the range of cells where the raw data is stored and the actual query. The header section can be added as an optional extra section. In this blog, we won’t need the header section so we’re going to leave it blank.

QUERY(data,query,headers)

Some use cases

There are many uses of the top ten tables and once you start using them, you’ll find many uses for them. However, here are ten use cases showing how you could start using the top ten tables to improve the performance of your Google Ads account.

1. High conversions from one ad group – You notice a lot of conversions coming from one particular ad group. When you investigate, you notice that that there is room to be more aggressive with your bids so you increase aggression.

2. Spot high converting phrases coming from non exact match keywords – You notice that a lot of conversions are coming from a non-exact match ad group. When you investigate, you realize that most of the conversions are coming from a particular phrase. You decide to split this search term out so that you can bid more aggressively on it.

3. Spot performance trends – You notice that most of the ad groups in your top converting table are for a particular product group or keyword theme. For example, if you’re a clothing retailer, you may notice that all of your conversions are coming from ankle boots. You decide to expand your boots or ankle boots keywords to find other high converting keyword themes. 

4. Spot spikes in traffic  – You notice a very high percentage increase in impressions for a particular ad group. When you dig into the search query report for the particular ad group you notice that due to some news coverage, there has been a spike in a particular irrelevant search. You can block this irrelevant traffic out.

5. Identify long tail exact match keywords from non exact match keywords – You notice a very high percentage increase in conversions coming from an ad group with broad match keywords. When you investigate the search terms report, you notice that there has been a spike in a particular long tail search that you had not considered. You decide to add this long tail keyword into your account.  This ad group may not have appeared in your top ten conversions by volume table due to it not having as a high of a conversion volume as some of your other ad groups.

6. Quickly spot new landing page performance fluctuations  – After making some landing page changes, you notice that the ROI of a lot of the ad groups going to the amended landing pages are showing large percentage increases. You can report this back to your manager or client.

7. Identify areas to carry out search terms review on  – You see very high impressions coming from a particular ad group and decide to investigate. You may find that there has been a spike in poor quality traffic coming through. You can block this traffic out.

8. Identify high volume ad groups to segment out and improve performance – You spot an ad group that has a lot of impressions. You investigate and find a number of longer tail traffic to split out and give its own ad copy, landing page and bid.

9. Easily give top level snapshots – You get asked by your client or boss what the highest searched keywords were last week and need to quickly give a snapshot. You can just screenshot the top ten impressions table if you haven’t got time to give a more detailed answer to your client or boss.

10. Spot Seasonality trends – You notice that a particular set of keywords have seen a large increase in searches and realize that this could be down to seasonality. For example, you may notice that you have seen your jackets ad groups in the Impression percentage change tables very often in the last two weeks. You therefore decide that it maybe to time to start working on your ‘winter’ keywords.

Setting up the Supermetrics report

Before we can create the top ten tables, we need to set up Supermetrics to pull the data. 

To do this, create a new Google Sheets spreadsheet and select a tab where you want your Supermetrics data to be inserted.

I like to insert my Supermetrics data into a separate tab so that all of my raw data is in one place. Once you’ve done this, open up Supermetrics in your Google Sheet like you normally would.

Selecting the right lookback settings

Seeing as though we’re going to be looking at the data for the last week, under the ‘Select dates’ column, select ‘Last week (mon-sun)’. I look at the top ten tables every Monday which is why I set Supermetrics to pull the data from Monday to Sunday every Monday. If you want the data to be up to date every day, then you can choose the last seven days instead.

This blog will focus on creating weekly top ten tables, but you can easily make the top ten tables report on monthly data instead if you want. You can do this by changing the Supermetrics settings to look back the last month instead of the last week.

Adding the Percentage change data

To allow us to report on the highest WoW percentage changes, we need to add columns to show the percentage change in the week prior to last week. To do this, under ‘Compare to’, select ‘Previous period of same length’. This will add columns in the data comparing the data from two weeks ago. Under ‘Comparison Type’, select ‘% change’. This will present the comparison figures as a percentage.

Choosing your metrics

Next we need to select the metrics that we want Supermetrics to pull. We’re going to pull the below metrics but once you feel comfortable to do so, feel free to pull any other metrics that you may find useful to look at in your top ten tables.

• Impressions
• Conversions
• Return on ad spend (ROAS)

In the ‘Split by’ section, select adgroup name and column name under ‘Split by rows’. Make sure the number of rows that you fetch is going to be large enough to cover all of your adgroups.

If you’ve done everything correctly, you should see something like the below. But without the green background and of course with different figures. For the purpose of this blog, I’ve created my own figures, campaign and adgroup names. We can now start to create the tables.

Scheduling the report

We only want the report to refresh once a week on Monday so that we can get an idea on last week’s performance. In Supermetrics, set the report to refresh weekly on Monday. There can be a delay in conversions being filtered into Google Ads which is why I like to set my reports to refresh at around 7:00 am instead of 00:00 am.

Creating the tables

I like to create all of the tables that I want to see and then add the formulas in afterwards. To create the top ten tables, you can use a format similar to the one below.

The formula

To pull the top ten highest numbers in a column, we’re going to use the below Query function. The first section of the formula is the column where the raw data can be found. The next part states which cells are to be retrieved. The last section tells the formula to order by descending order and only to show the first ten results.

QUERY({All of the columns in your raw data},“SELECT {column with campaign}, {column with adgroup}, {column with data to be pulled} Order by {data to be pulled} Desc Limit 10″)

If you have pulled the exact same data as in this blog and inserted the data in cell A1, then to pull the highest impressions data, you will need the below formula:

QUERY(‘Data – Top ten’!A2:H,“SELECT A, B, C Order by C Desc Limit 10”)

Insert the formulas in the first column where you would like the data to be inserted and the Query formula will fill in all of the ten rows and columns automatically.

Do this for each of the top ten tables by volume and by percentage change that you want to create. An easy place to start is by having top ten tables for the ad groups with the highest number of  impressions, conversions and ROAS/CPA.

Extending the top ten tables

Once you’ve grasped the basic concept of pulling the raw data using Supermetrics and then sorting it using the Query function in Google Sheets, there are a lot of ways to extend the top ten tables. Here are some ideas on further projects that you could look into:

Bottom ten tables

If you can see the top ten performers for each metric, then it shouldn’t be too difficult to alter the formulas to look at the bottom ten adgroups for each metric.

This could be useful to see the adgroups producing the worst ROI for example. When looking at the bottom 10 tables, it would be beneficial to make sure that you’re surfacing the adgroups that have enough data. Otherwise you could get adgroups appearing that only have a handful of impressions for example.

To do this, you’ll need to change the query to pull data in ascending order. To make sure that you pull adgroups with enough data, you could add a WHERE clause that only includes adgroups where the adgroup has enough impressions, clicks or cost behind them.

QUERY({All of the columns in your raw data},“SELECT {column with campaign}, {column with adgroup}, {column with data to be pulled} Where {metric to ensure you have enough data}>{minimum figure} Order by {column to order by} ASC Limit 10″)

Top ten selling products

E-commerce PPC accounts that have Google Analytics can use Supermetrics to pull product sales. This means you can create top ten tables to see your best selling products.

QUERY({All of the columns in your raw data},“SELECT {column with Product SKU}, {column with Product Name}, {column with Transactions} Order by {Transactions column} Desc Limit 10″)

MoM tables

I’ve created WoW tables but you could easily update the Supermetrics settings to pull MoM figures, Year to date or even daily top ten figures if that’s what you needed.

Top 5 or top 20 tables

If a top ten table is too much data or not enough data then why not look at a top 5 or top 20 table? This can easily be done by changing the limit in the Query formula to whatever you want it to be. 

Desc Limit 5

Desc Limit 20

High spending non converting ad groups

How about extending the basic idea to provide more meaningful insights? You could create a table to show all non converting ad groups where cost is higher than a certain amount. You can use this information to investigate your non converting ad groups 

QUERY({All of the columns in your raw data},“SELECT {add relevant columns} where {Spend column} > {spend threshold} and {conversions column} < 1″)

Low impression share ad groups achieving target

You could take a look at high ROAS or low CPA ad groups with low impression share. This gives a quick snapshot on some ad groups that you can easily increase aggression on to get a good increase in profitable traffic

QUERY({All of the columns in your raw data},“SELECT {add relevant columns} where {column to filter such as ROAS or CPA} > {ROAS or CPA threshold} and {Impression Share column} < {impression share threshold}”)

Geographic performance

You could extend this idea to looking at geographic search query performance. City and search query data is available in Supermetrics so this data will need to be added to your raw data section.

Top ten converting non exact match search terms

If you have keywords in your account that are not Exact match, then how about tracking your best converting traffic coming through non exact match keywords? You can add in your best converting traffic into its own ad group as exact match and give it a more aggressive bid with a more targeted ad copy.

There are a few ways to set up something like this depending on your requirements. You could start off by pulling the search query, keyword, match search term and conversions. You could then filter out all searches where the match type contains ‘exact’.

Looking back the seven days may not be enough data for this report and depending on your account, you may want to look back the last fourteen or thirty days instead.

To pull the relevant data, you could use a Query function that pulls the top converting search terms:

QUERY({All of the columns in your raw data},“SELECT {column with search term}, {column with keyword}, {column with conversions} Order by {column with conversions} Desc Limit 10″)

Monitor more metrics

Each account is different and the kind of metrics that you may want to monitor would be unique to your PPC account. If you want to see the top ten highest spending ad groups or the highest cost per click ad groups then you can create tables for these as well.

Start creating snapshots

I really think Google’s Query function is an extremely powerful and underutilized feature. While it may seem daunting at first, once you’re comfortable with the basics of writing a Query function, it opens up the door to a lot of interesting ways to manipulate data. 

As marketers, we’re juggling multiple things at once. The top ten tables can be used as a quick way to get a snapshot of some of the key areas that you need to look at. They can be ideal for those times when just a snapshot will do. 

With a little understanding of the Query function combined with automatic data retrieval by Supermetrics, there are a lot of snapshot type of reports that can be built. These can help drive optimization decisions and also make reporting more concise, easier to digest and immediately actionable.

Start your 14-day free trial of Supermetrics and build your own top ten PPC dashboards.

About Rashed Khan

Rashed is a Google Ads, Microsoft Advertising and Google Analytics certified PPC expert with over 8 years of experience working in digital marketing. Rashed is currently the PPC Director of a Leeds based PPC agency in the United Kingdom called Pepper PPC agency. Find out more about Rashed by taking a look at his Linkedin.