Маркетинг и реклама - Формирование структуры данных для анализа конверсии рекламного трафика в продажи

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

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

Краткое содержание главы
Определение целей и метрик конверсии и атрибуции в мультиканальном окружении
Архитектура данных и проектирование схемы для анализа конверсий
Интеграция источников данных и управление качеством данных
Эталонная схема DWH и базовые примеры анализа конверсий и атрибуции

Архитектура и модель данных

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

Ключевые концепции:

Фактовые и измеряемые события: воронка взаимодействий от показа (impression) до клика (click), просмотра (view), добавления в корзину (add_to_cart) и покупки (purchase). Эти события следует хранить в отдельных или совмещённых факт-таблицах с привязкой к единицам времени и керактеристикам сессий.
Измерения и конформированные измерения: единые размерности времени, кампании, источника/канала, объявления, продукта, устройства и аудитории позволяют агрегировать данные в разных контекстах без дублирования.
Константы времени и SCD: для атрибуции и анализа с учётом изменений рекламных кампаний необходимы конформированные размерности и механизмы управления изменениями в измерениях (SCD). Это позволяет сохранять историю изменений кампаний, тестовых групп, креативов.
Атрибуция и согласование источников: к каждому событию присваивается идентификатор сессии и, если возможно, идентификатор пользователя. Это обеспечивает сопоставление данных между платформами и внутренними системами. Важна также поддержка нескольких моделей атрибуции (например, LAST_CLICK, FIRST_CLICK, LINEAR, TIME_DECAY) и гибкая конфигурация на уровне кампании.
Границы ответственности и качество данных: документированные соглашения об источниках данных, частоте обновления и правилах обработки достаточны для поддержания достоверности. Логирование линейности данных и трассируемость изменений позволяют оперативно обнаруживать расхождения между источниками.

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

Основные компоненты модели данных:
- dim_time: дата и атрибуты времени (год, квартал, месяц, неделя, день недели, праздники).
- dim_campaign: идентификатор кампании, имя, формат (поисковая реклама, медийная, промо), страна/рынок, параметры UTM.
- dim_ad: идентификатор объявления, креатив, площадка, таргетинг.
- dim_channel: канал рекламы (например, Google Ads, Meta Ads, рекламная сеть маркетплейса).
- dim_user: идентификатор пользователя (или анонимизированный идентификатор сессии) и его признаки поведения.
- dim_product: товар/категория, цена, бренд, SKU, атрибуты товара.
- fact_clicks, fact_impressions: данные по взаимодействиям пользователя с рекламой (включая привязку к кампании и устройству).
- fact_conversions: конверсии и связанные продажи, Revenue, order_id, attribution data (по модели атрибуции).
- staging/auxiliary: промежуточные таблицы для очистки и нормализации данных из источников.

Эмпирически полезна идея конформирования размерностей: если кампания обновляется или добавляются новые креативы, данные по старым записям сохраняются в исторической таблице, а новые значения связываются через версии размерностей. Такой подход упрощает сопоставление данных между источниками и поддерживает долгосрочную аналитику.

Ниже приводится упрощённая структура DDL-кода для иллюстрации. Это базовый шаблон: конкретные поля и типы должны адаптироваться под используемую платформу DWH.

-- Пример упрощённой эталонной схемы DWH
CREATE TABLE dim_time (
  time_id DATE PRIMARY KEY,
  year INT,
  quarter INT,
  month INT,
  week INT,
  day INT,
  is_holiday BOOLEAN
);

CREATE TABLE dim_campaign (
  campaign_id STRING PRIMARY KEY,
  campaign_name STRING,
  channel STRING,
  source STRING,
  utm_source STRING,
  utm_medium STRING,
  utm_campaign STRING,
  start_date DATE,
  end_date DATE
);

CREATE TABLE dim_ad (
  ad_id STRING PRIMARY KEY,
  campaign_id STRING REFERENCES dim_campaign(campaign_id),
  adgroup_id STRING,
  ad_name STRING,
  creative STRING,
  size STRING
);

CREATE TABLE dim_user (
  user_id STRING PRIMARY KEY,
  anonymous_id STRING,
  country STRING,
  device_type STRING,
  first_visit DATE,
  last_visit DATE
);

CREATE TABLE dim_product (
  product_id STRING PRIMARY KEY,
  sku STRING,
  product_name STRING,
  category STRING,
  brand STRING,
  price DECIMAL(18,2)
);

CREATE TABLE fact_clicks (
  click_id STRING PRIMARY KEY,
  time_id DATE REFERENCES dim_time(time_id),
  user_id STRING REFERENCES dim_user(user_id),
  ad_id STRING REFERENCES dim_ad(ad_id),
  campaign_id STRING REFERENCES dim_campaign(campaign_id),
  channel STRING,
  event_time TIMESTAMP
);

