Двенадцатый урок из курса Максима Уварова «Power Bi для интернет-маркетинга» — Продвинутый DAX — Функции calculate, filter, all

Рабочий файл: PBI-11.pbix

Видеолекции

Вебинар «Продвинутые метрики для анализа контекстной рекламы»

Информация из вебинара является опциональной к изучению. Она поможет понять, зачем нам нужен прогноз конверсии.

Раздатка к вебинару

Домашнее задание

1. Модифицируйте созданную таблицу «summarizedPhrasesLearn», чтобы в ней появились столбцы «Region», «Targeting»;
2. В таблице «summarizedPhrasesLearn» создайте столбец «TargetingRegion», в котором соедините значения столбцов «Region», «Targeting»;
3. В таблице «summarizedPhrasesLearn» создайте столбцы «Sessions-RT» и «Transactions-RT», в которых подсчитайте суммарное количество сессий и транзакций по одинаковым значениям столбца «TargetingRegion» — для расчета коэффициента конверсии группы (априори);
4. В таблице «summarizedPhrasesLearn» создайте столбец «CRPooledByRT» в котором методом статистического пулинга для каждой фразы подсчитайте прогнозируемую конверсию.

5. На основе прогнозной конверсии в столбце «CRPooledByRT» создайте столбец «GroupsByCRRT»
6. В столбце разбейте ключевые фразы на 6 групп, на основе прогнозной конверсии. В каждой группе должно быть ключевых фраз на сумму кликов не меньше чем 10% от общей выборки в таблице «SummarizedPhrasesLearn».
7. Создайте визуализацию «Line and clustered combo chart» в которой в область «Shared axis» перенесите столбец «GroupsByCRRT». В область столбцов перенесите «_CR_fact_learn» и «_CR_fact_control» (меры создайте самостоятельно). В область линии перенесите меры «_Clicks_learn» и «_Clicks_control» (меры создайте самостоятельно).
8. В BI системе текстовое поле, в котором опишите выводы, которые можно сделать из полученной визуализации.

Эталонное выполнение домашнего задания

Папка с выполненным заданием

Шпаргалка курса и ментальная карта, ПДФ, 88 страниц, 13 Мб

Одиннадцатый урок из курса Максима Уварова «Power Bi для интернет-маркетинга» — Продвинутый DAX — прогнозирование конверсии методом статистического пулинга

Рабочий файл: папка PBI-10

Видеолекции

Прогноз вероятности конверсии от Андрея Белоусова

Домашнее задание

1. Создайте таблицу «RegionPriors», используя DAX-функцию summarize, в которой будут:
   • столбец «Регион» (взятый из названия рекламной кампании);
   • столбец «Сеансы» с суммой по сессиям;
   • столбец «Транзакции» с суммой по транзакциям.
2. Создайте новую таблицу «conversionPrediction», используя DAX-функцию summarize, в которой:
   • будут столбцы:
     • «phrase id»;
     • «Регион» (взятый из названия рекламной кампании)
   • будут подсчитаны сеансы и транзакции.
3. Используя полученные агрегированные показатели таблицы RegionPriors, рассчитайте прогнозный коэффициент конверсии для каждой строчки таблицы «conversionPrediction» методом пулинга, по статье «Прогноз вероятности конверсии».

Рабочие файлы: папка PBIX+CSV

Эталонное выполнение

Файл выполнения домашнего задания: pbi10golden.pbix

Шпаргалка курса и ментальная карта, ПДФ, 88 страниц, 13 Мб

Десятый урок из курса Максима Уварова «Power Bi для интернет-маркетинга» — Программирование Power Query — Создание частотного словаря

Рабочие файлы:

• ЧастотныйСловарь.pbix
• searchQueriesPrintio.csv
• Робот распознаватель принтио.xlsx
• Принтио-Поисковые-фразы.csv

Полезные ссылки к уроку:

• Пост Дмитрия Тумайкина в фэйсбуке 5 января 2017 года про робота-распознавателя, использующегося в уроке.
• Инструкция от Дмитрия Тумайкина на ютубе
• Робот-распознаватель 8.6
• Утилиты Excel и PowerBI для контекстной рекламы от Дмитрия Тумайкина

Видеолекции

Домашнее задание

1. Скачайте файл со статистикой по поисковым запросам, на которые сработали объявления Яндекс.Директ рекламодателя Printio.ru
   Файл: Printio.ru-Поисковые-фразы.csv
