Кейс 8. Сводные таблицы и расчеты

Решая данный кейс, вы разберете много важных элементов формирования отчетов.
В данном разделе мы произведем анализ расходов рекламных кампаний.

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

Анализ расходов рекламных кампаний, создание и настройка сводных таблиц.


Задача 1: По данным о расходах на рекламные кампании в интернете и данным о доходах из этих источников необходимо сделать отчет, в котором должны быть представлены данные в разрезе показателей:
- Сессии, Транзакции, CR, Расходы, Доходы, CPO, ROI

Задача 2: Создать правила условного форматирования для итоговой таблицы,
Построить 2 диаграммы:
- Круговая диаграмма по доходам, Гистограмма ROI в разрезе каналов

Имеются следующие данные:
  • Таблица с расходами по рекламной кампании на Google Adwords:
  • Таблица с расходами по рекламной кампании на Yandex Direct:
  • Таблица с информацией о доходам с группировкой по источникам рекламы:
Задачи
1. Привести структуру таблиц расходов к одному виду:
2. Подтянуть информацию о доходах в обе таблицы.
3. Рассчитать показатели CR, CPO, ROI для каждой рекламной кампании.
4. Объединить данные в одну таблицу.
5. Сформировать сводные таблицы, настроить условное форматирование и вывести графики.
1. CR (коэффициент конверсии) = (Количество показов / Количество кликов) * 100%
2. CPO (стоимость покупки) = Расходы / Количество показов
3. ROI (рентабельность) = (Доходы - Расходы) / Расходы
1
Делаем расчеты и приводим в заданный вид данные о расходах в Google Adwords
1.1. Добавляем столбцы в таблицу
1.2. Считаем коэффициент конверсии CR
  • В данном случае количество кликов это столбец "взаимодействия". Вводим формулу: =(E3/F3)*100
  • В некоторых ячейках видим ошибку, которая появляется при делении на нуль "0"
  • Для того, что бы избавиться от этой ошибки воспользуемся логической функцией "ЕСЛИОШИБКА"
Функция "ЕСЛИОШИБКА" в случае ошибки присваивает заданное значение ячейке
Аргументы
Описание
Значение
Ячейка, которую необходимо проверять на наличие ошибок
Значение если ошибка
Значение, которое нужно отображать вместо ошибки
1.3. Добавляем столбцы в таблицу
1.4. Расходы
  • Открываем "Мастер функций" и в категории логических функций находим "ЕСЛИОШИБКА"
  • В поле "Значение" вписываем формулу CR
  • В поле "Значение если ошибка" вписываем "0"
  • Нажимаем "ОК" и протягиваем до конца таблицы
  • В данном случае расходы это стоимость, поэтому вписываем формулу: =О3
  • Протягиваем формулу до конца таблицы
1.4. Доходы
  • Встаем на ячейку "I2" (Доходы), заходим в мастер функций и выбираем функцию "СУММЕСЛИМН"
  • Для поля "Диапазон суммирования" указываем на листе "Доходы" столбец "F" (Total Value)
  • Для поля "Диапазон условия1" указываем на листе "Доходы" столбец "С" (Campaign)
  • Для поля "Условие 1" указываем столбец "В" таблица с расходами по рекламной кампании Гугл
  • Для поля "Диапазон условия2" указываем на листе "Доходы" столбец "А" (Source / Medium)
  • Для поля "Условие 2" указываем значение "google/ cpc". По этому значения мы выберем данные по расходам на рекламу в Google Adwords.Обязательно указывать текстовое значение в кавычках.
Таблица "Доходы" содержит информацию о расходах по нескольким рекламным кампаниям с детализацией до уровня дня. Для того, что бы получить данные о расходах каждой рекламной кампании Google Adwords воспользуемся функцией "СУММЕСЛИ".
  • Нажимаем "ОК" и протягиваем до конца таблицы.
1.5. Считаем CPO (расходы/количество показов)
  • Вводим в ячейке J3 формулу: =H3/E3, протягиваем до конца таблицы
