Excel,  Excel прочее

Семинар Уваров по Excel с Power Query и Power BI

/

Семинар Макса Уварова по Excel и PowerBI

Примерная программа и раздаточный материал с семинара

День 1. Работа со сводными таблицами

Подготовка

Анализ данных методами сводных таблиц

Работа с Power Query

Работа со сводными таблицами

Работа с Data Model и Power Pivot

День 2. Визуализация данных в Power BI

Работа с Data Model и Power Pivot (продолжение)

Визуализация данных в Power BI

День 3. Работа с семантикой

Обзор инструментов Need 4 Data

Работа с семантикой

Дополнительно

День 4. Аудитор для рекламных кампаний

День 1. Работа со сводными таблицами

Подготовка

Анализ данных методами сводных таблиц

  1. Создание «умной» таблицы, переименование умных таблиц — видео (4:19), исходный файл для анализа, версия 1
  2. Создание сводной таблицы на основе «умной» — видео (1:42)
  3. Настройка сводной таблицы, вывод данных — видео (4:00)
  4. Изменение ширины столбцов — видео (2:09)
  5. Сохранение сводных данных — видео (1:12)
  6. Подсчёт количества ключевых слов с определённым числом кликов — видео (2:51)
  7. Подсчёт количества кликов по группам ключевых слов — видео (1:26)
  8. Анализ распределения кликов по ключевым словам в кампании — видео (1:00), результат, версия 2
  9. Группировка и фильтрация данных в сводной таблице — видео (7:27) 
  10. Изменение параметров подсчёта (на примере подсчёта кол-ва конверсий) — видео (3:20)
  11. Фильтрация данных по значениям (Value Filter) — видео (6:53), результат, версия 3
  12. Вычисляемые поля, подсчёт коэффициента и стоимости конверсий (CR и CPO) — видео (8:01)
  13. Анализ результатов подсчёта, представление результатов (сумма, % от суммы по столбцу) — видео (5:18), результат, версия 4
  14. Визуализация данных (часть 1) — видео (4:13)
  15. Изменение порядка дат, разделение текста по столбцам, визуализация данных (часть 2) — видео (7:08)
  16. Инструмент для анализа тенденций рынка — рекламные технологии Яндекса. видео (3:41)

Работа с Power Query

  1. Анализ сезонности по сегментам рынка, часть 1 — загрузка данных в Power Query из LiveInternet — статистика по медицине (.CSV), видео (5:20)
  2. Разделение столбцов в Power Query, импорт данных в Excel — видео (4:59), результат, версия 5
  3. Сравнение данных сайта со средней статистикой по Рунету (1 источник) — видео (3:50)
  4. Сравнение данных сайта со средней статистикой по Рунету (2 источника) — статистика Вордстата по запросу «Неврология», видео (6:39)
  5. Визуализация данных, диаграммы — видео (2:55), результат, версия 6

Работа со сводными таблицами

  1. Распределение кликов и конверсий по кампаниям — видео (1:49)
  2. Распределение кликов и CPO по типам таргетинга — видео (5:16)
  3. Распределение кликов и CPO по нескольким параметрам — таргетинг и тип РК (специалистов на примере МЗ) — видео (7:07)
  4. Копирование сводных таблиц, сравнение данных по годам — видео (2:10), результат, версия 7
  5. Группировка ключевых слов по CPO и количеству кликов — видео (3:40)
  6. Группировка ключевых слов по наличию кавычек — видео (3:30), результат, версия 7
  7. Группировка ключевых слов по нескольким параметрам (таргетинг, кавычки, тип кампании), выводы — видео (5:11)
  8. Заполнение дат с текущей до до определенного дня — видео (2:17) (вырезаны ошибки)
  9. Группировка и разгруппировка данных в сводных таблицах на примере дат — видео (6:27)
  10. Группировка и разгруппировка данных в сводных таблицах на примере количества кликов по объявлениям — видео (3:14), результат, версия 8

Работа с Data Model и Power Pivot

  1. Создание сводной таблицы с сохранением данных в Data Model, отличия сводных таблиц в Data Model — видео (3:16), результат, версия 9
  2. Начало работы с Data Model и Power Pivot — запуск, замена данных, вычисление коэффициента конверсии — видео (8:15)
  3. Вычисление CPO, форматирование результатов вычислений, вывод результатов в сводную таблицу — видео (2:17)
  4. Создание связей между таблицами в Power Pivot (на примере целевого расхода) — видео (11:14), результат, версия 10
  5. Работа со связанными таблицами, подсчёт прибыли или убытка от РК — видео (4:42)
  6. Работа с ключевыми словами, фильтрация по наличию кавычек и наличию минус-слов, подсчёт количества слов в ключе (неполное) — видео (14:50), исходный файл (версия 11) со словарем по ключевым словам, результат, версия 12

