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

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

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

Преимущество MRR в том, что такой отчет нормирован на начало месяца каждого оплаченного периода, и таким образом в каждом месяце мы видим суммы без скачков. Кроме того, если использовать стандартный бухгалтерский отчет, что для всех платежей на год начинающихся не 1 числа месяца будем получать 13 месяцев в отчете, в которых есть вклад от такого платежа, хотя клиент оплачивал всего-лишь год — 12 месяцев. Кроме того, отчет MRR учитывает будущие платежи.

Как же посчитать MRR небольшой компании, с чего начать, какие данные и как использовать? Для начала, весь отчет мы построим используя очень простую выгрузку из нашей учетной системы, например, банковского счета, взяв данные из выписок по оплате за наш сервис. Какие данные нам понадобятся?

В нашей выгрузке обязательно должна быть следующая информация: customer_id — идентификатор клиента, уникальное значение определяющее каждого отдельного клиента; paid_amount — сумма, оплаченная клиентом; paid_plan – тип тарифного плана, для примера будем использовать 2 типа monthly и annual; invoice_crated – дата выставления счета, ее же для простоты будем считать датой начала оплаченного периода. В итоге наша выгрузка будет выглядеть примерно так.

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

=IF(D2="monthly";EDATE(B2;1)-1;EDATE(B2;12)-1)

Что делает эта формула? Во-первых, она проверяет тип тарифного плана помесячный – monthly или annually – оплата сразу за год. Далее мы вычисляем дата с помощью функции EDATE, которая вычисляет правильно дата отстоящую на 1 месяц или на 12 месяцев от текущей, а также отнимает 1 день, чтобы правильно попадать в период оплаты. В итоге получаем следующую картину:

Следующим этапом будет создать промежуточную таблицу по каждому клиенту, а именно распределение уплаченных средств согласно периодам (нормированным на начало месяца) в которые они попадают. Я называют такую таблицу Money Per Period или MPP.

Фактически эта таблица, в которой строки это информация о нашем клиенте, а столбцы это периоды, в ячейках указывается уплаченная клиентом сумма за этот период.

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

=IF(AND(DATEVALUE("1."&B$1)>=DATEVALUE(data!$B2);
DATEVALUE("1."&B$1)<=DATEVALUE(data!$F2)); data!$E2/(DATEDIF(data!$B2;data!$F2;"Ym")+1);0))

В итоге мы получаем следующий вид

При этом важно понимать, что если у нас в выгрузке есть клиенты, которые платили несколько раз, то они будут в данной выборке иметь несколько строчек, например, вот так

Теперь надо свести этот отчет по каждому уникальному клиенту, чтобы правильно считать движение MRR (new, old, reactivation, expansion, contraction, churn). Для этого используем функцию

=SUMIFS(mpp!B2:B112;mpp!$A$2:$A$112;$A2)

И получаем следующую таблицу

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

Теперь можно переходить к построению отчета о движении MRR. Для этого нам надо определиться как мы будем считать new, old, reactivation, expansion, contraction и churn. New – это поступления от клиента, который нам ни разу еще не платил нам, то есть левее выбранного периода поступлений нет. Old — поступления в выбранном периоде равны поступлениям от предыдущего периода. Reactivation — поступления в текущем периоде не равны нулю, при этом в прошлом периоде они были равны нулю, а так же были поступления до предыдущего периода. Expansion – поступления в текущем периоде больше поступлений в предыдущем. Contraction – поступления в текущем периоде меньше поступлений в предыдущем. Churn – поступления в текущем периоде равны нулю, а в предыдущем больше нуля.

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

Чем плох данный метод?

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

Все подписчики:

Один план:

Другой план:

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