Быстрый расчет LTV, CAC, ROI, ROMI, CPL на данных CRM Bitrix24

Структура данных CRM Bitrix24

Основные сущности CRM – это Лиды (Leads), Сделки (Deals), Контакты (Contacts) и Компании (Companies). Bitrix24 хранит каждую сущность в отдельной таблице базы данных (например, b_crm_lead, b_crm_deal и т.д.), либо предоставляет через API соответствующие поля. Ниже перечислены ключевые поля этих таблиц на основе официальной документации:

• Лиды (b_crm_lead) – потенциальные клиенты, еще не ставшие покупателями. Основные поля лида включают:

  • ID – уникальный идентификатор лида.
  • TITLE – название лида (тема обращения).
  • NAME, LAST_NAME, SECOND_NAME – имя, фамилия, отчество контакта.
  • COMPANY_TITLE – название компании, указанное в лиде.
  • SOURCE_ID – источник, откуда пришёл лид (например, веб-сайт, реклама и т.д.). Справочник источников настраивается в CRM и содержит значения вроде “WEB”, “ADVERTISING”, “CALL” и т.п..
  • SOURCE_DESCRIPTION – дополнительное описание источника (например, уточнение кампании или площадки).
  • STATUS_ID – статус лида (например, новый, в работе, конвертирован, нерелевантный). Статусы хранятся в справочнике CRM.
  • STATUS_DESCRIPTION – описание статуса лида (заметка о текущей стадии).
  • CURRENCY_ID – валюта потенциальной сделки по лиду.
  • OPPORTUNITY – сумма потенциальной сделки (прогнозируемый доход по лиду).
  • ASSIGNED_BY_ID – ответственный менеджер (ID пользователя CRM).
  • DATE_CREATE – дата создания лида.
  • DATE_CLOSED – дата закрытия лида (когда лид конвертирован в сделку или отклонён).

• Сделки (b_crm_deal) – продажи/сделки, обычно появляющиеся после конвертации лида или создания вручную. В сделке фиксируется доход от клиента. Важные поля сделки:

  • ID – идентификатор сделки.

  • TITLE – название сделки (например, предмет сделки или автоматически сгенерированное название заказа).

  • OPPORTUNITY – сумма сделки (суммарная стоимость проданных товаров/услуг по сделке). Важно: это поле хранит именно общую сумму сделки, независимо от списка товаров – при наличии товарных позиций платформа обычно синхронизирует OPPORTUNITY с суммой позиций.

  • CURRENCY_ID – валюта сделки.

  • PROBABILITY – вероятность успешного закрытия сделки в процентах (может задаваться менеджером).

  • CATEGORY_ID – направление (воронка) сделки, если используется несколько воронок продаж.

  • STAGE_ID – стадия сделки в воронке (например, новая, в работе, выиграна, проиграна). Стадии хранятся в таблице статусов (b_crm_status) по ENTITY_ID = ‘DEAL_STAGE’, где есть финальные стадии с признаком успешная (STAGE_SEMANTIC_ID = 'S') или проваленная (STAGE_SEMANTIC_ID = 'F').

  • CLOSEDATE – дата фактического закрытия сделки (когда достигнута финальная стадия).

  • ASSIGNED_BY_ID – ответственный менеджер за сделку.

  • Связи с другими сущностями:

  • CONTACT_ID – связанный контакт (физическое лицо).

  • COMPANY_ID – связанная компания (юридическое лицо клиента).

  • LEAD_ID – связанный лид, из которого была сконвертирована данная сделка (если сделка создана через конвертацию лида). Это поле присутствует в таблице сделок и хранит ID лида-источника.

• Контакты (b_crm_contact) – сущность для хранения данных о клиенте-физическом лице. Основные поля:

  • ID – идентификатор контакта.
  • NAME, LAST_NAME, SECOND_NAME – имя, фамилия, отчество контакта.
  • BIRTHDATE – дата рождения (если известно).
  • Контактные данные: PHONE, EMAIL, WEB – основные телефон, email, сайт контакта (с возможностью множественных значений через связанные таблицы).
  • POST – должность клиента.
  • ASSIGNED_BY_ID – ответственный за этого контакта менеджер.
  • SOURCE_ID – источник, откуда узнали о клиенте (если контакт создан из лида, обычно копируется источник лида).
  • SOURCE_DESCRIPTION – описание источника контакта.
  • COMPANY_ID – привязанная компания (если контакт работает на какую-то компанию).
  • LEAD_ID – ID лида, из которого был получен данный контакт (при конвертации лида в контакт).
  • Примечание: Bitrix24 также хранит связь контакт–компания через отдельную таблицу b_crm_contact_company (т.к. один контакт может относиться к нескольким компаниям, например, подрядчик), но для упрощения можно использовать поле COMPANY_ID контакта, если ведется простое соответствие один к одному.

• Компании (b_crm_company) – сущность для клиентов-организаций. Ключевые поля:

  • ID – идентификатор компании.
  • TITLE – название компании.
  • COMPANY_TYPE – тип компании (клиент, партнер, поставщик и т.д., берется из справочника).
  • INDUSTRY – отрасль/сфера деятельности (справочник).
  • EMPLOYEES – численность сотрудников.
  • REVENUE – годовой оборот компании (с CURRENCY_ID для валюты).
  • Контакты компании: PHONE, EMAIL, WEB и др. (могут быть множественными, как и у контактов).
  • ASSIGNED_BY_ID – ответственный менеджер.
  • LEAD_ID – ID лида, из которого получена компания (при конвертации).

