As a multi-channel Agency, at Croud we spend a whole lot of time thinking about how PPC and SEO can run alongside each other in a harmonious way. In more traditional agency models, PPC and SEO can often end up working in siloes, leading to a lack of coherent strategy across both departments and no one looking at the data that might enable the right decisions.
This can lead to inefficiency in marketing spend allocation. For example, you might be bidding on search terms where you have no competition in the paid search space and where you currently hold pole position in organic search.
In this scenario, you’re essentially paying for visitors who might otherwise have clicked on the organic listing. And vice versa, time and effort might be wasted on trying to rank organically for certain search terms which the PPC team has already identified as low converters.
This article won’t be addressing the obvious issues such as getting everyone into the same room. I want to focus on the data. After all, digital marketing is a fast-moving industry. Staying on top of everything can be an extraordinary challenge. Especially with the vast amount of data that is available, finding yourself in a state of analysis paralysis is not uncommon.
Unless the data is readily available or even better if the relevant highlights are sent to you, it can be considered too time-consuming to look at the data. This is why at Croud we’re big fans of Supermetrics and the Google Spreadsheet. A combination of the two can greatly speed up the whole process and provide the data you need on a daily basis.
Supermetrics allows you to pull data from various data sources into a Google Spreadsheet. You can even schedule it to pull the newest data for you, as you need it. Within the spreadsheet, you can then write formulas and scripts to process the data, analyse it and send bits of insight automatically to certain stakeholders or the whole team. To accomplish this, I will explain what queries you have to set up in Supermetrics and how the data can be pulled together into one single report.
If you would also like to download this step by step guide for later use, you can do so HERE.
Getting Started
Step one is to enable your Supermetrics add-on for Google Drive.
Once enabled, open any Google Spreadsheet and click on “Add-ons” in the top navigation. Go to “Supermetrics” and select “Launch sidebar”.
Supermetrics comes with a free 30-day trial. You will need to be on the free trial in order to complete all the following steps in this tutorial.
When you look at the Supermetrics bar to the right, you can see all the tools you can plug into with Supermetrics under “Data Source”:
In the free version, you will only have access to Google Analytics and only be able to pull 50 rows of data at a time.
SEMRUSH – Keyword Data
If you have a SEMRush account, you will first want to connect it in order to pull a number of keyword-related metrics. Navigate to: https://www.semrush.com/api-use/. You will find your API key in there if you log into your account.
Within Supermetrics you need to select SEMRush under “Data Source”.
Select “+Add new account” to paste in your API Key. You should now be able to configure your query (the specifics around the data you want to pull into the Spreadsheet).
Under “Query Type” you want to select “Keyword Overview (one database)”. In the text box for “Phrases”, you can copy and paste in all the keywords you want to keep an eye on. Set “SEMRush database” to the location you want to track and change the “# of rows per item” to whatever is suitable (we recommend setting it to the highest possible).
In Search Fields, you can specify what metrics you would like to see for each of your keywords. This is very similar to dimensions in Google Analytics. For now, we recommend you select “Keywords”, “Search Volume”, “CPC” and “Competition”. Click on “Get Data to Table” and Supermetrics will fetch the data and start feeding it into your spreadsheet, starting from the cell you have selected.
Therefore, always check which cell you have selected before you run a query, as you don’t want one data set to override parts of another data set.
For our query, the data should look similar to this:
Google Search Console – SEO Keyword Performance Data
To connect to Google Search Console, you will need to ensure that you have the account and password for the domain you want to pull data for. Under “Select sites”, select the domain. For “Select dates” I recommend the “Last X days” option, which you can set to 30 days. Remember that Search Console only stores data for a maximum of 90 days.
The metrics you want to select should be Impressions, Clicks and Average Position. You then want to “Split by” Search query. Set “# of rows to fetch” to as high as possible and “Sort rows” by clicks; with “Sort direction” set to Descending. In “Filter” you need to select “Search type” to be equal to “Web”.
This is important, otherwise, your data will contain Image and Video search data, which is not very useful.
Google AdWords – PPC Keyword Performance Data
For Adwords, you will need to have the login for your AdWords account at hand. Once it is connected to Supermetrics, you have to select the account and choose the time-range. You probably want to go for something similar to what you have set previously for Search Console (last 30 days).
As metrics, you want to select Impressions, Clicks, Average position, Conversions, Cost, and Conversion Value. Finally, you want to split the data by “Keyword text”.
Google Analytics – Organic Landing Page Data
Connect Google Analytics via a login that has access to the analytics account you want to pull data from. Continue selecting the account and time-range as per the previous queries. Under metrics, you ‘ll want to select Sessions, Bounces, Conversions/ Transactions and Conversion/Transaction Value.
Then, split by Landing Page path and set a filter as Medium equals “organic”.
This will pull organic landing pages with the amount of traffic and conversions they have received via organic.
Data Crunching and Analysis
Now that we’ve pulled all the data we will need, we should combine everything into a single comprehensive table. You want to start with a column that lists the most important keywords (the same keywords you put into SEMRush). For each keyword, you also want to specify which landing page on your site is the most relevant one. Make sure that you type the landing pages in a format that is compatible with your Google Analytics data.
Next, you want to add columns to feed in the data you have pulled from Supermetrics. This could look like this:
To feed in the actual data you will need to work with the vlookup formula: http://chandoo.org/excel-formulas/vlookup.shtml.
In the majority of cases, you will need to use the keywords in the first column. For the organic landing page data from Google Analytics, you need to use the Target Landing Page column as the key. Some metrics, such as Click-through-rates and Conversion-rates will need to be calculated.
Once all the data is in the table you can find opportunities to adjust PPC and SEO activity. For example, your client may be urging you to spend your marketing budget more efficiently. You may spot that you are spending a large chunk of your PPC budget on a keyword that already has a great CTR in organic.
On top of that, the data may also imply that not only are CPAs for that keyword are extremely high, but PPC CTRs may also be much lower than for other keywords. In such a case you may consider shifting your PPC budget away from that keyword, as SEO is already catching sufficient levels of traffic.
You can also spot opportunities for Organic search optimisation, by looking at the conversion rates by keyword level. If a certain keyword is converting very well within paid search but is barely receiving any traffic in organic search, you want to make sure that this is being addressed.
Scheduling Reports
You might also want to automate this report so that you can see the freshest data on a weekly basis. In the Spreadsheet, you can set this up under the tab Add-ons > Supermetrics > Schedule Refresh & Emailing:
In the example above we’ve have set up a weekly refresh only (no email) for every Monday at 5 AM (well before everyone arrives in the office).
Set up customized alerts
Depending on your PPC and SEO strategy, you can set up a range of custom alerts to give you greater agility. Perhaps, as soon as the competition level of a particular keyword decreases to a certain threshold you want to inform your teams. This requires some scripting, which is unfortunately out of the scope of this guide. However, Google has provided a tutorial describing how to send emails from a Google Spreadsheet. It also describes how cells within a spreadsheet can be accessed. Take a look here:
https://developers.google.com/apps-script/articles/sending_emails
Supercharging Supermetrics A step-by-step Guide to combining your PPC and SEO data into one report
