This repository has been archived by the owner on Nov 1, 2023. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 1
/
analytics.sql
167 lines (143 loc) · 5.83 KB
/
analytics.sql
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
163
164
165
166
-- Número de usuárias únicas
SELECT
count(distinct c.handle_hashed) as "Número de conversas"
FROM analytics
join conversa c on c.id = analytics.conversa_id
join tag_code on tag_code.id = analytics.tag_code
where 1=1
--[[AND {{first_msg}}]]
--[[AND {{tag_code}}]]
-- Número de usuárias únicas que iniciaram conversas
SELECT
count(distinct c.handle_hashed) as "Número de conversas"
FROM analytics
join conversa c on c.id = analytics.conversa_id
join tag_code on tag_code.id = analytics.tag_code
where 1=1
and analytics.state != 'DURING_DECISION_TREE'
--[[AND {{first_msg}}]]
--[[AND {{tag_code}}]]
-- Número de usuários únicos com conversas finalizadas
SELECT
count(distinct c.handle_hashed) as "Número de conversas"
FROM analytics
join conversa c on c.id = analytics.conversa_id
join tag_code on tag_code.id = analytics.tag_code
where 1=1
and analytics.state IN( 'QUESTIONNAIRE_FINISHED', 'QUESTIONNAIRE_GAVE_UP')
--[[AND {{first_msg}}]]
--[[AND {{tag_code}}]]
-- Número de usuários únicos com conversas interrompidas
SELECT
count(distinct c.handle_hashed) as "Número de conversas"
FROM analytics
join conversa c on c.id = analytics.conversa_id
join tag_code on tag_code.id = analytics.tag_code
where 1=1
and analytics.state IN( 'QUESTIONNAIRE_TIMEOUT')
--[[AND {{first_msg}}]]
--[[AND {{tag_code}}]]
-- Tempo médio da conversa por tipo de finalização
SELECT
case when state = 'QUESTIONNAIRE_FINISHED' then 'Final do fluxo' when state='QUESTIONNAIRE_GAVE_UP' then 'Botão "Sair" ou "Reiniciar"' when state='QUESTIONNAIRE_TIMEOUT' then 'Timeout' else state::text end as "Tipo",
percentile_cont(0.5) WITHIN GROUP (ORDER BY (extract('epoch' from created_at - first_msg_tz) / 60)::int ) as "Mediana da resposta (em minutos)",
mode() WITHIN GROUP (ORDER BY (extract('epoch' from created_at - first_msg_tz) / 60)::int ) as "Tempo mais comum (em minutos)"
FROM analytics
join tag_code on tag_code.id = analytics.tag_code
where 1=1
and analytics.state in( 'QUESTIONNAIRE_FINISHED', 'QUESTIONNAIRE_GAVE_UP', 'QUESTIONNAIRE_TIMEOUT')
--[[AND {{first_msg}}]]
--[[AND {{tag_code}}]]
group by 1
order by 1
-- Número de conversas
SELECT
count(distinct analytics.conversa_id) as "Número de conversas"
FROM analytics
join tag_code on tag_code.id = analytics.tag_code
where 1=1
--[[AND {{first_msg}}]]
--[[AND {{tag_code}}]]
-- Número de conversas iniciadas
SELECT
count(distinct analytics.conversa_id) as "Número de conversas"
FROM analytics
join tag_code on tag_code.id = analytics.tag_code
where 1=1
and analytics.state != 'DURING_DECISION_TREE'
--[[AND {{first_msg}}]]
--[[AND {{tag_code}}]]
-- Número de conversas finalizadas
SELECT
count(distinct analytics.conversa_id) as "Número de conversas"
FROM analytics
join tag_code on tag_code.id = analytics.tag_code
where 1=1
and analytics.state IN ( 'QUESTIONNAIRE_FINISHED', 'QUESTIONNAIRE_GAVE_UP')
--[[AND {{first_msg}}]]
--[[AND {{tag_code}}]]
-- Número de conversas interrompidas
SELECT
count(distinct analytics.conversa_id) as "Número de conversas"
FROM analytics
join tag_code on tag_code.id = analytics.tag_code
where 1=1
and analytics.state IN ( 'QUESTIONNAIRE_TIMEOUT')
--[[AND {{first_msg}}]]
--[[AND {{tag_code}}]]
-- Locais de saída
SELECT
sc.code as "Local de saida",
count(distinct conversa_id) as "Quantidade de conversas",
percentile_cont(0.5) WITHIN GROUP (ORDER BY (extract('epoch' from analytics.created_at - analytics.first_msg_tz) / 60)::int ) as "Mediana da resposta (em minutos)",
mode() WITHIN GROUP (ORDER BY (extract('epoch' from analytics.created_at - analytics.first_msg_tz) / 60)::int ) as "Tempo mais comum (em minutos)",
(extract('epoch' from min( analytics.created_at - analytics.first_msg_tz)) / 60)::int as "Tempo mínimo (minutos)",
(extract('epoch' from max( analytics.created_at - analytics.first_msg_tz)) / 60)::int as "Tempo máximo (minutos)"
FROM analytics
join step_code sc on sc.id = analytics.step_code_id
join tag_code on tag_code.id = analytics.tag_code
where 1=1
and analytics.state in( 'QUESTIONNAIRE_FINISHED')
[[AND {{first_msg}}]]
[[AND {{tag_code}}]]
group by 1 order by 2 desc;
-- Locais de saída com origem
with by_code as ( SELECT
step_code_id, analytics.previous_step_code_id,
sc.code,
count(1) as count
FROM analytics
join step_code sc on sc.id = analytics.step_code_id
join tag_code on tag_code.id = analytics.tag_code
where 1=1
and analytics.state in( 'QUESTIONNAIRE_FINISHED')
[[AND {{first_msg}}]]
[[AND {{tag_code}}]]
group by 1, 2,3
), by_code_total as (
select code, sum(count) as total
from by_code
group by 1
)
select a.code || ' <- ' || sc.code As "Código <- Origem", a.count as "Finalizações", ((a.count / b.total) * 100 )::int ||'%' as "% em relação ao código"
from by_code a
join by_code_total b on b.code=a.code
join step_code sc on sc.id = a.previous_step_code_id
order by b.total desc, a.code;
-- Locais de timeout
SELECT
sc.code as "Código",
count(1) as "Quantidade de timeout",
percentile_cont(0.5) WITHIN GROUP (ORDER BY (extract('epoch' from analytics.created_at - analytics.first_msg_tz) / 60)::int ) as "Mediana até desistir (em minutos)",
mode() WITHIN GROUP (ORDER BY (extract('epoch' from analytics.created_at - analytics.first_msg_tz) / 60)::int ) as "Tempo mais comum até desistir (em minutos)",
(extract('epoch' from min( analytics.created_at - analytics.first_msg_tz)) / 60)::int as "Tempo mínimo de até desistir (minutos)",
(extract('epoch' from max( analytics.created_at - analytics.first_msg_tz)) / 60)::int as "Tempo máximo de até desistir (minutos)"
FROM analytics
join step_code sc on sc.id = analytics.step_code_id
join tag_code on tag_code.id = analytics.tag_code
where 1=1
and analytics.state in( 'QUESTIONNAIRE_TIMEOUT')
[[AND {{first_msg}}]]
[[AND {{tag_code}}]]
group by 1
order by 2 desc