Compute CLV for Grocery Subscriptions

Business Context

You’re the analytics lead for FreshCart, a US grocery delivery app with 6M MAUs and 1.2M monthly purchasers. FreshCart operates a hybrid model: customers can place one-off orders or subscribe to FreshCart+ ($9.99/month) for free delivery and member-only pricing. The business is preparing next quarter’s marketing budget and needs a defensible Customer Lifetime Value (CLV) model to set CAC caps by channel.

Over the last 8 weeks, leadership has seen contradictory signals:

New customer acquisition is up +18% (driven by TikTok and paid search).
90-day retention for new cohorts is down from 28% → 22%.
Average order value (AOV) is up $54 → $58, but gross margin % is down due to higher promo usage and substitution costs.

The CMO asks: “What is our CLV for new customers by channel, and can we still profitably scale paid acquisition?” The CFO adds: “I don’t want a single number—show me what’s driving it and what levers we can pull.”

Metric Scenario

You must define CLV in a way that works for:

one-off purchasers (irregular ordering), and
FreshCart+ subscribers (recurring revenue + different cost structure).

You also need to address practical issues: censoring (many customers are still active), seasonality (holidays), and the fact that some revenue is high but margin is negative after promos and refunds.

Data Available

Table	What it contains	Grain
`orders`	order_id, user_id, order_ts, subtotal, delivery_fee, tip, promo_discount, refund_amount, substitutions_cost, payment_status	Order
`order_items`	order_id, sku_id, qty, item_revenue, item_cogs, category	Line item
`subscriptions`	user_id, plan_type, start_ts, cancel_ts, monthly_fee, status	Subscription period
`user_acquisition`	user_id, first_touch_channel, campaign_id, install_ts, first_order_ts	User
`support_contacts`	user_id, contact_ts, issue_type, resolution_cost	Ticket
`delivery_ops`	order_id, courier_cost, distance_miles, batch_flag, late_delivery_flag	Order

What You Need To Produce

Define CLV precisely for FreshCart (state whether you mean revenue CLV or contribution-margin CLV, what time horizon, and what counts as “lifetime”).
Propose two calculation approaches:
- A simple heuristic model that can be explained to executives quickly.
- A cohort-based / retention-based model that is more accurate and handles censoring.
Provide a worked numeric example for each approach using realistic inputs (retention, order frequency, AOV, margin, subscription fees).
Decompose CLV into actionable drivers and identify which drivers likely explain the recent cohort decline.
Recommend 3–5 actions (product, pricing, ops, or marketing) you would take if CLV is falling for TikTok-acquired users, and list guardrail metrics you would monitor.

Constraints:

You have one week to deliver a CLV readout for the next exec meeting.
The model must be usable to set channel-level CAC caps and to evaluate trade-offs (e.g., promos vs retention).

Business Context

Over the last 8 weeks, leadership has seen contradictory signals:

New customer acquisition is up +18% (driven by TikTok and paid search).
90-day retention for new cohorts is down from 28% → 22%.
Average order value (AOV) is up $54 → $58, but gross margin % is down due to higher promo usage and substitution costs.

Metric Scenario

You must define CLV in a way that works for:

one-off purchasers (irregular ordering), and
FreshCart+ subscribers (recurring revenue + different cost structure).

You also need to address practical issues: censoring (many customers are still active), seasonality (holidays), and the fact that some revenue is high but margin is negative after promos and refunds.

Data Available

Table	What it contains	Grain
`orders`	order_id, user_id, order_ts, subtotal, delivery_fee, tip, promo_discount, refund_amount, substitutions_cost, payment_status	Order
`order_items`	order_id, sku_id, qty, item_revenue, item_cogs, category	Line item
`subscriptions`	user_id, plan_type, start_ts, cancel_ts, monthly_fee, status	Subscription period
`user_acquisition`	user_id, first_touch_channel, campaign_id, install_ts, first_order_ts	User
`support_contacts`	user_id, contact_ts, issue_type, resolution_cost	Ticket
`delivery_ops`	order_id, courier_cost, distance_miles, batch_flag, late_delivery_flag	Order

What You Need To Produce

Define CLV precisely for FreshCart (state whether you mean revenue CLV or contribution-margin CLV, what time horizon, and what counts as “lifetime”).
Propose two calculation approaches:
- A simple heuristic model that can be explained to executives quickly.
- A cohort-based / retention-based model that is more accurate and handles censoring.
Provide a worked numeric example for each approach using realistic inputs (retention, order frequency, AOV, margin, subscription fees).
Decompose CLV into actionable drivers and identify which drivers likely explain the recent cohort decline.
Recommend 3–5 actions (product, pricing, ops, or marketing) you would take if CLV is falling for TikTok-acquired users, and list guardrail metrics you would monitor.

Constraints:

You have one week to deliver a CLV readout for the next exec meeting.
The model must be usable to set channel-level CAC caps and to evaluate trade-offs (e.g., promos vs retention).

Business Context

Over the last 8 weeks, leadership has seen contradictory signals:

New customer acquisition is up +18% (driven by TikTok and paid search).
90-day retention for new cohorts is down from 28% → 22%.
Average order value (AOV) is up $54 → $58, but gross margin % is down due to higher promo usage and substitution costs.

Metric Scenario

You must define CLV in a way that works for:

one-off purchasers (irregular ordering), and
FreshCart+ subscribers (recurring revenue + different cost structure).

You also need to address practical issues: censoring (many customers are still active), seasonality (holidays), and the fact that some revenue is high but margin is negative after promos and refunds.

Data Available

Table	What it contains	Grain
`orders`	order_id, user_id, order_ts, subtotal, delivery_fee, tip, promo_discount, refund_amount, substitutions_cost, payment_status	Order
`order_items`	order_id, sku_id, qty, item_revenue, item_cogs, category	Line item
`subscriptions`	user_id, plan_type, start_ts, cancel_ts, monthly_fee, status	Subscription period
`user_acquisition`	user_id, first_touch_channel, campaign_id, install_ts, first_order_ts	User
`support_contacts`	user_id, contact_ts, issue_type, resolution_cost	Ticket
`delivery_ops`	order_id, courier_cost, distance_miles, batch_flag, late_delivery_flag	Order

What You Need To Produce

Define CLV precisely for FreshCart (state whether you mean revenue CLV or contribution-margin CLV, what time horizon, and what counts as “lifetime”).
Propose two calculation approaches:
- A simple heuristic model that can be explained to executives quickly.
- A cohort-based / retention-based model that is more accurate and handles censoring.
Provide a worked numeric example for each approach using realistic inputs (retention, order frequency, AOV, margin, subscription fees).
Decompose CLV into actionable drivers and identify which drivers likely explain the recent cohort decline.
Recommend 3–5 actions (product, pricing, ops, or marketing) you would take if CLV is falling for TikTok-acquired users, and list guardrail metrics you would monitor.

Constraints:

You have one week to deliver a CLV readout for the next exec meeting.
The model must be usable to set channel-level CAC caps and to evaluate trade-offs (e.g., promos vs retention).

Business Context

Over the last 8 weeks, leadership has seen contradictory signals:

New customer acquisition is up +18% (driven by TikTok and paid search).
90-day retention for new cohorts is down from 28% → 22%.
Average order value (AOV) is up $54 → $58, but gross margin % is down due to higher promo usage and substitution costs.

Metric Scenario

You must define CLV in a way that works for:

one-off purchasers (irregular ordering), and
FreshCart+ subscribers (recurring revenue + different cost structure).

You also need to address practical issues: censoring (many customers are still active), seasonality (holidays), and the fact that some revenue is high but margin is negative after promos and refunds.

Data Available

Table	What it contains	Grain
`orders`	order_id, user_id, order_ts, subtotal, delivery_fee, tip, promo_discount, refund_amount, substitutions_cost, payment_status	Order
`order_items`	order_id, sku_id, qty, item_revenue, item_cogs, category	Line item
`subscriptions`	user_id, plan_type, start_ts, cancel_ts, monthly_fee, status	Subscription period
`user_acquisition`	user_id, first_touch_channel, campaign_id, install_ts, first_order_ts	User
`support_contacts`	user_id, contact_ts, issue_type, resolution_cost	Ticket
`delivery_ops`	order_id, courier_cost, distance_miles, batch_flag, late_delivery_flag	Order

What You Need To Produce

Define CLV precisely for FreshCart (state whether you mean revenue CLV or contribution-margin CLV, what time horizon, and what counts as “lifetime”).
Propose two calculation approaches:
- A simple heuristic model that can be explained to executives quickly.
- A cohort-based / retention-based model that is more accurate and handles censoring.
Provide a worked numeric example for each approach using realistic inputs (retention, order frequency, AOV, margin, subscription fees).
Decompose CLV into actionable drivers and identify which drivers likely explain the recent cohort decline.
Recommend 3–5 actions (product, pricing, ops, or marketing) you would take if CLV is falling for TikTok-acquired users, and list guardrail metrics you would monitor.

Constraints:

You have one week to deliver a CLV readout for the next exec meeting.
The model must be usable to set channel-level CAC caps and to evaluate trade-offs (e.g., promos vs retention).

Interview Guides

Business Context

Metric Scenario

Data Available

What You Need To Produce

Compute CLV for Grocery Subscriptions

Business Context

Metric Scenario

Data Available

What You Need To Produce

Your Answer

Compute CLV for Grocery Subscriptions

Business Context

Metric Scenario

Data Available

What You Need To Produce

Compute CLV for Grocery Subscriptions

Business Context

Metric Scenario

Data Available

What You Need To Produce

Your Answer