История JaffleGaggle: Моделирование данных для обзора клиентов 360

Примечание редактора: В этом руководстве Донни рассказывает вымышленную историю SaaS-компании под названием JaffleGaggle, которой необходимо сгруппировать своих индивидуальных пользователей freemium в аккаунты компаний (так называемый обзор клиентов 360), чтобы стимулировать рост, основанный на продукте.

Вы можете следовать технике моделирования данных Донни для разрешения идентичности в этом репозитории проекта dbt. Он включает набор демонстрационных CSV-файлов, которые вы можете использовать как семена dbt, чтобы протестировать проект Донни самостоятельно.

Прежде чем мы начнем: небольшое примечание о Jaffles

Если вы были в сфере dbt, вы, вероятно, знаете легенду о магазине Jaffle. Если нет, я бы рекомендовал потратить минуту на ознакомление с README для оригинального демонстрационного проекта Jaffle Shop от Клэр Кэрролл (в противном случае это руководство может показаться немного странным, но все же полезным для чтения).

Кратко, jaffle это:

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

Смотрите выше: Вкусные, вкусные jaffles.

Jaffle Shop — это демонстрационный репозиторий, упомянутый в Руководстве по началу работы с dbt, и его jaffles занимают особое место в сердцах сообщества dbt, а также на Data Twitter™.

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

Что такое обзор клиентов 360?

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

Это может быть сложно, потому что люди меняют компании, создают новые аккаунты с разными адресами электронной почты, или одна и та же компания может иметь разные связанные рабочие пространства (в нашем случае — gaggles 🦢).

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

Познакомьтесь с JaffleGaggle, нашей вымышленной компанией

В нашем вымышленном мире данных для сегодняшнего примера, B2B-компания увидела, что людям действительно нравится одна вещь (например, jaffles) и нашла способ масштабировать эту любимую вещь в бизнес. Встречайте JaffleGaggle.

Продукт JaffleGaggle состоит из двух частей:

1. Лента рецептов jaffle, поддерживаемая функциональностью, которая позволяет заказывать все ингредиенты, необходимые для приготовления ночных jaffles дома.

2. Социальные группы для укрепления связей среди команд компании (ласково называемые Gaggle), где коллеги могут приглашать друг друга с помощью бесплатной электронной почты для виртуальных часов jaffle.

JaffleGaggle быстро растет и только что приобрела CRM (ура!), но он пока пуст (меньше ура 😟). К концу этого руководства вы и команда данных JaffleGaggle узнаете, как использовать dbt для моделирования данных аккаунтов, пользователей и событий из использования их приложения и агрегировать их в своем хранилище, чтобы загрузить в CRM для использования командой продаж.

По мере того, как люди приглашают больше своих коллег в свою Gaggle, они могут разблокировать еще больше рецептов и jaffles.

Видно выше: Один из многих, многих вкусных рецептов jaffle, которые ждут команды на JaffleGaggle.

Хорошо, теперь, когда мы заставили вас захотеть вкусных, вкусных jaffles, вот что это имеет общего с данными и ростом, основанным на продукте (так называемым PLG).

Как обзор клиентов 360 поддерживает рост, основанный на продукте

JaffleGaggle, как и многие стартапы, сосредоточена на подписании компаний на годовые контракты, чтобы они могли привлечь венчурное финансирование (по безумной оценке). Для этого они хотят развивать свои продажи, чтобы нацелиться на компании с активными gaggles.

JaffleGaggle должна отслеживать информацию о своих взаимодействиях с клиентами и бизнесами, к которым они принадлежат, включая данные, которые позволят команде продаж ответить на несколько ключевых вопросов:

• Как пользователь взаимодействовал с платформой?
• Сколько рабочих пространств связано с компанией?
• Кто из пользователей компании является активным и с кем следует связаться?

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

