In January Google announced that numbers will no longer be rounded in Google Webmaster Tool Search Query reports. With that announcement these reports became 20 to 30 percent more accurate.
Not even available from the API, the Top Pages report is the only place you can find page-level search query data. Does this make it the most valuable report around?
This article walks through how to get keyword to landing page data by using the Top Pages report as a template. Then consolidating analytics conversions and trending over time in a very basic way.
Tools used:
- Google Webmaster Tools
- Google Analytics
- Excel
Capture Top Pages Report Data
Google Webmaster Tools Search Query reports are the only way to can get decently comprehensive keyword data (we have to take what we can get from Google).
- Set the dates to the first week of February and expand page entries (figure 2 below) to reveal all keywords.
Tips: Toggling from the bottom up is quicker and Noah Haibach at Lunametrics has a niceJavaScript workaround for doing all of this automatically.
- Select, copy, then paste all data into Excel.
Note: Excel took awhile to think about wanting to paste.
After pasting, format to remove all links, insert a column to the left of Impressions, add new column headers, and save as a new .xlsx file.
Note: If pages contain a trailing space be sure to remove otherwise they won't match up when we use VLOOKUP later.
- Use the same process to create a similar tab for week 2 of February.
We now have the template to begin consolidating data from other sources, specifically Google Webmaster Tool Search Query Rankings and analytics Visits and Conversions.
Consolidate Data
Using Excel's VLOOKUP function we're going to begin to add data from the Google Webmaster Tool Search Query report and Google Analytics (see link if you don't know how to use VLOOKUP, also quick run through here).
Tip: Keep the downloads for reference later.
- Pull Average Rank from Google Webmaster Tool Search Query report.
Make sure you have the date set properly (this is set for piping in data to the week 1 tab).
Change 25 rows to 10, then change the grid.s parameter in the URL to the total rows given, in the case 2453.
Hit enter and then click "Download this table". Open the file so that you have it and your report .xlsx in accessible windows. We're going to use this file to pull in Average Position data per keyword.
In the week 1 report tab (make sure you pulled week 1 GWT data), enter =vlookup and arrow over to the cell you want to use as the lookup_value, then enter a comma.
In the Google Webmaster Tool Search Query download, highlight the data you want to use for thetable_array and add a comma. We want column H (8th column from left) values to be returned, add an 8, a comma, and finally a zero then hit enter.
The full formula looked like this:
=VLOOKUP(B3,'[www-yoursite-com_20140218T230012Z_TopSearchQueries_20140201-20140207.csv]www-yoursite-com_20140218T23'!$A$2:$H$2454,8,0)
- lookup_value - B3
- table_array - '[www-yoursite-com_20140218T230012Z_TopSearchQueries_20140201-20140207.csv]www-yoursite-com_20140218T23'!$A$2:$H$2454
- col_index_num - 8
- [range_lookup] - 0
Drag the column to all applicable cells, making sure not to override the Average rank that already exists for pages. It is normal that #N/A will show up with queries that have less clicks. Search and replace all instances with 1, since keywords can't be registered in Google's system without a click.
Repeat this process for week 2.
- Pull Organic Conversion data for URLs from Google Analytics.
Ensuring the proper dates are used, navigate to Customization (upper navigation) -> Create a New Custom Report -> Fill it out so it looks like the image below. Goal Starts can be any conversion data you want to include.
Change Show rows (bottom right) to 25 then find the explorer-table.rowCount parameter in the URL, substitute the number after %3D with the number of rows in the GA result set. Hit enter thenExport -> CSV.
Use the VLOOKUP process described previously to add conversions to both the week 1 and week 2 tab.
The final product should be two tabs with Google Webmaster Tool Top Page report used as a framework, combined with analytics visits and conversion data. Next step, taking this information and creating Ultimate Google Webmaster Tool Dashboard.
The Ultimate Google Webmaster Tool Dashboard
Note: Most / all of you are probably better than me at putting together reports and visuals.
The only thing ultimate about this solution is that is that it's a way to visualize correlation between URL conversion rate and keyword clicks and impressions.
What we're looking at is only the top 25 URLs, expanding this process to include more URLs is simple as noted earlier. This here represents about 60 percent of the site's Google organic search traffic.
Highlighted in green is the homepage of the site. We can see that our homepage was presented in search results 28.92 percent less, but our CTR is up almost 40 percent and our conversions up 33.05 percent.
Tip: Percent change formula is: =(new # - old #)/old #
Looking at our week tabs we can see it's because Google listed our page in many new searches that were not made in week 1. So while our page was not presented in search results as much, it was listed in 36 searches not made the previous week. By pulling in what we have in keyword level conversion data from GA we can really start to narrow down which keywords were responsible for this conversion increase begin using it to form new strategies.
Conclusion
Unfortunately since the introduction of SSL/"(not provided)" we are no longer to directly tie conversion data to a search someone used to enter our site. We can now only correlate.
This article merely scratches the surface of what could and should be done with this data. Enterprise level tools are doing what is shown and more on a massive scale. The key is finding ways to trend over time.
0 comments:
Post a Comment