WITH
    daily_funnel AS (
        SELECT
            DATE(reference_day) AS reference_date,
            visitors_count,
            contacts_count,
            qualified_contacts_count,
            opportunities_count,
            sales_count,
            SAFE_DIVIDE(CAST(contacts_count AS FLOAT64) * 100.0, visitors_count) AS visitor_to_contact_rate,
            SAFE_DIVIDE(CAST(qualified_contacts_count AS FLOAT64) * 100.0, contacts_count) AS contact_to_mql_rate,
            SAFE_DIVIDE(CAST(opportunities_count AS FLOAT64) * 100.0, qualified_contacts_count) AS mql_to_opportunity_rate,
            SAFE_DIVIDE(CAST(sales_count AS FLOAT64) * 100.0, opportunities_count) AS opportunity_to_sale_rate
        FROM
            `nekt_raw.rd_station_analytics_funnel`
        WHERE
            DATE(reference_day) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    )
SELECT
    reference_date,
    visitors_count,
    contacts_count,
    qualified_contacts_count,
    opportunities_count,
    sales_count,
    ROUND(visitor_to_contact_rate, 2) AS visitor_to_contact_pct,
    ROUND(contact_to_mql_rate, 2) AS contact_to_mql_pct,
    ROUND(mql_to_opportunity_rate, 2) AS mql_to_opportunity_pct,
    ROUND(opportunity_to_sale_rate, 2) AS opportunity_to_sale_pct
FROM
    daily_funnel
ORDER BY
    reference_date DESC

WITH
    source_conversions AS (
        SELECT
            COALESCE(payload.traffic_source, 'Direct') AS traffic_source,
            COALESCE(payload.traffic_medium, 'None') AS traffic_medium,
            COALESCE(payload.traffic_campaign, 'None') AS traffic_campaign,
            COUNT(DISTINCT contact_uuid) AS unique_leads,
            COUNT(*) AS total_conversions
        FROM
            `nekt_raw.rd_station_contact_events`
        WHERE
            event_type = 'CONVERSION'
            AND DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
        GROUP BY
            payload.traffic_source,
            payload.traffic_medium,
            payload.traffic_campaign
    )
SELECT
    traffic_source,
    traffic_medium,
    traffic_campaign,
    unique_leads,
    total_conversions,
    ROUND(SAFE_DIVIDE(CAST(total_conversions AS FLOAT64), unique_leads), 2) AS conversions_per_lead
FROM
    source_conversions
ORDER BY
    unique_leads DESC

WITH
    campaign_metrics AS (
        SELECT
            campaign_name,
            campaign_id,
            SUM(contacts_count) AS total_contacts,
            SUM(email_delivered_count) AS total_delivered,
            SUM(email_opened_count) AS total_opens,
            SUM(email_clicked_count) AS total_clicks,
            SUM(email_bounced_count) AS total_bounces,
            SUM(email_unsubscribed_count) AS total_unsubscribes,
            SUM(email_spam_reported_count) AS total_spam_reports,
            AVG(email_delivered_rate) AS avg_delivery_rate,
            AVG(email_opened_rate) AS avg_open_rate,
            AVG(email_clicked_rate) AS avg_click_rate
        FROM
            `nekt_raw.rd_station_analytics_email_stats`
        WHERE
            DATE(query_date_start) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
        GROUP BY
            campaign_name,
            campaign_id
    )
SELECT
    campaign_name,
    total_contacts,
    total_delivered,
    total_opens,
    total_clicks,
    ROUND(avg_delivery_rate * 100, 2) AS delivery_rate_pct,
    ROUND(avg_open_rate * 100, 2) AS open_rate_pct,
    ROUND(avg_click_rate * 100, 2) AS click_rate_pct,
    total_bounces,
    total_unsubscribes,
    ROUND(
        SAFE_DIVIDE(CAST(total_clicks AS FLOAT64) * 100.0, total_opens),
        2
    ) AS click_to_open_rate
FROM
    campaign_metrics
ORDER BY
    total_delivered DESC

WITH
    workflow_metrics AS (
        SELECT
            workflow_name,
            workflow_id,
            email_name,
            SUM(contacts_count) AS total_contacts,
            SUM(email_delivered_count) AS total_delivered,
            SUM(email_opened_unique_count) AS unique_opens,
            SUM(email_clicked_unique_count) AS unique_clicks,
            SUM(email_hard_bounced_unique_count) AS hard_bounces,
            SUM(email_soft_bounced_unique_count) AS soft_bounces,
            SUM(email_unsubscribed_count) AS total_unsubscribes,
            AVG(email_opened_rate) AS avg_open_rate,
            AVG(email_clicked_rate) AS avg_click_rate
        FROM
            `nekt_raw.rd_station_analytics_workflow_email_stats`
        WHERE
            DATE(query_date_start) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
        GROUP BY
            workflow_name,
            workflow_id,
            email_name
    )
