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:
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!
Some opinions expressed in this article may be those of a guest author and not necessarily Search Engine Land. Staff authors are listed here.
SMX Advanced is the only conference designed exclusively for experienced paid search advertisers and SEOs. You’ll participate in experts-only sessions and network with fellow internet marketing thought leaders. Check out the tactic-packed agenda!
About The Author
(Some images used under license from Shutterstock.com.)
Article source: http://searchengineland.com/replacing-googles-not-provided-seo-gold-220045