Доступ к пользовательским отчетам Google Analytics 4 из Google BigQuery

В этом руководстве показано, как создать собственный отчет с помощью надстройки Google Analytics 4 в Google Таблицах, представить этот отчет как внешнюю таблицу BigQuery, а затем запросить данные отчета непосредственно из BigQuery.

Материал основан на официальной документации Google, с моими комментариями и дополнениями по шагам настройки.

В качестве примера я буду воспроизводить отчет по событиям, но вы можете применить нижеприведенные шаги к любому другому отчету. Отчет по событиям в GA4 содержит данные о том, сколько раз активировалось каждое событие и сколько пользователей активировали каждое событие на вашем сайте или в приложении. Анализируя эти данные, можно повышать удобство для пользователей и количество конверсий.

Параметры в отчете:

• Название события (название в API - eventName)

Показатели в отчете:

• Количество событий (название в API - eventCount)
• Всего пользователей (название в API - totalUsers)
• Событий на активного пользователя (название в API - eventCountPerUser)
• Общий доход (название в API - totalRevenue)

Сам отчет выглядит следующим образом:

Создание отчета в Google Таблицах

Чтобы выгрузить этот отчет в Google Таблицы, а затем запросить данные этого отчета из Google BigQuery, перейдите в Google Sheets и создайте отдельный документ.

Перейдите в раздел Расширения - Дополнения - Установить дополнения:

В открывшемся окне найдите официальное расширение Google Analytics. Полностью оно называется GA4 Reports Builder for Google Analytics:

И установите его, предоставив все нужные разрешения.

В завершение вы увидите окно с успешной установкой расширения:

Примечание: установить надстройку Google Analytics Sheets можно и из Google Workspace Marketplace по ссылке.

После этого откройте Расширения - GA4 Reports Builder for Google Analytics - Create new report:

Вам откроется новое окно с настройкой конфигурации вашего отчета Google Analytics 4.

Укажите имя отчета (report name), выберите необходимый аккаунт и ресурс Google Analytics 4, а также задайте диапазон дат выгрузки статистики (например, за последние 30 дней).

Далее добавьте нужные поля вашего отчета. В Dimensions - параметры, в Metrics - показатели. Для моего примера отчета по событиям - это:

• eventName - параметр;
• eventCount, totalUsers, eventCountPerUser, totalRevenue - показатели.

В официальной документации Google демонстрируется другой пример - отчет Источники трафика (User acquisition), поэтому параметры и показатели там другие.

Оставьте поля Dimension filters и Metric filters пустыми. После настройки отчета нажмите кнопку Create report, чтобы создать конфигурацию отчета в текущем документе:

На новом листе Report Configuration вы увидите вашу собственную конфигурацию отчета:

После этого перейдите в Расширения - GA4 Reports Builder for Google Analytics - Run reports, чтобы создать отчет:

После завершения отчета будет создана новая вкладка с названием вашего отчета:

Чтобы этот отчет обновлялся автоматически, вы можете выставить динамический диапазон дат, а затем перейдите в Расширения - GA4 Reports Builder for Google Analytics - Schedule reports и настройке итоговое расписание. Например, каждый день час ночи:

Подключение отчета Google Таблицы как внешнюю таблицу BigQuery

Внимание! Перед тем, как выполнять этот шаг, у вас должен быть активный проект в Google Cloud с подключенным платежным биллингом. Если вы этого не сделаете, то не сможете полноценно работать с BigQuery. В связи с текущими событиями в мире Google приостановил работу для пользователей из России. На момент публикации этого материала вы не сможете привязать свою банковскую карту, выпущенную на территории РФ. Наиболее простое и эффективное решение - выпустить карту другой страны (Казахстан, Киргизия, Армения и т.д.), чтобы иметь возможность пользоваться Google Cloud и оплачивать счета.

С помощью BigQuery вы можете создать постоянную таблицу, связанную с вашим внешним источником данных в Google Sheets. Для этого перейдите в Google BigQuery, разверните проект (project) и выберите набор данных (dataset). Нажмите Create table:

В открывшемся окне в разделе Source для Create table from выберите Drive. В поле Select Drive URI введите URI вашего документа Google Sheets, содержащего отчет Google Analytics 4, в формате https://docs.google.com/spreadsheets/d/[file_id], где:

• [file_id] - идентификатор вашего файла Google Таблиц. Он отображается в ссылке после /d/ и до /edit:

В поле File format выберите Google Sheet. В BigQuery это будет выглядеть так:

В разделе Destination задайте следующие настройки:

• Project - выберите проект Google BigQuery;
• Dataset - выберите соответствующий набор данных;
• Table - введите имя таблицы, которую вы создаете в BigQuery (например, ga4report).

Убедитесь, что для Table type задан External table:

В поле Sheet range в разделе Source укажите имя листа и диапазон ячеек для запроса. Это должен быть лист, содержащий данные вашего отчета. Для нашего примера отчета диапазон может быть указан как eventsGA4!A:E:

В настройках конфигурации BigQuery:

В разделе Schema поставьте галочку Auto detect (функция автоматического определения полей):

Разверните раздел Advanced options. В поле Header rows to skip укажите количество строк, которые следует пропустить. В нашем примере данные отчета начинаются со строки 15:

Что является значением, которое следует задать в соответствующем поле:

В завершение нажмите кнопку Create table. При необходимости выберите свою учетную запись и нажмите Разрешить, чтобы предоставить клиентским инструментам BigQuery доступ к вашему Google Drive.

После этого в BigQuery вы должны увидеть созданную таблицу с вашей схемой данных:

Запрос к таблице отчета Google Таблиц из BigQuery

Теперь вы можете выполнить SQL-запрос в BigQuery к таблице своего отчета, созданного в Google Sheets, как если бы это была стандартная таблица BigQuery, с учетом ограничений на внешние источники данных.

Откройте созданную таблицу и нажмите Query. Вы можете открыть редактор SQL-запросов как в отдельной вкладке (In new tab), так и в отдельной части экрана (In split tab). В предложенном запросе измените SELECT FROM на SELECT * FROM, чтобы получить все поля отчета:

Нажмите RUN, чтобы выполнить запрос. Вы получите тот же результат с данными, что и в вашей Google Таблице:

Поскольку мы в этом примере использовали динамический диапазон дат (30daysAgo и yesterday) и расписание, то статистика будет всегда актуальна за последние 30 дней. Но вы можете изменить конфигурацию отчета Google Analytics 4 под себя, задав дату начала как статическое значение (какую-то конкретную дату), а дату окончания - yesterday. Таким образом, в ваш отчет будут добавляться все новые и новые данные, которые вы затем можете анализировать с помощью SQL-запросов в самом BigQuery. А добавив еще и параметр Date (date), вы сможете гибко работать с датами, запрашивая нужны срезы за любой период.

Но не забывайте, что статистика, которую вы выгрузили в Google Таблицы с помощью официального дополнения GA4 Reports Builder for Google Analytics, запрашивается по Data API (API Google Analytics 4), то время как использование прямой связи GA4 с BigQuery дает вам преимущество и возможность работы с сырыми данными.