В данном случае так же имеет место быть ошибка, поскольку в качестве делителя используются нулевые значения.
Некоторые ячейки содержат значения "#ДЕЛ/0!". Эта ошибка возникает при делении на нуль, поскольку многие значения расходов нулевые.
Воспользуемся функцией "ЕСЛИОШИБКА" и уберем не числовые значения
Напомним, что эта функция имеет следующий вид: =ЕСЛИОШИБКА(Значение; Значение если ошибка). В качестве значения нужно указать введенную нашу функцию "ВПР". Именно значения этой функции мы будем проверять на наличие ошибок.Таким образом вид измененной формулы: =ЕСЛИОШИБКА(Н3/Е3; 0 ).
Протягиваем вниз и получаем диапазон числовых значений.
1.6. Считаем ROI (Доходы - Расходы) / Расходы
  • Считаем значение показателя в связке с функцией "ЕСЛИОШИБКА", поскольку столбец расходы(делитель) содержит нулевые значенияТаким образом, формула будет иметь следующий вид: =ЕСЛИОШИБКА((I3-H3)/H3;0)
  • Протягиваем вниз и получаем столбец, содержащий только числовые значения
1.7. Удаляем все лишнее и оставляем только значения без формул
  • Нажимаем левой кнопкой мыши в левый верхний угол таблицы. Выделяется вся область листа. Копируем ее и вставляем значениями на новый лист через инструмент "Специальная вставка".
  • Для этого встаем на ячейку нового листа, нажимаем правой кнопкой мыши и выбираем команду "Специальная вставка".
  • Так же это можно было сделать, выбрав соответствующий параметр вставки:
  • Присваиваем листу на который вставили значение наименование "Данные"
  • Выбираем операцию вставки значений
2
Делаем расчеты и приводим в заданный вид данные о расходах в Yandex Direct
Столбец "В" таблицы содержит информацию о наименованиях рекламных кампаний. Для каждой рекламной компании имеются данные о расходах с детализацией до уровня дня. Наименование кампании содержится только рядом с первой датой периода.
Для дальнейшей обработки данных нам необходимо сделать элементы таблицы взаимосвязанными. Для этого нужно заполнить каждую пустую строку столбца "В" соответствующими наименованиями рекламных кампаний.
  • Удаляем первую строку целиком
  • Выделяем весь столбец "В"
  • На вкладке меню "Главная" выбираем команду "Найти и выделить" и выбираем "Выделить группу ячеек"
  • В появившемся диалоговом окне выбираем пункт "Пустые ячейки" и нажимаем "ОК"
  • Выделяются все пустые ячейки
  • В строке формул вводим координаты первой не пустой ячейки, значением которой нужно заполнить ячейки ниже нее.
  • Нажимаем комбинацию CTRL+Enter
  • Пустые ячейки заполнились значениями
Делаем то же самое для столбца "А". Таблица содержит строки с итогами по каждой рекламной кампании, их нужно удалить, что бы они не задвоили данные в итоговом расчете.
  • Ставим фильтр на заголовок
  • В заголовке наименования выбираем значения фильтра "по всем кампаниям"
  • Нажимаем "ОК" и удаляем фрагмент отобразившейся таблицы кроме заголовка
  • Далее переходим к фильтру заголовка "Дата" и выбираем поля "итого" и "пустые"
  • Удаляем полученные данные и снимаем фильтры
  • Приводим таблицу к необходимому виду. Добавляем пять столбцов после столбца "Е"
2.1. Рассчитаем CR (Количество кликов / Количество показов) * 100%
  • Присваиваем наименование заголовку и вводим формулу
  • Протягиваем значение до конца таблицы
2.2. Заполняем поле "Расходы" значениями
В правой части таблице есть столбец, содержащий информацию о расходах.
Просто скопируем и вставим его в столбец соседний "CR".
2.3. Подтянем информацию о доходах из таблицы "Доходы"
Наименование рекламной кампании в таблице с расходами по Yandex Direct не совпадают с наименованиями из таблицы "Доходы". Значения ячеек с наименованиями кампаний Яндекса на листе доходов выглядят следующим образом:
chelyabinsk_brand_context|20112731

, где правая часть, состоящая из 8 символов (цифр) - это номер рекламной кампании. Его и будем использовать при суммировании дохода.

Для этого нам нужно:
- отфильтровать таблицу "Доходы" по столбцу "А" и критерию "yandex / cpc"
- скопировать полученный диапазон на новый лист (назовем его "Доходы Яндекс")
- отделить номер кампании от наименования при помощи текстовой функции "ПРАВСИМВ"
Функция "ПРАВСИМВ" отделяет заданное количество знаков/символов, начиная с правой части строки