CREATE TABLE fact_impressions (
  impression_id STRING PRIMARY KEY,
  time_id DATE REFERENCES dim_time(time_id),
  user_id STRING REFERENCES dim_user(user_id),
  ad_id STRING REFERENCES dim_ad(ad_id),
  campaign_id STRING REFERENCES dim_campaign(campaign_id),
  channel STRING,
  impression_time TIMESTAMP
);

CREATE TABLE fact_conversions (
  conversion_id STRING PRIMARY KEY,
  time_id DATE REFERENCES dim_time(time_id),
  user_id STRING REFERENCES dim_user(user_id),
  order_id STRING,
  campaign_id STRING REFERENCES dim_campaign(campaign_id),
  ad_id STRING REFERENCES dim_ad(ad_id),
  product_id STRING REFERENCES dim_product(product_id),
  channel STRING,
  revenue DECIMAL(18,2),
  units INT,
  attribution_model STRING,
  attribution_weight DECIMAL(5,4),
  conversion_time TIMESTAMP
);

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

Моделирование конверсии и атрибуции

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

Основные подходы к атрибуции:

LAST_CLICK: конверсию приписывают последнему взаимодействию перед покупкой. Прост в реализации, часто даёт сильную корреляцию с конкретным рекламным источником, но недополучает вклад ранних взаимодействий.
FIRST_CLICK: конверсию приписывают первому касанию в пути пользователя. Полезно для оценки эффективности привлечения, но может недооценивать повторные контакты.
LINEAR: равномерное распределение конверсии между всеми касаниями в пути. Прост в интерпретации, но может не отражать фактический вклад каждого контакта.
TIME_DECAY: больший вес отдаётся последним касаниям, но более "мягко" учитывает ранние контакты. Хороший компромисс между ранними и поздними взаимодействиями.
POSITION_BASED: акцент на первом и последнем касании, оставляя меньшую часть на промежуточные контакты.
HYBRID/Custom: сочетание моделей в зависимости от сегмента, канала, периода или типа кампании.

Реализация в DWH требует хранения и поддержки нескольких элементов:

Исторические привязки к каждому событию: каждый клик, показ или сессия должны быть связаны с конкретной кампанией и каналом, чтобы можно было повторно вычислить атрибуцию при смене модели.
Модель атрибуции на уровне конверсии: хранение атрибуции в факт_conversions (attribution_model, attribution_weight) или в отдельной таблице атрибуции для разных моделей.
Временные окна атрибуции: поддержка различных окон (например, 7-14 дней) для оценки влияния рекламы на долгосрочные покупки.
Алгоритмы перерасчёта: пакетные задачи, которые могут пересчитывать атрибуцию на основе обновлённых правил и новых событий, без изменения исходных данных.

Пример концептуального сценария:

Событие impression и click фиксируются в факт_impressions и факт_clicks.
При покупке создаётся запись в fact_conversions с данными о revenue, order_id и campaign_id.
По умолчанию применяется LAST_CLICK: соответствующая запись в fact_conversions получает attribution_weight = 1 для последнего клика, остальные клики по данному сеансу могут быть помечены как нулевые веса или взвешены по другой модели.
```
-- Пример простого расчёта CVR по каждому кампейну с Last-Click атрибуцией
## WITH clicks AS (
  SELECT user_id, campaign_id, ad_id, event_time
  FROM raw_events
  WHERE event_type = 'click'
),
conversions AS (
  SELECT user_id, order_id, event_time AS conversion_time, revenue, campaign_id
## FROM orders o
  JOIN raw_events e ON o.checkout_id = e.event_id
  WHERE e.event_type = 'purchase'
)
SELECT
  c.campaign_id,
  COUNT(DISTINCT conv.user_id) AS conversions,
  SUM(conv.revenue) AS revenue,
## COUNT(DISTINCT click.user_id) AS clicks,
  1.0 * COUNT(DISTINCT conv.user_id) / NULLIF(COUNT(DISTINCT click.user_id),0) AS cvr
## FROM conversions conv
JOIN clicks click ON conv.user_id = click.user_id
  AND conv.campaign_id = click.campaign_id
GROUP BY c.campaign_id;
```
Приведённый пример иллюстрирует базовый подход: он показывает, как соотнести продажи с кликами по кампаниям и вычислить CVR. На практике следует строить более гибкие механизмы: учитывать мультиканальные касания, различать модели атрибуции по каналам, использовать атрибуцию по окнам времени и реализовать хранение нескольких версий атрибуционных параметров для регрессионного анализа и сценариев “что-if”.

