+7 (495) 801-60-42

ClickHouse для маркетинговой и продуктовой аналитики: как начать

Маркетинговые и продакт‑команды всё чаще выбирают ClickHouse, когда нужен быстрый событийный DWH для дешёвых и мгновенных запросов по миллиардам записей. Правильно спроектированная clickhouse аналитика маркетинг закрывает задачи когорт, retention, воронок, LTV и сквозной атрибуции без сложных кубов и OLAP‑монстров.

ClickHouse аналитика маркетинг: зачем бизнесу

  • Высокая скорость на агрегатах по событиям (мероприятия трафика, клики, просмотры, add_to_cart, purchase).
  • Дешёвое хранение и сжатие: экономия против классических реляционных СУБД на event data.
  • Нативная потоковая загрузка и агрегации через материализованные представления.
  • Гибкая схема под продуктовые события и маркетинговые источники.

Если у вас растущий трафик и продакт‑метрики считаются мучительно долго, ClickHouse — удобная база для событийной модели, сквозной аналитики DWH и дешёвых дашбордов.

Установка ClickHouse и базовая инфраструктура

Для старта подойдёт один инстанс (test/prod) на Linux, далее — шардирование и репликация. Минимум практики:

  • Ставьте стабильную версию из официального репозитория; включите zstd‑сжатие и лог‑ротацию.
  • Отдельные диски под data и logs; для горячих таблиц — NVMe.
  • Сразу планируйте бэкапы (filesystem snapshot или clickhouse-backup) и мониторинг (ClickHouse Keeper/ZooKeeper метрики, system.metrics в Prometheus).
  • Доступ по TLS/mtls и IP allowlist; секреты в Vault/KMS.

Инфраструктурные вопросы — зона ответственности DevOps. Если нужен аудит или помощь с продом, подключайте нашу услугу DevOps и инфраструктура.

Схема данных для маркетинга и продукта

Цель — единая событийная модель (event data), которая связывает пользователя, устройство, сессию, кампанию и платеж.

Рекомендуемые сущности:

  • events (фактология): event_time, event_name, user_id, session_id, device_id, source/medium/campaign, geo, page, revenue, currency, properties (JSON).
  • users (словарь): user_id, user_key (email/phone hash), reg_date, first_source, атрибуты профиля.
  • sessions: session_id, user_id, utm_*, landing_page, session_start/end, device, is_paid_traffic.
  • orders/transactions: order_id, user_id, amount, status, attribution_window.
  • channels/dim_campaigns: нормализованные источники, UTM‑параметры, cost (если импортируете расходы).

Практики:

  • Дедупликация по (event_id, user_id, event_time) с коллизией по окну 5–10 минут.
  • Храните currency и rate на дату платежа; не смешивайте деноминации.
  • Для «схема данных маркетинг» держите строгие типы: FixedString для campaign_id, Decimal для revenue.
  • Большие таблицы — движок MergeTree (или ReplicatedMergeTree), сортировка по (event_date, user_id, event_name) и партиции по event_date (месяц/неделя).

ETL в ClickHouse: от трекинга к витринам

Варианты приёма событий:

  • Стрим: Kafka → ClickHouse (Kafka engine + материализованные представления). Подходит для clickhouse event data в реальном времени.
  • Коннекторы: Airbyte/Singer для загрузки рекламных кабинетов, CRM, платежей.
  • CDC: Debezium/Maxwell из OLTP (заказы, биллинг) в Kafka.
  • Batch: S3/MinIO с Parquet/CSV и внешние таблицы для массовой догрузки.

Практики ETL:

  • На входе сырые events_raw; далее MV нормализует в events (очистка, UTM‑парсинг, user_id резолвинг).
  • Ещё одно MV формирует агрегаты (день/канал/страна, cohort_id, first_seen_date) для дешёвых отчётов.
  • Расходы по кампаниям приводите к единой таксономии источников (utm_source → channel).
  • Для GDPR/152‑ФЗ персональные поля храните хэшами; оригиналы — в защищённом хранилище.

Нужна проработка пайплайна под ваши источники? Команда LightsOn поможет спроектировать DWH и ETL под задачи бизнеса — смотрите услугу Аналитика и стратегия.

Материализованные представления: ускоряем отчёты без боли

Материализованные представления (MV) в ClickHouse автоматически агрегируют потоки в целевые витрины. Зачем они маркетингу:

  • Дешёвые отчёты по дню/каналу/стране без сканирования всего факта.
  • Пре‑подсчёт retention, когорты, first_touch/last_touch атрибуции в отдельных таблицах.
  • Индикативные метрики в реальном времени (active_users, revenue_today, ad_cost_today).

Практики:

  • MV настраивайте поверх движка Kafka или сырых MergeTree таблиц.
  • Следите за версионированием логики (schema evolution): добавление столбцов через TTL/ALTER, обратная совместимость.
  • Держите отдельно fast‑витрины (последние 90 дней) и cold‑витрины (архивные периоды).

Когорты и удержание: как считать в ClickHouse

Ключевая продуктовая задача — «когорты в clickhouse» и «retention sql запрос». Подход к расчёту:

1) Определите триггер первой активности (cohort_date) — например, первый event_name='signup' или первый платеж.

2) Создайте витрину users_cohort с полями user_id, cohort_date, first_source.