SELECT
    workflow_name,
    email_name,
    total_contacts,
    total_delivered,
    unique_opens,
    unique_clicks,
    ROUND(avg_open_rate * 100, 2) AS open_rate_pct,
    ROUND(avg_click_rate * 100, 2) AS click_rate_pct,
    ROUND(
        SAFE_DIVIDE(CAST(unique_clicks AS FLOAT64) * 100.0, unique_opens),
        2
    ) AS click_to_open_rate,
    hard_bounces + soft_bounces AS total_bounces,
    total_unsubscribes
FROM
    workflow_metrics
ORDER BY
    workflow_name,
    total_delivered DESC

WITH
    asset_performance AS (
        SELECT
            asset_type,
            asset_identifier,
            asset_id,
            SUM(visits_count) AS total_visits,
            SUM(conversion_count) AS total_conversions,
            AVG(conversion_rate) AS avg_conversion_rate
        FROM
            `nekt_raw.rd_station_analytics_conversion_assets`
        WHERE
            DATE(query_date_start) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
        GROUP BY
            asset_type,
            asset_identifier,
            asset_id
    )
SELECT
    asset_type,
    asset_identifier,
    total_visits,
    total_conversions,
    ROUND(avg_conversion_rate * 100, 2) AS conversion_rate_pct,
    CASE
        WHEN total_visits > 100 AND avg_conversion_rate < 0.02 THEN 'High traffic, low conversion - optimize!'
        WHEN total_visits > 100 AND avg_conversion_rate >= 0.05 THEN 'High performer'
        WHEN total_visits <= 100 AND avg_conversion_rate >= 0.05 THEN 'Good conversion, needs more traffic'
        ELSE 'Monitor'
    END AS recommendation
FROM
    asset_performance
ORDER BY
    total_visits DESC

WITH
    contact_stats AS (
        SELECT
            DATE_TRUNC(created_at, MONTH) AS acquisition_month,
            COUNT(DISTINCT uuid) AS new_contacts,
            COUNT(DISTINCT CASE 
                WHEN last_conversion_date IS NOT NULL 
                THEN uuid 
            END) AS contacts_with_conversion,
            MIN(created_at) AS earliest_contact,
            MAX(last_conversion_date) AS latest_conversion
        FROM
            `nekt_raw.rd_station_segmentation_contacts`
        WHERE
            created_at IS NOT NULL
        GROUP BY
            DATE_TRUNC(created_at, MONTH)
    )
SELECT
    acquisition_month,
    new_contacts,
    contacts_with_conversion,
    ROUND(
        SAFE_DIVIDE(CAST(contacts_with_conversion AS FLOAT64) * 100.0, new_contacts),
        2
    ) AS conversion_rate_pct,
    SUM(new_contacts) OVER (ORDER BY acquisition_month) AS cumulative_contacts
FROM
    contact_stats
ORDER BY
    acquisition_month DESC

WITH
    daily_conversions AS (
        SELECT
            DATE(event_timestamp) AS conversion_date,
            FORMAT_DATE('%A', DATE(event_timestamp)) AS day_of_week,
            COUNT(*) AS total_conversions,
            COUNT(DISTINCT contact_uuid) AS unique_leads,
            COUNT(DISTINCT payload.conversion_identifier) AS unique_assets
        FROM
            `nekt_raw.rd_station_contact_events`
        WHERE
            event_type = 'CONVERSION'
            AND DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
        GROUP BY
            DATE(event_timestamp),
            FORMAT_DATE('%A', DATE(event_timestamp))
    ),
    weekly_averages AS (
        SELECT
            day_of_week,
            AVG(total_conversions) AS avg_conversions,
            AVG(unique_leads) AS avg_leads,
            AVG(unique_assets) AS avg_assets,
            COUNT(*) AS days_counted
        FROM
            daily_conversions
        GROUP BY
            day_of_week
    )
SELECT
    day_of_week,
    ROUND(avg_conversions, 1) AS avg_daily_conversions,
    ROUND(avg_leads, 1) AS avg_daily_leads,
    ROUND(avg_assets, 1) AS avg_assets_used,
    days_counted,
    CASE
        WHEN avg_conversions >= (SELECT MAX(avg_conversions) * 0.8 FROM weekly_averages) THEN 'Peak Day'
        WHEN avg_conversions <= (SELECT MIN(avg_conversions) * 1.2 FROM weekly_averages) THEN 'Low Day'
        ELSE 'Average'
    END AS day_classification
FROM
    weekly_averages
ORDER BY
    CASE day_of_week
        WHEN 'Monday' THEN 1
        WHEN 'Tuesday' THEN 2
        WHEN 'Wednesday' THEN 3
        WHEN 'Thursday' THEN 4
        WHEN 'Friday' THEN 5
        WHEN 'Saturday' THEN 6
        WHEN 'Sunday' THEN 7
    END