AI agents: the complete RevenueCat documentation index is available at llms.txt. A Markdown version of this page is available at this URL.

Transactions feed

Columns, sample queries, and handling guidance for the Transactions Scheduled Data Export feed

Ask Rico

Copied as markdown!

This page lists the columns of the Transactions feed, explains how to handle updated transactions, and gives sample queries. For setup, delivery frequency, and file formats, see Scheduled Data Exports.

The Transactions feed exports per-transaction Customer and revenue data. RevenueCat delivers it to <base>/<date>/transactions_<timestamp>.<ext>. It's an incremental feed: with Receive new and updated transactions only enabled, each delivery includes just the rows that changed since the last one.

Columns

New integrations start with a default selection of the most commonly used columns; you can adjust the selection from the column catalog in the dashboard. For step-by-step instructions, see Selecting columns for an export.

All dates and times are in UTC.

Header	Description	Type	Example value	Can be null
rc_original_app_user_id	Can be used as a unique Customer identifier to find all of a Customer's transactions.	string	$RCAnonymousID:87c6049c58069238dce29853916d624c	No
rc_last_seen_app_user_id_alias	Can be used together with rc_original_app_user_id to match transactions with Customer identifiers in your systems.	string	$RCAnonymousID:87c6049c58069238dce29853916d624c	No
country	Store country of a transaction when known, or an IP-based estimate of a Customer's country when not known.	string	GB	Yes
country_source	from_sdk when the store country of a transaction is known, or estimated when country is sourced from an IP-based estimate.	string	from_sdk	Yes
product_identifier	The product identifier that was purchased. For Stripe, this can be a Stripe Price ID, Stripe Product ID, or manually specified identifier, depending on how the Product was imported or created. Learn how Stripe identifiers appear in exports.	string	price_1RzS9bQW6xN4cT2mY7kL8pA0	No
product_display_name	The display name of the product identifier if one has been set	string	Monthly $9.99	Yes
product_duration	The standard duration of the product if one is known by RevenueCat. May be null if RevenueCat does not know the authoritative duration. product_duration does not represent the trial or introductory period length of a transaction, it only represents the standard duration of the product that's been subscribed to.	string	P1M	Yes
start_time	Purchase time of transaction.	datetime	2023-01-01 08:27:06	No
end_time	Expected 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).	datetime	2023-02-01 08:27:06	Yes
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.	datetime	2023-02-17 08:27:06	Yes
effective_end_time	Single reference point of a Customer’s expiration and entitlement revocation; inclusive of each store’s logic for refunds, grace periods, etc.	datetime	2023-02-17 08:27:06	Yes
store	The source of the transaction. Can be app_store, play_store, stripe, or promotional.	string	play_store	No
is_auto_renewable	true for auto-renewable subscriptions, false otherwise.	boolean	true	No
is_trial_period	true if the transaction was a trial.	boolean	false	No
is_in_intro_offer_period	true if the transaction is in an introductory offer period.	boolean	false	No
is_sandbox	true for transactions made in a sandbox environment.	boolean	false	No
price_in_usd	The revenue (converted to USD) generated from the transaction after accounting for full and partial refunds. Can be null if product prices haven't been collected from the user's device.	float	0	Yes
purchase_price_in_usd	The gross revenue (converted to USD) generated from the transaction. Remains set for refunded transactions. Can be null if product prices haven't been collected from the user's device.	float	9.99	Yes
takehome_percentage	[DEPRECATED] 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.	float	0.7	No
tax_percentage	The portion of a transaction’s price that will be deducted by the store for taxes. VAT and Digital Services Taxes may be withheld by stores depending on the store and country. To learn more about how RevenueCat estimates taxes, click here.	float	0.1442	No
commission_percentage	The portion of a transaction's price that will be deducted 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.	float	0.15	No
store_transaction_id	orderId or transaction_identifier.	string	123456789012345	No
original_store_transaction_id	orderId of first purchase or original_transaction_id. Can be used to find all related transactions for a single subscription.	string	011223344556677	No
refunded_at	When a refund was detected, null if none was detected. Is not set in the case of upgraded transactions for which the App Store issues a partial refund.	datetime	2023-02-20 05:47:55	Yes
unsubscribe_detected_at	When we detected an unsubscribe (opt-out of auto renew).	datetime	2023-02-16 14:17:10	Yes
billing_issues_detected_at	When we detected billing issues, null if none was detected.	datetime	2023-02-01 08:27:15	Yes
purchased_currency	The currency that was used for the transaction.	string	GBP	Yes
price_in_purchased_currency	The revenue (in the purchased currency) generated from the transaction after accounting for full and partial refunds. Can be null if product prices haven't been collected from the user's device.	float	0	Yes
purchase_price_in_purchased_currency	The gross revenue (in the purchased currency) generated from the transaction. Remains set for refunded transactions. Can be null if product prices haven't been collected from the user's device.	float	3.99	Yes
entitlement_identifiers	An array of entitlements that the transaction unlocked or null if it didn't unlock any entitlements.	string array	["membership", "full_access"]	Yes
renewal_number	Always starts at 1. Trial conversions are counted as renewals. is_trial_conversion is used to signify whether a transaction was a trial conversion.	integer	2	No
is_trial_conversion	If true, this transaction is a trial conversion.	boolean	true	No
presented_offering	The offering presented to users.	string	Default Offering	Yes
ownership_type	Will be PURCHASED when a recorded transaction results from the Customer’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.	string	PURCHASED	Yes
reserved_subscriber_attributes	The reserved attributes set for the Customer. Keys begin with $.	string JSON	{"$ip": {"value": "203.78.120.117", "updated_at_ms": 1672549200}, "$gpsAdId": {"value": "80480bdc-06e0-11ee-be56-0242ac120002", "updated_at_ms": 1672549200}, "$androidId": {"value": "12345a9876b4c123", "updated_at_ms": 1673097132390}}	Yes
custom_subscriber_attributes	The custom attributes set for the Customer.	string JSON	{"feature_setting": {"value": "1", "updated_at_ms": 1672549200}, "survey_response": {"value": "2", "updated_at_ms": 1599112814785}}	Yes
platform	Last seen platform of the Customer.	string	android	Yes
updated_at	The last time an attribute of the transaction was modified.	datetime	2023-02-20 05:47:55	No
offer	The offer that was used for a transaction (if applicable).	string	black_friday_discount	Yes
offer_type	The type of offer that was used for a transaction (if applicable).	string	offer_code	Yes
first_seen_time	The time the customer was first seen by RevenueCat.	datetime	2023-01-01 03:00:00	No
first_seen_or_purchased_time	Earliest of RevenueCat first seen, App Store install, first purchase, and Customer created_at. Matches Charts, Customer Profile, and the audience First seen or purchased filter. Not selected by default.	datetime	2023-01-01 03:00:00	Yes
auto_resume_time	The time when a Google Play subscription would resume after being paused.	datetime	2023-03-20 03:00:00	Yes
cancel_survey_reason	Google Play cancel survey reason. One of Other reasons, Not enough usage, Technical issues, Cost, Found a better app, or Unspecified. Null for non-Google Play transactions. Not selected by default.	string	Technical issues	Yes
enrolled_experiments_by_layer	Object containing experiment enrollment information for the Customer, keyed by layer. Replaces the deprecated experiment_id and experiment_variant columns.	string JSON	{"layer_name": [{"experiment_id": "prexpaaaaaaaaaa", "variant": "a", "enrolled_at": "1970-01-01 00:00:00"}]}	Yes
payment_objects	Array of external payments for the transaction. Each object has processed_at, currency, amount_in_currency, tax_in_currency, commission_in_currency, amount_in_usd, tax_in_usd, commission_in_usd, payment_identifier, source_identifier, country, and rc_public_id. Not selected by default.	string JSON	[{"processed_at": "2023-01-01 08:27:06", "currency": "GBP", "amount_in_currency": 3.99, "amount_in_usd": 4.8, "country": "GB"}]	Yes

