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
	2008
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.

File Power

Folks who've been around the block a few times know that executives want to surround themselves with database marketing wizards who can predict the future.

With many businesses now struggling to post increases, executives are going to look to analytical folks, hoping to see evidence of a "turnaround".

A tool that is readily available to you is called "file power". It's not terribly hard to calculate, and should be run on a monthly basis. Let's calculate "file power" for an online business for November 2007.

Step 1: Identify all customers who purchased online from 11/1/2005 to 10/31/2006. Example: 100,000 customers.

Step 2: Calculate the average online spend, per customer, for this audience from 11/1/2006 to 10/31/2007. Many customers will have a value of $0, because they did not purchase. Example: $75.55 per customer.

Step 3: Identify all customers who purchased online from 11/1/2006 to 10/31/2007. Example: 110,000 customers.

Step 4: Multiply Step 2 by Step 3. This is called "file power". Example: 110,000 * $75,55 = $8,310,500.

This metric is re-run, every month, using the four steps listed above. The dates used in the analysis move forward by one month.

There are many ways to complicate this measure, to make the measure more accurate. Feel free to experiment.

Looking at this simple metric on a monthly basis will be telling.

At the end of my tenure at Nordstrom, this metric pointed south, month after month. In other words, the metric suggested we were running out of "file power". Comps were still increasing, year-over-year, but the customer file was running out of gas.

The metric is reasonably good at suggesting that a downturn will occur. The metric occasionally struggles to predict future upswings --- typically, great merchandise will inspire customers to purchase, fueling the customer file, causing "file power" to increase a month or two later.

Ok, your turn. What does your "file power" look like? Did you see this business downturn coming?