Вид функции: =ПРАВСИМВ(текст ; количество знаков)
  • Применяем фильтр и копируем таблицу с информацией о доходах с рекламы на Яндексе на новый лист
  • Вставляем столбец правее столбца "С"
Сопоставляя номера рекламных кампаний, подтянем информацию о доходах:
Наименование рекламной кампании в таблице с расходами по Yandex Direct не совпадают с наименованиями из таблицы "Доходы". Значения ячеек с наименованиями кампаний Яндекса на листе доходов выглядят следующим образом:
chelyabinsk_brand_context|20112731

, где правая часть, состоящая из 8 символов (цифр) - это номер рекламной кампании. Его и будем использовать при суммировании дохода.

Для этого нам нужно:
- отфильтровать таблицу "Доходы" по столбцу "А" и критерию "yandex / cpc"
- скопировать полученный диапазон на новый лист (назовем его "Доходы Яндекс")
- отделить номер кампании от наименования при помощи текстовой функции "ПРАВСИМВ"
  • Встаем на ячейку "В2", открываем Мастер функций и находим функцию "ПРАВСИМВ"
  • В поле "Текст" указываем ссылку на ячейку с наименованием
  • В поле "Количество знаков" указываем число 8 (номер рекламной кампании состоит из восьми символов)
  • Нажимаем "ОК" и протягиваем до конца списк
  • На листе с расходами встаем на ячейку "Н2" (доходы), открываем "Мастер функций" и находим "СУММЕСЛИМН"
  • Для поля "Диапазон суммирования" указываем на листе "Доходы Яндекс" столбец "G" (Total Value)
  • Для поля "Диапазон условия1" указываем на листе "Доходы Яндекс" столбец "D" (столбец с номерами рекламных кампаний)
  • Для поля "Условие 1" указываем столбец "А" таблица с расходами по рекламной кампании Яндекс
  • Нажимаем "ОК" и протягиваем формулу до конца таблицы
2.4. Рассчитаем СРО (Доходы / Количество показов)
Поскольку делитель или значения столбца "Количество показов" могут иметь нулевые значения, то будут ошибки в формуле. Поэтому создаем формулу в связке с функцией "ЕСЛИОШИБКА"
2.5. Считаем ROI (Доходы - Расходы) / Расходы
  • Встаем на ячейку "I2", открываем Мастер функций и находим функцию "ЕСЛИОШИБКА"
  • Для поля "Значение" указываем: H2/D2
  • Для поля "Значение если ошибка" указываем 0
  • Нажимаем "ОК" и протягиваем до конца таблицы
  • Считаем значение показателя в связке с функцией "ЕСЛИОШИБКА", поскольку столбец расходы(делитель) содержит нулевые значения
  • Таким образом, формула будет иметь следующий вид: =ЕСЛИОШИБКА((H2-G2)/G2;0)
  • Протягиваем вниз и получаем столбец, содержащий только числовые значения
2.6. Удаляем все лишнее и оставляем только значения без формул
Копируем всю таблицу и вставляем только значения на новый лист. Удаляем лишние столбцы.
3
Объединение полученных данных в одну таблицу
  • В левой части таблицы с полученными значениями по рекламной кампании Гугл (лист "Данные") вставляем столбец и присваиваем ему наименование "Канал"
  • Встаем на ячейку "А2" и вводим значение "Google"
  • Протягиваем до конца таблицы
  • Выделяем всю полученную таблицу показателей по рекламной кампании Яндекса, копируем и вставляем в итоговую таблицу рекламной кампании Гугл. (лучше брать таблицу целиком вместе с заголовком, что бы убедиться в том, что вставляем данные в нужные столбцы)
При работе с большими массивами данных удобно фиксировать положение верхней строки. Используя эту функцию, можно листать таблицу вниз и видеть заголовки.
  • Выделяем заголовок
  • В панели меню переходим на вкладку "Вид" и выбираем "Закрепить области"
  • И выбираем соответствующую команду
  • Убедившись в том, что данные в столбцах обеих таблиц совпадают, удаляем заголовок таблицы, которую вставили.
  • И проставляем в столбце "А" значения "Yandex"
