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
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
|
import clickhouse from '@/lib/clickhouse';
import {
EMAIL_DOMAINS,
PAID_AD_PARAMS,
SEARCH_DOMAINS,
SHOPPING_DOMAINS,
SOCIAL_DOMAINS,
VIDEO_DOMAINS,
} from '@/lib/constants';
import { CLICKHOUSE, PRISMA, runQuery } from '@/lib/db';
import prisma from '@/lib/prisma';
import type { QueryFilters } from '@/lib/types';
const FUNCTION_NAME = 'getChannelExpandedMetrics';
export interface ChannelExpandedMetricsParameters {
limit?: number | string;
offset?: number | string;
}
export interface ChannelExpandedMetricsData {
name: string;
pageviews: number;
visitors: number;
visits: number;
bounces: number;
totaltime: number;
}
export async function getChannelExpandedMetrics(
...args: [websiteId: string, filters?: QueryFilters]
): Promise<ChannelExpandedMetricsData[]> {
return runQuery({
[PRISMA]: () => relationalQuery(...args),
[CLICKHOUSE]: () => clickhouseQuery(...args),
});
}
async function relationalQuery(
websiteId: string,
filters: QueryFilters,
): Promise<ChannelExpandedMetricsData[]> {
const { rawQuery, parseFilters, getTimestampDiffSQL } = prisma;
const { queryParams, filterQuery, joinSessionQuery, cohortQuery, dateQuery } = parseFilters({
...filters,
websiteId,
});
return rawQuery(
`
WITH prefix AS (
select case when website_event.utm_medium LIKE 'p%' OR
website_event.utm_medium LIKE '%ppc%' OR
website_event.utm_medium LIKE '%retargeting%' OR
website_event.utm_medium LIKE '%paid%' then 'paid' else 'organic' end prefix,
website_event.referrer_domain,
website_event.url_query,
website_event.utm_medium,
website_event.utm_source,
website_event.session_id,
website_event.visit_id,
count(*) c,
min(website_event.created_at) min_time,
max(website_event.created_at) max_time
from website_event
${cohortQuery}
${joinSessionQuery}
where website_event.website_id = {{websiteId::uuid}}
and website_event.event_type != 2
${dateQuery}
${filterQuery}
group by prefix,
website_event.referrer_domain,
website_event.url_query,
website_event.utm_medium,
website_event.utm_source,
website_event.session_id,
website_event.visit_id),
channels as (
select case
when referrer_domain = '' and url_query = '' then 'direct'
when ${toPostgresPositionClause('url_query', PAID_AD_PARAMS)} then 'paidAds'
when ${toPostgresPositionClause('utm_medium', ['referral', 'app', 'link'])} then 'referral'
when utm_medium ilike '%affiliate%' then 'affiliate'
when utm_medium ilike '%sms%' or utm_source ilike '%sms%' then 'sms'
when ${toPostgresPositionClause('referrer_domain', SEARCH_DOMAINS)} or utm_medium ilike '%organic%' then concat(prefix, 'Search')
when ${toPostgresPositionClause('referrer_domain', SOCIAL_DOMAINS)} then concat(prefix, 'Social')
when ${toPostgresPositionClause('referrer_domain', EMAIL_DOMAINS)} or utm_medium ilike '%mail%' then 'email'
when ${toPostgresPositionClause('referrer_domain', SHOPPING_DOMAINS)} or utm_medium ilike '%shop%' then concat(prefix, 'Shopping')
when ${toPostgresPositionClause('referrer_domain', VIDEO_DOMAINS)} or utm_medium ilike '%video%' then concat(prefix, 'Video')
else '' end AS name,
session_id,
visit_id,
c,
min_time,
max_time
from prefix)
select
name,
sum(c) as "pageviews",
count(distinct session_id) as "visitors",
count(distinct visit_id) as "visits",
sum(case when c = 1 then 1 else 0 end) as "bounces",
sum(${getTimestampDiffSQL('min_time', 'max_time')}) as "totaltime"
from channels
where name != ''
group by name
order by visitors desc, visits desc
`,
queryParams,
FUNCTION_NAME,
).then(results => results.map(item => ({ ...item, y: Number(item.y) })));
}
async function clickhouseQuery(
websiteId: string,
filters: QueryFilters,
): Promise<ChannelExpandedMetricsData[]> {
const { rawQuery, parseFilters } = clickhouse;
const { queryParams, filterQuery, cohortQuery } = parseFilters({
...filters,
websiteId,
});
return rawQuery(
`
select
name,
sum(t.c) as "pageviews",
uniq(t.session_id) as "visitors",
uniq(t.visit_id) as "visits",
sum(if(t.c = 1, 1, 0)) as "bounces",
sum(max_time-min_time) as "totaltime"
from (
select case when multiSearchAny(utm_medium, ['cp', 'ppc', 'retargeting', 'paid']) != 0 then 'paid' else 'organic' end prefix,
case
when referrer_domain = '' and url_query = '' then 'direct'
when multiSearchAny(url_query, [${toClickHouseStringArray(
PAID_AD_PARAMS,
)}]) != 0 then 'paidAds'
when multiSearchAny(utm_medium, ['referral', 'app','link']) != 0 then 'referral'
when position(utm_medium, 'affiliate') > 0 then 'affiliate'
when position(utm_medium, 'sms') > 0 or position(utm_source, 'sms') > 0 then 'sms'
when multiSearchAny(referrer_domain, [${toClickHouseStringArray(
SEARCH_DOMAINS,
)}]) != 0 or position(utm_medium, 'organic') > 0 then concat(prefix, 'Search')
when multiSearchAny(referrer_domain, [${toClickHouseStringArray(
SOCIAL_DOMAINS,
)}]) != 0 then concat(prefix, 'Social')
when multiSearchAny(referrer_domain, [${toClickHouseStringArray(
EMAIL_DOMAINS,
)}]) != 0 or position(utm_medium, 'mail') > 0 then 'email'
when multiSearchAny(referrer_domain, [${toClickHouseStringArray(
SHOPPING_DOMAINS,
)}]) != 0 or position(utm_medium, 'shop') > 0 then concat(prefix, 'Shopping')
when multiSearchAny(referrer_domain, [${toClickHouseStringArray(
VIDEO_DOMAINS,
)}]) != 0 or position(utm_medium, 'video') > 0 then concat(prefix, 'Video')
else '' end AS name,
session_id,
visit_id,
count(*) c,
min(created_at) min_time,
max(created_at) max_time
from website_event
${cohortQuery}
where website_id = {websiteId:UUID}
and created_at between {startDate:DateTime64} and {endDate:DateTime64}
and event_type != 2
and name != ''
${filterQuery}
group by prefix, name, session_id, visit_id
) as t
group by name
order by visitors desc, visits desc;
`,
queryParams,
FUNCTION_NAME,
);
}
function toClickHouseStringArray(arr: string[]): string {
return arr.map(p => `'${p.replace(/'/g, "\\'")}'`).join(', ');
}
function toPostgresPositionClause(column: string, arr: string[]) {
return arr.map(val => `${column} ilike '%${val.replace(/'/g, "''")}%'`).join(' OR\n ');
}
|