Overview

This query tracks monthly ad spend and calculates Pixel ROAS (Return on Ad Spend) across selected channels. It helps compare ad efficiency over time by platform, using Pixel-attributed revenue and spend.

SQL Query

SELECT
  formatDateTime(pj.event_date, '%Y-%m') AS month,
  pj.channel AS channel,
  SUM(pj.spend) AS spend,
  SUM(pj.order_revenue) / nullif(SUM(pj.spend),0) as pixel_roas
FROM
  pixel_joined_tvf () pj
WHERE
  pj.event_date BETWEEN @start_date and @end_date
  AND pj.channel IN ('bing','facebook-ads','google-ads')
GROUP BY
  grouping sets ((month,channel), ())
ORDER BY 1,2

Tables and Metrics Used

Table

• pixel_joined_tvf (Pixel Joined Table)

Dimensions

• event_date
• channel

Measures

• spend
• order_revenue

Derived

• pixel_roas

Customization Options

This query compares monthly ROAS across channels using Pixel-attributed data. You can customize it in the following ways:

Add more channels

• Extend the IN (...) list to include more platforms like 'snapchat-ads', 'tiktok-ads', or 'email-sms'.

Group by campaign or ad level

• Add campaign_name or ad_id to the SELECT and GROUP BY clauses to drill down below the channel level.

Use a different time grouping

• Replace formatDateTime(event_date, '%Y-%m') with toStartOfWeek(event_date) for weekly aggregation instead of monthly.