Наскрізна аналітика: 5 причин, чому витрати не зводяться з лідами
Наскрізна аналітика рідко ламається там, де очікують. Кожне джерело окремо показує правильні цифри, але щойно їх зводять разом, CPL по кампаніях порожній, а фільтр по країні «з'їдає» витрати.
Ця стаття — розбір реального проєкту. Клієнт продає обладнання у B2B і B2C, працює з кількома брендами й кількома ринками, рекламується в Meta і Google Ads. Звіт на старті був побудований так:
Витрати: GA4 → Google Sheets (звіт GA4 через розширення, окремі аркуші на кожен рекламний кабінет, Apps Script зводить їх в один) → Data Studio.
Ліди й продажі: CRM (KeyCRM) і облікова система (BAS/BAF) вже зведені в MySQL одним SQL-запитом. Продаж прив'язується до ліда за номером замовлення в межах кількох тижнів від дати оплати.
Звіт: Data Studio (до недавнього часу — Looker Studio). Витрати й ліди поєднані блендом.
Окремо все сходилося: витрати збігалися з рекламними кабінетами, ліди — з CRM, продажі — з обліком. Разом вони не сходилися, і причин було кілька. Нижче — кожна з них і що з нею робити.
1. Однакова назва поля ще не означає однаковий зміст
У бленді витрати й ліди з'єднувалися за шістьма полями: кампанія, джерело, бренд, продукт, регіон, сегмент. Назви полів з обох боків однакові. Але частина з них рахувалася на різних даних.
Поле | У витратах | У лідах і продажах |
Регіон | з рекламного кабінету і назви кампанії: куди таргетувались | з воронки CRM: куди лід реально потрапив |
Сегмент | з назви кампанії (b2b, partner…) | з воронки CRM (B2B- чи B2C-воронка) |
Бренд | з рекламного кабінету | з воронки CRM |
Обидва варіанти правильні, просто відповідають на різні питання. Лід із B2B-кампанії, якого менеджер переніс у B2C-воронку, має сегмент B2B у витратах і B2C у CRM. Для аналізу це нормально. Для з'єднання це вирок: рядки не зведуться.
Правило. Ключем з'єднання може бути тільки поле, яке з обох боків означає одне й те саме і рахується з тих самих вихідних даних. У нашому випадку це кампанія і джерело. Регіон, бренд і сегмент можуть мати різну логіку, але тоді вони не беруть участі в з'єднанні: кожен рядок просто несе своє значення.
2. UTM-шаблон «з'їхав» на рівень нижче
Це була головна причина. Перевірка показала, що за місяць жодна кампанія не мала одночасно і лідів, і витрат. Хоча розмітка стояла на всіх оголошеннях, і в CRM усі поля UTM були заповнені.
Відповідь знайшлася, коли ми подивилися не на те, чи заповнені мітки, а на те, що в них лежить. З певного моменту UTM-шаблон у Meta змінили:
utm_medium — назва кампанії;
utm_campaign — назва групи оголошень;
utm_content — назва оголошення;
utm_term — ID групи.
А витрати в GA4 (а отже й у Google-таблиці) лежать на рівні кампанії. Звіт роками порівнював групи оголошень з кампаніями, і вони не могли збігтися.
Випадок не поодинокий. Ось що ще траплялося в тому самому проєкті:
Google Ads з автотегуванням передає в CRM не назву кампанії, а її числовий ID. У GA4 при цьому лежить назва. Щоб зводити такі дані, у вивантаження витрат треба додати ID кампанії.
Биті мітки. В окремих оголошеннях назва кампанії потрапила в utm_source, а в utm_campaign не було нічого.
Старий і новий шаблон одночасно. До зміни шаблону в utm_medium було загальне значення (paid), після — назва кампанії. Правило має враховувати обидва періоди, інакше «зламається» історія.
Практика. Перед побудовою звіту випишіть реальні приклади UTM з CRM по кожному каналу й кожному періоду та зіставте їх з назвами у вивантаженні витрат. Півгодини такої звірки заощаджують дні пошуку «бага» в звіті.
3. Нормалізація назв: пробіли, плюси, %20 і регістр
Одна й та сама кампанія може прийти з різних систем у різному вигляді:
Звідки | Як виглядає назва |
Рекламний кабінет | Lead quiz panel 7.07 (business) |
UTM у CRM | Lead+quiz+panel+7.07+(business) або Lead%20quiz%20panel… |
GA4 | Lead quiz panel 7.07 (business), іноді з пробілом у кінці |
Для людини це одна кампанія, для з'єднання в Data Studio — три різні рядки. До того ж з'єднання в Data Studio чутливе до регістру: Lead_Quiz і lead_quiz не зведуться.
У проєкті була спроба це виправити: у Data Studio для витрат додали поле, яке замінює пробіли на «+». Але аналогічної нормалізації з боку CRM не було, тому проблема лише змістилася.
Рішення — одна функція нормалізації, однакова для всіх джерел:
прибрати пробіли на початку й у кінці;
замінити %20 на пробіл;
звести будь-яку послідовність пробілів і плюсів до одного «+».
У MySQL це виглядає так:
REGEXP_REPLACE(REPLACE(TRIM(utm_campaign), '%20', ' '), '[ +]+', '+')А в Data Studio так:
REGEXP_REPLACE(REPLACE(TRIM(utm_campaign), "%20", " "), "[ +]+", "+")Зверху цього ми додали окремий технічний ключ: ту саму нормалізовану назву в нижньому регістрі. На ньому зручно групувати і визначати продукт, а у звіті показувати назву в оригінальному регістрі.
4. Бленди в Data Studio: обмеження, про які варто знати заздалегідь
Бленд — зручний спосіб поєднати два джерела без SQL. Але для наскрізної аналітики в нього є кілька обмежень, які проявляються не одразу.
Full outer join і фільтри
Щоб бачити і кампанії з витратами без лідів, і ліди без витрат, у бленді обирають Full outer join. Проблема в тому, що поле-ключ після з'єднання бере значення з лівої таблиці. У рядків, які є тільки справа, там NULL.
Умовний приклад: витрати на польську кампанію є, а лідів з неї за період немає.
Регіон (ліди) | Регіон (витрати) | Ліди | Витрати | Фільтр «Регіон = Польща» |
Польща | Польща | 41 | 520 | залишається |
null | Польща | 0 | 247 | зникає |
Польща | null | 12 | 0 | залишається |
Контрол фільтра прив'язаний до поля однієї сторони. Тому обираючи країну, ви втрачаєте частину витрат, а в списку значень фільтра з'являється загадковий пункт «null».
Фільтр «Регіон» у бленді: пункт «null» — це витрати, для яких не знайшлося пари серед лідів.
Створити поле, яке брало б значення з тієї сторони, де воно є (аналог COALESCE після з'єднання), можна лише на рівні окремого графіка. У контролах такі поля використати не можна.
Чим більше ключів, тим менше збігів
У бленді було шість ключів. Щоб рядки зійшлися, мають збігтися всі шість. Досить, щоб розійшлося одне поле, наприклад сегмент (розділ 1) чи назва кампанії (розділи 2–3), і замість одного рядка виходять два: один з лідами, другий з витратами.
Налаштування бленду: Full outer join і шість ключів. Кожен з них має збігтися, щоб рядки звелися.
Дата поза ключами
Якщо дати немає серед ключів, кожна таблиця спершу агрегується за період звіту окремо, і тільки потім з'єднується. Для таблиць це працює. А в часових рядах поле «Дата» береться лише з однієї сторони, і витрати по днях розкласти не вдається.
Костилі на рівні графіка
Щоб в одній таблиці бачити і ліди, і витрати, у проєкті зробили поле з CASE: «якщо є назва кампанії з CRM — показати її, інакше назву з витрат». Воно працює, але тільки в тому графіку, де створене. Кожен новий графік потребує нового костиля, а фільтрувати за таким полем не можна.
5. Одна логіка в двох місцях неминуче розходиться
Продукт, джерело й регіон рахувалися двічі: у SQL-запиті для лідів і в розрахункових полях Data Studio для витрат. Правила писали за одним зразком, але з часом вони розійшлися. Ось три знахідки.
REGEXP_MATCH у Data Studio і REGEXP у MySQL працюють по-різному
Це найпідступніша розбіжність, бо регулярний вираз виглядає однаково:
(^|_)(ід|ид)(_|$)REGEXP у MySQL шукає входження шаблону будь-де в рядку. Для кампанії quiz_ід_test умова спрацює.
REGEXP_MATCH у Data Studio перевіряє, чи відповідає шаблону весь рядок. Для тієї самої кампанії умова не спрацює, і вона потрапить у «Нерозмічені».
Виправлення в Data Studio: використовувати REGEXP_CONTAINS або обгортати шаблон у .*….*. Ті правила, що вже були написані як .*слово.*, працювали коректно. А правила з якорями ^ і $ мовчки не спрацьовували.
Словники джерел розійшлися
utm_source | SQL (ліди) | Data Studio (витрати) |
meta | Інше | fb |
goodpromo | fb | Інше |
youtube_ads | Інше | |
порожньо | «Невідомо» | «Невизначено» |
Кожна розбіжність окремо дрібна. Але джерело було ключем з'єднання, тож кожна з них розривала рядки.
Регістр літер
У Data Studio регістр керується прапорцем (?i) у регулярці. У MySQL він залежить від колейшну колонки. Щоб не залежати від налаштувань БД, ми перед усіма перевірками зводимо назву до нижнього регістру.
Висновок. Кожне правило має жити в одному місці. Якщо дані для звіту готуються в SQL, то й продукт, і джерело, і кампанія мають рахуватися там один раз для всіх типів подій. Data Studio тоді лише відображає результат.
6. Рішення: UNION замість JOIN
Замість того щоб з'єднувати витрати з лідами, ми склали їх в одну таблицю як події різного типу: «Лід», «Продаж», «Витрата». Для цього витрати з Google-таблиці щодня синхронізуються в MySQL, а один SQL-запит об'єднує три потоки через UNION ALL.
Кожен рядок має однаковий набір колонок. Метрики, які не стосуються події, дорівнюють нулю: у рядку витрати нуль лідів, у рядку ліда нуль витрат.
Тип події | Дата | Кампанія | Регіон | Ліди | Продажі | Витрати |
Витрата | день показу | з GA4 | з кабінету і кампанії | 0 | 0 | сума |
Лід | дата створення | з UTM | з воронки CRM | к-ть | 0 | 0 |
Продаж | дата оплати | з UTM ліда | з воронки CRM | 0 | сума | 0 |
Що це дало:
Фільтри працюють для всього. Регіон, бренд і сегмент більше не є ключами з'єднання. Кожен рядок несе своє значення, і фільтр застосовується до витрат і лідів однаково.
Дата є в кожному рядку. Часові ряди по витратах і CPL будуються без хитрощів.
Одне місце для правил. Продукт, джерело і кампанія рахуються в одному блоці SQL для всіх типів подій.
Швидкість. Зведена таблиця готується заздалегідь, тож Data Studio не виконує важких обчислень під час відкриття звіту.
Наслідок такої структури треба тримати в голові: витрати не можна розкласти за менеджером чи воронкою, бо в рядках витрат цих полів немає. Це чесне обмеження, а не помилка: рекламний кабінет не знає, якому менеджеру дістанеться лід.
Метрики — як відношення сум
У такій таблиці CPL, ROAS і конверсії не можна рахувати по рядках і потім усереднювати. Ліди й витрати лежать у різних рядках, тож таке ділення дасть нулі й порожнечу. Метрика завжди рахується як відношення сум на рівні джерела даних у Data Studio:
Метрика | Формула в Data Studio |
CPL | SUM(Рекламні витрати) / SUM(К-ть лідів) |
CPQL | SUM(Рекламні витрати) / SUM(К-ть QL) |
CR в продаж | SUM(К-ть продажів) / SUM(К-ть лідів) |
ROAS | SUM(Сума продажів) / SUM(Рекламні витрати) |
ROMI | (SUM(Сума продажів) - SUM(Собівартість) - SUM(Рекламні витрати)) / SUM(Рекламні витрати) |
Результат
Після переходу загальні цифри лідів, QL і витрат точно збіглися із реальністю, тобто нічого не втратилося. Змінилося те, як зводяться показники. До виправлень за місяць жодна кампанія Meta не мала одночасно лідів і витрат. Після виправлень зводиться 183 з 185 лідів Meta і більшість витрат. Витрати без пари — це кампанії без лід-форм (просування постів, вакансії тощо), які й не мали приводити лідів.
Після виправлень: витрати, ліди, CPL і QL по кампанії — в одному рядку. Кампанії з витратами й нулем лідів — ті, що не мали лід-форм. Назви кампаній приховано.
7. Наскрізна аналітика: чек-лист перед побудовою
Випишіть реальні приклади UTM з CRM по кожному каналу і з'ясуйте, що лежить у кожному параметрі: кампанія, група, оголошення чи ID.
Перевірте, чи змінювався UTM-шаблон з часом. Якщо так, правило має враховувати обидва періоди.
Визначте, на якому рівні лежать витрати (кампанія, група, оголошення), і зводьте ліди саме з цим рівнем.
Для Google Ads з автотегуванням заздалегідь додайте ID кампанії у вивантаження витрат.
Нормалізуйте назви однією функцією для всіх джерел: пробіли, «+», %20, регістр.
Ключами з'єднання робіть тільки поля з однаковим змістом з обох боків. Бренд, регіон чи сегмент з різною логікою — не ключі.
Кожне правило (продукт, джерело, регіон) має жити в одному місці, а не дублюватися в SQL і в Data Studio.
Якщо все ж пишете регулярки в Data Studio, пам'ятайте: REGEXP_MATCH перевіряє весь рядок, для пошуку входження є REGEXP_CONTAINS.
Якщо витрати, ліди й продажі треба фільтрувати разом, складайте їх в одну таблицю подій через UNION, а не в бленд.
Рахуйте CPL, ROAS і конверсії як відношення сум на рівні джерела, а не в кожному графіку окремо.
Перевірте, в якій валюті приходять витрати з кожного кабінету, до того як їх складати.
Звірте контрольні цифри за один період: окремо з кожного джерела і разом у звіті. Якщо «разом» не дорівнює сумі «окремо», шукайте розрив на стику.
Висновок
Наскрізна аналітика ламається не на інструментах, а на стиках між ними. GA4, CRM, облікова система й Data Studio кожен окремо працювали правильно. Помилки жили там, де дані переходили з однієї системи в іншу: у UTM-шаблоні, у нормалізації назв, у двох копіях одного правила, в обмеженнях бленду.
Найшвидший спосіб знайти такий стик — звірити цифри «окремо» і «разом». Якщо окремо все сходиться, а разом ні, проблема не в даних, а в тому, як їх зводять.
Якщо ваш звіт показує витрати, ліди й продажі окремо, а зведення по кампаніях не виходить, — напишіть нам. Adminka.Pro з 2019 року впроваджує CRM і будує наскрізну аналітику для бізнесу, тож розберемося, на якому стику губляться ваші дані.
Коментарі