Scheduled Data Exports
Scheduled data exports are available to all users signed up after September '23, the legacy Grow and Pro plans, and Enterprise plans. If you're on a legacy Free or Starter plan and want to access this integration, migrate to our new pricing via your billing settings.
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β
Customers on our Enterprise plan have the option to receive data exports more frequently than once per day when receiving new and updated transactions only. Contact your Customer Success Manager with questions, or visit our Pricing Page to learn more.
Version Change Logβ
Transaction Formatβ
Applicable to the latest version
All dates and times are provided in UTC.
Header | Description | Type | Example value | Can be null |
---|---|---|---|---|
rc_original_app_user_id | Can be used as a unique user identifier to find all of a user's transactions. | string | $RCAnonymousID:87c6049c58069238dce29853916d624c | |
rc_last_seen_app_user_id_alias | Can be used together with rc_original_app_user_id to match transactions with user identifiers in your systems. | string | $RCAnonymousID:87c6049c58069238dce29853916d624c | |
country | Store country of a transaction when known, or an IP-based estimate of a subscriber's country when not known. | string | GB | β |
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 | β |
product_identifier | The product identifier that was purchased. | string | rc_subscription_monthly | |
product_display_name | The display name of the product identifier if one has been set | string | Monthly $9.99 | β |
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. \n \nproduct_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 | β |
start_time | Purchase time of transaction. | datetime | 2023-01-01 08:27:06 | |
end_time | Expected expiration time of subscription. Null when is_auto_renewable = false \nFor 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 | β |
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 | β |
effective_end_time | Single reference point of a subscriberβs expiration and entitlement revocation; inclusive of each storeβs logic for refunds, grace periods, etc. | datetime | 2023-02-17 08:27:06 | β |
store | The source of the transaction. Can be app_store , play_store , stripe , or promotional . | string | play_store | |
is_auto_renewable | true for auto-renewable subscriptions, false otherwise. | boolean | true | |
is_trial_period | true if the transaction was a trial. | boolean | false | |
is_in_intro_offer_period | true if the transaction is in an introductory offer period. | boolean | false | |
is_sandbox | true for transactions made in a sandbox environment. | boolean | false | |
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 | β |
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 | β |
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) \n \nWe recommend using tax_percentage and commission_percentage to calculate proceeds instead. Learn more here. | float | 0.7 | |
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. | float | 0.1442 | |
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. | float | 0.15 | |
store_transaction_id | orderId or transaction_identifier. | string | 123456789012345 | |
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 | |
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 | β |
unsubscribe_detected_at | When we detected an unsubscribe (opt-out of auto renew). | datetime | 2023-02-16 14:17:10 | β |
billing_issues_detected_at | When we detected billing issues, null if none was detected. | datetime | 2023-02-01 08:27:15 | β |
purchased_currency | The currency that was used for the transaction. | string | GBP | β |
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 | β |
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 | β |
entitlement_identifiers | An array of entitlements that the transaction unlocked or null if it didn't unlock any entitlements. | string array | "[\"membership\", \"full_access\"]" | β |
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 | |
is_trial_conversion | If true , this transaction is a trial conversion. | boolean | true | |
presented_offering | The offering presented to users. | string | Default Offering | β |
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. \n \nNOTE: The FAMILY_SHARED designation is only supported on App Store transactions. | string | PURCHASED | β |
reserved_subscriber_attributes | The reserved attributes set for the Customer (subscriber). 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}}" | β |
custom_subscriber_attributes | The custom attributes set for the Customer (subscriber). | string JSON | "{\"feature_setting\": {\"value\": \"1\", \"updated_at_ms\": 1672549200}, \"survey_response\": {\"value\": \"2\", \"updated_at_ms\": 1599112814785}}" | β |
platform | Last seen platform of the subscriber. | string | android | β |
experiment_id | The unique ID of the Experiment that the subscriber is or was enrolled in. Will be null if the subscriber has not been enrolled in an experiment. Learn more about Experiments here. | string | prexp3a8a234abc | β |
experiment_variant | The value of the Experiment variant that the subscriber is or was enrolled in. a represents the Control, and b represents the Treatment. Will be null if the subscriber has not been enrolled in an experiment. Learn more about Experiments here. | string | a | β |
updated_at | The last time an attribute of the transaction was modified. | datetime | 2023-02-20 05:47:55 | |
offer * | The offer that was used for a transaction (if applicable). | string | black_friday_discount | β |
offer_type * | The type of offer that was used for a transaction (if applicable). | string | offer_code | β |
first_seen_time * | The time the customer was first seen by RevenueCat. | datetime | 2023-01-01 03:00:00 | |
auto_resume_time * | The time when a Play Store subscription would resume after being paused. | datetime | 2023-03-20 03:00:00 | β |
*Available only on our most recent export version
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 always provide the transaction price directly, so we rely on historical data & 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 that the same transaction can be different from one delivery to another if something changed (e.g. due to a refund or billing issue). You should recompute metrics for past time periods periodically to take these changes into account. You can use the
updated_at
field to detect if a transaction may have changed since a prior export. - Data is up to date as of the export beginning to be generated. Thus, changes could occur between the start of an export generating and its delivery that would not be 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 as the sources of truth, so any inconsistencies in the transaction data can always be traced back to the receipt.
The date and time set in Next export start time via the dashboard is when the next export should start getting generated and is not when the next export should be delivered.
If importing the data into a database, you can use the store_transaction_id
and renewal_number
together to uniquely identify transactions.
Updating to the latest versionβ
If you're on an older version of our exports, updating is easy:
- Open app.revenuecat.com
- Navigate to your Project in Project Settings
- Click on your Scheduled Data Exports integration in the βIntegrationsβ side panel
- Click UPDATE TEMPLATE
- After confirming that your data pipeline is configured to ingest the described changes, click UPDATE in the confirmation modal to complete the update.
Please note that Version 4 and all subsequent templates include data format changes which must be incorporated into your data pipeline before updating. Learn more here.
Sample queries for RevenueCat measuresβ
You can use the following sample queries (written in Postgresql) as starting points for reproducing common RevenueCat measures.
- Active Subscriptions
- Active Subscriptions Movement
- MRR
- MRR Movement
- Active Trials
- Revenue
-- 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 within a specified date range
WITH
filtered_subscriptipon_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,
COALESCE(a.num_renewals, 0) AS num_renewals,
COALESCE(e.num_expirations, 0) AS num_expirations,
num_expirations - num_renewals AS churned_actives,
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 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 Movement 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 for a specified date range
WITH
filtered_subscriptipon_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_subscriptipon_transactions
GROUP BY 1),
expirations AS (
SELECT
DATE(effective_end_time) AS date,
SUM(transaction_mrr) AS expired_mrr
FROM filtered_subscriptipon_transactions
GROUP BY 1)
SELECT
COALESCE(a.date, e.date) AS date,
COALESCE(a.new_mrr, 0) AS new_mrr,
COALESCE(a.renewal_mrr, 0) as renewal_mrr,
COALESCE(e.expired_mrr, 0) as expired_mrr,
expired_mrr - renewal_mrr as churned_mrr
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]
-- 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 DATE_DIFF('s', start_time, 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.
-- Revenue generated on a specified date
SELECT
SUM(purchase_price_in_usd) as gross_revenue,
SUM(price_in_usd) as revenue_net_of_refunds, /* "Total Revenue" in the Revenue Chart */
SUM(price_in_usd * (1 - tax_percentage - commission_percentage)) as proceeds
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.
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.
- Active Subs by Custom Attribute
- Active Subs by Auto Renew Status
- Weekly Revenue (starting Monday)
- Realized LTV Segments
- Active Trials by Grace Period Status
- Realized LTV Per Paying Customer by First Purchase Date
-- 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]
-- 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
-- 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
-- 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 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
-- 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 was_refunded = 'false'
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