Google BigQuery для маркетинговой аналитики: гайд по ROI и лидогенерации

Введение

Google BigQuery — это облачное хранилище данных, которое позволяет обрабатывать терабайты информации за секунды. Для маркетологов это инструмент, который объединяет разрозненные источники: CRM, рекламные кабинеты, SMM, SEO и аналитику сайта. В этой статье — конкретные кейсы и SQL-запросы для лидогенерации и расчета ROI.

1. Объединение данных из CRM и рекламы

Кейс: расчет стоимости лида по каждому каналу

Проблема: данные о лидах хранятся в CRM (например, AmoCRM или Bitrix24), а расходы — в Google Ads и Facebook Ads. В BigQuery вы можете соединить их и получить единую таблицу.

Пример SQL-запроса:

SELECT
  campaign_name,
  SUM(cost) AS total_cost,
  COUNT(DISTINCT lead_id) AS leads,
  SUM(cost) / COUNT(DISTINCT lead_id) AS cpl
FROM
  `project.dataset.ads_costs` AS ads
JOIN
  `project.dataset.crm_leads` AS crm
ON
  ads.campaign_id = crm.utm_campaign
GROUP BY
  campaign_name

Этот запрос покажет CPL (cost per lead) по каждой кампании. Вы можете сразу увидеть, какие кампании окупаются, а какие нет.

2. Анализ конверсии и воронки

Кейс: воронка от клика до покупки

Соберите события из Google Analytics 4 (GA4) в BigQuery и постройте воронку.

Пример запроса для шагов воронки:

SELECT
  step,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM (
  SELECT
    user_pseudo_id,
    CASE
      WHEN event_name = 'page_view' THEN '1. Посещение'
      WHEN event_name = 'add_to_cart' THEN '2. Добавление в корзину'
      WHEN event_name = 'purchase' THEN '3. Покупка'
    END AS step
  FROM `project.analytics_123.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20230101' AND '20230131'
)
GROUP BY step
ORDER BY step

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

3. Сегментация аудитории для SMM и контента

BigQuery позволяет сегментировать пользователей по поведению и создавать аудитории для ретаргетинга.

Пример: пользователи, которые посетили страницу «Цены», но не купили

SELECT
  user_pseudo_id
FROM `project.analytics_123.events_*`
WHERE event_name = 'page_view'
  AND page_title = 'Цены'
  AND user_pseudo_id NOT IN (
    SELECT user_pseudo_id
    FROM `project.analytics_123.events_*`
    WHERE event_name = 'purchase'
  )

Эту выборку можно экспортировать в Google Ads или Facebook для догона.

4. Расчет ROI рекламных кампаний

Кейс: ROI с учетом lifetime value (LTV)

Если в CRM хранятся данные о повторных покупках, можно рассчитать ROI за весь период.

SELECT
  campaign_name,
  SUM(revenue) - SUM(cost) AS profit,
  (SUM(revenue) - SUM(cost)) / SUM(cost) * 100 AS roi_percent
FROM (
  SELECT
    ads.campaign_name,
    ads.cost,
    crm.revenue
  FROM `project.dataset.ads_costs` ads
  LEFT JOIN `project.dataset.crm_orders` crm
    ON ads.client_id = crm.client_id
)
GROUP BY campaign_name

5. Прогнозирование лидогенерации

Используйте временные ряды в BigQuery ML для прогноза количества лидов на следующий месяц. Это поможет скорректировать бюджет на продвижение.

Пример обучения модели:

CREATE OR REPLACE MODEL `project.dataset.leads_forecast`
OPTIONS(model_type='ARIMA_PLUS', time_series_timestamp_col='date', time_series_data_col='leads') AS
SELECT
  date,
  COUNT(DISTINCT lead_id) AS leads
FROM `project.dataset.crm_leads`
GROUP BY date

Затем сделайте прогноз:

SELECT *
FROM ML.FORECAST(MODEL `project.dataset.leads_forecast`, STRUCT(30 AS horizon))

Практические рекомендации

• Экспортируйте данные из всех каналов: Google Ads, Facebook Ads, Яндекс.Директ, GA4, CRM, коллтрекинг. Используйте готовые коннекторы или загружайте через API.
• Создайте единую таблицу с utm-метками: это упростит объединение данных.
• Используйте Looker Studio: подключите BigQuery к Looker Studio для визуализации дашбордов.
• Автоматизируйте отчеты: настройте ежедневную выгрузку и отправку отчетов в Telegram или Slack.
• Не забывайте про cost-данные: без них невозможно посчитать ROI.

Заключение

Google BigQuery — мощный инструмент для маркетинговой аналитики, который позволяет объединить данные из CRM, SMM, SEO и рекламы. С его помощью вы сможете точно рассчитывать ROI, сегментировать аудиторию и прогнозировать лидогенерацию. Начните с простого экспорта данных из GA4 и рекламных кабинетов, а затем постепенно добавляйте CRM и коллтрекинг.