customer_v1 schema

​

Personal data

• IP addresses and network provider fields are not included.
• Email addresses in text and ID columns are replaced with [redacted].
• custom_user_traits_json, attributed_custom_traits_json, latest_custom_traits_json and latest_rc_subscriber_attributes_json drop keys for names, emails, phone numbers, IP addresses, postal addresses, birth dates, advertising IDs and ad click IDs (for example email, firstName, phone, fbclid). Other keys are returned as sent.

​

events

• app_environment: upper case, for example PRODUCTION, SANDBOX or DEBUG. Empty when the SDK didn’t send it.
• device_idiom: phone, tablet, tv, car, mac, vision or unknown.
• device_orientation: portrait, portrait_upside_down, landscape_left, landscape_right, face_up, face_down or unknown.
• locale_code: a language tag such as en-US, or just the language (en) when the device didn’t report a region.

• Event: event_id, event_name, event_timestamp, error_description, open_failure_reason
• User and session: user_id, helium_persistent_id, helium_device_id, helium_session_id, paywall_session_id, revenuecat_app_user_id, custom_user_traits_json
• Paywall: trigger_name, paywall_id, paywall_name, paywall_presentation_type, is_fallback, is_second_try, custom_paywall_action_name, custom_paywall_action_params
• Experiment: experiment_id, experiment_name, experiment_type, experiment_user_bucket, experiment_chosen_variant_id, experiment_chosen_variant_name, experiment_chosen_variant_type
• Purchase: product_id, storekit_transaction_id, storekit_original_transaction_id, app_transaction_id, google_play_order_id, google_play_purchase_token, canonical_transaction_id, attribution_token, currency_code
• App and device: app_bundle_id, app_display_name, app_version, app_build, app_environment, helium_sdk_version, platform, os_name, os_version, device_manufacturer, device_model, device_idiom, device_orientation, screen_width_points, screen_height_points, screen_width_pixels, screen_height_pixels, is_system_dark_mode
• Location and locale: geo_country_name, geo_continent_code, geo_continent_name, geo_region_name, geo_region_code, geo_city, locale_language, locale_code, locale_country

​

revenue

• Test purchases: environment is upper case: PRODUCTION, or SANDBOX for test purchases. A few rows have it empty. Stripe test mode can be a large share of Stripe rows. Add WHERE environment = 'PRODUCTION' for revenue numbers.
• Zero amounts: revenue_usd and revenue_local are 0 on rows without a charge, such as cancellations, expirations and free trial starts. That is most rows.
• Proceeds: proceeds_usd and proceeds_local are Helium’s estimate of what you keep after store commission and sales tax. They are negative on refunds. On Stripe refunds they equal the full refund amount. For App Store, Google Play and RevenueCat rows from before 22 June 2026, proceeds are 0 because Helium did not record them yet. Stripe and Paddle rows have proceeds for all dates.
• event_timestamp: empty for some Stripe rows. Use coalesce(event_timestamp, timestamp).
• Store-specific columns: recent_subscription_start_date is empty for RevenueCat. storefront is empty for Paddle and often for Stripe. offer_id and offer_code are mostly empty outside Google Play.
• product_type: auto_renewable_subscription, non_renewing_subscription, consumable, non_consumable or one_time (a one-off Stripe or Paddle purchase). NULL when the store doesn’t send it, which is common for RevenueCat.
• User columns: revenuecat_app_user_id is filled in only for RevenueCat and some Stripe and Paddle rows. idfv comes only from RevenueCat on iOS. app_account_token is filled in only when your app sets one at purchase.
• helium_persistent_id: often empty. It is always empty for App Store Server Notifications and Google Play notifications. To link revenue to paywalls and experiments, join revenue.original_transaction_id to subscriptions.subscription_id and use the attributed_* columns.

• Event: notification_id, event_type, timestamp, event_timestamp, store_notification_type, store_notification_subtype, source, app_store, environment
• Transaction: transaction_id, original_transaction_id, app_transaction_id, app_account_token, quantity, family_shared
• Money: revenue_usd, proceeds_usd, revenue_local, proceeds_local, currency, storefront
• Product and offer: product_id, product_type, subscription_group_id, renewal_date, recent_subscription_start_date, offer_id, offer_type, offer_code, ios_offer_type, ios_offer_period
• User: revenuecat_app_user_id, idfv, helium_persistent_id

​

subscriptions

• net_revenue_usd: gross charges minus refunds, before store fees. It equals sum(revenue_usd) over the subscription’s rows in revenue. total_revenue_usd holds the same value.
• net_proceeds_usd: what you keep after store commission, sales tax and refunds. It equals sum(proceeds_usd) over the same rows, so it has the same gap before 22 June 2026 for App Store, Google Play and RevenueCat.
• total_refund_amount_usd: the refunded amount, as a negative number.

