WITH
    campaign_metrics AS (
        SELECT
            c.campaign_name,
            c.objective_type,
            c.operation_status,
            c.budget_mode,
            SUM(SAFE_CAST(r.spend AS FLOAT64)) AS total_spend,
            SUM(SAFE_CAST(r.impressions AS INT64)) AS total_impressions,
            SUM(SAFE_CAST(r.clicks AS INT64)) AS total_clicks,
            SUM(SAFE_CAST(r.reach AS INT64)) AS total_reach,
            SUM(SAFE_CAST(r.conversion AS INT64)) AS total_conversions,
            SUM(SAFE_CAST(r.result AS INT64)) AS total_results
        FROM
            `nekt_raw.tiktok_ads_campaigns` c
            LEFT JOIN `nekt_raw.tiktok_ads_campaigns_daily_report` r 
                ON c.campaign_id = r.campaign_id
        WHERE
            PARSE_DATE('%Y-%m-%d', r.stat_time_day) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
        GROUP BY
            c.campaign_name,
            c.objective_type,
            c.operation_status,
            c.budget_mode
    )
SELECT
    campaign_name,
    objective_type,
    operation_status,
    total_spend,
    total_impressions,
    total_clicks,
    total_reach,
    total_conversions,
    total_results,
    ROUND(SAFE_DIVIDE(total_clicks * 100.0, total_impressions), 2) AS ctr,
    ROUND(SAFE_DIVIDE(total_spend, total_clicks), 2) AS cpc,
    ROUND(SAFE_DIVIDE(total_spend, total_conversions), 2) AS cpa
FROM
    campaign_metrics
ORDER BY
    total_spend DESC

SELECT
    a.ad_name,
    a.ad_format,
    SUM(SAFE_CAST(r.video_play_actions AS INT64)) AS total_plays,
    SUM(SAFE_CAST(r.video_watched_2s AS INT64)) AS watched_2s,
    SUM(SAFE_CAST(r.video_watched_6s AS INT64)) AS watched_6s,
    SUM(SAFE_CAST(r.video_views_p25 AS INT64)) AS views_25pct,
    SUM(SAFE_CAST(r.video_views_p50 AS INT64)) AS views_50pct,
    SUM(SAFE_CAST(r.video_views_p75 AS INT64)) AS views_75pct,
    SUM(SAFE_CAST(r.video_views_p100 AS INT64)) AS views_100pct,
    AVG(SAFE_CAST(r.average_video_play AS FLOAT64)) AS avg_watch_time,
    ROUND(
        SAFE_DIVIDE(
            SUM(SAFE_CAST(r.video_views_p100 AS FLOAT64)) * 100.0,
            SUM(SAFE_CAST(r.video_play_actions AS FLOAT64))
        ), 2
    ) AS completion_rate
FROM
    `nekt_raw.tiktok_ads_ads` a
    LEFT JOIN `nekt_raw.tiktok_ads_ads_daily_report` r ON a.ad_id = r.ad_id
WHERE
    PARSE_DATE('%Y-%m-%d', r.stat_time_day) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND a.video_id IS NOT NULL
GROUP BY
    a.ad_name,
    a.ad_format
ORDER BY
    total_plays DESC
LIMIT 20

SELECT
    age,
    gender,
    SUM(SAFE_CAST(spend AS FLOAT64)) AS total_spend,
    SUM(SAFE_CAST(impressions AS INT64)) AS total_impressions,
    SUM(SAFE_CAST(clicks AS INT64)) AS total_clicks,
    SUM(SAFE_CAST(conversion AS INT64)) AS total_conversions,
    ROUND(SAFE_DIVIDE(SUM(SAFE_CAST(clicks AS FLOAT64)) * 100.0, 
        SUM(SAFE_CAST(impressions AS FLOAT64))), 2) AS ctr,
    ROUND(SAFE_DIVIDE(SUM(SAFE_CAST(spend AS FLOAT64)), 
        SUM(SAFE_CAST(conversion AS FLOAT64))), 2) AS cpa
FROM
    `nekt_raw.tiktok_ads_ads_age_gender_report`
WHERE
    PARSE_DATE('%Y-%m-%d', stat_time_day) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY
    age,
    gender
ORDER BY
    total_spend DESC