Этот процесс агрегации требует аналитического хранилища, так как все эти вещи нужно синхронизировать вместе вне базы данных приложения, чтобы включить другие источники данных (информация о биллинге/событиях, прошлые точки взаимодействия в CRM и т.д.). Таким образом, мы можем создать наш модный обзор клиентов 360 в JaffleGaggle, что является стандартным проектом для команды данных B2B-компании.

Погружение в моделирование данных

В этом руководстве я проведу вас по пути JaffleGaggle к созданию обзора клиентов 360 с использованием dbt, чтобы они (и вы тоже) могли усилить свою стратегию PLG с помощью лучших данных (и распространить любовь к jaffles повсюду).

Структура данных разбивается следующим образом:

• 823 gaggles
• 5,781 пользователей (уникальных по электронной почте, могут быть связаны только с одним gaggle)
• 120,307 событий (‘recipe_viewed’, ‘recipe_favorited’ или ‘order_placed’)

Давайте начнем.

Внимание, строители! Если бы это был реальный поток событий, было бы гораздо лучше использовать инкрементальные модели на основе временной метки, но поскольку это проект-песочница, я этого не сделал.

Шаг 1: Определите наши сущности

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

Ниже я разберу DAG на каждом этапе нашего процесса, чтобы вы могли увидеть, как все это строится вместе.

Для использования нашего CRM нам нужно загрузить данные для следующего:

• Контакты (контакты, уникальные по адресу электронной почты)

• Gaggles (понимание активности рабочего пространства)

• Аккаунты (компании, которые наша команда продаж может отслеживать и приоритизировать)

Шаг 2: Моделирование контакта

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

1. Выполнение извлечения домена электронной почты из электронной почты

2. Пометка личных электронных писем

3. Создание столбца для корпоративных электронных писем

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

Шаг 2.1: Извлечение домена электронной почты из электронной почты

Для этого шага взгляните на фрагмент из models/staging/stg_users.sql ниже. В нем мы выполняем извлечение домена электронной почты из электронной почты.

    select
        id as user_id,
        name as user_name,
        email,

        {{ extract_email_domain('email') }} AS email_domain,

        gaggle_id,
        created_at

    from source

Мы определили извлечение домена электронной почты как макрос под названием extract_email_domain, который мы вызываем на строке 18 (которую вы можете найти в приведенном ниже фрагменте).

Этот макрос использует регулярное выражение для захвата текста справа от символа ‘@’ и гарантирует использование только параметра электронной почты в нижнем регистре перед извлечением домена. Это потому, что домены электронной почты не чувствительны к регистру, но SQL чувствителен (см. пользователей 2954 и 3140 в данных семян для примера).

{% macro extract_email_domain(email) %}

