Paid Search: Day-Parting with Color Scales

arrow_back Our 3 Favorite Speakers from Hero Conference 2016 RV Paid Search: Hero Conf 2016 arrow_forward

Day parting is not a new concept, but it is a very important aspect of Paid Search marketing and can save you a ton of unnecessary marketing spend when implemented correctly. Personally, I believe Excel’s Color Scales functionality provides the best way to look at day-parting your paid search campaigns. In this post I’ll cover the following:

a. Define Color Scales & show the audience where to find this function in Excel
b. Walk through how to transfer Time-of-Day & Day-of-Week data from AdWords into Color Scales
c. Discuss some use case scenarios for this tactic

So what are Color Scales?

In some circles, Color Scales are referred to as heat maps. Regardless of what you want to call them, they’re immediately recognizable. Below is a simple multiplication table. The Color Scales function allows the user to quickly deduce the highest and lower numbers in the series:



Granted, a multiplication table is fairly intuitive – Most people can quickly find the highest & lowest numbers. But what about something a little less intuitive? Take a look at the next two charts filled with randomly generated numbers. Try to find the smallest number as fast as you can:



I bet it was easier to identify “.03” as the smallest number in the second chart. Unfortunately, this is probably even less applicable than the first example. Not many PPC Analysts are spending time generating random number tables. That’s fair.

Now back to Paid Search applications. The 2 charts below looks at CTR by State and Time of Day for two different companies:

Company 1:


Company 2:


For Company 1, the data suggests that CTR is more dependent on Day of Week than state. Although it’s a little more difficult to read, the second chart shows that state is more influential than Day of Week of CTR.

Thanks Michael! You’ve completely changed the way I think about Paid Search. So how do I put this into practice?

I’m glad you asked, it’s actually incredibly easy –

Highlight your data set:


2. Select “Conditional Formatting” under the Home tab:


3. Select “Color Scales” & choose whichever setting works best for your data set


4. Alternative Method (if you’re completely averse to using a mouse): Alt+ H + L + S

Perfect – What’s next?

The only thing left is downloading a report from the AdWords interface and putting it in an easily-translatable format. This is where we are going to get into the specifics for day-parting your paid search campaign.

  1. Log into Google AdWords and navigate to the “Dimensions” tab
  2. Select your time frame in the top right corner. Regardless of what time frame you select, make sure you have selected a multiple of the number of days out of the week your campaign is turned on. For example, if your campaign is on 6-days a week, make sure your time frame has 6, 12, 18, or 24 days within the time frame.
  3. Select “View: Day of Week”
  4. Make sure you have the correct columns selected:
    1. If you’re only concerned about the overall health of the account, there’s no need to select campaign or ad group
    2. If you want to look at different campaign or ad group buckets, make sure you add those columns
    3. ONLY SELECT ABSOLUTE NUMBERS (ie. Costs, Impressions, Conversions); DO NOT SELECT RATES (ie. CTR, CPCs, etc)
  5. Select the download button
  6. Click “+Add Segment” & add Time >> Hour of Day
  7. Download the data

Once In Excel:

  1. Create a pivot table with the downloaded data
  2. Place “Hour of day” in the Rows & “Day of Week” in the Columns
  3. If needed, create calculated fields
    1. In the pivot table, navigate to Analyze >> Fields, Items, & Sets >> Calculated Field
    2. Name the calculated field
    3. Input the calculation into the “Formula” bar
  4. Pull in your desired metrics into “Values”
  5. Navigate to the Color scales & select your desired format

Voila! You now have a quick and easy way to interpret data by Day-of-Week & Time-of-Day!


In the chart above, there is a clear trend that suggests CTR gets worse over the course of the day. Even though this chart allows me to visually understand CTR trends, there are still +150 data points above. To make it even easier to read, I’ll group hours together into 4 buckets:



I hope this post helps you get a good sense of how you can use color scaling to more quickly understand performance and spot trends over the course of a day & a week.  We’d love to hear any other useful tips or tricks for using Color Scales within paid search – give us a shout on Twitter @RedVentures!


Michael Elkins began his career in finance at American Airlines before pivoting to digital marketing at Red Ventures 3.5 years ago. He’s worked across numerous industries and has scored a number of big wins, including identifying the proper cadence of using brand names in site links based on search engine, device and keyword bucket as well as capturing high-value users on ambiguous brand search queries while effectively eliminating non-prospect users. Outside of work, you can find him posting photos of his dog Nikko on Instagram or throwing up 3-pointers on the Red Ventures basketball court. (They rarely go in, but when they do, he’s shooting 100%.)

Related Posts

3 Ways to Embrace the Future of Ad Personalization
The Red Ventures Guide to SXSW
Analytics at the Speed of Sight