<\/p>\n<\/p>\n
В системах сквозной аналитики ключевую роль играет правильная модель атрибуции. Без нее данные невозможно интерпретировать, и их ценность для бизнеса невелика. При этом важно понимать, что любая модель напрямую зависит от качества данных.<\/p>\n
Частая проблема с сырыми данными в том, что информация об одном клиенте дублируется или, напротив, противоречит друг другу в разных источниках.<\/p>\n
Кроме того, что предобработка данных — база для аналитика, без правильного объединения персональных данных в принципе сложно отследить клиентский путь. Значит, нужно настраивать процессы объединения неоднородных персональных данных.<\/p>\n
Сегодня в любом клиентском бизнесе воронки регистрации устроены таким образом, что клиенты попадают в базу множеством способов — часто через маркетинговые каналы, которых всегда много (рассылки, реклама, соцсети). В каждом таком канале может быть ссылка на форму подписки, регистрацию на платформе или чат, и один клиент часто проходит все эти этапы. Сразу же образуется путаница в идентификации, которая сильно влияет на качество данных и результаты аналитики, если ее не лечить.<\/p>\n
Мы столкнулись с этой проблемой, работая с одним из наших клиентов, и решили ее, создав сквозной идентификатор. Это уникальный номер, который присваивается реальному клиенту и дублируется во все источники, где есть данные об этом клиенте, тем самым избавляя от путаницы.<\/p>\n
Мы разрабатывали кастомную систему сквозной аналитики для эдтех-стартапа Refocus. Данные каждого студента в системы Refocus попадали из нескольких источников и были записаны несколько раз — как минимум при регистрации на курс, при первом входе на образовательную платформу и при входе в чат сопровождения.<\/p>\n
В нашем случае мэтчинг был важнее всего по трем источникам из тринадцати:<\/p>\n
Остальные источники, с которыми мы работали, либо не содержали данных студентов (например, цифры эффективности работы sales-менеджеров были завязаны на данных сотрудников и трекались через другие системы), либо дублировали информацию из указанных трех.<\/p>\n
В Discord и Thinkific данные попадали напрямую, от студентов при регистрации в системах, а затем подтягивались в amoCRM. Основные причины несовпадения клиентских данных как у Refocus, так и в похожих случаях — человеческий фактор (опечатки), наличие у людей более чем одного телефона или адреса почты и ограничения самих платформ, с которых приходят данные: разный заданный формат полей и их количество.<\/p>\n
Часть этих факторов может решаться корректировкой самой клиентской воронки. Правда, не все платформы позволяют одинаково настроить вводные поля, а просьбы вводить данные в конкретном формате не всегда работают и не страхуют от ошибок. Плюс, задача аналитиков — получить чистые данные в любом случае.<\/p>\n
Данные в Refocus мы подгружали в хранилище в BigQuery напрямую из интересующих нас источников (рекламных кабинетов, LMS и т. д.), используя Python. В дальнейшем на этих данных строились дашборды в Tableau.<\/p>\n
Обнаружить проблему несложно — при создании хранилища и дальнейшей выгрузке данных из него мы в любом случае чистим датасет от дубликатов и несовпадений.<\/p>\n
Поля, в которых возникали ошибки и для которых нам важен был мэтчинг, чтобы правильно отследить клиентский путь:<\/p>\n
Поначалу, пока количество студентов Refocus было относительно небольшим, достаточно было скриптов, которые объединяли данные по одному из этих полей. В полученных таблицах в Tableau проводился поиск строк с пустым значением в соответствующем поле — и вот видно всех студентов, чьи данные не сошлись.<\/p>\n
Количество таких строк было в пределах пары десятков, и трекать и объединять их было несложно вручную. Это делалось прямо в первоисточниках сотрудниками Refocus, которые могли поправить опечатки и ошибки у себя в системах. После этого наш код выгрузки в хранилище перезапускался и тянул уже чистые данные. Если после этого что-то не сходилось, то наши аналитики правили информацию на уровне базы данных.<\/p>\n
Но при росте компании в какой-то момент число студентов, потерянных при мэтчинге, могло достигать сотни за месяц. Пока ошибка обнаружится, данные поправят в источниках, а мы перезапустим код выгрузки, могло пройти несколько часов — а это критичный интервал. Да и перезапускать выгрузку каждый день ради нескольких несовпадений — неэффективно. Стало понятно, что масштаб проблемы требует более точного и универсального решения.<\/p>\n
Вообще, в такой ситуации возможны несколько вариантов. Можно бесконечно править скрипты мэтчинга, учитывая новые и новые случаи и создавая костыли. А можно, например, настроить алерты в оркестраторе процессов (в нашем случае — Airflow), которые позволят моментально узнавать о появившемся несовпадении и объединять “потерянные” клиентские сущности по паре за раз. Но это все еще неполная автоматизация, и она только ускоряет, а не упрощает процесс.<\/p>\n
Руководствуясь соображениями эффективности, мы предложили ввести сквозной идентификатор — одно значение ID, присваиваемое одному клиенту после автоматической интеграции его данных из разных источников.<\/p>\n
Чтобы понять масштаб проблемы, мы начали с того, что создали таблицы несовпадающих персональных данных. Для этого мы использовали скрипты на Python. Эти скрипты объединяли данные из разных источников и создавали из них большую сводную таблицу. Для того, чтобы свести данные о студенте в одну сущность, использовался мэтчинг по адресу электронной почты. Мы попробовали мэтчить по имени, фамилии, телефону (который сначала надо было привести к одному формату!) и почте, и именно последний вариант показал самую высокую точность. Возможно, дело в том, что из всех данных почта имеет самый однородный формат, поэтому остается учитывать только опечатки.<\/p>\n
Например, нам нужно было мэтчить данные для создания дашборда по возвратам, о которых информация объединялась как раз из наших трех основных источников. В ранней версии скрипта данные отбирались таким образом:<\/p>\n
WITH snapshot_ AS (\r\n SELECT DISTINCT s.*,\r\n IFNULL(ae.name, ap.name) as contact_name,\r\n ap.phone, ae.email,\r\n split(replace(trim(lower(ae.email)),' ',''),'@')[OFFSET(0)] as email_first_part,\r\n ai.thinkific_id, ai.intercom_id, ac.student_id\r\n FROM (\r\n SELECT *,\r\n ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,\r\n FROM `Differture.amocrm_leads_snapshot`\r\n ) s\r\n LEFT JOIN (\r\n SELECT DISTINCT lead_id, contact_id, name, email,\r\n ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num1_\r\n FROM `Differture.amo_emails`\r\n ) ae using(lead_id)\r\n LEFT JOIN (\r\n SELECT DISTINCT lead_id, contact_id, name, phone,\r\n ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num2_\r\n FROM `Differture.amo_phones`\r\n ) ap using(lead_id)\r\n LEFT JOIN `Differture.amo_contact_thinkific_intercom_match` ai using(lead_id)\r\n LEFT JOIN `Differture.AmoContacts` ac on cast(ae.contact_id as string)=ac.amo_id\r\n WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null) and (num2_=1 or num2_ is null)\r\n and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'\r\n )<\/code><\/pre>Как можно заметить, идея сквозного идентификатора здесь уже присутствует — фигурирует student_id<\/tt>. На самом деле, в этой версии скрипта это графа из AmoContacts — таблицы, в которой хранятся только данные из amoCRM. Никаких джойнов по student_id<\/tt> пока не происходит. А происходят по email_first_part<\/tt>, адресу почты до символа @:<\/p>\nselect distinct * from th_amo_ds_rf\r\n left join calendly ce using(email_first_part)\r\n left join typeform_live tfl on email_first_part=tf_email_first_part\r\n left join typeform tf using(email_first_part)\r\n left join csat using(email_first_part)<\/code><\/pre>Первым шагом по практическому введению идентификатора была таблица students_main_info<\/b>, созданная в BigQuery in-house специалистом Refocus. К сожалению, у нас нет доступа к коду, который использовался для присвоения идентификатора. Зато мы можем показать вид этой таблицы:<\/p>\n\n\n\nstudent_full_name<\/td>\n string<\/td>\n<\/tr>\n \nstudent_email<\/td>\n string<\/td>\n<\/tr>\n \nstudent_country_id<\/td>\n string<\/td>\n<\/tr>\n \nstudent_country_name<\/td>\n string<\/td>\n<\/tr>\n \nstudent_courses_ids<\/td>\n array<\/td>\n<\/tr>\n \nstudent_courses_names<\/td>\n array<\/td>\n<\/tr>\n \nstudent_cohort_id<\/td>\n string<\/td>\n<\/tr>\n \nstudent_cohort_name<\/td>\n string<\/td>\n<\/tr>\n \ncohort_community_manager_name<\/td>\n string<\/td>\n<\/tr>\n \ncohort_community_manager_email<\/td>\n string<\/td>\n<\/tr>\n \nstudent_onboarding_live_session_id<\/td>\n string<\/td>\n<\/tr>\n \nstudent_onboarding_live_session_time<\/td>\n string<\/td>\n<\/tr>\n \nstudent_onboarding_live_session_zoom_url<\/td>\n string<\/td>\n<\/tr>\n \namo_contact_id<\/td>\n string<\/td>\n<\/tr>\n \nintercom_contact_id<\/td>\n string<\/td>\n<\/tr>\n \nthinkific_student_id<\/td>\n string<\/td>\n<\/tr>\n \ndiscord_user_id<\/td>\n string<\/td>\n<\/tr>\n \ndiscord_user_discord_id<\/td>\n string<\/td>\n<\/tr>\n \ndiscord_guild_id<\/td>\n string<\/td>\n<\/tr>\n \ndiscord_channel_id<\/td>\n string<\/td>\n<\/tr>\n \ndiscord_roles<\/td>\n string<\/td>\n<\/tr>\n<\/table>\n<\/div>\nВ students_main_info хранились данные из нужных источников с общим идентификатором в первой строке, и объединение проходило через сравнение этого поля.<\/p>\n
При этом поле student_id<\/tt> использовалось пока не везде; также использовались другие поля этой таблицы — например, thinkific_student_id<\/tt> или discord_user_id<\/tt>.<\/p>\n
После выгрузки и мэтчинга данных с помощью students_main_info студентов, которые потерялись при объединении, стало меньше, чем при первой схеме мэтчинга. Так мы убедились, что движемся в верном направлении. Тем не менее, использование одной таблицы, которая содержит больше десятка полей обо всех имеющихся персональных данных, не очень эффективно. Данные в ней уже обработаны скриптом специалиста Refocus, и если надо сверить их с сырыми источниками или ввести новый критерий отслеживания, все придется менять на бэкенде.<\/p>\n
Что получилось в итоге<\/h2>\n
После теста сквозного идентификатора через одну большую таблицу мы продолжили улучшать структуру данных на бэке. Вместо students_main_info усилиями специалиста Refocus появилась подробная сеть более мелких таблиц, которые могут обращаться друг к другу и лежат в одном хранилище с нашими таблицами сырых данных.<\/p>\n
Вот так выглядела схема соотношения этих таблиц:<\/p>\n
<\/p>\n
А вот так выглядела основная таблица Students:<\/p>\n
<\/p>\n
В ней-то и находились основные персональные данные студентов с присвоенным идентификатором, и к ней можно было обращаться для мэтчинга из остальных источников.<\/p>\n
Остальные таблицы выглядели похоже: всегда было поле с идентификатором и информация о какой-то характеристике студента — когорта, курс, роль в дискорде и так далее.<\/p>\n
Финальный код, написанный нашими аналитиками, объединял данные при выгрузке из хранилища, и больше не опирался на ненадежный мэтчинг через имейл.<\/p>\n
Сначала он отбирал собранные нами данные из amoCRM (amocrm_leads_snapshot)<\/tt> и объединял их с контактной информацией клиентов. Затем в таблицу добавлялось поле student_id<\/tt> и отбирались данные, которые понадобятся нам дальше.<\/p>\nWITH snapshot_ AS (\r\n SELECT DISTINCT s.*,\r\n ac.name as contact_name, ac.phone, ac.email,\r\n split(replace(trim(lower(ac.email)),' ',''),'@')[OFFSET(0)] as email_first_part,\r\n ac.intercom_id, ac.student_id\r\n FROM (\r\n SELECT *,\r\n ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,\r\n FROM `Differture.amocrm_leads_snapshot`\r\n ) s\r\n LEFT JOIN (\r\n select cast(al.amo_id as INT64) as lead_id, cast(ac.amo_id as INT64) as contact_id,\r\n ac.name, emails as email, phone, student_id, ic.intercom_id,\r\n ROW_NUMBER() OVER(PARTITION BY al.amo_id ORDER BY ac.amo_id) as num1_\r\n from `Differture.AmoContacts` ac\r\n left join `Differture.AmoLeads` al on al.amo_contact_id=ac.id\r\n left join `Differture.IntercomContacts` ic using(student_id)\r\n , unnest(ac.emails) emails\r\n ) ac using(lead_id)\r\n WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null)\r\n and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like '%test%'\r\n )<\/code><\/pre>Теперь при создании общей таблицы о возвратах с данными из amo, Thinkific и Discord объединение проходило через student_id:<\/p>\n
th_amo_ds_rf as (\r\n select distinct * except (channel_id, channel),\r\n ifnull(channel_id, 'Not in discord') as channel_id,\r\n ifnull(channel, 'Not in discord') as channel\r\n from thinkific_amo_refunds\r\n full outer join discord using(student_id)\r\n )<\/code><\/pre>Когда объединенные таблицы данных студентов были созданы, получить таблицы несовпадений можно было простой строкой кода в Tableau:<\/p>\n
<\/p>\n
Пустое значение поля student_id означает, что мэтча не случилось — где-то информация расходилась слишком сильно и не подтянулась в таблицы с идентификатором. Раньше, до введения идентификатора, поиск был таким же, но обращался к полям почты, телефона или имени-фамилии.<\/p>\n
Ниже можно увидеть таблицу, где данные из Thinkific не совпадали с amoCRM после перехода на Student ID. В этом случае студент есть в LMS, значит, на курсе учится — но его либо нет в системе учета, либо данные в ней разнятся с LMS.<\/p>\n
<\/p>\n
А вот таблица, где данные из Discord не совпадали с amoCRM. Все так же, как выше — студент есть в чатах сопровождения, но не ищется по своим данным в amoCRM.<\/p>\n
<\/p>\n
Оба скриншота показывают количество несовпадений примерно за месяц. Как видно по этим таблицам, количество несовпадений уменьшилось с 80-90 до пары десятков — примерно на 75%. Это позволило сократить количество перезапусков кода выгрузки вручную и уменьшить затраты времени и технических ресурсов на поддержание системы.<\/p>\n
Выводы<\/h2>\n
Сквозной идентификатор — эффективное решение проблемы мэтчинга персональных данных. Он позволяет максимально автоматизировать процесс отслеживания и устранения несовпадений или дубликатов клиентских сущностей при выгрузке данных для анализа. В случаях, когда объем данных в системе невелик, а у компании нет возможности выделить ресурсы на реализацию такого решения, можно воспользоваться и другими вариантами. Например, алерты в оркестраторе процессов хорошо справятся в ситуации, когда объединить данные — вопрос ручного запуска одного скрипта раз в неделю. Но сквозной идентификатор — наверное, самое универсальное из доступных решений, которое покроет большинство ошибок и заметно уменьшит погрешность в качестве данных.<\/p>\n",
"date_published": "2024-09-16T17:57:31+03:00",
"date_modified": "2024-09-16T17:57:05+03:00",
"image": "http:\/\/test.leftjoin.ru\/pictures\/students.png",
"_date_published_rfc2822": "Mon, 16 Sep 2024 17:57:31 +0300",
"_rss_guid_is_permalink": "false",
"_rss_guid": "148",
"_e2_data": {
"is_favourite": false,
"links_required": [
"system\/library\/highlight\/highlight.js",
"system\/library\/highlight\/highlight.css",
"system\/library\/highlight\/highlight.js",
"system\/library\/highlight\/highlight.css",
"system\/library\/highlight\/highlight.js",
"system\/library\/highlight\/highlight.css",
"system\/library\/highlight\/highlight.js",
"system\/library\/highlight\/highlight.css"
],
"og_images": [
"http:\/\/test.leftjoin.ru\/pictures\/students.png",
"http:\/\/test.leftjoin.ru\/pictures\/image-2.png",
"http:\/\/test.leftjoin.ru\/pictures\/filter.png",
"http:\/\/test.leftjoin.ru\/pictures\/unnamed.png",
"http:\/\/test.leftjoin.ru\/pictures\/discord.png"
]
}
},
{
"id": "136",
"url": "http:\/\/test.leftjoin.ru\/all\/youtube-interview-speech-analysis\/",
"title": "Анализируем речь с помощью Python: Сколько раз в минуту матерятся на интервью YouTube-канала «вДудь»?",
"content_html": "
![](\"http:\/\/test.leftjoin.ru\/pictures\/13-1.jpg\")
<\/a><\/p>\n
Выход практически каждого ролика на канале «вДудь» считается событием, а некоторые из этих релизов даже сопровождаются скандалами из-за неосторожных высказываний его гостей.
\nСегодня при помощи статистических подходов и алгоритмов ML мы будем анализировать прямую речь. В качестве данных используем интервью, которые журналист Юрий Дудь (признан иностранным агентом на территории РФ) берет для своего YouTube-канала. Посмотрим с помощью Python, о чем таком интересном говорили в интервью на канале «вДудь».<\/p>\n
Сбор данных<\/h2>\n
C помощью YouTube API<\/a> мы получили список всех видео с канала Юрия Дудя, а также их метаинформацию. О том, как это сделать, вы можете узнать, например, из статьи нашего блога<\/a>.
\nЕсли вы уже слышали знаменитое “Юрий будет дуть, дуть будет Юрий”, то наверняка знаете, что на этом канале есть документальные фильмы, а также интервью, в которых участвуют сразу несколько гостей. Нас заинтересовали только те выпуски, в которых преимущественно говорит только один гость. Поэтому нам пришлось провести фильтрацию всех видео вручную.
\nДля дальнейшего анализа нам необходимо было получить длительности роликов. Это мы сделали с помощью GET-запросов к YouTube API. Результаты приходили в специфическом формате (для примера: “PT1H49M35S”), поэтому их нам пришлось распарсить и перевести в секунды.
\nИтак, мы получили датафрейм, состоящий из 122 записей:<\/p>\n![](\"http:\/\/test.leftjoin.ru\/pictures\/--1.png\")
<\/a><\/p>\n
На основе метаинформации по лайкам, комментариям и просмотрам мы построили следующий Bubble Chart:<\/p>\n