First, it averages the data, so you don’t have to worry about getting a whole number of weeks into the date range. And it smoothes the data, too, using a five-wide weighted smoothing window to get a moving average. That means you can see trends more easily, because the noise of random variation is reduced.
You can skip particular days or even set up multiple date ranges to avoid longer periods – the week around Christmas may well be anomalous, for example – but that means you may want a longer date range to make up the number of days.
But because you don’t want to be distracted by fluctuations, you need to start off with a decent amount of data. We recommend at least six weeks’ worth (as you need several instances of each day of the week) and (if you’re looking at conversion rates) long enough to get 1,500 conversions.
How do I use it?
We’ve made a template with the formatting all set up for you – the first step is to go to the File menu and make your own copy. Then go to Google Ads, set up a new Script, and copy in the code below. You’ll need to change a few settings:
spreadsheetUrl is the URL of your copy of the heat map template.
dateRanges lists time periods you want to get data from. Each date range uses the format “yyyy-mm-dd, yyyy-mm-dd”.
You can specify multiple date ranges — just have them comma separated, like [“20150601, 20150930”, “20160601, 20160930”]
ignoreDates is used to exclude data from particular days. This is a comma-separated list of dates in the “yyyy-mm-dd” format.
For example [“20151225”, “20160101”] would exclude last Christmas and New Year’s Day.
fields lists the different metrics to make into heat maps. 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, Conversions and ConversionValue.
calculatedFields is for metrics that can’t just be added up: they have to be calculated from the summable metrics after they’ve been averaged and smoothed. This is a comma-separated list, in quotes, of one field divided by another.
For example [“Clicks/Impressions”, “Conversions/Clicks”] would give CTR and conversion rate.
devices lists the devices to produce heat maps for.
You can choose as many as you like from Mobile, Tablet and Desktop.
Leave this blank, , to only have the total stats for all device traffic.
If suggestAdSchedules is true, the script will suggest hourly bid multipliers that you could use in ad schedules. This is based on conversion rate.
If suggestDeviceBidModifiers is true, the script will suggest hourly bid modifiers for each of the devices in the devices list. This is based on the device’s conversion rate.
baseDeviceModifiersOnBiddingMultiplier only matters if suggestDeviceBidModifiers is true. If it’s true, then the script will give device bid modifiers assuming that the suggested ad schedules are already in place.
For example, suppose that at a certain hour mobile bids should be increased by 30%, and the suggested ad schedule for that hour is 10%. If this is false, the the mobile modifier will be given as 30%. If this is true, then the mobile modifier will be given as 18%, because when this and the 10% ad schedule are applied, the bid will be increased by 30%.
campaignNameContains and campaignNameDoesNotContain filter which campaigns the script gets data from. For example, if campaignNameContains is [“Brand”, “Generic”] then only campaigns with names containing “brand” or “generic” are included. If campaignNameDoesNotContain is [“Display”, “Competitor”] then any campaigns with names containing “display” or “competitor” are ignored.
This is not case-sensitive.
Leave blank, , to include all campaigns.
If you need to put a double quote into campaignNameContains or campaignNameDoesNotContain, put a backslash before it.
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 — they’re deleted, so it’s not like you’re going to be putting any ad schedules in them!
Then authorize and preview the script. If it doesn’t work, check the Logs in case there was an error with any of the settings. And take care – if you run (or even preview) the script again, it will overwrite what’s already in the sheet.
Note that you’re quite likely to get fractional results, even with metrics like clicks that have to be whole numbers. This is because the heat map shows the average per hour, not the total.
If you want to tinker further, there are a couple of Advanced Settings:
You can change how the data gets smoothed, by changing the smoothingWindow and smoothingWeight arrays.
By default, suggested ad schedules and bid modifiers will be kept between +35% and -35%. If you’d like them to go lower or higher, then you can change minBidMultiplierSuggestion and maxBidMultiplierSuggestion in the Advanced Settings section.