2. Поисковые фразы лемматизируйте удобным вам способом из предложенных на выбор:
   • Робота-распознавателя Дмитрия Тумайкина
   • Нормализация запросов Py7
3. Создайте таблицу «частотный словарь», так, чтобы в ней был столбец «лемма поисковой фразы» и столбец с леммами слов, входящих в поисковую фразу. Для этого разбейте леммы поисковых фраз на леммы слов входящих в поисковые фразы, при помощи функции Text.Split.
   На каждую лемму слова должна приходиться отдельная строчка. В столбце «лемма поисковой фразы» — элементы должны дублироваться (в этом столбце «лемма поисковой фразы» должна встречаться столько раз сколько в ней слов).

4. К каждой лемме слова подсчитайте метрики:
   • количество визитов
   • показатель отказов
   • глубина просмотра

5. В BI-системе подсчитайте и выведите в качестве визуализации, насколько меньше анализируемых поисковых фраз у нас становится, когда мы применяем лемматизацию.
   Чтобы рассчитать этот показатель нужно от количества уникальных поисковых фраз отнять количество уникальных лемм поисковых фраз. Уникальные леммы поисковых фраз (могут содержать больше одного слова), должны быть отсортированы по алфавиту.

6. На основе данных из таблицы «Частотный словарь» визуализируйте на точечных диаграммах зависимость между средней глубиной просмотра и средним показателем отказов по каждой лемме слова. В диаграмме должны быть леммы слов, по которым было совершено более 10 визитов.

Эталонное выполнение домашнего задания

Файл: папка Golden_10

Шпаргалка курса и ментальная карта, ПДФ, 88 страниц, 13 Мб

Девятый урок из курса Максима Уварова «Power Bi для интернет-маркетинга» — Программирование Power Query

Рабочий файл 1: ListGenerateRamblerTop100.pbix
Рабочий файл 2: PBI-8.pbix

Видеолекции

Генератор произвольных списков в Power Query и Power BI — про List.Generate в блоге Максима Зеленского

Домашнее задание

На основе файла с вашим домашним заданием из 4-го урока, по аналогии с видео 9-го урока, на основе запроса «xls» (в котором объединяются файлы xlsx):

1. Создайте новый запрос «XlsPositionsWithShare» со сгруппированными данными по полям:
   • Id кампании;
   • Название кампании;
2. И подсчитанными метриками:
   • Сумма кликов;
   • Сумма показов.
3. Добавьте столбец с использованием созданной функции (по аналогии с видео), в котором будет сформирована текстовая строка, которая будет отражать долю кликов по различным позициям (на основе данных из соответствующего столбца) вида:
   • «спец 0.70, прочее 0.30».
4. Убедитесь, что ваш запрос называется «XlsPositionsWithShare».
5. Загрузите результаты выполнения запроса в модель данных.

Шпаргалка курса и ментальная карта, ПДФ, 88 страниц, 13 Мб

Восьмой урок из курса Максима Уварова «Power Bi для интернет-маркетинга»

Видеолекции

Рабочий файл: PBI-7.pbix

Домашнее задание

Файл: NFDPBI7.pbix

Задание

По состоянию на 2023 API comagic работает нестабильно.
Это домашнее задние можно пропустить.

Ваша задача модифицировать вашу bi систему из раздатки так, чтобы:

1. Функция «getDataCoMagicFunciton» получала данные при помощи метода «Call», для этого нужно изменять запрос «getDataCoMagic Example».
2. По результатам выполнения функции «getDataCoMagicFunciton» были доступны столбцы с данными:
   • С какого номера звонили
   • На какой номер звонили (номер услуги)
   • Время звонка
3. Создать запрос, «Homework», который бы при помощи функции «getDataCoMagicFunciton» получал данные за период с 01.01.2016 по 31.12.2018 и загрузить его в модель данных.
4. Создать меру «_Количество звонков».
5. Создать визуализацию «График-линия», в которой помесячно (год + месяц) продемонстрировать количество полученных звонков (с использованием созданной меры).

Шпаргалка курса и ментальная карта, ПДФ, 88 страниц, 13 Мб

Седьмой урок из курса Максима Уварова «Power Bi для интернет-маркетинга»

Полезные ссылки к уроку:

• Блог Криса Вебба на аглийском языке
• Переводы некоторых статей Криса Вебба в блоге NeedForData