Отметим, что связи между сущностями устроены следующим образом: Лид может быть конвертирован в контакт, компанию и сделку. При этом в новых контакте/компании/сделке сохраняются ссылки на исходный лид (LEAD_ID в соответствующих таблицах). Сделка может быть связана либо с контактом, либо с компанией, или с обоими (например, сделка с компанией может также иметь контакт — конкретного представителя — в поле CONTACT_ID). Контакты и компании связываются через таблицу связки, позволяя множественную привязку.

Данные о продуктах и счетах: Bitrix24 также позволяет прикреплять товары к сделкам. Товары хранятся в отдельной таблице b_crm_product_row, связанной со сделкой по идентификатору сделки (поле OWNER_ID и OWNER_TYPE = 'D'). Сумма товаров обычно суммируется в поле сделки OPPORTUNITY, поэтому для большинства агрегатных финансовых метрик достаточно использовать сумму сделки. Кроме того, существуют сущности Счета (Invoices) и Предложения (Quotes), но для расчета маркетинговых метрик (LTV, CAC и др.) обычно достаточно сделок и лидов, поэтому мы их опустим.

Данные о маркетинговых затратах: Bitrix24 CRM позволяет фиксировать источник лидов, однако расходы на маркетинг (бюджеты рекламных кампаний) хранятся либо в модуле Сквозная аналитика, либо во внешних системах (рекламные кабинеты, файлы и т.д.). Для целей расчетов метрик привлечения нам понадобится интегрировать данные о расходах. В примерах ниже мы будем считать, что есть отдельная таблица (или представление) marketing_costs, где содержатся данные о затратах на маркетинг по каналам или кампаниям. Структура такой таблицы может быть, например: source (канал или кампания, соответствующий полям SOURCE_ID в CRM), period (период времени, например месяц или дата), cost (сумма затрат за период). Эти данные могут быть получены из рекламных систем (Google Ads, Яндекс.Директ и др.) и загружены в базу для совместного анализа с CRM.

Lifetime Value (LTV) – пожизненная ценность клиента

Для бизнеса: LTV показывает совокупный доход, который компания получает от одного клиента за все время сотрудничества с ним. Проще говоря, это «пожизненная ценность» клиента. Высокий LTV означает, что клиент приносит большую прибыль (например, делает повторные покупки, продлевает подписки или покупает дорогие продукты). Низкий LTV может свидетельствовать о том, что клиент быстро уходит или совершает единовременную небольшую покупку. Бизнес-мetricsы, такие как LTV, помогают понять, сколько в среднем стоит клиент и как много можно инвестировать в его привлечение. Если LTV растет, это очень хороший признак – вы получаете больше выручки от каждого клиента за более длительный период. Данный показатель также подсказывает верхнюю границу допустимых затрат на привлечение: например, если средний LTV клиента – 100 000 ₽, а стоимость привлечения (CAC) – 120 000 ₽, то такая модель убыточна; наоборот, LTV значительно выше CAC означает эффективную, окупаемую работу с клиентами.

Как рассчитывается LTV: существуют разные подходы к расчету LTV (например, на основе среднего чека, частоты покупок и средней продолжительности «жизни» клиента). В рамках анализа CRM-данных проще всего рассчитать фактический LTV как сумму всех сделок по каждому клиенту. Если клиент (контакт или компания) совершил несколько покупок (сделок) за весь период, их суммарная величина – и есть полученный от него доход. На исторических данных CRM это дает фактическую “пожизненную” ценность на текущий момент.

Пример: пусть у нас есть таблица сделок deals, в которой хранятся завершенные успешные сделки. Мы считаем LTV по контакту (для B2C) – суммируем сумму всех сделок каждого контакта, у которого были покупки. SQL-запрос для PostgreSQL может выглядеть так:

SELECT 
    d.CONTACT_ID AS customer_id,
    SUM(d.OPPORTUNITY) AS total_revenue,
    COUNT(d.ID) AS deals_count
FROM deals d
WHERE d.STAGE_ID = 'WON'            -- учитываем только успешно завершенные сделки
GROUP BY d.CONTACT_ID;

Этот запрос сгруппирует все сделки по клиентам и посчитает: total_revenue – совокупную выручку от клиента и deals_count – количество сделок с ним. Значение total_revenue для каждого customer_id и есть фактический LTV данного клиента на текущий момент. Для среднего LTV по всем клиентам можно взять среднее значение AVG(total_revenue) по этому результату.

Примечание: Если CRM ориентирована на B2B, имеет смысл считать LTV на уровень компании (COMPANY_ID вместо CONTACT_ID), либо даже комбинировать (учитывая что один контакт может представлять компанию). Принцип расчета – суммировать все сделки, связанные с данным клиентом (компанией). В приведенном запросе достаточно заменить CONTACT_ID на COMPANY_ID для расчета LTV по компании.

Интерпретация: понимание LTV важно для выявления наиболее ценных клиентов и обоснования маркетинговых затрат. Например, сегментируя клиентов по каналам привлечения, можно увидеть, где LTV выше – возможно, эти каналы приносят более “качественных” клиентов. Также LTV связан с повторными продажами и удержанием: если LTV низкий, стоит анализировать, на каком этапе теряется клиент (например, он не совершает повторных покупок – значит, упущена прибыль на стадии retention, и нужно усилить программы лояльности или дополнительные продажи).

Customer Acquisition Cost (CAC) – стоимость привлечения клиента

