How To Analyze Time-Of-Day Performance For Luxury Products
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
You can’t have an outstanding team without an outstanding culture
There's a reason why our team thinks we are a great place to work and no, its not because we have a ping pong table set up. See more about Hanapin's latest certification + we'll let you in on a little secret!
Tracking UAC Performance Through Third-Party App Analytics
Explore how to link and import app downloads and in-app events in Google Ads through third-party app analytics providers.
The 2019 Budget Season Doesn't Have to Be a Nightmare
You could just go off of a formula and call it a day, but in order to have a solid budget in place you need to take the time to prepare.
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
How to Build an In-Platform Facebook Ads Funnel
Facebook has many tools to help you build your funnel right in the platform, and to nurture that funnel without taking users off-platform at all.
How We Gained $21,000 More in Revenue by Changing One Color
Testing button color, or the color of any element on your page can be extremely rewarding - if it is done correctly. See how you can turn a simple test into drastic results.
New Research! The State of PPC
This year's State of PPC annual report has arrived! Grab the download for the latest trends and benchmarks in the PPC industry.
Load More »