Если вы являетесь аналитиком данных в SaaS-компании или предприятии электронной коммерции с компонентом подписки, вас обязательно попросят проанализировать такие метрики, как отток, апгрейды и даунгрейды. Данные показатели помогают бизнесу понять состояние вашей базы подписчиков:

• Состояние вашей абонентской базы независимо от сезонности продлений (поскольку вы амортизируете доходы по месяцам).

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

• Ценность клиента, основанная на том, насколько долго данный пользователь продолжает платить вам деньги: пожизненная ценность клиента, средняя сумма контракта.

Часто такой анализ называют анализом регулярного ежемесячного дохода (Monthly Recurring Revenue, MRR), и его результаты выглядят примерно так:

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

select
  date_month,
  sum(is_active::integer) as customers,
  sum(mrr) as mrr,
from analytics.fct_mrr
group by 1

Построение дашборда на основе модели данных, вместо необработанной информации, дает ряд преимуществ:

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

• Вам придется преобразовывать данные только в одном месте: Скорее всего, ваши исходные данные для подписок не идеальны. Вам может понадобиться их подчистить, например, преобразовать пустые строки в значения NULL. Или вам может потребуется изменить свойство (grain) таблиц, например, преобразовать таблицу с одной записью на подписку с датами начала и окончания в таблицу в виде одной записи на текущий месяц. Выполнив эту работу в модели данных, вам не придется делать ее повторно при построении аналогичных анализов или в различных BI-инструментах.

• Ваши предложения относительно модели данных могут быть проверены: Создавая модели в dbt, вы можете явно указать и затем проверить любые предположения, которые вы делаете о своих данных. Например, если ваша бэкенд-команда сообщает, что клиент не может иметь две активные подписки одновременно, неплохо было бы добавить тест для подтверждения этого (вот пример).

• Бизнес-пользователи могут более простым способом изучать данные: Если в этом месяце количество оттоков выглядело несколько выше обычного, бизнес-пользователи могут зайти в систему и написать простой SQL, чтобы понять, какие именно клиенты ушли, не перегружая себя сложными данными.

Модель данных MRR

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

Следует рассмотреть категории изменений:

• новый: клиент - новый клиент, у которого не было предыдущей подпискиф;

• отток: в прошлом месяце клиент оплатил подписку, а в этом месяце нет. Клиент может покидать сервис много раз;

• апгрейд: клиент прибавил в объеме использования и теперь платит вам больше денег в месяц;

• даунгрейд: клиент уменьшил потребление и теперь платит вам меньше денег в месяц;

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

Если вы сразу готовы окунуться в проект dbt, чтобы увидеть, как это делается, посмотрите наш образец MRR-модели здесь. Или читайте дальше, для более детального ознакомления.

Как смоделировать данные по доходам от подписки

Шаг 1: Соберите необходимые источники данных

Прежде чем преобразовывать данные, они должны быть в вашем хранилище. Данные о подписке в зависимости от вашей исходной системы могут иметь различную структуру - приведем некоторые из них:

• Таблица периодов подписки с датами начала и окончания.

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

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

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

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

К счастью, наши данные также оказались абсолютно чистыми:

• Каждый клиент одновременно обладает только одной активной подпиской.

• Дата старта и окончания каждой подписки приходится на начало месяца.

Мы даже подтвердили верность наших предположений, применив некоторые dbt-тесты!

Шаг 2: Распределите подписки по датам так, чтобы на каждого клиента приходилось по одной записи в месяц

Требуемая техника SQL: распределение дат (date spining) | Требуемая техника dbt: пакеты

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

Обычно мы делаем это за счет добавления к таблице "месяцев":

select
  months.date_month,
  subscriptions.customer_id,
  subscriptions.subscription_id,
  susbcriptions.monthly_revenue
  
from subscriptions
  
inner join months
  -- all months after start date
  on  months.date_month >= customers.date_month_start
  -- and before end date
  and months.date_month <= customers.date_month_end

Мы используем макрос date_spine из пакета dbt-utils для создания таблицы всех месяцев.

Но подождите, у нашего клиента не было подписки с февраля (2019-02-01) по апрель (2019-04-01), и мы хотим зафиксировать это как "отток" и "реактивацию"!

Для MRR-анализа важно "заполнить" все пропущенные месяцы, когда клиент не был активен. Вместо того, чтобы использовать простое распределение даты для разворачивания периода подписки, мы напишем дополнительный SQL, чтобы сначала найти все месяцы между первой и последней активностью клиента, и использовать их в качестве начальной и конечной дат.

with customers as (

  select
    customer_id,
    date_trunc('month', min(start_date)) as date_month_start,
    date_trunc('month', max(end_date)) as date_month_end

  from subscription_periods

  group by 1

),

customer_months as (
  select
    customers.customer_id,
    months.date_month

  from customers
  
  inner join months
    -- all months after start date
    on  months.date_month >= customers.date_month_start
    -- and before end date
    and months.date_month <  customers.date_month_end
  
)
  
