How to Create a Data-Based Ad Schedule

Posted on 16/11/2017 by Jonathan 'Cat' Catton

What hour of the day it is can have a huge impact on how likely someone is to buy. You need to be taking advantage of that in your optimisations. How? By using the data of course.

This is simpler for some business models than it is for others. If you’re driving phone calls, you want an ad schedule that prioritises times when your call centre is open. However, if you’re an e-commerce site or are always accepting leads, it can be tricky to work out what schedule you should be using.

So, how do you choose a schedule when you don’t have clear business needs driving the demand? Well, it’s a simple 3 step process:

  • Extract the historic data
  • Analyse using Excel or Google Sheets
  • Moderate the results to avoid over-stacking modifiers

This blog post is going to be more focused on those last two points. So, if you’re still not sure on the basics of getting out specific reports from AdWords, and what Time Of Day adjustments are, you may want to check out this post on custom ad scheduling first.

Extracting Historic Stats

First, you should generate a time of day report either from the AdWords interface or from Analytics. The important thing at this stage, is that you want to include a large enough data set to be able to make robust decisions based on the data obtained. You ideally want at least 100 conversions to be able to get a decent spread throughout the day. In addition, I typically do not select a time period less than 3 months, just to account for anomalous days. Generally the longer time period you can look at, the better. 6-12 months is usually a good ballpark.

Here’s where to find the Hour of Day Report in the new interface

The other important thing to do here is to only look at one type of campaign at a time. Your Display Campaigns will likely have varying stats to your Search Campaigns, and so analysing them together can produce some very unusual (and unrepresentative) results.

Once you’ve got the report, extract it into the spreadsheet program of your choice and let us get started on the fun bit.

Preparing the Data

The first thing you’ll want to do once you’ve got your data into a spreadsheet, is to make sure it’s sorted in time order and then delete the totals rows. If you try and run the analysis with the table sorted by clicks, you’ll have a very sad time.

This table is in dire need of sorting!

Next, you’ll need to make a column for cumulative clicks throughout the day. This can be done quite simply by using a cumulative sum. This is of the form =sum($B$3:B3). Then, as you drag that formula down, it will do the sum for all the rows so far up to the first one.

We’ve hidden some of the columns here (and in the rest of the screenshots) for clarity and to not make the screenshots too giant.

Creating the Schedule

We now need to work out what groupings of hours we want to use for our schedule. As you know, Google only allows you to make 6 groupings per day. The best way to get good value out of these 6 groupings is to make them have an approximately equal size. Therefore, we take the total clicks for the period (this will also be the cumulative clicks for hour 23) and divide it by 6 to get our partition size. We can then make a little lookup table for the partition marks by multiplying this by 2 through 5.

It’s not necessary to make this table a named range, but it does make vlookups easier to use.

We now want to work out which hours fit in which partition. Now, we could just do it by eye and write in 1 for all the rows where it’s less than the first partition mark. But frankly, that’s dull. Instead, we can use an “approximate match” vlookup to do it automatically. You may find it convenient to add more descriptive groupings instead of just 1 through 6, to make the understanding easier.

This is about the only use for a TRUE vlookup I’ve ever found. But boy, is it nifty.
Here once we’ve used the vlookup to work out what the groupings are, we’ve just renamed the lookup values in the table to fit what we know they are.


Getting Stats for Segments

Now, if we use a pivot table to analyse this, we can see the stats for each time group neatly assembled together. You will only want to output the “summable” metrics into the pivot table at first (i.e. clicks, impressions, cost and conversions).

The basic initial pivot table

Then to get the “average” metrics (like CTR, Avg CPC & Cost/Conversion) you’ll want to use calculated fields. This is because if you try to just average them in the pivot table, it will take the 100 clicks at 10% CTR and the 5 clicks at 50% and say the average CTR is 30% (rather than the correct 10.3%).

Calculated fields are always a must when dealing with efficiency stats
This should be what your pivot table looks like after having done the calculated fields. Don’t worry about making it look pretty – we can do that at the next stage.

If you then copy and paste values out, you have a nice easy table of values for each time band, which we can then calculate the correct bid adjustment for.

We’re mostly making it not a pivot table any longer, as it makes all the formulas much neater and easier to use.

Calculating Adjustments

When creating the schedule for the first time, this is a relatively simple process. Firstly we calculate the adjustment required for each time segment. For this, I take the relative difference in cost/conversion between the specific time segment and the overall average and subtract 1 to make it a +/-. i.e. ([Time Segment CPA]/[Overall CPA] – 1).

This is by no means the definitive way to calculate your bid adjustment. But it is the method I use. Feel free to let me know how you do it instead.

Then we download the “Day of the Week” report and also calculate the adjustments for each day.

We got these stats out of another report from the interface and then copy-pasted them into this spreadsheet.

We can then overlay these into a matrix to get the new bid adjustments.

Top tip: to get the Day of Week adjustments running along the top row copy-paste the bid adjustments once as “values” and then copy that and paste it “transposed”

Things can get a little complicated when you’re merely updating the adjustments on your current schedule. In this case, the adjustments you just produced are not the total adjustments that you want to put into AdWords. You need to adjust to your existing adjustments. The best way to do this is to use the difference in Avg CPC as the real change produced by the previous Bid Modifier.

You can then calculate the final adjustment using the following formula [Final Adjustment] = ([Time Period Avg CPC]/[Overall Avg CPC])*[Old Adjustment] +1) – 1.

Sanity Check the Results

This is where the human side comes in. It’s important to remember that there are lots of methods of adding Bid Adjustments into an AdWords account and all of them stack with each other. This compounding effect can lead to ridiculously high bids, which wouldn’t be profitable with 100% conversion rate. Therefore, we need to make sure that none of the adjustments are too large, as when they compound they could become huge.

Personally I don’t like to have any time of day adjustment over 50% (without a clear business reason); however, what value you use as your cap is entirely in your court. If you do have adjustments that are too large, you should reduce all adjustments by the same factor, e.g. If your adjustments are +10%, -25% & +75%, you should reduce them all by a factor of 2, to make +5%, -13% & +37% rather than adjusting any individually. This ensures the ratio of adjustments is still the same, it just lowers the power of them.

Here because Sunday Evening had a very large bid modifier we’ve scaled down all the modifiers by a factor of 25%

Now you just need to create the schedule and add the bid adjustment and you’re all done.

There you have it, you’ve now exposed the daily variations in your account and optimised to take maximum advantage of them. It’s a bit of an arduous process but well worth it to take advantage of the extra control you can have.



If you need help with your Paid Search don't hesitate to contact us.

Enjoy this article?

Subscribe for weekly insights

How to Create a Data-Based Ad Schedule

Let’s thrive together

Get in touch to take the first step.

Contact us