Цен-ь и атр-й дох- по камп&типам устр&типам польз&Город&Рег

--Ценность и атрибутированный доход по кампаниям и типам устройств и типам пользователей и городам и регионам в сравнении между моделями атрибуции [New] Funnel Based with Completed Orders и GA Last Non-Direct Click с 1 июня 2019 по 30 июня 2019
SELECT
    SOURCE,
    medium,
    campaign,
    device,
    user_type,
    city,
    region,
    sessions_count,
    cost,
    transactions_count,
    _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_value AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue,



        --GA Last Non-Direct Click
        _last_click_attribution_value_online AS _last_click_attribution_value_online
,
_last_click_attribution_attributed_revenue_online AS _last_click_attribution_attributed_revenue_online,

            _last_click_attribution_value_online_change,
                _last_click_attribution_value_change,
            _last_click_attribution_attributed_revenue_online_change,
                _last_click_attribution_attributed_revenue_change,



FROM
(



    SELECT
        SOURCE AS SOURCE,
        medium AS medium,
        campaign AS campaign,
        device AS device,
        user_type AS user_type,
        city AS city,
        region AS region,
        sessions_count,
        transactions_count,
        cost,
        _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online_summary AS
_38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online_summary
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_value AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_value_summary AS
_38bce04635ee4d74960b4ae47fcc4be0_attribution_value_summary
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online_summary AS
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online_summary
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue
,
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_summary AS
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_summary,

        sessions_count_summary,
        transactions_count_summary,
        cost_summary

        --GA Last Non-Direct Click
        , _last_click_attribution_value_online AS _last_click_attribution_value_online
,
_last_click_attribution_value_online_summary AS
_last_click_attribution_value_online_summary
,
_last_click_attribution_attributed_revenue_online AS _last_click_attribution_attributed_revenue_online
,
_last_click_attribution_attributed_revenue_online_summary AS
_last_click_attribution_attributed_revenue_online_summary,
                _last_click_attribution_value_online_change,
                _last_click_attribution_value_change,
                _last_click_attribution_attributed_revenue_online_change,
                _last_click_attribution_attributed_revenue_change

    FROM
    (


    SELECT
            COALESCE(t1.SOURCE, t2.SOURCE) AS SOURCE,
            COALESCE(t1.medium, t2.medium) AS medium,
            COALESCE(t1.campaign, t2.campaign) AS campaign,
            COALESCE(t1.device, t2.device) AS device,
            COALESCE(t1.user_type, t2.user_type) AS user_type,
            COALESCE(t1.city, t2.city) AS city,
            COALESCE(t1.region, t2.region) AS region,
        IFNULL(t1.cost, 0) AS cost,

        t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online
,
FIRST_VALUE(t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online_summary) OVER() AS
_38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online_summary
,
t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_value AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value
,
FIRST_VALUE(t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_value_summary) OVER() AS
_38bce04635ee4d74960b4ae47fcc4be0_attribution_value_summary
,
t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online
,
FIRST_VALUE(t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online_summary) OVER() AS
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online_summary
,
t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue
,
FIRST_VALUE(t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_summary) OVER() AS
_38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_summary,

        t1.cost_summary AS cost_summary,
        t1.sessions_count AS sessions_count,
        FIRST_VALUE(t1.sessions_count_summary) OVER() AS sessions_count_summary,
        t1.transactions_count AS transactions_count,
        FIRST_VALUE(t1.transactions_count_summary) OVER() AS transactions_count_summary

            --GA Last Non-Direct Click

            , t2._last_click_attribution_value_online AS _last_click_attribution_value_online
,
FIRST_VALUE(t2._last_click_attribution_value_online_summary) OVER() AS
_last_click_attribution_value_online_summary
,
t2._last_click_attribution_attributed_revenue_online AS _last_click_attribution_attributed_revenue_online
,
FIRST_VALUE(t2._last_click_attribution_attributed_revenue_online_summary) OVER() AS
_last_click_attribution_attributed_revenue_online_summary,


                (t2._last_click_attribution_value_online - t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online) / t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online AS _last_click_attribution_value_online_change,
                (t2._last_click_attribution_value - t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_value) / t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_value AS _last_click_attribution_value_change,

                (t2._last_click_attribution_attributed_revenue_online - t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online) / t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online AS _last_click_attribution_attributed_revenue_online_change,
                (t2._last_click_attribution_attributed_revenue - t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue) / t1._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue AS _last_click_attribution_attributed_revenue_change


    FROM
    (



    SELECT
        *
    FROM
    (
        SELECT
            SOURCE,
            medium,
            campaign,
            device,
            user_type,
            city,
            region,

                value_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online,
                VALUE AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value,
                    SUM(_38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online) OVER() AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online_summary,
                    SUM(_38bce04635ee4d74960b4ae47fcc4be0_attribution_value) OVER() AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_summary,
                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,
                        IFNULL(sessions.device, '(not set)') AS device,
                        IFNULL(sessions.user_type, '(not set)') AS user_type,
                        IFNULL(sessions.city, '(not set)') AS city,
                        IFNULL(sessions.region, '(not set)') AS region,
                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
,
                MAX(device) AS device
,
                MAX(user_type) AS user_type
,
                MAX(city) AS city
,
                MAX(region) AS region

        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,
            FIRST(device.deviceCategory) AS device,
            FIRST(IF (visitNumber IS NULL, '(not set)', IF (visitNumber=1, 'New', 'Returning'))) AS user_type,
            FIRST(geoNetwork.city) AS city,
            FIRST(geoNetwork.region) AS region,


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


    HAVING sessionTimestamp BETWEEN DATE_ADD(TIMESTAMP('2019-06-01'), -30, 'DAY') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 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,
            FIRST('offline') AS device,
            FIRST('(not set)') AS user_type,
            FIRST('(not set)') AS city,
            FIRST('(not set)') AS region,


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


    HAVING sessionTimestamp BETWEEN DATE_ADD(TIMESTAMP('2019-06-01'), -30, 'DAY') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 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_], TIMESTAMP('2019-06-01'), DATE_ADD(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 1, 'day'), -1, 'second'), 1, 'DAY'))    GROUP EACH BY
        session_id


    HAVING sessionTimestamp BETWEEN TIMESTAMP('2019-06-01') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 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 TIMESTAMP('2019-06-01') AND DATE_ADD(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 1, 'day'), -1, 'second'), 1, 'DAY')    GROUP EACH BY
        session_id


    HAVING sessionTimestamp BETWEEN TIMESTAMP('2019-06-01') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 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(TIMESTAMP('2019-06-01'), -30,'DAY')) AND DATE(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 1, 'day'), -1, 'second'))
        AND DATE(MSEC_TO_TIMESTAMP(transaction_time)) BETWEEN DATE(TIMESTAMP('2019-06-01')) AND DATE(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 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_], TIMESTAMP('2019-06-01'), DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 1, 'day'), -1, 'second'))    GROUP EACH BY
        session_id

            ) AS costs
            ON
                costs.session_id = sessions.session_id
            GROUP EACH BY
                SOURCE,
                medium,
                campaign,
                device,
                user_type,
                city,
                region
        )
    )

    ) AS t1
        FULL OUTER JOIN EACH
        (



    SELECT
        *
    FROM
    (
        SELECT
            SOURCE,
            medium,
            campaign,
            device,
            user_type,
            city,
            region,

                value_online AS _last_click_attribution_value_online,
                VALUE AS _last_click_attribution_value,
                    SUM(_last_click_attribution_value_online) OVER() AS _last_click_attribution_value_online_summary,
                    SUM(_last_click_attribution_value) OVER() AS _last_click_attribution_value_summary,
                attributed_revenue_online AS _last_click_attribution_attributed_revenue_online,
                attributed_revenue AS _last_click_attribution_attributed_revenue,
                    SUM(_last_click_attribution_attributed_revenue_online) OVER() AS _last_click_attribution_attributed_revenue_online_summary,
                    SUM(_last_click_attribution_attributed_revenue) OVER() AS _last_click_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(sessions.SOURCE, revenues.SOURCE) AS SOURCE,
                        COALESCE(sessions.medium, revenues.medium) AS medium,
                        COALESCE(sessions.campaign, revenues.campaign) AS campaign,
                        IFNULL(sessions.device, '(not set)') AS device,
                        IFNULL(sessions.user_type, '(not set)') AS user_type,
                        IFNULL(sessions.city, '(not set)') AS city,
                        IFNULL(sessions.region, '(not set)') AS region,
                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
,
                MAX(device) AS device
,
                MAX(user_type) AS user_type
,
                MAX(city) AS city
,
                MAX(region) AS region

        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,
            FIRST(device.deviceCategory) AS device,
            FIRST(IF (visitNumber IS NULL, '(not set)', IF (visitNumber=1, 'New', 'Returning'))) AS user_type,
            FIRST(geoNetwork.city) AS city,
            FIRST(geoNetwork.region) AS region,


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


    HAVING sessionTimestamp BETWEEN DATE_ADD(TIMESTAMP('2019-06-01'), -30, 'DAY') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 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,
            FIRST('offline') AS device,
            FIRST('(not set)') AS user_type,
            FIRST('(not set)') AS city,
            FIRST('(not set)') AS region,


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


    HAVING sessionTimestamp BETWEEN DATE_ADD(TIMESTAMP('2019-06-01'), -30, 'DAY') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 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_], TIMESTAMP('2019-06-01'), DATE_ADD(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 1, 'day'), -1, 'second'), 1, 'DAY'))    GROUP EACH BY
        session_id


    HAVING sessionTimestamp BETWEEN TIMESTAMP('2019-06-01') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 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 TIMESTAMP('2019-06-01') AND DATE_ADD(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 1, 'day'), -1, 'second'), 1, 'DAY')    GROUP EACH BY
        session_id


    HAVING sessionTimestamp BETWEEN TIMESTAMP('2019-06-01') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 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
            value_from_sid AS session_id,
            1 AS 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(TIMESTAMP('2019-06-01'), -30,'DAY')) AND DATE(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 1, 'day'), -1, 'second'))
        AND DATE(MSEC_TO_TIMESTAMP(transaction_time)) BETWEEN DATE(TIMESTAMP('2019-06-01')) AND DATE(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 1, 'day'), -1, 'second'))

                AND active_step == 5
    )
    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_], TIMESTAMP('2019-06-01'), DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-06-30')), 1, 'day'), -1, 'second'))    GROUP EACH BY
        session_id

            ) AS costs
            ON
                costs.session_id = sessions.session_id
            GROUP EACH BY
                SOURCE,
                medium,
                campaign,
                device,
                user_type,
                city,
                region
        )
    )

        ) AS t2
        ON
            t1.SOURCE = t2.SOURCE AND
            t1.medium = t2.medium AND
            t1.campaign = t2.campaign AND
            t1.device = t2.device AND
            t1.user_type = t2.user_type AND
            t1.city = t2.city AND
            t1.region = t2.region




    )



)

        ORDER BY sessions_count DESC
        LIMIT 2500