Перейти к основному содержимому

Кумулятивные метрики

(Версии dbt: v1.12 и новее)

Накопительные метрики агрегируют значения других метрик за заданный период накопления. Если период не указан, метрика накапливает значения за весь доступный временной диапазон.

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

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

Параметры​

(Версии dbt: v1.12 и новее)
Параметр
Описание
ОбязательныйТип
nameНазвание метрики.ОбязательныйString
descriptionОписание метрики.НеобязательныйString
typeТип метрики (cumulative, derived, ratio или simple).ОбязательныйString
labelНеобязательная строка, определяющая отображаемое значение в инструментах, использующих метрику. Допускает обычный текст, пробелы и кавычки (например, orders_total или "orders_total").НеобязательныйString
input_metricНазвание метрики, на которую ссылаются. Поддерживает следующие вложенные параметры: name, filter и alias.ОбязательныйDict
input_metric.nameНазвание метрики, на которую ссылаются.ОбязательныйString
input_metric.filterФильтр, применяемый к метрике.НеобязательныйString
input_metric.aliasПсевдоним, применяемый к метрике.НеобязательныйString
join_to_timespineЛогическое значение, указывающее, следует ли соединять агрегированную метрику с таблицей временной шкалы, чтобы заполнить пропущенные даты. По умолчанию — false.НеобязательныйBoolean
windowЗадаёт окно накопления, например 1 month, 7 days или 1 year. Нельзя использовать вместе с grain_to_date.НеобязательныйString
grain_to_dateЗадаёт гранулярность накопления, например hour, day, week, month, year, при этом накопление начинается заново в начале каждого периода указанной гранулярности. Например, при выборе month метрика агрегируется с начала месяца до текущей даты (month to date). Нельзя использовать вместе с window.НеобязательныйString
period_aggОпределяет способ повторной агрегации накопительной метрики при запросе с гранулярностью, отличной от заданной по умолчанию: first, last или average. Если period_agg не указан, по умолчанию используется first.НеобязательныйString

Полная спецификация​

Ниже приведена полная спецификация накопительных метрик вместе с примером:

(Версии dbt: v1.12 и новее)
metrics:
- name: my_advanced_cumulative_metric # required
description: my_description # optional
label: my_label # optional
type: cumulative # required -- cumulative | ratio | derived | conversion
# if cumulative, optionally can supply window or grain_to_date, but not both
window: 1 week # (interval)
# grain_to_date: hour # hour | day | week | month | year | ...
input_metric: # required, must refer to metric name or metric dict
name: my_metric_name_from_another_semantic_model
filter: "{{ Dimension('entity__dimension_name') }} > 10"
alias: my_metric_name_a_week_ago_in_another_semantic_model

Пример кумулятивных метрик​

Кумулятивные метрики измеряют данные за заданный период и считают период бесконечным, если параметр окна не передан, накапливая данные за все время.

Следующий пример показывает, как определить кумулятивные метрики в YAML-файле:

(Версии dbt: v1.12 и новее)
  • cumulative_order_total: рассчитывает накопительную сумму заказов за всё время. Параметр input_metric указывает простую метрику order_total, которую нужно агрегировать.

  • cumulative_order_total_l1m: рассчитывает накопительную сумму заказов за последний месяц. Параметр window задаёт окно в один месяц.

  • cumulative_order_total_mtd: рассчитывает накопительную сумму заказов с начала месяца. Параметр grain_to_date задаёт период month.

models/marts/fct_orders.yml
metrics:
- name: cumulative_order_total
label: "Cumulative order total (All-Time)"
description: "The cumulative value of all orders"
type: cumulative
input_metric: order_total

- name: cumulative_order_total_l1m
label: "Cumulative order total (L1M)"
description: "Trailing 1-month cumulative order total"
type: cumulative
window: 1 month
input_metric: order_total

- name: cumulative_order_total_mtd
label: "Cumulative order total (MTD)"
description: "The month-to-date value of all orders"
type: cumulative
grain_to_date: month
input_metric: order_total

Опции гранулярности​

(Версии dbt: v1.12 и новее)

Используйте параметр period_agg с функциями first, last и average для агрегации накопительных метрик за запрошенный период. Это необходимо, поскольку варианты гранулярности для накопительных метрик отличаются от вариантов для других типов метрик.

  • Для других метрик мы используем функцию date_trunc, чтобы задать гранулярность.
  • Однако накопительные метрики неаддитивны (их значения нельзя суммировать), поэтому мы не можем использовать функцию date_trunc для изменения их временной гранулярности.
  • По умолчанию мы берём первое значение периода. Вы можете изменить это поведение, указав другую функцию с помощью параметра period_agg.

В следующем примере мы определяем накопительную метрику cumulative_revenue, которая рассчитывает накопительную выручку по всем заказам:

