Приклади SQL-запитів до даних в Google BigQuery

1. Звіти, побудовані на даних стандартного експорту з Google Analytics 360 в Google BigQuery
2. 1. Дії користувачів в розрізі будь-яких параметрів
3. 2. Статистика по ключовим дій користувачів
4. 3. Вибір користувачів, які переглянули певні сторінки товарів
5. 4. Дії користувачів, які купили певний товар
6. 5. Налаштування воронки з релевантними для вашого бізнесу кроками
7. 6. Ефективність роботи внутрішнього пошуку на сайті
8. Звіти, побудовані на даних потоку OWOX BI Pipeline з Google Analytics в Google BigQuery
9. 1. Як змінювався атрибутувати витрата за джерелами і каналами по днях
10. 2. Як середня вартість залучення відвідувача залежала від міста
11. 3. Як ROAS за валовим прибутком залежав від джерела і каналу / кампанії
12. 4. Як кількість замовлень в CRM залежало від способів оплати і доставки
13. 5. Як середній час доставки залежало від міста
14. висновки

Матеріали для скачування

Шаблони SQL-запитів до даних в Google BigQuery

Якщо ви вже читали наш блог, то знаєте, що в якості сховища даних для наскрізної аналітики ми рекомендуємо Google BigQuery. Ця система дозволяє будувати звіти будь-якої структури на основі повних, несемплірованних даних, а також вирішувати задачі , З якими виникають складнощі навіть в Google Analytics 360.

У цій статті ми поділимося прикладами звітів, які можна побудувати за допомогою SQL-запитів до даних в Google BigQuery. Спочатку розповімо, що можна порахувати завдяки стандартному експорту з GA 360 в GBQ. Потім покажемо, якими унікальними метриками можна доповнити дані Google Analytics завдяки OWOX BI Pipeline. Тексти всіх запитів ми зібрали в окремому PDF-файлі. Щоб отримати файл, залиште свій email , І ми надішлемо вам посилання для скачування.

Звіти, побудовані на даних стандартного експорту з Google Analytics 360 в Google BigQuery

При створенні звітів в Google Analytics можна зіткнутися з семплірованіє , Агрегированием даних і іншими обмеженнями системи . Експорт даних в Google BigQuery дозволяє обійти ці перешкоди і будувати більш складні звіти за допомогою SQL-запитів.

1. Дії користувачів в розрізі будь-яких параметрів

Припустимо, ви впровадили на сайт нові метрики або оновили діючі, щоб вимірювати важливі для вашого бізнесу KPI. Перевірити, чи правильно передаються дані, і вчасно зреагувати на збої в аналітиці вам допоможе звіт по динаміці хітів на сайті. Для нього знадобляться такі параметри, як device.deviceCategory, device.browser, hits.type і деталізація щодо дій користувачів (eventCategory, eventAction, Content Grouping).

Отримати всю цю інформацію в стандартному звіті Google Analytics не вийде, так як в ньому можна одночасно вибрати тільки 2 параметри, в призначеному для користувача звіті трохи більше - 5. У SQL-запиті таких обмежень немає, ви можете вказати всі параметри і метрики, які хочете побачити в звіті.

Шаблон запиту, який ми наводимо в матеріалах для скачування, допоможе дізнатися, як розподілено кількість сесій, користувачів і хітів по браузерам, пристроїв і типам хітів. При необхідності ви можете доповнити запит будь-якими іншими параметрами. Наприклад, операційна система (device.operatingSystem), інформація по влаштуванню (device.mobileDeviceInfo), мова (device.language), регіон (geoNetwork.region).

В результаті ви отримаєте звіт з усіма необхідними параметрами:

Крім того, можна вивантажити в Google BigQuery інформацію з ваших внутрішніх CRM і ERP систем. Це дозволить аналізувати дії користувача в розрізі будь-яких потрібних вам метрик: продуктовий каталог, маржинальність, категорія товарів, властивості користувача, виконуваність замовлень і т.д. Наприклад, ви можете зробити запит по транзакціях, об'єднати їх з викупленим замовленнями з CRM і підрахувати частку виконаних online-замовлень. Цей параметр покаже, чи немає у вас проблем на шляху від оформлення замовлення до оплати / доставки.

