Scheduled Data Exports

Data deliveries of all of your apps' transaction data (formerly ETL Exports)

👍

The Scheduled Data Export integration is available on the Pro plan.

RevenueCat can automatically send data deliveries of all of your apps' transaction data to various cloud storage providers. These are in the form of gzip compressed .csv files delivered daily.

Setup Instructions

Transaction Format

📘

All dates and times are provided in UTC.

HeaderCan be nullComments
rc_original_app_user_idCan be used as a unique user identifier to find all of a user's transactions.
rc_last_seen_app_user_id_aliasCan be used together with rc_original_app_user_id to match transactions with user identifiers in your systems.
countryLast seen country of the subscriber.
product_identifierThe product identifier that was purchased.
start_timePurchase time of transaction.
end_timeExpected expiration time of subscription. Null when is_auto_renewable = false
For Google Play, end_time can be before start_time to indicate an invalid transaction (e.g. billing issue).
grace_period_end_time*Expiration time of a grace period (if applicable) for a subscription. Will remain set while a subscription is in its grace period, or if it exited its grace period without renewing. Null when a subscription is not in a grace period or expiration was not due to a grace period.
effective_end_time*Single reference point of a subscriber’s expiration and entitlement revocation; inclusive of each store’s logic for cancellations, grace periods, etc.
storeThe source of the transaction. Can be app_store, play_store, stripe, or promotional.
is_auto_renewabletrue for auto-renewable subscriptions, false otherwise.
is_trial_periodtrue if the transaction was a trial.
is_in_intro_offer_periodtrue if the transaction is in an introductory offer period.
is_sandboxtrue for transactions made in a sandbox environment.
price_in_usdThe gross revenue generated from the transaction. All prices are converted to USD. Can be null if product prices haven't been collected from the user's device.
takehome_percentageDEPRECATED: The estimated percentage of the transaction price that will be paid out to developers after commissions, but before VAT and DST taxes are taken into account. (will be either 0.7 or 0.85)

We recommend using tax_percentage and commission_percentage to calculate proceeds instead. Learn more here.
tax_percentage*The portion of a transaction’s price that will be deducted by the store for taxes. VAT & Digital Services Taxes may be withheld by stores depending on the store and country. To learn more about how RevenueCat estimates taxes, click here.
commission_percentage*The portion of a transaction’s price that will be detected by the store for commission. In stores where taxes are deducted before commission, this value will not equal the published commission from a store, because that commission is calculated on the post-tax revenue.
store_transaction_idorderId or transaction_identifier. ​Can be used as unique id.
original_store_transaction_idorderId of first purchase or original_transaction_id. Can be used to find all related transactions for a single subscription.
refunded_atWhen a refund was detected, null if none was detected. Can be checked for existence to indicate transactions which have been refunded.
unsubscribe_detected_atWhen we detected an unsubscribe (opt-out of auto renew).
billing_issues_detected_atWhen we detected billing issues, null if none was detected.
purchased_currencyThe currency that was used for the transaction.
price_in_purchased_currencyThe product's price in the currency that was used for the transaction.
entitlement_identifiersAn array of entitlements that the transaction unlocked or null if it didn't unlock any entitlements.
renewal_numberAlways starts at 1. Trial conversions are counted as renewals. is_trial_conversion is used to signify whether a transaction was a trial conversion.
is_trial_conversionIf true, this transaction is a trial conversion.
presented_offeringThe offering presented to users. Can be used to filter Experiment transactions.
ownership_type*Will be PURCHASED when a recorded transaction results from the subscriber’s direct purchase of it, or FAMILY_SHARED when a recorded transaction results from the subscriber having received it through Family Sharing.

NOTE: The FAMILY_SHARED designation is only supported on App Store transactions.
reserved_subscriber_attributesThe reserved subscriber attributes set for the subscriber. Keys begin with $.
custom_subscriber_attributesThe custom attributes set for the subscriber.
platformLast seen platform of the subscriber.

*Available only on our most recent export version

📘

Re-enable integration to update to latest version

If your exports don't contain all of the columns above, you may be on an older export version. To update to the latest version just delete, and re-add the integration from the RevenueCat dashboard.

A note on transaction data

