WITH payment_table AS SELECT DATE_TRUNC DATE finish_date week saturday

  • Anonymous
  • SQL
  • 26 Feb 2019 
  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
← newer dump
older dump →