Видеолекции

Домашнее задание

Рабочий файл: папка l7

Задача

Ваша задача модифицировать вашу bi систему из урока 3, чтобы добавить в нее обновленные и расширенные данные.
В процессе вы будете сталкиваться с ошибками, которые вам предстоит решать.
Удачи!

1. Откройте вашу BI систему с выполненным домашним заданием к уроку 3.
2. Скачайте и распакуйте архив раздатки.
3. Создайте запрос, который получает данные из папки «CSV» из распакованного архива раздатки.
4. Вероятнее всего, power bi автоматически не смог корректно распознать тип данных у столбцов содержащих десятичные дроби (все столбцы с расходами) и даты. У столбцов с десятичными дробями остался тип данных текстовый, а к датам хоть и был применен тип данных «дата», но в строчках ниже появились ячейки с ошибками. Убедиться в этом вы сможете, если примените функцию Keep Errors
5. Чтобы избавиться от ошибок в столбце «Дата» вам нужно удалить автоматически созданный шаг «Changed types» («Измененный тип»), во время которого появились ошибки.
6. Измените тип данных в столбце «Дата», выставив тип данных «Date» с использованием локали «English (USA)» («Английский (США)»).
7. Далее, измените тип данных у всех столбцов с расходом, на формат «decimal number» с локалью «English (USA)» («Английский (США)»). Зажав клавишу ctrl вы можете выбрать несколько столбцов и задать тип данных выделенным столбцам одновременно.
8. После этого вы можете выделить все столбцы и определить типы данных в столбцах автоматически. Чтобы выделить все столбцы сначала выделите первый столбец, зажмите клавишу shift и выберите последний столбец. Либо, выделив любую ячейку в таблице, вы можете нажать на сочетание клавиш CTRL+A. После того как все столбцы выделены вам нужно нажать на кнопку Detect Data types automatically.
9. Убедитесь, что запрос выполняется без ошибок — для этого используйте функцию keep errors.
10. Откройте код текущего запроса (где вы настроили обработку всех файлов из папки CSV), выделите его полностью и скопируйте в буфер обмена.
11. Откройте код запроса Stats в Advanced Editor и замените на код, который вы скопировали на предыдущем шаге. Если у вас нет этого запроса Stats — смотрите урок 3 и разбирайтесь сами, какой запрос вы поименовали не по инструкции.
12. Закройте редактор кода (Advanced Editor), убедитесь, что запрос выполняется корректно.
13. Добавьте столбец «Валовая прибыль». Для этого умножьте столбец «Доход» на 20%.
14. Удалите оригинальный запрос, в котором вы получали данные из папки с CSV.
15. Переименуйте запрос «CampaignNames» в «DimCampaignNames» и включите загрузку запроса в модель данных.
16. Загрузите в модель данных таблицы DimAds и DimPhrases из файла «Dict20190731.xlsx» из архива раздатки.
17. Создайте связи (или сделайте их активными) между таблицами «Stats» и «DimAds» по столбцу «Id объявления», между таблицам «Stats» и «DimPhrases» по столбцу «Id фразы», между таблицами «Stats» и «DimCampaigns» по столбцу «Id кампании». Если вам будут мешать активные связи — удалите или выключите их.
18. Модифицируйте меру, «*Ключевая фраза количество уникальных» по формуле *Ключевая фраза количество уникальных = CALCULATE( DISTINCTCOUNT(DimPhrases[Фраза (ключ)]) ; Stats ). Подробности в видео ниже.
19. Модифицируйте меру средней ставки по аналогии.
20. Восстановите работоспособность визуализаций «Таблиц» в отчете, заменив пропавшие столбцы из таблицы «Stats» на соответствующие столбцы из таблиц «DimCampaignNames» модели данных.
21. Откройте запрос «DimAds» в Power Query.
22. Добавьте «настраиваемый столбец» («custom column») с названием «Uri», в котором введите функцию Uri.Parts с аргументом [URL объявления] (=Uri.Parts([URL объявления]) ).
23. В результате выполнения функции в столбце «Uri» появятся ячейки, содержащие данные в структурированном типе данных «Record» («Запись»). Изучите, чего полезного есть в этих ячейках.
24. Добавьте «настраиваемый столбец» («custom column») с названием «UrlPath». В столбце «UrlPath» должны быть данные из поля «Path» записи в столбце «Uri».
25. Добавьте «настраиваемый столбец» («custom column») с названием «UrlQueryRegion». В столбце «UrlQueryRegion» должны быть данные из поля записи «region» (это поле вложено в поле записи «Query» столбца «Uri»). В уроке я рассказывал как обращаться к несуществующим полям таблицы, чтобы при обращении получалось значение «null» вместо ошибки. Примените эти знания.
26. Удалите столбец «Uri».
27. Загрузите запрос в модель данных.
28. Создайте пустой запрос «TextContainsAny». В Advanced Editor замените код пустого запроса на код функции Text.ContainsAny.pq. Эта функция проверяет, есть ли в тексте из первого аргумента вхождения любого из текстов списка второго аргумента.

