Инженерный блог

O2O (Offline-to-Online) аналитика: Как связать клики из Google Ads с реальными визитами в точки продаж в Алматы через BigQuery

08.08.2026
Шарафутдинов Р.

Типичная картина в алматинском ритейле: маркетолог заливает миллионы тенге в Google Ads, крутит смарт-кампании, Performance Max пыхтит, CTR пробивает потолок. Пользователь гуглит "купить айфон 15 про макс алматы", кликает по рекламе, переходит на сайт... и уходит. В дашборде GA4 — отказ, конверсии нет, ROAS плачет в сторонке, финдиректор готовит приказ об увольнении маркетолога.

А что в реальности? В реальности этот самый пользователь, посмотрев характеристики и убедившись, что товар в наличии в магазине на Абая-Правды (или в Меге), закрывает вкладку, садится в машину, приезжает в офлайн-точку и покупает телефон за наличку или через Kaspi QR. Магазин получил прибыль? Да. Реклама сработала? Еще как! Учла ли это аналитика? Нифига подобного.

Добро пожаловать в мир RoPO (Research online, Purchase offline) — главной головной боли омниканального ритейла. В этой статье мы будем собирать хардкорный O2O (Offline-to-Online) аналитический пайплайн, который свяжет клики из Google Ads с пробитием чеков на офлайн-кассах. И делать мы это будем элегантно, через Google BigQuery, с щепоткой SQL-магии и соблюдением закона о ПДн (мы же не хотим повторения прошлого кейса, верно?).

Архитектура O2O: Как подружить онлайн с офлайном

Для того чтобы магия случилась, нам нужен некий идентификатор, который будет склеивать сессию пользователя в браузере с его физическим визитом в магазин. Назовем его Святым Граалем Аналитики (Join Key).

В реалиях Казахстана лучшими Join Keys являются:

  • Номер телефона: Оставляется на сайте (регистрация, программа лояльности) и диктуется на кассе для начисления бонусов.
  • Email: Аналогично, но используется реже.
  • ID карты лояльности: Штрихкод в мобильном приложении, который юзер сканирует на кассе.
  • Промокоды: Уникальный код, показанный на сайте, который юзер называет кассиру.

Наш пайплайн будет выглядеть так:

  1. Слой сбора онлайна (Google Analytics 4): Собирает клики, UTM-метки, client_id, gclid (Google Click ID) и, если юзер авторизовался, отправляет хэшированный user_id (телефон).
  2. Слой сбора офлайна (CRM / 1C / ERP): Фиксирует транзакцию на кассе, сумму чека, артикулы и хэшированный номер телефона покупателя (через карту лояльности).
  3. Хранилище (Google BigQuery): Место, куда стекаются данные из онлайна (нативный экспорт GA4 -> BQ) и из офлайна (наш кастомный скрипт или сервис вроде Airbyte/Fivetran).
  4. Слой трансформации (SQL / dbt): Там, где происходит магия JOIN и атрибуции.
  5. Активация (Google Ads): Отправка офлайн-конверсий обратно в Google Ads через Offline Conversion Tracking (OCT) для обучения алгоритмов.

Шаг 1: Настраиваем онлайн (GA4 + GTM)

Первое правило бойцовского клуба O2O: никогда не передавай сырые номера телефонов в GA4. Во-первых, это нарушение ToS (Terms of Service) Гугла, за что прилетит бан аккаунта. Во-вторых, это нарушение Закона РК о ПДн.

Поэтому на фронтенде (или лучше в Server-Side GTM) мы хэшируем телефон в SHA-256. Когда пользователь логинится на сайте, мы отправляем событие login с параметром user_id.

// Пример хэширования на клиенте (хотя лучше делать на бэкенде)
async function sha256(message) {
    const msgBuffer = new TextEncoder().encode(message);                    
    const hashBuffer = await crypto.subtle.digest('SHA-256', msgBuffer);
    const hashArray = Array.from(new Uint8Array(hashBuffer));
    const hashHex = hashArray.map(b => b.toString(16).padStart(2, '0')).join('');
    return hashHex;
}

// При успешной авторизации
const rawPhone = '+77011234567'; // Телефон из формы
sha256(rawPhone).then(hashedPhone => {
    gtag('config', 'G-XXXXXXX', {
      'user_id': hashedPhone // Теперь это анонимный хэш
    });
    gtag('event', 'login', { method: 'phone' });
});

