I can't able to deploy my query due to getting date error

Get Help
1

this is my query below and getting the error as attached in the screenshot, can anyone help ?

image
image1413×885 56.3 KB

WITH live AS (
SELECT
date,
SUM(conversions) AS live_conversions
FROM google_ads_live
WHERE {{date}}
GROUP BY date
),
search_term_total AS (
SELECT
date,
SUM(conversions) AS total_search_conversions
FROM google_ads_search_term
WHERE {{date}}
AND campaign = 'TROAS'
GROUP BY date
),
modern_aminos AS (
SELECT
date,
SUM(conversions) AS modern_aminos_conversions
FROM google_ads_search_term
WHERE {{date}}
AND campaign = 'TROAS'
AND search_term = 'modern aminos'
GROUP BY date
)

-- Repeating Users
SELECT
'Repeating Users' AS user_type,
SUM(
COALESCE(ma.modern_aminos_conversions, 0)

  • (l.live_conversions - COALESCE(st.total_search_conversions, 0))
    ) AS total_users
    FROM live l
    LEFT JOIN search_term_total st ON l.date = st.date
    LEFT JOIN modern_aminos ma ON l.date = ma.date

UNION ALL

-- New Users
SELECT
'New Users' AS user_type,
SUM(st.total_search_conversions - COALESCE(ma.modern_aminos_conversions, 0)) AS total_users
FROM search_term_total st
LEFT JOIN modern_aminos ma ON st.date = ma.date;

2

Your problem is in this CTE – You use {{date}} here and it is field-linked to the google_ads_search_term table, but that table is not referenced in that query.

You’ll have to restructure the query so you can apply the condition, or create a second filter to filter google_ads_live.