Google BigQuery для маркетинговой аналитики: гайд по ROI и лидогенерации
This article is currently available only in Russian.
Введение
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 и коллтрекинг.