Инженерлік блог

O2O (Offline-to-Online) аналитикасы: BigQuery арқылы Google Ads-тен келген кликтерді Алматыдағы сату нүктелеріне нақты келумен қалай байланыстыруға болады

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

Алматы ритейліндегі типтік көрініс: маркетолог Google Ads-ке миллиондаған теңге құяды, смарт-кампанияларды айналдырады, Performance Max терлейді, CTR төбені теседі. Пайдаланушы "алматы айфон 15 про макс сатып алу" деп іздейді, жарнаманы басады, сайтқа өтеді... және кетіп қалады. GA4 дашбордында — бас тарту, конверсия жоқ, ROAS бір шетте жылап отыр, қаржы директоры маркетологты жұмыстан шығару туралы бұйрық дайындайды.

Ал шын мәнінде ше? Шын мәнінде, сол пайдаланушы сипаттамаларды қарап, тауардың Абай-Правдадағы (немесе Мегадағы) дүкенде бар екеніне көз жеткізгеннен кейін, қойындыны жабады, көлігіне отырады, офлайн нүктеге келіп, телефонды қолма-қол ақшаға немесе Kaspi QR арқылы сатып алады. Дүкен пайда көрді ме? Иә. Жарнама жұмыс істеді ме? Әрине! Мұны аналитика ескерді ме? Ешқандай да.

Омниканалды ритейлдің басты бас ауруы — RoPO (Research online, Purchase offline) әлеміне қош келдіңіз. Бұл мақалада біз Google Ads-тен келген кликтерді офлайн-кассаларда чектерді ұрумен байланыстыратын хардкорлы O2O (Offline-to-Online) аналитикалық пайплайнын жинайтын боламыз. Және біз мұны 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): Алгоритмдерді оқыту үшін Offline Conversion Tracking (OCT) арқылы офлайн-конверсияларды кері Google Ads-ке жіберу.

1-қадам: Онлайнды баптаймыз (GA4 + GTM)

O2O жекпе-жек клубының бірінші ережесі: шикі телефон нөмірлерін ешқашан GA4-ке жібермеңіз. Біріншіден, бұл Google-дің ToS (Terms of Service) бұзу болып табылады, ол үшін аккаунт бұғатталады. Екіншіден, бұл ҚР ЖД туралы заңын бұзу.

Сондықтан фронтендте (немесе жақсырақ Server-Side GTM-де) біз телефонды SHA-256-да хэштейміз. Пайдаланушы сайтқа кірген кезде біз user_id параметрімен login оқиғасын жібереміз.

// Клиентте хэштеу мысалы (бірақ бэкендте жасаған дұрыс)
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-ге жүктеп алу.

BigQuery-дегі offline_sales кестесінің схемасы шамамен мынадай болуы керек:

  • transaction_id (STRING) - чек нөмірі.
  • transaction_date (TIMESTAMP) - чек ұрылған уақыт.
  • store_location (STRING) - дүкен мекенжайы (мыс. "Алматы_Мега").
  • revenue (FLOAT64) - сатып алу сомасы.
  • hashed_phone (STRING) - сатып алушының телефонының дәл сол SHA-256 хэші.

Маңызды: бэкенд телефонды хэштеу үшін сайт сияқты алгоритмді және "тұзды" (salt) пайдалануы керек. Әйтпесе хэштер сәйкес келмейді, және JOIN бостық қайтарады, ал аналитик депрессияға түседі.

Оқуға ұсынамыз

Қазақша FinOps: логистикалық компания үшін бұлт шығындарын қалай 40%-ға оңтайландырдық

04.08.2026

Шынайы ROAS: Онлайн vs O2O (Өтпелі аналитика)

Кассаларды BigQuery-мен интеграциялағанға дейінгі және кейінгі жарнаманың рентабельділік көрсеткіштерін салыстыру.

3-қадам: BigQuery-дегі SQL Сиқыры (Деректерді желімдейміз)

