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

# Marketing & Ads

> CAC, ROAS by platform, and advertising efficiency analysis queries.

<Tip>
  Use the same query standards from the [SQL Query Library overview](/data-activation/template-resources/sql-query-library): `is_order_sm_valid = TRUE` for order analyses, `sm_store_id` scoping for multi-store setups, and `your_project` placeholders.
</Tip>

<Warning>
  Click/impression-based ratios such as CPC, CTR, and CPM are only meaningful when the underlying click or impression metric is available. For TikTok, exclude `ad_campaign_type = 'gmv_max'` when you need ordinary TikTok Ads metrics because GMV Max rows can carry spend without clicks or impressions.
</Warning>

<AccordionGroup>
  <Accordion title="Average CAC (last 30 days)">
    **What you'll learn:** How much you're spending to acquire each new customer, broken down by channel. Use this to identify which channels are most cost-efficient and where you might be overspending on acquisition.

    ```sql theme={null}
    -- Assumptions: timeframe=last_30_days | metric=CAC=ad_spend/new_customer_count | grain=sm_channel | scope=all_channels
    WITH channel_rollup AS (
      SELECT
        COALESCE(NULLIF(LOWER(TRIM(sm_channel)), ''), '(unknown)') AS sm_channel,
        SUM(ABS(ad_spend)) AS ad_spend,
        SUM(new_customer_count) AS new_customers
      FROM `your_project.sm_transformed_v2.rpt_executive_summary_daily`
      WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
        AND ad_spend IS NOT NULL
        AND new_customer_count IS NOT NULL
      GROUP BY 1
    ),
    overall AS (
      SELECT
        '(all_channels)' AS sm_channel,
        SUM(ABS(ad_spend)) AS ad_spend,
        SUM(new_customer_count) AS new_customers
      FROM `your_project.sm_transformed_v2.rpt_executive_summary_daily`
      WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
        AND ad_spend IS NOT NULL
        AND new_customer_count IS NOT NULL
    )
    SELECT
      sm_channel,
      ad_spend,
      new_customers,
      SAFE_DIVIDE(ad_spend, NULLIF(new_customers, 0)) AS cac
    FROM channel_rollup
    WHERE ad_spend > 0

    UNION ALL

    SELECT
      sm_channel,
      ad_spend,
      new_customers,
      SAFE_DIVIDE(ad_spend, NULLIF(new_customers, 0)) AS cac
    FROM overall
    WHERE ad_spend > 0
    ORDER BY cac ASC;
    ```
  </Accordion>

  <Accordion title="Highest ROAS by platform + campaign type (last 30 days)">
    **What you'll learn:** Which ad platform and campaign type combinations are generating the best return on ad spend. Helps you decide where to allocate more budget and which underperforming campaigns to optimize or cut.

    ```sql theme={null}
    -- Assumptions: timeframe=last_30_days | metric=ROAS=platform_reported_revenue/ad_spend | grain=platform+campaign_type | scope=all_stores
    SELECT
      sm_store_id,
      COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS platform,
      COALESCE(NULLIF(LOWER(TRIM(ad_campaign_type)), ''), '(unknown)') AS campaign_type,
      SUM(ad_platform_reported_revenue) AS platform_reported_revenue,
      SUM(ad_spend) AS ad_spend,
      SAFE_DIVIDE(SUM(ad_platform_reported_revenue), NULLIF(SUM(ad_spend), 0)) AS roas
    FROM `your_project.sm_transformed_v2.rpt_ad_performance_daily`
    WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      AND ad_spend > 0
    GROUP BY 1, 2, 3
    ORDER BY roas DESC
    LIMIT 20;
    ```
  </Accordion>

  <Accordion title="ROAS trends over time (monthly, last 6 months)">
    **What you'll learn:** How your ad efficiency has changed month-over-month by platform. Spot seasonal patterns, detect declining performance early, or confirm that recent optimizations are working.

    ```sql theme={null}
    -- Assumptions: timeframe=last_6_months | metric=ROAS=platform_reported_revenue/ad_spend | grain=month+platform | scope=all_stores
    WITH monthly AS (
      SELECT
        DATE_TRUNC(date, MONTH) AS month_start,
        COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS platform,
        SUM(ad_platform_reported_revenue) AS platform_reported_revenue,
        SUM(ad_spend) AS ad_spend
      FROM `your_project.sm_transformed_v2.rpt_ad_performance_daily`
      WHERE date >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 6 MONTH)
        AND date < DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH)
        AND ad_spend > 0
      GROUP BY 1, 2
    )
    SELECT
      platform,
      month_start,
      ad_spend,
      platform_reported_revenue,
      SAFE_DIVIDE(platform_reported_revenue, NULLIF(ad_spend, 0)) AS roas
    FROM monthly
    ORDER BY platform, month_start;
    ```
  </Accordion>

  <Accordion title="TikTok campaign metric coverage (GMV Max vs TikTok Ads)">
    **What you'll learn:** Which TikTok campaign types have spend, clicks, and impressions available. Use this before calculating CPC, CTR, or CPM across TikTok so GMV Max spend does not distort ordinary TikTok Ads metrics.

    ```sql theme={null}
    -- Assumptions: platform=tiktok | timeframe=last_90_days | grain=campaign_type+campaign | metric=spend+clicks+impressions | caveat=exclude_gmv_max_for_cpc_ctr_cpm
    SELECT
      COALESCE(NULLIF(ad_campaign_type, ''), '(blank)') AS ad_campaign_type,
      COALESCE(NULLIF(ad_platform_campaign_objective, ''), '(blank)') AS ad_platform_campaign_objective,
      ad_campaign_name,
      SUM(ad_spend) AS ad_spend,
      SUM(ad_clicks) AS ad_clicks,
      SUM(ad_impressions) AS ad_impressions,
      SAFE_DIVIDE(SUM(ad_spend), NULLIF(SUM(ad_clicks), 0)) AS cpc,
      SAFE_DIVIDE(SUM(ad_clicks), NULLIF(SUM(ad_impressions), 0)) AS ctr,
      SAFE_DIVIDE(SUM(ad_spend) * 1000, NULLIF(SUM(ad_impressions), 0)) AS cpm,
      SUM(ad_spend) > 0 AND COALESCE(SUM(ad_clicks), 0) = 0 AS has_spend_without_clicks,
      SUM(ad_spend) > 0 AND COALESCE(SUM(ad_impressions), 0) = 0 AS has_spend_without_impressions
    FROM `your_project.sm_transformed_v2.rpt_ad_performance_daily`
    WHERE source_system = 'tiktok'
      AND date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    GROUP BY 1, 2, 3
    HAVING ad_spend > 0
    ORDER BY has_spend_without_clicks DESC, has_spend_without_impressions DESC, ad_spend DESC;
    ```

    <Tip>
      For TikTok Ads-only CPC, CTR, and CPM, add `AND ad_campaign_type != 'gmv_max'` to the `WHERE` clause.
    </Tip>
  </Accordion>
</AccordionGroup>

***

## Related Categories

Back to the [SQL Query Library](/data-activation/template-resources/sql-query-library).

<CardGroup cols={2}>
  <Card title="Messaging" icon="envelope-open-text" href="/data-activation/template-resources/sql-query-library/messaging">
    Compare paid performance with owned-channel outcomes.
  </Card>

  <Card title="Orders & Revenue" icon="dollar-sign" href="/data-activation/template-resources/sql-query-library/orders-and-revenue">
    Connect media efficiency to order and revenue quality.
  </Card>

  <Card title="Attribution & Data Health" icon="heart-pulse" href="/data-activation/template-resources/sql-query-library/attribution-and-data-health">
    Validate tracking quality before channel-level decisions.
  </Card>
</CardGroup>