Advertisement
kirzecy670

Untitled

Jul 9th, 2025
86
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --// ПУШИ
  2.  
  3. with
  4. visitParamExtractString(addJson, 'id') AS event_id_,
  5.  
  6. exp_users as
  7.   (select uid,
  8.           if(MAX(visitParamExtractString(addJson, 'option') = 'new'), 'New', 'Old') as exp_group
  9.    from stat.funnelTrack
  10.    where dt >= '2024-08-16'
  11.      and `action` = 'pmr_overlay_push'
  12.      and region in ('ru', 'global')
  13.      and appVersion >= 2007731
  14.    group by 1),
  15.    
  16. groups as
  17.   (select exp_group,
  18.           uniqExact(uid) as all
  19.    from exp_users eu
  20.    group by 1),
  21.    
  22. pushes_sent_new as
  23.   (select es.exp_group,
  24.           count(user_uid) as users
  25.    from analytics.pushes_sent ps
  26.    join exp_users es on ps.user_uid = es.uid
  27.    where sub_type = 'PF'
  28.      and dt >= '2024-08-16'
  29.      and es.exp_group = 'New'
  30.    group by 1),
  31.    
  32. pushes_sent_old as
  33.   (select es.exp_group,
  34.           count(uid) as users
  35.    from stat.funnelTrack ft
  36.    join exp_users es on ft.user_uid = es.uid
  37.    where action = 'custom_push'
  38.      and visitParamExtractRaw(addJson, 'type') = '1224'
  39.      and dt >= '2024-08-16'
  40.      and exp_group = 'Old'),
  41.  
  42. pushes_sent_old_info as
  43.   (select event_id_ as event_id,
  44.           uid
  45.    from stat.funnelTrack
  46.    join exp_users using(uid)
  47.    where action = 'custom_push'
  48.      and visitParamExtractRaw(addJson, 'type') = '1224'
  49.      and dt >= '2024-08-16'
  50.      and exp_group = 'Old'),
  51.  
  52. pushes_open_old AS (
  53.     SELECT
  54.         eg.exp_group as exp_group,
  55.         uniqExact(ft.uid) as uid
  56.     FROM
  57.         stat.funnelTrack ft
  58.     JOIN
  59.         exp_users eg ON ft.uid = eg.uid
  60.     JOIN
  61.         pushes_sent_old_info pi ON pi.uid = ft.uid AND pi.event_id = visitParamExtractString(addJson, 'event')
  62.     WHERE
  63.         action = 'push_open'
  64.         AND dt >= '2024-08-16'
  65.         and exp_group = 'Old'
  66.         GROUP BY exp_group
  67. )
  68.  
  69. pushes_open_new as
  70.   (select exp_group,
  71.           count(if(action='overlay_btn_target', uid, null)) as overlay_btn_target
  72.    from stat.funnelTrack
  73.    join exp_users using(uid)
  74.    where dt >= '2024-08-16'
  75.      and action = 'overlay_btn_target'
  76.      and region in ('ru', 'global')
  77.      and exp_group = 'New'
  78.    group by 1),
  79.  
  80. select gs.exp_group as exp_group,
  81.        gs.all as all,
  82.        ps.users+su.users as sent,
  83.        sent/all as sent_cr,
  84.        CASE WHEN exp_group = 'New' THEN uae.overlay ELSE 0 END as overlay_show,
  85.        overlay_show/sent as overlay_to_sent_cr,
  86.       CASE WHEN exp_group = 'New' THEN overlay_btn_target WHEN exp_group = 'Old' THEN opo.uid END as btn_target,
  87.       CASE WHEN exp_group = 'New' THEN btn_target/overlay_show WHEN exp_group = 'Old' THEN btn_target/sent END as open_to_target_cr,
  88.       CASE WHEN exp_group = 'New' THEN overlay_btn_close ELSE 0 END as btn_close,    
  89.       CASE WHEN exp_group = 'New' THEN btn_close/overlay_show ELSE 0 END as open_to_close_cr  
  90. from groups gs
  91. left join pushes_sent_new ps on gs.exp_group = ps.exp_group
  92. left join pushes_sent_old su on gs.exp_group = su.e_group
  93. left join pushes_open_old uae on gs.exp_group = uae.exp_group
  94. left join pushes_open_new opo on gs.exp_group = opo.exp_group
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement