---
updatedAt: 2025-08-04T11:34:51.000Z
agentTools:
  projectIndex: https://triplewhale.readme.io/llms.txt
---

# SQL Example: Monthly Pixel ROAS for Selected Channels (Bing, Facebook, Google)

# 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

```sql
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](https://triplewhale.readme.io/docs/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.