Урок 6. Выгрузка данных из счетчиков веб-аналитики
Шестой урок из курса Максима Уварова «Power Bi для интернет-маркетинга»
Полезные ссылки к уроку:
Яндекс.Метрика
- PQYandexMetrika
- Список измерений и метрик (публичный)
- Выключить токен — Паспорт → Управление доступом
- Полный список измерений и метрик
Google Analytics
Видеолекции
Работа с фильтрами в Яндекс.Метрике
Справка по использованию фильтров Яндекс.Метрики в API
Примеры:
= PQYM("21781912", "ym:s:visits,ym:s:avgVisitDuration,ym:s:sumVisitDuration,ym:s:sumVisitDurationSeconds", "ym:s:date", "2016-12-01", "2016-12-01", "AQAAAAAQy8J1AAFg-Xkrkp9D6kpGpHz2THTYX74", "ym:s:regionCityName=='Москва' OR ym:s:regionCityName=='Санкт-Петербург'", null)= PQYM("21781912", "ym:s:visits,ym:s:avgVisitDuration,ym:s:sumVisitDuration,ym:s:sumVisitDurationSeconds", "ym:s:date", "2016-12-01", "2016-12-01", "AQAAAAAQy8J1AAFg-Xkrkp9D6kpGpHz2THTYX74", "NOT(ym:s:age!=18)", null)Полный список группировок Яндекс.Метрики
Полный список группировок и метрик Яндекс.Метрики. Этот список понадобится вам при выполнении домашнего задания.
C момента съемок урока коннектор PQGoogleAnalytics был доработан. В нем даты начала и конца периода отчета прописываются не текстом, а задаются в формате дат Power Query. Следовательно ключевые слова today, yesterday, 35daysAgo не работают.
Чтобы отчет у вас выгружался за динамический промежуток дат, например, плавающим окном 28 дней назад по вчера, вам нужно задать диапазоны при помощи формул вида:
Для 28 дней назад:
=Date.AddDays(Date.From(DateTime.LocalNow()) , -28)Для вчера:
=Date.AddDays(Date.From(DateTime.LocalNow()) , -1)И использовать эти даты в PQGA можно вот так, соответственно:
=PQGA("ga:72013428", "ga:sessions", "ga:sourceMedium", Date.AddDays(Date.From(DateTime.LocalNow()) , -28), Date.AddDays(Date.From(DateTime.LocalNow()) , -1), "ga:source=@direct", null, 3)Фиксированные даты по времени без использования календарика вы можете задавать в формате:
=#date(2017, 11, 1)где в качестве первого аргумента используется год, в качестве второго — месяц, и в качестве третьего — день месяца.
Новый параметр inetrvalsVar в коннекторе PQGA отвечает за разбивку запроса к Google Analytics на диапазоны дат с заданным количеством дней. Это сделано для уменьшения семплинга Google Analytics.
В 2023 году коннектор PQGOOGLEANALYTICS работает не стабильно.
Домашнее задание
Основное задание
Создайте новую BI-систему
Убедитесь, что в настройках Power BI, в разделе Privacy у вас должен быть выбран radiobutton «Always ignore privacy levels».
Скачайте последнюю версию коннектора PQYandexMetrica с github.
Скопируйте в текущую BI систему функцию PQYM и все необходимые для его работы параметры.
Создайте запрос «YMStats» в котором получите следующие измерения и метрики за период с 2013-01-01 по вчера:
- Дата визита;
- Количество визитов;
- Количество отказов;
- Количество просмотров страниц;
- Среднее время на сайте;
- Задайте корректные типы данных для столбцов.
- Создайте запрос «GAStats» в котором подключитесь к Google Analytics встроенным в Power BI коннектором (для выгрузки из Google Analytiсs нужно использовать данные view: «1. MW Main Profile»).
- Чтобы избавиться от refspam в google analytics, сначала в запросе получите только группировку «Hostname» Находится в группе «Page tracking» и метрику число сессий.
- Далее отфильтруйте таблицу, чтобы осталась одна строчка со значением «marketing-wiki.ru». Для этого отсортируйте по убыванию столбец «Sessions», щёлкните правой кнопкой на первой ячейке в столбце hostname «marketing-wiki.ru» и выберите в контекстном меню операцию Text Filters → Equals
- После этого на вкладке Cube Tools Manage нажмите на кнопку «Add items» и добавьте следующие метрики и измерения (найдите самостоятельно):
- Дата визита;
- Количество отказов;
- Количество просмотров страниц;
- Проведенное время на сайте (убедитесь, что оно отображается в секундах);
- Загрузите результаты запросов в модель данных;
- При помощи функции CALENDARAUTO создайте таблицу «Calendar» в модели данных, было показано в уроке 6а — моделирование данных;
- Свяжите эту таблицу по столбцу «Date» с таблицами из Google Analytics и Яндекс.Метрики
- Исключите дни, когда данные присутствуют только в одном из счетчиков (как было показано в уроке 6а);
- Создайте Меры BounceRate, SessionTime, PageDepth
- Создайте меры абсолютного (GA-YM) и относительного ((GA-YM)/YM) отклонения показателей:
- Разница в количестве визитов VisitsGA-YM и VisitsGA/YM;
- Разница в показателе отказов (отказов на визит) BRGA-YM и BRGA/YM;
- Разница в глубине просмотра (страниц на визит) PageDepthGA-YM и PageDepthGA/YM;
- Разница в среднем времени на сайте (секунд на визит) TimeGA-YM и TimeGA/YM.
- Отформатируйте все меры корректно.
- Создайте визуализацию таблица, где по строчкам будут отложены года, а в столбцах будут представлены меры с абсолютными и относительными отклонениями показателей.
- Создайте визуализацию Линейчатая диаграмма, в которой будут представлены меры с относительными показателями. В этой визуализации должны быть отфильтрованы данные за 2018 год.
- Подсчитайте относительное отклонение в числе уникальных пользователей, которые насчитали метрика и аналитикс за 2018 год. Ответ выведите в визуализации карточка].
Задание сложное. Как делать — думайте сами.
Обратите внимание:
Средний показатель отказов рассчитывается по формуле:
SUM ( отказы ) / SUM ( визиты )Если в выгрузке нет абсолютного числа ОТКАЗОВ, но есть ПОКАЗАТЕЛЬ ОТКАЗОВ, то для каждой строчки с исходными данными предварительно необходимо рассчитать абсолютное число ОТКАЗОВ. Для этого нужно умножить ПОКАЗАТЕЛЬ ОТКАЗОВ на ЧИСЛО ВИЗИТОВ. После этого у вас появится возможность рассчитывать средний показатель отказов корректно.
Аналогичным образом следует поступить с глубиной просмотра и временем на сайте.
Эталонное выполнение домашнего задания
Файлы выполнения домашнего задания: