Атриб-й доход по камп в FBC за посл 7д

--Атрибутированный доход по кампаниям в модели атрибуции [New] Funnel Based with Completed Orders за последние 7 дней
SELECT
    SOURCE,
    medium,
    campaign,
    sessions_count,
    transactions_count,
    cost,
    _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue,


FROM
(

    SELECT
        SOURCE AS SOURCE,
        medium AS medium,
        campaign AS campaign,
        sessions_count,
        transactions_count,
        cost,
        _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue,
        sessions_count_summary,
        cost_summary,
        transactions_count_summary
    FROM
    (



    SELECT
        *
    FROM
    (
        SELECT
            SOURCE,
            medium,
            campaign,

                attributed_revenue_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online,
                attributed_revenue AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue,
                    SUM(_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online) OVER() AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online_summary,
                    SUM(_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue) OVER() AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_summary,

            IFNULL(cost, 0) AS cost,
            SUM(cost) OVER() AS cost_summary,
            sessions_count,
            SUM(sessions_count) OVER() AS sessions_count_summary,
            transactions_count,
            SUM(transactions_count) OVER() AS transactions_count_summary
        FROM
        (
            SELECT
                        COALESCE(revenues.SOURCE, sessions.SOURCE) AS SOURCE,
                        COALESCE(revenues.medium, sessions.medium) AS medium,
                        COALESCE(revenues.campaign, sessions.campaign) AS campaign,
                SUM(revenues.attributed_revenue) AS attributed_revenue,
                SUM(revenues.attributed_revenue_online) AS attributed_revenue_online,
                SUM(revenues.VALUE) AS VALUE,
                SUM(revenues.value_online) AS value_online,
                SUM(costs.cost) AS cost,
                COUNT(DISTINCT summary.session_id, 1000000) AS sessions_count,
                SUM(revenues.transactions_count) AS transactions_count
            FROM
            (
        SELECT
                session_id
                ,
                MAX(SOURCE) AS SOURCE
,
                MAX(medium) AS medium
,
                MAX(campaign) AS campaign

        FROM
        (
    SELECT
        sessionId AS session_id,
        MIN(SEC_TO_TIMESTAMP(hits.TIME)) AS sessionTimestamp,
            FIRST(trafficSource.SOURCE) AS SOURCE,
            FIRST(trafficSource.medium) AS medium,
            FIRST(trafficSource.campaign) AS campaign,


FROM TABLE_DATE_RANGE([owox-140513:OWOXBI_Streaming.session_streaming_], DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -7,'DAY'), -30, 'DAY'), DATE_ADD(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second'), 1, 'DAY'))    GROUP EACH BY
        session_id


    HAVING sessionTimestamp BETWEEN DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -7,'DAY'), -30, 'DAY') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second')
        ),
        (
    SELECT
        CONCAT('offline', '.', STRING(IF(user_id IS NULL, 'null', user_id)), '_', STRING(TIMESTAMP_TO_SEC(TIMESTAMP(TIME)))) AS session_id,
        MIN(TIMESTAMP(TIME)) AS sessionTimestamp,
            FIRST('(not set)') AS SOURCE,
            FIRST('offline') AS medium,
            FIRST('(not set)') AS campaign,


FROM [owox-140513:1c.completed_orders_view] WHERE TIMESTAMP(TIME) BETWEEN DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -7,'DAY'), -30, 'DAY') AND DATE_ADD(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second'), 1, 'DAY')    GROUP EACH BY
        session_id


    HAVING sessionTimestamp BETWEEN DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -7,'DAY'), -30, 'DAY') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second')
        )
    GROUP EACH BY
        session_id
            ) AS sessions
            LEFT JOIN EACH
            (
        SELECT
                session_id

        FROM
        (
    SELECT
        sessionId AS session_id,
        MIN(SEC_TO_TIMESTAMP(hits.TIME)) AS sessionTimestamp,


FROM TABLE_DATE_RANGE([owox-140513:OWOXBI_Streaming.session_streaming_], DATE_ADD(UTC_USEC_TO_DAY(NOW()), -7,'DAY'), DATE_ADD(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second'), 1, 'DAY'))    GROUP EACH BY
        session_id


    HAVING sessionTimestamp BETWEEN DATE_ADD(UTC_USEC_TO_DAY(NOW()), -7,'DAY') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second')
        ),
        (
    SELECT
        CONCAT('offline', '.', STRING(IF(user_id IS NULL, 'null', user_id)), '_', STRING(TIMESTAMP_TO_SEC(TIMESTAMP(TIME)))) AS session_id,
        MIN(TIMESTAMP(TIME)) AS sessionTimestamp,


FROM [owox-140513:1c.completed_orders_view] WHERE TIMESTAMP(TIME) BETWEEN DATE_ADD(UTC_USEC_TO_DAY(NOW()), -7,'DAY') AND DATE_ADD(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second'), 1, 'DAY')    GROUP EACH BY
        session_id


    HAVING sessionTimestamp BETWEEN DATE_ADD(UTC_USEC_TO_DAY(NOW()), -7,'DAY') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second')
        )
    GROUP EACH BY
        session_id
            ) AS summary
            ON
                summary.session_id = sessions.session_id
            LEFT JOIN EACH
            (
    SELECT
        session_id,
            FIRST(SOURCE) AS SOURCE,
            FIRST(medium) AS medium,
            FIRST(campaign) AS campaign,
        SUM(VALUE) AS VALUE,
        SUM(IF(transaction_source!=101, VALUE, 0)) AS value_online,
        SUM(revenue * VALUE) AS attributed_revenue,
        SUM(IF(transaction_source!=101, revenue, 0) * IF(transaction_source!=101, VALUE, 0)) AS attributed_revenue_online,
        SUM(IF(active_step == 5, 1, 0)) AS transactions_count
    FROM
    (
        SELECT
            session_id,
            VALUE,
                    IFNULL(SOURCE, 'NULL') AS SOURCE,
                    IFNULL(medium, 'NULL') AS medium,
                    IFNULL(campaign, 'NULL') AS campaign,
            revenue,
            active_step,
            MAX(IF(active_step == 5, data_source, NULL)) OVER(PARTITION BY transaction_id) AS transaction_source,
    RANK() OVER(PARTITION BY transaction_id ORDER BY DATE DESC) AS rowf
        FROM
            [owox-140513:Funnel_Based_with_Completed_Orders.VALUES]
        WHERE
        DATE(_PARTITIONTIME) BETWEEN DATE(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -7,'DAY'), -30,'DAY')) AND DATE(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second'))
        AND DATE(MSEC_TO_TIMESTAMP(transaction_time)) BETWEEN DATE(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -7,'DAY')) AND DATE(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second'))

    )
    WHERE
        rowf = 1
    GROUP EACH BY
        session_id
            ) AS revenues
            ON
                revenues.session_id = sessions.session_id
            LEFT JOIN EACH
            (

    SELECT
        sessionId AS session_id,
        SUM(trafficSource.attributedAdCost) AS cost

FROM TABLE_DATE_RANGE([owox-140513:OWOXBI_Streaming.session_streaming_], DATE_ADD(UTC_USEC_TO_DAY(NOW()), -7,'DAY'), DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(DATE_ADD(UTC_USEC_TO_DAY(NOW()), -1,'DAY')), 1, 'day'), -1, 'second'))    GROUP EACH BY
        session_id

            ) AS costs
            ON
                costs.session_id = sessions.session_id
            GROUP EACH BY
                SOURCE,
                medium,
                campaign
        )
    )

    )

)

        ORDER BY sessions_count DESC
        LIMIT 2500