Сквозной идентификатор: решение проблемы мэтчинга персональных данных студентов Refocus

В системах сквозной аналитики ключевую роль играет правильная модель атрибуции. Без нее данные невозможно интерпретировать, и их ценность для бизнеса невелика. При этом важно понимать, что любая модель напрямую зависит от качества данных.

Частая проблема с сырыми данными в том, что информация об одном клиенте дублируется или, напротив, противоречит друг другу в разных источниках.

Кроме того, что предобработка данных — база для аналитика, без правильного объединения персональных данных в принципе сложно отследить клиентский путь. Значит, нужно настраивать процессы объединения неоднородных персональных данных.

Сегодня в любом клиентском бизнесе воронки регистрации устроены таким образом, что клиенты попадают в базу множеством способов — часто через маркетинговые каналы, которых всегда много (рассылки, реклама, соцсети). В каждом таком канале может быть ссылка на форму подписки, регистрацию на платформе или чат, и один клиент часто проходит все эти этапы. Сразу же образуется путаница в идентификации, которая сильно влияет на качество данных и результаты аналитики, если ее не лечить.

Мы столкнулись с этой проблемой, работая с одним из наших клиентов, и решили ее, создав сквозной идентификатор. Это уникальный номер, который присваивается реальному клиенту и дублируется во все источники, где есть данные об этом клиенте, тем самым избавляя от путаницы.

Кейс Refocus: данные и путь клиента

Мы разрабатывали кастомную систему сквозной аналитики для эдтех-стартапа Refocus. Данные каждого студента в системы Refocus попадали из нескольких источников и были записаны несколько раз — как минимум при регистрации на курс, при первом входе на образовательную платформу и при входе в чат сопровождения.

В нашем случае мэтчинг был важнее всего по трем источникам из тринадцати:

— amoCRM, где фиксируется весь клиентский путь студента;
— Discord, где проходило сопровождение студентов;
— Thinkific, сама образовательная платформа с курсами.

Остальные источники, с которыми мы работали, либо не содержали данных студентов (например, цифры эффективности работы sales-менеджеров были завязаны на данных сотрудников и трекались через другие системы), либо дублировали информацию из указанных трех.

В Discord и Thinkific данные попадали напрямую, от студентов при регистрации в системах, а затем подтягивались в amoCRM. Основные причины несовпадения клиентских данных как у Refocus, так и в похожих случаях — человеческий фактор (опечатки), наличие у людей более чем одного телефона или адреса почты и ограничения самих платформ, с которых приходят данные: разный заданный формат полей и их количество.

Часть этих факторов может решаться корректировкой самой клиентской воронки. Правда, не все платформы позволяют одинаково настроить вводные поля, а просьбы вводить данные в конкретном формате не всегда работают и не страхуют от ошибок. Плюс, задача аналитиков — получить чистые данные в любом случае.

Задача и поиск решения

Данные в Refocus мы подгружали в хранилище в BigQuery напрямую из интересующих нас источников (рекламных кабинетов, LMS и т.д.), используя Python. В дальнейшем на этих данных строились дашборды в Tableau.

Обнаружить проблему несложно — при создании хранилища и дальнейшей выгрузке данных из него мы в любом случае чистим датасет от дубликатов и несовпадений.

Поля, в которых возникали ошибки и для которых нам важен был мэтчинг, чтобы правильно отследить клиентский путь:

— имя — да, люди иногда вводят разные вариации ФИО (Юлия, Юля и Бля — на деле один человек!);
— телефон — с кодом страны или без, с пробелами, дефисами или слитно;
— электронная почта — длинные строки сложного формата, в которых легко опечататься.

Поначалу, пока количество студентов Refocus было относительно небольшим, достаточно было скриптов, которые объединяли данные по одному из этих полей. В полученных таблицах в Tableau проводился поиск строк с пустым значением в соответствующем поле — и вот видно всех студентов, чьи данные не сошлись.

Количество таких строк было в пределах пары десятков, и трекать и объединять их было несложно вручную. Это делалось прямо в первоисточниках сотрудниками Refocus, которые могли поправить опечатки и ошибки у себя в системах. После этого наш код выгрузки в хранилище перезапускался и тянул уже чистые данные. Если после этого что-то не сходилось, то наши аналитики правили информацию на уровне базы данных.

Но при росте компании в какой-то момент число студентов, потерянных при мэтчинге, могло достигать сотни за месяц. Пока ошибка обнаружится, данные поправят в источниках, а мы перезапустим код выгрузки, могло пройти несколько часов — а это критичный интервал. Да и перезапускать выгрузку каждый день ради нескольких несовпадений — неэффективно. Стало понятно, что масштаб проблемы требует более точного и универсального решения.

