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.
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.
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.
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.
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.
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).
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%).
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.
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).
Then we download the “Day of the Week” report and also calculate the adjustments for each day.
We can then overlay these into a matrix to get the new bid adjustments.
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.
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.