WITH payment_table AS SELECT DATE_TRUNC DATE finish_date week saturday

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
WITH
payment_table AS (
SELECT DATE_TRUNC(DATE(finish_date), week(saturday)) AS week, IF(product IS NULL, "Other", product) AS product, COUNT(DISTINCT reference_number) AS transactions,
SUM(total) AS grossRevenue, SUM(profit) AS netRevenue
FROM (
SELECT * FROM (
SELECT finish_date, reference_number, product_id, total, profit FROM `activate.avantage` WHERE is_test IS NULL AND finish_date BETWEEN TIMESTAMP("2018-09-01") AND TIMESTAMP_ADD(TIMESTAMP("2018-09-28"), INTERVAL 24 HOUR)
UNION ALL
SELECT finish_date, reference_number, product_id, total, profit FROM `activate.a1pay_ru` WHERE is_test IS NULL AND finish_date BETWEEN TIMESTAMP("2018-09-01") AND TIMESTAMP_ADD(TIMESTAMP("2018-09-28"), INTERVAL 24 HOUR)
UNION ALL
SELECT DATE AS finish_date, reference_number, product_id, total, profit FROM `activate.allsoft` WHERE is_test IS NULL AND DATE BETWEEN TIMESTAMP("2018-09-01") AND TIMESTAMP_ADD(TIMESTAMP("2018-09-28"), INTERVAL 24 HOUR)
UNION ALL
SELECT finish_date, reference_number, product_id, total, profit FROM `activate.Sberbank` WHERE is_test IS NULL AND finish_date BETWEEN TIMESTAMP("2018-09-01") AND TIMESTAMP_ADD(TIMESTAMP("2018-09-28"), INTERVAL 24 HOUR)
UNION ALL
SELECT finish_date, reference_number, product_id, total, profit FROM `activate.yandex` WHERE is_test IS NULL AND finish_date BETWEEN TIMESTAMP("2018-09-01") AND TIMESTAMP_ADD(TIMESTAMP("2018-09-28"), INTERVAL 24 HOUR)
) LEFT JOIN (SELECT CAST(id AS STRING) AS pid, productGroup AS product FROM `productGroups.productGroups`) ON product_id = pid
) GROUP BY 1, 2
),
ga_table AS (
SELECT DATE_TRUNC(DATE, week(saturday)) AS week, IF(contentGroup IS NULL, 'Other', contentGroup) AS contentGroup,
COUNT(DISTINCT userId) AS uniqueUsers,
COUNT(DISTINCT IF(isLP=1, userId, NULL)) AS usersOnLandingPages,
COUNT(DISTINCT IF(PageType="Download", userId, NULL)) AS downloaders,
COUNT(DISTINCT IF(PageType="Install", userId, NULL)) AS installers,
COUNT(DISTINCT IF(PageType="Buynow", userId, NULL)) AS buynow,
COUNT(DISTINCT IF(PageType="Buy", userId, NULL)) AS checkout
FROM (
SELECT DATE, page, CASE WHEN contentGroup = "VE Mac Business" THEN "VEM Biz" WHEN contentGroup = "VE Win Business" THEN "VE Biz" WHEN contentGroup = "Slideshow MAC" THEN "Slideshow Mac" ELSE contentGroup END AS
contentGroup, PageType, userId, isLP FROM (
SELECT
PARSE_DATE("%Y%m%d", DATE) AS DATE,
LOWER( REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(hits.page.pagePath, "(www|store).movavi.(com|de|ru)/([a-z]{2}/)?", "/"), r"\?.*=.*|#.*|&.*|!.*|index.html", ""), r"\d*$|(/)?(\d)*-([a-z]{2})$", "") ) AS page,
fullVisitorId AS userId,
IF(hits.hitNumber=1, 1, NULL) AS isLP
FROM `movavi---owox-demo.969794.ga_sessions_*`, UNNEST(hits) AS hits
WHERE hits.page.pagePath IS NOT NULL AND _TABLE_SUFFIX BETWEEN "20180901" AND "20180928"
UNION ALL
SELECT
PARSE_DATE("%Y%m%d", DATE) AS DATE,
LOWER( REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(hits.page.pagePath, "(www|store).movavi.(com|de|ru)/([a-z]{2}/)?", "/"), r"\?.*=.*|#.*|&.*|!.*|index.html", ""), r"\d*$|(/)?(\d)*-([a-z]{2})$", "") ) AS page,
fullVisitorId AS userId,
IF(hits.hitNumber=1, 1, NULL) AS isLP
FROM `movavi---owox-demo.8254485.ga_sessions_*`, UNNEST(hits) AS hits
WHERE hits.page.pagePath IS NOT NULL AND _TABLE_SUFFIX BETWEEN "20180901" AND "20180928"
) LEFT JOIN (
SELECT LOWER(page) AS pageURI, PageType, contentGroup FROM `productGroups.contentGroups`
GROUP BY 1,2,3
) ON page = PageURI
) GROUP BY 1, 2
),
costs_table AS (
SELECT DATE_TRUNC(DATE, week(saturday)) AS week, costsProduct, SUM(costs) AS costs FROM (
SELECT DATE,
CASE
WHEN product = "Game Capture" THEN "GC"
WHEN product IN ("Video Editor", "Video Editor Plus") THEN "VE"
WHEN product IN ("Video Editor for Mac", "Video Editor Mac Plus") THEN "VEM"
WHEN product = "Slideshow Creator" THEN "Slideshow"
WHEN product = "Video Suite" THEN "VS"
WHEN product = "PDF Editor" THEN "PDF"
WHEN product = "PDF Editor for Mac" THEN "PDFM"
WHEN product = "Video Converter" THEN "VC"
WHEN product IN ("Screen Recorder for Mac", "Screen Capture Studio for Mac") THEN "SCM"
WHEN product = "Video Editor Business" THEN "VE Biz"
WHEN product IN ("Screen Capture Studio", "Screen Recorder") THEN "SC"
WHEN product = "Screen Capture for Mac" THEN "SCM Pro"
WHEN product = "Screen Capture" THEN "SC Pro"
WHEN product = "Photo Editor" THEN "PE"
WHEN product = "Photo Editor Mac" THEN "PEM"
WHEN product = "Video Converter for Mac" THEN "VCM"
WHEN product = "Video Editor Business for Mac" THEN "VEM Biz"
WHEN product = "Slideshow Creator for Mac" THEN "Slideshow Mac"
ELSE "Other"
END AS costsProduct, SUM(adCostUSD) AS costs
FROM `movavi---owox-demo.stat_expenses.adsByCountry_*`
WHERE _TABLE_SUFFIX BETWEEN "20180901" AND "20180928"
GROUP BY DATE, costsProduct
UNION ALL
SELECT PARSE_DATE("%Y-%m-%d", DATE) AS DATE, product AS costsProduct, SUM(cost) AS costs
FROM `movavi---owox-demo.stat_expenses.pr_expenses`
WHERE DATE BETWEEN "2018-09-01" AND "2018-09-28"
GROUP BY DATE, costsProduct
-- UNION ALL
-- SELECT PARSE_DATE("%Y-%m-%d", date) as date, SUM(cost) as costs,
-- CASE
-- WHEN product = "Video Editor" THEN "VE"
-- WHEN product = "Video Converter" THEN "VC"
-- WHEN product = "Video Suite" THEN "VS"
-- WHEN product = "Screen Recorder" THEN "SR"
-- WHEN product = "Photo Editor" THEN "PE"
-- WHEN product = "Slideshow Creator" THEN "Slideshow"
-- WHEN product = "Slideshow Creator Mac" THEN "Slideshow Mac"
-- WHEN product = "Video Editor Mac" THEN "VEM"
-- WHEN product = "Screen Capture Studio" THEN "SCS"
-- WHEN product = "Screen Capture Studio Mac" THEN "SCSM"
-- WHEN product = "Photo Editor Mac" THEN "PEM"
-- WHEN product = "Video Converter Mac" THEN "VCM"
-- WHEN product = "Screen Capture" THEN "SC"
-- WHEN product = "Screen Recorder Mac" THEN "SRM"
-- WHEN product = "Game Capture" THEN "GC"
-- WHEN product = "PDF Editor" THEN "PDF"
-- WHEN product = "PDF Editor Mac" THEN "PDFM"
-- WHEN product = "Screen Capture Pro Mac" THEN "SC Pro"
-- WHEN product = "Screen Capture Mac" THEN "SCM Pro"
-- ELSE "Other"
-- END AS costsProduct
-- FROM `stat_expenses.seo_expenses`
-- WHERE date BETWEEN "2018-09-01" AND "2018-09-28"
-- GROUP BY date, costsProduct
) GROUP BY week, costsProduct
)
SELECT
pt.week,
pt.product,
gt.usersOnLandingPages,
gt.uniqueUsers,
gt.downloaders,
gt.installers,
gt.buynow,
gt.checkout,
pt.transactions,
pt.grossRevenue,
pt.netRevenue,
ct.costs
FROM payment_table pt
LEFT JOIN ga_table gt
ON pt.week = gt.week AND pt.product = gt.contentGroup
LEFT JOIN costs_table ct
ON pt.product = ct.costsProduct AND ct.week = gt.week