All transaction data is based on the store receipts that RevenueCat has received. Receipts often have inconsistencies and quirks which may need to be considered. For example:

  • The expiration date of a purchase can be before the purchase date. This is Google's way of invalidating a transaction, for example when Google is unable to bill a user some time after a subscription renews. This doesn’t occur on iOS.
  • If you migrated to RevenueCat, Google subscriptions that were expired for more then 60 days before being migrated will not have transaction histories in export files.
  • Apple and Google do not provide the transaction price directly, so we must rely on historical data for the products that we have. This isn’t 100% accurate in cases where the prices were changed or receipts were imported.
  • Renewal numbers start at 1, even for trials. Trial conversions increase the renewal number.
  • Data is pulled from a snapshot of the current receipt state, this means that the same transaction can be different from one delivery to another if something changed, e.g.refunds, and billing issues. You should recompute metrics for past time periods periodically to take these changes into account.

We try to normalize or at least annotate these quirks as much as possible, but by and large we consider receipts as the sources of truth, so any inconsistencies in the transaction data can always be traced back to the receipt

Sample queries for RevenueCat measures

You can use the following sample queries (written in Postgresql) as starting points for reproducing common RevenueCat measures.

-- Active Trials 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 = 'true'
  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 Trials chart excludes
-- promotional transactions and transactions resulting from family sharing
-- since they do not reflect auto-renewing future payments.
-- 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.
-- Revenue generated on [targeted_date]

SELECT
  SUM(price_in_usd) as revenue
FROM
  [revenuecat_data_table]
WHERE 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'

-- Transactions which are refunded can be identified through the refunded_at field.
-- Once refunded, price_in_usd will be set to $0, so revenue will always be net of refunds.

Sample queries for customized measures

Scheduled Data Exports are a powerful way to add your own customizations on top of the core measures provided by RevenueCat. Check out the following sample queries (written in Postgresql) for some ideas.

-- How many Active Subscriptions do I have with a given custom attribute value?
  
SELECT
  you.custom_attribute_key, COUNT(*)
FROM
  [revenuecat_data_table] rc
  
LEFT JOIN [your_data_table] you 
    ON you.rc_original_app_user_id = rc.rc_original_app_user_id
  
WHERE date(rc.effective_end_time) > [targeted_date]
  AND date(rc.start_time) <= [targeted_date]
  AND rc.is_trial_period = 'false'
  AND (rc.effective_end_time IS NULL OR DATE_DIFF('s', rc.start_time, rc.effective_end_time)::float > 0)
  AND rc.ownership_type != 'FAMILY_SHARED'
  AND rc.store != 'promotional'
  AND rc.is_sandbox <> 'true'
  GROUP BY you.custom_attribute_key
-- What is my weekly revenue, where Monday is set as the start day of the week?

SELECT
  date_trunc('week', start_time) as week,
  SUM(price_in_usd) as revenue
FROM
  [revenuecat_data_table]
WHERE date(start_time) BETWEEN [targeted_period_start_date] AND [targeted_period_end_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'
  GROUP BY week
-- What is my Realized LTV of each monthly subscription cohort, segmented by whether they were offered a trial?
  
WITH 
(SELECT
  MIN(start_time) as subscription_start_time,
  original_store_transaction_id,
  MAX(is_trial_period) as had_a_trial,
  SUM(price_in_usd) as realized_ltv
FROM
  [revenuecat_data_table]
WHERE date(start_time) > [targeted_period_start_date]
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'
  GROUP BY original_store_transaction_id) as subscriptions
  
SELECT
  to_char(first_start_time, 'YYYY-MM') as subscription_start_month,
  had_a_trial,
  COUNT() as subscriptions,
  SUM(realized_ltv) as realized_ltv,
  SUM(realized_ltv) / COUNT() as realized_ltv_per_subscription
FROM
  subscriptions
-- What portion of my Active Trials are in a grace period?
  
SELECT
  CASE
    WHEN grace_period_end_time IS NOT NULL THEN 'in_grace_period'
    ELSE 'in_trial_period'
    end_as period_type,
  COUNT(*) as active_trials
FROM
  [revenuecat_data_table]
WHERE date(effective_end_time) > [targeted_date]
  AND date(start_time) <= [targeted_date]
  AND is_trial_period = 'true'
  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'
  GROUP BY period_type