Когда нет БД под рукой

Шаблон ручного отчёта. Drive.

Структура ручного отчёта

Алгоритм создания ручного отчёта

Выгрузки

Как делается выгрузка из Директа

Как делается выгрузка из GA

Выгрузка из Facebook

Выгрузка из Bing

Выгрузка из Mango

Выгрузка из ВКонтакте

Создание листов под источники в Excel

Форматирование выгрузок

Сводная таблица

Формат сводной таблицы

Зачем нужна ручная отчётность

При запуске нового проекта отчётность нужна нам, чтобы контролировать показатели и изменять бюджеты и клиенту, чтобы видеть эффективность проекта. Мы делаем ручную отчётность на сводных таблицах Excel.

Ручной отчёт нужен, когда статус проекта неизвестен. Это наша «твёрдая почва под ногами», на данные ручного отчёта нужно опираться.

Джедай должен уметь делать ручной отчёт, чтобы лучше прочувствовать механику: откуда берутся данные, как мы их объединяем и группируем. Если что-то сломалось в БД, мы всегда сможем в течение нескольких часов построить ручной отчёт.

Структура ручного отчёта

Данные для ручного отчёта мы берём из разных источников:

Что берём	Откуда
Показы, клики, стоимость по дням и РК	Площадки (Директ, AdWords итд.)
Конверсии в цели по дням и РК	GA
Звонки по дням и источникам	АТС (Манго, CallRail итд.) или UA
Продажи	Клиентские выгрузки / Системы учёта коммуникаций

Минимальная сущность ручного отчёта выглядит так: кампания – дата – показы – клики

С площадок нам нужно получить: показы, клики, стоимость по дням для каждой кампании.

Из Аналитикса нам нужно получить: количество каждой цели по дням для каждой кампании.

Из АТС нам нужно получить: количество звонков по дням для каждой кампании.

В таблице дан пример привязки. Зелёным цветом обозначено то, чем мы привязываем данные из разных источников.

*Площадки*	GA	*АТС*
Дата	Дата	Дата
Кампания	Кампания	Кампания
Показы	Кол-во целей	Кол-во звонков
Клики
Стоимость

Клиентские продажи в зависимости от проекта и воронки могут быть привязаны к кампании, городу или группе источников (Google Поиск, Яндекс Сети).

Все остальные расчётные данные (ctr, % конверсий, суммарное число коммуникаций, KPI или ROMI) получаем уже в Excel.

В сводных данных мы должны получить строку примерного вида:

Дата	Кампания	Город	Площадка	Показы	Клики	Заявки	Звонки	Продажи
12.12.2014	Питер-Суперцелевые — поиск	Питер	Google Поиск	3445	255	16	10	2

На первом этапе составления отчёта или прототипа отчётности нужно определиться с привязками: посмотреть все этапы воронки, понять к чему будем привязывать звонки и продажи. Будут ли отдельные категории товаров. Только после того, как мы выясним какой будет итоговый вид источника для сводного отчёта, можно приступать к следующим шагам.

Единая сущность – ключ, по которым мы связываем данные – это название кампании в GA. Обязательно нужны получить в выгрузках названия кампаний и даты до дня.

Примеры строк.

Алгоритм создания ручного отчёта

Выгрузки

Нужно выгрузить данные с площадок и из GA и скопировать выгрузку каждой площадки на отдельный лист Excel.

Как делается выгрузка из Директа

Статистика по всем кампаниям → выбрать нужный диапазон → скачать в виде xls файла.

Как делается выгрузка из AdWords

Все интернет-кампании → быстрая статистика → вид – день → загрузить отчёт → xlsx.

Как делается выгрузка из GA

Для начала нужно создать кастомный отчёт по целям, которые нас интересуют по кампаниям.

Добавляем в отчёт сессии.

Тип отчёта: Flat Table.

Пример отчёта в галерее решений.

Для того, чтобы отчёт отправлялся по расписанию на ваш ящик по понедельникам, нажимаем на email и устанавливаем расписание, тему письма и формат xlsx.

Выгрузка из Facebook

Переходим в менеджер рекламы – отчёты. Добавляем разбивку: время – ежедневно. Жмём «экспорт», выгружаем в xlsx.

Важно, что при разбивке по дням Facebook указывает как дату начала, так и дату завершения, они одинаковые. Копируем в лист только диапазон начиная с даты завершения.

Выгрузка из Bing

Заходим в Reports, выставляем значения:

Report Type:Campaign

Show (unit of time) Day

Date Range: интересующая нас неделя

Формат: Excel.

В нижней части экрана жмём на Download

Выгрузка из Mango

Войти в личный кабинет Mango.

В правом верхнем углу нажать «детализация».

Выбрать произвольный период и формат xls → сформировать.

Выгрузка из ВКонтакте

Рекламные кампании → экспорт статистики.

Тип отчётности: эффективность рекламы.

Период отчётности: по дням.

Суммирование: разбить по кампаниям.

Формат вывода – xls.

Выбираем нужные диапазон дат.

Создание листов под источники в Excel

Нужно создать листы в Excel под каждый источник, например, Source Direct и Source AdWords.

Пример листов ручного отчёта для проекта с заявками и звонками через АТС Манго:

Source Direct

Source AdWords

Source GA

Source Mango

Source Client_Sales

Source сводные данные

Pivot

Pivot (по кампаниям)

Pivot (по площадкам)

Settings

Пример листов ручного отчёта для проекта по США и АТС, интегированной в UA:

Source Bing

Source AdWords

Source Facebook

Source GA

Source Client_Sales

Source сводные данные

Pivot

Pivot (по кампаниям)

Pivot (по площадкам)

Settings

3. Создаём служебный лист «Настройки».

В нём указываем связки разных выгрузок. Добавляем табличку с привязкой номеров АТС. В этом случае телефон привязан к площадке.

Номер телефона	Город	Площадка	Группа источников
78124261113	Питер	Яндекс	Яндекс Телефон
78124261114	Питер	Google	Google Телефон

Делаем таблицу связки названий кампаний в GA, названий в отчёта, площадки, группы источников, города:

Название GA	Название кампании	Площадка	Группа источников	Город
rko_spb_y_supercelevye_poisk	Суперцелевые Поиск	Яндекс	Яндекс Поиск	Питер
РКО: Питер — Суперцелевые — Поиск	Суперцелевые Поиск	Google	Google Поиск	Питер
РКО: Питер-Ремаркетинг	Ремаркетинг	Google	Google Сети	Питер
rko_spb_y_remarketing_seti	Ремаркетинг	Яндекс	Яндекс Сети	Питер
Google Телефон	Google Телефон	Google	Google Телефон	Питер
(not set)	Без кампании	Без источника	Без группы	Без города

Форматирование выгрузок

Source Direct

Добавляем колонки Название GA и Бюджет.

В сводном листе нам нужно привести всё к единому знаменателю: одно название кампании (как в GA), бюджет в рублях. Для этого добавляем столбцы с формулами.

Всё, что изменено относительно выгрузки площадки выделяем серым цветом для удобства.

Названия GA тянем из листа настройки по формуле:

=IFERROR(VLOOKUP(B2;Settings!$M$1:$N$3; 2; FALSE); » «)

Расход в рублях получаем с помощью формулы

=IFERROR(G2*Settings!$F$2;» «)

Source AdWords

Добавляем бюджет без проблелов, т.к. в стандартной выгрузке AdWords стоит пробел.

Далем это с помощью формулы.

=IFERROR(SUBSTITUTE(F2;CHAR(160);»»)*1;0)

Добавляем колонку с бюджетом с НДС с помощью формулы

=I2*1,18

Проставляем дату, приводим её к нашему единому формату с помощью формулы.

=DATE(LEFT(A2;4); MID(A2;6;2); RIGHT(A2;2))

Source GA

Добавляем группу источников, источник с помощью формул VLOOKUP.

Cтавим дату правильном формате:

=DATE(LEFT(B2;4); MID(B2;5;2); RIGHT(B2;2))

Сводный лист данных

Копируем 2 левых столбца из выгрузки GA и полученные 2 столбца из выгрузки Mango. Все остальные данные притянутся автоматически через формулы.

Нужно сразу проставить колонкам правильные названия. Проверяем правильность данных по каждому столбцу.

Сводная таблица

Выделям лист со сводными данными, после чего нажимаем insert – pivot table.

Выбираем new worksheet.

Выбираем нужны поля сводной талицы, после чего создаём расчетные колонки: ctr, проценты конверсии.

Создание расчётных колонок: справка 1, 2.

Создаем расчётные поля с помощью функции IFERROR (для того, чтобы не было ошибок в сводной таблице).

Пример формулы расчётной колокнки 60 сек, %:

=IFERROR(’60 секунд’/Клики*100%;0)

Делаем несколько копий отчёта в разных разрезах, чтобы смотреть общую динамику, динамику по площадке или кампании.

Формат сводной таблицы

Чек лист для форматирования сводной таблицы:

установлен правильный формат: (валюта, проценты и др.);
установлено необходимое количество знаков после запятой;
добавлены полупробелы-разделители после тысяч;
добавлены функции IFERROR в расчётные поля;
у каждого листа есть название;
выравнивание цифр по правому краю;
выравнивание текстов по левому краю.

Про оформление таблиц даже был отдельный семинар

Ещё вопросы:

Зачем Дата и Дата2?
Как обновлять?
Клиентские данные: какие бывают подводные камни и как с ними бороться?
Добавить в шаблон Фейсбук, ВК и остальных