2. Статистика по ключовим дій користувачів

Якщо ви хочете об'єднати клієнтів в сегменти і налаштувати персоналізовані розсилки, вам знадобиться інформація про їх активність на сайті. І чим детальніше будуть дані, тим ширше можливості сегментації. У Google Analytics немає звіту, де були б зібрані всі дії користувача і розбиті по типам хітів: pageview, event, social, timing, а також події Enhanced Ecommerce (click, detail, add, remove, checkout, purchase, refund). Також там можна подивитися середнє, максимальне або мінімальне значення за типами хітів для певного користувача. Всю цю інформацію легко отримати за допомогою SQL-запиту, який ви знайдете в матеріалах для скачування.

Цей запит допоможе розрахувати для кожного користувача середнє, максимальне і мінімальне:

• Кількість переглядів сторінок з пошуковою видачею.
• Кількість переглядів сторінок за відвідування.
• Кількість додатків в кошик.
• Кількість вилучень з кошика.
• Кількість товарів, доданих до кошика.
• Кількість товарів, вилучених з кошика.
• Суму доданих до кошика товарів.
• Суму видалених з корзини товарів.

Також за допомогою SQL-запиту можна підрахувати для кожної сесії:

• Кількість переглядів сторінок з пошуковими запитами.
• Кількість переглянутих сторінок.
• Кількість додатків / вилучень з кошика.
• Кількість товарів, доданих / видалених з корзини.
• Суму доданих / видалених з корзини товарів.

Ця інформація буде корисна для прогнозування повторних покупок і мікроконверсій.

3. Вибір користувачів, які переглянули певні сторінки товарів

Якщо ви хочете оптимізувати воронку продажів, вам потрібно проаналізувати, що роблять відвідувачі вашого сайту перед покупкою. У стандартних звітах GA немає можливості переглянути всі типи дій користувача (pageview, event, social, timing). Є звіт «Статистика по користувачам» (User Explorer), але там не можна побачити статистику відразу по всім користувачам, необхідно відкривати кожного окремо і застосовувати розширені сегменти з фільтрацією на ID товару.

Побудувавши звіт на даних стандартного експорту, ви зможете переглянути всі дії користувача, які відслідковуються через код GA. Наприклад, наш SQL-запит допоможе вибрати користувачів, які переглянули певні сторінки товарів. Ви можете використовувати цю інформацію, щоб відправляти нагадування тим, хто кинув товар в кошику, показувати товарні рекомендації або складати портрети користувачів.

4. Дії користувачів, які купили певний товар

Все написане в пункті 3 справедливо і для цього звіту. За допомогою SQL-запиту з файлу для скачування можна побудувати звіт, що містить всі дії користувачів, які купили певний товар. Ви можете використовувати цю інформацію, щоб пропонувати клієнтам товари, схожі на ті, що вони вже придбали, або аксесуари до них.

5. Налаштування воронки з релевантними для вашого бізнесу кроками

Припустимо, ви написали статтю в блозі, яка повинна мотивувати читачів підписатися на розсилку. Для відстеження конверсій вам важливі обидва дії - і читання статті і підписка, але в Google Analytics їх не можна об'єднати, тому що це різні типи цілей: «Перегляд сторінки» і «Подія».

Крім цього, при настройках цілей в GA є й інші обмеження:

• В одному поданні Google Analytics можна налаштувати максимум 20 цілей. При цьому мета не можна видалити, можна тільки зупинити для неї запис даних.
• У звіт потрапляють дані, зібрані після створення цілі. Тобто ви не можете застосувати мета до даних за минулий період.

Експорт даних в Google BigQuery і простий SQL-запит дозволяють обійти ці обмеження. Ви можете налаштувати воронку з будь-якими необхідними кроками, щоб перевірити вузькі місця на сайті і дізнатися, на яких етапах «відпадає» найбільше користувачів.