Для бизнеса: CAC – это стоимость привлечения одного нового клиента. Показатель рассчитывается как общая сумма затрат на маркетинг и продажи, поделенная на число привлеченных клиентов за период. Он отвечает на вопрос: «Во сколько нам обходится один покупатель?». Бизнес-ценность метрики в том, что она позволяет оценить эффективность маркетинга: сравнить CAC с LTV (сколько приносит клиент) и понять, окупаются ли вложения. Например, отношение LTV к CAC показывает эффективность: обычно бизнесу выгодно, когда LTV значительно превышает CAC (например, в 3 раза и более); если же CAC близок к LTV или превышает его, компания тратит на привлечение больше, чем зарабатывает от клиента – прибыль «сгорает» уже на этапе привлечения. Высокий CAC может указывать на проблемы на верхних этапах воронки (дорогая реклама, нецелевой трафик) или на низкую конверсию лидов в сделки – то есть деньги тратятся, а клиентов выходит мало.

Данные и расчет CAC: Чтобы посчитать CAC, нам нужны две вещи:

1. Количество новых клиентов за определенный период (например, месяц).
2. Затраты на их привлечение (маркетинговый бюджет за этот период, или именно затраты, привязанные к тем кампаниям, которые принесли этих клиентов).

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

Пример: посчитаем CAC по каналам (источникам лидов) за месяц. Предположим, в таблице marketing_costs хранится бюджет по каждому каналу за январь 2024 (period = ‘2024-01’). Мы определим новых клиентов через лиды и сделки: возьмем лиды января, которые перешли в успешные сделки, группируя по SOURCE_ID. SQL-запрос:

SELECT 
    l.SOURCE_ID AS source,
    (SELECT SUM(m.cost) 
       FROM marketing_costs m 
       WHERE m.source = l.SOURCE_ID AND m.period = '2024-01'
    )::numeric / NULLIF(COUNT(DISTINCT d.CONTACT_ID), 0) AS CAC
FROM leads l
JOIN deals d 
    ON d.LEAD_ID = l.ID AND d.STAGE_ID = 'WON'
WHERE l.DATE_CREATE BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY l.SOURCE_ID;

Этот запрос выполняет следующее:

• Соединяет таблицу лидов l с таблицей сделок d по связке LEAD_ID (т.е. находит сделки, полученные из каждого лида). Фильтр d.STAGE_ID = 'WON' оставляет только успешно завершенные сделки, то есть действительно превратившиеся в клиентов.
• Фильтрует лиды по дате создания в январе 2024.
• Группирует по коду источника SOURCE_ID.
• Для каждого источника рассчитывает стоимость привлечения: в подзапросе берется сумма затрат из marketing_costs для данного источника за январь, и делится на число уникальных клиентов COUNT(DISTINCT d.CONTACT_ID) из этого источника. NULLIF(...,0) использован для предотвращения деления на ноль (на случай, если по какому-то каналу не оказалось клиентов).

На выходе получим таблицу, где для каждого канала source указан CAC (стоимость привлечения одного клиента) за январь 2024. Например, можно увидеть, что канал “WEB” (органический веб-трафик) имеет CAC, скажем, 5 000 ₽, а канал “ADWORDS” – 15 000 ₽. Это означает, что привлечение клиента через контекстную рекламу обходится в три раза дороже, чем через веб-сайт, и это повод задуматься об оптимизации рекламы или о повышении конверсии этого канала.

Примечание: Если у вас маркетинг разбит по конкретным кампаниям, стоит использовать не обобщенный SOURCE_ID, а идентификатор кампании (например, UTM-метку). В Bitrix24 можно сохранять UTM-метки в полях лида или сделки (через Сквозную аналитику или веб-формы). Тогда связка с расходами будет точнее – таблица marketing_costs может хранить campaign_id (например, UTM_CAMPAIGN) и расходы, а в CRM лидах хранится соответствующая метка кампании. Запрос по вычислению CAC по кампаниям аналогичен вышеприведенному, только группировать и связывать надо по полю кампании.

Как автоматизировать расчет CAC: технические специалисты могут реализовать регулярный подсчет CAC, например, с помощью представлений или материальных таблиц. Например, создать SQL-представление “CAC_by_channel”, которое объединяет данные CRM и маркетинга, как в запросе выше, и рассчитывает CAC по каналам ежемесячно. Это представление можно обновлять по расписанию или в режиме реального времени подключить к BI-системе для построения дешборда. Ниже, в разделе визуализации, мы покажем, как эти данные выглядят на диаграммах и как их интерпретировать бизнесу.

С точки зрения бизнес-интерпретации, CAC напрямую влияет на окупаемость маркетинга. Если CAC становится слишком высоким, стоит разобраться, где «теряются» деньги:

• Возможно, слишком дорогие рекламные каналы (в этом случае CPL тоже будет высок, см. ниже),
• либо канал дает достаточно лидов, но малая их доля закрывается в сделки (тогда проблема в конверсии – например, отдел продаж не успевает обработать или лиды некачественные).

Высокий CAC при относительно низком LTV означает, что воронка теряет прибыль на самом первом этапе – привлечение и конверсия клиентов. Для бизнеса это сигнал либо уменьшать расходы на данный канал, либо улучшать работу с лидами (скорость и качество доведения до сделки).

Return on Investment (ROI) – возврат на инвестиции

Для бизнеса: ROI – коэффициент возврата инвестиций (не только маркетинговых). Классическая формула ROI: (Доход от инвестиций – Сумма инвестиций) / Сумма инвестиций × 100%. В контексте продаж и маркетинга ROI часто рассматривают как общий показатель окупаемости: сколько рублей прибыли приносит каждый вложенный рубль. Например, ROI = 200% означает, что получено в 3 раза больше денег, чем потрачено (100 руб. вложили, 200 руб. чистой прибыли получили + 100 руб. возврат вложений). Для маркетинговых кампаний нередко вместо термина ROI используют ROMI (Return on Marketing Investment), но принцип расчета схожий – разница в том, что ROMI учитывает только маркетинговые затраты, а ROI можно считать для любых инвестиций (например, включая себестоимость товаров, операционные расходы и т.д.).

Расчет ROI в CRM-контексте: Если мы хотим быстро оценить общую отдачу от маркетинга за период, можно посчитать ROI по маркетинговым расходам – фактически это и будет ROMI (см. следующий раздел). Однако, чтобы не путать термины, под ROI часто понимают более широкий показатель. Например, можно учесть помимо маркетинга еще и себестоимость продаж (CoGS). В наших данных нет себестоимости, поэтому мы покажем пример ROI маркетинговых вложений – он отражает окупаемость рекламного бюджета в выручке. В дальнейшем бизнес может скорректировать формулу под прибыль (выручка минус себестоимость).

Пример: Рассчитаем общий ROI всех маркетинговых вложений за январь 2024. Для этого возьмем общую сумму выручки от новых клиентов и расходы на маркетинг за этот месяц. Используем таблицы deals и marketing_costs. Предположим, marketing_costs содержит столбец cost с суммарным бюджетом за месяц (в разрезе каналов). Мы суммируем расходы по всем каналам и всю выручку от клиентов, привлеченных в этом месяце. Запрос:

SELECT 
    (SUM(d.OPPORTUNITY) - (SELECT SUM(cost) FROM marketing_costs WHERE period = '2024-01'))
    / NULLIF((SELECT SUM(cost) FROM marketing_costs WHERE period = '2024-01'), 0) 
    AS ROI_ratio
FROM deals d
WHERE d.STAGE_ID = 'WON'
  AND d.CLOSEDATE BETWEEN '2024-01-01' AND '2024-01-31';

Здесь:

• SUM(d.OPPORTUNITY) – общая сумма успешно закрытых сделок в январе 2024 (то есть выручка от всех новых продаж за этот месяц).
• Подзапрос (SELECT SUM(cost) FROM marketing_costs WHERE period = '2024-01') получает общий бюджет маркетинга за январь по всем каналам (предполагаем, что эта таблица содержит строки по каналам, поэтому суммируем).
• Разность дает чистый доход с маркетинговых инвестиций (приблизительно, без учета иных затрат), а деление на сумму инвестиций – собственно ROI в относительном выражении. Если умножить результат на 100, получится ROI в процентах.

Полученный ROI_ratio – это отношение (выручка – затраты) к затратам. Например, значение 1.5 (или 150% если в процентах) означает, что затраты окупились и принесли 150% отдачи (т.е. 50% чистой прибыли сверх вложенного). Значение менее 1 (менее 100%) означает, что вложения не окупились полностью (принесли убыток).

Примечание: В идеале в формуле ROI следует использовать прибыль, то есть выручку минус все переменные издержки (например, себестоимость товара, комиссию). Если такие данные доступны (например, себестоимость товаров в сделке или валовая прибыль), нужно подставлять их. В Bitrix24 можно хранить себестоимость как пользовательское поле товара или сделки. В нашем упрощенном примере мы использовали всю сумму сделки как доход, что подходит, если маржинальность примерно постоянна.

Интерпретация: ROI показывает общую эффективность инвестиций. В разрезе маркетинга, ROI < 100% (коэффициент < 1) сигнализирует, что маркетинговые вложения пока не окупились – т.е. воронка “теряет” деньги либо на этапе привлечения (слишком дорого, см. CAC/CPL), либо на этапе конверсии/продаж (недостаточно сделок на выходе), либо на этапе монетизации клиентов (низкий средний чек или LTV). ROI можно повышать, либо сокращая затраты, либо увеличивая выручку (конверсию, средний чек). Бизнес-руководителям ROI понятен как интегральный показатель – он напрямую связан с прибылью. Технически же данный расчет легко автоматизировать: например, можно интегрировать данные о маркетинговом бюджете и фактической выручке в один отчёт. В Bitrix24 (начиная с определенных тарифов) доступен модуль сквозной аналитики, который фактически считает ROI/ROMI по заданным каналам автоматически. Если же делать самостоятельно, связка SQL + BI позволит в режиме реального времени пересчитывать ROI по итогам продаж.

Return on Marketing Investment (ROMI)

Для бизнеса: ROMI – это ROI именно на маркетинговые инвестиции. Часто говорят “окупаемость рекламы”. Формула ROMI аналогична ROI, но берется только маркетинговый эффект: (Доход от маркетинга – Расходы на маркетинг) / Расходы на маркетинг × 100%. Проще: насколько прибыльна рекламная кампания. Например, ROMI = 300% означает, что каждые 100 ₽, вложенные в рекламу, принесли 300 ₽ чистой прибыли (или 400 ₽ выручки, если считать с возвратом затрат). Положительный ROMI (>0% или >100% если формулой как выше) значит, что маркетинг окупается; отрицательный – деньги потрачены в убыток. Важно отслеживать ROMI по каналам и кампаниям: это помогает понять, какие каналы эффективно генерируют прибыль, а какие нет. Также ROMI учитывается при распределении бюджета – естественно, больше инвестировать хочется в каналы с высоким ROMI.

