Using Search Data To Explain Media Impact

Wondering how your television ads are impacting search? Columnist Andrew Ruegger shares his method for gleaning these insights.

Chat with SearchBot

search-computer-analytics-data-ss-1920

Google search is essentially the largest real-time, unbiased consumer panel that has ever existed, with “over 3.5 billion searches per day and 1.2 trillion searches per year worldwide.”

Oftentimes, when a brand is running a large media campaign, consumers will turn to search engines to learn more about the products they are exposed to on other media channels, such as television. A marketer can gain valuable insights by analyzing the correlations between media campaigns and the fluctuations in relevant search activity.

This article walks you through several examples of how to produce these types of analyses and the value they provide.

Companies such as Millward Brown and Nielsen tend to measure TV impact by GRPs (gross rating points = reach x frequency). GRPs are typically modeled by week and location/DMA (designated market area). Modeling often requires two or three years of data to ensure that seasonality and other statistical noise can be accounted for.

Because TV remains the largest area of investment, we are commonly asked by brands to associate search fluctuations with GRPs and consumer interest. To do this, we look at several things, including the general increase of interest and the incremental traffic absorbed by the brand’s digital properties.

First, let’s look at what it takes to get interest by week. Currently, Google only publicly provides monthly search volumes, not weekly. But with a little ingenuity, you can get free, fairly accurate weekly search volumes by using Google Keyword Planner and Google Trends. If you are completely new to Keyword Planner tool, visit here for a good summary of the basics.

Personally, I do all of this through Google AdWords & Trends API in KNIME, but it can be done manually with the other tools, as follows.

Weekly Search Volumes

  1. Use Google Keyword Planner to select your keywords, and set the date range to ~two years.

2years-google-keywordplanner

  1. Use Google Trends for the same keywords and get an export of the past two years with weekly indexed values. Because it is time-intensive to pull and clean all of the data, I use the KNIME and the Google Trends API to streamline the process; however, it can be done manually with no APIs.

knime-weekly

  1. Now, format the Month value from Google Keyword Planner into the corresponding Sunday-Saturday weekly buckets provided by Google Trends.
  2. Clean, merge, aggregate and scale the data so that each index value represents the correct percentage of search volume for that given week. This KNIME workflow representation is only meant to show how many Excel/data prep and calculation steps it takes to make this happen. There are a lot:

Knime-Workflow-math

[Click to enlarge.]

(Please reach out to me if you are a KNIME user and would like the actual workflow.)

You will get results that are ready to be used in a weekly model:

weekly-search-volumes

Search Volume By DMA And States

By using the location filtering abilities in the Keyword Planner tool, you can leverage consumer queries by DMA (Nielsen’s 210 DMAs), state or city.

DMA-targeting-planner

Through the Keyword Planner UI, you need to export each DMA individually to get unique data per market area. With Google’s API and access to the TargetingIdeaService, a lot of targeting information can be pulled far more easily.

AdWords-API-Choices

Again, I personally use KNIME and APIs, which I recommend to anyone interested in doing this efficiently, as you can loop calls to the API for each of the 210 DMAs instead of manually changing it through the Keyword Planner UI.

DMA-KNIME-INFO

[Click to enlarge.]

Once you acquire the dataset, you can merge it with additional information such as DMA population, TV households, cable households, longitude, latitude and so on, which enriches your ability to compare volumes to potential TV watchers (Nielsen Numbers). The dataset also provides clear buying efficiencies, as average CPC prices vary greatly from city to city and/or DMA to DMA. Examples are in the next section.

It is also possible to convert your DMA monthly data to weekly stats, using the top metro index numbers from Google Trends for each of your keywords. However, you will need to rotate each Sunday-to-Saturday date range and call the API for each, as the Top Metro data is not naturally returned by week. For state information, the entire process is identical, except the API variables are State and not DMA or Top Metro.

Advanced Modeling

To get the ultimate modeling set, you need to join the DMA monthly data with your search campaign data.

You should pair these data to your actual spend data by keyword by DMA by week, which is available to be exported through the Google AdWords API. The API can be accessed via AdWords Scripting or traditional API calls. Keep in mind, depending on the size of your campaigns and the time frame (with two years minimum being recommended), the file size will far exceed the limits of Excel and must be handled by a more powerful program such as SAS, R or KNIME.

If you actually ever take the time to do this, congratulations! You have completed the search portion. Now it’s time to blend the data together with all other media channels and sales information and create an ensemble of linear regression models to correlate all media activity with total sales.

Examples

Because our brands were running new national commercials in October, they wanted to know the elevation in brand interest. To get a clean picture, seasonality was controlled, and search volume by DMA was scaled to TV owners per DMA; then we calculated the percentage of change to see which had the greatest uplift controlling/scaling for TVs/DMA and seasonality:

dma-tvviewer-scaled

Considering this was a national TV buy running in October, and our change was positive in most regions, it would appear the commercial generated additional interest in major markets but failed to influence some smaller markets in Arizona, Maine and Kentucky.

Of course, in relation to TV, we are making assumptions, so I recommend looking at search traffic to your website by minute (or second, if you have the proper analytics) and comparing it to TV spots bought by location. As an example (one I had readily available), this is an analysis for a specific regional buy, showing the spikes associated with TV when pairing up the timing, both for organic search (blue) and paid search (pink). The x-axis shows  time in seconds, and y is search visits, focusing on three days for three  specific ad spots:

tv-impact-search

Each spike occurred within a 30 seconds post-commercial/80-second full window of the TV spot. There is a lot more to this, and I will write a follow-up on it specifically, but I just wanted to show it is possible, and a real relationship can be identified.

Similarly, looking at interest over time by state while scaling active internet user population can provide great competitor comparisons. In this example, of the five soda brands, Pepsi essentially dominates the US interest, with a few exceptions throughout the year, and Dr. Pepper wins Texas:

soda-comparison

[Click to enlarge.]

A month-over-month increase in brand interest may be an indication that demand is growing in a specific region, and you should revisit your supply chain management strategy to ensure you’re equipped to satisfy the increase. Or just looking at the search volume by state scaled by population can provide insights for brand managers about areas of opportunity:

Pepsi

In this case, Michigan and New York have the highest search averages per internet user for the Pepsi brand. Similar visuals are available through the Google Trends tool, but it can be much harder to get data for a large set of keywords or keyword themes.

The most important takeaway from geo-locational search data is the variety of CPCs. I was shocked in many cases by the vast difference in CPC throughout each market.

CPC-difference-by-DMA1

When looking at volume, competition, and CPC together, there isn’t a strong correlation among the three, meaning that search engine marketers no longer build campaigns with a holistic US mindset. You should work closely with brand teams to understand where they are losing or gaining market share, and overload and decrease spend accordingly. Executing at a keyword and DMA level may seem daunting, but it’s not if you assemble rules through AdWords scripting.

Regardless, these are only a few examples, and you can get very creative with the way you leverage keyword themes and locations, so if haven’t already, start exploring!


Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.


About the author

Andrew Ruegger
Contributor
Andrew Ruegger is Senior Partner, Head of Data Science at GroupM Search & Social - Catalyst. He is a digital veteran with over 7 years of experience in advertising. He currently is responsible for running the data and insights practice for search and social marketing.

Get the must-read newsletter for search marketers.