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 - Prepaid | Prepaid subscription (multiple periods paid upfront) |
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 | Amazon Subscribe & Save line item match | Subscription |
| 2 | prepaid + subscription tag in order tags | Subscription - Prepaid |
| 3 | Subscription platform line item type (ReCharge, Retextion) | Per platform classification |
| 4 | Shopify Subscription Contract app ID | Subscription |
| 5 | Order tags matching subscription patterns | Subscription |
| 6 | Line item is_from_subscription flag | Subscription |
| 7 | Default | One-time |
Subscription Tag Patterns
The system recognizes these tag patterns (case-insensitive):
Subscription, SubscriptionActiverecurring_order, recurring-ordersubscription_order_loopfirst_subscription_order, first-subscriptionLuna 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.
| 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 |
is_subscription_order
A boolean convenience field derived from sm_order_type:
is_subscription_order = (sm_order_type = 'Subscription')
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
WHERE sm_order_type = 'Subscription'
-- or equivalently
WHERE 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 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 obt_orders
WHERE 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.
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.