Міне, біз етке де жеттік. Бізде GA4-тен events_* кестесі және CRM-нен offline_sales кестесі бар. Біздің мақсатымыз — жарнаманы басқан (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-тен іздейді, оның сатып алуға дейінгі соңғы 7 күн ішінде Google Ads жарнамасынан кіргенін тексереді және чекті нақты жарнамалық науқанға байланыстырады. Бум! Енді сіз сол "Search_iPhone_15_Almaty" науқаны офлайнда 0 теңге емес, 5 000 000 теңге әкелгенін білесіз!

4-қадам: Активтендіру. Конверсияларды Google Ads-ке кері жібереміз

Дашбордта (Looker Studio) ROAS есептеу — бұл керемет. Бірақ 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.

Алматының шынайылығы: Kaspi-мен интеграция және нюанстар

Теорияда бәрі әдемі естіледі, бірақ іс жүзінде Қазақстанда сіз қатаң шындықпен бетпе-бет келесіз. Басты қиындық — идентификация пайызы. Онлайн мен офлайнды желімдеу үшін юзер сайтта авторизациядан өтіп, кассада бонустық картаны пайдалануы керек.

Егер юзер сайтта логин жасағысы келмесе не істеу керек?

  • Экрандағы промокодтар: Сайтта "Кассада APPLE құпия сөзін айт та, чехол сыйлыққа ал" баннерін көрсетіңіз. Құпия сөз әр сессия үшін бірегей жасалады (немесе UTM-ге байланған). 1С-те кассир промокодты енгізеді, және сіз адамның қай науқаннан келгенін нақты білесіз. Арзан әрі BigQuery-сіз.
  • Формалар арқылы лидтер жинау: Қалдырылған телефон нөмірі үшін жеңілдік ұсыныңыз. Телефон CRM-ге кетеді, ал GA4 кукасы user_id-мен желімделеді.

Корреляция: Google Ads трафигі және дүкендердегі чектер

Брендтік және тауарлық сұраныстар бойынша онлайн-кликтердің өсуі физикалық кассадағы ұруларға қалай әсер етеді (лаг 2-3 күн).

Сіз міндетті түрде басатын архитектуралық тырмалар

Егер сіз бәрі бірінші реттен жұмыс істейді деп ойласаңыз, онда сіз оптимистсіз. Міне, бірінші айда-ақ пайплайныңызды бұзатын заттар тізімі:

  1. Таймзоналар. GA4 деректерді UTC-те экспорттайды. Сіздің 1С-іңіз Asia/Almaty-да (UTC+5) өмір сүреді. Егер желімдеу кезінде DATETIME(timestamp, "Asia/Almaty") жасамасаңыз, түнде болған конверсияларды жоғалтасыз.
  2. Нөмір форматтары. Юзер сайтта 87011234567 енгізді, ал кассир 1С-те +77011234567 деп ұрды. Хэштер мүлдем басқа болады! Хэштеу алдында деректерді міндетті түрде қалыпқа келтіріңіз (плюстерді, бос орындарды алып тастаңыз, 7701... форматына келтіріңіз).
  3. Атрибуция терезесі. Кейде сатып алу кликтен кейін 30 күн өткен соң болады. Сессия тарихын ұзақ сақтаңыз.

Қорытынды: Бұның бәрі не үшін қажет?

BigQuery арқылы O2O-аналитиканы баптау — бұл Middle/Senior Data Engineer деңгейіндегі жоба. Ол фронтендті, бэкендті, CRM және жарнама кабинеттерін интеграциялауды талап етеді. Неге олай азапталу керек?

Өйткені чегі жоғары тауашаларда (электроника, жиһаз, авто, жылжымайтын мүлік) офлайн-сатылымдардың үлесі 70-90%-ға жетуі мүмкін. Егер маркетолог тек онлайн-конверсияларға (форма жіберу, қоңырау) сүйенсе, ол мұзтаудың шыңын ғана көреді. "Тиімсіз" науқандарды өшіре отырып, ол байқаусызда дүкендерге үлкен трафик әкелген науқандарды өлтіріп алуы мүмкін.

GA4 + BigQuery + 1C байламы сізге рентгендік көру қабілетін береді. Сіз клиенттің нақты жолын көре бастайсыз. Ал келесі жолы қаржы директоры "Google Ads-ке кеткен мына 10 миллион теңге қайда кетті?" деп сұрағанда, сіз жай ғана Looker Studio-дағы дашбордты ашып, оған кассаларда ұрылған 50 миллион түсімді көрсетесіз. Mic drop.

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

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

Инженерлік блог авторы

Google Cloud архитектурасы саласындағы сарапшы және 15 жылдан астам тәжірибесі бар Senior Full-Stack әзірлеушісі. Ақауға төзімді архитектураларға, жоғары жүктемелі жобаларды оңтайландыруға және AI (Vertex AI) интеграциясына маманданған.

Сараптама: GCP, Kubernetes, Микросервистер, React, Node.js

Пікірлер (0)