Untitled

kirzecy670

Jun 10th, 2025

634

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

MySQL 2.95 KB | None | 0 0

raw download clone embed print report

WITH article_views AS (
SELECT
uid,
MIN(dt) AS first_view_date,
visitParamExtractString(addJson, 'option') AS experiment_group
FROM stat.funnelTrack
WHERE dt >= '2024-12-01'
AND action = 'pmr_all_articles_screen'
[[AND visitParamExtractString(addJson, 'al') = {{locale}}]]
AND experiment_group in ('new', 'old')
GROUP BY uid, experiment_group
),
user_cohorts AS (
SELECT
av.experiment_group as experiment_group,
av.uid as uid,
av.first_view_date as first_view_date,
pp.full_reg_datetime as full_reg_datetime,
CASE
WHEN dateDiff('day', pp.full_reg_datetime, av.first_view_date) < 14 THEN 'A. < 2 weeks'
WHEN dateDiff('day', pp.full_reg_datetime, av.first_view_date) BETWEEN 14 AND 29 THEN 'B. 2-4 weeks'
WHEN dateDiff('day', pp.full_reg_datetime, av.first_view_date) BETWEEN 30 AND 119 THEN 'C. 1-4 months'
WHEN dateDiff('day', pp.full_reg_datetime, av.first_view_date) BETWEEN 120 AND 364 THEN 'D. 4-12 months'
WHEN dateDiff('day', pp.full_reg_datetime, av.first_view_date) BETWEEN 365 AND 729 THEN 'E. 1-2 years'
ELSE 'F. > 2 years'
END AS registration_age_group
FROM article_views av
JOIN analytics.parent_properties pp USING (uid)
WHERE pp.application_language IN ('ru', 'en', 'en-US', 'en-GB', 'de', 'esp', 'pt-BR', 'tr')
AND pp.full_reg_datetime BETWEEN '2024-12-01' AND '2025-05-01'
AND abs(dateDiff('day', pp.full_reg_datetime, av.first_view_date)) <= 1
[[AND registration_age_group = {{weeks_group}}]]
)
-- SELECT * FROM user_cohorts;
SELECT
experiment_group,
action,
COUNT() AS event_count,
uniqExact(uid) AS unique_users,
SUM(unique_users) over (partition by action) as _total_users,
MAX(unique_users) over (partition by experiment_group) as _group_total_users,
unique_users/_group_total_users as conversion_rate
FROM stat.funnelTrack
JOIN user_cohorts USING (uid)
WHERE dt >= '2024-12-01'
AND dt <= full_reg_datetime + interval '7' DAY
AND action IN (
'feed_opened_full',
'block_viewed',
'articleCarousel_link_click',
'article_click',
'articles_block_clicked',
'article_saved',
'article_subscribe_open',
'article_subscribe_click',
'search_tool_click'
)
AND (
(action = 'block_viewed' AND visitParamExtractString(addJson, 'block_name') = 'articleCarousel')
OR action = 'articleCarousel_link_click'
OR action = 'feed_opened_full'
OR (action = 'article_click' AND visitParamExtractString(addJson, 'ar') = 'all_articles')
OR action = 'article_saved'
OR action = 'article_subscribe_open'
OR action = 'article_subscribe_click'
OR action = 'search_tool_click'
OR action = 'articles_block_clicked'
)
GROUP BY action, experiment_group
ORDER BY _total_users DESC, action, experiment_group

Add Comment

Please, Sign In to add comment