Привет, Хабр!
Мы — Александр Погудин и Елена Сидорова, BI‑разработчики в Лемана Тех. Однажды к нам пришёл заказчик и попросил разработать дашборд, который будет показывать все ключевые метрики воронки продаж на одной странице.
Можно сделать «в лоб», но это семь таблиц фактов, 150+ мер и каша под капотом. И это только на старте проекта, до того, как заказчик попросит добавить ещё 30 метрик...
В статье расскажем, как мы обошлись одной EAV‑таблицей фактов и несколькими универсальными мерами, чтобы собрать на одном листе стратегического дашборда десятки метрик для C‑level.
Запрос
Желание заказчика довольно естественно. Хочется легко и непринуждённо зайти в один дашборд и увидеть максимальное количество показателей, которое позволит понять текущий пульс направления бизнеса, быстрее реагировать на изменения и видеть как сильные, так и уязвимые места, куда нужно направить больше внимания.
Боль заказчика понятна, приборную панель надо делать.
При этом не требуется глубокой детализации и хитрых срезов. Просто по каждому шагу воронки продаж одна цифра, которая говорит бизнесу: ОК или НЕ ОК.
А для более точечного изучения вопроса идти к конкретной команде, у которой есть свои настроенные дашборды с подробностями и деталями.
Оперативное реагирование на изменения потребностей рынка — один из важнейших критериев успешной работы, поэтому мы потёрли ладошками и принялись изучать ТЗ.
Перечень метрик дали примерно такой:
Тридцать метрик. И для каждой — факт, факт предыдущего года, дельта, план, отклонение от плана.
Ну ок. Начали распутывать метрики и раскладывать по источникам. Составляем схему, чтобы окинуть взглядом поле работ:
Получается макаронный монстр. Семь разных баз. Какие-то метрики и вовсе собираются одновременно из нескольких баз.
Уже на этом этапе понятно, что надо активно думать про масштабируемость и выбрать стратегию.
Как выкрутились?
Сразу прыгаем к результату: решение было найдено.
Получился ли треш в модели данных? Неа, вот таблица фактов:
Получился ли мрак в мерах? Неа, вот меры. Пара технических мер, одна для факта, вторая для факта прошлого года, третья для дельты, и для плана и отклонения от плана было бы ещё две.
Как же это получилось?
Ответ: EAV!
EAV
Суть заключается в следующем: EAV (Entity-Attribute-Value) — это в общем случае такая гибкая модель данных, в которой атрибуты какой-то сущности развёрнуты по вертикали.
В нашем случае за сущность принимаем дату, за атрибуты — метрики. Например:
В EAV-модели мы транспонируем таблицу:
И теперь, чтобы взять динамику по конкретной метрике, надо лишь дополнительно применить фильтр по названию метрики:
К чему это приводит?
Плюс первый
Сколько бы мы метрик ни добавили, схема таблицы не меняется. Это будет всегда три колонки.
Можно написать три меры — для факта, факта прошлого года, дельты — и управлять тем, для какой метрики это применяется, при помощи фильтра по metric_name в самом визуальном элементе.
Плюс второй
Мало того — количество баз на нас теперь тоже не сказывается. Результат SQL к каждому источнику будет возвращать одинаковую структуру, и все таблицы можно будет соединить между собой в одну таблицу фактов на стороне PowerQuery.
Если одна метрика собирается из нескольких источников — так же на стороне PowerQuery можно заджойнить компоненты в одну таблицу, посчитать метрику и результат в той же структуре EAV присоединить к общей таблице фактов.
Плюс третий
Такая модель позволяет, например, сделать дополнительную широкую таблицу-справочник по всем мерам:
Поля из этой таблицы, используя тот же самый фильтр по metric_name, можно выводить, например, в подсказки и описания визуальных элементов, в легенду, на инфостраничку.
С таблицей разобрались, а что с мерами?
Поскольку наша таблица фактов состоит из столбцов «дата — метрика — значение метрики» и все метрики уже предрассчитаны, осталось сделать меры для факта, факта предыдущего года, дельты, план, отклонение от плана.
Подготовительные меры
Итак, сначала мы считаем пререквизиты для мер:
1. Выводим инфо по значению метрики
000 CurrentValue = MAX(fact_report[metric_value])
— это даст нам максимальное значение из столбца с предрассчитанными метриками.2. Выводим инфо по названию метрики
000 MetricFact = SELECTEDVALUE(fact_report[metric_name])Форматирование метрик
Затем мы поняли, что метрики могут быть в разных форматах — абсолюты, проценты, тысячи, десятичные числа и т. д.
И вот этот момент нужно задать изначально. Мы берём наши пререквизиты мер и по названию каждой меры задаём, в каком формате она должна выводиться:
KPI_3p_format =
VAR MaxValue = [000 CurrentValue]
VAR Metric = [000 MetricFact]
RETURN
SWITCH(
TRUE(),
Metric IN { "Срок_доставки_факт", "Срок_доставки_план" },
FORMAT(MaxValue, "#,##0.0 дней"),
Metric IN { "Визиты_все", "Визиты_органика", "Визиты_платный_трафик" },
FORMAT(MaxValue / 1000, "#,##0 тыс"),
Metric IN { "GMV_доставленный_3P", "GMV_оплаченный_3P", "Выручка_3P_аналитика" },
FORMAT(MaxValue / 1000000, "#,##0 млн"),
Metric IN { "Индекс_цен_1P", "Индекс_цен_3P" },
FORMAT(MaxValue, "#,##0.00"),
Metric IN { "Продавцы_3P", "Заказы_оплаченные_3P", "Товары_3P" },
FORMAT(MaxValue, "#,##0"),
Metric IN { "OTIF_3P", "OTIF_доставки_3P", "Доля_возвратного_GMV_3P" },
FORMAT(MaxValue, "0.0%"),
Metric IN { "Доля_визитов_с_добавлением_в_корзину_3P", "Доля_визитов_с_покупкой_3P"
, "Доля_SKU_с_рейтингом_4_5_и_выше_3P" },
FORMAT(MaxValue, "0.00%"),
FORMAT(MaxValue, "#,##0.00")
)Аналогично делаем для расчёта прошлого периода, но меняем переменную для расчёта значения на:
VAR MaxValue =
CALCULATE(
[000 CurrentValue],
SAMEPERIODLASTYEAR('Calendar'[Date])
)Расчёт дельты
Итого у нас получилось 2 меры, которые задают нужный формат данных. Остаётся посчитать дельту:
KPI_3p_YoY =
VAR CurrentValue = [000 CurrentValue]
VAR PreviousValue =
CALCULATE(
[000 CurrentValue],
SAMEPERIODLASTYEAR('Calendar'[Date])
)
VAR result =
IF(
PreviousValue <> BLANK() && CurrentValue <> BLANK(),
CurrentValue / PreviousValue - 1,
BLANK()
)
RETURN
resultТакой набор позволяет нам не захламлять отчёт мерами и задавать только формат вывода метрики в мере, прописывая его так же, как мы сделали выше.
Чтобы менять метрики в визуальных элементах, нужно просто выбрать фильтром название нужной метрики =)
Что было с визуалом?
Заказчик принёс свой вариант визуала. Мы его посмотрели, подумали — и решили поискать дополнительные варианты.
Задача
Вывести воронку, то есть последовательные шаги по разным направлениям бизнеса слева направо. Каждый шаг раскрывается на внутренние, более мелкие шаги.
Использование Figma
Для создания фона мы решили подключить Figma. Чем, к слову, хороша Figma в качестве фона в отчёт?
Помогает избежать создания кучи разных визуальных блоков, созданных вручную через коробку в инструменте.
В случае изменения отчёта можно очень быстро фон подредактировать и вставить новой картинкой в отчёт.
Вариантов было много. Вот часть из того, что мы пробовали и обсуждали:
Затем мы начали тестировать разные варианты KPI карточек, и вариантов тоже набралось достаточно много.
В итоге 7 баз, 30+ метрик и перспектива пары сотен мер превратились в
одну EAV‑таблицу фактов;
несколько универсальных мер;
стратегический дашборд, который легко масштабируется.
Какие ограничения?
У этого подхода, разумеется, есть свои ограничения.
Ограничение 1. Идентичный набор разрезов
Все метрики должны иметь идентичный набор разрезов.
Если одни метрики будут иметь разрез по городам, другие — по магазинам, третьи — по типу клиента, то тут надо либо как-то наращивать количество названий метрик (например, gmv_store_1, gmv_store_2, gmv_store_100), либо добавлять новые колонки, которые будут применимы к одним метрикам, но не применимы к другим. Это быстро засорит модель данных и лишит её простоты и читаемости. В штатном же случае это таблица из трёх колонок: дата, метрика, значение — суть читается с ходу.
С другой стороны, дашборд-кокпит и не предполагает уйму фильтров, которые влияют на одни элементы и не влияют на другие. Это должна быть простейшая приборная панель со светофором, которая сообщает о наличии проблем у направления в целом. Для деталей есть специализированные дашборды.
Ограничение 2. Аддитивность метрик
Либо метрики должны быть аддитивны, либо дашборд будет выводить только один отчётный период (один день / одну неделю / один месяц) за раз.
Логичный минус любого агрегата. Таблица предполагает, что метрика уже посчитана для каждого отчётного периода. Если метрика неаддитивная (например, количество пользователей), то мы не сможем выводить её в сумме за несколько периодов (один посетитель в дату_1 и тот же посетитель в дату_2 не дают в сумме за два дня двух посетителей).
Соответственно, либо в дашборд можно выводить один отчётный период за раз, либо под капотом держать разрез до идентификаторов пользователей, что уже не ложится на модель.
Вывод
Для задач уровня «одним взглядом понять, всё ли нормально по ключевым этапам воронки и продаж», EAV‑подход оказался ровно тем, что нужно.
Вместо того чтобы плодить 150+ мер под каждый показатель и его варианты по периодам, мы обошлись одной EAV‑таблицей фактов и несколькими универсальными мерами, которые работают с любым набором метрик.
Приятный бонус в том, что EAV хорошо переживает изменения: можно добавлять новые метрики и подтягивать новые источники без изменения модели данных и дополнительных расчётов в мерах.
Ещё один плюс — возможность навесить богатый справочник метрик и использовать его в подсказках и описаниях визуалов, чтобы C‑level видел не только цифру, но и контекст: как она считается и за что отвечает.
Обратная сторона — нельзя хаотично усложнять модель: набор разрезов должен быть единым, а запросы в стиле «давайте сюда ещё один особый срез только для этой метрики» нужно отлавливать и обсуждать на берегу. Работа с такой моделью данных требует дисциплины и хладнокровия.