> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Query Library

> Copy/paste BigQuery SQL templates to answer common questions using SourceMedium tables

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

<Tip>
  **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
</Tip>

If you're not sure which table to use, start with: [`obt_orders`](/data-activation/data-tables/sm_transformed_v2/obt_orders) and [`obt_order_lines`](/data-activation/data-tables/sm_transformed_v2/obt_order_lines).

***

## Browse by Category

<CardGroup cols={2}>
  <Card title="Marketing & Ads" icon="chart-line" href="/data-activation/template-resources/sql-query-library/marketing-and-ads">
    CAC, platform ROAS, campaign-type performance, and marketing efficiency analysis.
  </Card>

  <Card title="Messaging" icon="envelope-open-text" href="/data-activation/template-resources/sql-query-library/messaging">
    Email and SMS campaign/flow performance, list growth, and engagement analysis.
  </Card>

  <Card title="Funnel" icon="filter" href="/data-activation/template-resources/sql-query-library/funnel">
    Funnel progression, drop-off, and conversion-rate analysis.
  </Card>

  <Card title="Journeys & Lead Capture" icon="route" href="/data-activation/template-resources/sql-query-library/journeys-and-lead-capture">
    Touchpoint journeys, lead capture quality, and conversion timing.
  </Card>

  <Card title="Customers & Retention" icon="users" href="/data-activation/template-resources/sql-query-library/customers-and-retention">
    First vs repeat behavior, retention, and customer lifecycle patterns.
  </Card>

  <Card title="Products" icon="bag-shopping" href="/data-activation/template-resources/sql-query-library/products">
    Product-level performance, assortment, bundle analysis, and inventory coverage.
  </Card>

  <Card title="Orders & Revenue" icon="dollar-sign" href="/data-activation/template-resources/sql-query-library/orders-and-revenue">
    Revenue composition, order quality, refunds, returns, and channel-mix trends.
  </Card>

  <Card title="LTV & Retention" icon="recycle" href="/data-activation/template-resources/sql-query-library/ltv-and-retention">
    Cohort value, payback, and long-term customer economics.
  </Card>

  <Card title="Attribution & Data Health" icon="heart-pulse" href="/data-activation/template-resources/sql-query-library/attribution-and-data-health">
    Attribution coverage, tracking quality, attribution-signal debugging, and data reliability checks.
  </Card>

  <Card title="Customer Support" icon="headset" href="/data-activation/template-resources/sql-query-library/customer-support">
    Ticket volume, handle time, resolution quality, and support trends.
  </Card>
</CardGroup>

***

## Quick Links

| Category                                                                                                       | Topics                                                                                                                 |
| -------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------- |
| [Marketing & Ads](/data-activation/template-resources/sql-query-library/marketing-and-ads)                     | CAC, ROAS by platform, ROAS trends                                                                                     |
| [Messaging](/data-activation/template-resources/sql-query-library/messaging)                                   | Email/SMS performance, flows vs campaigns, list growth                                                                 |
| [Funnel](/data-activation/template-resources/sql-query-library/funnel)                                         | Funnel step counts, conversion rates, top converting pages                                                             |
| [Journeys & Lead Capture](/data-activation/template-resources/sql-query-library/journeys-and-lead-capture)     | Lead capture → purchase timing, landing pages, first vs last touch (MTA)                                               |
| [Customers & Retention](/data-activation/template-resources/sql-query-library/customers-and-retention)         | First vs repeat orders, repeat rates by source, new vs repeat trends                                                   |
| [Products](/data-activation/template-resources/sql-query-library/products)                                     | Top products by revenue/units, gateway products, product combos, inventory coverage                                    |
| [Orders & Revenue](/data-activation/template-resources/sql-query-library/orders-and-revenue)                   | AOV by channel, subscription revenue, refund rates, return rates, sales channel mix                                    |
| [LTV & Retention](/data-activation/template-resources/sql-query-library/ltv-and-retention)                     | Cohort LTV, payback period, LTV:CAC, repeat purchase rates, 90-day LTV by product                                      |
| [Attribution & Data Health](/data-activation/template-resources/sql-query-library/attribution-and-data-health) | Table freshness, UTM coverage, fallback signals, click-id coverage, attribution-signal debugging, tracking regressions |
| [Customer Support](/data-activation/template-resources/sql-query-library/customer-support)                     | Ticket volume, one-touch rate, resolution time, CSAT                                                                   |

***

<Info>
  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.
</Info>

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