Мы получили данные для построения отчета.
4
Сводные таблицы
  • Выделяем диапазон таблицы полностью
  • На вкладке меню "Вставка" выбираем команду "Сводная таблица"
  • В диалоговом окне все оставляем по умолчанию. Данные мы уже выбрали, а отчет нам нужен на новом листе.
  • Нажимаем "ОК". Создается новый лист с полями сводной таблицы.
4.1.Настраиваем поля сводной таблицы
Поля сводной таблицы делятся на пять составляющих:
1. Поля для отображения
Столбцы таблицы, на основании которой строится сводная таблица. Их нужно просто перетащить в одну из ниже перечисленные области.
2. Фильтры
В эту область перетаскиваем поля которые планируется устанавливать в качестве фильтра данных. Удобно использовать для того, что бы сузить разброс и анализировать данные в разрезе разных показателей и группировок.
3. Колонны
Сюда перетаскиваем те поля, значения которых должны расположиться в качестве наименований столбцов сводной таблицы.
4. Строки
В эту область перетаскиваем поля, которые должны стать наименованиями заголовков строк.
5. Значения
В поле значения перетаскиваются поля, которые будут отображены как значения. Если поле содержит числовые значения, то они будут суммированы по умолчанию. В противном случае будут посчитаны количества значений. Так же это возможно менять в настройках сводной таблицы.
4.2. Выведем сводную таблицу по рассчитанными нами показателей в разрезе источников рекламы
Перетащим поле "Канал" в область "Строки". Для этого просто нажимаем мышкой и перетаскиваем поле.
  • В сводной таблице появились соответствующие поля
  • В поле значений перетягиваем показатели
Получаем искомую таблицу.
4.3. Приведем ее немного в порядок
  • Выделяем область значений таблицы
  • Присваиваем им финансовый формат с разделителями
  • И уберем знаки после запятой
  • Данные в таблице стали выглядеть немного лучше

    • Можно менять дизайн макета. Для этого нужно поставить курсор на поле сводной таблицы, тем самым мы активируем вкладку меню "Конструктор"
    • Выбираем оформление из предложенных
5
Создание сводной диаграммы доходов в разрезе каналов рекламы
  • Выделяем ту же таблицу с общими данными
  • В панели меню переходим на вкладку "Вставка" и выбираем команду "Сводная диаграмма"
  • В диалоговом окне указываем место где нужно разместить диаграмму
  • Нажимаем "ОК" и на указанном листе появляются поля для настройки отображения сводной диаграммы
5.1. Настройка отображения сводной диаграммы
Настройка отображения полей сводной диаграммы аналогична настройке сводной таблицы. Просто перетягиваем поля таблицы в нужную область.
1. Фильтры
Перетащите в эту область те значения по которым нужно произвести отбор, сузить разброс данных
2. Легенда (ряды)
В эту область перетаскиваем поля, которые должны сгруппировать данные на графике. Например, при изображении структуры расходов туда можно перетащить наименования статей бюджета. Тогда для каждой статьи будет построен свой график. При этом в область фильтра можно так же поместить поле наименования статей и скрыть из вида не затратные статьи бюджета.
3. Ось категорий
Сюда следует перетащить поле, которое сгруппирует данные на осях координат. Например, если перетащить сюда дату, то для каждой даты будут отображены данные из области "Легенда (ряды)".
4. Значения
Данная область предназначена для полей, на основании которых будут построены графики. Это должны быть числовые значения или текстовые значения в случае когда нужно просто посчитать количество текстовых полей.
  • Перемещаем поле "Каналы" в область "Ось категорий", а поле "Доход" в область "Значения"
  • Получаем вид диаграммы
5.2. Работа над дизайном
  • Первым делом поменяем название. Для этого просто нужно поставить курсор в поле названия на диаграмме и ввести нужный текст
  • Добавим подписи данных
  • Для того, что бы график не был загроможден излишеством цифр, уберем оси
  • Перейдем к сводной таблице на основе которой построена диаграмма и зададим формат для чисел
Файл с решенным кейсом - Кейс 8(решенный)
Хочешь закрепить материал?
Реши задачу самостоятельно!
Построить диаграмму ROI в разрезе каналов на основе полученных при решении кейса данных.
comments powered by HyperComments