ROI, ROAS и ДРР по источникам и каналам за последние 30 дней

#standardSQL
--ROI, ROAS и ДРР по источникам и каналам за последние 30 дней
SELECT
marketing_shareOfAdCosts,-- as ДРР
marketing_roas,-- as ROAS
marketing_roi,-- as ROI
trafficSource_source,-- as источник трафика
trafficSource_medium-- as канал
FROM
(
SELECT
SAFE_DIVIDE(_1488499584, _1535043462) AS marketing_shareOfAdCosts,
SAFE_DIVIDE(_1535043462, _1488499584) AS marketing_roas,
SAFE_DIVIDE(_627893217, _1488499584) AS marketing_roi,
trafficSource_source AS trafficSource_source,
trafficSource_medium AS trafficSource_medium
FROM
(
SELECT
SUM(sessionStreaming.trafficSource.attributedAdCost) AS _1488499584,
(SUM(IF(_hits.eCommerceAction.action_type = 'refund' AND _hits.TRANSACTION.transactionRevenue > 0, -_hits.TRANSACTION.transactionRevenue, _hits.TRANSACTION.transactionRevenue)) - SUM(sessionStreaming.trafficSource.attributedAdCost)) AS _627893217,
SUM(IF(_hits.eCommerceAction.action_type = 'refund' AND _hits.TRANSACTION.transactionRevenue > 0, -_hits.TRANSACTION.transactionRevenue, _hits.TRANSACTION.transactionRevenue)) AS _1535043462,
sessionStreaming.trafficSource.SOURCE AS trafficSource_source,
sessionStreaming.trafficSource.medium AS trafficSource_medium
FROM
`owox-140513.OWOXBI_Streaming.session_streaming_*` AS sessionStreaming
,
UNNEST(hits) AS _hits
WHERE _TABLE_SUFFIX BETWEEN FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -30 DAY))) AND FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_ADD(TIMESTAMP_ADD(TIMESTAMP_TRUNC(TIMESTAMP(DATETIME_ADD(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)), INTERVAL -1 DAY)), DAY), INTERVAL 1 DAY), INTERVAL -1 SECOND))
GROUP BY trafficSource_source, trafficSource_medium
)
)
ORDER BY marketing_roi DESC
LIMIT 2500