/*
//Check if a string contains any of the keywords from a given list
//Usage:
    Text.ContainsAny = Load("Text.ContainsAny"),
    Text.ContainsAny("the cat sat on the mat", {"cat", "apple"})
//Result: true
*/

(str, needles) as logical =>
let
	count = List.Count(needles)
in

List.AnyTrue(
	List.Generate(
		()=>[i=0],
		each [i] < count,
		each [i=[i]+1],
		each Text.Contains(str,needles{[i]})
	)
)

29. В запросе «DimPhrases» разделите столбец «Фраза (ключ)» по крайнему левому разделителю « -» Split Column by Delimeter — Разделить текстовый столбец по разделителю.
30. Переименуйте получившиеся столбцы соответственно «Ключевая фраза» и «Минус-слова».
31. Добавьте столбец «ПродающиеДобавки», в котором при помощи недавно созданной функции «TextContainsAny» проверьте значения столбца «Ключевая фраза» на наличие слов:
    1. купить
    2. Москва
    3. интернет
32. Загрузите запрос в модель данных.

Эталонное выполнение

Файлы: папка l7_Gold

Шпаргалка курса и ментальная карта, ПДФ, 88 страниц, 13 Мб

Шестой урок из курса Максима Уварова «Power Bi для интернет-маркетинга»

Полезные ссылки к уроку:

Яндекс.Метрика

• PQYandexMetrika
• Список измерений и метрик (публичный)
• Выключить токен — Паспорт → Управление доступом
• Полный список измерений и метрик

Google Analytics

• PQGoogleAnalytics
• Список измерений и метрик
• Выключить токен

Видеолекции

Работа с фильтрами в Яндекс.Метрике

Справка по использованию фильтров Яндекс.Метрики в 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 по вчера:

1. Дата визита;
2. Количество визитов;
3. Количество отказов;
4. Количество просмотров страниц;
5. Среднее время на сайте;

В Google Analytics можно выгрузить корректно суммарное время на сайте в секундах, в то время как из Яндекс.Метрики суммарное время на сайте выгрузить не получится из-за бага метрики: длительность всегда выгружается в формате времени (например, 22:01:17) при этом число суток ОТБРАСЫВАЕТСЯ. Поэтому нужно выгрузить метрику среднего времени нахождения на сайте, применить тип данных Duration к столбцу. Далее нужно преобразовать столбец в общее число секунд. И уже после создать новый столбец «Общее время на сайте», который получится после перемножения среднего времени в секундах на число визитов.

6. Задайте корректные типы данных для столбцов.
7. Создайте запрос «GAStats» в котором подключитесь к Google Analytics встроенным в Power BI коннектором (для выгрузки из Google Analytiсs нужно использовать данные view: «1. MW Main Profile»).
8. Чтобы избавиться от refspam в google analytics, сначала в запросе получите только группировку «Hostname» Находится в группе «Page tracking» и метрику число сессий.

«Hostname» Находится в группе «Page tracking»

9. Далее отфильтруйте таблицу, чтобы осталась одна строчка со значением «marketing-wiki.ru». Для этого отсортируйте по убыванию столбец «Sessions», щёлкните правой кнопкой на первой ячейке в столбце hostname «marketing-wiki.ru» и выберите в контекстном меню операцию Text Filters → Equals

Контекстное меню Text Filters → Equals

10. После этого на вкладке Cube Tools Manage нажмите на кнопку «Add items» и добавьте следующие метрики и измерения (найдите самостоятельно):
    • Дата визита;
    • Количество отказов;
    • Количество просмотров страниц;
    • Проведенное время на сайте (убедитесь, что оно отображается в секундах);

Вкладка Cube Tools Manage → «Add items»

