Гайд: как реализовать автоматизированный отчёт на 500+ показателей через Google-таблицы

При масштабировании любого бизнеса встаёт вопрос автоматизации отчётов. Однако бизнес не всегда готов инвестировать в дорогие решения вроде Roistat или Owox. В этом кейсе я расскажу поэтапно, как создавала бесплатный вариант маркетингового отчёта через Google sheets для самой крупной партнерской сети «Додо Пиццы».

• 30+ городов (более 80 пиццерий)

• 15 источников данных (рекламные площадки, CRM-система, аналитические системы и прочее)
  

• Более 500 показателей по каждому городу

До запуска проекта отчёты делали по старинке – каждый сотрудник еженедельно заполнял итоги по своим направлениям в общей таблице (на 3000 строк) через скрытые фильтры. Передо мной стояла задача упростить эту систему для команды и сделать более простой для масштабирования.

• Сократили время подготовки отчёта для всей команды с 10-12 часов до 2 часов

• Снизили нагрузку на команду, поскольку отчёт может заполнять один человек

• Минимизировали ошибки из-за человеческого фактора ручного заполнения

• Упростили дополнение отчёта новыми каналами, показателями и городами присутствия

• Командам, у которых много показателей, но мало бюджета на разработку кастомных дашбордов

• Всем, кто работает через таблицы и хочет максимально автоматизировать этот процесс

Сразу оговорю – данные в таблицах изменены и носят демонстрационный характер.

1. Отчетная таблица. Эту таблицу видят все сотрудники сети, могут редактировать отдельные ячейки (например, для фильтрации по городу). Таблица состоит из 4 листов / конфигураций отчета:

• в разрезе недели по всей сети;
• в разрезе месяца по всей сети;
  
• в разрезе недели по отдельному городу;
  
• в разрезе месяца по отдельному городу.
  

2. Таблица с источниками (для простоты будем называть ее просто «Источники») – документ, в котором хранятся все итоги по каждому источнику данных.

3. Взаимодействие таблиц. Так как Google-таблицы не позволяют прямо в формулы вшивать данные из другой таблицы, в отчётную таблицу я добавила дубли всех листов из Источников – эти листы скрыты, а данные в них подтягиваются через функцию IMPORTRANGE.

Таким образом сырые данные вносятся только в таблицу Источники и при этом импортируются в отчётную таблицу.

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

Я разделила его на несколько основных этапов:

1. Определиться со структурой хранения и импорта данных

2. Собрать все источники данных в одной таблице и найти оптимальный способ их загрузки

3. Найти функции для преобразования одних данных в другие (зачем — станет понятно чуть позже)

4. Настроить импорт между таблицами (при необходимости)
5. Собрать итоговую отчётную таблицу

Определяем структуру хранения и импорта данных. Создавать отдельно таблицу источников и отчётную таблицу не обязательно – все можно реализовать проще на базе одной таблицы.

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

Так что тут выбирайте сами, что важнее – внешний вид, удобство или скорость работы. Сравнение в таблице:

Определились, сколько таблиц создаём – можно подгрузить сырые данные из всех источников.

Создаём в таблице новые листы по количеству источников и сразу называем так, чтобы было понятно, откуда данные. Плюс нам понадобится Лист «Справка» для отображения соответствий.

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

1. Через расширения. Расширения для таблиц – отличный инструмент, их достаточно много на рынке, я же нашла оптимальный для нас – Adveronix. Сервис бесплатный в рамках нужного для нас функционала, можно настроить полностью под себя.

Подробная настройка отчётов через расширения – тема для отдельного гайда. Здесь опишу основные преимущества.

• Можно настроить полностью автоматизированные выгрузки, которые будут ежедневно / еженедельно обновлять данные за нужный период. Таким образом вы один раз настраиваете отчёт – и больше не думаете об этом источнике.

• Большой выбор сервисов для интеграции, и разработчики расширяют их количество. На текущий момент мы используем интеграции с Google Аналитикой, Яндекс Метрикой, Яндекс Direct.

2. Через «отчёты» источников. Часть источников (в нашем случае это CRM, некоторые рекламные платформы, а также сервисы аналитики, как LiveDude) пока не удается интегрировать через расширения бесплатно, однако почти везде можно выгружать отчеты в xlsx также за нужный период и с необходимыми фильтрами.

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

3. Ручками. Да, в 2022 году еще остались сервисы, которые не дают выгружать отчёты по тем или иным причинам.

В нашем случае есть ограничение по сервисам, которые администрирует наша Управляющая компания Додо – и у партнёров просто недостаточно прав для выгрузки отчетов.

4. Самописная программа. Если у вас в штате есть разработчик, то можно, конечно, всю работу сделать автоматизированной бесплатно... Программа в любом случае будет требовать обслуживания, особенно если у вас часто меняются или дополняются источники.

Сразу отвечу на вопрос, который может возникнуть на этом этапе.

Мы собрали данные за какой-то период в таблице. Но по каждому источнику они получились совершенно разные – названия столбцов, форматы и т.д. Это нормально. Нам важно, чтобы на всех листах были столбцы, по которым мы будем все эти данные затем конфигурировать в отчёте.

Я выделила столбцы для наших отчётов, по которым должны фильтроваться все данные:

• Фильтр по городу (именно по городу, а не по пиццерии или, например, названию сообщества в ВК)

• Фильтр по дате (неделе / месяцу)

• Фильтр по владельцу пиццерий (помимо своей сети мы также ведём несколько городов партнеров. Чтобы иметь возможность делать не только отчет по городам, но и суммировать по всей нашей сети)

Таким образом, мы получаем 3 обязательных столбца для каждого листа: Дата, Город и Владелец.