• 2299-12-31: trial_start_timestamp when there was no trial, and first_paid_timestamp when the subscription was never paid. To find trials, use trial_start_timestamp < '2200-01-01'. To count paid subscriptions, use has_paid_revenue = 1.
• 1970-01-01: last_renewal_timestamp, last_cancellation_timestamp, expiration_timestamp, conversion_timestamp and event_timestamp when there is no such event. trial_end_timestamp is either 1970-01-01 or 2299-12-31 when there was no trial, and 2299-12-31 for about 2% of trials whose end date is unknown. Use trial_start_timestamp, not trial_end_timestamp, to tell whether there was a trial.
• Empty: latest_subscription_start_date and latest_renewal_date are NULL when unknown.

• Subscription: subscription_id, product_id, product_type, store, webhook_source, environment, storefront, currency, family_shareable, has_access, subscription_status
• Lifecycle: trial_start_timestamp, trial_end_timestamp, is_trial_pending, is_trial_resolved, first_paid_timestamp, conversion_timestamp, conversion_type, last_renewal_timestamp, latest_renewal_date, latest_subscription_start_date, last_cancellation_timestamp, expiration_timestamp, first_event_type, latest_event_type, first_revenue_event_type, transaction_type, earliest_revenue_event_timestamp, latest_revenue_event_timestamp, last_updated
• Revenue: total_revenue_usd, net_revenue_usd, net_proceeds_usd, num_payments, renewal_count, has_paid_revenue, has_refund, num_refunds, total_refund_amount_usd
• Offer: offer_type, offer_id, store_offer_id, event_offer_id
• User: rc_app_user_id, rc_app_id, app_transaction_id, app_account_id, idfv, helium_persistent_id, helium_session_id, app_session_id, attributed_user_id, user_country
• Attribution: is_helium_attributed, attributed_paywall_id, attributed_paywall_name, attributed_paywall_template_name, attributed_paywall_session_id, attributed_trigger_name, attributed_experiment_id, attributed_experiment_name, attributed_experiment_type, attributed_experiment_audience_id, attributed_variant_allocation_id, attributed_variant_allocation_name, attributed_custom_traits_json, attributed_event_store_transaction_id, attributed_event_app_transaction_id
• Attributed SDK event: event_timestamp, event_is_fallback, event_product_id, event_app_build, event_app_name, event_os_name, event_sdk_version, event_device_type, event_device_model, event_country, event_city, event_environment

​

users

• Identity: helium_persistent_id, observed_user_ids, observed_rc_user_ids, observed_app_attribution_tokens, observed_transaction_ids, canonical_rc_app_user_id, canonical_app_attribution_token
• Activity: first_seen_timestamp, last_seen_timestamp, num_sessions, num_paywall_sessions, num_paywall_opens, num_conversions, last_updated
• Firsts: first_paywall_seen, first_paywall_seen_timestamp, first_experiment_allocated, first_experiment_allocated_timestamp, first_product_purchased, first_product_purchased_timestamp
• Latest device: latest_country, latest_city, latest_app_store, latest_app_name, latest_device_build, latest_device_model, latest_device_type, latest_sdk_version, latest_environment
• Attributes: latest_custom_traits_json, latest_rc_subscriber_attributes_json

​

Example queries

SELECT
    trigger_name,
    uniqExactIf(event_id, event_name = 'experience_opened') AS opens,
    uniqExactIf(event_id, event_name = 'purchase_succeeded') AS purchases,
    round(purchases / nullIf(opens, 0), 4) AS conversion_rate
FROM events
WHERE event_timestamp > now() - INTERVAL 30 DAY
  AND app_environment NOT IN ('SANDBOX', 'DEBUG')
GROUP BY trigger_name
ORDER BY opens DESC

SELECT
    attributed_experiment_name,
    attributed_variant_allocation_name,
    countIf(has_access = 1) AS active_subscribers,
    round(sum(net_revenue_usd), 2) AS net_revenue_usd,
    round(sum(net_proceeds_usd), 2) AS net_proceeds_usd
FROM subscriptions
WHERE attributed_experiment_id NOT IN ('', 'Unknown')
  AND environment = 'PRODUCTION'
GROUP BY attributed_experiment_name, attributed_variant_allocation_name
ORDER BY attributed_experiment_name, net_revenue_usd DESC

SELECT toDate(timestamp) AS day, round(sum(revenue_usd), 2) AS revenue_usd
FROM revenue
WHERE timestamp > now() - INTERVAL 14 DAY
  AND environment = 'PRODUCTION'
GROUP BY day
ORDER BY day