A constant source of frustration in the world of SEM is the seemingly conscious decision by Google to prevent the analyst from running reports detailing conversions and conversion rate by hour. From a campaign management perspective, it’s extremely valuable data for day parting.

Day Parting, for those who are unaware, is a technique allowing you to adjust bid rates by time of day and/or day of the week with the goal of taking advantage of times with high conversion rates or low cost-per-leads, while not wasting money during the less effective and more expensive times.

I can see the Google Ann Arbor building out the window next to me, and I’ve shaken my fist in that direction a few times before, but today I’ll avoid the conspiracy theories of why Google would not want to share this beautiful data with us, and instead refocus that anxious energy on solving the problem!

THERE IS A WAY! With a little mixing and mashing of reports from AdWords and Google Analytics, its possible to conjure up  a report that gives conversions by hour (regardless of date) and by day of week. To do this, you will need to have ‘Conversion Goals’ set up in analytics already. If you don’t, do that now and come back in a month.

Let’s map this out….

What do I want?
A report detailing Conversions and Conversion Rate by time of day (regardless of date) at the Campaign level.

1.) In AdWords reports:
Report type: Campaign Performance
Settings: Hourly (regardless of date),  choose a date range and your campaign of choice.

Run the report, and export to .CSV for Excel.

AdWords Data .CSV

2.) Lets go to Analytics!
- Set the date range for the same time period.
- On the left-hand side, click Goals, then Total Conversions.
- Underneath the graph, it gives you the option to ‘Select Goal’.  For this example, I choose to select ‘All Goals’.
- Next, graph by time of day.  This option is located in the square with a clock in it by the top right corner of the graph.
- Now we need to filter the data to include only the campaign we’re interested in.  To do this, we’ll need to create a new Advanced Segment.

Google Analytics Screenshot

3.) Advanced Segmentation
- Click ‘Create a new advanced segment’
- Dimensions –> Traffic Sources
- Drag ‘Campaign’ to the ‘Dimension or Metric’ box,  choose your campaign from the drop down menu, and name the segment.
- Click the ‘create segment’ button.
- Un-click the ‘All Visits’ tab, and select your newly created one.
- Export the data into a .CSV for Excel

Advanced Segmentation of Individual Campaign

4.) Mash it up!
- Make sure your Time Zones for AdWords and Analytics are the same (and then double check, this is pretty important…)
- Select your Conversion Rate data from the analytics .CSV you just created, and line it up with the AdWords spreadsheet from step 1.
- Format the data however you please.  I’ve added Conversion Rate, Cost/Lead, and % of total Leads

Adjusted Spreadsheet data

5.) Visualize the data, and interpret it accordingly.

Dayparting Spreadsheet complete

6.) Day Parting
You’ve analyzed the data, came up with a plan of action, and now your ready to optimize your Ad Scheduling.
- Back in AdWords now, click into Settings for the chosen campaign.  Under Advanced Settings, you may now edit to your heart’s content.

Ad Scheduling

Voila! Together, we’ve outsmarted Google for a minute *high five*, and avoided purchasing a 3rd party software!
Now as mentioned earlier, you can view the data by day of the week also.  The only changes that you’ll have to make are adjusting the unit of time you want to view in AdWords to ‘Day of Week’, and in Analytics, view data by day (then organize and aggregate once in a spreadsheet).

Yet another small victory that makes life exciting!