{"id":3758,"date":"2014-01-14T14:45:12","date_gmt":"2014-01-14T14:45:12","guid":{"rendered":"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/"},"modified":"2014-01-14T14:45:12","modified_gmt":"2014-01-14T14:45:12","slug":"easily-spot-serp-ctr-trends-for-seo-excellence-181425","status":"publish","type":"post","link":"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/","title":{"rendered":"How To Use Excel To Easily Spot SERP CTR Trends"},"content":{"rendered":"

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<\/a> in their webmaster tools interface.<\/p>\n

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

\"Google's\"Google's<\/p>\n

Spot the difference! Google’s charts showing before & after their data update.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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<\/a>?) is a critical failure in Webmaster Tools.<\/p>\n

Fortunately, however, Excel can help us easily create some actionable reports using this new data accuracy in super-sharp time.<\/p>\n

Solving Poor Trend Charts: Double Vertical Axis FTW<\/h2>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

\"Google\"Google<\/p>\n

Filter your data to exclude brand or target a specific location for more actionable insights.<\/p>\n

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

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”<\/em> and then click the Format Selection <\/em>button underneath. For your series options, select “Plot Series” on “Secondary Axis.”<\/p>\n

\"Charting\"Charting<\/p>\n

Charting Impressions Vs. Clicks in Excel<\/p>\n

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.)<\/p>\n

\"Formatting\"Formatting<\/p>\n

Formatting Date Ranges for Clarity<\/p>\n

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

\"Clearly\"Clearly<\/p>\n

Clearly Visualised Impression Vs. Click Data<\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

Bingo: I can now freshen up underperforming snippets with a proven conversion CTA for my business.<\/p>\n

Micro Reports For Key Performers<\/h2>\n

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.<\/p>\n

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.<\/p>\n

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.)<\/p>\n

One of the most useful pivot architectures here is setting Avg. position<\/em> as a report filter — then using the Query<\/em> as a label, and then impressions<\/em> and clicks <\/em>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.<\/p>\n

This is particularly useful to find weaknesses within high value top ranking terms.<\/p>\n

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.<\/p>\n

\"Canary\"Canary<\/p>\n

Canary Report<\/p>\n

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.<\/p>\n

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.<\/p>\n

\n

Contributing authors are invited to create content for Search Engine Land and are chosen for their expertise and contribution to the search community. Our contributors work under the oversight of the editorial staff<\/a> and contributions are checked for quality and relevance to our readers. The opinions they express are their own.<\/em><\/p>\n

