Skip to main content

​
Overview

Use these queries as starting points for analysis in BigQuery. Replace your_project with your BigQuery project ID (e.g., sm-yourcompany) and your-sm_store_id with your store identifier.
Query Standards:
  • Always include is_order_sm_valid = TRUE for order-based analyses
  • If you have multiple stores, add sm_store_id = 'your-sm_store_id' to filter for a specific store’s data
  • Use your_project.sm_transformed_v2.* for standard tables
  • Use your_project.sm_experimental.* for MTA tables
If you’re not sure which table to use, start with: obt_orders and obt_order_lines.

​
Browse by Category

Marketing & Ads

CAC, platform ROAS, campaign-type performance, and marketing efficiency analysis.

Messaging

Email and SMS campaign/flow performance, list growth, and engagement analysis.

Funnel

Funnel progression, drop-off, and conversion-rate analysis.

Journeys & Lead Capture

Touchpoint journeys, lead capture quality, and conversion timing.

Customers & Retention

First vs repeat behavior, retention, and customer lifecycle patterns.

Products

Product-level performance, assortment, and bundle analysis.

Orders & Revenue

Revenue composition, order quality, refunds, and channel-mix trends.

LTV & Retention

Cohort value, payback, and long-term customer economics.

Attribution & Data Health

Attribution coverage, tracking quality, and data reliability checks.

Customer Support

Ticket volume, handle time, resolution quality, and support trends.
CategoryTopics
Marketing & AdsCAC, ROAS by platform, ROAS trends
MessagingEmail/SMS performance, flows vs campaigns, list growth
FunnelFunnel step counts, conversion rates, top converting pages
Journeys & Lead CaptureLead capture → purchase timing, landing pages, first vs last touch (MTA)
Customers & RetentionFirst vs repeat orders, repeat rates by source, new vs repeat trends
ProductsTop products by revenue/units, gateway products, product combos
Orders & RevenueAOV by channel, subscription revenue, refund rates, sales channel mix
LTV & RetentionCohort LTV, payback period, LTV:CAC, repeat purchase rates, 90-day LTV by product
Attribution & Data HealthTable freshness, UTM coverage, fallback signals, click-id coverage, tracking regressions
Customer SupportTicket volume, one-touch rate, resolution time, CSAT
Most examples default to the last 30 days for performance and “current state” analysis. Adjust the timeframe and add sm_store_id scoping when needed.

​
Request a Query

Have a question that’s not covered here? We regularly add new queries. If there’s a template you’d like added (subscription churn, cohort LTV curves, creative performance, etc.), reach out to your SourceMedium team and include the business question and the table(s) you’re using.