JetStat | Дашборд

Дашборд

Введение

После того как ты подготовил сырые данные в JetStat и выгрузил их в Google Таблицы, настало время собрать из них дашборд.

Дашборд — это таблица, в которой в одном месте собраны ключевые рекламные метрики, позволяющие быстро оценить эффективность кампаний.

Обычно в дашборде есть две части:

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

В этом уроке мы не будем подробно разбирать создание полноценного дашборда — это тема для отдельного занятия. К тому же на разных проектах плановая часть может отличаться (хотя общие принципы остаются теми же).

Сегодня мы сосредоточимся на основе любого дашборда — фактических данных, то есть статистике рекламных кампаний. Покажем, как оформить сырую выгрузку из JetStat в аккуратный и наглядный дашборд с помощью формулы «СУММЕСЛИ» или «СУММЕСЛИМН»

Важно: если ты пока не знаком с формулами «СУММЕСЛИ» и «СУММЕСЛИМН», сперва разберись с принципами их работы по любой справке в интернете — дальше материал будет восприниматься гораздо легче.

Вот пример дашборда, который мы сделаем сегодня:

Готовим шаблон

Для начала подготовь шаблон дашборда, подходящий под твой проект. Сами метрики, порядок столбцов, цвета и оформление — на твоё усмотрение.

Главное — помнить, что дашборд должен быть функциональным. У столбцов должна быть логика, они не должны располагаться хаотично.

Обычно метрики располагают в порядке появления событий:

сначала показы;
затем клики;
потом расход;
после — конверсии и доход.

Расчётные показатели (CTR, CPC, CPA, ДРР и т. д.) размещают рядом с базовыми, чтобы их было удобно анализировать в связке.

Иногда расходы ставят в самое начало (см. скрин реального дашборда выше) — это тоже частая история.

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

Добавляем параметры для мэтчинга

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

если в выгрузке данные только по одному флайту — достаточно СУММЕСЛИ, потому что параметр мэтчинга всего один — дата;
если в выгрузке данные по нескольким флайтам, но дашборд нужен по клиенту в целом, также можно использовать СУММЕСЛИ, ведь сводить данные будем по дате, а разные кампании просто схлопывать;
если же в выгрузке присутствуют кампании из разных флайтов, а дашборд нужно собрать по конкретному флайту (например, «Скидки на плиты Gorenje»), тогда потребуется СУММЕСЛИМН, добавив второй параметр — название флайта.

В последнем случае логично указать название флайта прямо на дашборде, чтобы потом ссылаться на эту ячейку в формуле, например так:

Однако это необязательно — название флайта можно указать прямо в формуле.

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

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

В этом уроке мы разберём создание дашборда с помощью формулы СУММЕСЛИМН (хотя по сути она нам не требуется, ведь в нашей выгрузке всего один флайт). Тем не менее именно эта формула чаще всего используется в работе.

Важно: если в JetStat заранее сгруппировать все кампании по дате, дашборд можно собрать и с помощью старого доброго ВПР (или, что лучше, его продвинутой версии — ПРОСМОТРX), ведь в этом случае суммировать данные по разным кампаниям уже не придётся.

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

В общем, здесь большой простор для творчества — подбирай вариант под свои задачи и привычки.

Ссылка на шаблон.

Пишем первую формулу для первой метрики

У формулы СУММЕСЛИМН следующий синтаксис:

1

сначала указывается столбец в сырой выгрузке с метрикой, которую нужно суммировать;

2

затем — столбец с первым параметром, по которому происходит мэтчинг (например, название флайта);

3

после этого — ячейка в дашборде, которая соответствует этому параметру (или можно указать название флайта);

4

далее — столбец со вторым параметром для мэтчинга (например, дата);

5

и наконец — ячейка в дашборде, которая соответствует этому параметру (ячейка с первой датой).

В итоге формула в нашем примере выглядит так:

=СУММЕСЛИМН('Ozon (raw)'!$D:$D;'Ozon (raw)'!$J:$J;$A$1;'Ozon (raw)'!$A:$A;$A3)
Вариант с указанием названия флайта в самой формуле:

=СУММЕСЛИМН('Ozon (raw)'!$D:$D;'Ozon (raw)'!$J:$J;"*Скидки на плиты Gorenje*";'Ozon (raw)'!$A:$A;$A3)

