Note: By submitting this form, you agree to Third Door Media's terms. We respect your privacy.

How To Replace Google’s (Not Provided) Data To Strike SEO Gold

Google Analytics may not be providing keyword referral data anymore, but Chris Liversidge has developed a process for matching keyword queries from Google Webmaster Tools to their probable landing page URLs.

Chris Liversidge on May 5, 2015 at 1:15 pm

While it is certainly useful to review organic keyword data in Google Webmaster Tools, it’s not a patch on the level of insightful detail once achievable in Google Analytics back in the days before Google’s move to 100% secure search (a.k.a. “not provided”). I’ve written about the importance of replacing this data before, but given the feedback I’ve received, it’s clear that more detail is in demand.

Mapping traffic behavior for particular keyword phrases though from landing page to exit, or through the conversion funnel, allows for much more powerful insights to be derived. Care for an example? OK.

Let’s say you’re a retailer trying to work out why your purchases for a particular product have fallen off a cliff over the last month. You can see that that all traffic sources are still at the same level. Filtering by channel shows that organic traffic is suddenly bouncing 200% more than in the previous month and is exiting the funnel to contribute zero revenue. What’s changed?

Without keyphrase data, you’d have to do a fair bit of digging to get an answer. With it, you can see the problem straight away. Don’t believe me? Here’s an example with annotated insights:

[CLICK TO ENLARGE] The Google Zipper in action!

In this case, we have two URLs in exactly our situation: $0 revenue recorded (even for brand search terms with tens of thousands of searches each month) with similar figures for total organic traffic hitting the landing page (remember, this will include all long-tail terms also landing on the page).

So why the catastrophic failure to convert? In one instance, no properly configured call-to-action remains on the page after a “content refresh” undertaken by a different department. (In fact, in this particular case, all the content had been replaced with an image of the content. I don’t even know where to begin on that one!)

And, as a sort of bonus, we can also easily see the second basket case URL to take care of, where a “Help & Support” page is returning for a highly commercial search term. The support page has no main navigation, so it’s impossible to get to the shop section — thus, no revenue.

These two fixes are pretty quick to do and would deliver c £1,200 per month from just over 20,000 monthly visits at an assumed 6% conversion rate, the average for this retailer. (The number would likely be higher in actuality, as one of the keyphrases is a brand term, and those average a c12% conversion rate.) Assuming an hour or two to make the fix (generous!), that’s a healthy ROI for our time!

So I think we can all agree it would be great to get keyphrase data from Webmaster Tools matched up to data from Google Analytics. How do we do that? Welcome to the Google Zipper!

The Google Zipper & Fuzzy Lookup

The key to matching these data sets is a firm statistical bond — our zipper — matching the keyphrase to the landing page.

To do that matching, we need to do two things:

Extract keyphrases from the Google Analytics data to match Google Webmaster Tools data to. In this case, we are going to pull the keywords from the URL string (more on how to do this below).
Download and install the Fuzzy Lookup extension for Excel. (Yep, this is all in Excel, folks! Unfortunately for you Mac users, it’s Windows only.)

Google Webmaster Tools gives us three full months of keyword click and impression data in its Search Queries report. To get this data, navigate to Search Traffic > Search Queries, set the date range to go as far back as possible (three months), and click on the button that says “Download this table” to export to a CSV.

In Google Analytics, set your date range to the same time period, then apply the “Organic Traffic” segment. (Make sure to remove the “All Sessions” segment.) Navigate to Behavior > Site Content > Landing Pages, set rows to 5000, and export the data to Excel (XLSX). This data should include transaction and revenue figures if you use e-commerce tracking. If not, you’ll need to include goal data and work your revenue figure from that (Avg. inquiry value, etc).

We are now going to extract keywords from your website URLs. To do this, open your Google Analytics data in Excel. On one tab, you should find data with headings such as “Landing Page,” “Sessions,” etc. Add a new column somewhere with the heading “Extracted Terms.”

We’re going to break up the URL into individual words by SUBSTITUTING special characters (like ampersands, forward slashes, hyphens, etc.) with spaces. You should also ditch known URL quirks like .html or .php, as well folder names that are not directly related to important keyphrases for growth SEO strategies (like “login,” “cart,” “welcome,” etc.). Needless to say, you need to be using URLs with actual words in them (which you should be, anyway).

Your formula might look a little like this. (I’ve normalised my URLs with LOWER and TRIM as well, though this isn’t strictly necessary.):

=TRIM(LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IFERROR(SEARCH(“search”,A2),A2),”www.”,” “),”.com”,” “),”.co.uk”,” “),”/en/”,” “),”_”,” “),”/”,” “),”-“,” “),”?”,” “),”.”,” “),”+”,” “),”=”,” “),”&”,” “),” do”,” “),”welcome”,” “),”my “,” “),”account”,” “),”basket”,” “),”contact”,” “),” us”,” “),”logout”,” “),”login”,” “),”index”,” “),”html”,” “),”  “,” “),”  “,” “),”  “,” “),”  “,” “),”  “,” “)))

Yep, that’s a lot of substitutions!

Note: There are almost certainly better, cleaner ways to write this part, and it’ll need to be adjusted for your local language/location/code setup. But hey, the above does the trick nicely and is pretty simple to adjust!

Put this formula into the “Extracted Terms” column to extract the keyphrases from the site URLs. Turn this data into a table (select all the data and hit CTRL + T), then name the table “ga” (this can be done in the Design tab under “Table Tools”).

Open up the CSV you exported from Google Webmaster Tools. Convert that data into a table as well, name that table “gwmt,” and copy and paste this table so that it’s in the same workbook as your Google Analytics table (“ga”).

Select a blank cell in a new sheet and fire up Fuzzy Lookup. Select “ga” as your Left Table and “gwmt” as your Right Table. You want to join “Extracted Terms” from the Google Analytics table and “Query” from the Google Webmaster Tools table, so select those and click the button between them. For outputs, select “FuzzyLookup.Similarity” and “gwmt.Query.”

Depending on how many terms you have, go grab a coffee while Fuzzy Lookup does its thing. When it’s finished, you’ll have your terms and probabilities listed out.

I then use another tab to VLOOKUP against those terms and the two tables to bring everything together and produce the output shown in the screenshot at the beginning of this article.

To update the tool in future, just refresh your data and re-run Fuzzy. You’re done!

Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.

About The Author

Chris Liversidge

Chris Liversidge has over twelve years web development experience & is the founder of QueryClick Search Marketing, a UK agency specialising in SEO, PPC and Conversion Rate Optimisation strategies that deliver industry-leading ROI.