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.

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.

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.

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

record_extracted_at timestamp

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

record_exported_at timestamp

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

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.

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;