WITH
    campaign_metrics AS (
        SELECT
            c.name AS campaign_name,
            c.campaign_type,
            c.status,
            SUM(cpr.impressions) AS total_impressions,
            SUM(cpr.clicks) AS total_clicks,
            SUM(cpr.spend) AS total_spend,
            SUM(cpr.conversions) AS total_conversions,
            SUM(cpr.revenue) AS total_revenue,
            AVG(cpr.ctr) AS avg_ctr,
            AVG(cpr.average_cpc) AS avg_cpc,
            AVG(cpr.quality_score) AS avg_quality_score
        FROM
            nekt_raw.microsoft_ads_campaigns c
            LEFT JOIN nekt_raw.microsoft_ads_campaign_performance_report cpr 
                ON c.id = cpr.campaign_id
        WHERE
            date_parse(cpr.time_period, '%Y-%m-%d') >= CURRENT_DATE - INTERVAL '30' DAY
        GROUP BY
            c.name,
            c.campaign_type,
            c.status
    )
SELECT
    campaign_name,
    campaign_type,
    status,
    total_spend,
    total_impressions,
    total_clicks,
    total_conversions,
    total_revenue,
    avg_ctr,
    avg_cpc,
    avg_quality_score,
    CASE 
        WHEN total_spend > 0 THEN total_revenue / total_spend 
        ELSE 0 
    END AS roas
FROM
    campaign_metrics
ORDER BY
    total_spend DESC

SELECT
    kpr.keyword,
    kpr.bid_match_type,
    kpr.campaign_name,
    kpr.ad_group_name,
    SUM(kpr.impressions) AS total_impressions,
    SUM(kpr.clicks) AS total_clicks,
    SUM(kpr.spend) AS total_spend,
    SUM(kpr.conversions) AS total_conversions,
    SUM(kpr.revenue) AS total_revenue,
    AVG(kpr.ctr) AS avg_ctr,
    AVG(kpr.average_cpc) AS avg_cpc,
    AVG(kpr.quality_score) AS avg_quality_score,
    AVG(kpr.average_position) AS avg_position,
    CASE 
        WHEN SUM(kpr.clicks) > 0 THEN SUM(kpr.conversions) * 100.0 / SUM(kpr.clicks) 
        ELSE 0 
    END AS conversion_rate,
    CASE 
        WHEN SUM(kpr.conversions) > 0 THEN SUM(kpr.spend) / SUM(kpr.conversions) 
        ELSE 0 
    END AS cost_per_conversion
FROM
    nekt_raw.microsoft_ads_keyword_performance_report kpr
WHERE
    date_parse(kpr.time_period, '%Y-%m-%d') >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY
    kpr.keyword,
    kpr.bid_match_type,
    kpr.campaign_name,
    kpr.ad_group_name
HAVING
    SUM(kpr.clicks) > 10
ORDER BY
    total_conversions DESC,
    total_clicks DESC
LIMIT 50

SELECT
    ulpr.country,
    ulpr.state,
    ulpr.city,
    SUM(ulpr.impressions) AS total_impressions,
    SUM(ulpr.clicks) AS total_clicks,
    SUM(ulpr.spend) AS total_spend,
    SUM(ulpr.conversions) AS total_conversions,
    SUM(ulpr.revenue) AS total_revenue,
    AVG(ulpr.ctr) AS avg_ctr,
    AVG(ulpr.average_cpc) AS avg_cpc,
    CASE 
        WHEN SUM(ulpr.clicks) > 0 THEN SUM(ulpr.conversions) * 100.0 / SUM(ulpr.clicks) 
        ELSE 0 
    END AS conversion_rate,
    CASE 
        WHEN SUM(ulpr.spend) > 0 THEN SUM(ulpr.revenue) / SUM(ulpr.spend) 
        ELSE 0 
    END AS roas
FROM
    nekt_raw.microsoft_ads_user_location_performance_report ulpr
WHERE
    date_parse(ulpr.time_period, '%Y-%m-%d') >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY
    ulpr.country,
    ulpr.state,
    ulpr.city
HAVING
    SUM(ulpr.impressions) > 1000
ORDER BY
    total_conversions DESC,
    total_spend DESC
LIMIT 25