Question

The number of active subscribers that Revenuecat gave us does not match the table we created

  • 23 June 2022
  • 1 reply
  • 24 views

Badge

Hello,

We want to create our own dashboards from the raw data that we received from Revenucat. We transfer the data to BigQuery about 1 day behind. When we group monthly active subscribers by month, we cannot match them with the data in the table that revenuecat provides us. Meanwhile, we find minor differences acceptable. Unfortunately the difference is too much.

For example, while the number of active users in May is 1419 in our code, it is 1893 in the active subscriber chart of Revenucat. I am sharing the SQL code I wrote. The logic we have established here, in short, is this. We got price_in_usd > 0 to eliminate trial users so that data is not duplicated. We also set the sandbox area to false to avoid getting sandbox data. We thought that the original_store_transaction_id refreshes itself when creating the store transaction id with every purchase. That's why we thought that when we group and count the original_store_transaction_id by months, we can reach active subscribers. So what am I missing? If you have any suggestions, please feel free to comment. 

Best Regards

 

WITH revenuecat AS(

SELECT
DISTINCT FORMAT_DATE("%Y%m", DATE(start_time)) AS start_time,
FORMAT_DATE("%Y%m", DATE(end_time)) AS end_time,
ROUND(price_in_usd,2) AS price_in_usd,
store_transaction_id,
original_store_transaction_id,
renewal_number
FROM table_name
WHERE
price_in_usd > 0
AND is_sandbox = false
)


SELECT
start_time,
COUNT(original_store_transaction_id) AS org_trans_count
FROM revenuecat
GROUP BY 1
ORDER BY 1

 


1 reply

Userlevel 5
Badge +9

Hey @adnan!

 

We’ve got a sample query to calculate active subscriptions here that may help:
 

-- Active subscriptions
SELECT
COUNT(*)
FROM
transactions
WHERE
end_time > NOW()
AND is_trial_period = FALSE
AND is_in_intro_offer_period = FALSE
AND is_sandbox = FALSE
AND refunded_at IS NULL
AND price != 0;

-- The RevenueCat charts exclude promotional transactions.
-- you can include the following filter to exclude promotional
-- transactions from your queries as well
product_identifier NOT ILIKE 'rc_promo%'

 

To be honest I’m not 100% sure how to plot this over a monthly timeseries that’s beyond my SQL skills but the transaction exports are based on the same underlying data as the charts. 

Can you reproduce the latest active subscriber count before you group it by months? This could help you build confidence in the datasource and start with a query you trust.

I think the original_store_transaction_id approach could work, but you may just be able to count the number of transactions with end_time between a certain range. Since the same subscription should be counted in every month it’s active it only needs one transaction to be active during the month to count toward the active number.

Reply