How to Perform Hour of Day & Day of Week Analyses

In our next installment of our Ad Words Reporting 101 series, we’re covering Hour of Day and Day of Week Analyses! These reports are excellent for optimizing accounts. Using the data you can adjust ad schedules so that your ads are only running during peak times for higher CTR and more conversions.


Why?

INFORM. Keep clients up to date on how their ads are performing at different times during the day and week.

DECIDE. See when ads are most successful and adjust bids based on these observations.

OPTIMIZE. Ultimately, as certain ads are adjusted, optimize performance of the ads.


How?

1. Download an Hour of Day/Day of Week report

2. Create a pivot table that sums impressions, clicks, cost, and conversions by hour/day.

3. Calculate CTR, CPC, CVR, and CPA for each.

4. Conditionally format and identify best and worst performing hours/days.


Skills

1. Excel concatenate function

2. Excel calculation functions

3. Create a pivot table in Excel

4. Conditional and number formatting in excel

End products:

image15.png
image11.png



1. Download an Hour of Day or Day of Week report off Google Ads

Both the Hour of Day and Day of Week Reports are under the “Ad Schedule” tab on the left sidebar once you’ve clicked into an account:

image5.png




Next, set the filters by clicking into the bar that looks like this:

image13.png



Filters:

  • Impressions: >0


Date range:

  • 30 days up to yesterday

image5.png
image6.png


Columns:

  • Impressions

  • Clicks

  • Cost

  • Conversions

image10.png

2. Create a pivot table that sums impressions, clicks, cost, and conversions by hour/day

image12.png

CREATE PIVOT TABLE. Select all raw data and insert a pivot table in a new tab. Once your sheet is selected, click in the “Insert” tab in Excel and select “PivotTable”.

Drag the hour/day field in the “rows” pivot section and the impressions, cost, clicks, and conversions into the “value” field.

COPY TO A NEW TAB. On a new tab, remove all lines by selecting the entire sheet and hitting the white color under the paint bucket. This is to make the sheet look more presentable for clients.

Copy the pivot table, excluding the grand total row. Next, right click in the new tab you created and select “Paste Special” and then “Values”. Right-click again and hit “Paste Special” and “Format”.

Remove “Sum of ” from the headers with the find and replace function so that you have something that looks like this:

image8.png

3. Calculate CTR, CPC, CVR, and CPA for each hour/day and format numbers in fields

INSERT CTR, CPC, CVR, CPA ROWS. Insert a row before cost, before conversions, and 2 after conversions labeled respectively: CTR, CPC, CVR, CPA.

image9.png

CALCULATE CTR, CPC, CVR, CPA. Using a formula, calculate the CTR (clicks/impressions), CPC (cost/click), CVR (conversions/click), and CPA (cost/conversion) of each hour/day.

TIP. If you get errors because you are dividing by 0, wrap your calculation with the IFERROR function

FORMAT NUMBERS. Format all rates to the % format with two digits after the decimal point and all dollars to the $ format.

4. Conditionally format fields and analyze

CONDITIONALLY FORMAT. Add color scales to the CTR, CPC, CVR, and CPA columns by using the conditional format option in the Home tab in excel. CTR and CVR are scaled with larger numbers in green and CPC an CPA scaled with larger numbers in red.

Identify best and worst performing ads

BEST PERFORMANCE. Identify which hour or day has the best overall performance: high CTR/CVR and low CPC/CPA.

WORST PERFORMANCE. Identify which hour or day has the worst overall performance: low CTR/CVR and high CPC/CPA.

PRESENT TO CLIENT. Either present the high level summary of the best and worst performing ads to the client directly, or convey the information to the account manager succinctly so they can easily communicate the report to their contacts.

FILTERING BY CATEGORY. You can filter your pivot table by campaign or category. To filter by campaign, pull the campaign field into the “filters” box when editing the pivot table.

To filter by category, first isolate the category from the campaign name in the raw data by using the find and replace function. Then, pull the new category field into the filters box when editing the pivot table.








Claire Forgan