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.

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.

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

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

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

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

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.

Start The New Year With A PPC Health Check: Part 1

As Group Head at a fast growing search agency, I’ve carried out hundreds of campaign checks over the years. In my contributions here, I’ll be sharing my top tips on things to look out for when reviewing your own campaigns. Some of these things are obvious, while others might have slipped past you because you’re not up to date on recent changes or are just too close to the day-to-day running of the campaign.

It can be easy to just leave your campaign(s) running and not delve into the detail of things such as the settings of your campaign, especially if your campaign is performing well. But auditing your account on a regular basis and carrying out health checks are big keys to the success of any campaign. In the fast paced world of Google AdWords, even going on holiday for two weeks might mean you miss a big announcement of an upcoming change or new feature.

In part 1 of my PPC Health Check series, I’ll be covering structure, settings, conversion tracking, and budgets. This is laid out in a way that should be really clear and easy to follow, with step-by-step instructions on what to examine in each category.

These questions should give you the building blocks for performing a health check on your PPC campaigns. It’s really important to try and self-assess (or have a colleague assess) your PPC campaign every few months, as there can be so many changes within the interface and settings that suggestions for improvements can always be made.

One of the main areas worth examining during your audit is your campaign budget. Being limited by your budget isn’t really a good way to start the year. Assess the results of the campaign, and if performance is good, see if you can secure more budget for your PPC campaigns this year. In order to get growth from your PPC campaigns, you’ll often need to increase the budget to remain competitive and to take advantage of new campaign features and types.

Another key subject for analysis this year are your bid multipliers; these are still often underused in many accounts. Schedule yourself some time to carry out analysis and adjust things like time-of-day, location and device.

The mobile landscape has become more competitive than ever; as an agency, we’ve seen CPCs increase since the launch of Enhanced Campaigns. With this in mind, make sure you’re not falling behind the curve and missing out on this traffic. Remember: there are only positions 1 and 2 in the banner for mobile, and if you’re not showing there, you’re unlikely to be seen!

In my next post, I’ll be covering ad copy, keywords and performance so make sure to check back for updates from me!

Report: US Search Ad Spend Rises 12 Percent YoY In Q4 2013; Smartphone CPCs Drop

IgnitionOne released its fourth quarter Digital Marketing Report today, showing that the paid search market in the US continues to grow. Search ad spend rose 12 percent year-over-year in the fourth quarter of 2013. In part as a result of the later kick-off to the holiday shopping season this year, December ad spending was up a significant 27 percent over 2012.

Though overall ad impressions fell 16 percent year-over-year, clicks rose 5 percent and click-through rates jumped 25 percent in Q4.  IgnitionOne posits lower impressions are a result of “top of page innovations” including new ad extensions and image ads that “leave less room for ads”. Roger Barnette, IgnitionOne president, added by email, that extensions are “decreasing the overall number of ads that are showing up for any given search and thus lowering overall impression volume. It’s actually one of the advantages of utilizing these tools, not only do CTRs improve but it pushes the competition further down or off the first page.” Barnette also noted, “One thing I’ve noticed a lot is for exact brand searches there is often times only one ad that shows up on the page, which is a recent change.”

Advertisers Placed Lower Premium On Smartphone Ad Inventory: CPCs Fall YoY

Despite a 251 percent increase in impressions and 306 percent increase in clicks, smartphone CPCs actually fell by 13 percent in Q4 compared to 2012 among advertisers in this data set. Tablet CPCs rose 22 percent year-over-year on the more modest gains of 27 percent in impressions and a 49 percent increase in clicks year-over-year. Here again, the CPC increase for tablets is likely a result of the tethered tablet and desktop bidding instituted with Enhanced Campaigns. In contrast, while advertiser participation on mobile has increased with the roll out of Enhanced Campaigns, advertisers have actively discounted their mobile bids in relation to desktop/tablet.

Ad spending growth for tablets remained strong with a year-over-year increase of 82 percent. Smartphones spend saw exponential growth of 253 percent. The mobile share of spend in Q4 was 63 percent tablet, 37 percent smartphone.

Google Product Listing Ads Perform Strongly In The Holiday Shopping Season

As other reports have also shown, advertiser investment in Google Product Listing Ads (PLAs) increased substantially in Q4 compared to 2012, when Google converted to Google Shopping to a paid platform. Spending on PLAs increased 618 percent, impressions grew by 380 percent and clicks were up 312 percent year-over-year.

Still, PLA adoption has grown substantially, but traditional text ads still account for the bulk of advertiser account activity. For advertisers running PLAs, the ads accounted for 10 percent of impressions, 13 percent of the clicks and 16 percent of the total spend.

“It has been over a year since PLAs were introduced to digital marketers and this quarter has proved that the ad product has come into its own,” said Roger Barnette, President of IgnitionOne. “Both marketers and consumers have found value in these ads, shown by the increase in spend as well as the increase in click-throughs compared to standard search ads.”

PLAs by their nature, appeal to consumers deep in the purchasing funnel. The ad units were particularly effective during the holiday shopping season, according to the report, with click-through rates 74 percent higher than text ads during the week of Thanksgiving and 95 percent higher on Cyber Monday. On smartphones, however, IgnitionOne found that PLA click-through rates were 38 percent lower than smartphone text ads.

Yahoo Bing Network Held Steady

The Yahoo! Bing Network maintained share in Q4, up a tick from last quarter’s 22.6 percent to 22.7 percent of spend versus Google’s 77.3 percent share.