Автоматизация выгрузки данных из Google Analytics в Google Sheets, далее в Google BigQuery

Проблема:
К примеру вам необходимо совмещать данные Google Sheets c данными Google Analytics, вы можете соединить их в Google BigQuery. Или вы хотите построить кастомный отчет в BI системе используя данные Google Analytics.

Решение.
Схема автоматизации выгрузки данных из Google Analytics в Google BigQuery через addon Google Sheets

Сокращения, которые могут использоваться в описании схемы

BQ - Google BigQuery
GA - Google Analytics
GS - Google Sheets
GD - Google Drive
Add-on GA - Веб-приложение API Google Analytics для загрузки данных в Google Sheets

1. Создание источников данных через Google Sheets
Необходимо создать новый Google Sheet в пространстве Google Drive, далее для этого GS необходимо установить Add-on Google Analytics из магазина аддонов
После установки этого Add-on GA необходимо в него зайти и нажать create new report
в появившемся окне заполнить необходимые поля
далее нажать create report
Пример заполненных полей на вкладке Report Configuration
Такие настройки загрузят данные за 6 августа 2020 года.

Для поиска точных названий показателей и параметров API Google Analytics - можно использовать их документацию Google Analytics https://ga-dev-tools.appspot.com/dimensions-metrics-explorer/
Для создания автоматических выгрузок на ежедневной основе требуется настроить автопланирование


При этом в настройках параметра Start Date и End Date необходимо установить автоматическую дату "ВЧЕРА" через формулу в ячейке =today()-1 но учитывая тот ежедневный объем данных и время которое GA берет на полную загрузку данных, лучше =today()-2

Таким образом на ежедневной основе по заданному в расписании времени данного GS будет наполняться данными за позавчерашний день.

Важные детали подготовки GS для корректного экспорта данных
  • Параметр Limit должен быть выставлен желательно не менее 50000 (запросы требуется проверять, чтобы выгрузка помещалась в это количество строк)
  • Лист с выгрузкой (report name) должен быть подтянут ссылкой на новый лист и протянут до конца + количество строк должно быть на максимум.

  • Протянуть можно от края до края через CTRL+D
  • Порядок параметров и показателей в выгрузке (слева направо) должен точно соответствовать будущей схеме в таблицах BQ (BQ - param1, param2, metric1, metric2), GS (GS - param1, param2, metric1, metric2). Важно использовать одинаковые типы данных (string, date, integer, float итд)
  • Запланированная загрузка данных (расписание выгрузок) в GS должна быть РАНЕЕ будущей миграции данных между таблицами BQ иначе будут либо дубли либо загрузится не корректный массив.

2. Настройка BigQuery, планирование импорта данных из Google Sheets в BigQuery

Настройка Google BigQuery
Для загрузки данных в BigQuery необходимо подготовить 2 базы данных
Первая база (Source) будет источником данных на основе таблицы GS
Вторая база (Result) будет результирующей таблицей, которая будет забирать ежедневно данные из первой базы (Source) схемы и последовательность параметров и показателей (столбцов) должны быть идентичны между Source и Result
Планирование миграции данных между Source и Result таблицами
Создание и настройка запроса
Для первого импорта данных следует выбрать полную запись таблицы (TRUNCATE)
, нужный dataset name
и указать корректное имя таблицы . Далее после настройки импорта GA - GS, GS - BQ необходимо изменить настройки запроса на ежедневное добавление данных (APPEND).
Запрос по переносу данных максимально простой. Вместо where Date IS NOT NULL может быть другой столбец.
SELECT  *
FROM
`t****al-***-279208.*****_events.******_*****_source`
WHERE Date IS NOT NULL
Все. Данные на ежедневной основе будут складироваться в BigQuery таблицы. Далее, вы из них можете построить желаемый отчет в BI системе (например Power BI, Google Data Studio)
Made on
Tilda