Omnichannel subscription item offers - Chargebee Data Catalog

Represents offer details applied to omnichannel subscription items (for example, introductory and promotional offers) across app store sources such as the Apple App Store and Google Play Store. Captures offer-level pricing and term details to help analyze offer-driven pricing, renewals, and revenue impact.

Columns

id string, not null

Primary Key

A unique identifier for the omnichannel subscription item offer.

omnichannel_subscription_item_id string, not null

Foreign Key omnichannel_subscription_items.id

Identifier of the omnichannel subscription item to which this offer is applied. Used in the data warehouse to link the offer to its subscription item.

category string, not null

Indicates the functional purpose of the offer. Possible values:

introductory (for first-time subscribers; special pricing or terms for the first billing cycle)
promotional (available to new and existing subscribers; often limited-time)
developer_determined (developer-defined terms; support planned for a future update)

category_at_source string, null

Category label as defined by the source platform (for example, the Apple App Store or Google Play Store). Directly fetched from the source; useful for debugging or platform-specific workflows.

discount_type string, null

Discount strategy for the offer. Possible values:

fixed_amount (subtracts a fixed amount from the original price)
percentage (percentage discount on the original price, e.g., 20% off)
price (overrides the original price with a fixed discounted price for the offer term)

duration string, not null

Indicates how long the offer applies to the subscription. Uses the ISO 8601 duration format. For example, P1M (1 month) or P7D (7 days). After this duration, regular pricing resumes.

offer_id_at_source string, null

Identifier of the offer on the source platform (for example, the Apple App Store or Google Play Store). Used to map records to the source.

offer_term_end timestamp, null

Timestamp when the offer expires. After this time, regular pricing or terms apply to the subscription item.

offer_term_start timestamp, null

Timestamp when the offer becomes effective for the subscription item. It is usually set to the time when the offer is first applied or activated.

percentage double, null

Used when discount_type is percentage. Specifies the discount as a decimal percentage value. For example, a value of 12.5 corresponds to a 12.5% discount.

price_currency string, null

Three-letter ISO 4217 currency code for the offer price (for example, USD, EUR, INR).

price_nanos long, null

Fractional part of the offer amount, expressed in nanos (billionths of the currency unit). For example, 500000000 represents 0.50 of the currency unit. Combine with price_units to determine the total price (for example, $10.50). Depending on the discount type, it indicates the amount deducted (fixed_amount) or the final amount payable (price).

price_units long, null

Whole-unit portion of the offer amount (for example, 10 for $10.00). Depending on the discount type, this value can represent different meanings: for a fixed_amount discount, it indicates the amount deducted from the original price; for a price discount, it reflects the final amount payable by the customer.

type string, not null

Indicates how the offer is applied from a pricing-model perspective. Possible values:

free_trial (customer not charged during the trial period; regular billing thereafter)
pay_up_front (fixed upfront payment for a defined period, often at a discount)
pay_as_you_go (recurring discounted price per billing cycle over multiple renewals)

type_at_source string, null

Offer type as recorded by the source platform (for example, the Apple App Store or 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 item offers by channel and category

Counts offers applied to omnichannel subscription items, grouped by source and offer category. Useful for analyzing introductory and promotional offer adoption across apple_app_store and google_play_store.

copy

Click to Copy

      SELECT
  os.source AS channel,
  osio.category AS offer_category,
  COUNT(osio.id) AS offer_count
FROM
  omnichannel_subscription_item_offers osio
  JOIN omnichannel_subscription_items osi ON osio.omnichannel_subscription_item_id = osi.id
  JOIN omnichannel_subscriptions os ON osi.omnichannel_subscription_id = os.id
GROUP BY
  os.source,
  osio.category
ORDER BY
  channel,
  offer_count DESC;