Order Type (sm_order_type)

How SourceMedium classifies orders as subscription vs one-time purchases

The sm_order_type field classifies whether an order is a subscription or one-time purchase. This classification is essential for subscription analytics, cohort analysis, and LTV calculations.

Order Type Values

Value	Description
subscription	Recurring subscription order
one_time	Non-subscription purchase
subscription_&_one_time	Mixed order containing both subscription and one-time items

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. The system checks multiple signals in priority order:

Signal Priority (Highest to Lowest)

Priority	Signal	Result
1	Shopify explicit one-time line signal	One-time
2	Shopify explicit subscription line signal	Subscription
3	Amazon Subscribe & Save line item match	Subscription
4	Subscription platform line item type (ReCharge, Retextion)	Per platform classification
5	Legacy tag/order-source fallback logic	Subscription / One-time
6	Default	One-time

Shopify Metadata Update (February 20, 2026)

For Shopify, order-line classification now uses a broader set of line-item metadata keys from stg_stitch__shopify__order_line_properties.

What changed

• Added more subscription-indicator keys (e.g., selling plan, prepaid, bundle, and app-emitted subscription metadata keys).
• Added explicit one-time keys used by common subscription apps.
• Added guardrail for placeholder values:
  • subscription_id = '1' by itself is treated as a placeholder and does not create a subscription signal.
• Added Shopify app-id fallback coverage for single-line orders:
  • Includes ReCharge app id 294517 in the post-cutover order-source fallback list.

Signal precedence (Shopify)

1. Explicit one-time signal wins.
2. Explicit subscription signal is used when explicit one-time is absent.
3. Exact/fuzzy subscription platform line-item matches are used next.
4. Legacy/tag-based fallback logic applies.
5. Otherwise classify as one-time.

What existing users should expect

• Some Shopify lines/orders may move from One-time to Subscription where subscription metadata was previously under-detected.
• A smaller set of Shopify lines/orders may move from Subscription to One-time when the only signal was placeholder subscription_id = '1'.
• No schema change: field names like sm_order_type, is_subscription_order, and subscription_order_sequence are unchanged.

Subscription Tag Patterns

The system recognizes these tag patterns (case-insensitive):

• Subscription, SubscriptionActive
• recurring_order, recurring-order
• subscription_order_loop
• first_subscription_order, first-subscription
• Luna Subscription, Ordergroove Trigger Order

The final sm_order_type on dim_orders uses COALESCE(line_level_type, 'One-time'), so orders always have a value—never null.

Subscription Platform Integration

When you have a direct integration with a subscription platform, line-level data provides more accurate classification:

Platform	Data Source
ReCharge	Line item type from ReCharge API
Retextion	Line item type from Retextion API
Chargebee	Invoice line subscription sequence
Amazon Subscribe & Save	SKU-level subscription matching

Related Dimensions

is_subscription_order

A boolean convenience field derived from sm_order_type:

SELECT
  sm_order_type,
  sm_order_type = 'subscription' AS is_subscription_order
FROM `your_project.sm_transformed_v2.dim_orders`
WHERE sm_order_type IS NOT NULL
LIMIT 10;

order_sequence

Customer lifecycle classification based on order position:

Value	Description
1st_order	Customer’s first order (new customer)
repeat_order	Any subsequent order (returning customer)

subscription_order_sequence

Subscription-specific lifecycle classification:

Value	Description
1st_sub_order	Customer’s first subscription purchase
recurring_sub_order	Subscription renewal or subsequent subscription order
one_time_order	Non-subscription order

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 sm_order_type = 'subscription';
-- or equivalently
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

Subscription Cohort Analysis

Use subscription_order_sequence to analyze subscription retention:

SELECT
  DATE_TRUNC(order_processed_at, MONTH) as cohort_month,
  subscription_order_sequence,
  COUNT(DISTINCT sm_customer_key) as customers
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND is_subscription_order = TRUE
GROUP BY 1, 2

Best Practices

Ensure Consistent Tagging

For accurate subscription classification in Shopify, ensure your subscription app consistently applies subscription tags to orders. Most subscription apps (ReCharge, Skio, etc.) do this automatically.

Validate with Subscription Platform Data

If you have a direct subscription platform integration, compare the sm_order_type classification against your subscription platform’s data to ensure accuracy.

Use for LTV Segmentation

Subscription and one-time customers often have very different lifetime value patterns. Use sm_order_type to segment your LTV analysis accordingly.

Related Resources

LTV & Retention Module

Analyze subscription and one-time customer retention

Orders Deep Dive

Filter and analyze orders by type