Регулярная выгрузка данных из Google Analytics в Power BI с помощью Google Sheets & Big Query

Этапы решения задачи:

Подготовка и создание таблиц в Google Sheets
Подготовка и создание таблиц в Google BigQuery
Настройка регулярного обновления данных
Создание библиотеки дат в BigQuery DWH
Создание отчета в Power BI

1. Подготовка и создание таблиц в Google Sheets & BigQuery
Для начала нам нужно зайти в режим инкогнито, потому что Google не всегда корректно работает когда вы вошли во множество аккаунтов Google.

Авторизуемся в аккаунте

Заходим на сайт таблиц и создаем пустую таблицу

Кликаем по кнопке меню "Дополнения" ➜ "Установить дополнение"

Добавляем дополнение Google Analytics.

После установки нужно запустить расширение.

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

Появляется пустая таблица, либо уже с теми данными что вы ввели при запуске.

1 - название отчета
2 - ид представления GA
3 - дата начала выгрузки отчета
4 - дата окончания выгрузки отчета
5 - показатели
6 - параметры
7 - фильтры
8 - лимит строк при выгрузке

Заполняем таблицу тем, что хотим увидеть в отчетах

Запускаем работу расширения.

Через некоторое время, в зависимости от количества данных отчет появится на отдельной вкладке.

2. Подготовка и создание таблиц в Google Big Query

Выбираем нужный проект

Чтобы появилась строка редактирование кода и вообще вся область с которой мы будем работать нужно выбрать SQL workspace.

Для создания набора данных, то есть таблицы куда мы будем загружать данные из Google Sheets, нужно выбрать проект, и нажать на три точки справа названия проекта:

В google bigquery появится окно, в котором вводим название нашего набора данных и кнопку создать

Открываем только что созданный набор данных

Создаем таблицу в bigquery, оболочку таблицы

Выбираем "Drive" , ссылку на нашу таблицу (открыть доступ - редактировать могут все у кого есть ссылка), формат файла Google Sheets.
Название дата сета, и название для таблицы источника.

Вводим названия для наших столбцов и выбираем их тип, дата/строка/число/число с плавающей точкой и нажимаем создать таблицу.

Чтобы проверить все ли правильно загрузили пишим простой запрос:

select * from `путь к нашей таблице`
where date is not null

В результате видим таблицу такую же как и в Google Sheets. Сравниваем все ли в порядке, нет пустых ячеек, пропусков и т.д. а так же смотрим на количество строк.

Осталось только из таблицы источника сделать результирующую таблицу bigquery, которая в дальнейшем будет подключена к power bi. Нужно выбрать настройки запроса, который мы писали ранее и изменить там:

Выбираем галочку "назначаем путь для запроса". Выбираем проект, датасет, называем таблицу (это конечная таблица). Галочка "Write if empty" и дальше "Save"

Дальше нужно еще раз запустить наш запрос

Теперь наша результирующая таблица появилась слева

Нажимаем на нее чтобы убедиться в том, что не допустили ошибок

3. Настройка регулярного обновления данных

Настраиваем регулярное обновление данных в нашей Google Sheets. Для этого нужно внести изменения в нашу таблицу, добавить в ячейки "start date" & "end date" функцию "= сегодня ( ) -1 ".

Теперь опять нужно запустить работу расширения для обновления данных в таблице

Проверяем результат

Приступаем к созданию расписания для каждодневного обновления данных в таблице за вчерашний день

Галочка - автоматический старт загрузки, каждый день, в 6-7 утра (узнайте какое у вас время в аккаунте Google чтобы подкорректировать под свой часовой пояс). Сохраняем "Save"

Переходим в Google BigQuery для обновления наших данных и создания расписания.
Находим путь к нашей таблице источнику.

Проверяем обновились ли данные в таблице. Пишем просто запрос.

Результат

Теперь новые данные поступившие в таблицу источник нужно отправить в результирующую таблицу

Делаем те же действия что и раньше. Только теперь галочку нужно поставить либо "append to table" либо "overwrite table", первое добавляет в таблицу данные, второе их перезаписывает, тут уже как вы выбрали ранее.

Теперь расписание работы кода

Создаем новое расписание

Запрос для проверки, сработало ли все правильно

Проверяем появилось ли наше расписание в списке

4. Создание библиотеки дат в BigQuery

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

Пишем простой запрос. Запрос: выбери не дублирующую дату из таблицы …

Выбираем проект, ид датасета, и новое название для нашей библиотеки.

В результате появится еще одна таблица слева в списке

5. Создание отчета в Power BI

Загружаем данные из визуализации Google Big Query в Power BI

Входим аккаунт Google в котором у нас таблица

Выбираем какие таблицы хотим загрузить из списка

Для качественной последующей визуализации данных из biguqery, настраиваем связи между таблицами

Можно приступать к созданию таблиц, фильтров, графиков и т.д.
Готовый отчет в power bi будет зависеть от того какие данные выгружаете, мы же оставим один из примеров нашего отчета power bi