Сводные таблицы + Plarin

Анализ статистики рекламных кампаний ВКонтакте и myTarget из системы Plarin, с помощью сводных таблиц Excel

Пошаговая инструкция
Введение
Plarin позволяет выгрузить статистику по рекламным кампаниям в формате таблиц Excel. При этом данные выгружаются за любой выбранный период.

Наиболее удобный инструмент для дальнейшей работы с выгрузкой из Plarin- сводные таблицы Excel. Сводные таблицы помогут построить срезы данных, вычислить новые метрики на основе исходных данных, визуализировать данные с помощью диаграмм.

Даже если вы не знакомы со сводными таблицами, но владеете базовыми навыками работы в Excel. Прочитав данную инструкцию, вы научитесь работать со сводными таблицами для анализа выгрузки из Plarin.

Инструкция
Для дальнейшей работы нам понадобятся
1. MS Excel - при написании данной инструкции использовался Excel 2013
2. Выгрузки из Plarin в формате .xls - для обучения вы можете воспользоваться тестовыми выгрузками. Скачайте выгрузки по этой ссылке.

Далее выполняйте все действия шаг за шагом, согласно инструкции.
1. Подготовка данных
В данном примере будем работать с выгрузкой статистики по объявлениям. Скачать файл.

1. Откройте файл с выгрузкой. Встаньте на любую заполненную ячейку. Нажмите сочетание клавиш CTR + A, затем CTR + T
2. Далее выберите пункт Вставка -> Сводная таблица и нажмите ОК.

На данном этапе мы преобразовали диапазон данных в таблицу и создали на её основе сводную таблицу.
Познакомимся с основными объектами сводной таблицы Excel.

1. Область, в которой будут отображаться созданные таблицы.
2. Перечень полей доступных для добавления в сводную таблицу и вычисления производных метрик (вычисляемых полей). Также в этой области будут отображаться созданные вычисляемые поля
3. Область, перетаскивая в которую поля с данными формируется сводная таблица. Эта область делится на 4 блока
  • Фильтры - если разместить поле с данными в блок Фильтры, то появляется возможность фильтровать таблицу по этому полю.
  • Строки - поля размещённые в этом блоке будут являться строками сводной таблицы.
  • Значения - поля размещённые в этом блоке будут являться метриками, вычисляемыми для строк.
  • Колонны - изменение структуры таблицы
2. Формирование сводных таблиц
На первом этапе мы создали сводную таблицу на основе выгрузки из Plarin.

Далее научимся:
  • Формировать отчёты
  • Форматировать таблицы для удобного восприятия данных
  • Вычислять метрики на основе существующих данных
  • Строить диаграммы на основе построенных отчётов
Формирование отчётов
Сформируем первый отчёт и посмотрим статистику по кампаниям и объявлениям.
1. Перетащите поля Название кампании и ID объявления в блок СТРОКИ.
2. Перетащите поля Расход, Показы, Клики, Конверсии в блок ЗНАЧЕНИЯ.

Обратите внимание, что над данными в блоке ЗНАЧЕНИЯ произвелось суммирование для каждой строки отчёта. Однако кроме суммирования возможны и другие вычисления.
Форматирование таблицы
Далее для того чтобы привести отчёт в более удобный вид слегка отформатируем его.

1. Свернём все строки. Выделите любую строку, далее правый клик -> Развернуть/свернуть -> Свернуть все поле.
2. Отсортируем таблицу по полю Расход. Выделите любую ячейку в поле Расход, далее правый клик, Сортировка -> Сортировка по убыванию.
3. Отформатируем значение полей. Приведём Расход в денежный вид, а остальные поля в числовой.

Выделите весь столбец с полем Расход. Далее правый клик, Формат ячеек. В диалоговом окне выберите Денежный. Выберите число десятичных знаков 0, округлим с точностью до рубля. Жмём ОК.

Выделите столбцы Показы, Клики, Конверсии и отформатируйте следующим образом. Выделили 3 столбца, далее правый клик мышкой, Формат ячеек. Выберите Числовой, укажите 0 в Число десятичных знаков, поставьте галочку в Разделитель групп разрядов.

4. Переименуем столбцы, при формировании названий столбцов, Excel добавляет в название тип вычисления над значениями. В итоге названия получаются длинными и неудобными. Для переименования, встаём на заголовок поля и пишем новые названия столбцов _Расход, _Показы, _Клики, _Конверсии.

