Атрибутированный доход по ист-кан в сравн м//LC и FB 300619-

--Атрибутированный доход по источникам и каналам в сравнении между моделями атрибуции GA Last Non-Direct Click и Funnel Based с 30 июня 2019 по 29 июля 2019
SELECT
    SOURCE,
    medium,
    sessions_count,
    cost,
    transactions_count,
    _last_click_attribution_attributed_revenue_online AS _last_click_attribution_attributed_revenue_online,



        --Funnel Based
        _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online AS _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online
,
_5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue AS _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue,

            _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online_change,
                _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_change,



FROM
(



    SELECT
        SOURCE AS SOURCE,
        medium AS medium,
        sessions_count,
        transactions_count,
        cost,
        _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,

        sessions_count_summary,
        transactions_count_summary,
        cost_summary

        --Funnel Based
        , _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online AS _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online
,
_5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online_summary AS
_5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online_summary
,
_5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue AS _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue
,
_5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_summary AS
_5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_summary,
                _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online_change,
                _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_change

    FROM
    (


    SELECT
            COALESCE(t1.SOURCE, t2.SOURCE) AS SOURCE,
            COALESCE(t1.medium, t2.medium) AS medium,
        IFNULL(t1.cost, 0) AS cost,

        t1._last_click_attribution_attributed_revenue_online AS _last_click_attribution_attributed_revenue_online
,
FIRST_VALUE(t1._last_click_attribution_attributed_revenue_online_summary) OVER() AS
_last_click_attribution_attributed_revenue_online_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

            --Funnel Based

            , t2._5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online AS _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online
,
FIRST_VALUE(t2._5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online_summary) OVER() AS
_5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online_summary
,
t2._5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue AS _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue
,
FIRST_VALUE(t2._5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_summary) OVER() AS
_5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_summary,


                (t2._5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online - t1._last_click_attribution_attributed_revenue_online) / t1._last_click_attribution_attributed_revenue_online AS _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online_change,
                (t2._5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue - t1._last_click_attribution_attributed_revenue) / t1._last_click_attribution_attributed_revenue AS _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_change


    FROM
    (



    SELECT
        *
    FROM
    (
        SELECT
            SOURCE,
            medium,

                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,
                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
        sessionId AS session_id,
        MIN(SEC_TO_TIMESTAMP(hits.TIME)) AS sessionTimestamp,
            FIRST(trafficSource.SOURCE) AS SOURCE,
            FIRST(trafficSource.medium) AS medium,


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


    HAVING sessionTimestamp BETWEEN DATE_ADD(TIMESTAMP('2019-06-30'), -30, 'DAY') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 1, 'day'), -1, 'second')

            ) AS sessions
            LEFT JOIN EACH
            (

    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-30'), DATE_ADD(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 1, 'day'), -1, 'second'), 1, 'DAY'))    GROUP EACH BY
        session_id


    HAVING sessionTimestamp BETWEEN TIMESTAMP('2019-06-30') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 1, 'day'), -1, 'second')

            ) AS summary
            ON
                summary.session_id = sessions.session_id
            LEFT JOIN EACH
            (
    SELECT
        session_id,
            FIRST(SOURCE) AS SOURCE,
            FIRST(medium) AS medium,
        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,
            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:Attribution_FunnelBased.VALUES]
        WHERE
        DATE(_PARTITIONTIME) BETWEEN DATE(DATE_ADD(TIMESTAMP('2019-06-30'), -30,'DAY')) AND DATE(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 1, 'day'), -1, 'second'))
        AND DATE(MSEC_TO_TIMESTAMP(transaction_time)) BETWEEN DATE(TIMESTAMP('2019-06-30')) AND DATE(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 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-30'), DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 1, 'day'), -1, 'second'))    GROUP EACH BY
        session_id

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

    ) AS t1
        FULL OUTER JOIN EACH
        (



    SELECT
        *
    FROM
    (
        SELECT
            SOURCE,
            medium,

                attributed_revenue_online AS _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online,
                attributed_revenue AS _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue,
                    SUM(_5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online) OVER() AS _5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue_online_summary,
                    SUM(_5222fa8b1f644e9aa30ad723a5199319_attribution_attributed_revenue) OVER() AS _5222fa8b1f644e9aa30ad723a5199319_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,
                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
        sessionId AS session_id,
        MIN(SEC_TO_TIMESTAMP(hits.TIME)) AS sessionTimestamp,
            FIRST(trafficSource.SOURCE) AS SOURCE,
            FIRST(trafficSource.medium) AS medium,


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


    HAVING sessionTimestamp BETWEEN DATE_ADD(TIMESTAMP('2019-06-30'), -30, 'DAY') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 1, 'day'), -1, 'second')

            ) AS sessions
            LEFT JOIN EACH
            (

    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-30'), DATE_ADD(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 1, 'day'), -1, 'second'), 1, 'DAY'))    GROUP EACH BY
        session_id


    HAVING sessionTimestamp BETWEEN TIMESTAMP('2019-06-30') AND DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 1, 'day'), -1, 'second')

            ) AS summary
            ON
                summary.session_id = sessions.session_id
            LEFT JOIN EACH
            (
    SELECT
        session_id,
            FIRST(SOURCE) AS SOURCE,
            FIRST(medium) AS medium,
        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,
            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:Attribution_FunnelBased.VALUES]
        WHERE
        DATE(_PARTITIONTIME) BETWEEN DATE(DATE_ADD(TIMESTAMP('2019-06-30'), -30,'DAY')) AND DATE(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 1, 'day'), -1, 'second'))
        AND DATE(MSEC_TO_TIMESTAMP(transaction_time)) BETWEEN DATE(TIMESTAMP('2019-06-30')) AND DATE(DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 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-30'), DATE_ADD(DATE_ADD(UTC_USEC_TO_DAY(TIMESTAMP('2019-07-29')), 1, 'day'), -1, 'second'))    GROUP EACH BY
        session_id

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

        ) AS t2
        ON
            t1.SOURCE = t2.SOURCE AND
            t1.medium = t2.medium




    )



)

        ORDER BY sessions_count DESC
        LIMIT 2500