-
Notifications
You must be signed in to change notification settings - Fork 8
/
funnel.py
89 lines (74 loc) · 2.34 KB
/
funnel.py
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
import sys
def data_select_statement(event_names):
statements = []
for i, event_name in enumerate(event_names):
statements.append(
f'(CASE event.name '
f'WHEN "{event_name}" '
f'THEN event.timestamp_micros END) AS step_{i}_timestamp'
)
return ',\n '.join(statements)
def funnel_select_statement(event_names):
statements = []
for i, event_name in enumerate(event_names):
statements.append(
f'LAST_VALUE(step_{i}_timestamp IGNORE NULLS) OVER'
f'(PARTITION BY session ORDER BY timestamp) AS step_{i}_funnel'
)
return ',\n '.join(statements)
def last_select_statements(event_names):
statements = []
for i, event_name in enumerate(event_names):
statement = f'SELECT\n "{i + 1}_{event_name}" AS step,\n'
statement += f' COUNT(\n'
statement += f' DISTINCT CASE\n'
statement += f' WHEN step_0_funnel IS NOT NULL\n'
for j in range(1, i + 1):
statement += (
f' AND step_{j}_funnel IS NOT NULL '
f'AND step_{j - 1}_funnel < step_{j}_funnel\n'
)
statement += f' THEN step_0_funnel END\n'
statement += f' ) AS count\n FROM funnel'
statements.append(statement)
return '\nUNION ALL '.join(statements)
def build_sql(table_name, event_names):
sql = f'''\
#standardSQL
-- Generated by BigQuery Firebase Funnel Builder
-- https://github.com/StyleShare/bigquery-firebase-funnel-builder
WITH
data AS (
SELECT
user_dim.first_open_timestamp_micros AS session,
event.timestamp_micros AS timestamp,
{data_select_statement(event_names)}
FROM
`{table_name}`,
UNNEST(event_dim) as event,
UNNEST(event.params) as params
),
funnel AS (
SELECT
session,
timestamp,
{funnel_select_statement(event_names)}
FROM data
)
{last_select_statements(event_names)}
ORDER BY step
;'''
return sql
if __name__ == '__main__':
if len(sys.argv) <= 3:
help = '''\
USAGE:
python funnel.py table_name event_name_0 event_name_1 ...
EXAMPLE:
python funnel.py com_myapp_IOS.app_events_* sign_up add_to_cart purchase'''
print(help)
exit()
table_name = sys.argv[1]
event_names = sys.argv[2:]
sql = build_sql(table_name=table_name, event_names=event_names)
print(sql)