В итоге мы получили наш первый отчёт, при этом он отформатирован в удобном для восприятия виде.
Вычисление метрик
На предыдущих этапах мы сформировали отчёт, привели его в удобный для восприятия вид. Далее усложним задачу и добавим в наш отчёт новые метрики, которые будут вычисляться на основе существующих метрик Расход, Показы, Клики, Конверсии.

Для примера создадим следующие метрики:
CPC - стоимость клика
CR - коэффициент конверсии
CPA - стоимость конверсии
CTR - коэффициент кликабельности объявления.

Создадим метрику CPC, остальные делаются по аналогии

1. Встаём на любую ячейку отчёта, выбираем в меню Анализ -> Поля, элементы и наборы -> Вычисляемое поле.
2. В появившемся диалоговом окне создаём новую метрику. В терминах Excel эти метрики называются вычисляемыми полями.

Заполняем поля в диалоговом окне.
Имя - пишем _CPC (хорошей практикой считается начинать названия вычисляемых полей со знака нижнего подчёркивания, это позволяет отличать вычисляемые поля от обычных).
Формула - пишем формулу вычисления CPC, выбирая нужные поля из блока Поля.

Жмём кнопку Добавить и ОК.

Вычисляемое поле появилось в отчёте. Сразу приведите его к денежному формату, и переименуйте в __CPC. Названия столбцов не должны совпадать с названиями вычисляемых полей, поэтому поставьте перед названием ещё одно нижнее подчёркивание.

Для того чтобы избавиться от ошибок деления на ноль, поменяйте формулу для вычисляемого поля вот на эту =ЕСЛИОШИБКА(Расход/Клики;Расход )

Для этого откройте Анализ -> Поля, элементы и наборы -> Вычисляемые поля. В диалоговом окне в поле Имя, в списке выберите наше поле _CPC, далее поменяйте формулу в поле Формула. Нажмите ОК для сохранения изменений.

Далее по аналогии создайте оставшиеся вычисляемые поля.

CPC - =ЕСЛИОШИБКА(Расход/Клики;Расход)
CR - =ЕСЛИОШИБКА(Клики /Показы;0), отформатируйте как %
CPA - =ЕСЛИОШИБКА(Расход /Конверсии;Расход )
CTR - =ЕСЛИОШИБКА(Клики /Показы;0 ), отформатируйте как %.
Построение диаграмм
Мы создали отчёт, содержащий некоторые метрики и измерения из выгрузки, а так-же новые метрики на основе вычисляемых полей.

Далее создадим ещё один отчёт и диаграмму на его основе.

Создадим отчёт и диаграмму, отображающий расход по дням.

1. Вернитесь на лист Sheet1 с исходными данными. По аналогии с первым отчётом создаём новую сводную таблицу. Вставка -> Сводная таблица -OK.

2. В блок СТРОКИ перетащите поле Дата, в блок ЗНАЧЕНИЯ перетащите поле Расход.

Мы создали новый отчёт, отображающий расход по датам.

3. Выделив любую ячейку отчёта открываем меню Вставка - Сводная диаграмма. Выберите вид диаграммы График и нажмите ОК.

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

Временная шкала
Выделите диаграмму, далее выберите в меню Анализировать -> Вставить временную шкалу. Далее в диалоговом окне выберите поле Дата.

Появилось новое диалоговое окно которое позволяет фильтровать отчёт по дате. Выберите в верхнем правом углу шкалы, пункт ДНИ вместо месяцев, и попробуйте его изменить. В результате изменится содержимое отчёта и диаграммы.

Группировка дат
Благодаря группировке по полю Дата, вы можете проанализировать данные укрупнёнными периодами.

Кликните по ячейке в поле Дата правой кнопкой мыши, выберите Группировать.

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

Я выберу группировку по дням и укажу период 7 дней.
Видеоинструкция
В данном видеоролике я продемонстрирую все действия, описанные в инструкции выше.

Скачайте файл со сводными таблицами, описанными в данной статье ссылка.
Выводы и комментарии
1. Анализируйте статистику в различных срезах.
2. Создавайте собственные метрики, которых вам не хватает в стандартной статистике.
3. Визуализируйте отчёты с помощью диаграмм.
4. Используйте однажды созданные сводные отчёты повторно. Просто обновив данные в исходной таблице.


5. Любите Excel и да пребудут с вами красивые отчёты, полные инсайтов.