How To Use Excel To Easily Spot SERP CTR Trends

Data fiends and worshippers at the temple of clear data visualisation will have suppressed a dry chuckle when reading Google’s recent announcement on improved detail for Search Query reports in their webmaster tools interface.

The reason for our data geekery? Google’s ‘Impressions Vs. Clicks’ before and after comparison illustrating the impact of their change.

Google's Before Vs After Charts

Spot the difference! Google’s charts showing before & after their data update.

You would be forgiven for taking a few seconds or more trying to discern the minor change in visual impact in the two charts above.

It is a shame, as the underlying data tweak is a welcome, and important, change in policy from Google: increased accuracy in their impressions and click data allows for much more accurate reports highlighting actionable outcomes that can have a significant impact on your SEO campaign’s bottom line.

The failure of these charts to visualise this important data to allow a useful insight (for example, try to pick out a firm outcome just looking at the charts above. Anyone, Bueller, anyone?) is a critical failure in Webmaster Tools.

Fortunately, however, Excel can help us easily create some actionable reports using this new data accuracy in super-sharp time.

Solving Poor Trend Charts: Double Vertical Axis FTW

The killer issue with Google’s existing charting is inherent to their data: impressions will always dwarf click data. The solution: chart clicks on a separate axis.

Exporting data is straightforward, although Google’s API for Webmaster Data does not allow for easy exporting of impressions and click data yet. (Google: this is my Christmas wish for 2014, please!) As a result of this limitation, we will only be able to work with the top 500 rows of data for any segment we apply. This should still be enough for some trend insights.

Set your report to 500 rows, apply desired filters (for example, remove brand terms, select US only, etc.), and then “Download Chart Data” in CSV.

Google Webmaster Tools Impressions Vs Clicks Export

Filter your data to exclude brand or target a specific location for more actionable insights.

At this stage, we can quickly graph out our data to better see any top-level trend by throwing clicks onto a secondary axis.

Open the CSV file in Excel and create a 2-D line chart with the data. In Excel’s ribbon bar, select “Layout” from “Chart Tools.” (In 2013, it will be “Format” rather than “Layout.”)  In the drop down on the far left, select Series “Clicks” and then click the Format Selection button underneath. For your series options, select “Plot Series” on “Secondary Axis.”

Charting Impressions Vs. Clicks in Excel

Charting Impressions Vs. Clicks in Excel

You may also wish to apply formatting to the date range information to make it more easily understandable for you and your team. Below, I’ve used some custom formatting to show dates in UK format with my preferred layout. (BTW, you’ll also need to cope with the export data using US date layouts if you’re changing them to UK. I’ve set up some data resorting to solve that on import.)

Formatting Date Ranges for Clarity

Formatting Date Ranges for Clarity

We’re then left with some clear trend data like the below, which allows me to see easily that between the 14th and the 20th of December, our SERP CTR was much greater than normal.

Clearly Visualised Impression Vs. Click Data

Clearly Visualised Impression Vs. Click Data

Digging into the individual term data will show me which terms overperformed for that period. I can also easily see that there was better performance at the start of the data in this snapshot, so re-snapshotting the data for an earlier date range to compare against this trend is another quick outcome. Using Google’s WMT interface, I can filter by date and sort by CTR to find the key contributors to this performance.

Incidentally, I also add axis titles and a concise, clear title at this stage to ensure the data is clear and stands alone in the chart.

My outcomes: by knowing the terms contributing to increased SERP CTR, I can identify the organic snippet shown and pull out whatever call to action was used and was successful during that period. This can be used to improve the SERP CTR of my other listings for similar pages.

Bingo: I can now freshen up underperforming snippets with a proven conversion CTA for my business.

Micro Reports For Key Performers

Since we now have more accurate data from Google, we can also dig into the other data export available from Webmaster Tools and set up a few “Canary” micro reports to quickly spot SERP risers and fallers with good precision and high actionability.

To pull the data, set your rows to 500 again, apply filtering as before, then click “Download This Table” in CSV. To import the UTF-8 characters Google regularly encodes in this data to Excel, you will likely need to import as text, setting the encoding to UTF-8 and identifying the commas as data delimiters.

From there, you can apply a pivot table to the data, which will allow you to dynamically play with your charting data to either further segment the data by applying text filters, or simply to adjust pull-though term data for associated visualisations. Create your pivot table in a new tab, and for future reports you can simply replace the ‘Data’ tab and all your pivot segmentation will be applied with a quick refresh. (Hit CTRL + Alt + F5 to refresh all pivots in an Excel workbook.)

