( websiteId: string, filters: QueryFilters, )
| 38 | } |
| 39 | |
| 40 | async function relationalQuery( |
| 41 | websiteId: string, |
| 42 | filters: QueryFilters, |
| 43 | ): Promise<ChannelExpandedMetricsData[]> { |
| 44 | const { rawQuery, parseFilters, getTimestampDiffSQL } = prisma; |
| 45 | const { queryParams, filterQuery, joinSessionQuery, cohortQuery, excludeBounceQuery, dateQuery } = |
| 46 | parseFilters({ |
| 47 | ...filters, |
| 48 | websiteId, |
| 49 | }); |
| 50 | |
| 51 | return rawQuery( |
| 52 | ` |
| 53 | WITH prefix AS ( |
| 54 | select case when website_event.utm_medium LIKE 'p%' OR |
| 55 | website_event.utm_medium LIKE '%ppc%' OR |
| 56 | website_event.utm_medium LIKE '%retargeting%' OR |
| 57 | website_event.utm_medium LIKE '%paid%' then 'paid' else 'organic' end prefix, |
| 58 | website_event.referrer_domain, |
| 59 | website_event.url_query, |
| 60 | website_event.utm_medium, |
| 61 | website_event.utm_source, |
| 62 | website_event.session_id, |
| 63 | website_event.visit_id, |
| 64 | website_event.hostname, |
| 65 | website_event.event_id, |
| 66 | website_event.created_at |
| 67 | from website_event |
| 68 | ${cohortQuery} |
| 69 | ${excludeBounceQuery} |
| 70 | ${joinSessionQuery} |
| 71 | where website_event.website_id = {{websiteId::uuid}} |
| 72 | and website_event.event_type NOT IN (2, 5) |
| 73 | ${dateQuery} |
| 74 | ${filterQuery}), |
| 75 | |
| 76 | channels as ( |
| 77 | select case |
| 78 | when referrer_domain = '' and url_query = '' then 'direct' |
| 79 | when ${toPostgresPositionClause('url_query', PAID_AD_PARAMS)} then 'paidAds' |
| 80 | when ${toPostgresPositionClause('utm_medium', ['referral', 'app', 'link'])} then 'referral' |
| 81 | when utm_medium ilike '%affiliate%' then 'affiliate' |
| 82 | when utm_medium ilike '%sms%' or utm_source ilike '%sms%' then 'sms' |
| 83 | when ${toPostgresPositionClause('referrer_domain', LLM_DOMAINS)} then 'llm' |
| 84 | when ${toPostgresPositionClause('referrer_domain', SEARCH_DOMAINS)} or utm_medium ilike '%organic%' then concat(prefix, 'Search') |
| 85 | when ${toPostgresPositionClause('referrer_domain', SOCIAL_DOMAINS)} then concat(prefix, 'Social') |
| 86 | when ${toPostgresPositionClause('referrer_domain', EMAIL_DOMAINS)} or utm_medium ilike '%mail%' then 'email' |
| 87 | when ${toPostgresPositionClause('referrer_domain', SHOPPING_DOMAINS)} or utm_medium ilike '%shop%' then concat(prefix, 'Shopping') |
| 88 | when ${toPostgresPositionClause('referrer_domain', VIDEO_DOMAINS)} or utm_medium ilike '%video%' then concat(prefix, 'Video') |
| 89 | when referrer_domain != regexp_replace(hostname, '^www.', '') and referrer_domain != '' then 'referral' |
| 90 | else '' end as "name", |
| 91 | session_id, |
| 92 | visit_id, |
| 93 | event_id, |
| 94 | created_at |
| 95 | from prefix), |
| 96 | |
| 97 | visit_channels as ( |
no test coverage detected