\n","protected":false},"excerpt":{"rendered":"

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. Spot […]<\/p>\n","protected":false},"author":2,"featured_media":3758,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[18],"class_list":{"0":"post-3758","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-seo","8":"tag-seo"},"yoast_head":"\nHow To Use Excel To Easily Spot SERP CTR Trends - SEO<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Use Excel To Easily Spot SERP CTR Trends - SEO\" \/>\n<meta property=\"og:description\" content=\"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. Spot […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/\" \/>\n<meta property=\"og:site_name\" content=\"SEO\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-14T14:45:12+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/searchengineland.com\/wp-content\/seloads\/2014\/01\/Googles-Before-Vs-After-Charts.png\" \/>\n<meta name=\"author\" content=\"Fox News\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Fox News\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/\",\"url\":\"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/\",\"name\":\"How To Use Excel To Easily Spot SERP CTR Trends - SEO\",\"isPartOf\":{\"@id\":\"https:\/\/cherylroll.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/searchengineland.com\/wp-content\/seloads\/2011\/09\/What-is-SEO.jpg\",\"datePublished\":\"2014-01-14T14:45:12+00:00\",\"dateModified\":\"2014-01-14T14:45:12+00:00\",\"author\":{\"@id\":\"https:\/\/cherylroll.com\/#\/schema\/person\/e4ce5f7f0c831df260bde5b6992ae397\"},\"breadcrumb\":{\"@id\":\"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/#primaryimage\",\"url\":\"https:\/\/searchengineland.com\/wp-content\/seloads\/2011\/09\/What-is-SEO.jpg\",\"contentUrl\":\"https:\/\/searchengineland.com\/wp-content\/seloads\/2011\/09\/What-is-SEO.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/cherylroll.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Use Excel To Easily Spot SERP CTR Trends\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/cherylroll.com\/#website\",\"url\":\"https:\/\/cherylroll.com\/\",\"name\":\"SEO\",\"description\":\"News, Search Engine Optimization (SEO)\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/cherylroll.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/cherylroll.com\/#\/schema\/person\/e4ce5f7f0c831df260bde5b6992ae397\",\"name\":\"Fox News\",\"sameAs\":[\"http:\/\/cherylroll.com\"],\"url\":\"https:\/\/cherylroll.com\/author\/fox-news\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Use Excel To Easily Spot SERP CTR Trends - SEO","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/","og_locale":"en_US","og_type":"article","og_title":"How To Use Excel To Easily Spot SERP CTR Trends - SEO","og_description":"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. Spot […]","og_url":"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/","og_site_name":"SEO","article_published_time":"2014-01-14T14:45:12+00:00","og_image":[{"url":"https:\/\/searchengineland.com\/wp-content\/seloads\/2014\/01\/Googles-Before-Vs-After-Charts.png"}],"author":"Fox News","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Fox News","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/","url":"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/","name":"How To Use Excel To Easily Spot SERP CTR Trends - SEO","isPartOf":{"@id":"https:\/\/cherylroll.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/#primaryimage"},"image":{"@id":"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/#primaryimage"},"thumbnailUrl":"https:\/\/searchengineland.com\/wp-content\/seloads\/2011\/09\/What-is-SEO.jpg","datePublished":"2014-01-14T14:45:12+00:00","dateModified":"2014-01-14T14:45:12+00:00","author":{"@id":"https:\/\/cherylroll.com\/#\/schema\/person\/e4ce5f7f0c831df260bde5b6992ae397"},"breadcrumb":{"@id":"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/#primaryimage","url":"https:\/\/searchengineland.com\/wp-content\/seloads\/2011\/09\/What-is-SEO.jpg","contentUrl":"https:\/\/searchengineland.com\/wp-content\/seloads\/2011\/09\/What-is-SEO.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/cherylroll.com\/easily-spot-serp-ctr-trends-for-seo-excellence-181425\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/cherylroll.com\/"},{"@type":"ListItem","position":2,"name":"How To Use Excel To Easily Spot SERP CTR Trends"}]},{"@type":"WebSite","@id":"https:\/\/cherylroll.com\/#website","url":"https:\/\/cherylroll.com\/","name":"SEO","description":"News, Search Engine Optimization (SEO)","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/cherylroll.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/cherylroll.com\/#\/schema\/person\/e4ce5f7f0c831df260bde5b6992ae397","name":"Fox News","sameAs":["http:\/\/cherylroll.com"],"url":"https:\/\/cherylroll.com\/author\/fox-news\/"}]}},"_links":{"self":[{"href":"https:\/\/cherylroll.com\/wp-json\/wp\/v2\/posts\/3758","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cherylroll.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cherylroll.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cherylroll.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/cherylroll.com\/wp-json\/wp\/v2\/comments?post=3758"}],"version-history":[{"count":0,"href":"https:\/\/cherylroll.com\/wp-json\/wp\/v2\/posts\/3758\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cherylroll.com\/wp-json\/wp\/v2\/posts\/3758"}],"wp:attachment":[{"href":"https:\/\/cherylroll.com\/wp-json\/wp\/v2\/media?parent=3758"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cherylroll.com\/wp-json\/wp\/v2\/categories?post=3758"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cherylroll.com\/wp-json\/wp\/v2\/tags?post=3758"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}