Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt

Use this file to discover all available pages before exploring further.

The sm_order_type field classifies whether an order is a subscription or one-time purchase. SourceMedium determines this at the line-item level first, then rolls those line-level signals up to the order. This classification is used in subscription analytics, cohort analysis, and LTV reporting.

​
Order Type Values

Common values in customer-facing datasets are:
ValueDescription
subscriptionRecurring subscription order
one_timeNon-subscription purchase
subscription_&_one_timeMixed order containing both subscription and one-time items
Exact enum strings can vary by dataset version or customer environment. When you need to filter precisely, confirm values with SELECT DISTINCT sm_order_type in your own warehouse. When possible, prefer boolean helper fields such as is_subscription_order.

​
How Order Type Is Determined

Order type classification uses a multi-signal approach evaluated at the line-item level, then rolled up to the order.

​
General signal sources

SourceMedium can use several signal families:
  • Direct subscription-platform line-item mappings such as ReCharge, Retextion, Chargebee, or Amazon Subscribe & Save
  • Shopify line-item metadata and property-based signals
  • Shopify order tags and app/source signals
  • Platform fields such as is_from_subscription

​
Shopify signal precedence

For Shopify, SourceMedium now prefers explicit line-level signals over older order-tag-only heuristics.
PrioritySignalNotes
1Direct subscription-platform line-item mappingStrongest signal when a subscription platform integration provides line-level classification
2Explicit Shopify one-time line signalPrevents false subscription classification when a line is explicitly marked one-time
3Explicit Shopify subscription line signalUses line-item metadata from Shopify properties when present
4Pre-cutover legacy Shopify fallbacksIncludes prepaid + subscription tags, Shopify subscription contract app ID, subscription tags, and is_from_subscription
5Limited merchant-aware post-cutover fallbackUsed only for specific not-yet-cutover Shopify merchants in narrow single-line scenarios
6DefaultTreat as one-time
For Shopify stores, order tags are still useful, but they are no longer the best signal after the cutover for merchants with reliable line-level metadata. Explicit line-item signals and direct subscription-platform mappings are more authoritative.
Direct subscription-platform mappings can come from exact line-item joins or fallback matching when platform data is incomplete, which helps some lines classify correctly even when a perfect line-item ID match is unavailable.

​
Shopify cutover behavior

After the Shopify subscription-classification cutover date (2025-01-01 in the current project config), SourceMedium applies stricter line-level rules:
  • Explicit one-time metadata can override ambiguous subscription-like metadata.
  • Placeholder metadata such as a Shopify subscription_id of 1 is ignored as a subscription signal.
  • Free-gift metadata is separated from true subscription classification.
  • Legacy order-tag fallback remains available only in limited merchant-specific cases.
  • That post-cutover fallback is restricted to single-line orders. Multi-line orders from not-yet-cutover merchants default to one-time unless explicit line signals or direct subscription-platform mappings are present.

​
Subscription tag patterns

When legacy order-tag fallbacks are used, the system recognizes patterns such as:
  • Subscription, SubscriptionActive
  • recurring_order, recurring-order
  • subscription_order_loop
  • first_subscription_order, first-subscription
  • Luna Subscription, Ordergroove Trigger Order

​
is_subscription_order

A boolean convenience field derived from sm_order_type: 
SELECT
  sm_order_type,
  is_subscription_order
FROM `your_project.sm_transformed_v2.dim_orders`
WHERE sm_order_type IS NOT NULL
LIMIT 10;
Use is_subscription_order = TRUE when you want a stable filter that is less sensitive to enum-string differences across environments.

​
order_sequence

Customer lifecycle classification based on order position:
ValueDescription
1st_orderCustomer’s first order (new customer)
repeat_orderAny subsequent order (returning customer)

​
subscription_order_sequence

Subscription-specific lifecycle classification commonly appears as:
ValueDescription
1st_sub_orderCustomer’s first subscription purchase
recurring_sub_orderSubscription renewal or subsequent subscription order
one_time_orderNon-subscription order

​
Line-level subscription fields

For product-level analysis, use obt_order_lines instead of relying only on the order rollup:
  • order_line_type
  • is_order_line_subscription
  • subscription_order_index
  • subscription_order_sequence
This is especially important for mixed carts, bundles, free gifts, and any analysis where some lines in an order are subscription and others are not.

​
Using Order Type In Analysis

​
Filter to subscription orders

SELECT COUNT(*) AS subscription_orders
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND is_subscription_order = TRUE;

​
Separate subscription vs one-time metrics

SELECT
  sm_order_type,
  COUNT(*) AS order_count,
  SUM(order_net_revenue) AS revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
GROUP BY sm_order_type;

​
Use line-level fields when carts can be mixed

SELECT
  order_line_type,
  subscription_order_sequence,
  COUNT(*) AS line_count,
  SUM(order_line_net_revenue) AS net_revenue
FROM `your_project.sm_transformed_v2.obt_order_lines`
WHERE is_order_sm_valid = TRUE
  AND order_processed_at_local_datetime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY 1, 2
ORDER BY net_revenue DESC;

​
Best Practices

​
Prefer direct subscription-platform integrations

If you have ReCharge, Skio, Stay AI, Loop, Retextion, Chargebee, or another supported subscription platform, the most reliable classification comes from direct line-level platform data.

​
Preserve explicit Shopify line-item metadata

For Shopify stores, explicit line-item metadata is more reliable than tag-only logic after the cutover. If your app emits explicit subscription or one-time properties, preserve them.

​
Validate with source-platform data

If you have a direct subscription-platform integration, compare SourceMedium classifications to that platform’s line-level or order-level data when troubleshooting mismatches.

​
Use line-level fields for product analysis

Use order-level fields for order and customer retention analysis. Use line-level fields when the business question is about products, bundles, subscription mixes, or gifts inside a cart.

Subscription Flags

Understand order-level and line-level subscription fields

obt_order_lines

Review line-level subscription field definitions