Расчет ROMI в SQL: В принципе, ROMI можно считать тем же запросом, что и ROI выше, только разбивать по каналам или кампаниям. Давайте рассчитаме ROMI по каналам (источникам лидов) за тот же период – январь 2024. Мы возьмем суммарную выручку по сделкам каждого канала и разделим на расходы по этому каналу.

SELECT 
    d.SOURCE_ID AS source,
    SUM(d.OPPORTUNITY) AS revenue,
    m.cost AS marketing_cost,
    (SUM(d.OPPORTUNITY) - m.cost) * 100.0 / NULLIF(m.cost, 0) AS ROMI_percent
FROM deals d
JOIN marketing_costs m 
    ON m.source = d.SOURCE_ID AND m.period = '2024-01'
WHERE d.STAGE_ID = 'WON'
  AND d.CLOSEDATE BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY d.SOURCE_ID, m.cost;

В этом запросе:

• Мы напрямую используем SOURCE_ID сделки (важно: если сделки создавались из лидов, в Bitrix24 поле SOURCE_ID сделки, как правило, наследует источник лида, либо его можно заполнить вручную при создании сделки через API).
• Агрегируем SUM(d.OPPORTUNITY) – получаем общую выручку по успешно закрытым сделкам данного источника за месяц.
• Через JOIN добавляем соответствующие затраты на маркетинг из marketing_costs m (предполагается, что там одна строка на источник за месяц, столбец cost).
• Вычисляем ROMI_percent как процент: (выручка – затраты) / затраты * 100%.

Результат покажет, к примеру:

• источник WEB: revenue = 500 000 ₽, cost = 100 000 ₽, ROMI_percent = 400% (т.е. 4 к 1 окупаемость – очень эффективно),
• источник ADWORDS: revenue = 150 000 ₽, cost = 200 000 ₽, ROMI_percent = -25% (отрицательный – вложено больше, чем получено выручки, маркетинг не окупился).

На что обратить внимание: ROMI, как и ROI, желательно считать на прибыли, а не на выручке. В нашем примере, если известно, что маржинальность \~50%, можно умножить сумму сделок на 0.5 чтобы получить оценку прибыли, прежде чем считать процент. Кроме того, для корректного сравнения каналов следует убедиться, что выручка привязана именно к этим маркетинговым вложениям (например, сделки именно от клиентов, пришедших по данному каналу в данном периоде). Bitrix24 сквозная аналитика в интерфейсе как раз связывает расходы и продажи автоматизировано. При самостоятельном расчетe – нужно аккуратно фильтровать данные. В нашем запросе мы фильтруем сделки по дате закрытия в том же месяце, что и расходы. Это допущение, что цикл сделки краткий (в рамках месяца). В реальности, лид мог прийти в январе (расход из бюджета января), а сделка закрыться, например, в феврале. Строго говоря, стоило бы связывать по лидам: взять лиды источника с маркетинговыми расходами января и смотреть все сделки, которые из них получились (независимо от даты закрытия). Такой запрос сложнее, но при необходимости можно реализовать (через связь d.LEAD_ID = l.ID и фильтрацию l.DATE_CREATE по январю). Для макроанализа, однако, часто берут простое приближение как выше.

Интерпретация ROMI: для маркетолога и руководителя ROMI по каналам – один из самых наглядных показателей. Он сразу отвечает, какой канал приносит прибыль. Например, отрицательный ROMI по некоторому каналу – повод либо оптимизировать кампании, либо перераспределить бюджет. Канал с самым высоким ROMI – эталонный, туда можно вливать больше средств (до точки снижения эффективности). Кроме того, сравнение ROMI и конверсий по воронке даст понимание, почему у канала хороший или плохой ROMI. Может оказаться, что канал дорогой (высокий CPL/CAC), но зато дает клиентов с большим LTV – тогда ROMI может быть положительным. Или напротив, дешевый CPL, но клиенты одноразовые – ROMI страдает. В общем, ROMI агрегирует влияние всех метрик: и стоимости, и конверсии, и средней выручки.

С технической точки зрения, автоматизация ROMI схожа с CAC/ROI: либо настроить его подсчет в BI-системе с помощью формул (например, в том же Power BI можно создать вычисляемые меры: SUM(Revenue) / SUM(Cost) для каждого канала), либо в SQL-представлении. Так как ROMI часто нужен по множеству срезов (по каналам, по кампаниям, по месяцам и т.д.), удобно сразу подготовить таблицу фактов с полями: период, канал, затраты, выручка, а в BI уже вычислять проценты и коэффициенты.

Cost Per Lead (CPL) – стоимость лида

Для бизнеса: CPL – цена за лид. Это метрика, показывающая, сколько денег в среднем тратится, чтобы получить один лид (потенциального клиента). От CAC отличается тем, что учитывает всех привлеченных потенциальных клиентов, даже если они не конвертировались в покупателя. CPL важен для оценки эффективности рекламных каналов на самой вершине воронки. Высокий CPL означает, что даже привлечение контакта (еще до продажи) обходится дорого – возможно, узкая аудитория или конкуренция в рекламе. Низкий CPL – реклама генерирует много лидов за небольшие деньги, но важно смотреть и на качество этих лидов (в связке с конверсией в сделки и CAC).

Расчет CPL: Обычно считается как маркетинг-бюджет / число полученных лидов (за период, по каналу или кампании). Все нужные данные – в лидах и маркетинговых затратах. Нам не нужны сделки, только количество лидов и стоимость рекламы.

Пример: Посчитаем CPL по тому же набору каналов за январь 2024.

SELECT 
    l.SOURCE_ID AS source,
    (SUM(m.cost) / COUNT(*))::numeric(10,2) AS CPL
FROM leads l
JOIN marketing_costs m 
    ON m.source = l.SOURCE_ID AND m.period = '2024-01'
WHERE l.DATE_CREATE BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY l.SOURCE_ID, m.cost;

Объяснения к запросу:

• Берем все лиды января, группируем по SOURCE_ID (источнику).
• Через JOIN прикрепляем расходы m.cost данного канала за январь.
• COUNT(*) дает количество лидов по этому источнику.
• Делим стоимость на количество. Здесь для простоты предполагается, что в marketing_costs для каждого источника одна строка с общей суммой за месяц (поэтому можно напрямую SUM(m.cost) или просто m.cost после группировки, раз мы включили m.cost в GROUP BY). Если же там несколько строк (например, разбивка по кампаниям внутри источника), лучше сначала агрегировать их в подзапросе или суммировать.

Результат покажет, например:

• source WEB: CPL = 500 ₽ (т.е. 500 рублей за один лид с сайта),
• source ADWORDS: CPL = 2000 ₽ (за лид из контекстной рекламы).

Такие цифры означают, что лиды из контекста обходятся значительно дороже. Однако, как отмечалось, CPL не говорит ничего о том, стал ли лид клиентом. Возможно, при CPL 500 ₽ конверсия в продажу 5%, а при CPL 2000 ₽ конверсия 20% – тогда стоимость привлечения клиента (CAC) может оказаться близкой. Поэтому CPL надо всегда оценивать в связке с конверсией лидов в сделки. Тем не менее, CPL полезен для чисто маркетинговой оптимизации – например, отдел маркетинга оценивает в режиме реального времени эффективность разных каналов по генерации заинтересованных контактов, еще до получения выручки.

Автоматизация CPL: так же проста, как и других метрик – по сути, это агрегирование чисел. В BI-системах можно настроить карточки или диаграммы, показывающие CPL по каналам, а также динамику CPL месяц к месяцу. Технически CPL можно высчитывать сразу при получении данных из рекламных кабинетов: многие рекламные платформы (Facebook Ads, Google Ads) отдают Cost per Lead напрямую. Но в единой системе аналитики (например, связав все каналы и CRM) расчет CPL дает сопоставимый показатель по всем каналам.

Примеры визуализации метрик в BI-системах

После расчета метрик с помощью SQL или встроенных средств, следующий шаг – предоставить результаты бизнес-пользователям в наглядной форме. Рассмотрим, как LTV, CAC, ROI/ROMI, CPL и другие показатели можно визуализировать в популярных BI-инструментах:

• Microsoft Power BI: Один из самых распространенных инструментов бизнес-аналитики. В Power BI можно напрямую подключиться к базе данных (например, к PostgreSQL с выгруженными данными Bitrix24 или к облачному Bitrix24 через OData BI-connector) и импортировать таблицы лидов, сделок и маркетинговых затрат. Затем с помощью DAX-мер можно рассчитывать метрики, либо использовать заранее подготовленные представления SQL. Пример отображения:

  • Карточки KPI: Вывести средний LTV, текущий CAC, ROI и т.п. большими цифрами наверху дашборда, чтобы руководство сразу видело ключевые показатели.
  • Столбчатые диаграммы по каналам: сравнить CAC и ROMI по каналам привлечения. Например, ось X – канал, два столбца – CAC (руб. за клиента) и LTV (руб. от клиента). Такая визуализация покажет, окупается ли канал (столбец LTV должен быть выше CAC, иначе цветом отметить проблему).
  • Линейный график тренда: отображать изменение метрик во времени, например, месячный CAC и LTV или ROI по месяцам. Это важно для отслеживания динамики – растет ли эффективность маркетинга.
  • Воронка продаж: Power BI позволяет строить воронкообразные диаграммы. Можно построить воронку от лидов к сделкам: этапы – “Лиды”, “Квалифицированные лиды” (или “Сделки в работе”), “Успешные сделки”. На каждом этапе указать средние метрики, напр. CPL на уровне лидов, CAC на уровне успешных сделок. Это поможет бизнесу увидеть, на каком этапе теряется какое количество потенциальной выручки.

  Power BI дает интерактивность: бизнес-пользователь может фильтровать, например, по сегментам клиентов или по рекламным кампаниям, и в реальном времени получать обновленные значения метрик. В официальных рекомендациях по продажным дашбордам отмечается важность включения таких метрик, как коэффициент конверсии, CAC, churn rate, LTV в состав отчета, что говорит о высокой ценности их визуализации для принятия решений.

• Apache Superset: Это open-source BI-платформа, которая может быть использована техническими специалистами для разворачивания внутренних дашбордов. Подключив Superset к тому же хранилищу данных, можно построить аналогичные графики. Superset обладает множеством визуальных компонентов:

  • Можно создать дашборд маркетинговой эффективности, где, например, на pie-chart (круговой диаграмме) показать распределение маркетингового бюджета по каналам, а рядом bar-chart – ROMI по каналам. Это сразу демонстрирует, не тратится ли бюджет на неэффективные источники.
  • Superset хорошо подходит для табличных виджетов: можно вывести таблицу топ-10 клиентов по LTV, например, с указанием их источника привлечения и потраченного бюджета на их привлечение (если отслеживается до такого уровня). Для бизнеса это конкретные данные – понимать, какие клиенты самые ценные.
  • Еще пример – линейная диаграмма с областями (area chart), показывающая кумулятивный ROI: нарастающим итогом, сколько вы инвестировали и сколько заработали.

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

• Яндекс DataLens: отечественная BI-система от Яндекс. DataLens позволяет строить интерактивные дашборды и поддерживает подключение к различным источникам, включая PostgreSQL, ClickHouse, Yandex Cloud хранилища и др. В DataLens можно реализовать следующие визуализации:

  • Комбинированные графики: например, одновременно показать столбцы с количеством лидов и линию – стоимость за лид (CPL) по месяцам. Это наглядно покажет, не растет ли CPL при увеличении количества лидов.
  • Гео-распределение: если у лидов/сделок есть география (регионы), DataLens может нанести показатели на карту – например, размер круга = LTV в этом регионе, цвет – ROI. Бизнесу это поможет понять, в каких регионах привлечение клиентов наиболее выгодно.
  • Детализация по источникам: DataLens позволяет делать страницы дашборда с фильтрами. Например, выпадающий список “Источник” – и вся панель ниже пересчитывается для выбранного источника: показываются его LTV, CAC, ROI, динамика и т.п. Это удобно на встречах маркетинга – быстро переключаться между каналами и обсуждать, где просадка.

• Бизнес.Ру (Business.ru): это российская облачная платформа, предлагающая CRM, учет и аналитику для малого и среднего бизнеса. В ней есть встроенные отчеты и возможность строить простые дашборды. Если компания использует Бизнес.Ру, метрики можно визуализировать прямо в системе:

  • Например, отчет “Воронка продаж” – дополнить его расчетными полями: конверсия, стоимость лида, стоимость клиента. Многие CRM, включая Бизнес.Ру, позволяют добавлять пользовательские показатели в отчет.
  • Графики окупаемости рекламных кампаний: если данные о расходах загружены, можно построить диаграмму “доход vs расходы по кампаниям” – это, по сути, иллюстрация ROI/ROMI. Показать столбиком доход от клиентов с кампании и линией – затраты. Пересечение или разница даст наглядное представление, кампания убыточна или прибыльна.

  Особенностью российских BI/CRM-систем является интеграция с 1С и привычными инструментами. Например, можно выгружать рассчитанные показатели из Bitrix24 в 1С для управленческого учета. В Яндекс DataLens встроены коннекторы к популярным источникам, а Бизнес.Ру сама может являться источником данных (она предоставляет API и отчеты).

Пример визуализации: Представим итоговый дашборд для руководителя отдела маркетинга:

• Вверху – четыре больших индикатора: Средний LTV, CAC текущего месяца, ROMI текущего месяца, CPL текущего месяца. Они могут быть подсвечены зеленым или красным в зависимости от выполнения плана или изменений (например, ROMI выше 100% – зеленый, ниже – красный).
• Ниже – график “LTV vs CAC по каналам”: каналы на оси Х, значения в рублях на Y. Для каждого канала два столбца: средний LTV клиентов из этого канала и CAC этого канала. Если столбец LTV ниже столбца CAC – сразу видно, что по этому каналу клиент окупается не полностью (тратим больше, чем зарабатываем с него).
• Рядом – график “ROI/ROMI по каналам”: например, точечный или столбиковый процент ROI. Там же может быть указана конверсия лидов в продажи по каналам. Часто используют bubble chart: на оси X – ROMI%, на Y – конверсия, размер пузыря – количество лидов, цвет – канал. Такой график сразу показывает, какие каналы и эффективно конвертируют, и окупаются (в идеале – верхний правый угол графика).
• Внизу – таблица “Топ-10 клиентов по LTV”: список клиентов, их LTV, источник, и возможно, пометка, сколько стоило их привлечь (если такой matching возможен). Бизнес любит конкретику – например, увидеть, что клиент “ООО Ромашка” принес 1 млн ₽ LTV, пришел с канала “Выставка”, при этом на канал “Выставка” потрачено всего 100 тыс. ₽. Это положительный пример. А какой-нибудь “ИП Иванов” с LTV 50 тыс., пришел с AdWords, а AdWords-счет на него “съел” 70 тыс. ₽ – отрицательный пример.

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

• Маркетинг увидит эффективность каналов (CPL, ROMI).
• Продажи увидят качество лидов и узкие места конверсии (например, много лидов, но низкий LTV – может, продают разово и не работают на повторные продажи).
• Руководство увидит финансовую отдачу (ROI, LTV/CAC ratio). Метрика LTV\:CAC часто приводится как ключевой показатель устойчивости бизнеса. В отчетах ее можно вывести как коэффициент или в виде индикатора (например, 3:1 считается хорошим ориентиром для многих).

Важно, что Bitrix24 сама по себе предоставляет некоторые встроенные отчеты и сквозную аналитику, но гибкость BI-инструментов гораздо выше. В Bitrix24 Сквозная аналитика можно настроить источники расходов и конверсии вплоть до сделки, и система рассчитает ROI/ROMI автоматически. Однако для расширяемости (учесть, например, LTV за долгое время, сложные сегментации) часто данные выгружаются в внешние хранилища и анализируются там.

Значение метрик для бизнеса и рекомендации по улучшению

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

• LTV (Lifetime Value): показывает ценность удержания клиента. Низкий LTV говорит о том, что клиенты либо совершают мало покупок, либо быстро уходят. Здесь компания теряет прибыль на этапе повторных продаж и продления отношений. Решения: программы лояльности, доп. продажи, сервисное обслуживание – чтобы повысить LTV. Высокий LTV – хорошо, но всегда нужно сравнивать с CAC.
• CAC (Customer Acquisition Cost): отражает эффективность этапа привлечения и конвертации. Если CAC слишком высок, это значит, что либо стоимость маркетинга велика (дорогие клики, лиды), либо конверсия низкая (много денег ушло, а клиентов получилось мало). При высоком CAC воронка теряет прибыль сразу “на входе” – стоимость привлечения не отбивается. Решения: оптимизировать рекламные расходы (таргетинг, ставки) и улучшать работу с лидами (скорость обработки, скрипты продаж, квалификация лидов).
• CPL (Cost per Lead): косвенно указывает на эффективность рекламных каналов в генерации интереса. Высокий CPL – дорого получить сам контакт. Но даже нормальный CPL может скрывать проблему – допустим, лиды дешевые, но почти все “холодные” и не конвертируются (тогда CAC все равно взлетит). Поэтому бизнес должен следить за качеством лидов. Если CPL высок и качество при этом низкое – явный сигнал пересмотреть канал или объявление, где могут привлекаться нерелевантные люди.
• ROI: суммарный показатель окупаемости. Если общий ROI по маркетингу низкий или отрицательный, бизнес тратит больше, чем зарабатывает – стратегия убыточна. Нужно детально смотреть, где “утекают” деньги: на каком этапе. Часто оказывается, что, скажем, ROI негативный из-за одного-два неэффективных канала или из-за низкой конверсии продавцов (например, лидов много и они дорогие, а отдел продаж не успевает обрабатывать – клиенты «протухают» без внимания). ROI – язык, понятный финансистам, поэтому обоснование маркетингового бюджета обычно идет через прогноз ROI. Данные CRM позволяют подкрепить эти расчеты фактами.
• ROMI: приватный случай ROI для маркетинга. Помогает расставить приоритеты в каналах. Бизнес видит, какие рекламные вложения дают наибольший выхлоп. Если какой-то канал имеет ROMI 300%, а другой 50%, понятно куда стоит реинвестировать. Низкий ROMI при нормальном CAC может означать, что проблема в самом продукте или клиенте – например, клиентов много, а чек маленький (может, скидки слишком большие, или продаем им только один раз и не предлагаем подписки). Так ROMI связывает маркетинг с качеством монетизации.

Для технических специалистов задача – обеспечить, чтобы эти метрики считались оперативно и точно. Рекомендации по автоматизации:

• Интеграция данных: настроить регулярную выгрузку данных из Bitrix24. Если это облачный Bitrix24, можно использовать REST API (методы crm.deal.list, crm.lead.list и т.д.) или встроенный BI-коннектор. Для коробочной версии – прямое подключение к базе. Данные о расходах маркетинга брать из рекламных API или сводить вручную в таблицу.
• Хранилище и вычисления: собрать все необходимые данные в единой базе (например, PostgreSQL, ClickHouse или даже Excel/Google Sheets для начала). Написать SQL-запросы, как в примерах выше, проверить их корректность (важно удостовериться, что связи лид→сделка работают, что не дублируются данные при JOIN, что все расходы мапятся на источники).
• Автоматизация обновления: метрики должны пересчитываться либо в режиме реального времени (например, BI-дашборд обращается к представлениям в БД), либо с регулярным интервалом (например, ежедневный скрипт обновляет сводную таблицу метрик за предыдущий день/месяц). Инструменты: планировщик задач (cron/Windows Task Scheduler), ETL-платформы (например, Hevo, если нужно тянуть из облака в базу), или встроенные механизмы BI (в Power BI можно настроить обновление по расписанию).
• Валидация: желательно сверять цифры. Например, суммарный маркетинговый расход за месяц в отчете ROMI должен совпадать с известным бюджетом (если нет – возможно, где-то не все кампании учтены). Количество новых клиентов с данным CAC должно соответствовать количеству реально появившихся клиентов.
• Гибкость и детализация: обеспечить возможность “нырнуть” вглубь. Руководитель может спросить: а почему у нас CAC по каналу X вырос? Технически должны быть данные, чтобы разложить CAC = (бюджет / клиенты) на составляющие: бюджет известен, а клиентов стало меньше – значит, проблема с конверсией, и можно посмотреть, сколько лидов было и сколько дошло до сделок. Поэтому полезно хранить промежуточные цифры (кол-во лидов, конверсия% по каналам, и т.п., либо рассчитывать их на лету запросами).

В итоге, сочетание грамотной методологии расчета (LTV, CAC, ROI, ROMI, CPL – по единым правилам) и инструментов визуализации приводит к прозрачности воронки продаж и маркетинга. Бизнес сможет увидеть, где именно теряется прибыль: будь то дорогостоящий канал, низкая конверсия отдела продаж или отсутствие повторных продаж. А техническая команда – быстро предоставлять эти сведения и поддерживать их обновление без ручных вычислений. Такая сквозная аналитика на основе данных Bitrix24 CRM дает возможность принимать решения, основанные на данных: перераспределить бюджет, настроить рекламу, обучить отдел продаж или запустить программы удержания – и тут же видеть влияние на ключевые метрики в следующих периодах. Это и есть цель – быстро и достоверно считать метрики и сразу использовать их для улучшения бизнеса.

Источники:

1. Официальная документация Bitrix24 по структуре CRM (поля лидов, сделок, контактов и компаний)
2. Bitrix24 Developer’s Guide – описание таблиц CRM и связей между ними (Lead, Deal, Contact, Company)
3. Методические материалы Яндекс и Carrot Quest по расчету ROI/ROMI в маркетинге
4. Hevodata: Рекомендации по метрикам для дашборда продаж в Power BI (включает CAC, LTV и др.)
5. Growth & Profitability Insights: соотношение LTV и CAC для оценки эффективности бизнеса