Теперь в ежедневном экспорте GA4 в BigQuery у нас появится колонка user_id, по которой мы сможем идентифицировать этого пользователя.

Шаг 2: Выгружаем офлайн из 1С/CRM

Офлайн-магазины обычно сидят на 1С, RetailCRM или самописных ERP. Наша задача — выгружать данные о продажах каждый день (или в real-time) в BigQuery.

Схема таблицы offline_sales в BigQuery должна быть примерно такой:

  • transaction_id (STRING) - номер чека.
  • transaction_date (TIMESTAMP) - время пробития чека.
  • store_location (STRING) - адрес магазина (напр. "Алматы_Мега").
  • revenue (FLOAT64) - сумма покупки.
  • hashed_phone (STRING) - тот самый SHA-256 хэш телефона покупателя.

Важно: бэкенд должен использовать тот же самый алгоритм и "соль" (salt) для хэширования телефона, что и сайт. Иначе хэши не совпадут, и JOIN вернет пустоту, а аналитик уйдет в депрессию.

Рекомендуем к прочтению

Скрытая угроза: Почему GA4 «не видит» половину оплат через Kaspi и как мы починили это с помощью BigQuery

06.08.2026

Истинный ROAS: Онлайн vs O2O (Сквозная аналитика)

Сравнение показателей рентабельности рекламы до и после внедрения интеграции касс с BigQuery.

Шаг 3: SQL Магия в BigQuery (Склеиваем данные)

Вот мы и добрались до мяса. У нас есть таблица events_* от GA4 и таблица offline_sales от CRM. Наша цель — найти пользователей, которые кликали на рекламу (имеют сессии с utm_source=google / cpc), а затем купили в офлайне.

Пишем SQL-запрос, который сделает нас героями компании:

WITH online_sessions AS (
  -- Достаем сессии с Google Ads, где юзер залогинился (есть user_id)
  SELECT 
    user_id AS hashed_phone,
    CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS session_id,
    TIMESTAMP_MICROS(event_timestamp) AS session_time,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS utm_source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS utm_medium,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS utm_campaign
  FROM 
    `your-project.analytics_123456789.events_*`
  WHERE 
    user_id IS NOT NULL 
    AND event_name = 'session_start'
),

offline_transactions AS (
  -- Достаем наши офлайн транзакции
  SELECT 
    transaction_id,
    hashed_phone,
    revenue,
    transaction_date
  FROM 
    `your-project.crm_data.offline_sales`
)

-- Склеиваем!
SELECT 
  o.transaction_id,
  o.revenue,
  o.transaction_date,
  s.utm_campaign,
  s.session_time
FROM 
  offline_transactions o
JOIN 
  online_sessions s ON o.hashed_phone = s.hashed_phone
WHERE
  -- Покупка была ПОСЛЕ визита на сайт
  o.transaction_date > s.session_time
  -- Окно атрибуции (например, 7 дней)
  AND TIMESTAMP_DIFF(o.transaction_date, s.session_time, DAY) <= 7
  AND s.utm_source = 'google' 
  AND s.utm_medium = 'cpc'
-- Берем только последний клик перед покупкой (Last Non-Direct Click)
QUALIFY ROW_NUMBER() OVER(PARTITION BY o.transaction_id ORDER BY s.session_time DESC) = 1;

Что делает этот скрипт? Он берет офлайн-чеки, ищет по хэшу телефона этого же пользователя в GA4, проверяет, заходил ли он с рекламы Google Ads за последние 7 дней до покупки, и привязывает чек к конкретной рекламной кампании. Бум! Теперь вы знаете, что та самая кампания "Search_iPhone_15_Almaty" принесла не 0 тенге, а 5 000 000 тенге в офлайне!

Шаг 4: Активация. Отправляем конверсии обратно в Google Ads

Рассчитать ROAS в дашборде (Looker Studio) — это круто. Но чтобы алгоритмы Google Ads (особенно Performance Max) начали оптимизироваться под офлайн-покупки, им нужно эти данные «скормить».

Для этого используется Offline Conversion Tracking (OCT). Нам нужно выгрузить из BigQuery CSV-файл (или отправлять по API) со следующими полями:

  • GCLID (Google Click ID) — если мы сохраняли его при визите на сайт.
  • Conversion Name — имя действия-конверсии (например, "Offline Purchase").
  • Conversion Time — время чека на кассе (с таймзоной!).
  • Conversion Value — сумма чека.
  • Conversion Currency — KZT.