Источники данных и интеграции

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

Типичные источники:

Рекламные платформы: Google Ads, Meta Ads, рекламные сети маркетплейса. Важно иметь доступ к данным по кампаниям, группам объявлений, креативам, расходам, кликам и показам.
Веб-аналитика: GA4 или аналог, предоставляющий данные о сессиях, поведении на сайте, путях пользователя, UTM-метках и взаимодействиях.
Внутренние данные магазина: заказы, продажи, возвраты, SKU и связанные данные по товарам.
Каталог товаров: структура категорий, брендов, атрибуты товара, цены и наличие.
Идентификация пользователей: через cookie/идентификаторы сессии и, при наличии, зашифрованные или агрегированные идентификаторы клиентов.

Задачи интеграции:

Связать рекламные события с заказами: щепетильная задача, требующая единицы идентификации (session_id, user_id) и сопоставления через цепочки событий.
Привязка UTM и параметров источника к записям кампании: обеспечение консистентности между различными источниками и платформами.
Нормализация полей и схем: приведение полей к единому формату (timestamps, currency, product_id, campaign_id и т. д.).
Управление данными и качеством: включение проверок целостности зависимостей между фактами и измерениями, обработка пропусков, дубликатов и ошибок сопоставления.
Эволюция схемы: поддержка изменений в источниках данных через версионирование схем, документирование контрактов данных (data contracts).

Инструменты и подходы:

Оркестрация и обработка данных: Apache Airflow обеспечивает управление зависимостями и расписание загрузок, извлечений и трансформаций, а также мониторинг.
Трансформации и тестирование моделей: dbt позволяет построить модульные трансформации, тестирование данных и управление зависимостями между таблицами.
Вопросы качества и соответствия: подходы на базе данных контрактов и тестов, а также инструменты для контроля качества (например, на уровне данных) обеспечивают прозрачность и воспроизводимость.
Варианты хранения: для больших массивов данных и аналитических запросов выбираются колоночные и масштабируемые архитектуры; в рамках политики компании можно опираться на облачные платформы или гибридные решения. В рамках открытых технологий и российской экосистемы часто упоминаются инструменты, такие как dbt и Apache Airflow, которые хорошо сочетаются с различными хранилищами данных и позволяют реализовать повторяемые процессы загрузки и трансформации.
Примечание о примерах: в рамках данного раздела упоминаются практики и инструменты без привязки к конкретной поставке, чтобы сохранить фокус на архитектуре и процессах. При необходимости можно адаптировать набор инструментов под отраслевые требования и регулятивные ограничения.

Эталонная схема DWH и реализация

На практике рекомендуется реализовать концепцию «звезды» (star schema) с конформированными размерностями и несколькими факт-таблицами, обеспечивающими гибкость и скорость аналитики. Основное преимущество такой схемы - простота агрегации и понятность для бизнес-пользователей.

Ключевые элементы:

Факт_conversion как основная единица анализа конверсий и выручки. Включает параметры attribution_model и attribution_weight для поддержки мульти-модели атрибуции.
Факты_clicks и факт_impressions служат для анализа первичных взаимодействий и соответствия путей пользователя.
Размерности dim_time, dim_campaign, dim_ad, dim_user, dim_product и возможно dim_geography и dim_device для региональных и технических сегментаций.
Механизм версионирования размерностей: при изменении кампании или креатива создаются новые версии записей, а старые сохраняются для исторической анализируемости.
Логика атрибуции: в fact_conversions может сохраняться несколько записей по разным моделям атрибуции или в отдельной таблице, чтобы позволить сравнение сценариев и исторический анализ.

Практический подход к реализации:

Привязка событий к времени: каждая запись факта содержит time_id, что обеспечивает согласованный анализ по дням, неделям и месяцам.
Единый идентификатор пользователя: пользовательские сессии и пользователи должны быть синхронизированы через единый идентификатор (user_id) и/или session_id, чтобы корректно сопоставлять взаимодействия и продажи.
Стратегия обновления и архитектурные качества: поддержка инкрементальных загрузок, управление изменениями схемы, обработка ошибок в процессе загрузки и автоматическое повторение без потери данных.

Пример DDL для возможного слоя фактов (схема упрощена для наглядности):

-- Пример упрощённой схемы факт-конверсий и связанных размерностей
CREATE TABLE dim_time (...);           -- как ранее показано
CREATE TABLE dim_campaign (...);
CREATE TABLE dim_ad (...);
CREATE TABLE dim_user (...);
CREATE TABLE dim_product (...);