11. Загрузите результаты запросов в модель данных;
12. При помощи функции CALENDARAUTO создайте таблицу «Calendar» в модели данных, было показано в уроке 6а — моделирование данных;
13. Свяжите эту таблицу по столбцу «Date» с таблицами из Google Analytics и Яндекс.Метрики
14. Исключите дни, когда данные присутствуют только в одном из счетчиков (как было показано в уроке 6а);
15. Создайте Меры BounceRate, SessionTime, PageDepth
16. Создайте меры абсолютного (GA-YM) и относительного ((GA-YM)/YM) отклонения показателей:
    • Разница в количестве визитов VisitsGA-YM и VisitsGA/YM;
    • Разница в показателе отказов (отказов на визит) BRGA-YM и BRGA/YM;
    • Разница в глубине просмотра (страниц на визит) PageDepthGA-YM и PageDepthGA/YM;
    • Разница в среднем времени на сайте (секунд на визит) TimeGA-YM и TimeGA/YM.
17. Отформатируйте все меры корректно.
18. Создайте визуализацию таблица, где по строчкам будут отложены года, а в столбцах будут представлены меры с абсолютными и относительными отклонениями показателей.
19. Создайте визуализацию Линейчатая диаграмма, в которой будут представлены меры с относительными показателями. В этой визуализации должны быть отфильтрованы данные за 2018 год.
20. Подсчитайте относительное отклонение в числе уникальных пользователей, которые насчитали метрика и аналитикс за 2018 год. Ответ выведите в визуализации карточка].

Задание сложное. Как делать — думайте сами.

Визуализации → карточка

Обратите внимание:

Средний показатель отказов рассчитывается по формуле:

SUM ( отказы ) / SUM ( визиты )

Если в выгрузке нет абсолютного числа ОТКАЗОВ, но есть ПОКАЗАТЕЛЬ ОТКАЗОВ, то для каждой строчки с исходными данными предварительно необходимо рассчитать абсолютное число ОТКАЗОВ. Для этого нужно умножить ПОКАЗАТЕЛЬ ОТКАЗОВ на ЧИСЛО ВИЗИТОВ. После этого у вас появится возможность рассчитывать средний показатель отказов корректно.

Аналогичным образом следует поступить с глубиной просмотра и временем на сайте.

Распространенная ошибка рассчитывать СРЕДНИЙ ПОКАЗАТЕЛЬ ОТКАЗОВ в качестве встроенной меры AVERAGE по столбцу ПОКАЗАТЕЛЬ ОТКАЗОВ. Так средний показатель отказов рассчитывать некорректно.

Эталонное выполнение домашнего задания

Файлы выполнения домашнего задания:

• l5-standard-20220128.pbix
• PQYandexMetrica.1.22-Power.BI.pbix

Шпаргалка курса и ментальная карта, ПДФ, 88 страниц, 13 Мб

Шестой урок из курса Максима Уварова «Power Bi для интернет-маркетинга»

Моделирование данных

Рабочий файл: pbi моделирование данных v2.1d — YM+GA.pbix

Моделирование данных — это создание и настройка структуры таблиц и их связей в базе данных. Во время этого процесса мы определяем, какие данные где должны храниться и как между собой эти данные должны быть связаны.

Пожалуй, лучшие из специалистов по DAX Альберто Феррари и Марко Руссо посвятили отдельную книгу вопросам моделирования данных «Analyzing Data with Power BI and Power Pivot for Excel»
Их книга начинается со слов о том, что зачастую проблемы, с которыми обращаются к ним (как к консультантам) клиенты, когда все необходимые данные для конкретных расчетов загружены в базу данных, но формулы для этих расчетов становятся запредельно сложными, вызваны ошибками архитектуры модели данных. Если эти ошибки устранить, то формулы для необходимых вычислений станут проще и понятнее.

Рассмотрим пример. Нам нужно подсчитать разницу (абсолютную и относительную) в показаниях счетчиков Яндекс.Метрики и Google Analytics, установленных на проекте Marketing-Wiki.ru. Изменение этой разницы нам нужно отследить по годам.

При помощи PQYandexMetrica и PQGoogleAnalytics мы выгрузили две похожих таблички.

В Power Query при помощи команды «Merge Queries» («Объединить запросы») эту операцию сходу сделать не получится, т. к. в таблицах есть несколько строчек с одной датой. И даже если бы получилось, то подобная таблица противоречила принципам tidy data — столбец с визитами дублировался бы дважды.