Если GCLID потерялся (юзер зашел с iOS, где ITP режет параметры), Google Ads поддерживает импорт конверсий через Enhanced Conversions for Leads. Вы можете отправлять импортом хэшированный email или телефон, и Google сам найдет в своих логах, на какую рекламу кликал этот человек. Это работает как черная магия, но результаты впечатляют.

Реалии Алматы: Интеграции с Kaspi и нюансы

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

Что делать, если юзер не хочет логиниться на сайте?

  • Промокоды на экране: Покажите на сайте баннер "Скажи на кассе кодовое слово APPLE и получи чехол в подарок". Кодовое слово генерируется уникальное для каждой сессии (или привязано к UTM). В 1С кассир вводит промокод, и вы точно знаете, с какой кампании пришел человек. Дешево, сердито, без BigQuery.
  • Сбор лидов через формы: Предлагайте скидку за оставленный номер телефона. Телефон уходит в CRM, а кука GA4 склеивается с user_id.
  • Оплата через Kaspi: В идеальном мире интеграция с банками позволяла бы получать хэши телефонов при оплате QR-кодом. На практике банки не отдают ПДн ритейлерам без явного согласия. Поэтому кассиру все равно придется просить: "Ваш номер телефона для начисления бонусов?". Чем лучше скрипт кассира — тем выше ваш Match Rate в BigQuery.

Корреляция: Трафик Google Ads и Чеки в магазинах

Как рост онлайн-кликов по брендовым и товарным запросам влияет на физические пробития на кассе (лаг 2-3 дня).

Архитектурные грабли, на которые вы обязательно наступите

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

  1. Таймзоны. GA4 экспортирует данные в UTC. Ваша 1С живет в Asia/Almaty (UTC+5). Если не делать DATETIME(timestamp, "Asia/Almaty") при склейке, вы потеряете конверсии, произошедшие ночью.
  2. Форматы номеров. Юзер на сайте ввел 87011234567, а кассир в 1С забил +77011234567. Хэши будут абсолютно разными! Обязательно нормализуйте данные перед хэшированием (убирайте плюсы, пробелы, приводите к формату 7701...).
  3. Окно атрибуции. Иногда покупка происходит через 30 дней после клика. Храните историю сессий долго. По умолчанию GA4 хранит данные 2 или 14 месяцев (зависит от настроек), но в BigQuery они лежат вечно (пока вы платите за storage).
  4. Возвраты товаров. Клиент купил айфон, вы отправили конверсию в Google Ads. На следующий день он вернул телефон. Обязательно настройте скрипт, который будет отправлять корректировки конверсий (Conversion Adjustments), иначе алгоритм будет думать, что привел крутого лида, и потратит еще больше бюджета на похожих отказников.

Вывод: Зачем все это нужно?

Настройка O2O-аналитики через BigQuery — это проект уровня Middle/Senior Data Engineer. Он требует интеграции фронтенда, бэкенда, CRM и рекламных кабинетов. Зачем так страдать?

Затем, что в нишах с высоким чеком (электроника, мебель, авто, недвижка) доля офлайн-продаж может достигать 70-90%. Если маркетолог опирается только на онлайн-конверсии (отправка формы, звонок), он видит лишь верхушку айсберга. Отключая "неэффективные" кампании, он может случайно убить кампании, которые генерили огромный трафик в магазины.

Связка GA4 + BigQuery + 1C дает вам рентгеновское зрение. Вы начинаете видеть реальный путь клиента. И когда в следующий раз финдиректор спросит "А куда ушли эти 10 миллионов тенге на Google Ads?", вы просто откроете дашборд в Looker Studio и покажете ему 50 миллионов выручки, пробитой на кассах. Mic drop.

Рустам Шарафутдинов

Рустам Шарафутдинов

Автор инженерного блога

Эксперт в области архитектуры Google Cloud и Senior Full-Stack разработчик с более чем 15-летним опытом. Специализируется на отказоустойчивых архитектурах, оптимизации высоконагруженных проектов и интеграции AI (Vertex AI).

Экспертность: GCP, Kubernetes, Микросервисы, React, Node.js

Комментарии (0)