CREATE TABLE fact_conversions (
  conversion_id STRING PRIMARY KEY,
  time_id DATE REFERENCES dim_time(time_id),
  user_id STRING REFERENCES dim_user(user_id),
  order_id STRING,
  campaign_id STRING REFERENCES dim_campaign(campaign_id),
  ad_id STRING REFERENCES dim_ad(ad_id),
  product_id STRING REFERENCES dim_product(product_id),
  channel STRING,
  revenue DECIMAL(18,2),
  units INT,
  attribution_model STRING,
  attribution_weight DECIMAL(5,4),
  conversion_time TIMESTAMP
);

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

Инструменты, процессы и управление качеством данных

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

Управление качеством данных: определение критических правил верификации данных (проверки целостности между фактами и размерностями, контроль пропусков и аномалий), применение тестов к данным при каждом развёртывании моделей.
Оркестрация процессов: планирование загрузок и трансформаций, мониторинг исполнения и автоматическое уведомление об ошибках.
Контроль версий схемы и данных: возможность отката изменений и сохранения версий размерностей, чтобы позволить аудит и воспроизводимость.
Управление доступом и соблюдение регуляторных ограничений: разграничение прав доступа к данным, особое внимание к чувствительным данным пользователей.

Key takeaways

Формирование единого источника данных для анализа конверсий требует аккуратно спроектированной звездной схемы с конформированными размерностями и фактами по кликам, показам и конверсиям.
Модели атрибуции должны быть гибкими: хранение информации о разных подходах (LAST_CLICK, FIRST_CLICK, LINEAR, TIME_DECAY) и поддержка сложных расчетов по окнам времени.
Интеграция источников данных требует надёжной идентификации пользователя и сессий, нормализации полей, проверки качества и прозрачной политики обновления данных.
Инструменты для реализации процессов (dbt, Apache Airflow) поддерживают повторяемость и управляемость, что критично для масштабирования и аудита.
Важно обеспечить возможность агрегаций и детального анализа, а также сохранение истории изменений размерностей кампаний и креативов.
В рамках корпоративной практики необходимо внедрить процессы контроля качества, управления данными и документацию контрактов данных.
Аналитика конверсий должна опираться на объективные KPI: CTR, CVR, CPA/ROAS, LTV и путь к покупке, с учётом эффективности каждого канала и кампании.

FAQ

Что такое конверсия в рамках анализа рекламного трафика на маркетплейсе?

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

Какие данные следует включать в DWH для атрибуции?

Нужно объединить данные по времени (dim_time), источникам кампаний (dim_campaign), объявлениям и креативам (dim_ad), пользователям (dim_user), товарам (dim_product) и сами факты конверсий (fact_conversions), кликов (fact_clicks) и показов (fact_impressions). Эти данные позволяют рассчитывать KPI по каналам, кампании и продукту.

Как выбрать модель атрибуции?

Выбор модели зависит от бизнес-целей и специфики ваших каналов. LAST_CLICK хорошо отражает прямой вклад последнего контакта, FIRST_CLICK - привлечения, LINEAR - равномерное распределение, TIME_DECAY - акцент на поздних касаниях. Рекомендуется начинать с дефолтной модели (например, LAST_CLICK) и затем рассмотреть гибридные сценарии для разных каналов или сегментов.

Как обеспечить качество данных при интеграции нескольких источников?

Необходимо определить единый набор идентификаторов (user_id, session_id), нормализовать поля, проверять полноту данных и дубликаты, внедрить контроль целостности между фактами и размерностями, а также вести журнал изменений и версий размерностей.

Какие инструменты применяются для реализации процесса загрузки и трансформации?

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

Как обеспечить согласованность между источниками и бизнес-потребностями?

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

Какие преимущества даёт единая схема данных для аналитики конверсий?

Упрощается построение KPI, сравнение каналов и кампаний, ускоряется ответы на вопросы бизнес-аналитиков и маркетинга, появляется возможность внедрять продвинутые модели атрибуции и оптимизации бюджета, а также проводить аудит и транзакционные проверки в рамках централизованного хранилища.

Как автоматизировать обновления и адаптацию к новым источникам?

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

Какие KPI особенно важны для анализа конверсий в контексте маркетплейса?

CTR (кликрейт), CVR (конверсия кликов в покупки), CPA (cost per acquisition), ROAS (возврат на рекламные затраты), LTV/CAC (пожизненная ценность клиента/стоимость привлечения), а также путь к покупке и показатели по каналам и кампаниям, включая анализ по сегментам (география, устройство, категория товара).

Какие подходы к приватности и соответствию требованиям стоит учитывать?

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

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

← Предыдущая статья

Маркетинг и реклама - Подготовка витрины данных для анализа эффективности ключевых слов

Следующая статья →

Маркетинг и реклама - Интеграция данных маркетинговых акций и скидок для анализа их влияния на продажи