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