Формулы из этапа №37

Подсчет количества слов=LEN([@[Условие показа]])-LEN(SUBSTITUTE([@[Условие показа]];» «;»»))+1
Есть минус-слова=IFERROR(IF(SEARCH(» -«;[@[Условие показа]])<>0;»есть минус-слова»);»нет минус-слов»)
  1. Перезапуск Power Pivot (если не работает кнопка Manage Data Model) — видео (3:53)
  2. Подсчет количества слов в ключевой фразе (часть 2, уточнение) — видео (3:53)
  3. Связи между таблицами, анализ зависимости CPO от параметров ключевых фраз (кол-во слов, наличие кавычек и минус-слов) — видео (6:53)
  4. Расчёт стоимости конверсии по отдельным ключевым словам — видео (7:47)
  5. Подсчёт количества уникальных значений (уникальных ключевых слов по кампаниям) — видео (5:20)
  6. Зависимость стоимости заказа от месяца (показ значений как % difference from), условное форматирование — видео (7:52), результат, версия 15

День 2. Визуализация данных в Power BI

Работа с Data Model и Power Pivot

Исходный файл, версия 15

  1. Вычисление глубины просмотра и показателя отказов в абсолютных значениях (Вспоминаем инструменты Power Query, меняем формат данных, округляем, умножаем относительные показатели на количество кликов для получения абсолютных значений) — видео (22:03)
  2. Работа с этапами обработки в Power Query, ограничение выборки для ускорения обработки (Возвращаемся к первому этапу обработки данных, используем Keep Top Rows, скрываем часть строк, проверяем на наличие ошибок, меняем формат данных на различных этапах работы) — видео (9:00), результат, версия 16
  3. Изменение параметров обработки, расчёт позиций на показы и на клики (значение показателей — в след. этапе) — видео (9:57)
  4. Сохранение данных из Power Query в Data Model (Изменяем параметры сохраненной таблицы Query, задаём Soursе, куда помещаем данные) — видео (3:29)
  5. Изменение данных, исправление ошибок в Power Pivot (Если нашли ошибку в Power Pivot — возвращаемся в Power Query, меняем формат данных или название таблицы, обновляем данные в Power Pivot) — видео (5:51)
  6. Расчёт средней позиции показов и кликов в Power Pivot (вспоминаем работу с вычисляемыми полями из 1 дня семинара) — видео (5:48)
  7. Анализ средней позиции показов и кликов, фильтрация кампаний (создаем сводную таблицу на основе данных из Power Pivot, фильтруем значения, анализируем средние позиции показов и кликов) — видео (9:54), исходный файл, версия 17
  8. Работа с таймлайном, срезы по определенному промежутку времени (добавляем таймлайн, смотрим как изменяются показатели с изменением времени) — видео (2:49)
  9. Наводим порядок, удаляем лишние листы — видео (2:46), результат, версия 18
  10. Расчёт стоимости конверсий (Считаем CPO, сравниваем статистику CPO и средней позиции кликов в разных РК) — видео (5:33), результат, версия 19

Формула из этапа №10

CPO:=IFERROR([Sum of Расход (у.е.) 2]/[Sum of Конверсии 2];[Sum of Расход (у.е.) 2])

  1. Расчёт доли показов в спецразмещении (разбиваем все показы на спецразмещение и другие блоки, объединяем столбцы даты, условия и номера объявления, считаем процент спецразмещения от всех показов) — видео (22:08), промежуточный файл, версия 20, результат, версия 21
  2. Объединение таблиц в Power Pivot (создаем связи между таблицами в Power Pivot по общему столбцу) — видео (8:57)
  3. Расчёт доли показов в спецразмещении (исправленная, корректная формула) — видео (4:17), результат, версия 22

Правильная формула из этапа №13

доляПоказовВСпецразмещении3:=SUMX(‘статистика_директ_pq’;IF([Позиция]=»спецразмещение»;[Показы]))/[Sum of Показы]

