Visualizing your busiest PPC time periods using pivot tables & Excel.August 22, 2011
Ad scheduling can be a particularly useful tool to use within Google AdWords if you’re running a campaign on a tight budget. For anyone who hasn’t used Ad Scheduling before, it allows you to set time periods in which your AdWords ads within the selected campaign are allowed to show. This is useful because with a little research you’re able to find out when are the busiest hours of the day and adjust your AdWords campaigns accordingly, allowing your available daily budget for each campaign to be spent only within the time periods specified. I’m going to show you how I go about finding this out for each campaign, and how to set it up in approx. 10 minutes!
Sounds Great! How Do I Know What Times Searchers Are Most Active?
First of all you’ll need a sample period where Ad Scheduling isn’t used and you’ll need a fairly decent daily budget so that the display of ads isn’t limited by your daily budget. I’d suggest running the campaign like this over a month and work with the data available.
Step 1: Download the Report
Log into Google AdWords and select the date range for the sample period. Click on the ‘Campaigns’ tab and click on the reports icon, shown below:
The box will then expand to show the report name, format, and allow you to add segments. Click the ‘+ segment’ link, adding the three segments shown below:
Add the ‘Day’, ‘Day of the week’ and ‘Hours of day’ segments to your report and click ‘Create’ to download the report. Once downloaded open the report in Excel.
Step 2: Using Pivot Tables to Group Periods
Depending on the number of Campaigns and AdGroups you have running, chances are you’re going to have a spreadsheet with quite a few rows. To make sense of this we are going to break this down using a pivot table.
First delete the top row (containing the report name and the sample data period) so that:
You will also need to remove the last few rows from the bottom of the spreadsheet containing the totals as well:
Next highlight all columns (my example goes from columns A to Q), and under the ‘Insert’ menu in Excel click ‘Pivot Table’:
You will then see a dialog box similar to the one below- click ‘OK’ to create a pivot table in a new sheet. After, click on the new sheet where you will see the empty pivot table:
You can now start adding the fields required to the areas within the ‘Field List’. To start with, drag the ‘Campaign’ field into the ‘Report Filter’ box, ‘Days of week’ into the ‘Column Labels’ box, ‘Hour of day’ into the ‘Rob Labels’ box and ‘Impressions’ into the ‘Values’ box. The field list should look like this:
Next click the down arrow on ‘Count of Impressions’ value in the ‘Values’ box and click ‘Value Field Settings:
and select ‘Sum’ before clicking ‘OK’.
You should now see that ‘Count of Impressions’ has changed to ‘Sum of Impressions’ and the values within the pivot table have also changed. You can now see the total number of impressions for the selected campaign broken down by hour of the day for each day of the week:
This is pretty useful as you can see the number of total number of impressions for each hour of the day for each day of the week. The only problem is I’ve then got to compare the numbers, and since I prefer pretty pictures or graphs, I’d rather see this visually represented.
Step 3: Make It Pretty
To see a visual representation of more popular hours we can add conditional formatting to the table and highlight busier periods. To do this, start off by selecting all of the values for ‘Monday’ and under the ‘Home’ menu, click on ‘Conditional Formatting->Color Scales and select an awesome-looking colour scale:
Then do the same for the other columns for other days of the week (you’ll have to do each column individually). Afterwards you’ll end up with something like this:
Here you can see how the number of impressions differs by hour on each typical weekday, and more importantly when the quieter periods are. You can then apply this data to each Campaign (by changing the Campaign drop down in cell B1) and apply ad scheduling to these periods. This will allow you to show your ads only during the periods where searchers are more active, meaning your available daily budget is used more wisely.
Remember to consider different timezones- if your campaign is targeting more than one timezone you will need to account for this, and you may wish to separate different timezones into separate campaigns.