Как построить единый отчёт из данных 1С, CRM и рекламных кабинетов

Единый отчёт из 1С, CRM и рекламных кабинетов нельзя надёжно получить простым склеиванием трёх выгрузок. В рекламной системе основной объект — кампания и расход, в CRM — лид или сделка, в 1С — заказ, реализация, платёж и возврат. У источников различаются даты, статусы, идентификаторы и правила исправления записей. Пока эти различия не описаны, красивый дашборд будет показывать числа, которым нельзя объяснить происхождение.

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

Сформулируйте решение, которое должен поддержать отчёт

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

Одновременно задайте зерно — чему равна одна строка будущей таблицы. Например, «одна сделка CRM», «одна строка реализации 1С» или «кампания × день». Нельзя без промежуточной модели соединить расходы по дням со строками товаров: расход размножится на количество продаж, и сумма станет завышенной.

Распределите роли источников

Рекламные кабинеты

Обычно они являются источником расходов, показов, кликов и идентификаторов кампаний, объявлений и ключевых условий показа. Получайте отчёты через поддерживаемый API и сохраняйте исходный ответ. Учитывайте валюту, НДС, часовой пояс кабинета, корректировки и период, за который данные ещё могут измениться.

CRM

CRM описывает контакт с клиентом: лид, сделку, источник, ответственного, этап и историю переходов. Текущий статус недостаточен для анализа воронки: если система перезаписывает его, отчёт не узнает, когда сделка находилась на каждом этапе. Нужны события или периодические снимки истории.

1С

Учётная система подтверждает хозяйственный результат: заказ, реализацию, оплату, себестоимость, скидку, отмену и возврат — в соответствии с конкретной конфигурацией и правилами компании. Не называйте любую сделку CRM продажей, если управленческое определение требует отгрузки или оплаты. Для интеграции платформа 1С предоставляет механизмы обмена, включая REST-интерфейс; конкретный способ выбирают с учётом конфигурации, нагрузки и политики доступа.

Создайте устойчивые ключи связки

Связь должна передаваться по цепочке, а не угадываться задним числом. При входе сохраните рекламные метки и доступные идентификаторы клика, создайте идентификатор обращения, перенесите его в лид и сделку CRM, затем — в заказ или связующую таблицу для 1С. Дополнительно храните внутренние ID кампании, объявления, сделки, заказа и документа реализации.

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

Если одна сделка связана с несколькими заказами или один заказ объединяет несколько сделок, создайте мост — таблицу соответствий с типом связи, долей распределения, датой действия и источником решения. Иначе соединение «многие ко многим» размножит суммы.

Разделите загрузку, нормализацию и витрину

1. Raw-слой. Неизменённые ответы API и выгрузки с временем получения, источником и идентификатором запуска.
2. Staging. Типизированные даты и суммы, единые справочники статусов, валют и часовых поясов, удаление технических дублей.
3. Витрина. Факты рекламы, лидов, продаж и платежей, соединённые с измерениями даты, кампании, клиента, товара, подразделения и менеджера.
4. BI-слой. Проверенные показатели, фильтры и детализация до исходной записи.

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

Спроектируйте модель данных

Типовой набор включает fact_ad_cost на уровне «кабинет × кампания × день», fact_lead и fact_deal с историей стадий, fact_sale по документу или строке реализации, fact_payment и fact_return. Измерения содержат календарь, рекламную иерархию, канал, товар, клиента, менеджера и организацию. Точные таблицы зависят от вопросов отчёта; не нужно помещать всё в одну широкую таблицу.

Для изменяемых справочников определите, что должен показывать прошлый период: текущее название менеджера и кампании либо значение на дату события. Если нужна историческая правда, храните версии атрибутов с периодом действия. Для удалённых сущностей сохраняйте технический признак, чтобы итог прошлого месяца не исчез после очистки CRM.

Зафиксируйте словарь показателей

Для каждой метрики укажите формулу, зерно, источники, дату отнесения, включаемые статусы, обработку НДС, валюты, скидок, отмен и возвратов. Примеры:

• CPL = рекламный расход / число лидов по согласованному определению;
• CAC = выбранные затраты на привлечение / число новых клиентов, а не любых заказов;
• ROAS = атрибутированная выручка / рекламный расход;
• валовая прибыль = выручка минус себестоимость по утверждённому правилу учёта;
• конверсия в продажу = число исходных обращений, дошедших до критерия продажи / число исходных обращений.

Отдельно опишите атрибуцию. Первый, последний или последний непрямой источник отвечают на разные вопросы. Если метки потеряны, заведите категорию «источник не определён», а не распределяйте такие продажи автоматически между известными кампаниями. Долю несвязанных записей показывайте рядом с итогом: она характеризует надёжность вывода.

Порядок внедрения

1. Согласуйте вопросы, метрики, зерно и владельцев.
2. Проведите профилирование API и выгрузок на ограниченном периоде.
3. Утвердите схему идентификаторов и исправьте их передачу в новых обращениях.
4. Загрузите raw-данные с журналом запусков и контрольными суммами.
5. Нормализуйте даты, статусы, деньги и справочники.
6. Постройте факты, измерения и явные мосты связки.
7. Выполните сверку и только затем подключите BI.
8. Назначьте мониторинг свежести, полноты и ошибок загрузки.

Как проверить цифры до публикации

• сумма расхода за три выбранных дня совпадает с кабинетом с учётом валюты и НДС;
• число лидов и сделок совпадает с CRM при одинаковых фильтрах и часовом поясе;
• выручка, возвраты и себестоимость сверены с 1С по конкретным документам;
• первичные ключи уникальны, а обязательные внешние ключи заполнены;
• соединения не размножают расходы и продажи;
• цепочка «клик → лид → сделка → заказ → реализация» раскрывается до исходных ID;
• поздний платёж и возврат корректно меняют прошлый период;
• повторная загрузка не создаёт дублей;
• на дашборде видны время обновления, период данных и доля несвязанных записей.

Приёмку проводит не только разработчик. Владелец каждого источника подтверждает контрольные итоги, а бизнес-владелец — смысл метрик. Расхождение не маскируют округлением: для него фиксируют источник, величину, причину и решение. Заказать проектирование можно как витрину данных для управленческих отчётов из 1С, CRM и рекламы.

Как организовать дашборд

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

Источники

1. 1С:Предприятие — REST-интерфейс.
2. 1С:Предприятие — интеграция с другими системами.
3. API Яндекс Директа: спецификация.
4. API Яндекс Директа: пример формирования отчёта.
5. Документация Yandex DataLens.
6. DataLens: подключение к Битрикс24.
7. DataLens: кеширование данных.