Gap Analysis in Excel – Discover (not provided) data in minutes

Tried to do an effective AdWords and Organic gap analysis with (not provided) taking up your valuable analytics real estate? Simple keyword rankings not enough? Then read on and bring life back into your organic performance reporting!

Running a heavily data driven agency means that any lack of clarity or transparency works against us when it comes to making business decisions. (not provided) is the epitome of vague so it doesn’t sit well with us as well.

For an agency like ours, from direct organic performance through to a meaningful and actionable Gap Analysis, we need real, hard data to make the right decisions.

The ever growing blanket over our organic eyes via encrypted search is one of the main reasons our business had to reconsider how we operate in regards to SEO since Google went all encrypto on us.

In this post I am going to take you through how we, as a business merge the worlds of Adwords super targeted query data and SEO performance down to the keyword level, even if those keywords are hidden from us.

The result is a wicked SEO gap analysis that you can use internally or send to your clients, showing them not only the keywords that are driving traffic to your site but also the opportunities they are missing out on.

What is the (not provided) keyword in Analytics?

Basically encrypted search means that organic keyword data is not sent across into Analytics, meaning that the majority of organic search from Google comes up as (not provided). Helpful right?

The (not provided) organic keyword data is no doubt the bane of the existence for the modern day SEO marketer or anyone wanting to understand how their organic search performs in terms of conversions and sales.

The encryption has had such an affect of web analytics that studies show around 80% of your data is encrypted and therefore has the (not provided) moniker slapped on it.

Here is the kicker; pay for the traffic via Adwords then the search query is there for everyone to see!

This very encryption was a determining factor we as a business moved away from SEO traditional services. We didn’t move away completely (although very rarely do I write about SEO) but more shifted our focus and how we define SEO.

The stance in our agency is now to consult and develop strategies for businesses in regards to their SEO but we do not take an active day to day hands on approach to SEO anymore.

The decision to pull back was two fold:

1. The first and main reason was that with the introduction of encrypted search, aka (not provided) keywords, meant that SEO became harder to quantify in terms of both correlated performance and ROI. You can still get close to the data in that you can narrow it down to what you think the keyword may be but the exact phrase falls in the grey area. To me this is incredibly frustrating.

2. The second was that the timeliness and affect of SEO updates/upgrades was not felt in the short term. Apart from the glaringly obvious, I do not like to make too many tweaks to a site or page at once. I like to test the affect of making a single amendment and how that change affects the overall performance of a campaign. The fact we need to wait days, weeks and sometimes months to determine the effect of optimisation always proved to be incredibly tedious. Couple this with encrypted search, we were ultimately forced us to revisit our management approach to SEO.

Lets put this into real life perspective; imagine you are working on your tennis game and you are trying to improve your serve. The coach asks you to do one thing and that is to throw the ball a little higher.

Now imagine that the results you get from your new ball toss will play out one month later. In addition to that someone removed the net just for your coaching sessions!

You kinda know where the net should be and one month later you kinda remember what you served like before. Naturally you start making decisions on gut instinct. You think you have improved but sometimes its a fine line, so you guess.

When it comes to data, gut instinct and guessing should not control your decision making. That’s where my frustration lies with SEO.

Naturally when you know what keywords are working well (paid search), you would want to rank and gain traffic from these terms organically (no brainer). The first step in this process is what is known as a Gap Analysis.

What is a Gap Analysis

A Gap Analysis takes your best performing search queries (not keywords) in Adwords and compares these with your organic queries.

We try to find matches to determine good SEO performance as well as ‘gap’ where you are missing out on potentially high performing traffic. A gap can be filled with either better on-site optimisation or via content.

That’s a gap analysis in a nut shell.

Knowing what we know about encrypted search can you see the problem here? We are trying to match up Adwords hard queries with organic keywords which most of the time, come in as (not provided).

So is it possible to do a proper Gap Analysis with insightful data anymore?

Absolutely yes. Let us show you this neat workaround.

Building your gap analysis in excel

This setup will required any version of excel (Windows or Mac), decent copy/paste skills and like all good SEOs and digital marketers like you and I, a keen eye and a bit of imagination and willingness to look at the story behind the data.

After you get the knack of this it you should be able to do this in under 15 minutes and you might find quicker and easier ways.

Step 1. Download your search AdWords query data

The first thing you want to do is grab your query data from AdWords. As I said earlier you don’t want to grab the keywords as this will not provide and accurate report on the best performing keywords. You want your queries.

There are 2 ways you can download this data and I will show you both ways.

Download queries from Google Analytics

My favourite way is to download your data from Google Analytics especially if you have revenue attributed to conversions.

On the left hand navigation menu go to Acquisitions -> Adwords > Search Queries:

Google Analytics download Adwords search query data

Select as many rows as possible to ensure you are downloading as much data as possible. Analytics only seems to allow you to download what is on screen (odd but true).

Downloading queries from Google Adwords

If you don’t have these queries in Analytics or you have not implemented your revenue tracking in Analytics, you will need to download this data in AdWords directly.

You can get these in the keywords tab and then select the search terms option (highlighted below). If you can’t see this option then you may still be seeing the older interface. If you are seeing the older interface then click Details -> View all details and it will take you to a list of all your search query data.

search term download adwords gap analysis

You will want to download this data in CSV or Excel format.

Step 2: Download Webmaster Tools/Search Console Queries

The next step is downloading your organic search queries from your Search Console, formerly known as Webmaster Tools. I’ll abbreviate this to WMT to save confusion. WMT = Search Console = Webmaster Tools. Ok that’s out of the way.

