Hi, we have the same problem as @jeanne-2b2c08, could you provide a SQL query to compute Actives Lost please ? we can’t find the same numbers as what is reported in revenueCat charts.

@Michael Fogel So far you redirected to the Amplitude doc, which we also have access to, but here we need more insights please.

Thanks.

Hey There! 

We have some sample queries here which can help you match the information that you are seeing on the dashboard: https://www.revenuecat.com/docs/scheduled-data-exports#sample-queries-for-revenuecat-measures

Below is an example of a query you would run to see the active subscriptions from a selected start date: 

-- Active Subscriptions as of your  targeted_date]

SELECT
  COUNT(*)
FROM
  rrevenuecat_data_table]
WHERE date(effective_end_time) >  targeted_date]
  AND date(start_time) <= &targeted_date]
  AND is_trial_period = 'false'
  AND (effective_end_time IS NULL OR DATE_DIFF('s', start_time, effective_end_time)::float > 0)
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'

-- The RevenueCat Active Subscriptions chart excludes trials,
-- promotional transactions, and transactions resulting from family sharing
-- since they do not reflect auto-renewing future payments.

Hi Michael, 

Thank you very much for the reply!

Would you have an example query to calculate actives lost perhaps please? I am still struggling to match the metrics, and basing off assumptions hasn't returned me accurate numbers.

Hey @jeanne-2b2c08 ,

Unfortunately I dont have a great query to send for churned subscriptions. These can be calculated by the following though. Getting a dataset of the Active subscriptions as well as the Actives lost and then following this formula: 

(

1. Actives: The count of paid, active (unexpired) subscriptions at the beginning of the period.
2. Actives Lost: The count of paid subscriptions that have expired within the period, minus those that have resubscribed.