3) Соберите события активности по дням/неделям после коорты (diff_days = datediff(event_date, cohort_date)).

4) Постройте таблицу retention, где на каждую cohort_date и diff_days есть active_users и retention_rate = active_users / cohort_size.

Пример логики запроса (упрощённо, без кода):

  • Шаг 1: выбрать для каждого user_id минимальный event_time регистрации → cohort_date.
  • Шаг 2: для всех событий после cohort_date посчитать активных уникальных пользователей по дню diff_days.
  • Шаг 3: джойн с размером когорты и расчёт retention_rate, churn_rate, rolling‑retention (максимум активности до N дня).

Расширения:

  • Сегментация по first_source/utm_campaign/стране/платформе.
  • Product‑retention: активность по ключевому действию (например, played_level, opened_app, placed_order).
  • Revenue‑retention: доля когорты, давшая выручку в день N.

Сквозная аналитика DWH и атрибуция в ClickHouse

Склейте ad_costs, web/app события, CRM сделки и платежи — и получите сквозную картину в одном DWH.

  • Импортируйте расходы (Google Ads, VK, myTarget, Яндекс Директ) в таблицу costs с нормализацией campaign/ad_group/ad_id.
  • Для атрибуции используйте окна по session_start: first_touch (минимальный), last_touch (перед конверсией), position‑based (40/20/40), data‑driven — на старте достаточно rule‑based.
  • Храните conversion_time и атрибуционные окна (например, 7/30 дней) отдельными полями, чтобы не пересчитывать историю.

Подробнее о принципах — в нашей статье «Сквозная аналитика: что это, как работает и кому нужна».

Визуализация в Looker Studio и продуктовые дашборды

ClickHouse можно подключить к Looker Studio через коннекторы/прокси. Рекомендации:

  • Для «визуализация в looker studio» давайте не raw, а агрегированные витрины: daily_marketing, funnel_steps, cohorts_retention, ltv_by_cohort.
  • Пропорции и валюты приводите в витринах, а не в графиках.
  • Для скоростных графиков держите витрины с ограничением периода (например, последние 180 дней).
  • Чётко именуйте поля: date, channel, campaign, sessions, users, cr, cac, revenue, romi.

Если нужен набор готовых дашбордов под ваш стек, подключайте услугу Маркетинг и рост — соберём метрики и отчёты под ключевые цели.

Производительность, стоимость, безопасность: что важно знать

  • Сортировка и партиции: партицируйте по месяцу, сортируйте по (date, user_id, event_name) или (date, source, user_id) — меньше чтений.
  • Индексы: используйте data‑skipping (minmax, bloom) для часто фильтруемых полей (campaign_id, user_id).
  • TTL и холодное хранение: переносите старые партиции на дешёвые диски/облака.
  • Консистентность: ReplicatedMergeTree с quorum‑записями; для SLA — рассчитайте RPO/RTO и протестируйте восстановление.
  • Безопасность: row‑level security для чувствительных сегментов, аудит логинов, шифрование at‑rest и in‑transit.

Больше об инфраструктуре для данных — см. «Kubernetes: что это и когда он нужен проекту» и «Обслуживание серверов для веб‑приложений: что это и зачем бизнесу».

Пошаговый план запуска за 2–4 недели

  1. Цели и метрики: список KPI и продуктов/каналов, нужные разрезы и период хранения.
  2. Схема событий: договориться о номенклатуре event_name и обязательных свойствах (user_id, session_id, utm_*).
  3. Установка ClickHouse и мониторинг, настройка бэкапов.
  4. Источники: трекинг веб/мобайл, рекламные кабинеты, CRM, платежи.
  5. ETL: загрузка сырых данных, нормализация, справочники каналов.
  6. Материализованные представления: витрины для маркетинга, когорт, retention, воронок.
  7. Проверка качества: дедупликация, покрытие событий, согласование сумм с первичкой.
  8. Дашборды в Looker Studio: маркетинг, юзеркейсы продукта, LTV/CAC/ROMI.
  9. Документация: словарь метрик, SLA обновления, версия схем.
  10. Передача и обучение команды.

Чек‑лист: готовы ли вы к ClickHouse

  • Есть перечень ключевых событий и их свойства.
  • Определён user_id и механика идентификации между устройствами.
  • Зафиксирован маппинг UTM → канал/источник.
  • Источники расходов по рекламе доступны для импорта.
  • Выбран способ загрузки: стрим через Kafka или batch через S3/коннектор.
  • Настроены бэкапы и мониторинг ClickHouse.
  • Созданы материализованные витрины под основные отчёты.
  • Есть тестовые дашборды и сценарии сверки цифр.

Итог

ClickHouse позволяет быстро собрать событийный DWH для маркетинга и продукта: единая схема, надёжный ETL в clickhouse, материализованные представления для ускорения, когорты и retention sql запрос для продуктовых решений, плюс наглядные отчёты в Looker Studio. Если хотите ускорить внедрение и снизить риски, мы поможем со стратегией данных, пайплайнами и визуализацией — смотрите Аналитика и стратегия и Маркетинг и рост. А инфраструктурные вопросы закроем через DevOps и инфраструктура.

Другие полезные статьи

Делимся экспертизой, разбираем кейсы и рассказываем, как превращать идеи в работающие digital-продукты