Omnichannel subscriptions - Chargebee Data Catalog

Represents subscription records unified across multiple app store sources (Apple App Store and Google Play Store). Provides a one-to-one mapping of source data into Chargebee’s omnichannel format.

UPSERT KEY

Each row is uniquely identified by this column. Use it as the upsert key when applying incremental datasets to your warehouse.

Related Tables

Tables referencing omnichannel_subscriptions.id

omnichannel_subscription_items omnichannel_subscription_transactions

Columns

id string, not null

Primary Key

Chargebee-generated identifier for the recorded omnichannel subscription.

customer_id string, null

Foreign Key customers.id

The ID of the Chargebee customer linked to this subscription.

app_id string, not null

Chargebee App Identifier associated with the mobile app that generated the purchase.

change_type string

The column indicates the type of change [create, update, delete, read] and can be used to load data into the warehouse accordingly.

created_at timestamp, not null

Timestamp when the omnichannel subscription was created in Chargebee.

id_at_source string, not null

Identifier of the subscription in the source store. For Apple, this is the Transaction ID of the initial purchase; for Google, the purchase token of the initial purchase.

initial_purchase_transaction_id string, null

Identifier of the initial purchase transaction record associated with this subscription.

record_exported_at timestamp

The column represents the timestamp when the data was exported to the configured destination.

record_extracted_at timestamp

The column represents the timestamp when the updated data became available in Chargebee's data lakehouse.

resource_version long, null

Version number of this resource, updated to a new millisecond timestamp whenever the resource changes.

source string, not null

Origin store where the purchase is managed (e.g., `apple_app_store' and `google_play_store`).

Sample Queries

Subscription churn by channel

Calculates churn and retention rates for subscriptions grouped by source. Possible values are:

web
apple_app_store
google_play_store

copy

Click to Copy

      SELECT
  channel,
  total_subscriptions,
  active_subscriptions,
  cancelled_subscriptions,
  paused_subscriptions,
  ROUND(
    cancelled_subscriptions * 100.0 / total_subscriptions,
    2
  ) as churn_rate,
  ROUND(
    active_subscriptions * 100.0 / total_subscriptions,
    2
  ) as retention_rate
FROM
  (
    SELECT
      'web' as channel,
      COUNT(*) as total_subscriptions,
      COUNT(
        CASE
          WHEN status = 'active' THEN 1
        END
      ) as active_subscriptions,
      COUNT(
        CASE
          WHEN status = 'cancelled' THEN 1
        END
      ) as cancelled_subscriptions,
      COUNT(
        CASE
          WHEN status = 'paused' THEN 1
        END
      ) as paused_subscriptions
    FROM
      subscriptions
    UNION ALL
    SELECT
      os.source as channel,
      COUNT(DISTINCT os.id) as total_subscriptions,
      COUNT(
        DISTINCT CASE
          WHEN osi.status = 'active' THEN os.id
        END
      ) as active_subscriptions,
      COUNT(
        DISTINCT CASE
          WHEN osi.status = 'cancelled' THEN os.id
        END
      ) as cancelled_subscriptions,
      COUNT(
        DISTINCT CASE
          WHEN osi.status = 'paused' THEN os.id
        END
      ) as paused_subscriptions
    FROM
      omnichannel_subscriptions os
      LEFT JOIN omnichannel_subscription_items osi ON os.id = osi.omnichannel_subscription_id
    GROUP BY
      os.source
  ) channel_metrics
ORDER BY
  churn_rate ASC;

Count of subscriptions across - Web, Apple App Store and Google Play Store

Retrieves count of subscriptions with status across different sources - web, apple_app_store, and google_play_store.

copy

Click to Copy

      SELECT
  os.source,
  osi.status,
  COUNT(1) as subscription_count
FROM
  omnichannel_subscriptions os
  LEFT JOIN omnichannel_subscription_items osi ON os.id = osi.omnichannel_subscription_id
GROUP BY
  os.source,
  osi.status
UNION
SELECT
  'web',
  s.status,
  COUNT(1) as subscription_count
FROM
  subscriptions s
GROUP BY
  s.status;

Subscription revenue by channel

Calculates total subscription revenue in USD grouped by source. Possible values are:

web
apple_app_store
google_play_store

copy

Click to Copy

      WITH
  latest_exchange_rates AS (
    SELECT
      currency_code,
      exchange_rate,
      ROW_NUMBER() OVER (
        PARTITION BY
          currency_code
        ORDER BY
          date DESC
      ) as rn
    FROM
      exchange_rate_daily
  ),
  all_revenue AS (
    SELECT
      'web' as channel,
      SUM(i.total / 100.0 * COALESCE(er.exchange_rate, 1.0)) as revenue_usd
    FROM
      invoices i
      LEFT JOIN latest_exchange_rates er ON i.currency_code = er.currency_code
      AND er.rn = 1
    UNION ALL
    SELECT
      os.source as channel,
      SUM(
        (
          ot.price_units + (COALESCE(ot.price_nanos, 0) / 1000000000.0)
        ) * COALESCE(er.exchange_rate, 1.0)
      ) as revenue_usd
    FROM
      omnichannel_transactions ot
      JOIN omnichannel_subscriptions os ON ot.app_id = os.app_id
      LEFT JOIN latest_exchange_rates er ON ot.price_currency = er.currency_code
      AND er.rn = 1
    GROUP BY
      os.source
  )
SELECT
  channel,
  ROUND(SUM(revenue_usd), 2) as total_revenue_in_usd,
  ROUND(
    SUM(revenue_usd) * 100.0 / (
      SELECT
        SUM(revenue_usd)
      FROM
        all_revenue
    ),
    2
  ) as percentage
FROM
  all_revenue
GROUP BY
  channel
ORDER BY
  total_revenue_in_usd DESC;