{# Это SQL для извлечения домена электронной почты в формате SQL Snowflake #}

	regexp_substr(lower({{ email }}), '@(.*)', 1, 1, 'e',1)

{% endmacro %}

Внимание, строители! Обратите внимание, что мы не проверяли неправильно отформатированные электронные письма, такие как точки в конце домена или пробелы. Убедитесь, что вы проверили свой набор данных, чтобы увидеть, является ли это допустимым предположением.

В общем, было бы полезно использовать регулярное выражение для удаления и извлечения адреса электронной почты. Однако, поскольку это B2B-кейс, не все домены электронной почты созданы равными. Мы хотим убедиться, что помечаем личные электронные письма, чтобы они обрабатывались иначе, чем корпоративные электронные письма, к которым наша команда продаж будет обращаться (это делает продажи более продуктивными и гарантирует, что мы не будем обращаться к людям более одного раза).

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

Шаг 2.2: Пометка личных электронных писем

Далее мы можем пометить личные электронные письма с помощью models/jafflegaggle_contacts.sql, который вызывает другой макрос в начале файла, чтобы подтянуть личные электронные письма, которые мы хотели бы исключить:

{% macro get_personal_emails() %}

	{{ return(('gmail.com', 'outlook.com', 'yahoo.com', 'icloud.com', 'hotmail.com')) }}

{% endmacro %}

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

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

Шаг 2.3: Создание столбца для корпоративных электронных писем

Далее мы создадим столбец для корпоративных электронных писем, который будет пустым, если домен электронной почты является личным, также в той же модели jafflegaggle_contacts:

iff(users.email_domain in {{ personal_emails }}, null, users.email_domain)

           as corporate_email

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

Другие аспекты этой модели пользователей связаны с данными событий, на которые мы ссылаемся в потоке событий. Например, событие order_placed разбивается в , потому что оно важно для нашего случая использования в JaffleGaggle (это основа для получения той самой прибыли 💰).

order_events as (

    select
        user_id,

        min(timestamp) as first_order,
        max(timestamp) as most_recent_order,
        count(event_id) as number_of_orders

    from events
    where event_name = 'order_placed'
    group by 1

),

К концу модели jafflegaggle_contacts у нас есть унифицированное событий по электронной почте пользователя, с отфильтрованными личными доменами электронной почты.

Шаг 2.4: Объединение дублирующихся контактов

Определение: Когда я пишу "Человек в цикле", я имею в виду, что операционные люди в компании вносят вклад в целостность данных на этапе моделирования и проверяют данные на качество. Это очень важно для того, чтобы доменные знания использовались в определениях CRM.

Я намеренно оставил два файла семян, один из которых data/merged_user.csv содержит пользователей, которых команда JaffleGaggle идентифицировала как одного и того же человека.

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

Однако, поскольку JaffleGaggle только начала строить свою инфраструктуру для CRM, этот CSV-файл существует для сопоставления старых адресов электронной почты с новыми (этот пример для Констанс Рор, userId 6759):

old_email, new_email

constancerohr@icloud.com,constancerohr@outlaws.com

Итак, что это делает для дублирующихся контактов? На строке 100 jafflegaggle_contacts мы выполняем левое соединение с этим файлом семян merged_user, чтобы сопоставить старые электронные письма с новыми:

left join {{ ref('merged_user') }}

Теперь, чтобы сгенерировать dbt docs и просмотреть наш DAG, мы можем выполнить:

dbt docs generate dbt docs serve

Это дает нам доступ к DAG для jafflegaggle_contacts.sql, который может служить источником истины для команды Ops JaffleGaggle о том, где находятся аналитические определения для контактов в системе.

Шаг 3: Моделирование Gaggle

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

Например, NFL Rams (которые обожают jaffles) переехали из Сент-Луиса в Лос-Анджелес, изменив свой корпоративный домен электронной почты в процессе. Для команды продаж JaffleGaggle важно, чтобы измененный домен электронной почты не изменял разрешение идентичности для gaggles и аккаунтов в процессе.

Если мы не успешно выполним это объединение, когда корпоративный домен изменится (например, rams.sl и rams.la), у нас будет две строки для одного и того же gaggle_id (т.е. 1187), когда на самом деле нам нужна только одна. Файл семян merged_company_domain + левое соединение в модели final_merged CTE jafflegaggle_facts решает эту проблему для нас.

old_email, new_email

rams.sl,rams.la

Внимание, строители! Как указано в комментариях к файлу jafflegaggle_facts, это предполагает, что существует только один некорпоративный домен электронной почты на рабочее пространство. Если это не так, нам нужно будет установить правила для того, что делать среди gaggles с несколькими компаниями внутри, например, выполнять атрибуцию к соответствующим корпоративным адресам электронной почты пользователей в каждом Gaggle.

Мы также агрегируем информацию о всей Gaggle, включая пользователей, у которых нет корпоративного домена. Это можно найти в CTE под названием gaggle_total_facts.

gaggle_total_facts as (

    select
        gaggles.gaggle_id,
        gaggles.gaggle_name,
        gaggles.created_at,

        min(users.first_event) as first_event,
        max(users.most_recent_event) as most_recent_event,
        sum(number_of_events) as number_of_events,
        count(users.user_id) as number_of_users,

        min(users.first_order) as first_order,
        max(users.most_recent_order) as most_recent_order,
        sum(users.number_of_orders) as number_of_orders

    from users
    left join gaggles on users.gaggle_id = gaggles.gaggle_id

    group by 1,2,3

),

Я знаю, это много кода. Ознакомьтесь с dbt docs для проекта, чтобы получить объяснение полей. Вот вывод финальной таблицы jafflegaggle_facts:

Ссылаясь на DAG из dbt docs, вы можете увидеть, как мы уже получаем выгоду от объединения на уровне пользователей для аналитической информации, связанной с jafflegaggle_contacts.

Мы также используем восходящую таблицу stg_gaggles, которая подтягивает информацию о создании Gaggle и его имени.

Шаг 4: Моделирование аккаунта

Мы смоделировали контакты и gaggles, так что теперь мы на уровне аккаунта.

Мы хотим объединить характеристики для разных gaggles, которые имеют один и тот же домен корпоративной электронной почты, чтобы мы могли использовать операционную аналитику для создания обзора клиентов 360 для команды продаж, чтобы приоритизировать усилия по взаимодействию. 🙌

Посмотрев на dbt docs, мы видим, что каждая модель является восходящим источником для jafflegaggle_corporate_accounts.

На этом уровне мы объединяем компанию для объединенного домена, как мы сделали с merged_users ранее. Вот код для этого шага:

select

       coalesce(mcd.new_domain, corporate_gaggles.corporate_email) as corporate_email,

       ....

   from corporate_gaggles

   left join {{ ref('merged_company_domain') }} mcd on corporate_gaggles.corporate_email = mcd.old_domain

   group by 1

Примечание: Это не единственное место, где мы ссылаемся на файл merged_company_domain. Нам также нужно ссылаться на это в случае, если существуют различные gaggles со старыми и новыми корпоративными доменами, такими как Thrashers и Jets.

Вот вывод финальной таблицы корпоративных аккаунтов:

Шаг 4.1: Идентификация активных пользователей для аккаунта

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

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

Отличные новости для команды продаж! Мы можем идентифицировать этих людей, используя следующий CTE для идентификации лучших пользователей для каждого аккаунта.

corporate_power_users as (

   select

       corporate_email,

       get(array_agg(user_id) within group (order by created_at asc), 0)::int as first_user_id,

       get(array_agg(user_id) within group (order by number_of_events desc), 0)::int as most_active_user_id,

       get(array_agg(user_id) within group (order by number_of_orders desc), 0)::int as most_orders_user_id

   from {{ ref('jafflegaggle_contacts') }}

   where corporate_email is not null

   group by 1

),

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

Построение империи JaffleGaggle вашей мечты

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

1. Определите ваши сущности

2. Моделируйте контакт

   1. Выполните извлечение домена электронной почты из электронной почты

   2. Пометьте личные электронные письма

   3. Создайте столбец для корпоративных электронных писем

   4. Добавьте вашу логику "человек в цикле"

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

   5. Добавьте логику объединения "человек в цикле"

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

   6. Добавьте логику объединения "человек в цикле"

Если мы хотим пойти дальше, мы могли бы:

• Создать модель для gaggles, основанную исключительно на личных электронных письмах, которые могут рассмотреть возможность перехода на платный
• Создать еще один уровень разбивки, который использует user_gaggle или gaggle_domain

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

1. Используйте обратный ETL, чтобы синхронизировать эти таблицы фактов с CRM
2. Отслеживайте информацию о платежах или подписках для аккаунтов, которые в настоящее время платят
3. Используйте /, чтобы вернуть данные CRM в хранилище данных
4. Разработайте, как вы можете расширить текущее решение по объединению

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

Напишите мне в dbt Community Slack (я @Donny Flynn (Census)), если хотите узнать больше о JaffleGaggle, PLG, обратном ETL или о том, как я создал эти случайные наборы данных. 😊

PS: @clrclr, не ненавидь меня. 🙏🏻