Будет лучше создать третью таблицу календарь-справочник и связать с ней имеющиеся таблицы по полю дата.

Создать таблицу календарь-справочник можно
в Dax при помощи функций:

• CALENDARAUTO
• CALENDAR

в Power Query при помощи:

• KillerDataTableRU
• List.Dates

На видео создается таблица при помощи функции CALENDARAUTO.

calendar = CALENDARAUTO()

Функция CALENDARAUTO сначала проверяет все столбцы в загруженных в модель данных таблиц и находит столбцы с форматом «Дата».
Далее для каждого найденного столбца функция ищет значение самой ранней и самой поздней даты.
Далее из всех значений она выбирает самую раннюю и самую позднюю дату. И создает календарь, начиная с 1 января года самой ранней даты до 31 декабря года самой поздней даты.

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

Мы можем создать визуализацию «график-линия», перенести в область оси столбец «Date» из таблицы-календаря, а в качестве значений добавить столбцы с визитами из таблиц Яндекс.Метрики и Google Analytics.

Когда мы переносим столбец в область значений, Power BI автоматически создает встроенную меру (implicit measure), в которой для числовых столбцов по умолчанию считает сумму, а для не числовых столбцов считает количество. Использование встроенных мер — это не оптимальная техника.

Лучше создать внешние меры (explicit measures) — меры явно подсчитывающие сумму по столбцу визитов каждой из таблиц.

Далее мы можем создать меры с подсчетом абсолютной и относительной разницы в числе визитов:

_ym-ga sessions = [_ym:sessions] - [_ga:sessions]
_ym/ga sessions = (YM[_ym:sessions] / GA[_ga:sessions])-1

И за весь промежуток времени мы можем видеть следующие показатели.

Преимущество использования таблицы календаря-справочника

Рабочий файл: pbi 5а моделирование данных v2.1b — YM+GA.pbix

Если мы поставим уровень детализации визуализации «график-линия» до дней, то мы можем увидеть, что счетчик Яндекс.Метрики начал собирать данные позже, чем счетчик Google Analytics.

Для честного сравнения нам нужно выбрать только промежуток времени, когда данные поступали в оба счетчика одновременно.

Для этого мы можем создать «Custom Column» в таблице «Calendar» и этот столбец будет проверять, что в конкретную дату у нас есть данные из обоих счетчиков.

FlagThereIsDataInYMAndGA = AND([_ym:sessions]>0;[_ga:sessions]>0)

Когда TRUE — у нас есть данные из обоих счетчиков

После мы можем добавить на нашу визуализацию Slicer с появившимся столбцом «FlagThereIsDataInYMAndGA» и благодаря этому наши цифры будут пересчитаны.

Шпаргалка курса и ментальная карта, ПДФ, 88 страниц, 13 Мб

Четвертый урок из курса Максима Уварова «Power Bi для интернет-маркетинга» — извлечение данных из папки с файлами (xls, csv)

Видеолекции

Раздатка: папка pbi4, 13 Мб

Алгоритм поиска ошибок в запросах Power Query:

1. Добавляете шаг «Оставить ошибки»
2. Таскаете его между другими шагами, пока не найдете шаг, на котором ошибки появились
3. Исправляете ошибку
4. Перетаскиваете шаг «Оставить ошибки» в самый конец запроса. Убеждаетесь, что ошибок нет.
5. Удаляете шаг «Оставить ошибки»

При добавлении нового шага обработки данных, Power Query обновляет все запросы связанные с текущим запросом полностью. На этапе настройки и отладки массивных запросов это может стать проблемой: округлили числа в столбце до первого знака запятой (например), и ждете 3 минуты, пока все шаги обработки данных выполнятся.

В этом видео я рассказываю как используя функцию Keep Top Rows в начале массивного запроса можно ограничить количество строк — и ускорить обработку на момент отладки запроса, а когда запрос будет готов — чтобы удалить этот ограничивающий шаг.

Домашнее задание

Материалы: папка NFDPBI — v20190728

Создайте новую BI-систему

Распакуйте файлы из архива к домашнему заданию