One of the most useful pivot architectures here is setting Avg. position as a report filter — then using the Query as a label, and then impressions and clicks as values. (usually I grab the “Average” value here, but Max or Min may also be appropriate, depending on the final report you’re building.) Then we can segment our report into top three ranking, top 10, or whatever ranking blend we choose.

This is particularly useful to find weaknesses within high value top ranking terms.

I like to also break out report sets where there has been at least one click for reports focused on already performing rankings. For example, here’s a segment incorporating: location, non-brand, within a defined date range, minimum of 100 impressions, for all ranking positions, sorted by average impression change MoM.

Canary Report

Canary Report

Though small, this range shows me terms that should be driving visits, but aren’t — which are improving and which are failing. Pulling out the worst performers will give me content refresh options that will improve my ranking position and deliver a better SERP CTR to boot.

As you can see, there’s a world of useful reports waiting at the touch of a button to speed your content strategy onto SEO success in 2014: get your copies of Excel at the ready and get mining.

Opinions expressed in the article are those of the guest author and not necessarily Search Engine Land.

Related Topics: All Things SEO Column | Channel: Analytics | Google: Webmaster Central | How To | How To: Analytics


About The Author: 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.

Connect with the author via: Email | Twitter | Google+ | LinkedIn


Get all the top search stories emailed daily!  


Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • Ronnie’s Mustache

    Booger to Snotty: “Master!”

  • Ronnie’s Mustache

    Booger to Snotty: “Master!”

  • Roland

    I have a lot of high volume, low CTR queries in webmaster tools. They are all instances where my average rank is 1.0 and I have a very high volume of impressions, but low volume of clicks, resulting in very low CTR of like 0.04. When I spot check those cases they all seem to be cases where my site comes up in SERPS in a local seven pack of map listings in local search. Are others seeing this too? I’m thinking there is some kind of error in how this data is reported like maybe the clicks aren’t all being counted or the impressions are wrong? Either that or people scroll right past those map listings to click on the first real organic result?

  • Ken Fobert

    Nice Article Chris! The improvement of accuracy in GWT is definitely a welcomed change. One tip if you want to pull more than 500 rows of data: When you select 500 from the drop down list, you’ll see a parameter in the URL like this: grid.s=500

    If you want to grab data for all search queries in the report, just replace the numeric value in that parameter with the number of queries that are in your report for that date range. For example, if you had 9,637 queries in your report just update the URL parameter to grid.s=9637 and press enter. Export the data as usual.

  • Chris Liversidge

    That’s a nice pro tip Ken, lovely stuff.

  • Sjoerd Visser

    For me, exporting the table already grabs all entries anyway. Using the grid.s trick only shows all of them in the browser. This should be the case for everyone?

  • Matt Dimock

    @cliversidge:disqus: you should update this article to include an excel template. Not only would it be good link bait for SEL, but this blog post (and subsequently, you) would likely get more publicity as a result as well. :)

  • Tally

    Too bad that both impressions and clicks are still both ‘estimates’. I guess these new figures are ‘improved estimates’…but still not something I’d report on or use in a brief. Pathetic that they provide data with pinpoint accuracy for paid search, but then claim that organic search numbers are ‘private.’

  • Andreas Mitschke

    Exactly… people are already skipping those listings jumping straight to the organic results they are familiar with.

    A comparable behaviour can be recognized with the author-branded listenings. People value them on a level with paid results… because they visually stand out of the familiar results.

  • Andreas Mitschke

    Exactly… people are already skipping those listings jumping straight to the organic results they are familiar with.

    A comparable behaviour can be recognized with the author-branded listenings. People value them on a level with paid results… because they visually stand out of the familiar results.

  • Guest

    Chris, thanks for the tips. Trying it now. One thing I’m a little confused about is when you talk about the 500 record limitation. Simply change the url, specifically”grid.s=500″, to whatever number you need, hit enter, and download the report. Am I missing something here?

  • Ken Fobert

    hmm, seems you’re right Sjoerd. I remember a time when you did an export of the search queries in GWT, it only exported a subset of the data, not all keywords. Good to know.


Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest


Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States


Australia & China

Learn more about: SMX | MarTech

Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!



Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide