Автоматизация выгрузки данных из 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)