How do Stripe identifiers appear in exports?

For a Stripe Product configured with a specific Price, product_identifier contains the exact Stripe Price ID (price_...). Stripe generates opaque Price IDs, so the value does not describe the Price's interval, amount, or parent Product. It does not contain the parent Stripe Product ID (prod_...), and RevenueCat does not add a separate column for the parent Product ID.

The identifier remains consistent with the Price attributed to the transaction throughout its lifecycle:

Transaction state	product_identifier
Initial purchase using a price-specific Product	Purchased Stripe Price ID
Renewal using the same Price	Same Stripe Price ID
Transaction updated after a Price change	New Stripe Price ID
Cancellation or refund	Stripe Price ID already attributed to the transaction; the cancellation or refund fields change instead

Expect mixed Stripe identifiers in the same export:

• prod_...: Stripe Products imported before RevenueCat supported Price-level imports are identified by their parent Stripe Product ID. Their transactions export prod_... in product_identifier.
• Manually specified: Stripe Products created manually in RevenueCat are identified by the product_identifier value specified when they were created. Their transactions export that exact value.
• price_...: Price-specific Stripe Products use the Stripe Price ID and export price_....

RevenueCat doesn't migrate existing mappings automatically, so the same export can contain prod_..., price_..., and manually specified identifiers.