models/marts/fct_orders.yml
metrics:
- name: cumulative_revenue
description: "Накопительная выручка по всем заказам."
label: "Накопительная выручка (за всё время)"
type: cumulative
input_metric: revenue
period_agg: first # Необязательный параметр. По умолчанию: first. Допустимые значения: first|last|average

В этом примере для period_agg задано значение first, при котором выбирается первое значение в пределах интервала выбранной гранулярности. Чтобы запросить cumulative_revenue по неделям, используйте следующий синтаксис запроса:

  • dbt sl query --metrics cumulative_revenue --group-by metric_time__week
 Разверните, чтобы увидеть скомпилированный SQL

Обратите внимание на использование функции window для выбора первого значения. Для last и average мы заменили бы функцию first_value() в сгенерированном SQL на last_value() и average соответственно.

-- повторная агрегация метрики через group by
select
metric_time__week,
metric_time__quarter,
revenue_all_time
from (
-- оконная функция для повторной агрегации метрики
select
metric_time__week,
metric_time__quarter,
first_value(revenue_all_time) over (
partition by
metric_time__week,
metric_time__quarter
order by metric_time__day
rows between unbounded preceding and unbounded following
) as revenue_all_time
from (
-- соединение с самим собой по временной шкале
-- передача только элементов: ['txn_revenue', 'metric_time__week', 'metric_time__quarter', 'metric_time__day']
-- агрегация измерений
-- вычисление метрик через выражения
select
subq_11.metric_time__day as metric_time__day,
subq_11.metric_time__week as metric_time__week,
subq_11.metric_time__quarter as metric_time__quarter,
sum(revenue_src_28000.revenue) as revenue_all_time
from (
-- временная ось
select
ds as metric_time__day,
date_trunc('week', ds) as metric_time__week,
date_trunc('quarter', ds) as metric_time__quarter
from mf_time_spine subq_12
group by
ds,
date_trunc('week', ds),
date_trunc('quarter', ds)
) subq_11
inner join fct_revenue revenue_src_28000
on (
date_trunc('day', revenue_src_28000.created_at) <= subq_11.metric_time__day
)
group by
subq_11.metric_time__day,
subq_11.metric_time__week,
subq_11.metric_time__quarter
) subq_16
) subq_17
group by
metric_time__week,
metric_time__quarter,
revenue_all_time

Параметры окна​

Этот раздел содержит примеры, когда следует указывать и не указывать опции окна.

(Версии dbt: v1.12 и новее)
  • Если указан период, MetricFlow применяет скользящее окно к базовой простой метрике, например для отслеживания активных пользователей за неделю с окном в 7 дней.
  • Если период не указан, накопительные метрики накапливают значения за всё время, что полезно для расчёта нарастающих итогов, таких как текущая выручка и количество активных подписок.
 Пример с указанным окном

Если указан параметр окна, MetricFlow применяет скользящее окно к базовой метрике.

Предположим, базовая метрика customers настроена на подсчёт уникальных клиентов, размещающих заказы в Jaffle shop:

models/marts/customers.yml
models:
- name: customers
semantic_model:
enabled: true
...

metrics:
- name: customers
description: "Уникальные клиенты, размещающие заказы в Jaffle shop"
label: "Клиенты"
type: simple
agg: count_distinct
expr: customer_id

Накопительную метрику weekly_customers можно определить следующим образом:

models/marts/customers.yml
metrics:
- name: weekly_customers
description: "Активные клиенты за неделю со скользящим окном в 7 дней"
label: "Активные клиенты за неделю"
type: cumulative
input_metric: customers
window: 7 days
period_agg: first # При использовании нестандартной детализации с накопительными метриками требуется повторная агрегация. period_agg: first выбирает первое значение в каждом окне детализации при повторной агрегации.

В приведённом примере YAML обратите внимание на следующие параметры:

  • type: укажите cumulative, чтобы обозначить тип метрики.
  • input_metric: укажите метрику для агрегации (в данном случае — customers).
  • window: укажите окно накопления (в данном случае — 7 дней).
  • period_agg: укажите функцию повторной агрегации (в данном случае — first).
  • grain_to_date: укажите интервал детализации, с начала которого накапливаются значения (в данном случае — week).

Например, для накопительной метрики weekly_customers MetricFlow берёт скользящее окно в 7 дней с соответствующими клиентами и применяет функцию подсчёта уникальных значений.

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

 Пример без указания окна

Предположим, у вас (для этого примера — компании, работающей по подписной модели) есть таблица журнала событий со следующими столбцами:

  • date: столбец с датой.
  • user_id: (целое число) идентификатор пользователя, ответственного за событие.
  • subscription_plan: (целое число) столбец, указывающий конкретный тарифный план подписки пользователя.
  • subscription_revenue: (целое число) столбец, указывающий стоимость тарифного плана подписки.
  • event_type: (целое число) столбец, принимающий значение +1 при добавлении подписки или -1 при удалении подписки.
  • revenue: (целое число) столбец, в котором event_type умножается на subscription_revenue, чтобы отразить сумму прироста или потери выручки за определённую дату.

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

