zh
English Español Deutsch Français Italiano Português (Brasil) Русский 中文 日本語
Submit post
Go to Blog

Weekly Wisdom with Joel Bondorowsky: Day Parting Report

Weekly Wisdom with Joel Bondorowsky: Day Parting Report

Joel Bondorowsky

This video is part of our Weekly Wisdom series which will feature experts on a variety of topics. 

Transcript

Hello to all the amazing internet marketers. Today I’m going to explain to you the “how to” of a Day Parting Report in Google Ads, and why this is important to get an edge over your advertising competitors.

Anyone doing PPC is used to checking their campaign, ad group, ad, and keyword performance regularly.

Conversion rates directly correlate to the value of the traffic they send. The higher the conversion rate, the more valuable the traffic the keyword delivers. Therefore, the keyword can afford a higher bid.But if this is all you are looking at, your campaigns could be performing much better, and you wouldn’t even know it. 

The reason is that the collection of clicks and sales that produce your conversion rate aren’t all happening at the same time, location, or even device. They can be segmented by many different dimensions.

If you are not actively adjusting bids for variations of performance across these dimensions, you are paying too much for lower converting traffic, and too little for higher converting traffic. To make matters worse, if your advertising competitors are making proper adjustments, they will outrank you more with traffic converts better, and you will outrank them for worse converting traffic.

You must regularly segment your campaigns by dimensions to understand how this may be impacting you and adjust accordingly.Now I will show you in a few simple steps how to create a report so that you can understand how much conversion rate variations by hour of the day and day of the week are hurting you so that you can make the proper adjustments.

First Part - Extracting the Raw Numbers from Google

This report can be divided into two steps. The first step is where you extract data from Google, and second is where you create the report in excel.

  1. Click on the reports icon.

  2. Click Custom - to create a custom report.

  3. Select Table - because you’re going to want to produce a table which is then downloaded as a CSV file and then opened up in Excel.

  4. Date Range - chose a range that you would like to analyze. 

  5. Filter Campaigns - that you would like included in the report.

  6. Rows - you want to drag over:

    1. Day of week

    2. Hour of Day

    3. Campaign

  7. Columns - you will want:

    1. Clicks

    2. Conversions

  8. Download report as CSV

  9. If you want to run this report on a regular basis, you can choose to save it and scheduled. You can even have it emailed to you automatically so that you don’t need to repeat these steps.

Second Part - Creating The Report

Now that you have the report downloaded, we can proceed onto the second part which is actually creating the report in Excel.

  1. Open the report with Excel.

  2. Save it as an Excel xls file.

  3. Create a pivot table in a new sheet - selecting the columns and rows that we are analyzing; this would be everything except for the top rows that are labels for the report such as date range and so on.

  4. Insert CVR as a calculated field.

  5. Make columns percentages.

  6. Move two decimal points.

  7. Conditional formatting per column - formatting for all the columns so that visually the colors can show you which hours are the best and which are the worst.

  8. Add campaign name as a filter - so that you can select different campaigns that you would like shown for the analysis; this will enable you to see everything at once, or just a few at a time.

This is what it looks like when it is done:

day-parting-google-ads.png

Conclusion - What to Do with This Information

And now we are done creating the report. Next comes the analysis, which is simple, thanks to the visual nature of the report which shows clear patterns.

Now that we have created the report, we can analyze it; this is actually pretty simple, thanks to the visual nature of the report itself which lets you simply look at it and see some clear patterns. Conditional formatting makes the hours of the day that have higher conversion rates stand out in a darker green while the worse hours of the day are orange.

Each row in the report from 0 to 23 represents a different hour. The grand total on the bottom row is the average conversion rate for that particular day. The conversion rate on the right column for each row is the average conversion rate for that particular hour, spread out across every day of the week while the conversion rate on the very bottom right cell, 0.6%, represents the overall conversion rate for the campaign itself.

Now…0.6%, if you are not doing day parting, you are bidding for that average conversion rate across the board; this means that you are not aggressive enough during the better converting hours which are highlighted in dark green and you are too aggressive during worse hours of the day which are represented in the red, and the orange. Besides missing out on better traffic when it converts better, your competitors will grab that better traffic if they are making proper bid adjustments.

