"AV" ... Annual Value

Here's something I run in various projects ... something I call "Annual Value".

You'll have to click on the grid to see it more clearly, as there is a LOT of information in the image.

Structure of the Query:

1. Freeze the customer file as of, say, June 30, 2019.
2. Calculate months since last purchase (recency) and life-to-date orders (frequency) for each customer as of that date.
3. Average how much each recency/frequency combination spent in the next year (July 1, 2019 to June 30, 2020).
4. Depict data in a grid-style format, as illustrated above.

We create an "AV-Table" or "Annual Value Table" so we can understand the general value of customers at different life stages. 

Go ahead and read down the first column ... Frequency = 01. I'll summarize for you here, at least for the first fifteen months.

• Recency 01 = $26.81.
• Recency 02 = $21.28.
• Recency 03 = $13.32.
• Recency 04 = $14.09.
• Recency 05 = $14.42.
• Recency 06 = $14.39.
• Recency 07 = $17.28.
• Recency 08 = $15.73.
• Recency 09 = $10.99.
• Recency 10 = $10.99 (again).
• Recency 11 =   $9.47.
• Recency 12 =   $8.70.
• Recency 13 =   $7.92.
• Recency 14 =   $8.19.
• Recency 15 =   $6.07.

The first-time buyer just drops off of a cliff, doesn't s/he? You lose more than five dollars of annual value if you don't get that customer to purchase again in the first month the customer is on the file. You lose another eight dollars of annual value if the customer drops from recency = 02 to recency = 03. It's almost like a welcome program would really work in this instance.

Ideally you aren't looking at annual demand/sales ... ideally, you subtract out cost of goods sold and net delivery expense and marketing expense. But for a first example, we'll start here, ok?