How Are You Doing This?

I know, I know, you are looking at the tables from Hillstrom's Optimizer, and you have questions.
  1. How is he doing this?
  2. How can I figure out what is wrong with this?
Those of you who have been exposed my content for almost twelve years know how I do everything, because every method used in my Optimizer worksheet has been shared with you ... repeatedly.


For the 0-12 month file, I have nine segments ... segments created by a Principal Components Analysis. I enter the square root of three year demand, and I enter percent of demand spent over the past three years by attributed marketing channel.

I have lapsed buyer segments ... 13-24 month, 25-36 month, 37-48 month, and 49-60 month buyers.

I have nine new buyer segments ... new buyers that enter the business via each of the nine segments for twelve-month buyers.

For each segment, I measure the following:
  • Next Year's Rebuy Rate.
  • Next Year's Spend per Repurchaser.
  • Which Segment The Customer Migrates To Next Year If The Customer Repurchases.
  • The Share Of Next Year's Demand Generated By Attributed Marketing Channel.
This stuff is not complicated ... companies like Fingerhut were running "nameflow" models in the 1980s which are not fundamentally different than what I use. The PCA analysis / procedure, as best I can tell, isn't used by anybody.

Say email marketing is accountable for 40% of next year's demand, and say that the segment has a 40% chance of buying again next year. I take 40% * 40% = 16% ... and this becomes what I use to measure email marketing effectiveness.

I then use power functions (a^x) to approximate what happens if you double or triple email marketing contacts. Often, the "x" value in the power function is 0.5 (the square root function).

Example: Say we want to double email marketing frequency. We know that the customer has a 40% chance of buying again, and if the customer buys again, 40% of repurchase activity will happen via email marketing. Here's the equation:
  • (40% * 40%) * (2.00 / 1.00)^(0.5) = 0.227 ... the fraction of repurchase rate generated by email marketing goes from 16% (40% * 40%) to 22.7%.
Example:  Say we want to end email marketing.
  • (40% * 40%) * (0.00 / 1.00)^(0.5) = 0.000. The fraction of repurchase goes from 16% to 0%.
This process is repeated over and over and over again for each marketing channel, yielding an adjusted annual repurchase rate by segment.

Then the process is repeated year-over-year for five years.

Toss in Merchandise Productivity and Profit Factors and Ad Cost assumptions and guess what? You have Hillstrom's Optimizer!!

You can do this yourself ... I've given you every single tool you need ... all you need is a simple Excel Spreadsheet and thorough knowledge of how customers respond to marketing channels. That's it. That's exactly what you should know, right??!!

We'll continue with examples next week. You can do the work and create your own spreadsheet ... I've given you everything you need to know here to be successful.

But if you want me to run your data through my Optimizer spreadsheet, give me a holler (kevinh@minethatdata.com).


P.S.:  Having a hard time convincing your Executive Team to do anything different? Then read this article about how Lowe's creates Comics to help the Executive Team see what the future might look like (click here). I know, I know, what's the return on investment of creating Comics for your Executive Team ... there's nothing "funny" about business? Try something!

P.P.S.:  I tweeted this quiz Thursday afternoon ... look at the results (click here).