Гайд: как реализовать автоматизированный отчёт на 500+ показателей через Google-таблицы
При масштабировании любого бизнеса встаёт вопрос автоматизации отчётов. Однако бизнес не всегда готов инвестировать в дорогие решения вроде Roistat или Owox. В этом кейсе я расскажу поэтапно, как создавала бесплатный вариант маркетингового отчёта через Google sheets для самой крупной партнерской сети «Додо Пиццы».
Вводные показатели
30+ городов (более 80 пиццерий)
15 источников данных (рекламные площадки, CRM-система, аналитические системы и прочее)
- Более 500 показателей по каждому городу
До запуска проекта отчёты делали по старинке – каждый сотрудник еженедельно заполнял итоги по своим направлениям в общей таблице (на 3000 строк) через скрытые фильтры. Передо мной стояла задача упростить эту систему для команды и сделать более простой для масштабирования.
Чего удалось достичь
Сократили время подготовки отчёта для всей команды с 10-12 часов до 2 часов
- Снизили нагрузку на команду, поскольку отчёт может заполнять один человек
Минимизировали ошибки из-за человеческого фактора ручного заполнения
- Упростили дополнение отчёта новыми каналами, показателями и городами присутствия
В этом кейсе я не только расскажу, как всё устроено, но и дам все необходимые инструменты и инструкции, чтобы вы могли применить их в своей работе.
Кому будет полезен гайд
- Командам, у которых много показателей, но мало бюджета на разработку кастомных дашбордов
Всем, кто работает через таблицы и хочет максимально автоматизировать этот процесс
Как выглядит текущая система
Сразу оговорю – данные в таблицах изменены и носят демонстрационный характер.
Структурно весь отчёт делится на 2 основные части – отчетная таблица и «база данных» – таблица с источниками.
1. Отчетная таблица. Эту таблицу видят все сотрудники сети, могут редактировать отдельные ячейки (например, для фильтрации по городу). Таблица состоит из 4 листов / конфигураций отчета:
- в разрезе недели по всей сети;
- в разрезе месяца по всей сети;
- в разрезе недели по отдельному городу;
- в разрезе месяца по отдельному городу.
Каждая ячейка в этой таблице содержит формулу, которая и подтягивает данные по определенным условиям.
2. Таблица с источниками (для простоты будем называть ее просто «Источники») – документ, в котором хранятся все итоги по каждому источнику данных.
Каждый лист (помимо Справки) – это отдельный источник, куда вручную, либо через расширения Google-таблиц еженедельно и ежемесячно добавляются данные.
3. Взаимодействие таблиц. Так как Google-таблицы не позволяют прямо в формулы вшивать данные из другой таблицы, в отчётную таблицу я добавила дубли всех листов из Источников – эти листы скрыты, а данные в них подтягиваются через функцию IMPORTRANGE.
Таким образом сырые данные вносятся только в таблицу Источники и при этом импортируются в отчётную таблицу.
Путь реализации проекта
Теперь, когда вы уже имеете представление о том, как выглядят отчёты, можно углубиться в детали и пройти вместе со мной весь путь реализации проекта.
Я разделила его на несколько основных этапов:
Определиться со структурой хранения и импорта данных
Собрать все источники данных в одной таблице и найти оптимальный способ их загрузки
Найти функции для преобразования одних данных в другие (зачем — станет понятно чуть позже)
- Настроить импорт между таблицами (при необходимости)
- Собрать итоговую отчётную таблицу
Этап 1
Определяем структуру хранения и импорта данных. Создавать отдельно таблицу источников и отчётную таблицу не обязательно – все можно реализовать проще на базе одной таблицы.
В моём случае таблицы отдельные, поскольку нужно, чтобы отчётная всегда была в чистовом варианте – она доступна для всей компании. По другим проектам мы также используем упрощенную систему – все источники и сам отчёт в одной таблице.
Так что тут выбирайте сами, что важнее – внешний вид, удобство или скорость работы. Сравнение в таблице:
Этап 2
Определились, сколько таблиц создаём – можно подгрузить сырые данные из всех источников.
Создаём в таблице новые листы по количеству источников и сразу называем так, чтобы было понятно, откуда данные. Плюс нам понадобится Лист «Справка» для отображения соответствий.
К этому листу мы будем обращаться в формулах, когда загружаемые данные не соответствуют нашему основному фильтру. Например, сервис выгружает данные по названиям сообществ в ВК, а нам нужно фильтровать данные по городу – для этого мы прописываем на листе «Справка», какому городу соответствует каждое сообщество.
Начинаем трудоёмкий процесс поиска оптимального способа загрузки данных в таблицу. По каждому источнику будет свой способ – я же опишу всё, что использовала в своей работе:
1. Через расширения. Расширения для таблиц – отличный инструмент, их достаточно много на рынке, я же нашла оптимальный для нас – Adveronix. Сервис бесплатный в рамках нужного для нас функционала, можно настроить полностью под себя.
Подробная настройка отчётов через расширения – тема для отдельного гайда. Здесь опишу основные преимущества.
Можно настроить полностью автоматизированные выгрузки, которые будут ежедневно / еженедельно обновлять данные за нужный период. Таким образом вы один раз настраиваете отчёт – и больше не думаете об этом источнике.
Большой выбор сервисов для интеграции, и разработчики расширяют их количество. На текущий момент мы используем интеграции с Google Аналитикой, Яндекс Метрикой, Яндекс Direct.
2. Через «отчёты» источников. Часть источников (в нашем случае это CRM, некоторые рекламные платформы, а также сервисы аналитики, как LiveDude) пока не удается интегрировать через расширения бесплатно, однако почти везде можно выгружать отчеты в xlsx также за нужный период и с необходимыми фильтрами.
Схема работы с этими каналами достаточно простая – если вы отслеживаете статистику еженедельно, то заходите в понедельник в нужный сервис, экспортируете статистику за предыдущую неделю и переносите данные в нужный лист. Инструкции к выгрузкам можно написать прямо на всех листах – так любой сотрудник сможет без труда внести данные, пока вы в отпуске:
3. Ручками. Да, в 2022 году еще остались сервисы, которые не дают выгружать отчёты по тем или иным причинам.
В нашем случае есть ограничение по сервисам, которые администрирует наша Управляющая компания Додо – и у партнёров просто недостаточно прав для выгрузки отчетов.
4. Самописная программа. Если у вас в штате есть разработчик, то можно, конечно, всю работу сделать автоматизированной бесплатно... Программа в любом случае будет требовать обслуживания, особенно если у вас часто меняются или дополняются источники.
Сразу отвечу на вопрос, который может возникнуть на этом этапе.
За какой период выгружать данные? – За тот, который вам необходим. В нашем случае требуются как еженедельные, так и ежемесячные выгрузки – они все хранятся на одном листе. Для фильтрации по сроку мы используем столбец «Дата», о котором расскажу в следующем этапе.
Этап 3
Мы собрали данные за какой-то период в таблице. Но по каждому источнику они получились совершенно разные – названия столбцов, форматы и т.д. Это нормально. Нам важно, чтобы на всех листах были столбцы, по которым мы будем все эти данные затем конфигурировать в отчёте.
Я выделила столбцы для наших отчётов, по которым должны фильтроваться все данные:
- Фильтр по городу (именно по городу, а не по пиццерии или, например, названию сообщества в ВК)
Фильтр по дате (неделе / месяцу)
Фильтр по владельцу пиццерий (помимо своей сети мы также ведём несколько городов партнеров. Чтобы иметь возможность делать не только отчет по городам, но и суммировать по всей нашей сети)
Таким образом, мы получаем 3 обязательных столбца для каждого листа: Дата, Город и Владелец.
Рассмотрим подробнее, как я подгружаю данные по этим столбцам во все источники.
- Дата – схема незамысловатая. Если данные по источнику подгружаются вручную – просто добавляем в столбец Дата эту самую дату (мы используем дату первого дня отчетной недели / месяц в формате «Июль 2022»). И протягиваем дату на все строки добавленного диапазона.
Если данные подгружаются автоматически (например, через Adverobix), то формат этого столбца определяется при настройке отчета Adveronix – и далее выгружается в нужном виде.
Город и Владелец – вот здесь нам поможет Лист Справка, к которому мы обращаемся через функцию ВПР: =ВПР(F6;'справка'!$F:$G;2;0)или =ЕСЛИОШИБКА(ВПР(B4;'справка'!$B:$C;2;0))
На лист Справка мы добавляем все необходимые соответствия. У нас лист Справка при этом выглядит следующим образом:
Лайфхаки
Для удобства заполнения источников столбцы можно выделить разными цветами. Я выделяю зелёным те данные, которые вношу вручную (как Дата), а красным – данные с функциями и формулами. Таким образом сразу видно, куда вносить данные, соответственно меньше вероятность ошибки.
Чтобы заполнение занимало минимум времени – можно первым этапом пройтись по всем листам и добавить даты (чтобы потом можно было быстро их растянуть на весь диапазон).
Чтобы заполнять ещё быстрее – обязательно используйте сочетания клавиш. Например, Ctrl+Shift+стрелка для быстрой навигации. Полный список можно посмотреть по ссылке.
Используйте функцию ЕСЛИОШИБКА, чтобы в случае, если значение не найдено Справке, возвращался просто пустая ячейка.
- Случается, что форматы данных, которые выгружаются из отчётов, сложно упаковать в Справку. Например, если вам нужно преобразовать названия кампании в город. Для этого я использую функцию =SPLIT – она позволяет разделить ячейку на несколько ячеек в столбце по определенному разделителю (для этого важно, чтобы все кампании в кабинете назывались по одному шаблону.
Этап 4
Поздравляю, самая сложная часть пути пройдена! Можно этап 4 пропустить (но только если вы реализуете всё в одной таблице).
Если же у вас отдельно таблица с источниками и отчётная таблица, то нужно настроить импорт.
Всё просто:
Копируем всю таблицу с источниками
Переименовываем эту таблицу, чтобы вы понимали, что здесь Отчёт
- Удаляем данные со всех Листов, при этом сами листы оставляем
- А теперь в левую верхнюю ячейку вставляем функцию IMPORTRANGE на все листы, меняя названия листов и столбцов для экспорта – пользуемся инструкцией от Google
! Важный момент 1 – диапазон лучше сразу выбирать только из столбцов – так вам не придется корректировать в дальнейшем функцию. Пример: IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "лист1!A:C")
! Важный момент 2 – функция IMPORTRANGE может переносить ограниченный объём данных. Если данных много, и вылезает соответствующая ошибка – просто копируйте функцию и ограничивайте по строкам. Функция будет подгружать данные отдельными диапазонами.
Этап 5
Создаем отчётную таблицу — то, к чему мы шли весь долгий путь! Осталось 3 шага до финиша.
Буду показывать на примере основного отчёта – в разрезе недели по всей сети. Остальные отчёты формируются изменением 1-2 параметров в функциях.
Шаг 1 – Прописать заголовки строк и столбцов. Прописываем в строки все необходимые показатели (если у вас ранее была отчётная таблица – то и выдумывать ничего не надо). Столбцы – период.
- Шаг 2 – Подтянуть данные. Вся таблица строится на основе одной единственной функции – СУММЕСЛИМН. Разберём на примере ячейки AD555.
Лист с отчетом:
Лист с данными «телега» находится в этой же таблице (напомню, мы подтянули данные через IMPORTRANGE):
Воспользуемся шпаргалкой Google:
И формируем нашу формулу: =ЕСЛИОШИБКА(СУММЕСЛИМН('телега'!$E:$E;'телега'!$A:$A;AD$1;'телега'!$B:$B;"Троян"))
'телега'!$E:$E – диапазон суммирования – столбец, содержащий количество подписчиков. Обязательно фиксируем долларами диапазон, чтобы при копировании ячейки столбец в формуле не менялся.
Помним, что у нас есть 3 основных параметра, по которому конфигурируем данные: дата, город и владелец. Так как конкретно в этом отчёте мы суммируем все данные по подписчикам по сети, то у нас остаются дата и город. Их и зашиваем в формулу:
'телега'!$A:$A – диапазон критерия 1. Выделяем столбец с Датой, фиксируем долларами.
AD$1 – критерий 1. Это ячейка хранит значение Даты, которому должно соответствовать значение в диапазоне критерия 1. Нам нужно, чтобы при копировании формулы менялся столбец (на актуальную дату), поэтому фиксируем только строку.
'телега'!$B:$B – диапазон критерия 2. Столбец с владельцем.
"Троян" – критерий 2. Нужное нам значение для конкретной таблицы.
Аналогичным образом готовим формулы для всех остальных показателей. При необходимости мы увеличиваем количество условий. Например, если на одном листе содержатся данные по разным направлениям, которые нужно разделить.
- Шаг 3 – Оформить. На что стоит обратить внимание?
1. Ограничить доступ к редактированию таблицы (защита столбцов и строк) – только так получится избежать случайного удаления данных, смещения ячеек и прочих неприятных эксцессов. Инструкция здесь.
2. Группировка строк / столбцов (но учтите, что скрывать и раскрывать строки сможет только редактор).
3. Закрепление столбцов и строк для удобства навигации.
4. Диаграмма – удобная функция SPARKLINE для наглядности результатов позволяет сделать график прямо внутри ячейки с данными.
5. Условное форматирование – если у вас есть расчётные данные, например, динамика показателя, условное форматирование позволяет сделать отчёт более наглядным.
ГОТОВО! Наш основной отчёт можно запускать в работу. Но мы помним, что у нас есть и другие конфигурации отчета. Пройдёмся ещё раз по каждой, чтобы у вас не осталось вопросов.
В разрезе недели по всей сети – разобрали выше
В разрезе месяца по всей сети – просто заменяем строку с Датой в отчётной таблице с названий недели на названия месяцев
- В разрезе недели / месяца по отдельному городу – здесь всё интереснее.
Если с отчётом по всей сети мы зашивали в формулу критерий Владелец, то здесь нам необходима фильтрация по Городу.
В нашей универсальной формуле СУММЕСЛИМН заменяем Критерий 2 и Диапазон критерия 2 на данные по городу. И получается следующая формула:
=ЕСЛИОШИБКА(СУММЕСЛИМН('телега'!$E:$E;'телега'!$A:$A;AD$1;'телега'!$С:$С;ячейка-переменная))
'телега'!$E:$E – диапазон суммирования остаётся.
'телега'!$A:$A – диапазон критерия 1 остаётся (столбец Дата в источниках).
AD$1 – критерий 1 остаётся (Дата в отчетной таблице).
'телега'!$С:$С – диапазон критерия 2 меняем на столбец Город в источниках.
Ячейка-переменная– критерий 2. А вот здесь мы оставляем свободу выбора.
Если пропишем в формуле сразу город, то, чтобы посмотреть итоги по другому городу, нам нужно будет менять все формулы. Мы же присвоим переменную Город конкретной ячейке – и любой сотрудник сможет, изменив всего в 1 ячейке город, получить в таблице все необходимые данные.
Вот как это выглядит в нашем случае:
Заключение
Система не без минусов – на больших объемах Google начинает крашиться и показывает «Опаньки», довольно много процессов ещё делается вручную.
И наш следующий эволюционный этап – это полностью автоматизированные дашборды в Яндекс Data Lens с облачной базой данных и самописной программой для экспорта статистики из всех наших источников.
Но это уже совсем другая история…
Тут еще нужно приложить количество и стоимость человеко/часов на поддержку данного "бесплатного" решения.
В таких отчетах много нюансов требующих постоянного внимания. Если google sheets бесплатный, то вот стабильная загрузка в него данных - совсем нет.
Алексей, привет! Мы не говорим, что это идеальная система, даже наоборот у нас есть планы по доработке, о которых в конце статьи немного упомянули.
Но на самом деле при таком объеме и сложностях выгрузки данных из некоторых систем (в которые мы не можем интегрироваться из-за API) – для нас это огромная экономия ресурсов.
В последнее время гугл начал жёстко крашится. Но, что интересно, не на этапе выгрузки, а на этапе отчетной таблицы – слишком много формул и данных. Начала по старым данным сохранять просто значения – теперь там сами формулы в общих таблицах в последних 2-4 столбцах. Помогает)
Но это конкретно с этой таблицей проблема. По другим направлениям всё работает гораздо быстрее.
Ну и плюс таблиц в том, что легко можно настроить всё под себя, поддержка на самом деле минимальная требуется)
Команду тоже научили – так что пока работаем
Можно импортировать сразу из таблиц онлайн через IMPORTHTML, тоже иногда пользовался. В недвижке через xml делал сбор данных об объектах и связывал с реестром договоров. Очень понимаю автора. Добротно все сделать очень не просто. Некоторые отчеты меня убивали тем, что данные не во всех полях присутствовали и возникали пропуски строк в мелких выборках, в итоге на диаграмме прибыль красным могла отобразиться)
Мне ещё нравится, когда числовые данные подгружаются с точкой в формате текста :-D
И в какой-то момент помучилась, когда ещё был ФБ, с форматами дат. Но там тоже нашлись решения через ЛЕВСИМВ
Отображение отчетов и систематизация – это тоже целая история! Понимаем :)