Вообще, в такой ситуации возможны несколько вариантов. Можно бесконечно править скрипты мэтчинга, учитывая новые и новые случаи и создавая костыли. А можно, например, настроить алерты в оркестраторе процессов (в нашем случае  — Airflow), которые позволят моментально узнавать о появившемся несовпадении и объединять “потерянные” клиентские сущности по паре за раз. Но это все еще неполная автоматизация, и она только ускоряет, а не упрощает процесс.

Руководствуясь соображениями эффективности, мы предложили ввести сквозной идентификатор — одно значение ID, присваиваемое одному клиенту после автоматической интеграции его данных из разных источников.

Реализация решения и рабочий процесс

Чтобы понять масштаб проблемы, мы начали с того, что создали таблицы несовпадающих персональных данных. Для этого мы использовали скрипты на Python. Эти скрипты объединяли данные из разных источников и создавали из них большую сводную таблицу. Для того, чтобы свести данные о студенте в одну сущность, использовался мэтчинг по адресу электронной почты. Мы попробовали мэтчить по имени, фамилии, телефону (который сначала надо было привести к одному формату!) и почте, и именно последний вариант показал самую высокую точность. Возможно, дело в том, что из всех данных почта имеет самый однородный формат, поэтому остается учитывать только опечатки.

Например, нам нужно было мэтчить данные для создания дашборда по возвратам, о которых информация объединялась как раз из наших трех основных источников. В ранней версии скрипта данные отбирались таким образом:

WITH snapshot_ AS (
      SELECT DISTINCT s.*,
        IFNULL(ae.name, ap.name) as contact_name,
        ap.phone, ae.email,
        split(replace(trim(lower(ae.email)),’ ‘,»),’@’)[OFFSET(0)] as email_first_part,
        ai.thinkific_id, ai.intercom_id, ac.student_id
      FROM (
        SELECT *,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,
        FROM `Differture.amocrm_leads_snapshot`
      ) s
      LEFT JOIN (
        SELECT DISTINCT lead_id, contact_id, name, email,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num1_
        FROM `Differture.amo_emails`
      ) ae using(lead_id)
      LEFT JOIN (
        SELECT DISTINCT lead_id, contact_id, name, phone,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY contact_id) as num2_
        FROM `Differture.amo_phones`
      ) ap using(lead_id)
      LEFT JOIN `Differture.amo_contact_thinkific_intercom_match` ai using(lead_id)
      LEFT JOIN `Differture.AmoContacts` ac on cast(ae.contact_id as string)=ac.amo_id
      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null) and (num2_=1 or num2_ is null)
        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like ‘%test%’
    )

Как можно заметить, идея сквозного идентификатора здесь уже присутствует — фигурирует student_id. На самом деле, в этой версии скрипта это графа из AmoContacts — таблицы, в которой хранятся только данные из amoCRM. Никаких джойнов по student_id пока не происходит. А происходят по email_first_part, адресу почты до символа @:

select distinct * from th_amo_ds_rf
    left join calendly ce using(email_first_part)
    left join typeform_live tfl on email_first_part=tf_email_first_part
    left join typeform tf using(email_first_part)
    left join csat using(email_first_part)

Первым шагом по практическому введению идентификатора была таблица students_main_info, созданная в BigQuery in-house специалистом Refocus. К сожалению, у нас нет доступа к коду, который использовался для присвоения идентификатора. Зато мы можем показать вид этой таблицы:

В students_main_info хранились данные из нужных источников с общим идентификатором в первой строке, и объединение проходило через сравнение этого поля.

При этом поле student_id использовалось пока не везде; также использовались другие поля этой таблицы — например, thinkific_student_id или discord_user_id.

После выгрузки и мэтчинга данных с помощью students_main_info студентов, которые потерялись при объединении, стало меньше, чем при первой схеме мэтчинга. Так мы убедились, что движемся в верном направлении. Тем не менее, использование одной таблицы, которая содержит больше десятка полей обо всех имеющихся персональных данных, не очень эффективно. Данные в ней уже обработаны скриптом специалиста Refocus, и если надо сверить их с сырыми источниками или ввести новый критерий отслеживания, все придется менять на бэкенде.

Что получилось в итоге