Рассмотрим подробнее, как я подгружаю данные по этим столбцам во все источники.

• Дата – схема незамысловатая. Если данные по источнику подгружаются вручную – просто добавляем в столбец Дата эту самую дату (мы используем дату первого дня отчетной недели / месяц в формате «Июль 2022»). И протягиваем дату на все строки добавленного диапазона.

• Город и Владелец – вот здесь нам поможет Лист Справка, к которому мы обращаемся через функцию ВПР: =ВПР(F6;'справка'!$F:$G;2;0)или =ЕСЛИОШИБКА(ВПР(B4;'справка'!$B:$C;2;0))

На лист Справка мы добавляем все необходимые соответствия. У нас лист Справка при этом выглядит следующим образом:

• Для удобства заполнения источников столбцы можно выделить разными цветами. Я выделяю зелёным те данные, которые вношу вручную (как Дата), а красным – данные с функциями и формулами. Таким образом сразу видно, куда вносить данные, соответственно меньше вероятность ошибки.

• Чтобы заполнение занимало минимум времени – можно первым этапом пройтись по всем листам и добавить даты (чтобы потом можно было быстро их растянуть на весь диапазон).

• Используйте функцию ЕСЛИОШИБКА, чтобы в случае, если значение не найдено Справке, возвращался просто пустая ячейка.

• Случается, что форматы данных, которые выгружаются из отчётов, сложно упаковать в Справку. Например, если вам нужно преобразовать названия кампании в город. Для этого я использую функцию =SPLIT – она позволяет разделить ячейку на несколько ячеек в столбце по определенному разделителю (для этого важно, чтобы все кампании в кабинете назывались по одному шаблону.

Поздравляю, самая сложная часть пути пройдена! Можно этап 4 пропустить (но только если вы реализуете всё в одной таблице).

Если же у вас отдельно таблица с источниками и отчётная таблица, то нужно настроить импорт.

Всё просто:

• Копируем всю таблицу с источниками

• Переименовываем эту таблицу, чтобы вы понимали, что здесь Отчёт

• Удаляем данные со всех Листов, при этом сами листы оставляем
• А теперь в левую верхнюю ячейку вставляем функцию IMPORTRANGE на все листы, меняя названия листов и столбцов для экспорта – пользуемся инструкцией от Google

Создаем отчётную таблицу — то, к чему мы шли весь долгий путь! Осталось 3 шага до финиша.

Буду показывать на примере основного отчёта – в разрезе недели по всей сети. Остальные отчёты формируются изменением 1-2 параметров в функциях.

• Шаг 1 – Прописать заголовки строк и столбцов. Прописываем в строки все необходимые показатели (если у вас ранее была отчётная таблица – то и выдумывать ничего не надо). Столбцы – период.

• Шаг 2 – Подтянуть данные. Вся таблица строится на основе одной единственной функции – СУММЕСЛИМН. Разберём на примере ячейки AD555.

Лист с отчетом:

Лист с данными «телега» находится в этой же таблице (напомню, мы подтянули данные через IMPORTRANGE):

Воспользуемся шпаргалкой Google:

И формируем нашу формулу: =ЕСЛИОШИБКА(СУММЕСЛИМН('телега'!$E:$E;'телега'!$A:$A;AD$1;'телега'!$B:$B;"Троян"))

'телега'!$E:$E – диапазон суммирования – столбец, содержащий количество подписчиков. Обязательно фиксируем долларами диапазон, чтобы при копировании ячейки столбец в формуле не менялся.

'телега'!$A:$A – диапазон критерия 1. Выделяем столбец с Датой, фиксируем долларами.

AD$1 – критерий 1. Это ячейка хранит значение Даты, которому должно соответствовать значение в диапазоне критерия 1. Нам нужно, чтобы при копировании формулы менялся столбец (на актуальную дату), поэтому фиксируем только строку.

'телега'!$B:$B – диапазон критерия 2. Столбец с владельцем.

"Троян" – критерий 2. Нужное нам значение для конкретной таблицы.

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

• Шаг 3 – Оформить. На что стоит обратить внимание?

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

2. Группировка строк / столбцов (но учтите, что скрывать и раскрывать строки сможет только редактор).

3. Закрепление столбцов и строк для удобства навигации.

4. Диаграмма – удобная функция SPARKLINE для наглядности результатов позволяет сделать график прямо внутри ячейки с данными.

5. Условное форматирование – если у вас есть расчётные данные, например, динамика показателя, условное форматирование позволяет сделать отчёт более наглядным.

• В разрезе недели по всей сети – разобрали выше

• В разрезе месяца по всей сети – просто заменяем строку с Датой в отчётной таблице с названий недели на названия месяцев

• В разрезе недели / месяца по отдельному городу – здесь всё интереснее.

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

В нашей универсальной формуле СУММЕСЛИМН заменяем Критерий 2 и Диапазон критерия 2 на данные по городу. И получается следующая формула:

'телега'!$E:$E – диапазон суммирования остаётся.

'телега'!$A:$A – диапазон критерия 1 остаётся (столбец Дата в источниках).

AD$1 – критерий 1 остаётся (Дата в отчетной таблице).

'телега'!$С:$С – диапазон критерия 2 меняем на столбец Город в источниках.

Ячейка-переменная– критерий 2. А вот здесь мы оставляем свободу выбора.

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

Вот как это выглядит в нашем случае:

Система не без минусов – на больших объемах Google начинает крашиться и показывает «Опаньки», довольно много процессов ещё делается вручную.

И наш следующий эволюционный этап – это полностью автоматизированные дашборды в Яндекс Data Lens с облачной базой данных и самописной программой для экспорта статистики из всех наших источников.