> ## Documentation Index
> Fetch the complete documentation index at: https://docs.tryhelium.com/llms.txt
> Use this file to discover all available pages before exploring further.

# customer_v1 schema

> Tables and columns you can query with the Data Access API.

`customer_v1` has four tables. Each one only returns your organization's rows, and each has an `organization_id` column with your organization's ID.

New columns may be added to `customer_v1` at any time, so select columns by name instead of relying on `SELECT *` column order. Removing or renaming a column, or changing what it means, only happens in a new schema version. See the [schema changelog](https://docs.tryhelium.com/data-access/changelog.md).

## Personal data

Helium leaves out direct contact details:

* 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

One row per SDK event from your apps. This is the same event stream described in [Helium events](https://docs.tryhelium.com/guides/helium-events.md).

`event_name` uses the same names as analytics forwarding. Only these events are included:

| `event_name` | Meaning |
| - | - |
| `initialization_failed` | The SDK could not download your paywalls |
| `experience_opened`, `experience_open_failed` | A paywall was presented, or failed to present (see `open_failure_reason`) |
| `experience_view_rendered`, `experience_view_render_failed` | The paywall finished rendering, or failed to |
| `experience_skipped`, `experience_dismissed`, `experience_view_closed` | The paywall was skipped by targeting, dismissed, or closed |
| `purchase_started`, `purchase_pending`, `purchase_succeeded`, `purchase_failed`, `purchase_abandoned` | Purchase flow |
| `entitlement_restored`, `entitlement_restore_failed` | Restore purchases |
| `user_experiment_allocated` | The user was assigned to an experiment variant |
| `custom_event` | A custom paywall action (see `custom_paywall_action_name`) |

Not included: successful SDK setup and paywall downloads, attempts to open a paywall that was already on screen, and internal events such as web checkout modals and consent prompts.

Some columns are normalized so the same value is always spelled the same way:

* **`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.

Columns:

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

> [!NOTE]
>
> A very small number of events can appear twice with the same `event_id`, about 1 in 1,000 rows. It's rare, but you still need to deduplicate on `event_id` for exact counts: count with `uniqExact(event_id)` instead of `count()`, or upsert on `event_id` when you copy rows into your warehouse.

## revenue

One row per store revenue event (purchase, renewal, refund and so on) from the App Store, Google Play, Stripe, Paddle or RevenueCat. A store notification delivered more than once appears once: rows with the same transaction, `event_type`, event time and amount are counted once. Repeated real events, such as a cancellation, resubscribe and second cancellation, each keep their own row.

Each store sends different fields, so many `revenue` columns are empty for some stores. Only stores you have connected to Helium appear.

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

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

One row per subscription or purchase, with its revenue to date and the paywall, experiment and variant it is attributed to.

`has_access` is `1` when the subscription currently grants access: it has a renewal date in the future or a pending trial, and its latest event is not a refund, revocation or expiration. Use `has_access` instead of `subscription_status` to count active subscribers. `subscription_status` only changes when a store event arrives, so it can lag behind.

`is_trial_pending` is `1` until a trial is both at least 7 days old and at least 2 days past its `trial_end_timestamp`, so it stays `1` for 7 days even on a 3-day trial. `is_trial_resolved` is its opposite. To find trials whose end date has passed, filter on `trial_end_timestamp < now()`. For trial-to-paid conversion rates, use `is_trial_resolved = 1`, because it waits 2 extra days for the store to report whether the trial converted.

The `attributed_*` columns are filled in only when Helium can link the subscription to a paywall session from the SDK, and `is_helium_attributed` is `1` for those rows. Purchases Helium cannot link to a paywall session have no attribution, and their `attributed_*` text columns hold `Unknown`. `attributed_experiment_id` is also `Unknown` on attributed rows that weren't part of an experiment, so filter with `NOT IN ('', 'Unknown')`.

Revenue totals cover the subscription's whole life to date:

* **`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.

Some timestamp columns use a placeholder date instead of being empty when something never happened:

* **`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.

Columns:

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

One row per Helium user (`helium_persistent_id`), with their latest device details and lifetime counts.

Columns:

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

Paywall conversion by trigger over the last 30 days:

```sql theme={null}
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
```

Active subscribers and lifetime net revenue by experiment variant:

```sql theme={null}
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
```

Daily production revenue for the last 14 days:

```sql theme={null}
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
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.