If you have linked your Google Analytics with WMT then you can point 1 in Analytics (Acquisition -> Search Engine Optimisation -> Queries section in your Analytics. If this is you then skip point 1 below and go straight to point 2.

This is how you get the data in WMT:

1. Log into WMT and scroll to Search Traffic -> Search Analytics.

2. Set yourself the desired date range. Make sure this date range matches the date range you used in your AdWords queries download.

3. The data you want to take is the following:

  • Clicks
  • Impressions
  • CTR
  • Position

Clicks is the obvious one as you want to work out how much traffic each search query drove to your website.

The other 3 are great at finding opportunities where you may have a bad CTR but good impressions and CTR which means you can look at how you amend your meta description, schema and even title to drive more volume. But that is a conversation for another time.

Search Analytics download from search console

Download the data from Webmaster tools into an Excel (or CSV) spreadsheet. It should be sorted by clicks (highest to lowest). If it isn’t then ensure that it is because you want the keywords that are driving the most traffic.


Step 3: Combine into an Excel Spreadsheet

Now what have you got? You’ve got a whole bunch of data in a spreadsheet that gives you some pretty good info across Adwords and organic, but doesn’t solve a problem. Yes it will tell you what queries are driving in traffic and their average position in Google but this is really just the start.

After you have this data you will want to push all the data a single excel document so you can start marrying the two channels and deciphering the organic search code.

When I got to this step a little while ago I truly got stuck. The issue I came across when looking for a solution was that the macro everyone was using for analysis was Fuzzy Lookup Extension for excel which I am sure is fantastic but unfortunately I use a Mac and it is only supported in Windows. Great.

This forced me to come up with my own solution to marry this data up and guess what? It’s actually pretty easy so stay with me here. Let the bells start ringing.

First place the WMT and Adwords query data in 2 separate sheets. No real reason I just like to have the raw data sitting somewhere in case I screw up half way through.. it’s been known to happen.

Now create an additional sheet where you combine the data together. You want to put the Adwords query data right at the top and the WMT organic queries directly underneath like the screenshot below:

gap analysis excel spreadsheet step 1

Step 4: Find your matches

Now you want to create an additional column between Columns A and B. This is assuming the column in which your queries are located is Column A. To do this go to the top of column B, right clicks and then select ‘Insert’.

insert column excel

You will now see that another Column B has been created. I know I am going slow here but I don’t want to assume everyone knows how to do this so I’ll try make it as simple as possible.

In the additional column go to the very first line (B1) and put in the following:


What this does is try to match up terms that have an exact match with other terms within the A column. Remember how we put in the WMT and Adwords Query data both in Column A? Well this little COUNTIF is trying to find matches.

Now copy and paste this script (drag down or copy/paste) until the end of both your WMT queries and Analytics data. You will end up with a series of 1s and 2s.

See below the example of the new column with the 1s and 2s.

adwords wmt excel lookup and match

What the 1s and 2s essentially means is that if the number is a 1, there is no match. If the number is a 2 then we have a match with both an Adwords query as well as an Organic query.

Step 5: Sort for alignment

Now you have your matches you still need to sort so you can correctly align the Adwords terms with those from the search console. This isn’t tricky at all and is really done with ultra basic excel skills.

First copy all your WMT data (including your newly created column) and then open up a new sheet in the document. Right click and select Paste Special > Values. This will ensure you don’t copy and paste the formula and only bring across the values.

Copy paste special values only excel

Once you have the values on a new sheet then you can name Column B ‘Matched’. You need to then sort this column from highest to lowest so you have all the values that are ‘matched’.

Now delete all those with a match value of 1, leaving you with only queries with a successful match (2).

remove non matched keywords excel
Remove non matched keyword Data

After you have deleted these unmatched queries. You then need to sort these by alphabetical order.

Now you need to do the same thing for the Adwords queries. As with the WMT data you want to sort the AdWords queries by match. But since this is a gap Analysis looking at organic queries and how they match up with AdWords, we don’t want to delete any of the rows. This is because we NEED to know what queries we are not showing for.

For now what you need to do is sort by the Matches, highest to lowest and then by alphabetical order.. so again you will see all the 2’s at the top and the queries should be in alphabetical order.

This is how I did the custom sort:

sort adwords query data

Step 6: Align!

The is the moment of truth. Go back to the WMT filtered sheet we created in the alignment phase. All you need to do now is paste this next to the AdWords query data and if you did the above steps properly, it should align perfectly.

completing the gap analysis

Now you are all aligned it is up to you how you view this data and what you deal with it. You have conversion rate and total conversion data from AdWords and you also have average position, impressions and clicks from the Search Console.

From here you choose to manipulate and mould as you wish. Find opportunities around keywords that don’t rank at all and those which have a low CTR.

For me I think the 2 main goals that can be identified from this gap analysis fall around traffic volume as well as revenue/conversions. I’ll explain this a little below:

1. Traffic volume – Finding queries that have proved to be popular on AdWords generally means there is an opportunity to drive traffic volume organically. Analysing the gap Analysis and looking for queries that have high impressions and low or non-existent organic clicks identifies traffic generation opportunities.

This is one big advantage of downloading search query data from AdWords directly. It will give you impression stats that give you insight into how much volume is available out there and what type of traffic return can be had.

2. Conversion and revenue – Sorting your AdWords data into a list of the best performing queries in terms of revenue and performance will allow you to see how these are performing organically.

Find opportunities with high converting keywords the same way you would as finding traffic generating keywords with a lesser emphasis on impressions. Find low or non-existent positioning, CTR and clicks and use your SEO knowledge to work out the best way to improve your organic performance for these terms.

No doubt I would prefer to see organic keyword data in Google Analytics but after doing this a few times you will soon realise it only takes several minutes to build it. You will still miss the old transparent keyword data but this little setup will dull some of that ache.