Overview

This query groups ad performance data by campaign keywords, allowing you to compare how different naming conventions or themes perform across clicks, conversions, impressions, and spend. It’s particularly useful for analyzing brand vs. prospecting strategies or other naming-based groupings without manually tagging campaigns.

SQL Query

WITH
    splitByString(',', replaceRegexpAll(trim(@campaign), '\\s*,\\s*', ',')) AS keywords,
    arrayFirst(kw -> if(length(kw) > 0 AND a.campaign_name LIKE concat('%', kw, '%'), 1, 0), keywords) AS matched_keyword,
    if(assumeNotNull(matched_keyword)='', 'Other Campaigns',matched_keyword) AS campaign_group
SELECT
    campaign_group,
    sum(a.clicks) AS clicks,
    sum(a.conversion_value) AS conversion_value,
    sum(a.impressions) AS impressions,
    sum(a.spend) AS spend
FROM
    ads_table a
WHERE
    event_date BETWEEN @start_date AND @end_date
GROUP BY
    GROUPING SETS ((campaign_group), ())
ORDER BY
    conversion_value DESC

Tables and Metrics Used

Table

ads_table (Ads Table)

Measures

clicks – Number of ad clicks reported by the channel
conversion_value – Revenue from channel-attributed conversions
impressions – Number of times the ad was shown
spend – Total ad spend as reported by the ad channel