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