DirecTV — Customer Lifecycle | OBT Advanced Analytics

Dashboard

Interactive KPI dashboard — the live working version embedded below.

A live preview of the raw sample data used in this analysis. Use the download tab for the full file.

Sheet KPI Summary · showing 8 rows.

KPI	Value	Unit	YoY Change	Trend
Total Active Subscribers	11300000	Subscribers	+4.2%	↑
Average LTV	2847	$	+8.1%	↑
Avg Customer Tenure	6.2	Years	+0.3 yrs	↑
Monthly ARPU	87.4	$	+5.8%	↑
NPS Score	42	Score	+7 pts	↑
Upsell Conversion Rate	23.7	%	+2.1pp	↑
Annual Churn Rate	14.2	%	-1.8pp	↓ (Improving)
Customer Acquisition Cost	187	$	-$12	↓ (Improving)

Sheet Lifecycle Stage Distribution · showing 8 rows.

Stage	Tenure	Subscribers	% of Base	ARPU ($)	Monthly Revenue ($M)	Annual Revenue ($M)	LTV ($)	NPS	Churn Rate %
Acquisition	0-3 months	1200000	0.106194690265487	62	74.4	892.8	248	18	0.062
Onboarding	3-6 months	800000	0.0707964601769912	71	56.8	681.6	425	27	0.048
Activation	6-12 months	1400000	0.123893805309735	79	110.6	1327.2	740	35	0.034
Growth	1-3 years	3100000	0.274336283185841	88	272.8	3273.6	1968	42	0.021
Maturity	3-5 years	2800000	0.247787610619469	94	263.2	3158.4	3480	53	0.016
Advocacy	5+ years	1400000	0.123893805309735	102	142.8	1713.6	7750	61	0.008
At-Risk	Any	600000	0.0530973451327434	55	33	396	330	12	0.28
TOTAL / AVERAGE	—	11300000	1	78.7142857142857	953.6	11443.2	2134.42857142857	35.4285714285714	0.067

Sheet LTV by Acquisition Channel · showing 7 rows.

Acquisition Channel	Avg LTV ($)	Avg Tenure (Years)	Avg ARPU ($)	CAC ($)	LTV:CAC Ratio	% of New Subs	12-Month Retention %
Organic / Referral	4210	8.4	98	42	100.238095238095	0.18	0.91
Bundle / Partnership	3890	7.8	94	67	58.0597014925373	0.24	0.89
Streaming Promo	3240	6.5	87	94	34.468085106383	0.19	0.85
Direct Mail	2980	6.1	84	112	26.6071428571429	0.11	0.82
Digital / CTV	2750	5.8	82	134	20.5223880597015	0.16	0.79
Retail	2110	4.9	74	158	13.3544303797468	0.08	0.74
Win-Back	1870	4.2	68	82	22.8048780487805	0.04	0.72

Sheet Stage Transition Rates · showing 6 rows.

From Stage	To Stage	Progress Rate %	Churn Rate %	Stagnation Rate %	Avg Days in Stage	Trigger Event	Check (=100%)
Acquisition	Onboarding	0.72	0.18	0.1	75	First bill payment	1
Onboarding	Activation	0.68	0.22	0.1	82	App login + content stream	1
Activation	Growth	0.74	0.16	0.1	145	Package add-on	1
Growth	Maturity	0.82	0.08	0.1	540	Autopay enrollment	1
Maturity	Advocacy	0.78	0.12	0.1	720	Referral made	1
Advocacy	(Retained)	0.91	0.04	0.05	1460	NPS 9-10 response	1

Sheet Engagement vs Revenue (Monthly) · showing 24 rows.

Month	Engagement Score (0-100)	Monthly ARPU ($)	Cumulative Revenue ($)	Product Features Adopted	Churn Risk Score
1	45	62	62	1	78
2	52	64	126	1	72
3	61	67	193	2	65
4	68	71	264	2	58
5	72	74	338	3	51
6	78	79	417	3	44
7	76	82	499	3	40
8	80	88	587	4	36
9	79	87	674	4	33
10	81	89	763	4	30
11	80	90	853	5	27
12	82	91	944	5	25
13	79	89	1033	5	24
14	78	88	1121	5	23
15	80	90	1211	5	22
16	77	87	1298	6	21
17	79	89	1387	6	20
18	78	88	1475	6	19
19	80	91	1566	6	18
20	79	90	1656	6	17
21	77	88	1744	6	17
22	75	92	1836	7	15
23	74	93	1929	7	14
24	72	94	2023	7	13

Sheet Product Adoption Funnel · showing 6 rows.

Funnel Stage	Subscribers	Conversion Rate %	Drop-Off	Cumulative % Retained	Key Action
All Subscribers	11300000	—	—	1	Onboarded
App Downloaded	8927000	0.79	2373000	0.79	Download app
Streamed Content	7231000	0.810014562563011	1696000	0.639911504424779	First stream session
Added Package	3832000	0.529940533812751	3399000	0.339115044247788	Upsell completed
Referred Friend	1609000	0.419885177453027	2223000	0.142389380530973	Referral sent
Brand Advocate	903000	0.561218147917961	706000	0.0799115044247788	NPS 9-10 given

Sheet NPS by Tenure Cohort · showing 10 rows.

Tenure (Months)	NPS Score	Detractors %	Passives %	Promoters %	Sample Size	NPS Check (=Promoters-Detractors)
3	18	0.31	0.2	0.49	12400	18
6	27	0.26	0.21	0.53	10800	27
12	35	0.22	0.21	0.57	9600	35
18	39	0.2	0.21	0.59	8900	39
24	42	0.18	0.22	0.6	8100	42
36	48	0.15	0.22	0.63	7400	48
48	53	0.13	0.21	0.66	6200	53
60	58	0.11	0.2	0.69	5100	58
72	61	0.1	0.19	0.71	3800	61
84	64	0.09	0.18	0.73	2600	64

Source artefacts behind this analysis — case study writeups, raw data, and deliverable templates.

XLSX

DirecTV_Customer_Lifecycle_Data.xlsx Excel Data · ~18 KB Download →