If your export pipeline validates or joins product_identifier, update it to accept every identifier format configured in your project. To group price-specific transactions by their parent Stripe Product, resolve each Price ID through your Stripe product catalog. For manually created Products, use the configured product_identifier in your mapping. Use product_display_name when you need the display name configured in RevenueCat.

A Price change can cause an existing transaction to be exported again with an updated product_identifier. Follow the guidance in Handling updated transactions correctly when processing incremental deliveries.

Known quirks in transaction data

All transaction data is based on the store receipts that RevenueCat has received. Receipts often have inconsistencies and quirks that you may need to account for. 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 Customer some time after a subscription renews. This doesn't occur on iOS.
• If you migrated to RevenueCat, Google subscriptions that were expired for more than 60 days before being migrated won't have transaction histories in export files.
• Apple and Google don't always provide the transaction price directly, so we rely on historical data and store APIs. This may result in inaccuracies if receipts were imported, or if a product price was increased before your App Store Connect API Key was added.
• 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 the same transaction can differ from one delivery to another if something changed (for example, due to a refund or billing issue). Recompute metrics for past time periods periodically to account for these changes, and use the updated_at field to detect whether a transaction changed since a prior export.
• Data is current as of when the export begins generating. Changes that occur between the start of generation and delivery aren't reflected in that export.

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

Handling updated transactions correctly

Keep Receive new and updated transactions only enabled to significantly reduce the amount of data you need to process in each daily export.

Handling transaction updates can be tricky. These tips make it easier:

1. For most stores, store_transaction_id will be unique for each transaction, but for Stripe it is not; so for best results we recommend treating every unique set of [store_transaction_id + renewal_number] as a unique transaction.
2. Instead of overwriting prior transaction states when receiving an updated transaction, consider adding them as new rows to your output table and setting a property like is_latest to ensure you're never double-counting different versions of the same transaction. Or, you could set an ingested_time property to order the transactions by the most recent version you received from RevenueCat.
3. When in doubt, use updated_at (provided by RevenueCat in your export) as a reference point to determine the latest version of a transaction if you have multiple prior versions and can't otherwise confidently determine which one is latest.

Sample queries for RevenueCat measures

These sample queries use the Transactions feed. They're written in PostgreSQL and serve as starting points for reproducing common RevenueCat measures.

Active Subscriptions