You must adjust bids so that you are more aggressive during the green hours, and less during the red hours. If not, your competition will, thus outranking you when traffic is converting, and you outranking them when traffic is not. These adjustments can be made in your camping settings.

One tip, if your campaigns aren’t profitable, you may consider scheduling your campaigns so that they don’t even run during the bad hours. What that would do is instantly increase conversion rates; thus your value per visitor and possibly, from one small step, turn a losing campaign into one that is profitable.

banner.png

Joel Bondorowsky
Superstar

Knows everything… well, almost.

Joel Bondorowsky can be described as a true PPC addict. The last thing he does before bed is check his stats, which is also the first thing he does after waking up. It is his love for the job that feeds this addiction. He dabbled with his first small campaigns back in 2000, when PPC was at its infancy. However, it wasn’t until 2010, when he started working at Wix.com, when he got into more extreme PPC. After leaving Wix, Joel started a boutique ad agency called Quality Score where he managed some of the largest PPC campaigns in history. Today, he serves as the SEMrush Academy PPC professor s well as founder of PPCDesigns, a new boutique agency providing tailor made marketing solutions for large, mass market activities.
Share this post
or

Comments

2000
Newcomer

Either just recently joined or is too shy to say something.

Can help grow seo strategy faster
i exam with brand name سئو یوزر
show analyze detail click and mention in new tools
Nils Rooijmans
Enthusiast

Occasionally takes part in conversations.

Nice report!

You could actually build a script to periodically generate the report, including the conditional formatting in a Google Sheet for you. Saves you some clicks if you want to do the analyses on a regular basis :)
Joel Bondorowsky
Superstar

Knows everything… well, almost.

Nils Rooijmans
You totally can write a script for it, but with practice this report can take less than 3 minutes to make.
Arianne Donoghue
Helper

An experienced member who is always happy to help.

Great content! It's amazing how many accounts I see that don't have this sort of optimisation in place.

It can also be helpful to do this same analysis for the days in the month, or even the months of the year, to try and understand what monthly and seasonal trends you might have!
Amy Bishop
Helper

An experienced member who is always happy to help.

Great tips! Dayparting is a simple way to make a big impact!
Matt Macchia
Expert

Provides valuable insights and adds depth to the conversation.

Wow, Great job Joel and thanks for sharing. Definitely an actionable day parting strategy. The next steps would be to apply to gender and age range reports.
AJ Wilcox
Helper

An experienced member who is always happy to help.

This is so valuable, but because dayparting isn't part of the basics, many professionals overlook the analysis. Great way of showing a cool way to crunch this in Excel!
Newcomer

Either just recently joined or is too shy to say something.

Excellent stuff, Joel, great insights. One thing I would like to add to this report is, segment by device. In this way, we will know which device is performing better (desktop vs mobile) and then make bid adjustments.
Thanks!
Anil
Michael Knysok
Enthusiast

Occasionally takes part in conversations.

Fantastic results and great insights. Thanks for sharing this valuable information.
rashna
Newcomer

Either just recently joined or is too shy to say something.

welldone.this is great
Stelios Theodoridis
Newcomer

Either just recently joined or is too shy to say something.

When someone is a serious professional he achieves these great results and should not surprise you at all
Brad Geddes
Helper

An experienced member who is always happy to help.

Nice work :) The one change we like to make when doing this is filter by a minimum number of clicks, spend, and/or conversions so that we aren't seeing a heatmap with red items if there were 0 conversions, 1 click, and $0.05 spent during one of those times. I've seen a lot of accounts have some terrible bid modifiers as they made decisions on tiny amounts of data. The process is all there - nicely done :) - just make sure you have enough data in each section before you start making major decisions.
James Svoboda
Helper

An experienced member who is always happy to help.

Brad Geddes
Yep, we do this also. I find that early morning hours more often tend to have smaller data sets by hour. For those I will typically group several hours into one block or two and optimize those groups.
Joel Bondorowsky
Superstar

Knows everything… well, almost.

