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
FAME: Exploring Facebook's Ads Manager for Excel
Facebook Ads Manager for Excel, FAME for short, is a tool designed to alleviate data export and reporting by fetching information from multiple Facebook campaigns or accounts. Learn more!
Explore the Breadth of Hero Conf London - at 35% Off!
Hero Conf London brings back our best speakers from 2016, but introduces the newest, most relevant topics for PPC learning. So what do we mean when we say "all-PPC?"
Training The New Generation Of Digital Marketers
So lazy this new generation of people, yeah? Not even willing or able to learn new things because of their short attention spans. Not so fast there.
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
Goodbye For Good Standard Text Ads
Last week, Bing Ads officially announced that on July 31st, 2017, it will end support for creating new and editing existing standard text ads.
Liveblogging Google Marketing Next 2017
Join PPC Hero as we live blog Google Marketing Next 2017. There will be updates about Google AdWords, Analytics, and DoubleClick.
AdWords Announces Upcoming Changes To Enhanced CPC Bidding
Beginning in early June, AdWords will be rolling out changes to Enhanced CPC bidding (ECPC). Read on to find out how these changes will impact you.