-- Active Subscriptions as of a specified 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 DATE_DIFF('s', start_time, 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.

Active Subscriptions Movement

-- Active Subscriptions Movement within a specified date range

WITH

filtered_subscription_transactions AS (
    SELECT
        *
    FROM [revenuecat_data_table]
    /* Filter down to the date range that you want to measure MRR Movement for */
    WHERE (start_time BETWEEN [targeted_start_date] and [targeted_end_date] 
        OR effective_end_time BETWEEN [targeted_start_date] and [targeted_end_date])
        /* Exclude trials, which do not contribute to MRR */
        AND is_trial_period = 'false'
        AND DATE_DIFF('s', start_time, end_time)::float > 0
        AND ownership_type != 'FAMILY_SHARED'
        AND store != 'promotional'
        AND is_sandbox != 'true'),

actives AS (
  SELECT
    DATE(start_time) AS date,
    COUNT(
        CASE 
            WHEN renewal_number = 1
                OR is_trial_conversion = 'true'
            THEN 1 
            ELSE NULL 
        END) AS num_new_actives,
    COUNT(
        CASE 
            WHEN renewal_number > 1
                AND is_trial_conversion = 'false'  
            THEN 1 
            ELSE NULL 
        END
    ) AS num_renewals
    
  FROM filtered_subscriptipon_transactions
  GROUP BY 1),
  
expirations AS (
  SELECT
    DATE(effective_end_time) AS date,
    COUNT(*) AS num_expirations
  FROM filtered_subscriptipon_transactions
  GROUP BY 1)

SELECT
    COALESCE(a.date, e.date) AS date,
    COALESCE(a.num_new_actives, 0) AS new_actives, /* "New Actives" in the Active Subscriptions Movement Chart */
    COALESCE(a.num_renewals, 0) AS num_renewals,
    COALESCE(e.num_expirations, 0) AS num_expirations,
    num_expirations - num_renewals AS churned_actives, /* "Churned Actives" in the Active Subscriptions Movement Chart */
FROM actives a
FULL JOIN expirations e ON a.date = e.date
WHERE a.date BETWEEN [targeted_start_date] AND [targeted_end_date]
    AND e.date BETWEEN [targeted_start_date] AND [targeted_end_date]

MRR

-- MRR as of a specified date

SELECT
    SUM(
        CASE WHEN effective_end_time IS NOT NULL THEN
            CASE 
                /* Handle cases where product_duration cannot be used for the transaction first */
                WHEN (is_in_intro_offer_period = 'true' OR product_duration IS NULL) THEN 
                CASE
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 0 AND 1 
                        THEN (30 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) = 3 
                        THEN (10 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 6 AND 8 
                        THEN (4 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 12 AND 16 
                        THEN (2 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 27 AND 33 
                        THEN (1 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 58 AND 62 
                        THEN (0.5 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 88 AND 95 
                        THEN (0.333333 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 179 AND 185 
                        THEN (0.1666666 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 363 AND 375 
                        THEN (0.08333 * price)::DECIMAL(18,2)
                    ELSE ((28 / (DATE_DIFF('s', start_time, end_time)::float / (24 * 3600))) * price)::DECIMAL(18,2)
                END
                /* Then handle cases where product_duration can be used */
                WHEN product_duration = 'P1D' 
                    THEN (30 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P3D' 
                    THEN (10 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P7D' 
                    THEN (4 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P1W' 
                    THEN (4 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P2W' 
                    THEN (2 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P4W' 
                    THEN (1 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P1M' 
                    THEN (1 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P2M' 
                    THEN (0.5 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P3M' 
                    THEN (0.333333 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P6M' 
                    THEN (0.1666666 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P12M' 
                    THEN (0.08333 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P1Y' 
                    THEN (0.08333 * price)::DECIMAL(18,2)
                ELSE ((28 / (DATE_DIFF('s', start_time, end_time)::float / (24 * 3600))) * price)::DECIMAL(18,2)
            END
        END 
    ) AS active_mrr
FROM [revenuecat_data_table] 

/* Filter down to the date range that you want to measure MRR for */
WHERE date(effective_end_time) > '2024-02-06'
  AND date(start_time) <= '2024-02-06'
  /* Exclude trials, which do not contribute to MRR */
  AND is_trial_period = 'false'
  AND DATE_DIFF('s', start_time, end_time)::float > 0
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'

MRR Movement

-- MRR Movement for a specified date range

WITH

filtered_subscription_transactions AS (
    SELECT
        *,
        CASE WHEN effective_end_time IS NOT NULL THEN
            CASE 
                /* Handle cases where product_duration cannot be used for the transaction first */
                WHEN (is_in_intro_offer_period = 'true' OR product_duration IS NULL) THEN 
                CASE
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 0 AND 1 
                        THEN (30 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) = 3 
                        THEN (10 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 6 AND 8 
                        THEN (4 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 12 AND 16 
                        THEN (2 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 27 AND 33 
                        THEN (1 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 58 AND 62 
                        THEN (0.5 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 88 AND 95 
                        THEN (0.333333 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 179 AND 185 
                        THEN (0.1666666 * price)::DECIMAL(18,2)
                    WHEN DATE_DIFF(day, start_time, end_time) BETWEEN 363 AND 375 
                        THEN (0.08333 * price)::DECIMAL(18,2)
                    ELSE ((28 / (DATE_DIFF('s', start_time, end_time)::float / (24 * 3600))) * price)::DECIMAL(18,2)
                END
                /* Then handle cases where product_duration can be used */
                WHEN product_duration = 'P1D' THEN (30 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P3D' THEN (10 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P7D' THEN (4 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P1W' THEN (4 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P2W' THEN (2 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P4W' THEN (1 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P1M' THEN (1 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P2M' THEN (0.5 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P3M' THEN (0.333333 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P6M' THEN (0.1666666 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P12M' THEN (0.08333 * price)::DECIMAL(18,2)
                WHEN product_duration = 'P1Y' THEN (0.08333 * price)::DECIMAL(18,2)
                ELSE ((28 / (DATE_DIFF('s', start_time, end_time)::float / (24 * 3600))) * price)::DECIMAL(18,2)
            END
        END AS transaction_mrr
    FROM [revenuecat_data_table]
    /* Filter down to the date range that you want to measure MRR Movement for */
    WHERE (start_time BETWEEN [targeted_start_date] and [targeted_end_date] 
        OR effective_end_time BETWEEN [targeted_start_date] and [targeted_end_date])
        /* Exclude trials, which do not contribute to MRR */
        AND is_trial_period = 'false'
        AND DATE_DIFF('s', start_time, end_time)::float > 0
        AND ownership_type != 'FAMILY_SHARED'
        AND store != 'promotional'
        AND is_sandbox != 'true'),

actives AS (
  SELECT
    DATE(start_time) AS date,
    SUM(
        CASE
            WHEN renewal_number = 1
                OR is_trial_conversion = 'true' 
            THEN transaction_mrr
            ELSE null
        END
    ) AS new_mrr,
    
    SUM(
        CASE
            WHEN renewal_number > 1 
                AND is_trial_conversion = 'false' 
            THEN transaction_mrr
            ELSE null
        END
    ) AS renewal_mrr
    
  FROM filtered_subscription_transactions
  GROUP BY 1),
  
expirations AS (
  SELECT
    DATE(effective_end_time) AS date,
    SUM(transaction_mrr) AS expired_mrr
  FROM filtered_subscription_transactions
  GROUP BY 1)

SELECT
    COALESCE(a.date, e.date) AS date,
    COALESCE(a.new_mrr, 0) AS new_mrr, /* "New MRR" in the MRR Movement Chart */
    COALESCE(a.renewal_mrr, 0) as renewal_mrr,
    COALESCE(e.expired_mrr, 0) as expired_mrr,
    expired_mrr - renewal_mrr as churned_mrr /* "Churned MRR" in the MRR Movement Chart */
FROM actives a
FULL JOIN expirations e ON a.date = e.date
WHERE a.date BETWEEN [targeted_start_date] AND [targeted_end_date]
    AND e.date BETWEEN [targeted_start_date] AND [targeted_end_date]

Revenue

-- Revenue generated on a specified date

SELECT
  SUM(purchase_price_in_usd) as gross_revenue,
  SUM(price_in_usd) as revenue_net_of_refunds, /* "Revenue" in the Revenue Chart */
  SUM(price_in_usd * (1 - tax_percentage)) as revenue_net_of_taxes, /* "Revenue (net of taxes)" in the Revenue Chart */
  SUM(price_in_usd * (1 - tax_percentage - commission_percentage)) as proceeds /* "Proceeds" in the Revenue Chart */
FROM
  [revenuecat_data_table]
WHERE date(start_time) = [targeted_date]
  AND is_trial_period = 'false'
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'

-- Transactions which have been refunded can be identified through the refunded_at field.

Subscription Retention

-- Subscription Retention per product

WITH filtered_transactions AS (
    SELECT
        *
    FROM [revenuecat_data_table] rc
    WHERE end_time IS NOT NULL /* only include subscriptions */
        AND NOT (billing_issues_detected_at IS NOT NULL
            AND (store = 'play_store' OR store = 'stripe'))
        AND is_sandbox <> 'true'
        AND is_trial_period = 'false'
        AND is_in_intro_offer_period = 'false' /* exclude introductory offers */
        AND ownership_type = 'PURCHASED'
),
  
subs AS (
    SELECT
        ft.rc_original_app_user_id,
        product_identifier,
        product_duration,
        DATE(MIN(ft.start_time)) AS first_start_time, /* first start time is used to define each cohort */
        MAX(DATE_DIFF('day', start_time, end_time)) as max_transaction_duration
    FROM filtered_transactions ft
    GROUP BY 1, 2, 3
),

/* some products don't have a set duration, so this CTE will calculate the duration on a per subscription basis */
calculated_product_duration AS (
    SELECT
        rc_original_app_user_id,
        product_identifier,
        CASE
            WHEN product_duration IS NOT NULL THEN 
            CASE
                WHEN product_duration = 'P7D' THEN 'P1W'
                WHEN product_duration = 'P30D' THEN 'P1M'
                WHEN product_duration = 'P4W' THEN 'P1M'
                WHEN product_duration = 'P12M' THEN 'P1Y'
                WHEN product_duration = 'P200Y' THEN 'lifetime'
                WHEN product_duration = 'P999Y' THEN 'lifetime'
            ELSE product_duration
            END
        ELSE 
            CASE
                WHEN max_transaction_duration BETWEEN 0 AND 1 THEN 'P1D'
                WHEN max_transaction_duration = 3 THEN 'P3D'
                WHEN max_transaction_duration BETWEEN 6 AND 8 THEN 'P1W'
                WHEN max_transaction_duration BETWEEN 12 AND 16 THEN 'P2W'
                WHEN max_transaction_duration BETWEEN 27 AND 37 THEN 'P1M'
                WHEN max_transaction_duration BETWEEN 58 AND 62 THEN 'P2M'
                WHEN max_transaction_duration BETWEEN 88 AND 95 THEN 'P3M'
                WHEN max_transaction_duration BETWEEN 179 AND 185 THEN 'P6M'
                WHEN max_transaction_duration BETWEEN 363 AND 375 THEN 'P1Y'
            ELSE NULL
            END
        END AS calculated_product_duration
    FROM subs s
    GROUP BY 1, 2, 3
),
  
retention AS (
    SELECT
        subs.first_start_time,
        subs.product_identifier,
        cpd.calculated_product_duration,
        /* Each period number represents the number of billing cycles the subscriber was active for */
        CASE
            WHEN calculated_product_duration = 'P1D' THEN DATE_DIFF('day', subs.first_start_time, start_time)
            WHEN calculated_product_duration = 'P1W' THEN DATE_DIFF('week', subs.first_start_time, start_time)
            WHEN calculated_product_duration = 'P1M' THEN CAST(ROUND(DATE_DIFF('day', subs.first_start_time, start_time) / CAST(30 AS NUMERIC)) AS INTEGER)
            WHEN calculated_product_duration = 'P2M' THEN CAST(ROUND(DATE_DIFF('day', subs.first_start_time, start_time) / CAST(60 AS NUMERIC)) AS INTEGER)
            WHEN calculated_product_duration = 'P3M' THEN CAST(ROUND(DATE_DIFF('day', subs.first_start_time, start_time) / CAST(90 AS NUMERIC)) AS INTEGER)
            WHEN calculated_product_duration = 'P6M' THEN CAST(ROUND(DATE_DIFF('day', subs.first_start_time, start_time) / CAST(180 AS NUMERIC)) AS INTEGER)
            WHEN calculated_product_duration = 'P1Y' THEN CAST(ROUND(DATE_DIFF('month', subs.first_start_time, start_time) / CAST(12 AS NUMERIC)) AS INTEGER)
        END AS period_number,
        count(1) AS subscriptions
    FROM filtered_transactions ft
    INNER JOIN subs ON 
        subs.rc_original_app_user_id = ft.rc_original_app_user_id AND
        subs.product_identifier = ft.product_identifier
    INNER JOIN calculated_product_duration cpd ON 
        cpd.rc_original_app_user_id = ft.rc_original_app_user_id AND
        cpd.product_identifier = ft.product_identifier
    WHERE period_number IS NOT NULL
    GROUP BY 1, 2, 3, 4
),
  
pending_retention AS (
    SELECT
        subs.first_start_time,
        subs.product_identifier,
        cpd.calculated_product_duration,
        CASE
            WHEN calculated_product_duration = 'P1D' THEN DATE_DIFF('day', subs.first_start_time, start_time) + 1
            WHEN calculated_product_duration = 'P1W' THEN DATE_DIFF('week', subs.first_start_time, start_time) + 1
            WHEN calculated_product_duration = 'P1M' THEN CAST(ROUND(DATE_DIFF('day', subs.first_start_time, start_time) / CAST(30 AS NUMERIC)) AS INTEGER) + 1
            WHEN calculated_product_duration = 'P2M' THEN CAST(ROUND(DATE_DIFF('day', subs.first_start_time, start_time) / CAST(60 AS NUMERIC)) AS INTEGER) + 1
            WHEN calculated_product_duration = 'P3M' THEN CAST(ROUND(DATE_DIFF('day', subs.first_start_time, start_time) / CAST(90 AS NUMERIC)) AS INTEGER) + 1
            WHEN calculated_product_duration = 'P6M' THEN CAST(ROUND(DATE_DIFF('day', subs.first_start_time, start_time) / CAST(180 AS NUMERIC)) AS INTEGER) + 1
            WHEN calculated_product_duration = 'P1Y' THEN CAST(ROUND(DATE_DIFF('month', subs.first_start_time, start_time) / CAST(12 AS NUMERIC)) AS INTEGER) + 1
        END AS period_number,
        count(1) AS subscriptions
    FROM filtered_transactions ft
    INNER JOIN subs ON 
        subs.rc_original_app_user_id = ft.rc_original_app_user_id AND
        subs.product_identifier = ft.product_identifier
    INNER JOIN calculated_product_duration cpd ON 
        cpd.rc_original_app_user_id = ft.rc_original_app_user_id AND
        cpd.product_identifier = ft.product_identifier
    WHERE unsubscribe_detected_at IS NULL /* count only subscriptions that are set to renew */
        AND
            ((calculated_product_duration = 'P1D' AND DATE_ADD(start_time, '1 day') > CURRENT_DATE)
            OR (calculated_product_duration = 'P1W' AND DATE_ADD(start_time, '1 week') > CURRENT_DATE)
            OR (calculated_product_duration = 'P1M' AND DATE_ADD(start_time, '1 month') > CURRENT_DATE)
            OR (calculated_product_duration = 'P2M' AND DATE_ADD(start_time, '2 months') > CURRENT_DATE)
            OR (calculated_product_duration = 'P3M' AND DATE_ADD(start_time, '3 months') > CURRENT_DATE)
            OR (calculated_product_duration = 'P6M' AND DATE_ADD(start_time, '6 months') > CURRENT_DATE)
            OR (calculated_product_duration = 'P1Y' AND DATE_ADD(start_time, '1 year') > CURRENT_DATE))
        AND period_number IS NOT NULL
    GROUP BY 1, 2, 3, 4
)

SELECT
    COALESCE(retention.first_start_time, pending_retention.first_start_time) AS first_start_date,
    COALESCE(retention.calculated_product_duration, pending_retention.calculated_product_duration) AS product_duration,
    COALESCE(retention.product_identifier, pending_retention.product_identifier) AS product_identifier,
    COALESCE(retention.period_number, pending_retention.period_number) AS period_number,
    CASE
        WHEN retention.period_number = 0 THEN 'Subscriptions'
        WHEN retention.calculated_product_duration = 'P1Y' THEN CONCAT('Year ', retention.period_number)
        WHEN retention.calculated_product_duration = 'P6M' THEN CONCAT('Month ', 6 * retention.period_number)
        WHEN retention.calculated_product_duration = 'P3M' THEN CONCAT('Month ', 3 * retention.period_number)
        WHEN retention.calculated_product_duration = 'P2M' THEN CONCAT('Month ', 2 * retention.period_number)
        WHEN retention.calculated_product_duration = 'P1M' THEN CONCAT('Month ', retention.period_number)
        WHEN retention.calculated_product_duration = 'P1W' THEN CONCAT('Week ', retention.period_number)
        WHEN retention.calculated_product_duration = 'P1D' THEN CONCAT('Day ', retention.period_number)
    ELSE CONCAT('Period ', retention.period_number)
    END AS period_name,
    COALESCE(retention.subscriptions, 0) + COALESCE(pending_retention.subscriptions, 0) AS subscriptions
FROM retention
FULL OUTER JOIN pending_retention ON
    pending_retention.first_start_time = retention.first_start_time AND
    pending_retention.calculated_product_duration = retention.calculated_product_duration AND
    pending_retention.product_identifier = retention.product_identifier AND
    pending_retention.period_number = retention.period_number
WHERE first_start_date >= /* desired date range */
ORDER BY product_identifier, first_start_date, period_number

Sample queries for customized measures

These examples also use the Transactions feed. Use them as ideas for adding your own customizations on top of the core measures provided by RevenueCat. They're written in PostgreSQL.

Active Subs by Custom Attribute

-- How many Active Subscriptions do I have with a given custom attribute value?
  
SELECT
  COUNT(*)
FROM
  [revenuecat_data_table] rc
  
WHERE date(effective_end_time) > [targeted_date]
  AND date(start_time) <= [targeted_date]
  AND is_trial_period = 'false'
  AND DATE_DIFF('s', start_time, end_time)::float > 0
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'
  AND json_extract_path_text(custom_subscriber_attributes, '[custom_attribute_key].value') = [custom_attribute_value]

Active Subs by Auto Renew Status

-- What is my split of Active Subs by auto renew status?
  
SELECT
  CASE 
    WHEN unsubscribe_detected_at IS NOT NULL THEN 'Set to cancel' 
    ELSE 'Set to renew' 
  END as auto_renew_status,
  COUNT(*) as active_subscriptions
FROM
  [revenuecat_data_table]
  
WHERE date(effective_end_time) > [targeted_date]
  AND date(start_time) <= [targeted_date]
  AND is_trial_period = 'false'
  AND DATE_DIFF('s', start_time, end_time)::float > 0
  AND ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'
  GROUP BY 1

Weekly Revenue (starting Monday)

-- 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 total_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 ownership_type != 'FAMILY_SHARED'
  AND store != 'promotional'
  AND is_sandbox != 'true'
GROUP BY week

Realized LTV Segments

-- 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
  date_trunc('month', subscription_start_time) 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

Active Trials by Grace Period Status

-- 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 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

Realized LTV Per Paying Customer by First Purchase Date

-- What is my Realized LTV per Paying Customer cohorted by First Purchase Date?
  
WITH filtered_transactions AS
  (SELECT *
  FROM [revenuecat_data_table]
  WHERE is_trial_period = 'false'
    AND ownership_type != 'FAMILY_SHARED'
    AND store != 'promotional'
    AND is_sandbox != 'true'
    AND refunded_at IS NULL
    AND price > 0),

first_purchase_dates AS
  (SELECT
    rc_original_app_user_id,
    MIN(start_time) as first_purchase_date
  FROM filtered_transactions
  GROUP BY 1)

SELECT

  DATE(fpd.first_purchase_date) AS first_purchase_date,
  COUNT(DISTINCT rc_original_app_user_id) AS paying_customers,
  SUM(CASE WHEN DATEADD(day, 7, first_purchase_date) > start_time 
    THEN price_in_usd ELSE 0 END)::DECIMAL(18,2) AS total_ltv_7_days,
  SUM(CASE WHEN DATEADD(day, 30, first_purchase_date) > start_time 
    THEN price_in_usd ELSE 0 END)::DECIMAL(18,2) AS total_ltv_30_days,
  SUM(CASE WHEN DATEADD(month, 6, first_purchase_date) > start_time 
    THEN price_in_usd ELSE 0 END)::DECIMAL(18,2) AS total_ltv_6_months,
  SUM(CASE WHEN DATEADD(month, 12, first_purchase_date) > start_time 
    THEN price_in_usd ELSE 0 END)::DECIMAL(18,2) AS total_ltv_12_months,
  SUM(CASE WHEN DATEADD(month, 24, first_purchase_date) > start_time 
    THEN price_in_usd ELSE 0 END)::DECIMAL(18,2) AS total_ltv_24_months,
  SUM(price_in_usd)::DECIMAL(18,2) AS total_ltv_unbounded,

  (SUM(CASE WHEN DATEADD(day, 7, first_purchase_date) > start_time 
    THEN price_in_usd ELSE 0 END)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_7_days,
  (SUM(CASE WHEN DATEADD(day, 30, first_purchase_date) > start_time 
    THEN price_in_usd ELSE 0 END)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_30_days,
  (SUM(CASE WHEN DATEADD(month, 6, first_purchase_date) > start_time 
    THEN price_in_usd ELSE 0 END)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_6_months,
  (SUM(CASE WHEN DATEADD(month, 12, first_purchase_date) > start_time 
    THEN price_in_usd ELSE 0 END)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_12_months,
  (SUM(CASE WHEN DATEADD(month, 23, first_purchase_date) > start_time 
    THEN price_in_usd ELSE 0 END)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_24_months,
  (SUM(price_in_usd)/COUNT(DISTINCT rc_original_app_user_id))::DECIMAL(18,2) AS avg_ltv_unbounded

FROM filtered_transactions ft
LEFT JOIN first_purchase_dates fpd 
  ON fpd.rc_original_app_user_id = ft.rc_original_app_user_id
GROUP BY 1

Trial Conversion Rate by Trial End Date

-- What was the conversion-to-paid of all trials that ended each day?

WITH trials AS
(SELECT
  rc_original_app_user_id,
  DATE(effective_end_time) AS end_time
  FROM [revenuecat_data_table] 
  WHERE is_trial_period
),

conversions AS
(SELECT
  rc_original_app_user_id,
  DATE(start_time) AS start_time
  FROM [revenuecat_data_table] 
  WHERE is_trial_conversion
)

SELECT
  DATE(t.end_time) AS date,
  COUNT(t.*) AS trials_ending,
  COUNT(c.*) AS conversions,
  (COUNT(c.*)::real / COUNT(t.*)::real) AS cvr
  FROM trials AS t
  LEFT JOIN conversions AS c ON c.rc_original_app_user_id=t.rc_original_app_user_id
  WHERE t.end_time < CURRENT_DATE
  GROUP BY date;

Related articles

• Scheduled Data Exports
• Selecting columns for an export
• Charts

Was this page helpful?