Визуализация данных в Power BI

  1. Начало работы в Powe BI, импорт данных (Основные элементы интерфейса, загрузка данных из Excel) — видео (8:32)
  2. Расчёт и визуализация стоимости клика (CPC) (Изменение данных Query внутри Powe BI, работа с формулами, вывод диаграммы, изменение параметров даты и визуализации) — видео (3:44)
  3. Визуализация CPC по нескольким кампаниям (добавляем дополнительный параметр «Кампания» на ось) — видео (1:29)
  4. Визуализация, расходы по кампаниям, взаимосвязь между диаграммами — видео ()
  5. Создание дашборда с основными показателями кампаний (Визуализируем CPO, CPC, среднюю позицию показов и кликов, долю показов в спецразмещении по кампаниям, изменяем размеры шрифтов, проверяем показатели по разным кампаниям) — видео (4:03)
  6. Визуализация стоимости заказа разными способами и диаграммами (смотрим, как меняется стоимость заказа в разных кампаниях) — видео (1:49)
  7. Импорт данных из Директ.Коммандера (Добавляем данные из Директ.Коммандера к имеющимся данным, настраиваем связи, определяем тип данных, разбиваем данные на несколько таблиц — по кампаниям, фразам и объявлениям, удаляем дубликаты) — видео (24:09), Исходный файл, CSV, результат, PBIX
  8. Подсчёт уникальных фраз, заголовков и текстов объявлений (Создаём связи между таблицами, выводим нужные данные в таблицу, визуализируем результаты подсчёта) — видео (4:56)
  9. Подсчёт количества минус-слов на фразу (добавляем новый столбец, отделяем фразу от минус-слов) — видео (17:22), результат, версия 2

Формула из этапа №22 

