Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Ценность и атрибутированный доход по кампаниям в сравнении между моделями атрибуции GA Last Non-Direct Click и [New] Funnel Based with Completed Orders с 1 июня 2019 по 30 июня 2019
- SELECT
- SOURCE,
- medium,
- campaign,
- sessions_count,
- cost,
- transactions_count,
- _last_click_attribution_value_online AS _last_click_attribution_value_online
- ,
- _last_click_attribution_attributed_revenue_online AS _last_click_attribution_attributed_revenue_online,
- --[New] Funnel Based with Completed Orders
- _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,
- _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online_change,
- _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_change,
- _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online_change,
- _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_change,
- FROM
- (
- SELECT
- SOURCE AS SOURCE,
- medium AS medium,
- campaign AS campaign,
- sessions_count,
- transactions_count,
- cost,
- _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,
- sessions_count_summary,
- transactions_count_summary,
- cost_summary
- --[New] Funnel Based with Completed Orders
- , _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,
- _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online_change,
- _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_change,
- _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online_change,
- _38bce04635ee4d74960b4ae47fcc4be0_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,
- IFNULL(t1.cost, 0) AS cost,
- t1._last_click_attribution_value_online AS _last_click_attribution_value_online
- ,
- FIRST_VALUE(t1._last_click_attribution_value_online_summary) OVER() AS
- _last_click_attribution_value_online_summary
- ,
- 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
- --[New] Funnel Based with Completed Orders
- , t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online
- ,
- FIRST_VALUE(t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online_summary) OVER() AS
- _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online_summary
- ,
- t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_value AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value
- ,
- FIRST_VALUE(t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_value_summary) OVER() AS
- _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_summary
- ,
- t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online
- ,
- FIRST_VALUE(t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online_summary) OVER() AS
- _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online_summary
- ,
- t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue
- ,
- FIRST_VALUE(t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_summary) OVER() AS
- _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_summary,
- (t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online - t1._last_click_attribution_value_online) / t1._last_click_attribution_value_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_online_change,
- (t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_value - t1._last_click_attribution_value) / t1._last_click_attribution_value AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_value_change,
- (t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online - t1._last_click_attribution_attributed_revenue_online) / t1._last_click_attribution_attributed_revenue_online AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_online_change,
- (t2._38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue - t1._last_click_attribution_attributed_revenue) / t1._last_click_attribution_attributed_revenue AS _38bce04635ee4d74960b4ae47fcc4be0_attribution_attributed_revenue_change
- FROM
- (
- SELECT
- *
- FROM
- (
- SELECT
- SOURCE,
- medium,
- campaign,
- 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,
- 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(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,
- 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
- )
- )
- ) AS t1
- FULL OUTER JOIN EACH
- (
- SELECT
- *
- FROM
- (
- SELECT
- SOURCE,
- medium,
- campaign,
- 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,
- 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(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,
- 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
- )
- )
- ) AS t2
- ON
- t1.SOURCE = t2.SOURCE AND
- t1.medium = t2.medium AND
- t1.campaign = t2.campaign
- )
- )
- ORDER BY sessions_count DESC
- LIMIT 2500
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement