How To Analyze Time-Of-Day Performance For Luxury Products
July 22, 2016
Time of day analysis and bid adjustments are not new in the PPC world. However, the importance of ad schedules varies across industries, services, and products. For ecommerce companies that sell luxury goods, ad schedules are more crucial.
Online shoppers still want to talk to a salesperson when making a relatively expensive purchase. They want to double check the details of an expensive order by speaking to a sales representative, especially if the luxury good or service is highly customizable. Thus, luxury good companies often prefer that ad spend is mostly allocated during call center or regular business hours.
I have not tried to prove this theory, but some of our luxury product/service clients have told us that it is consistently true for their businesses. Their customers consistently convert at higher rates after calling the sales team. This theory sounds accurate when I consider my own comfort with online shopping. For me, there’s a spend threshold at which I feel more responsible if I call and talk to a company employee before clicking “Purchase.” I want to ensure I’m not spending my money on the wrong product or the wrong features of an expensive item.
With that concept in mind, our luxury product clients have expressed that they want to see a majority of their ad expenditures occurring during their call center hours. There are a few Excel features and functions that clearly demonstrate the time-of-day spend for an AdWords account along with the percentage of total ad spend for each time frame.
How To Do Time-Of-Day Analysis For Luxury Products
These steps include a pivot table, conditional formatting, and a SUMIF function, but the process should not take more than twenty minutes.
First, find and download your hour of day data from AdWords into an Excel workbook. Make sure you have the metrics you want to analyze by the hour. I would start with revenue, cost, and conversions.
Next, delete the totals row, highlight the data, and PIVOT!
(You can expect to see this F.R.I.E.N.D.S reference in every blog post where I mention Excel pivots.)
Include the metrics you want to analyze by hour of day in the values portion of the pivot table.
Next, conditionally format each metric, one column at a time. Try not to choose formatting that is too distracting, especially if you are going to show this analysis to a client.
Helpful Conditional Formatting Tip
With a column of data that’s more than 10 rows, segment the colored formatting by 3 color scales, based on percentiles. The 3 color scales show more distinction between the varied amounts of cost, conversions, revenue etc.
At this point, the pivot table may be enough for a client to see that a majority of costs are occurring during business hours. However, if you would like specific percentages of ad spend before, during, and after business hours there’s one simple SUMIF function you can use.
If you have not used the SUMIF function before, you need to understand the three arguments that it includes: Range, Criteria, and Sum Range (which is optional, but necessary in this situation).
Think of the IF in SUMIF as pertaining to the first two arguments and the SUM pertains to the last argument.
Let’s assume that business hours are between 9 AM and 5 PM. We are looking for the percentage of spend before, during, and after business hours, which means we have 3 calculations to do, each requiring a SUMIF function.
For any spend before business hours, I want to SUM the cost column IF the hour of day is less than 9 AM. My criteria is “<9” and that criteria is found in the hour of day range. (Quotes need to be placed before and after the <9 or the function will return an error.) The values I want to sum are in the cost column, given that those values meet the criteria (<9).
For the range of times during business hours, the same calculation can be used. However, the criteria should be “<17” so as to include all hours before 5 PM. Then, subtract the cost from the hours before 9 AM.
For the final range of times, make sure to change the criteria to “>=17”.
Once the SUMIFs are complete, create a simple percentage of spend calculation for each time frame. The GETPIVOTDATA function seen below is automatically inserted when you select the sum of cost cell as the denominator.
Your final product should look similar to this:
This process can be duplicated for any of the other metrics in the values portion of the pivot.
If this data was real, I might add more negative bid adjustments to the hours after 5 PM or maybe change the delivery method to accelerated, if it’s not already. 30% of total spend after call center hours is not optimal for a luxury goods client who knows their customers convert more often when they can speak to a sales representative.
As I mentioned, this method was developed for a luxury goods client that knew their customers’ behaviors from years of experience. It may not be true in every luxury goods account, but it is definitely a conversation worth having with a client.
Have you experienced any other customer behaviors in luxury goods accounts that affect time-of-day spend? I’m interested in hearing whether this method works for other PPC-ers. Please tweet to myself or PPC Hero.
Browse By Category
Why PPC Clicks And PPC Sessions Never Match
There are many reasons why Analytics clicks and sessions don't match. Read on to find out why.
Excel & Account Hero Passes are Sold Out - Get Your Ticket Now!
We'll keep it simple - your time to join hundreds of your nerdy friends for this annual PPC pilgrimage is nearly gone! We've sold out of our Excel Hero and Account Hero passes and have limited numbers of our Agency Hero and Conference Passes remaining....
How To Evaluate Your PPC Ad Rotation Settings
Learn what options you have for PPC ad rotation and how to quickly identify opportunities.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Drive Quality Conversions for Your Brand with Facebook Ads
In this new live webinar, Hanapin’s Chadd Powell and DialogTech’s Blair Symes show you how to optimize the customer journey by using Facebook ads to drive the online conversions and offline phone conversations that drive revenue.
The 7 Things You Wish You’d Known About Facebook
Interested in Facebook advertising? I present you with 7 caveats you’ll be glad you know as you master the art of Facebook advertising.
Building An Automated PPC Spend Tracker In Google Sheets
We'll showcase an application of Google Sheets to solve a problem in a way that saves time while revealing actionable insights.