После теста сквозного идентификатора через одну большую таблицу мы продолжили улучшать структуру данных на бэке. Вместо students_main_info усилиями специалиста Refocus появилась подробная сеть более мелких таблиц, которые могут обращаться друг к другу и лежат в одном хранилище с нашими таблицами сырых данных.

Вот так выглядела схема соотношения этих таблиц:

А вот так выглядела основная таблица Students:

В ней-то и находились основные персональные данные студентов с присвоенным идентификатором, и к ней можно было обращаться для мэтчинга из остальных источников.

Остальные таблицы выглядели похоже: всегда было поле с идентификатором и информация о какой-то характеристике студента — когорта, курс, роль в дискорде и так далее.

Финальный код, написанный нашими аналитиками,  объединял данные при выгрузке из хранилища, и больше не опирался на ненадежный мэтчинг через имейл.

Сначала он отбирал собранные нами данные из amoCRM (amocrm_leads_snapshot) и объединял их с контактной информацией клиентов. Затем в таблицу добавлялось поле student_id и отбирались данные, которые понадобятся нам дальше.

WITH snapshot_ AS (
      SELECT DISTINCT s.*,
        ac.name as contact_name, ac.phone, ac.email,
        split(replace(trim(lower(ac.email)),’ ‘,»),’@’)[OFFSET(0)] as email_first_part,
        ac.intercom_id, ac.student_id
      FROM (
        SELECT *,
          ROW_NUMBER() OVER(PARTITION BY lead_id ORDER BY updated_at DESC) as num_,
        FROM `Differture.amocrm_leads_snapshot`
      ) s
      LEFT JOIN (
        select cast(al.amo_id as INT64) as lead_id, cast(ac.amo_id as INT64) as contact_id,
          ac.name, emails as email, phone, student_id, ic.intercom_id,
          ROW_NUMBER() OVER(PARTITION BY al.amo_id ORDER BY ac.amo_id) as num1_
        from `Differture.AmoContacts` ac
        left join `Differture.AmoLeads` al on al.amo_contact_id=ac.id
        left join `Differture.IntercomContacts` ic using(student_id)
        , unnest(ac.emails) emails
      ) ac using(lead_id)
      WHERE (num_=1 or num_ is null) and (num1_=1 or num1_ is null)
        and s.pipeline_id in (4920421,5245535) and s.status_id=142 and lower(s.lead_name) not like ‘%test%’
    )

Теперь при создании общей таблицы о возвратах с данными из amo, Thinkific и Discord объединение проходило через student_id:

th_amo_ds_rf as (
      select distinct * except (channel_id, channel),
        ifnull(channel_id, ‘Not in discord’) as channel_id,
        ifnull(channel, ‘Not in discord’) as channel
      from thinkific_amo_refunds
      full outer join discord using(student_id)
    )

Когда объединенные таблицы данных студентов были созданы, получить таблицы несовпадений можно было простой строкой кода в Tableau:

Пустое значение поля student_id означает, что мэтча не случилось — где-то информация расходилась слишком сильно и не подтянулась в таблицы с идентификатором. Раньше, до введения идентификатора, поиск был таким же, но обращался к полям почты, телефона или имени-фамилии.

Ниже можно увидеть таблицу, где данные из Thinkific не совпадали с amoCRM после перехода на Student ID. В этом случае студент есть в LMS, значит, на курсе учится — но его либо нет в системе учета, либо данные в ней разнятся с LMS.

А вот таблица, где данные из Discord не совпадали с amoCRM. Все так же, как выше — студент есть в чатах сопровождения, но не ищется по своим данным в amoCRM.

Оба скриншота показывают количество несовпадений примерно за месяц. Как видно по этим таблицам, количество несовпадений уменьшилось с 80-90 до пары десятков — примерно на 75%. Это позволило сократить количество перезапусков кода выгрузки вручную и уменьшить затраты времени и технических ресурсов на поддержание системы.

Выводы

Сквозной идентификатор — эффективное решение проблемы мэтчинга персональных данных. Он позволяет максимально автоматизировать процесс отслеживания и устранения несовпадений или дубликатов клиентских сущностей при выгрузке данных для анализа. В случаях, когда объем данных в системе невелик, а у компании нет возможности выделить ресурсы на реализацию такого решения, можно воспользоваться и другими вариантами. Например, алерты в оркестраторе процессов хорошо справятся в ситуации, когда объединить данные — вопрос ручного запуска одного скрипта раз в неделю. Но сквозной идентификатор — наверное, самое универсальное из доступных решений, которое покроет большинство ошибок и заметно уменьшит погрешность в качестве данных.