SQL/sql_ga_consolidation at master · simonbreton/SQL · GitHub

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
140
141
142
#1_spreadsheets_pageviews_consolidation

select ID,sum(Unique_pageviews) as Unique_pageviews from(
SELECT
  REGEXP_EXTRACT(REGEXP_REPLACE(Page_path, r'\?.*', ''), r'/([^/]+)/?$') AS ID,
  SUM(Unique_pageviews) AS Unique_pageviews
FROM
  `data.historical_data.pageviews2017`
Group by ID
UNION ALL
SELECT
  REGEXP_EXTRACT(REGEXP_REPLACE(Page_path, r'\?.*', ''), r'/([^/]+)/?$') AS ID,
  SUM(Unique_pageviews) AS Unique_pageviews
FROM
  `data.historical_data.pageviews2018`
Group by ID
UNION ALL
SELECT
  REGEXP_EXTRACT(REGEXP_REPLACE(Page_path, r'\?.*', ''), r'/([^/]+)/?$') AS ID,
  SUM(Unique_pageviews) AS Unique_pageviews
FROM
  `data.historical_data.pageviews2019`
Group by ID)
group by ID

#2_spreadsheets_events_consolidation

SELECT
  REGEXP_EXTRACT(REGEXP_REPLACE(Event_label, r'\?.*', ''), r'/([^/]+)/?$') AS ID,
  SUM(Unique_events) AS Events
FROM
  `data.historical_data.events`
GROUP BY
  ID

#3_spreadsheets_timeonpage_consolidation

SELECT
  REGEXP_EXTRACT(REGEXP_REPLACE(Page_path, r'\?.*', ''), r'/([^/]+)/?$') AS ID,
  avg( Avg__time_on_page ) AS avgtimeonpage
FROM
  `data.historical_data.timeonpage`
GROUP BY
  ID

#4_spreadsheets_pageviews_events_timeonpage_consolidation
SELECT
  t1.ID,
  Unique_pageviews,
  events,
  avgtimeonpage
FROM
  `data.1_spreadsheets_pageviews_consolidation` AS t1
FULL OUTER JOIN
  `data.2_spreadsheets_events_consolidation` AS t2
ON
  t1.ID = t2.ID
FULL OUTER JOIN
  `data.3_spreadsheets_timeonepage_consolidation` AS t3
ON
  t1.id = t3.ID

#5_stitch_pageviews_events_timeonpage_consolidation
SELECT
  a.id,
  unique_pageviews,
  unique_events,
  avg_timeonpage
FROM (
  SELECT
    ID,
    SUM(pageviews) AS unique_pageviews,
    avg(avgtimeonepage) as avg_timeonpage
  FROM (
    SELECT
      REGEXP_EXTRACT(REGEXP_REPLACE(pagepath, r'\?.*', ''), r'/([^/]+)/?$') AS ID,
      pagepath,
      end_date,
      campaign,
      MAX(uniquepageviews) AS pageviews,
      MAX(avgtimeonpage) AS avgtimeonepage
    FROM
      `data.academicpositions_ga_pageviews.report`
    WHERE
      REGEXP_CONTAINS(pagepath, r'\/ad\/')
    GROUP BY
      end_date,
      pagepath,
      campaign,
      ID)
  GROUP BY
    ID) a
JOIN (
  SELECT
    ID,
    SUM(unique_events) AS unique_events
  FROM (
    SELECT
      REGEXP_EXTRACT(REGEXP_REPLACE(eventlabel, r'\?.*', ''), r'/([^/]+)/?$') AS ID,
      MAX(uniqueevents) AS unique_events,
      end_date,
      campaign,
      eventlabel
    FROM
      `data.academicsposition_ga_event.report`
    WHERE
      REGEXP_CONTAINS(eventlabel, r'\/ad\/')
      and eventcategory = "Application"
    GROUP BY
      end_date,
      campaign,
      eventlabel )
  GROUP BY
    ID) b
ON
  a.id = b.id

#6_spreadsheets_stitch_pageviews_events_timeonpage_consolidation

SELECT
  ID,
  SUM(Unique_pageviews) AS pageviews,
  SUM(events) AS events,
  avg(avgtimeonpage) as avg_timeonpage
FROM (
  SELECT
    ID,
    Unique_pageviews,
    events,
    avgtimeonpage
  FROM
    `data.job_ads.4_spreadsheets_pageviews_events_timeonpage_consolidation`
  UNION ALL
  SELECT
    ID,
    unique_pageviews,
    unique_events,
    avg_timeonpage
  FROM
    `data.job_ads.5_stitch_pageviews_events_timeonpage_consolidation`)
GROUP BY
  ID