Примеры SQL-запросов к данным в Google BigQuery

21
2712
Материалы для скачивания

Если вы уже читали наш блог, то знаете, что в качестве хранилища данных для сквозной аналитики мы рекомендуем 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:

график в 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 он выглядит так:

Как ROAS по валовой прибыли зависел от кампании за последние 30 дней

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

4. Как количество заказов в CRM зависело от способов оплаты и доставки

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

Как количество заказов в CRM зависело от способа оплаты и доставки?

На графике мы видим, что клиенты платят картой (onlineCard) только в двух случаях доставки из четырех — это самовывоз из магазина и доставка в почтовое отделение. Однако, оплатить картой можно и курьерскую доставку. Если вы предусмотрели такую возможность, но клиенты ею не пользуются, возможно, у вас проблемы с этим функционалом на сайте.

5. Как среднее время доставки зависело от города

Для этого отчета вам также понадобятся данные об исполняемости заказов из CRM. С его помощью можно проверить, нет ли проблем с доставкой в определенных городах, не нарушается ли установленное время.

Как среднее время доставки зависело от города?

На скриншоте выше видно, что дольше всего товары доставляют в Москве. Если при этом среднее время доставки больше того, что указано на сайте, необходимо проверить, есть ли в этом регионе склад или offline-магазин. Если нет, возможно, стоит его организовать — это улучшит время доставки. Также не будет лишним измерить удовлетворенность пользователей.

Выводы

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

  • Посмотреть действия пользователей в разрезе любого количества параметров.
  • Посмотреть статистику по ключевым действиям пользователей, а также среднее, максимальное или минимальное значение по типам хитов для конкретного пользователя.
  • Выбрать пользователей, которые посетили определенные страницы товаров, или посмотреть действия людей, которые купили конкретный товар.
  • Настроить воронки с любыми необходимыми вам шагами.
  • Узнать эффективность работы внутреннего поиска на сайте.

С помощью OWOX BI Pipeline вы можете дополнить свои данные в Google BigQuery и определить, например:

  • Как изменялся атрибутированный расход по источникам и каналам.
  • Как средняя стоимость привлечения посетителя зависела от города.
  • Как ROAS по валовой прибыли зависел от источника и канала / кампании.
  • Как количество заказов в CRM зависело от способов оплаты и доставки.
  • Как среднее время доставки зависело от города.

Напомним, что шаблоны SQL-запросов для каждого отчета, описанного в этой статье, мы собрали в отдельный PDF-файл. Чтобы получить его, просто заполните форму. Если у вас остались вопросы, с радостью обсудим их в комментариях к статье.

Использованные инструменты

Вас также могут заинтересовать