SHOW:
|
|
- or go back to the newest paste.
| 1 | #standardSQL | |
| 2 | --ROI, ROAS и ДРР по источникам и каналам за последние 30 дней | |
| 3 | SELECT | |
| 4 | marketing_shareOfAdCosts,-- as ДРР | |
| 5 | marketing_roas,-- as ROAS | |
| 6 | marketing_roi,-- as ROI | |
| 7 | trafficSource_source,-- as источник трафика | |
| 8 | trafficSource_medium-- as канал | |
| 9 | FROM | |
| 10 | ( | |
| 11 | SELECT | |
| 12 | SAFE_DIVIDE(_1488499584, _1535043462) as marketing_shareOfAdCosts, | |
| 13 | SAFE_DIVIDE(_1535043462, _1488499584) as marketing_roas, | |
| 14 | SAFE_DIVIDE(_627893217, _1488499584) as marketing_roi, | |
| 15 | trafficSource_source as trafficSource_source, | |
| 16 | trafficSource_medium as trafficSource_medium | |
| 17 | FROM | |
| 18 | ( | |
| 19 | SELECT | |
| 20 | SUM(sessionStreaming.trafficSource.attributedAdCost) as _1488499584, | |
| 21 | (SUM(IF(_hits.eCommerceAction.action_type = 'refund' AND _hits.transaction.transactionRevenue > 0, -_hits.transaction.transactionRevenue, _hits.transaction.transactionRevenue)) - SUM(sessionStreaming.trafficSource.attributedAdCost)) as _627893217, | |
| 22 | SUM(IF(_hits.eCommerceAction.action_type = 'refund' AND _hits.transaction.transactionRevenue > 0, -_hits.transaction.transactionRevenue, _hits.transaction.transactionRevenue)) as _1535043462, | |
| 23 | sessionStreaming.trafficSource.source as trafficSource_source, | |
| 24 | sessionStreaming.trafficSource.medium as trafficSource_medium | |
| 25 | FROM | |
| 26 | `owox-140513.OWOXBI_Streaming.session_streaming_*` as sessionStreaming | |
| 27 | , | |
| 28 | UNNEST(hits) as _hits | |
| 29 | 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))
| |
| 30 | GROUP BY trafficSource_source, trafficSource_medium | |
| 31 | ) | |
| 32 | ) | |
| 33 | ORDER BY marketing_roi DESC | |
| 34 | LIMIT 2500 |
