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, bundle analysis, and inventory coverage.

Orders & Revenue

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

LTV & Retention

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

Attribution & Data Health

Attribution coverage, tracking quality, attribution-signal debugging, and data reliability checks.

Customer Support

Ticket volume, handle time, resolution quality, and support trends.
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.