April 03, 2009

Mega-Analytics: File Power

When is the last time you reviewed the dashboard your business intelligence folks prepared for you and saw a "file power" metric/kpi?

It's important to measure the file power of your customer file, folks.

"File Power" is defined as the average amount of demand your twelve-month customers will spend in the next twelve months.

There are easy ways to define this variable, and there are those that are more insightful from a business intelligence standpoint.

Let's work with the latter --- statisticians, I'm keeping this simple, you can build upon it.

Step 1: Take all customers who purchased during 2007. For those customers, tabulate the following metrics.
  1. Demand spent during 2008.
  2. Demand spent in 2007.
  3. Demand spent in 2006.
  4. Demand spent in 2005.
  5. Demand spent prior to 2005.
With this data, build a very simple regression model, with 2008 demand as your dependent variable, and 2007, 2006, 2005, and pre-2005 demand as independent variables. Your model will look something like this:

File Power Coefficients

Constant $30.004
00-12mo. Demand $0.391
13-24mo. Demand $0.165
25-36mo. Demand $0.110
37+mo. Demand $0.041

So, let's look at a sample customer:
  • Twelve Month Demand = $100.
  • 13-24 Month Demand = $0.
  • 25-36 Month Demand = $100.
  • 37+ Month Demand = $0.
Future Value = 30.004 + 0.391*100 + 0.165*0 + 0.110*100 + 0.041*0 = $80.04

In other words, given the attributes of this customer, this customer can be expected to spend $80.04 in the next twelve months.

So, the next step is to calculate the metrics for your customer file as of the end of 2008. Once you've calculated your metrics for the twelve month file as of the end of 2008, you apply the equation (above) to every customer.

Finally, you average the scores for every customer in your twelve-month buyer file. This average is your "file power".

Most folks analyze file power over time, paying close attention to the relationship between file power and file size. For many, file power decreases as the customer file increases --- the brand keeps acquiring customers, but each customer acquired has marginally less value. Conversely, you'll see situations where the customer file is shrinking (i.e. Fall 2008 - Spring 2009), but the customers who are left tend to be more loyal than the ones that are leaving the business.

Households File Power
1-Jan-08 122,318 $145.38
1-Feb-08 122,794 $146.62
1-Mar-08 123,834 $146.39
1-Apr-08 124,840 $145.72
1-May-08 124,941 $144.60
1-Jun-08 125,005 $143.39
1-Jul-08 125,252 $142.88
1-Aug-08 125,365 $141.56
1-Sep-08 125,790 $140.40
1-Oct-08 123,686 $142.38
1-Nov-08 120,597 $144.63
1-Dec-08 118,398 $146.39
1-Jan-09 117,043 $148.55
1-Feb-09 116,750 $149.38
1-Mar-09 116,591 $149.77

We measure file power so that we can understand if our marketing, merchandising, and creative efforts are contributing to a customer file that is more robust, more loyal, willing to spend more than in the past.