Скопируйте запрос «CampaignNames» из вашей pbix к 3-ему уроку и вставьте в текущий PBIX файл. Короткое видео как копировать запросы [здесь](https://www.facebook.com/maximuvarov.ru/videos/608365746195970/).

Выключите загрузку запроса «CampaignNames» в модель данных.

Объедините файлы из папки XLS в единый запрос «StatRegionsXls»

Присоедините названия рекламных кампаний из запроса «CampaignNames» к данным запроса «StatRegionsXls».

Приведите все типы данных у столбцов в запросе «StatRegionsXls» в порядок. В случае если понадобится в отдельных столбцах замените ошибки на _null_ при помощи функции «Replace errors»

Создайте запрос «StatRegionsXlsErrorsCheck», который будет ссылаться на запрос «StatRegionsXls», и в котором будет использована функция «Keep Errors». Этот запрос должен быть пустой. Если он не пустой, то в запросе «StatRegionsXls» необходимо избавиться от ошибок и добиться того, чтобы запрос «StatRegionsXlsErrorsCheck» содержал 0 строчек.

В названии рекламных кампаний есть сегменты Network и Region. В кампаниях с сегментом «Network» «Sea» показы должны быть только на поиске, с сегментом «Net» — только в партнерской сети. В кампаниях с сегментом «Region» «Msk_mo» должны быть показы только в Москве и Московской области, «Spb» — только в Питере и Ленинградской области, в «all» — во всей РФ, кроме Москвы, Московской области, Питера и Ленинградской области.

Имея это в памяти, выполните следующие задания.

Создайте запрос «ErrorsNetworkInSeaCampaigns», который будет ссылаться на запрос «StatRegionsXls» — в котором должны быть только столбцы с уникальными id рекламных кампаний, их полными названиями, значениями столбца из запроса «StatRegionsXls» «Тип площадки» и Суммарное значение по полю «Показы». В запросе должны быть данные по рекламным кампаниям с сегментом «Sea» которые имеют показы в партнерской сети.

Создайте запрос «ErrorsSearchInNetCampaigns», который будет ссылаться на запрос «StatRegionsXls» — в котором должны быть только столбцы с уникальными id рекламных кампаний, их полными названиями, значениями столбца из запроса «StatRegionsXls» «Тип площадки» и Суммарное значение по полю «Показы». В запросе должны быть данные по рекламным кампаниям с сегментом «Net» которые имеют показы на поиске.

Создайте запросы «ErrorsRegionMsk_mo», «ErrorsRegionSpb», «ErrorsRegionAll» , которые будут ссылаться на запрос «StatRegionsXls», в которых найдите рекламные кампании с ошибочным таргетингом. В запросе «ErrorsRegionMsk_mo» должны быть кампании, которые имеют показы в регионах кроме Москвы и Московской области, в запросе «ErrorsRegionSpb» должны быть кампании, которые имеют показы в регионах кроме Санкт-Петербурга и Ленинградской области, в запросе «ErrorsRegionAll» должны быть кампании с показами в Москве, Московской области, Санкт-Петербурге и Ленинградской областях. В таблицах должны быть только столбцы с уникальными id рекламных кампаний с неправильным таргетингом и их полными названиями.

Желательно сделать это алгоритмически а не вручную выбирая ошибочные регионы. Но если алгоритмически не получается — делайте вручную. Этот пункт сложный. Как делать — думайте сами. Если не сделаете — не страшно. Если сделаете — опишите в комментариях к сданной работе, какой алгоритм вы выдумали.

Создайте визуализации «таблица» на каждый из запросов «ErrorsNetworkInSeaCampaigns», «ErrorsSearchInNetCampaigns», «ErrorsRegionMsk*mo», «ErrorsRegionSpb», «ErrorsRegionAll» и выведите там все поля из таблиц. Обратите внимание, что эти визуализации могут быть пустыми, т. к. Не везде есть ошибки.

В Dax, в таблице «StatRegionsXls» создайте меры «средняя позиция показов взвешенная» — взвешенной на показы, и «средняя позиция кликов взвешенная» взвешенная на клики.

Отформатируйте меры «*Средняя позиция показов взвешенная» и «*Средняя позиция кликов взвешенная» как десятичные числа с округлением до второго знака после запятой.

Создайте визуализацию «Таблица» в которой будут столбцы «Region» (из названия рекламной кампании), «Сумма кликов», «Сумма показов», «*Средняя позиция показов взвешенная» и «_Средняя позиция кликов взвешенная»

Эталонное выполнение

Файл с выполненным заданием

Шпаргалка курса и ментальная карта, ПДФ, 88 страниц, 13 Мб