ab-test-case-ecommerce/sql/ab_test_query.sql at main · stepjuly-pixel/ab-test-case-ecommerce · 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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
with session_info as (
  select
    ss.date,
    ss.ga_session_id,
    sp.country,
    sp.device,
    sp.continent,
    sp.channel,
    ab.test,
    ab.test_group
  from `DA.ab_test` ab
  join `DA.session` ss
    on ab.ga_session_id = ss.ga_session_id
  join `DA.session_params` sp
    on sp.ga_session_id = ab.ga_session_id
),

session_with_orders as (
  select
    si.date,
    si.country,
    si.device,
    si.continent,
    si.channel,
    si.test,
    si.test_group,
    count(distinct o.ga_session_id) as session_with_orders
  from `DA.order` o
  join session_info as si
    on si.ga_session_id = o.ga_session_id
  group by
    si.date,
    si.country,
    si.device,
    si.continent,
    si.channel,
    si.test,
    si.test_group
),

events as (
  select
    si.date,
    si.country,
    si.device,
    si.continent,
    si.channel,
    si.test,
    si.test_group,
    ep.event_name,
    count(ep.ga_session_id) as event_cnt
  from `DA.event_params` ep
  join session_info si
    on ep.ga_session_id = si.ga_session_id
  group by
    si.date,
    si.country,
    si.device,
    si.continent,
    si.channel,
    si.test,
    si.test_group,
    ep.event_name
),

session as (
  select
    si.date,
    si.country,
    si.device,
    si.continent,
    si.channel,
    si.test,
    si.test_group,
    count(distinct si.ga_session_id) as session_cnt
  from session_info si
  group by
    si.date,
    si.country,
    si.device,
    si.continent,
    si.channel,
    si.test,
    si.test_group
),

account as (
  select
    si.date,
    si.country,
    si.device,
    si.continent,
    si.channel,
    si.test,
    si.test_group,
    count(distinct acs.ga_session_id) as new_account_cnt
  from `DA.account_session` acs
  join session_info si
    on acs.ga_session_id = si.ga_session_id
  group by
    si.date,
    si.country,
    si.device,
    si.continent,
    si.channel,
    si.test,
    si.test_group
)

select
  session_with_orders.date,
  session_with_orders.country,
  session_with_orders.device,
  session_with_orders.continent,
  session_with_orders.channel,
  session_with_orders.test,
  session_with_orders.test_group,
  'session with orders' as event_name,
  session_with_orders.session_with_orders as value
from session_with_orders

union all

select
  events.date,
  events.country,
  events.device,
  events.continent,
  events.channel,
  events.test,
  events.test_group,
  event_name,
  event_cnt as value
from events

union all

select
  session.date,
  session.country,
  session.device,
  session.continent,
  session.channel,
  session.test,
  session.test_group,
  'session' as event_name,
  session_cnt as value
from session

union all

select
  account.date,
  account.country,
  account.device,
  account.continent,
  account.channel,
  account.test,
  account.test_group,
  'new account' as event_name,
  new_account_cnt as value
from account