Question

Scheduled Data Export -

  • 2 May 2023
  • 4 replies
  • 178 views
J
Badge

Hi there, I’m having trouble trying to match data from the scheduled csv exports to the data as displayed on revenueCat dashboard. Can someone please assist? I’m not sure where I’m going wrong with my querying. 

For instance, to count actives and actives lost for the month, I count the distinct user id grouped by start date, then for lost I group by unsubscribe_detected_at instead. However, my metrics do not match what is on the dashboard. I’m off by a few hundreds sometimes.

4 replies

Michael Fogel
Rank
Userlevel 4
Badge +6

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
  [revenuecat_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.

 

J
Badge

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.

Michael Fogel
Rank
Userlevel 4
Badge +6

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: 

([Actives Lost] / [Actives]) * 100 = Churn Rate

  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.

 

J
Badge

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.

Reply


Terms & ConditionsCookie settings