A Forecasting Quiz
All right folks, here's your chance to shine!
Last year, you mailed a catalog to three of the five customer segments you manage. The results are listed in the table below:
| Last Year's Catalog Performance | ||||
| Households | LY Catalog | LY Online | LY Totals | |
| Customer Segment 1 | 50,000 | $5.75 | $4.25 | $500,000 |
| Customer Segment 2 | 60,000 | $3.25 | $3.75 | $420,000 |
| Customer Segment 3 | 75,000 | $2.50 | $3.25 | $431,250 |
| Customer Segment 4 | 110,000 | $1.90 | $209,000 | |
| Customer Segment 5 | 150,000 | $1.65 | $247,500 | |
| Mailed Totals | 185,000 | $3.62 | $3.68 | $1,351,250 |
| Other Segments | 260,000 | $1.76 | $456,500 | |
| Grand Totals | 445,000 | $1,807,750 | ||
In total, your customers spent $1.8 million dollars. As you can see, the fourth and fifth customer segments did not receive a catalog, and consequently, did not generate any telephone/catalog demand. Last year totals are calculated by multiplying households by the sum of catalog and online demand. For instance, in customer segment 1, total demand = 50,000 * ($5.75 + $4.25) = $500,000.
This year, you have a new forecast of households in each segment. In addition, your management team is allowing you to mail each of the five segments of customers.
The quiz: Tell our loyal readers how much demand, in total, you expect to occur by mailing all five customer segments. Explain your logic in the comments section of this post. Good luck!!
| This Year's Projection | ||||
| Households | TY Catalog | TY Online | TY Totals | |
| Customer Segment 1 | 62,000 | |||
| Customer Segment 2 | 68,000 | |||
| Customer Segment 3 | 79,000 | |||
| Customer Segment 4 | 125,000 | |||
| Customer Segment 5 | 195,000 | |||
| Grand Totals | 529,000 |
Labels: catalog, Database Marketing, Forecasting, Projections
posted by Kevin @ 9:17 PM
6 Comments:
One approach is to use the computed average for last year's values. The average catalog sale amount for LY was 3.62 (to two significant digits, 3.6216 if you want more precision for later). Assume that this average holds for next year along with the other segment values. This drives the average catalog sale up to 3.66. The revised average sale for online becomes 2.52. The forecasted grand total is approx $3,268,540.
I used regression modeling on the spend per customer in each segment. Catalog appears to follow a power law degradation, while online appears to follow a linear degredation, with an incremental degredation when we don't mail a catalog...i.e., catalog drives online and offline value.
So, I came up with the following, assuming I mail the whole file...
Segment
Households
Predicted Catalog
Predicted Online
Predicted Totals
% of online driven by catalog
Customer Segment 1
62,000
$5.69
$4.25
$616,156
38%
Customer Segment 2
68,000
$3.35
$3.75
$482,589
36%
Customer Segment 3
79,000
$2.45
$3.25
$450,634
34%
Customer Segment 4
125,000
$1.97
$2.75
$589,919
31%
Customer Segment 5
195,000
$1.66
$2.25
$762,502
27%
Total
529,000
$2.54
$2.94
$2,901,799
31%
End is, about $2.9 million in total demand, with 31% of online demand driven by catalog. I estimated that latter number by looking at the trend by segment of those mailed last year versus those not mailed...
Net net, mailing the whole file will add $792 to the demand total, and theoretically, catalog is responsible for $1.845 million in demand - roughly 2/3.
Steve/Bryan ... not too shabby!
If it were me, I would estimate Segment Four to spend $2.25 in catalog, $2.90 online, if mailed a catalog.
I would estimate Segment Five to spend $2.00 in catalog, $2.65 online, if mailed a catalog.
Do the math, and you end up with $3,100,750.
This is a great example to illustrate that there isn't a right or wrong answer to any of these problems.
Over time, an analyst develops an intuition that suggests which of these three answers is "most likely" to be accurate.
Thank you, Kevin. How did you arrive at your forecast?
I agree wholeheartedly with your comment regarding intuition (ans history and context) as key inputs into any forecast.
The numbers alone, without context, ignore several key elements...longer term file performance and trends, segment migration (e.g., source) of the customers in each segment, plus a feel for the merchandise and how changes in the assortment might effect response.
Plus, with a little history, predictions can be vetted against the reality of actuals over time, which is perhaps the best way to validate and refine forecasting models.
Awesome blog, by the way. Thanks!
Kevin,
How did you come up with your numbers? Great exercise, but a little more detail on segments 4 and 5--were they marketed in anyway or just stumbled on web? Either way, I thought both you and Bryan have underestimated the ratio of online sales to catalog, seeing as portion of segments 4 and 5 already used it year previously without catalog and would do so again even with catalog mailed to them.
Thanks for sharing.
K
For segment four and segment five, you can use the dropoff in performance for segments one, two and three to estimate segments four and five.
This exercise assumes the same level of online marketing from year to year. Adjustments to online marketing volume should happen if traffic increases, conversion rate improvements or online marketing changes are expected.
The really good forecasting folks don't actually use a lot of statistical modeling ... gut feel, business instinct, knowledge of relationships and numerical accuracy are most important.
For me, the most important thing is the dropoff between the first and second segment, then the second and third segment. That tells me that I should see smaller and smaller dollar dropoffs from segment three to segment four, and smaller yet from segment four to segment five.
Post a Comment
Links to this post:
Create a Link
<< Home