-- join the customer_months spine to MRR base model
joined as (

  select
    customer_months.date_month,
    customer_months.customer_id,
    coalesce(subscription_periods.monthly_amount, 0) as mrr

  from customer_months

  left join subscription_periods
    on customer_months.customer_id = subscription_periods.customer_id
    -- month is after a subscription start date
    and customer_months.date_month >= subscription_periods.start_date
    -- month is before a subscription end date
    and customer_months.date_month < subscription_periods.end_date
  
)
...

Шаг 3: Определить первый и последний месяцы активности клиента

Требуемая SQL-техника: Оконные функции

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

...
final as (
  select
    date_month,
    customer_id,

    mrr,

    mrr > 0 as is_active,

    -- calculate first and last months
    min(case when is_active then date_month end) over (
      partition by account_id
    ) as first_active_month,

    max(case when is_active then date_month end) over (
      partition by account_id
    ) as last_active_month,

    -- calculate if this record is the first or last month
    first_active_month = date_month as is_first_month,
    last_active_month = date_month as is_last_month
  
  from joined

)

select * from final

Шаг 4: Создайте месяц "оттока"

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

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

select
  dateadd(month, 1, date_month)::date as date_month,
  customer_id,
  0::float as mrr,
  false as is_active,
  first_active_month,
  last_active_month,
  false as is_first_month,
  false as is_last_month
  
from mrr
  
where is_last_month

Затем, далее, объедините результаты этого запроса с другими записями, чтобы в итоге мы получили все месяцы для клиента.

with unioned as (

  select * from {{ ref('mrr_xf') }}
  
  union all
  
  select * from {{ ref('mrr_last_churn') }}
)
...

Шаг 5: Используйте оконные функции для определения MRR за предыдущий месяц

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

select
  *,

  coalesce(
    lag(is_active) over (partition by customer_id order by date_month),
    false
  ) as previous_month_is_active,

  coalesce(
    lag(mrr) over (partition by customer_id order by date_month),
    0
  ) as previous_month_mrr,
  
  mrr - previous_month_mrr as mrr_change
  
from unioned

Шаг 6: Добавьте категорию изменения MRR

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

...
case
  when is_first_month
      then 'new'
  when not(is_active) and previous_month_is_active
      then 'churn'
  when is_active and not(previous_month_is_active)
      then 'reactivation'
  when mrr_change > 0 then 'upgrade'
  when mrr_change < 0 then 'downgrade' end as change_category

Шаг 7: Напишите тесты данных и документацию

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

Данная модель должна работать как минимум со следующими тестами: Тесты на исходных данных для проверки ваших предположений, Тесты на конечной модели, чтобы убедиться, что вы не допустили ошибок в SQL, в частности: Проверьте ваш первичный ключ, используя тест unique и not_null (это поможет выявить фанауты!). Включите тесты not_null для других столбцов. Кроме того, со временем вы, вероятно, добавите тесты для выявления edge кейсов, которые нельзя предугадать прямо сейчас.

Наконец, добавьте документацию, чтобы ваши стейкхолдеры понимали каждый столбец.

Шаг 8: Запускайте!

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

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

Создание собственного продукта

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

1. Как ваш бизнес относится к цифрам, которые не полностью совпадают с финансовыми отчетами?

2. Позволяет ли ваш бизнес пользователям приостанавливать подписку? Должно ли это отслеживаться иначе, чем отток?

3. Какова ваша базовая единица измерения (т.е. клиент, аккаунт, продукт)?

4. Существуют ли дополнительные расходы, которые полезно добавить в качестве атрибута в эту таблицу?

5. Как следует обрабатывать неполные месяцы?

6. Все ли подписки оплачиваются в один и тот же день, или они оплачиваются в разные дни для каждого клиента? Если последнее, то каково бизнес-правило для месяца, в котором признается доход?

Это может показаться быстрым решением, но основная часть работы будет проделана именно здесь. Многие из перечисленных примеров требуют компромиссов - помните, что MRR-дашборд должен помогать бизнес-пользователям в оценке состояния компании, а не использоваться для финансовой сверки. Хотя идеальная сверка может выглядеть как хорошая цель, но в итоге вы можете попасть в черную дыру, пытаясь выяснить, куда делись $0,02.

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

Посмотрите код

Мы создали проект dbt с некоторыми примерами данных, чтобы увидеть его в действии:

• Ознакомьтесь с примером проекта dbt здесь

• Посмотреть исходный код этого проекта можно здесь

Сегодня состоится открытое занятие по вопросам оптимизации производительности. На уроке обсудим:
— Какие проблемы с производительностью будоражат хранителей DWH;
— Существующие подходы, ключевые принципы и практики оптимизации;
— Как легко положить базу данных (конечно же, для того, чтобы так не делать).

Регистрация на урок доступна по ссылке.