Brad Geddes
That’s something I should have mentioned, but is only a problem with fresh campaigns. Bid adjustments on low data is a mistake I see people make when adjusting for cities. This can happen regardless of campaign size. No matter how large a campaign is, there will always be cities with false positives.
Gareth Hoyle
Helper

An experienced member who is always happy to help.

Nice work! This is especially useful if you are selling things that could be a "drunk purchase" - Use the data to optimise the ad delivery after the pub closes rather than wasting money when they are at the pub!

Would be interesting to get multiple data sets together and produce content based around what is the best day and time for certain industries - I know Friday afternoon would be up there for certain industries!!
Anastasiia Nesterova
SEMrush

SEMrush employee.

Gareth Hoyle
Hey Gareth! That's a nice idea indeed ;) We'll discuss it with the editorial team, thanks for the suggestion!
Judith Lewis
Guru

A bearer of digital marketing wisdom.

I'm always recommending day parting and I have no idea if anyone ever listens to me so hopefully this will amplify all those voices out there shouting about the importance of day parting. My god... it might lead to... dare I say it... EFFICIENT CAMPAIGNS :-o
OH THE HORROR!
Craig Campbell
Helper

An experienced member who is always happy to help.

Great as always Joel, data is the key component here :D
William Rock
Expert

Provides valuable insights and adds depth to the conversation.

Fantastic Weekly Wisdom Tip :) I wonder if this can also be taken a step forward with Google Studio Reporting. I have been working to better define an hourly & daily lead generation that also contributes to ROI vs just leads. I will definitely build out the template you provided here. Thank You
Elliot Kemp
Helper

An experienced member who is always happy to help.

Well done, SEMRush & Joel! Including what to actually DO with the data once you've compiled it is such a crucial piece. It's too common for us data nerds to love pulling, compiling, and visualizing data, where more weight should always be placed on the most valuable step - the action that should be taken based on the data.
James Svoboda
Helper

An experienced member who is always happy to help.

This is great. We analyze Time of Day and Day of Week in accounts and use Dayparting as well. Its great to use when budget becomes an issue for specific campaigns.
Pro

Asks great questions and provides brilliant answers.

Incredible weekly wisdom. Knowing about Day Parting Report and how to prepare it, is beneficial for a business.
Tony McCreath
Helper

An experienced member who is always happy to help.

A nice introduction to the idea of exporting and creating custom reports using an easy to implement while valuable example.
Scott Rumsey
Helper

An experienced member who is always happy to help.

Automation and saving time (in a good way) are vital. We can all learn from this blog. Excellent post
Newcomer

Either just recently joined or is too shy to say something.

Is this going to get rid of adwords express and give us all the same type of account or no?
Sagar Mankar
Enthusiast

Occasionally takes part in conversations.

Part 1 is very essential as it will help us with the best and worst time for the campaign. Using the report surely we can perform better A/B testing. You guys always come up with something solid!

Small Practice I'll follow henceforth: adjusting bid during the peak hours :)

Thanks team SEMRush, Anton & Joel.
Paul Lovell
Expert

Provides valuable insights and adds depth to the conversation.

Thanks, Joel
These reports are great. This will arm agencies and advertisers the opportunity to see where they could be wasting spend and missing conversion opportunities

Send feedback

Your feedback must contain at least 3 words (10 characters).

We will only use this email to respond to you on your feedback. Privacy Policy

Thank you for your feedback!

Community Ranking System

Our SEMrush community rank reflects the level of your professional recognition in our community. We value quality contributions, so highly ranked members will get valuable incentives. Take part in discussions, write posts and speak on webinars, be friendly and helpful, and you will eventually get to the top of the ladder.

  • Newcomer
    Either just recently joined or is too shy to say something.
  • Enthusiast
    Occasionally takes part in conversations.
  • Helper
    An experienced member who is always happy to help.
  • Master
    A veteran community member.
  • Pro
    Asks great questions and provides brilliant answers.
  • Expert
    Provides valuable insights and adds depth to the conversation.
  • Guru
    A bearer of digital marketing wisdom.
  • Superstar
    Knows everything… well, almost.
  • Legend
    Getting here is not easy at all!