Наш шаблон запиту допоможе дізнатися, як часто користувачі відкривають розділ «Характеристики товара» і як це впливає на конверсію. У нашому прикладі воронка виглядає так: 1. Перегляд сторінки товару → 2. Перегляд характеристик товару → 3. Додавання товару в кошик. Однак ви можете в Як проміжний крок воронки вибрати будь-які дії, які відслідковуються на сайті. В результаті у вас вийде приблизно такий графік:

6. Ефективність роботи внутрішнього пошуку на сайті

Ви можете підвищити конверсію за рахунок поліпшення внутрішнього пошуку на сайті. Наприклад, проаналізувати частку нульових відповідей на запит користувача і внести правки в контент на сайті, додати товари-замінники, акції на товари з тієї ж категорії і т.д. Або, якщо якусь категорію шукають частіше за інших, можна вивести її в каталозі на більш високу позицію. Для цього вам знадобляться звіти по пошуковим запитам.

Параметри і показники в звіті GA повинні бути одного рівня, тобто мати однакову область доступу: «Хіт», «Сесія», «Користувач» або «Товар». Це означає, що в одному звіті з параметрами, наприклад, рівня «Хіт» не можна подивитися SKU товарів або рейтинг пошукового запиту за кількістю сесій.

SQL-запит з файлу для скачування допоможе вам налаштувати аналітику внутрішнього пошуку на сайті, щоб порахувати ефективність сесій з пошуковими запитами і перевірити гіпотези про юзабіліті.

За допомогою SQL ви зможете порахувати для кожного пошукового запиту:

• Кількість сесій, в яких зустрічався цей запит.
• Середній розмір пошукової видачі.
• Сумарний дохід по сесіях, в яких був пошуковий запит.
• Рейтинг пошукового запиту за кількістю сесій.
• Рейтинг пошукового запиту за розміром результату видачі.
• Рейтинг пошукового запиту за доходом.

Звіти, побудовані на даних потоку OWOX BI Pipeline з Google Analytics в Google BigQuery

завдяки OWOX BI Pipeline ви зможете збагатити свої дані в Google BigQuery додатковою інформацією, не доступної ні в стандартній, ні в платній версії Google Analytics. Справа в тому, що GA дозволяє імпортувати тільки агреговані дані про витрати в прив'язці до рекламних кампаній, а в схемою даних стандартного експорту взагалі немає інформації про витрати.

За допомогою OWOX BI Pipeline можна об'єднати дані з усіх рекламних майданчиків і передати їх в Google BigQuery. При цьому сервіс прив'яже витрати до всіх сесій. Завдяки цьому ви зможете групувати витрати і доходи на рівні кожного користувача, сегмента або когорти користувачів, а також посадкової сторінки.

Нижче ми наведемо кілька метрик, які не можна розрахувати зі стандартним експортом, але можна з OWOX BI. Всі ці звіти ви можете отримати або за допомогою SQL-запитів, або задаючи питання звичайною мовою в OWOX BI Smart Data .

1. Як змінювався атрибутувати витрата за джерелами і каналами по днях

Цей звіт допоможе зібрати дані по рекламних витрат за певний період і простежити динаміку показників у часовому відрізку, порівняти з іншими періодами, оцінити частку реклами серед джерел.

Скріншот звіту з OWOX BI Smart Data:

Наприклад, на графіку видно, що 10 червня витрати на facebook / cpc різко знизилися. Це може стати сигналом для маркетолога - потрібно подивитися, які саме кампанії, групи оголошень і ключові слова в цьому джерелі стали приносити менше кліків, і розібратися з причинами. Можливо, знизилася частка показів через оголошень конкурентів. В такому випадку можна спробувати підняти ставки, підвищити релевантність оголошень, змінити контент і т.д.

2. Як середня вартість залучення відвідувача залежала від міста

Як ми вже писали вище, ні Google Analytics, ні стандартний експорт в Google BigQuery не дають можливості розрахувати ваші витрати на кожну сесію, користувача, когорту. Зате це легко зробити з OWOX BI.

За допомогою цього звіту ви можете дізнатися, скільки в середньому витратили на залучення відвідувача в різних містах за певний період. Ось як він виглядає в OWOX BI Smart Data:

