Relational Purchase Data and a Churn Model for Grocery Subscriptions
FreshCart is a grocery subscription business with 450,000 active subscribers and 12 million order lines in its data warehouse. In Emarsys, purchase history exists only as six contact fields: last order date, last order value, order count, favorite category, and two promotion flags. The team wants a churn model, a replenishment reminder, and a win-back program before the holiday season. They have nine months of clean order history, but 60,000 subscribers have fewer than three orders, and 25,000 have no email engagement in 12 months. The data team proposes loading all order lines into contact fields via API. Marketing wants to trigger a reminder seven days before each customer's usual reorder interval. The current Automation Center has one welcome program and no relational data tables. The CRM ID is stable and present on every order.
1. Evaluate the proposal to load every order line into contact fields and recommend a data model.
Read the worked answer
Loading order lines into contact fields is the wrong model. Emarsys contact fields are effectively one value per contact; 12 million order lines would create an unmaintainable field set, hit practical limits, and make historical queries impossible. Use a relational data table keyed by the stable CRM ID, with one row per order or order line, and keep a small set of derived contact fields for fast segmentation: last order date, orders in 90 days, lifetime value band, favorite category, and next expected order date. Define the table schema before import, agree refresh frequency (daily is usually enough for replenishment), and test joins against a known cohort. Do not duplicate sensitive order detail into contact fields. This design separates analytical history from operational segmentation and gives Predict and Automation Center clean, current inputs.
2. How would you prepare the churn model given the data constraints?
Read the worked answer
Start by defining churn operationally, for example no order for 60 days among subscribers who previously ordered at least twice. Then assess whether nine months gives enough observation and outcome windows. Subscribers with fewer than three orders should be excluded from training or modeled separately, because they do not yet show a stable purchase rhythm. Also exclude or flag the 25,000 with no engagement for 12 months, since inactivity may be a different phenomenon from churn. Build the model on clean, complete order history joined to contact engagement, not on promotion flags alone. Validate with a holdout period and compare predicted risk against simple RFM rules. If the positive/negative examples are too few, delay Predict and run RFM-based win-back first. The reasoning is that a churn score is only useful if the label, population, and timing match the action you will take.
3. Design the replenishment reminder and explain how it should exit.
Read the worked answer
Calculate a next-expected-order date per contact from order cadence-for example, median days between the last three orders-and store it as a date field refreshed daily from the relational table. Build a daily Automation Center program that enters contacts whose expected date is seven days away, applies consent and frequency-cap checks, and sends a reminder with category or replenishment content. The program must re-check before send that no order has arrived since entry; use an exit rule or a wait-plus-condition path that checks recent order date. If the customer orders, exit immediately to avoid a redundant reminder. If no order occurs, allow at most one follow-up, then stop. Exclude contacts with too little history to calculate a cadence and those in an active win-back to avoid conflicting messages. This makes the reminder event-driven but still governed by consent and contact fatigue.