Как вывести значение между символами в Excel с помощью регулярных выражений
Получая выгрузки по содержимому utm_меток мы получаем длинный список значений, которые необходимо обработать перед использованием в аналитике. Обрабатывать можно разными способами, но я чаще всего использую регулярные выражения.
Например есть вот такой список utm_меток выгруженных из ad_content Гугл Аналитикса. Цель вытащить из них id объявлений, чтобы в дальнейшем построить отчёт по их эффективности до kpi конверсий.
Пример выгрузки ad_content по разным источникам из Гугл Аналитикса
Нужный нам id объявлений хранится между нескольких элементов. В Яндексе это |aid| и |adp|. Но конкретные элементы зависят от того, как вы пропишите utm_метки. Например все они могут быть однообразны и разбираться единым выражением.
Чтобы вытащить этот id необходимо с помощью регулярного выражения обработать эту ссылку.
Пример регулярного выражения для Яндекса
С помощью этой регулярки \|aid\|(\S+?)\|adp\| мы говорим:
\|aid\| — начни с этого текста. Символы \ экранируют настоящее значение |, т.к. без экранирования смысл у этих символов другой;
\S+? — возьми все символы кроме пробела;
\|adp\| — до этого текста.
Чтобы сделать аналогичную операцию в Гугл Таблицах используем функцию REGEXEXTRACT.
И на выходе получаем чистый id, по которому можно будем связать расходы из рекламных площадок.
Обратите внимание на то, что в формуле я фильтрую условие по источнику, т.к. utm_метки были записаны по разному и текст нужно выводить между разными элементами.
Я подготовил на основе своего примера разные варианты вывода, но по хорошему нужно фиксить эту проблему на этапе формирования utm_меток, чтобы по одному регулярному выражению получать сразу по всем источникам нужные данные.
Яндекс
\|aid\|(\S+?)\|adp\|
4581056828_10762810146_desktop_|pid|32173035084_|cid|62340136|gid|4581056828|aid|10762810146|adp|no|pos|premium1|src|search_none|dvc|desktop
Фейсбук
\|aid\|\S+?)\|
pos\|cid|23848805094380332|gid|23848805094360332|aid|23848805186850332|pos|Facebook_Right_Column|adn|fb
Гугл
\|aid\|(\S+?)\|gid\|
|pid|kwd-902197508079|cid|13327074478|aid|524922003015|gid|125757829409|pos||src|g_|dvc|c|reg|20955|rin|advertiser-id
Майтаргет
\|(\S+?)
advertiser-id|10953064_campaign-id|44313940_campaign-name|mt_v_cpv_traf_msk_re_all_all_banner-id|93856956_geo_id|5506_gender|f_age|59_weekday|wed_hour|00_user-timezone| 3_search-phrase|
Вконтакте
ad-id\|(\S+?)
campaign-id|1018743605_campaign-name|vk_target_brand_rus_vk-post_ad-id|99167670_client-id|1606794093_platform|android_keyword|
А также прикрепляю ссылку на сервис проверки регулярных выражений.
Заключение
Ещё больше полезных материалов по маркетингу, рекламе, аналитике читайте в моём блоге и Телеграм. Например, я собрал с десяток шаблонов , которые здорово повышают качество и скорость работы: шаблон медиаплана, шаблон сбора семантики, шаблон учёта рабочего времени → забирайте.
Если маркетологи начнут писать регулярные выражения, то мир рухнет.
А маркетологам и нечего ломать регуляркой, тут либо работает и достал нужное значение. Либо нет и соответственно никуда дальше не идёт.
Ох ё... а чем power query то не угодил?
Приведите пожалуйста пример на языке М в PQ, до этого нужда была только в Гугл Таблицах
Эт я не радти «типа докажи», а реально интересно, не задавался в PQ этой задачей. Можно в личку.
Надо добраться до компа, дело в том, что сейчас у меня лапки. 😁
По идее надо строку в список, потом в столбцы по разделителю. Или можно вообще через условный столбец. В общем можно тем что доступно из gui, а можно m функциями преобразования. Но по памяти не подскажу, потому что регулярно этим не занимаюсь, по ситуации, сам подглядываю на планете эксель всегда. Помню что делал и было не сложно.
Ммм, красота, завидую)))
Разделителями проблематично будет из-за разной структуры меток, где то по одному символу, а где-то по другому.
Идеально если бы была конкретная функция, но такой пока не встречал.
Одной точно не обойтись.
К стати, а не проще из самой системы аналитики уже разобранные по параметрам данные вынимать? Я из метрики так и забираю, сразу все по своим столбикам.
Имеется в виду через Параметр URL?
https://yandex.ru/dev/metrika/doc/api2/api_v1/attrandmetr/dim_all.html
Я вот об этом, тут можно что угодно вынуть
Аа, про API, понял, эт да.