I am trying to write SQL for Meta report, but conversions, roas and conversion value doesnt work. There are no data in the table. What is wrong? The SQL is:
-- BigQuery Standard SQL
WITH base_metrics AS (
SELECT
DATE(DateStart) AS dt,
TRIM(CAST(CampaignId AS STRING)) AS campaign_id,
CampaignName AS campaign_name,
CAST(Impressions AS INT64) AS impressions,
CAST(Clicks AS INT64) AS clicks,
CAST(Spend AS NUMERIC) AS spend
FROM `my_project.my_dataset.AdInsights`
WHERE DATE(DateStart) >= u/start_date
AND REGEXP_REPLACE(CAST(AdAccountId AS STRING), r'[^0-9]', '') =
),
conversions_data AS (
SELECT
DATE(DateStart) AS dt,
TRIM(CAST(CampaignId AS STRING)) AS campaign_id,
SUM(COALESCE(CAST(Action7dClick AS INT64), 0) + COALESCE(CAST(Action1dView AS INT64), 0)) AS conversions,
SUM(COALESCE(CAST(ActionValue AS NUMERIC), 0)) AS conversion_value
FROM `my_project.my_dataset.AdInsightsActions`
WHERE DATE(DateStart) >= u/start_date
AND LOWER(ActionCollection) LIKE '%purchase%'
GROUP BY 1, 2
)
SELECT
b.dt,
b.campaign_id,
b.campaign_name,
b.impressions,
b.clicks,
b.spend,
SAFE_DIVIDE(b.clicks, b.impressions) * 100 AS ctr_pct,
SAFE_DIVIDE(b.spend, b.clicks) AS cpc,
IFNULL(c.conversions, 0) AS conversions,
IFNULL(c.conversion_value, 0) AS conversion_value,
SAFE_DIVIDE(IFNULL(c.conversion_value, 0), b.spend) AS roas
FROM base_metrics b
LEFT JOIN conversions_data c
ON b.dt = c.dt AND b.campaign_id = c.campaign_id
ORDER BY b.dt DESC, b.campaign_name;
I still see so many PRs where people write a subquery just to filter a window function.
BigQuery supports QUALIFY, which filters the results of window functions directly. It makes the code so much more readable.
The Old Way (Subquery hell):
SELECT * FROM (
SELECT
user_id,
status,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY timestamp DESC) as rn
FROM `my-project.dataset.table`
)
WHERE rn = 1
The QUALIFY Way:
SELECT
user_id,
status
FROM `my-project.dataset.table`
QUALIFY ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY timestamp DESC) = 1
If you’re working heavily with analytical SQL and want to uncover more BigQuery features that simplify complex queries at scale, this guide is a solid deep dive.
It runs the same under the hood, but it's cleaner and easier to debug. Are there any other "syntactic sugar" features in BQ that you feel are underused?
