8-MINUTE READ · By Joona Tuunanen and Anna Shutko

Nowadays many marketers are considering what they should invest in more, organic search vs paid search. And it’s understandable, since in order to stay ahead of the competition and reach your target audience, you have to deliver the best content, stay on top of your SEO game, and constantly improve your PPC campaigns all while having a limited budget.

We understand. And instead of having to choose between paid and organic we would like to suggest you embrace the genius of the “and” by combining your organic and paid data together to identify opportunities in both areas.

In order to achieve this, you should create reports that bring all the information you need from each data source and combine it in a meaningful way. And in addition, you need to build the reports fast, while having the fresh data easily available.

Sounds quite challenging, huh?

But don’t worry – Supermetrics and OIKIO Digital Performance Agency come to the rescue! Joona from OIKIO has kindly shared 2 free plug-and-play Google Sheets-based reporting templates together with the relevant use cases their team has created. After reading the article and trying out these Supermetrics-powered templates you should be well-equipped with the ideas on how you can improve both paid and organic performance.

By default, these templates use data from Google Search Console, Google Analytics and Google Ads.

A few considerations from the creators before you can jump into using these templates. Firstly, you have to keep in mind that Google Search Console data should be taken more as directional rather than the absolute truth: it is quite volatile by nature, especially outside of the first page of search results.

In case you are not using Google Ads as a data source for the paid search data in the second template, it can be replaced with other platforms (Verizon Media native ads or Bing ads, for example).

Ok, let’s dive in!

 

Template #1: Most important SEO landing pages and the phrases bringing traffic to those pages

There is no doubt that keyword analysis and page traffic data analysis go hand in hand – it is vital for any content marketing or SEO specialist to understand which top organic search phrases have brought traffic to the website pages.

In order to build this report manually you would either have to install and configure applications which show the SEO data in Google Analytics or manually download the data from each platform and spend your time stitching it together to uncover relevant insights.

Or you can skip the “heavy lifting” part with a Supermetrics-powered template from OIKIO. This template gives something valuable indeed: a combined view of the urls with most traffic and the phrases that bring organic traffic to them. This is something we have not seen ever since Google has removed the search phrases data from Google Analytics.

OIKIO has found it extremely useful in cases where the client wants to invest in some new content or improve the current content.

 

This template helps you answer the following questions:

  1. Which are the landing pages that bring the most organic traffic and conversions?
  2. What are the top referring search phrases for each url?

What’s great about this automated dashboard is if you put effort into improving on-page SEO for some landing pages or build some more links to them, you’ll see here how those phrases start to appear in the report; such a rewarding sight for content editors and writers 🙂

Besides that, you’ll also see how the popularity of landing pages changes with time and how trends emerge from the data. So basically this template can act as a reminder of what your customers are searching for during this time of the year.

Learn how to activate this template in the end of this post.

 

Template #2: A rolling 30-day gap analysis between Google Ads and SEO visibility

There is no doubt that SEO and paid advertising work better in tandem – a Google study has revealed that the CTR of ads with associated organic search results are higher. In addition, one has to keep in mind that the CTR of different ad types is affected by organic traffic differently.

And I have mentioned only the general trends. Add to this the fact that the relationship of the paid ads CTR and the organic traffic is different, depending on your industry, the size of the company and the nature of the campaign. So in order to stay on top of the game you have to closely monitor both your paid ads and organic performance – and this is where the “30-day gap analysis” template comes in handy.

This template will provide you with some immediately actionable data that can either save you money or give you the search phrases that you should prioritise in your SEO work or in your Google Ads paid search campaigns.

The concept is simple: take the top matched search phrases report from Google Ads and match it up with the SEO search phrases provided by Google Search Console along with relevant data.

 

Comparing this data helps you answer the following questions very quickly:

  • What are the best converting search phrases through Google Ads, for which we don’t currently get enough clicks from organic search?
  • Do we get significant organic traffic for some phrases that we don’t target at all with our PPC?
  • What’s the ratio of clicks between PPC/SEO for any given search phrase?

The first question in the above list is one of OIKIO’s favorites. If you know that the term converts through PPC, but you’re not ranking for it in organic search, wouldn’t it make a lot of sense to prioritise your SEO efforts around that term because you have data to prove that it converts?

It’s trivial to build a business case around the work if you have the data to support your recommendations.

If you combine the Google Ads conversion data with expected organic CTR for different positions, which you can now get easily straight from Supermetrics, you can build projections what would be the yearly impact if you could move up in organic search results. That allows you to talk about money and not only about rankings or clicks.

The template includes phrases with branded terms straight out of the box, but it is more convenient to filter those out while setting up the Supermetrics connector. Eliminating them keeps the report cleaner as you’ll most likely rank for your brand terms in any case.

If you would like to change a 30-day rolling gap to, say, a 90-day gap all you need to do is to head over to Supermetrics Queries tab and adjust the number of days in the “Date range type” column accordingly. Refresh the queries after that.

Pro tip: You can build a template with other paid search data source platforms instead of Google Ads (for example, if you are using Bing Ads, you can alter the template to connect it as a data source.)

 

How to get the templates

Both of the templates mentioned in the blog post can be found from Supermetrics Google Sheets – based template gallery. In order to use the templates follow these simple steps:

  1. Get the Supermetrics add-on for Google Sheets if you don’t have it already.
  2. Open the Supermetrics add-on in Google Sheets.
  3. Open the template gallery.
  4. You can find both OIKIO templates under the “SEO” template category.
  5. Click “Add template” button on the template you would like to use and proceed to the account selection according to the instructions.
  6. Follow the further instructions from the “Instructions” tab to finalize the template’s setup.

 

Closing thoughts

Supermetrics For Google Sheets is amazing not only at automating reporting and eliminating routine work, but also at bringing in data from multiple sources effortlessly. When you combine the awesomeness of Supermetrics connectors with the flexibility of Google Sheets, you basically have unlimited options at your fingertips.

These templates are just the starting point – you can easily customize them to create the report you need or build your own report from scratch by pulling in any custom data you want.

You can connect with Joona from OIKIO on Twitter here.

Get started with Supermetrics.

#1 reporting automation tool for PPC, SEO, social and web analytics.
 Free trial with full features. No credit card required.

Start Free Trial

Share This