Advanced Excel Hacks: AdWords Hour of Day of Week Report for Better Ad Scheduling

Google AdWords already gives us the Day of Week report so we can see which days perform the best and worst. This allows us to modify bids accordingly for different days. It also already provides us with an Hour of Day report so we can see the best and worst times of day so, again, we can adjust Ad Scheduling and bids to take advantage of this. The problem is that each report on its own does not give you the full picture:

Example 1: Day of Week report tells me that Friday has the best conversion rate:

Example 2: Hour of Day report tells me that the best conversion rate is between 7am to 8am:

Based on these reports what can I do to improve performance?

• Should I increase exposure on Fridays?
• Should I increase exposure during the time period 7am to 8am?
• Should I increase exposure during 7am to 8am on Fridays?

The answer to the above is not necessarily!

At first, these suggestions might seem logical, after all, this is what the data implies but let’s think about it, the Day of Week Report is actually independent of the Hour of Day report. To gain true insight we need to combine the data to create an Hour of Day of Week report so this is where Excel comes in. This is a favourite of mine and a good one for AdWords Excel enthusiasts.

DOWNLOAD THE DATA FROM ADWORDS

There are different screens you can download your data from. The following steps describe the screenshot below:

1. Within AdWords, go to Dimensions tab
2. View the Day of Week report
3. Click the Download report button
4. Click on the link +Add segment
5. Navigate to Time then select Hour of Day
6. Click Download button

APPLY PIVOT TABLE TO THE DATA

Now that you have downloaded the report in Excel:

1. Apply a pivot table to the data
2. Drag the Day of Week field into the Row Labels area
3. Drag the Hour of Day field into the Column Labels area
4. Drag the chosen metric you wish to analyse into the Values area (the screenshot below shows conversions)

This will give you the result below which now shows you the stats for each hour of each day of week.

ANALYSE ACTIONABLE DATA

I recommend applying Conditional Formatting Colour Scales to the main data and to the Grand Totals help you spot the trends more easily, such as below:

Some insights we can gain from this view above is that:

• Although there are a good number of conversions during 10am to 1pm, this only applies on weekdays and not weekends
• Thursdays is one of the best days for conversions but the most popular times are earlier in the day

However, this is only one view of one metric. With a little more Excel data manipulation you can combine different tables to create a report that looks like this:

The above report shows us a bigger picture of what is going on so that we can take appropriate action. Here are just a few suggestions:

• Reduce exposure during 8am to 9am on most days as it is costing the most wasted spend
• Reduce exposure during 1pm to 2pm on Mon-Tue and during 2pm to 3pm on Wed-Sun
• Increase exposure during 4pm to 5pm on Wed-Sun as it has good conversion rates and lower CPA
• Increase exposure during 7am to 8am on Mon & Wed-Thu as it has very strong conversion rate

BENEFITS

You will gain more insight of when you are driving conversions and when you are wasting budget.

You can be more accurate with Ad Scheduling to improve your results.

You can show off your in-depth analysis to you clients/manager/MD who will love the detail.

TOP TIPS

For comparable data, choose a date range that runs through equal days for each week (eg. Mon-Sun).

Choose a date range that you know is reliable (eg. not over the Christmas period, Black Friday, etc. if your results are likely to be skewed by these events).

If you have enough data then it might be worthwhile doing this separately for each campaign to match the trend patterns as closely as possible.

Written by William Cheng PPC Director at Circus PPC Agency