Your question is Calculate Subscription Customer Lifetime Value. Take a moment with it on the right.
Talk me through your thinking if you like. When you're confident, submit your answer and I'll grade it like a real screen (7/10 or better passes).
StreamFlow is a video subscription product with monthly and annual plans. The CFO wants a consistent customer lifetime value (LTV) definition to evaluate acquisition efficiency and set CAC targets for the next quarter.
In the last 12 months, StreamFlow acquired 120,000 new subscribers: 90,000 monthly-plan users at $20/month and 30,000 annual-plan users at $180/year. Monthly-plan gross monthly churn averages 5%, and annual-plan renewal churn averages 30% at the end of each term. Average payment processing and support costs are $3 per active subscriber per month for monthly users and $24 per active subscriber per year for annual users. Refunds and failed payments reduce recognized revenue by 4% overall. Finance currently reports average revenue per user (ARPU), but growth leaders want a forward-looking LTV metric by acquisition channel and plan type.
subscriptions: subscriber_id, plan_type, start_date, renewal_date, cancel_date, acquisition_channelbilling_transactions: subscriber_id, charge_date, amount, refund_amount, payment_statussubscriber_costs: subscriber_id, month, support_cost, payment_processing_costmarketing_attribution: subscriber_id, acquisition_channel, campaign_id, CACsubscriber_activity: subscriber_id, watch_hours, active_days, device_type