See Hour-by-Hour Trends In AdWords With Heat Maps

Everybody loves heat maps! They make it so much easier to see patterns in your data. They’re especially good for looking at how performance changes over the hours of the day and the days of the week: with 168 different data points, it’s hard to see anything if you just have a table of numbers. 

So the team at Brainlabs (my employer) wrote a script to help you create heat maps automatically. We hope you love it as much as we do…

What Does It Do?

This script takes the metrics of your choice and then totals them up for each hour of each day of the week. It also smooths the data. Then it pumps the result into a Google Docs spreadsheet where — through the magic of conditional formatting — it turns into a heat map. As a bonus, we’ve thrown in a line graph, so you have another way to see the difference between the weekdays.

Why do we smooth the data? Even after averaging, there’s still “noise” from random variation. This is particularly true when the numbers are smaller, like early in the morning. We’re not interested in noise: we want to be able to see the difference between flukes and trends.

Is there a spike at 5:00 p.m. because people are more likely to convert at that hour? Or was that hour just lucky a few times, and shouldn’t be expected to outperform 4:00 p.m. or 6:00 p.m. in the future? By smoothing the data, we avoid peaks that don’t reflect actual important features, and we can see the trends more easily.

How do we smooth the data? We thought about using simple exponential smoothing, but that could only weight each hour equally. Instead, we decided to use a 5-wide weighted smoothing window to get a moving average, with symmetrical weights: the data for 9:00 a.m. is actually made from data from 7:00 a.m. to 11:00 a.m., but with the highest weighting for 9:00 a.m.’s data.

What Do I Do With All This Data?

Enjoy the new insights that you’ve spotted from all your data. You could also use the latest version of our 24-hour AdWords script to set different multipliers for every hour of every day. This is very powerful compared to the six bidding windows that you get in the AdWords interface, or the one or two bid changes that most third-party bid management tools typically make.

You’ll find some suggested percentage bid multipliers in the “Bidding Multipliers” tab of your output spreadsheet. These are calculated using variance from the mean of conversion rate; you’ll need to play around with your own formulae if you want to calculate multipliers for another metric.

Date Ranges

How long should you look back? This is a bit of a balancing act. Too short a date range means you don’t have enough data and can’t remove the random fluctuations. But too long means your data could be out of date, especially if you’ve been making big changes to your account or site.

As a rule of thumb, we suggest having a time frame long enough to get 1,500 conversions. And use at least six weeks, as you need each day of the week to have enough data. If you’re really not sure, you could always try the script on different date ranges and see if there are big differences.

What about seasonality? If you’re in an industry with massive peak seasons – like travel – you may not want to look at those peaks alongside the rest of the year. The averages will be skewed towards the days when there was more traffic. To help you with this, the script lets you enter multiple date ranges.

For example, if you want to avoid September’s data, you could put in a date range for July to August and another covering October: the script would give you a heat map for those three months, so you could still include your most recent data.

You can also give the script individual dates to be ignored. You want the heat map to reflect a day’s typical performance, so you want to skip anomalous days — for example, when it was a bank holiday, when you had a big sale or when your ads were on TV. There may be other occasions, depending on what affects your traffic, like big sports events or public transport strikes. You also want to skip days if you know there was a problem with the data, like if your site was down, all advertising was paused or your conversion tracking was broken.

The script counts how often each day of the week appears in the date range(s) and averages by that number, so you don’t have to worry about using whole numbers of weeks. But it does assume you have the full information for each day; don’t use today’s data, as it won’t be complete. Also, gaps may interfere with the smoothing slightly, so try not to have too many days to ignore in your date range.

How To Use The Script

To make your own heat maps, first make a copy of this template. It’s got the formatting all set up for you. If you want, you can go to the File menu and into Spreadsheet Settings to change the locale; this affects how numbers are formatted (for example, whether a dot or comma is used as a decimal mark).

Note: In our template, the heat map is set to show higher numbers in green, lower numbers in red, and anything in the middle in yellow. If you’d like to change this in your copy of the template, click into cell B4, and then go to the Format menu and into Conditional Formatting. A “Conditional format rules” bar will appear to the side. Click where it says “Colour scale B4:H27.” You can then pick the colours you want and click “Done” to apply the changes. You can also change the colour or style of lines on the graph.

Then copy and paste the script below into your AdWords account, and change a few settings:

• spreadsheetUrl is the URL of your copy of the heat map template. This is where your heat maps will end up.
• dateRanges says dates you want to get data from. You can give multiple date ranges. Each date range uses the format “yyyy-mm-dd, yyyy-mm-dd.”
• ignoreDates is used to exclude data from particular days. This is a comma-separated list of dates in the “yyyy-mm-dd” format.
• fields is used to list the different metrics you want. Put them in quotes, separated by commas. These are all metrics that can simply be added up and averaged (rather than ones that need fancier calculations). You can choose as many as you like from “Impressions,” “Clicks,” “Cost,” “ConvertedClicks,” “Conversions” and “ConversionValue.”
• calculatedFields is for metrics that can’t just be added up and averaged. For example, if you wanted the CTR (click-through rate), you can’t just add up the CTR of each hour of each day; the script has to calculate it from clicks and impressions once it has averaged and smoothed them. This is a comma-separated list, in quotes, of one field divided by another. For example, “Clicks/Impressions” (to give the CTR) and “ConvertedClicks/Clicks” (to get the click conversion rate).
• campaignNameContains is used to narrow down which campaigns you get data from. For example if it’s set to “Shopping,” the script would only look at campaigns with “shopping” in the name. It’s not case-sensitive.
• campaignNameDoesNotContain is used to ignore some campaigns. For example, if it’s set to “Brand,” the script would not look at any campaigns with “brand” in the name. Again, this is not case-sensitive.
• ignorePausedCampaigns should be set to true if you only want to look at currently active campaigns or false if you want to include them. Removed campaigns are always ignored.

Then run the script. Voilà! Your Google spreadsheet should have a sheet for each field, containing that field’s heat map and line chart.

Note that if you run the script a second time, it will overwrite the data, so if you want to run the script multiple times for different campaigns or date ranges, you’ll need to either download the data after each run or have a different spreadsheet URL for each.

There are also advanced settings — Feel free to ignore this bit! The smoothingWeight array at line 68 defines the weights for the weighted moving average which smooths the data. The default is [0.25, 0.75, 1, 0.75, 0.25]: this means that the script adds up the metric for current hour, 75 percent of the metric for the next and previous hours and 25 percent of the metric  two hours before and after, then divides by three (the sum of the weights). If you’d like to experiment with other weightings, then try changing the array. Or if you want no smoothing at all, set smoothingWeight to [0,0,1,0,0] instead.