if [#»минус-слова на фразу»] = null

then 0

else List.Count(Text.Split([#»минус-слова на фразу»], » «))

  1. Проверка наличия кавычек в поисковой фразе (Выводим первый символ в отдельный столбец, создаем условие «есть/нет кавычки») — видео (5:03)
  2. Подсчёт количества слов в поисковой фразе (За основу берем формулу для подсчёта количества минус-слов, загружаем данные в Power BI) — видео (3:07)
  3. Визуализация данных, подсчёт фраз с кавычками по кампаниям — видео (4:43)
  4. Визуализация данных по количеству фраз с минус-словами — видео (11:25)
  5. Замена расположения файла при открытии файла PBIX (Заменяем Source исходного файла с данными: Query → Home → Advanced Editor) — видео (5:41)
  6. Объединение таблицы со статистикой и данных из Коммандера, зависимость эффективности от наличия минус-слов и кавычек (Убираем букву M из ID объявлений, связываем таблицы «Статистика директ PQ» и данные из Коммандера по ID объявления, считаем количество кликов по фразам с кавычками и минус-словами) — видео (8:07)
  7. Анализ эффективности (CPO, клики) кампании по страницам приземления (Разбиваем URLы страниц при помощи функции Uri.Path, в поле значений выводим показатели эффективности, смотрим распределение CPO и кликов по страницам) — видео (14:01)
  8. Подсчёт количества уникальных фраз по различным заголовкам, фильтрация по рекламным кампаниям (меняем параметр Path на параметр «заголовок», считаем количество уникальных ключевых фраз по каждому заголовку) — видео (4:40)
  9. Вывод сводных данных по кампаниям (Считаем количество уникальных заголовков, текстов и поисковых фраз, выводим в отдельную визуализацию) — видео (2:41), результат, версия 4

День 3. Работа с семантикой

Обзор инструментов Need 4 Data

  1. Обзор инструментов NFD. Готовые таблицы для аналитики рекламных кампаний — группировка поисковых запросов — видео (6:54)
  2. Обзор сводной таблицы для анализа эффективности ключевых слов. На какие данные следует обратить внимание при аналитике рекламной кампании, как их трактовать и как получить — видео (15:09)
  3. Анализ стоимости заказа, CTR, ставок и отказов по группам ключевых слов, взаимозависимость показателей эффективности — на примере крупного e-commerce — видео (15:56)
  4. Отступление на тему «Что делать если запрос попадает в 2 РК?» — видео (9:08)
  5. Отступление на тему «Что такое рекламная кампания?» — видео (3:51)
  6. CRM-механики. Анализ рекламы в соцсетях — видео (5:35)
  7. Модель аукциона Яндекс.Директ. Расчёт позиции и стоимости клика в аукционе VCG — видео (9:38), Исходный файл
  8. Модель аукциона Яндекс.Директ. Анализ зависимости позиции и стоимости клика от ставок и CTR конкурентов — видео (9:28)
  9. Загрузка нескольких CSV файлов в Power Query из папки — видео (11:34), исходный файл CSV works (.ZIP)
  10. Загрузка нескольких CSV файлов в Power BI из папки — видео (4:59)
  11. Форматирование выгрузки в Power Query, исправление ошибок автоматической загрузки (делим столбцы по разделителю, удаляем дубликаты и лишние колонки) — видео (8:54)
  12. Импорт и склеивание нескольких XLSX файлов из папки (импортируем таблицы, удаляем лишние столбцы, меняем формат данных, меняем размещение файла на переменную (filepath) => в расширенном редакторе) — видео (27:22), исходный файл XLS works (.ZIP)
  13. Загрузка данных из метрики в Excel (Меняем настройки безопасности, получаем уникальный токен для своего аккаунта, подключаем счётчик) — видео (16:55), исходный файл, XLSM 
  14. Загрузка сконфигурированных отчетов из Метрики в Excel (На листе ConfigFromURL добавляем URL отчёта из Метрики, импортируем данные в сводную таблицу с помощью формулы getDataFromMetrikaFunction) — видео (7:47)

URL счетчика из примера (этап №14):
https://metrika.yandex.ru/stat/new?selected_rows=Luedqn%252CJKFzPK%252CM7iK79%252CDb5SnE%252CnzOnx5&period=2015-05-01%3A2015-08-30&metrics=ym%3As%3Avisits%2Cym%3As%3Ausers%2Cym%3As%3AbounceRate%2Cym%3As%3ApageDepth%2Cym%3As%3AavgVisitDurationSeconds%2Cym%3As%3AconversionRateGroup7168287reaches%2Cym%3As%3Apageviews&dimensions=ym%3As%3A%3Cattribution%3ETrafficSource%2Cym%3As%3A%3Cattribution%3ESourceEngine%2Cym%3As%3AstartURLHash&id=21781912

  1. Загрузка данных из метрики в Power BI (импортируем XLS файл с помощью формулы getDataFromMetrikaFunction) — видео (8:54) 

Работа с семантикой

  1. Начало работы с JustMagic. Расширение семантического ядра (Знакомимся с основными инструментами, загружаем список запросов, анализируем результат авторасширения) — видео (9:34), результат, XLS
  2. Кластеризация семантического ядра в Power Query (Загружаем расширенное семантическое ядро в Power Query, объединяем исходный и расширенный список запросов) — видео (7:41)
  3. Кластеризация запросов в JustMagic и Яндекс.Метрики. Анализ соответствия объявления кластеру запроса — видео (5:50)
  4. Лемматизация поисковых запросов с помощью PY7 (Знакомимся с сервисом, получаем список лемм для семантического ядра, анализируем результаты) — видео (8:41)
  5. Лемматизация поисковых запросов с помощью робота-распознавателя (Получаем леммы, сортируем, формируем частотный словарь в Power Query — сколько раз слово встречалось в поисковых запросах, добавляем новые кластеры) — видео (24:37), исходный файл для анализа (.CSV), последняя версия робота-распознавателя, результат
  6. Словарь N-gram, обзор инструмента (сколько раз в семантическом ядре встречаются комбинации из отдельных слов) — видео (10:28), исходный файл, скачать N-gram
  7. Система генерации объявлений K50, обзор инструмента (загружаем файл с нужной кодировкой, подключаем необходимые поля и генерируем объявления) — видео (12:52), исходный файл .CSV, исходный файл .XLS
  8. Работа с тегами и склонениями в K50 (Генерация и склонение тегов в шаблонах объявлений K50, обрезка количества ключевых слов в запросе, добавление UTM-меток) — видео (6:48)
  9. Создание дочерних кампаний в K50 (создаём кампанию с отдельным таргетингом или дубль кампании для показа в определенный временной период) — видео (2:45)
  10. Создание правил и фильтрация в K50, недостатки инструмента (Создаём правила и условия на основе фильтров и полей исходного файла, особенности и недостатки K50) — видео (4:16)
  11. Дополнительные вопросы по K50 (Добавление минус-слов, работа с 404 страницами, настройки региональных кампаний, работа с вычисляемыми полями) — видео (11:12)

Дополнительно

  1. Условное форматирование в модели аукциона Яндекс.Директа (Выделяем цветом рекламодателя с определённой поисковой позицией) — видео (7:13)

День 4. Аудитор для рекламных кампаний

На основании статьи А. Белоусова

  1. Подсчет математического ожидания и оценки вероятного CTR — видео (15:24), исходный файл, версия 22
  2. Анализ результатов подсчёта вероятности. Выводы о качестве объявлений. (Строим график и смотрим, как зависит вероятный CTR от количества испытаний (показов) — видео (13:50)
  3. Оценка вероятной конверсии на основе CTR (Заменяем нулевые значения на средние для объявлений с недостаточным количеством показов) — видео (15:37)
  4. Оценка вероятного CTR и конверсии на различных площадках в Power BI (Готовим сводную таблицу: группируем данные по номеру кампании и рекламной площадке, считаем суммы показов и кликов, считаем математическое ожидание, делим таблицы по рекламным площадкам и сравниваем) — видео (25:49), исходный файл auditor 8
  5. Визуализация вероятного CTR и конверсии на различных площадках в матрице Power BI (На основе сводной таблицы из предыдущего этапа) — видео (11:55)
  6. Группировка ключевых слов по величине CTR (Формируем группы по 10% слов с максимальным и минимальным CTR) — видео (33:44)
  7. Импорт данных из Google таблиц в Power Query и Power BI — видео (5:26)
от  Комментарии к записи Семинар Уваров по Excel с Power Query и Power BI отключены