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.

New call-to-action

 

Why Are Hour of Day & Day of Week Analyses Important?

1. They Inform your clients, and keep them up to date on how their ads are performing at different times during the day and week.

2. They help make decisions. See when ads are most successful and adjust bids based on these observations.

3. The insights help to optimize. Ultimately, as certain ads are adjusted, optimize performance of the ads.

 

How to Do it

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.

Full step-by-step process is below

 

Skills Required

1. Excel concatenate function

2. Excel calculation functions

3. Create a pivot table in Excel

4. Conditional and number formatting in excel

Note: The above are all explained in detail in our Essential SEM Excel Skills blog.

 

End products

Hour of Day Analysis.

Day of Week analysis.

 


Step-by-Step Process

 

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:

The 'Ad Schedule' tab on the left sidebar.

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

Set the filters by clicking into the bar.

 

Filters:

  • Campaign Name: contains “google-search”
  • Impressions: >0

Date range:

  • 30 days up to yesterday

Date range.

Date range expanded.

Columns:

  • Impressions
  • Clicks
  • Cost
  • Conversions

Column settings.

 


 

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

 

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.

Select all raw data and insert a pivot table in a new tab.

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:

Day of the week analysis preview.

 


 

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.

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

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

CTR Formula.

CPC Formula.

CVR formula.

CPA formula.

 

Format Numbers

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

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 and CPA scaled with larger numbers in red. 

Add color scales to the CTR, CPC, CVR, and CPA columns by using the conditional format option.

 

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.

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.

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 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.

 


 

Using these analyses, we hope your team will be able to optimize your accounts and make data-driven decisions. If you'd like to see if Jordan Digital Marketing could help with your Paid Search campaign, drop us a line!

GA4 Migration download

Recent Articles

How to Run a Geo Analysis

Next up on our Ad Words Reporting 101 series: how to run a Geo Analysis. It’s important to run a...

How to Run a Device Analysis

Next up on our Ad Words Reporting 101 series: Device Analysis! These reports can be handy when...

How to Perform a Negative Keyword Scrub