Всем привет! Меня зовут Антон Лопатин, компания ADAD Digital. С 2000 года мы занимаемся web-разработкой, c 2006 продвижением и рекламой, и с 2018 активно развиваем направление BI-аналитики. Мы внедряем BI-отчетность своим клиентам и разрабатываем дашборды на заказ. И в этой статье я хочу рассказать об одном методе, применяемом для оценки качества источников привлечения клиентов.
Проблема
Часто при оценке качества привлекаемого трафика аналитик ограничивается показателями конверсии, стоимости лида или заказа, объемом полученного дохода, ROI. Проще говоря, чем дешевле трафик из источника и чем больше денег приносит источник, тем лучше.
Однако, следует понимать, что доход от привлеченных клиентов компания получает неравномерно. При одинаковом суммарном доходе из разных источников, один из них может привлекать очень крупных клиентов, которые обеспечивают значительную часть общей выручки компании, другой же приносит множество мелких клиентов, расходующих ресурсы компании.
Более эффективно анализировать качество трафика, привлекаемого из различных источников, можно, используя ABC-классификацию клиентов.
Что такое ABC-классификация клиентов
Это разделение клиентов на несколько категорий. Критерием для их разделения является суммарный объем платежей. Фактически, этот показатель означает размер дохода, который принесли компании клиенты.
Используется три категории:
А — это покупатели, которые обеспечили основной доход. Работа с ними является приоритетной для компании.
B — это средний уровень вложений в благосостояние компании.
С — наименьший уровень, такие клиенты наименее интересны.
Расчет и отнесение потребителей к той или иной категории осуществляется путем математических вычислений. Это удобнее всего представить поэтапно:
- вычислить 70% от общей суммы реализации;
- выделить объем платежей по каждому клиенту от максимального до самого незначительного;
- сложить объемы платежей от самого крупного клиента до тех пор, пока не получится сумма, означающая 70% от общего дохода. Это будут клиенты категории А;
- взять еще 20% от общего объема и посчитать клиентов, которые принесли эти 20%. Они войдут в категорию В;
- оставшиеся 10% приходятся на категории С. Это потребители с минимальным вкладом в доход компании.
Решение
Рассмотрим решение на примере заказчика, продающего цифровые услуги: подписку на сервис с переменной абонентской платой. Сквозная аналитика основана на данных CRM Битрикс 24 и рекламных кабинетов (собираемых в том числе через myBI Connect).
Мы обогатили модель данными биллинга (реальные значения заказчика изменены) и получили возможность оценить качество привлекаемых клиентов с точки зрения ABC-классификации в разрезе рекламных систем, площадок, рекламных кампаний и даже ключевых фраз.
Данные о суммарном доходе и числе клиентов вывели в таблицах с сегментацией по классам, группам тарифов, типам трафика и параметрам рекламных источников (система, площадка, кампания и так далее). Вот так выглядит рабочий отчет:
Каждая таблица фильтрует остальные. Можно видеть, например, структуру клиентов для определенного вида трафика или структуру трафика для определенного класса клиентов.
Здесь видно, что сетевые кампании в Яндексе приводят в основном клиентов класса С:
Основная доля клиентов класса А привлечены посредством холодного обзвона:
Модель данных
Модель состоит из таблиц фактов, нескольких справочников и вспомогательной таблицы.
Основная таблица фактов (billing facts) представляет из себя выгрузку данных биллинга (биллинговый идентификатор, номер договора, дата подключения клиента, дата транзакции, сумма платежа) и дополнена вычисляемыми столбцами, где определяются тариф, актуальный на момент платежа, источник трафика и статический класс XYZ.
Таблица фактов (billing tarif change) содержит данные о смене тарифа клиентом. Она используется для определения тарифа, актуального на момент платежа в таблице billing facts.
Таблица фактов (B24 Полученные лиды) содержит данные об источнике, из которого был привлечен лид, таблица справочника (B24 Дополнительные параметры лидов) – данные о биллинговом идентификаторе, присвоенном лиду, ставшему клиентом. Эти таблицы используются для определения источника трафика в таблице billing facts.
Также в модели присутствуют справочники: Источники трафика (SHD Параметры трафика), Календарь (Date), Группировка тарифов (billing tarif) – они используются для сегментации и фильтрации данных в визуализациях.
Особняком в модели находится таблица, используемая для ABC-классификации, она не имеет связей с другими таблицами, служит для определения границ каждого класса и используется в визуализациях для сегментации и фильтрации данных.
ABC-классификация
Динамическая классификация реализуется в мере, которая создает виртуальную таблицу из уникальных значений биллинговых идентификаторов (проще говоря, клиентов), вычисляет совокупный доход по клиенту и его место в накопительном итоге по доходу от всех клиентов, начиная от самых крупных до самых мелких.
Самые крупные клиенты, на которых приходится до 70% дохода, относятся к классу A. Клиенты, приносящие следующие 20% дохода, находятся в классе B, а клиенты, приносящие последние 10% дохода, относятся к классу C.
На основании классификации вычисляется доход (Sales Customers) и число клиентов (Customers) разных классов:
Sales Customers = SUM ( 'billing facts'[income] )
Customers = DISTINCTCOUNT( 'billing facts'[id] )
Собственно, мера, которая вычисляет доход с ABC-классификацией:
Sales Amount ABC = CALCULATE ( [Sales Customers]; VALUES ( 'billing facts'[id] ); FILTER ( CALCULATETABLE ( ADDCOLUMNS ( ADDCOLUMNS ( VALUES ( 'billing facts'[id] ); "OuterValue"; [Sales Customers] ); "CumulatedSalesPercentage"; DIVIDE ( SUMX ( FILTER ( ADDCOLUMNS ( VALUES ( 'billing facts'[id] ); "InnerValue"; [Sales Customers] ); [InnerValue] >= [OuterValue] ); [InnerValue] ); CALCULATE ( [Sales Customers]; VALUES ( 'billing facts'[id] ) ) ) ); ALL ( 'billing facts' ) ); [CumulatedSalesPercentage] > MIN ( 'Classification'[LowerBoundary] ) && [CumulatedSalesPercentage] <= MAX ( 'Classification'[UpperBoundary] ) ) )
По тому же принципу вычисляется число клиентов в классах. В мере изменяется только результирующая часть. Если в первом случае это суммарный доход (Sales Customers), то здесь это число клиентов (Customers):
Customers ABC = CALCULATE ( [Customers]; VALUES ( 'billing facts'[id] ); FILTER ( CALCULATETABLE ( ADDCOLUMNS ( ADDCOLUMNS ( VALUES ( 'billing facts'[id] ); "OuterValue"; [Sales Customers] ); "CumulatedSalesPercentage"; DIVIDE ( SUMX ( FILTER ( ADDCOLUMNS ( VALUES ( 'billing facts'[id] ); "InnerValue"; [Sales Customers] ); [InnerValue] >= [OuterValue] ); [InnerValue] ); CALCULATE ( [Sales Customers]; VALUES ( 'billing facts'[id] ) ) ) ); ALL ( 'billing facts' ) ); [CumulatedSalesPercentage] > MIN ( 'Classification'[LowerBoundary] ) && [CumulatedSalesPercentage] <= MAX ( 'Classification'[UpperBoundary] ) ) )
Дополнительно можно вычислить долю каждого класса (% клиентов и % дохода):
% клиентов ABC = DIVIDE ( [Customers ABC]; CALCULATE ( [Customers ABC]; ALL ( Classification ) ) )
% от дохода ABC = DIVIDE ( [Sales Amount ABC]; CALCULATE ( [Sales Amount ABC]; ALL ( Classification ) ) )
Применительно к другим параметрам (тип трафика, тарифы и другие сегменты) доля вычисляется так:
% клиентов = DIVIDE ( [Customers ABC]; CALCULATE ( [Customers ABC]; ALLSELECTED ( 'billing facts' ) ) )
% от дохода = DIVIDE ( [Sales Amount ABC]; CALCULATE ( [Sales Amount ABC]; ALLSELECTED ( 'billing facts' ) ) )
Вычисляемые столбцы
Дополним данные биллинга информацией об источнике трафика. Для этого возьмем в таблице параметров (B24 Дополнительные параметры лидов) соответствующий биллинговому идентификатору лид и для него в таблице лидов (B24 Полученные лиды) найдем идентификатор источника трафика, который свяжем со справочником трафика (SHD Параметры трафика):
Идентификатор источника трафика = VAR currid = [id] VAR curlead = MINX ( FILTER ( 'B24 Дополнительные параметры лидов'; [Билл. ИД] == currid ); 'B24 Дополнительные параметры лидов'[Идентификатор лида] ) RETURN MINX ( FILTER ( 'B24 Полученные лиды'; [Идентификатор лида] == curidlead ); [Идентификатор источника трафика] )
Добавим актуальное на дату платежа значение тарифа:
Tarif = VAR paydata = [date] VAR contract = [contract] VAR changedate = CALCULATE ( MAX ( 'billing tarif change'[date_modified] ); FILTER ( 'billing tarif change'; [contract] == contract ); FILTER ( 'billing tarif change'; [date_modified] <= paydata ) ) RETURN CALCULATE ( MIN ( 'billing tarif change'[name] ); FILTER ( 'billing tarif change'; [contract] == contract ); FILTER ( 'billing tarif change'; [date_modified] == changedate ) )
Определим XYZ-класс клиента на основании регулярности его платежей:
Период между платежами = VAR pay_date = [date] VAR bid = [id] VAR previous_pay_date = CALCULATE ( MAX ( 'billing facts'[date] ); FILTER ( 'billing facts'; 'billing facts'[date] < pay_date ); FILTER ( 'billing facts'; 'billing facts'[id] == bid ) ) VAR period = DATEDIFF ( previous_pay_date; pay_date; MONTH ) RETURN IF ( period == BLANK (); 0; IF ( period == 0; 1; period ) ) XYZStatic = VAR bid = [id] VAR average_period = CALCULATE ( AVERAGE ( 'billing facts'[Период между платежами] ); FILTER ( 'billing facts'; 'billing facts'[id] == bid ) ) VAR STDEV_period = CALCULATE ( STDEV.S ( 'billing facts'[Период между платежами] ); FILTER ( 'billing facts'; 'billing facts'[id] == bid ) ) VAR VariationCoefficient = DIVIDE ( STDEV_period; average_period; 0 ) RETURN IF ( CALCULATE ( COUNTROWS ( 'billing facts' ); FILTER ( 'billing facts'; 'billing facts'[id] == bid ) ) < 3; "Z"; SWITCH ( TRUE (); AND ( VariationCoefficient <= 0,2; VariationCoefficient >= 0 ); "X"; AND ( VariationCoefficient <= 0,6; VariationCoefficient > 0,2 ); "Y"; "Z" ) )
Вывод
Полученную классификацию можно использовать для анализа не только рекламы. По сути, это дополнительный срез для анализа чего угодно – трафика, продуктов (тарифов), сотрудников отдела продаж, клиентов в совокупности с любыми другими классификациями (например, XYZ).
