I’m trying to replicate the “LTV per paying customers (unbounded)” metric in our data export.
However, my results are consistently wrong in reporting LTV by more than 8% (standard deviation) compared to the “LTV per paying customers” chart in the dashboard.
Could there be an error in my approach, or is it possible to extract the underlying account IDs from the dashboard’s “LTV per paying customers” chart for validation?
| cohort | Mismatch in % |
| 2025-01-31 | 5.10% |
| 2025-02-28 | 4.16% |
| 2025-03-31 | 2.65% |
| 2025-04-30 | -4.40% |
| 2025-05-31 | 6.42% |
| 2025-06-30 | 1.61% |
| 2025-07-31 | 7.87% |
| 2025-08-31 | 11.92% |
| 2025-09-30 | -5.34% |
WITH subscriptions AS (
SELECT
MIN(start_date) as subscription_start_time,
rc_original_app_user_id,
MAX(CASE WHEN subscription_status = 'trial' THEN 1 ELSE 0 END) as had_a_trial,
SUM(price_in_usd) as realized_ltv,
store,
subscription_status
FROM {{ ref('int_store_transaction_id__renewal_number___w_account_label_filtered') }}
WHERE subscription_status != 'trial'
AND store != 'promotional'
-- AND is_sandbox != true
--AND ownership_type != 'FAMILY_SHARED'
GROUP BY rc_original_app_user_id, store, subscription_status
)
SELECT
DATE_TRUNC('month', subscription_start_time) as subscription_start_month,
store,
subscription_status,
COUNT(*) as subscriptions,
SUM(realized_ltv) as realized_ltv,
SUM(realized_ltv) / COUNT(*) as realized_ltv_per_subscription
FROM subscriptions
where subscription_status = 'P1M'
GROUP BY 1,2,3
ORDER BY 1,2,3