Як правило, рекламні кампанії налаштовують для кожного регіону по-різному. Цей звіт підкаже, на яку область звернути увагу. Наприклад, на графіку вище ми бачимо, що середня вартість залучення користувача (CAC) вище за все в Дніпрі. Можливо, варто провести більш детальний аналіз рекламних кампаній в цьому регіоні, розрахувати середній чек, вартість залучення покупця в розрізі рекламних кампаній і порівняти її з довічною цінністю клієнта (LTV). Якщо кампанії нерентабельні, можна їх відключити або знизити ставки.

3. Як ROAS за валовим прибутком залежав від джерела і каналу / кампанії

Звіт допоможе оцінити ефективність реклами та побачити результати кампаній з урахуванням наповнюваності замовлень і собівартості товарів з CRM. Щоб отримати такий звіт, потрібно попередньо імпортувати дані про покупки з вашої внутрішньої системи в Google BigQuery. В інтерфейсі OWOX BI Smart Data він виглядає так:

Цей графік показує кращі і гірші кампанії, а також «середнячків». Маркетологи можуть детальніше аналізувати лідерів і аутсайдерів, щоб зрозуміти причини успіхів або невдач, і вибирати для майбутньої реклами найбільш ефективні канали.

4. Як кількість замовлень в CRM залежало від способів оплати і доставки

Цей звіт допоможе зрозуміти, чи є проблеми при оформленні замовлень, очікувано чи працюють способи оплати. Щоб його побудувати, потрібно вивантажити з CRM в Google BigQuery інформацію про виконані замовлення. Потім ви можете використовувати наш шаблон SQL-запиту або ж додати CRM-дані в OWOX BI Smart Data , Задати питання з потрібними метриками і отримати такий графік:

На графіку ми бачимо, що клієнти платять карткою (onlineCard) тільки в двох випадках доставки з чотирьох - це самовивезення з магазину і доставка в поштове відділення. Однак, оплатити картою можна і кур'єрську доставку. Якщо ви передбачили таку можливість, але клієнти нею не користуються, можливо, у вас проблеми з цим функціоналом на сайті.

5. Як середній час доставки залежало від міста

Для цього звіту вам також знадобляться дані про наповнюваності замовлень з CRM. З його допомогою можна перевірити, чи немає проблем з доставкою в певних містах, чи не порушується встановлений час.

На скріншоті вище видно, що найдовше товари доставляють в Москві. Якщо при цьому середній час доставки більше того, що зазначено на сайті, необхідно перевірити, чи є в цьому регіоні склад або offline-магазин. Якщо немає, можливо, варто його організувати - це поліпшить час доставки. Також не буде зайвим виміряти задоволеність користувачів.

висновки

За допомогою стандартного експорту даних в Google BigQuery і SQL-запитів можна обійти обмеження Google Analytics і будувати звіти для більш глибокого аналізу. Наприклад, ви можете:

• Подивитися дії користувачів в розрізі будь-якої кількості параметрів.
• Подивитися статистику по ключовим дій користувачів, а також середнє, максимальне або мінімальне значення за типами хітів для конкретного користувача.
• Вибрати користувачів, які відвідали певні сторінки товарів, або подивитися дії людей, які купили конкретний товар.
• Налаштувати воронки з будь-якими необхідними вам кроками.
• Дізнатися ефективність роботи внутрішнього пошуку на сайті.

За допомогою OWOX BI Pipeline ви можете доповнити свої дані в Google BigQuery та визначити, наприклад:

• Як змінювався атрибутувати витрата за джерелами і каналами.
• Як середня вартість залучення відвідувача залежала від міста.
• Як ROAS за валовим прибутком залежав від джерела і каналу / кампанії.
• Як кількість замовлень в CRM залежало від способів оплати і доставки.
• Як середній час доставки залежало від міста.

Нагадаємо, що шаблони SQL-запитів для кожного звіту, описаного в цій статті, ми зібрали в окремий PDF-файл. Щоб отримати його, просто заповніть форму . Якщо у вас залишилися питання, з радістю обговоримо їх у коментарях до статті.

використані інструменти