models/marts/fct_orders.yml
metrics:
- name: current_revenue
description: "Текущая выручка"
label: "Текущая выручка"
type: cumulative
input_metric: revenue
- name: active_subscriptions
description: "Количество активных подписок"
label: "Активные подписки"
type: cumulative
input_metric: subscription_count

Нарастающий итог с начала периода​

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

(Версии dbt: v1.12 и новее)

Например, рассмотрим базовую простую метрику order_total, определённую в семантической модели:

models:
- name: fct_orders
semantic_model:
enabled: true

# Simple metrics defined within semantic model
metrics:
- name: order_total
description: "Сумма итоговых сумм заказов"
type: simple
agg: sum
expr: order_total

Сравним окно длительностью 1 месяц и накопление с начала текущего месяца (grain to date).

  • При использовании окна к накопительной метрике применяется скользящее окно длительностью 1 месяц
  • При накоплении с начала текущего месяца значение сбрасывается в начале каждого месяца.
(Версии dbt: v1.12 и новее)
models/marts/fct_orders.yml
# Top-level metrics key for cumulative metrics
metrics:
- name: cumulative_order_total_mtd
label: "Cumulative order total (MTD)"
description: "The month-to-date value of all orders"
type: cumulative
grain_to_date: month
input_metric: order_total

Кумулятивная метрика с зерном до даты:

(Версии dbt: v1.12 и новее)
models/marts/fct_orders.yml
# Ключ metrics верхнего уровня для накопительных метрик
metrics:
- name: orders_last_month_to_date
label: "Заказы с начала месяца"
description: "Количество заказов с начала месяца"
type: cumulative
grain_to_date: month
input_metric: order_count
 Разверните, чтобы посмотреть результат компиляции в SQL
with staging as (
select
subq_3.date_day as metric_time__day,
date_trunc('week', subq_3.date_day) as metric_time__week,
sum(subq_1.order_count) as orders_last_month_to_date
from dbt_jstein.metricflow_time_spine subq_3
inner join (
select
date_trunc('day', ordered_at) as metric_time__day,
1 as order_count
from analytics.dbt_jstein.orders orders_src_10000
) subq_1
on (
subq_1.metric_time__day <= subq_3.date_day
) and (
subq_1.metric_time__day >= date_trunc('month', subq_3.date_day)
)
group by
subq_3.date_day,
date_trunc('week', subq_3.date_day)
)

select
*
from (
select
metric_time__week,
first_value(orders_last_month_to_date) over (partition by date_trunc('week', metric_time__day) order by metric_time__day) as cumulative_revenue
from
staging
)
group by
metric_time__week,
cumulative_revenue
order by
metric_time__week
1

Пример реализации SQL​

Чтобы вычислить кумулятивное значение метрики за заданный период, мы выполняем соединение по временной шкале с таблицей временной оси, используя основное временное измерение в качестве ключа соединения. Мы используем окно накопления в соединении, чтобы решить, следует ли включать запись в конкретный день. Следующий SQL-код, полученный из примера кумулятивной метрики, предоставлен для справки:

Чтобы реализовать кумулятивные метрики, обратитесь к примеру SQL-кода:

select
count(distinct distinct_users) as weekly_active_users,
metric_time
from (
select
subq_3.distinct_users as distinct_users,
subq_3.metric_time as metric_time
from (
select
subq_2.distinct_users as distinct_users,
subq_1.metric_time as metric_time
from (
select
metric_time
from transform_prod_schema.mf_time_spine subq_1356
where (
metric_time >= cast('2000-01-01' as timestamp)
) and (
metric_time <= cast('2040-12-31' as timestamp)
)
) subq_1
inner join (
select
distinct_users as distinct_users,
date_trunc('day', ds) as metric_time
from demo_schema.transactions transactions_src_426
where (
(date_trunc('day', ds)) >= cast('1999-12-26' as timestamp)
) AND (
(date_trunc('day', ds)) <= cast('2040-12-31' as timestamp)
)
) subq_2
on
(
subq_2.metric_time <= subq_1.metric_time
) and (
subq_2.metric_time > dateadd(day, -7, subq_1.metric_time)
)
) subq_3
)
group by
metric_time,
limit 100;

Ограничения​

Если вы указываете window в определении вашей кумулятивной метрики, вы должны включить metric_time в качестве измерения в SQL-запросе. Это связано с тем, что окно накопления основано на времени метрики. Например,

select
count(distinct subq_3.distinct_users) as weekly_active_users,
subq_3.metric_time
from (
select
subq_2.distinct_users as distinct_users,
subq_1.metric_time as metric_time
group by
subq_3.metric_time

Нашли ошибку?

0
Loading