· 4 years ago · Jul 08, 2021, 09:44 AM
1 CASE
2 WHEN LOWER(utm_source) = "yandex" AND LOWER(utm_medium) = "organic" THEN "Яндекс Органика"
3 WHEN LOWER(utm_source) = "yandex" AND LOWER(utm_medium) = "cpc" AND type = "seti" AND is_event = 0 THEN "Яндекс Сеть"
4 WHEN LOWER(utm_source) = "yandex" AND LOWER(utm_medium) = "cpc" AND type = "poisk" AND is_event = 0 THEN "Яндекс Поиск"
5 WHEN LOWER(utm_source) = "yandex" AND LOWER(utm_medium) = "cpc" AND type = "video" AND is_event = 0 THEN "Яндекс Видео Клики"
6 WHEN LOWER(utm_source) = "yandex" AND LOWER(utm_medium) = "cpm" AND type = "video" AND is_event = 0 THEN "Яндекс Видео Показы"
7 WHEN LOWER(utm_source) = "yandex" AND is_event = 0 THEN "Яндекс Остальное"
8 WHEN LOWER(utm_source) = "yandex" AND type = "seti" AND is_event = 1 THEN "Яндекс Сеть w"
9 WHEN LOWER(utm_source) = "yandex" AND type = "poisk" AND is_event = 1 THEN "Яндекс Поиск w"
10 WHEN LOWER(utm_source) = "yandex" AND type = "video" AND is_event = 1 THEN "Яндекс Видео w"
11 WHEN LOWER(utm_source) = "yandex" AND is_event = 1 THEN "Яндекс Остальное w"
12 WHEN LOWER(utm_source) = "google" AND LOWER(utm_medium) = "organic" THEN "Google Органика"
13 WHEN LOWER(utm_source) = "google" AND LOWER(utm_medium) = "cpc" AND type = "seti" AND is_event = 0 THEN "Google Сети"
14 WHEN LOWER(utm_source) = "google" AND LOWER(utm_medium) = "cpc" AND type = "performance-max" AND is_event = 0 THEN "Google Performance Max"
15 WHEN LOWER(utm_source) = "google" AND LOWER(utm_medium) = "cpc" AND type = "poisk" AND is_event = 0 THEN "Google Поиск"
16 WHEN LOWER(utm_source) = "google" AND LOWER(utm_medium) = "cpc" AND type = "video" AND is_event = 0 THEN "Google Видео"
17 WHEN LOWER(utm_source) = "google" AND is_event = 0 THEN "Google Остальное"
18 WHEN LOWER(utm_source) = "google" AND type = "seti" AND is_event = 1 THEN "Google Сети w"
19 WHEN LOWER(utm_source) = "google" AND type = "poisk" AND is_event = 1 THEN "Google Поиск w"
20 WHEN LOWER(utm_source) = "google" AND type = "video" AND is_event = 1 THEN "Google Видео w"
21 WHEN LOWER(utm_source) = "google" AND is_event = 1 THEN "Google Остальное w"
22 WHEN LOWER(utm_source) = "instagram" AND LOWER(utm_medium) = "blogger" AND type = "video" AND is_event = 0 THEN "Insta Блоггеры Видео"
23 WHEN LOWER(utm_source) = "instagram" AND LOWER(utm_medium) = "blogger" AND type = "stories" AND is_event = 0 THEN "Insta Блоггеры Сторис"
24 WHEN LOWER(utm_source) = "instagram" AND type = "saleset" AND is_event = 0 THEN "Insta Saleset"
25 WHEN LOWER(utm_source) = "instagram" AND LOWER(utm_medium) <> "social" AND is_event = 0 THEN "Insta Таргет"
26 WHEN LOWER(utm_source) = "instagram" AND LOWER(utm_medium) = "blogger" AND is_event = 1 THEN "Insta Блоггеры w"
27 WHEN LOWER(utm_source) = "instagram" AND LOWER(utm_medium) <> "social" AND is_event = 1 THEN "Insta Таргет w"
28 WHEN LOWER(utm_source) = "facebook" AND LOWER(utm_medium) <> "social" AND type = "leads" AND is_event = 0 THEN "Facebook Лидс"
29 WHEN LOWER(utm_source) = "facebook" AND LOWER(utm_medium) <> "social" AND is_event = 0 THEN "Facebook Таргет"
30 WHEN LOWER(utm_source) = "facebook" AND LOWER(utm_medium) <> "social" AND type = "leads" AND is_event = 1 THEN "Facebook Лидс w"
31 WHEN LOWER(utm_source) = "facebook" AND LOWER(utm_medium) <> "social" AND is_event = 1 THEN "Facebook Таргет w"
32 WHEN LOWER(utm_source) = "vk" AND LOWER(utm_medium) = "groups" AND type = "post" AND is_event = 0 THEN "VK Посевы"
33 WHEN LOWER(utm_source) = "vk" AND type = "leads" AND is_event = 0 THEN "VK Лидс"
34 WHEN LOWER(utm_source) = "vk" AND LOWER(utm_medium) IN ("cpc", "cpv", "cpm") AND is_event = 0 THEN "VK Таргет"
35 WHEN LOWER(utm_source) = "vk" AND LOWER(utm_medium) NOT IN ("social", "messages", "referral", "community-widget") AND is_event = 0 THEN "VK Остальное"
36 WHEN LOWER(utm_source) = "vk" AND LOWER(utm_medium) = "groups" AND is_event = 1 then "VK Посевы w"
37 WHEN LOWER(utm_source) = "vk" AND type = "leads" AND is_event = 1 THEN "VK Лидс w"
38 WHEN LOWER(utm_source) = "vk" AND LOWER(utm_medium) IN ("cpc", "cpv", "cpm") AND is_event = 1 THEN "VK Таргет w"
39 WHEN LOWER(utm_source) = "vk" AND LOWER(utm_medium) NOT IN ("social", "messages", "referral", "community-widget") AND is_event = 1 THEN "VK Остальное w"
40 WHEN LOWER(utm_source) = "mytarget" AND LOWER(utm_medium) = "cpc" THEN "MyTarget"
41 WHEN LOWER(utm_source) = "telegram" AND is_event = 0 THEN "Telegram"
42 WHEN LOWER(utm_source) = "telegram" AND is_event = 1 THEN "Telegram w"
43 WHEN LOWER(utm_source) = "ya-zen" AND is_event = 0 THEN "Яндекс Дзен"
44 WHEN LOWER(utm_source) = "ya-zen" AND is_event = 1 THEN "Яндекс Дзен w"
45 WHEN LOWER(utm_source) = "reffection" THEN "Reffection"
46 WHEN LOWER(utm_source) = "youtube" AND LOWER(utm_medium) = "blogger" AND type = "video" AND is_event = 0 THEN "Блогеры Youtube"
47 WHEN LOWER(utm_source) = "youtube" AND LOWER(utm_medium) = "blogger" AND is_event = 1 THEN "Блогеры Youtube w"
48 WHEN LOWER(utm_source) = "tiktok" AND LOWER(utm_medium) = "cpv" AND type = "video" AND is_event = 0 THEN "Тикток Видео"
49 WHEN LOWER(utm_source) = "tiktok" AND LOWER(utm_medium) = "cpc" AND type = "traffic" AND is_event = 0 THEN "Тикток Таргет"
50 WHEN LOWER(utm_source) = "tiktok" AND is_event = 1 THEN "Тикток w"
51 WHEN LOWER(utm_source) = "criteo" AND LOWER(utm_medium) = "cpc" AND type = "seti" AND is_event = 0 THEN "Criteo"
52 WHEN LOWER(utm_source) = "dtf" AND LOWER(utm_medium) = "cpm" THEN "DTF"
53 WHEN LOWER(utm_source) = "tproger" AND LOWER(utm_medium) = "cpc" AND is_event = 0 THEN "Tproger"
54 WHEN LOWER(utm_source) = "tproger" AND is_event = 1 THEN "Tproger w"
55 WHEN LOWER(utm_source) = "twitter" AND LOWER(utm_medium) = "groups" AND type = "post" THEN "Твиттер Группы"
56 WHEN LOWER(utm_source) = "twitter" AND LOWER(utm_medium) = "cpc" AND type = "traffic" THEN "Твиттер Таргет"
57 WHEN LOWER(utm_source) = "pulse-mail" AND LOWER(utm_medium) = "cpv" AND type = "articles" AND is_event = 0 THEN "Pulse Mail"
58 WHEN LOWER(utm_source) = "pulse-mail" AND is_event = 1 THEN "Pulse Mail w"
59 WHEN LOWER(utm_source) = "superjob" AND LOWER(utm_medium) = "cpс" AND is_event = 0 THEN "SuperJob"
60 WHEN LOWER(utm_source) = "superjob" AND is_event = 1 THEN "SuperJob w"
61 WHEN LOWER(utm_source) = "headhunter" AND LOWER(utm_medium) = "cpс" AND is_event = 0 THEN "HeadHunter"
62 WHEN LOWER(utm_source) = "headhunter" AND LOWER(utm_medium) = "cpс" AND is_event = 1 THEN "HeadHunter w"
63 WHEN LOWER(utm_source) = "relap" AND LOWER(utm_medium) = "cpc" AND type = "articles" THEN "Relap Статьи"
64 WHEN LOWER(utm_source) = "relap" AND LOWER(utm_medium) = "cpm" AND type = "video" THEN "Relap Видео"
65 WHEN LOWER(utm_source) = "mediatooday" AND LOWER(utm_medium) = "cpv" AND type = "video" THEN "Mediatooday"
66 WHEN LOWER(utm_source) = "twitch" AND LOWER(utm_medium) = "cpv" THEN "Twitch"
67 WHEN LOWER(utm_source) = "cybersport" AND LOWER(utm_medium) = "cpv" AND type = "articles" THEN "Cybersport"
68 WHEN LOWER(utm_source) = "pikabu" AND LOWER(utm_medium) = "cpv" AND type = "articles" THEN "Pikabu"
69 WHEN utm_source = "n+1" AND LOWER(utm_medium) = "cpv" AND type = "articles" THEN "N+1"
70 WHEN LOWER(utm_source) = "weproject" AND LOWER(utm_medium) = "social" AND type = "articles" THEN "Weproject"
71 WHEN LOWER(utm_source) = "linkedin" AND LOWER(utm_medium) = "cpc" AND type = "post" THEN "Linkedin Post"
72 WHEN LOWER(utm_source) = "linkedin" AND LOWER(utm_medium) = "cps" AND type = "inmail" THEN "Linkedin Inmail"
73 WHEN LOWER(utm_source) = "devby" AND LOWER(utm_medium) = "banners" AND type = "banners" THEN "Devby"
74 WHEN LOWER(utm_source) = "gnezdo" AND LOWER(utm_medium) = "cpc" AND type = "seti" THEN "Gnezdo"
75 WHEN LOWER(utm_source) = "moevideo" AND LOWER(utm_medium) = "cpm" AND type = "video" THEN "MoeVideo"
76 WHEN LOWER(utm_source) = "tadviser" AND LOWER(utm_medium) = "cpm" AND type = "articles" THEN "Tadviser"
77 WHEN LOWER(utm_source) = "vc.com" THEN "VС com w"
78 WHEN LOWER(utm_source) = "proglib" THEN "Proglib w"
79 WHEN LOWER(utm_source) = "medium.com" THEN "Medium w"
80 WHEN LOWER(utm_source) = "dv360" THEN "Dv360 w"
81 WHEN LOWER(utm_source) = "twitch" THEN "Twitch"
82 WHEN LOWER(utm_source) = "advcake" AND LOWER(utm_medium) = "cpa" AND utm_campaign = "affiliate" THEN "Advcake Парнерка"
83 WHEN LOWER(utm_source) = "advcake" AND LOWER(utm_medium) = "cpa" AND utm_campaign <> "affiliate" THEN "Advcake Сети"
84 WHEN LOWER(utm_source) = "advcake" AND LOWER(utm_medium) = "refferal" THEN "Advcake Реферальный Трафик"
85 WHEN LOWER(utm_source) = "partners" THEN "Весь Партнерский Трафик"
86 WHEN LOWER(utm_source) = "advcake" THEN "Остальной Партнерский Трафик"
87 WHEN LOWER(utm_medium) IN ("cpc", "cpv", "cpm", "cpa") AND is_event = 0 THEN "Платный Трафик Остальное"
88 WHEN LOWER(utm_medium) IN ("cpc", "cpv", "cpm", "cpa") AND is_event = 1 THEN "Платный Трафик Остальное w"
89 WHEN LOWER(utm_medium) = "email" THEN "Email"
90 WHEN LOWER(utm_medium) = "messages" THEN "Messages"
91 WHEN LOWER(utm_medium) = "push" THEN "Push"
92 WHEN LOWER(utm_medium) = "sms" THEN "SMS"
93 WHEN LOWER(utm_medium) = "lms" THEN "LMS"
94 WHEN LOWER(utm_source) = "yandex.ru" AND utm_medium = "referral" THEN "Яндекс Подсказки"
95 WHEN LOWER(utm_medium) = "organic" THEN "Остальное Органика"
96 WHEN LOWER(utm_medium) = "referral" AND (utm_source = "instagram.com" OR
97 utm_source = "org.telegram.messenger" OR
98 utm_source = "vk" OR
99 utm_source = "m.facebook.com" OR
100 utm_source = "l.facebook.com" OR
101 utm_source = "facebook.com" OR
102 utm_source = "m.vk.com" OR
103 utm_source = "instagram" OR
104 utm_source = "l.instagram.com" OR
105 utm_source = "twitter.com" OR
106 utm_source = "lm.facebook.com" OR
107 utm_source = "vk.com" OR
108 utm_source = "facebook" OR
109 utm_source = "mobile.facebook.com" OR
110 utm_source = "mobile.ok.com") THEN "Социальные сети"
111 WHEN LOWER(utm_source) = "vk" AND utm_medium = "community-widget" THEN "Социальные сети"
112 WHEN LOWER(utm_medium) = "social" THEN "Социальные сети"
113 WHEN LOWER(utm_medium) = "referral" THEN "Переходы с сайтов"
114 WHEN LOWER(utm_medium) = "(none)" THEN "Прямой трафик"
115 WHEN LOWER(utm_source) = "conference" THEN "Конференции"
116 WHEN LOWER(utm_source) = "pr" THEN "PR"
117 WHEN LOWER(utm_source) = "offline" AND LOWER(utm_medium) = "retail" THEN "Оффлайн проект (ритейл)"
118 WHEN LOWER(utm_source) = "offline" AND LOWER(utm_medium) = "proforientation" THEN "Профориентация"
119 ELSE "Остальное"
120 END AS traffic_sourse,