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 | 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:
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.
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.