Важно: обрати особое внимание на использование знаков «$» (если не знаешь, как они работают, загляни в справку или посмотри какое-нибудь обучающее видео) и на то, как именно указываются столбцы в сырой выгрузке — это критично и для корректной работы дашборда, и для его быстрого масштабирования. Ниже разберём по шагам.

▶

Столбцы в сырой выгрузке всегда выделяем целиком — например, D:D, а не диапазон D1:D1000.

По нашей логике в выгрузке из JetStat данные будут регулярно обновляться — каждый день внизу добавляются новые строки. Если указать ограниченный диапазон (например, D1:D1000), то, когда строк станет больше 1000, новые данные просто перестанут подтягиваться в дашборд.

▶

Используем закрепление ссылки на столбец — то есть пишем $D:$D, а не просто D:D.

При копировании или растягивании формулы на другие ячейки (а это как раз способ быстро собрать дашборд) ссылки останутся фиксированными и не «съедут». Без знаков $ формула начнёт смещаться относительно новых ячеек, и расчёты собьются.

▶

Ячейку с названием флайта закрепляем полностью — $A$1.

Это нужно, чтобы при копировании или протягивании формулы она всегда ссылалась именно на эту ячейку и ничего не «съехало».

▶

Ячейку с датой закрепляем частично — $A3 (только столбец).

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

Результат:

Ссылка на пример.

Копируем для остальных абсолютных метрик

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

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

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

Далее нужно немного подредактировать формулы — заменить пару аргументов в каждой.

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

Ранее он ссылался на $D:$D (показы). Клики находятся в столбце E, а расходы — в F:

Поэтому меняем на $E:$E для кликов:

И на $F:$F для расхода:

Для заказов первый аргумент менять не нужно, но нужно поправить два момента:

заменить название листа — вместо Ozon (raw) указать AdRiver (raw);
изменить столбец, ссылающийся на название флайта — в выгрузке из AdRiver метрик меньше, поэтому вместо столбца J нужно указать I.

Сейчас в формуле указан лист «Ozon (raw)», а конверсии (и доход) у нас находятся на листе «AdRiver (raw)».

Можно, конечно, просто вручную заменить название листа (и в формулах для дохода тоже) — всего-то шесть правок: по три аргумента в заказах и три в доходе.

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

Выдели диапазон с ячейками, в которых нужно произвести замену (в нашем случае он небольшой):

Затем открой функцию «Замена» с помощью сочетания клавиш Ctrl + H и задай такие параметры:

Важно: обязательно поставь галочку «Искать в формулах», иначе замена не сработает.

После этого нажми «Заменить всё» — должно появиться уведомление, что выполнено 6 замен:

Теперь для заказов замени второй аргумент с 'AdRiver (raw)'!$J:$J на 'AdRiver (raw)'!$I:$I. В итоге формула должна выглядеть так:

Для дохода также заменяем ссылку на столбец с названием флайта — с J на I. Кроме того, меняем первый аргумент, и вместо столбца D (конверсии) указываем E (доход):

Последний штрих — протянуть все формулы вниз:

Важно: внизу пропала заливка — ничего страшного, перекрасим позже.

Сейчас главное — добавить расчётные метрики (например, CTR, CPC, CPA, ДРР и т. д.) и также протянуть формулы для них по всем датам.

Ссылка на пример.

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

Последний шаг — добавить формулы для расчётных метрик: CTR, CPC, CPA, ДРР и других.

Тут всё просто, но есть одна важная рекомендация: всегда оборачивай формулы в ЕСЛИОШИБКА, чтобы в ситуациях, когда происходит деление на ноль (например, нет кликов, конверсий или данных за день), не появлялась некрасиво выглядящая надпись:

Вот как выглядит формула:

То есть логика простая: если при вычислении формулы возникает ошибка, то вместо неё выводим пустую ячейку — чтобы дашборд выглядел аккуратно:

При желании можно сделать, чтобы вместо пустоты выводился ноль — это дело вкуса и формата отчётности:

Добавляем все расчётные метрики и протягиваем формулы вниз по всем строкам. Заливку мы тоже уже поправили:

Дашборд готов.