Однажды в крупной логистической компании в Астане случилась неприятность: аналитик подготовил отчёт для совета директоров, где показал рост среднего чека на 15%. Директор по продажам посмотрел в свою CRM — там рост был 8%. Финансовый директор открыл 1С — там вообще 12%. Три системы, три правды, и никто не понимает, какая из них настоящая.
«У нас же данные синхронизируются каждую ночь!» — возмутился IT-директор. Да, синхронизируются. Но никто не задумывался о том, *как* они синхронизируются. Какие ключи используются для связи записей. Как хранится история изменений. Что происходит, когда статус сделки меняется три раза за день.
Это типичная история компании, которая «интегрировала системы», но не построила правильную архитектуру данных. И таких историй в Казахстане — сотни. Сегодня мы разберём, как делать правильно. Поговорим про ETL и ELT, про batch и streaming, про загадочные SCD-типы и про то, почему выбор правильного ключа — это не техническая мелочь, а стратегическое решение.
«Данные — это не нефть. Нефть ценна сама по себе. Данные ценны только тогда, когда они правильно организованы, связаны и доступны для анализа. Иначе это просто цифровой мусор, который занимает место на серверах.»
Принцип Data Engineering
адаптировано для бизнеса
Зачем вообще нужен ETL/ELT для CRM?
Начнём с базы. В любой компании данные живут в разных местах: CRM хранит информацию о клиентах и сделках, 1С — бухгалтерию и склад, маркетинговые системы — данные о рекламных кампаниях, колл-центр — записи звонков. Каждая система хороша в своей области, но для принятия бизнес-решений нужна полная картина.
Вот тут и появляется задача интеграции данных. Нам нужно:
- Собрать данные из разных источников в одно место
- Преобразовать их в единый формат (что такое «клиент» в CRM и в 1С — разные вещи)
- Загрузить в хранилище данных или аналитическую систему
- Сохранить историю изменений (что было вчера, месяц назад, год назад)
ETL (Extract-Transform-Load) и ELT (Extract-Load-Transform) — это два подхода к решению этой задачи. Звучит как техническая мелочь, но выбор между ними влияет на всё: скорость получения данных, стоимость инфраструктуры, возможности аналитики.
ETL: Extract → Transform → Load
Классический подход: сначала преобразуем, потом загружаем
- Данные «чистые» уже при загрузке
- Меньше нагрузка на хранилище
- Подходит для on-premise решений
- Сложнее менять логику преобразований
ELT: Extract → Load → Transform
Современный подход: сначала загружаем «сырые» данные, потом преобразуем
- Быстрая загрузка, гибкие преобразования
- Можно пересчитать историю с новой логикой
- Идеально для облачных хранилищ
- Требует больше места для хранения
На практике в Казахстане мы чаще видим гибридный подход. Сырые данные из CRM загружаются в промежуточный слой (staging), там проходят базовую очистку, а затем трансформируются в готовые витрины для аналитики. Это позволяет и сохранить гибкость, и не тратить ресурсы на хранение совсем уж «грязных» данных.
Подробнее об архитектуре хранилища данных вокруг CRM читайте в статье BI вокруг CRM: DWH, ETL/ELT, витрины данных, Power BI и DataLens.
Batch vs Streaming: когда какой подход выбрать
Допустим, у вас есть CRM и вам нужно передавать данные о сделках в аналитическую систему. Вопрос: как часто это делать?
Batch-обработка — это когда данные собираются порциями и обрабатываются по расписанию. Например, каждую ночь в 3:00 запускается процесс, который выгружает все изменения за день, преобразует их и загружает в хранилище. Утром аналитики видят актуальные данные (точнее, актуальные на вчерашний вечер).
Streaming (потоковая обработка) — это когда данные передаются в момент их возникновения. Менеджер закрыл сделку — через секунду это видно в аналитической системе. Клиент оставил заявку — мгновенно срабатывает триггер для маркетинга.
Выбор между Batch и Streaming
| Критерий | Batch | Streaming |
|---|---|---|
| Задержка данных | Часы (обычно ночной процесс) | Секунды-минуты |
| Сложность реализации | Проще, проверенные инструменты | Сложнее, требует специализированной инфраструктуры |
| Стоимость | Ниже (ресурсы используются периодически) | Выше (постоянная работа) |
| Обработка ошибок | Проще — можно перезапустить весь batch | Сложнее — нужно учитывать порядок событий |
| Типичные сценарии | Ежедневные отчёты, исторический анализ, финансовая отчётность | Real-time дашборды, триггерные уведомления, fraud detection |
Расскажу реальный случай из практики. Онлайн-магазин электроники в Алматы использовал batch-синхронизацию данных о заказах. Каждую ночь данные из интернет-магазина загружались в CRM. Проблема? Если клиент оформлял заказ утром, менеджер видел его только на следующий день. К этому моменту клиент уже мог уйти к конкуренту.
Перешли на streaming для критичных данных: новые заказы, изменения статусов, запросы на возврат. Остальное (история покупок, аналитические метрики) осталось на batch — нет смысла тратить ресурсы на real-time обновление данных, которые нужны раз в неделю.
Золотое правило: streaming там, где нужна скорость реакции; batch там, где важнее полнота и надёжность.
Batch: когда использовать
- Ежемесячная финансовая отчётность
- Загрузка справочников (товары, цены)
- Исторический анализ продаж
- Синхронизация с 1С (бухгалтерия)
- RFM-сегментация клиентов
Streaming: когда использовать
- Новые заявки и лиды
- Изменения статусов сделок
- Real-time дашборды для руководства
- Триггеры для маркетинговых автоматизаций
- Алерты при аномалиях (fraud, SLA)
Технически для streaming используют CDC (Change Data Capture) — механизм, который отслеживает изменения в базе данных источника и передаёт их в целевую систему. Популярные инструменты: Debezium для PostgreSQL/MySQL, Airbyte с поддержкой CDC, встроенные механизмы в облачных платформах.
О событийной архитектуре и том, какие события собирать из разных систем, мы подробно рассказывали в статье Event model для продаж и поддержки: какие события собирать с сайта, продукта, чатов, звонков.
Slowly Changing Dimensions: как хранить историю изменений
А теперь к самому интересному. Допустим, вы синхронизировали данные о клиентах из CRM в хранилище. Клиент Ержан Сагынбаев был в сегменте «Малый бизнес» с лимитом кредита 500 000 тенге. Через месяц его перевели в «Средний бизнес» с лимитом 2 000 000 тенге.
Вопрос: когда вы смотрите отчёт за прошлый месяц — Ержан должен быть в каком сегменте? В «Малом бизнесе» (как было тогда) или в «Среднем бизнесе» (как есть сейчас)?
Ответ зависит от того, как вы организовали хранение истории изменений. В мире данных это называется Slowly Changing Dimensions (SCD) — медленно изменяющиеся измерения. «Медленно» — потому что изменения происходят не каждую секунду, а раз в день, неделю, месяц.
Типы SCD: как хранить историю
SCD Type 1
Перезаписываем
История не хранится. Новое значение заменяет старое. Просто, но теряем информацию о прошлом.
SCD Type 2
Версионируем
Создаём новую строку с датами начала/конца действия. Полная история, но растёт объём данных.
SCD Type 3
Добавляем колонку
Храним текущее и предыдущее значение в разных колонках. Компромисс, но ограниченная история.
Давайте на примере. Возьмём того же Ержана и посмотрим, как его запись будет выглядеть в разных типах SCD.
Пример: клиент меняет сегмент
SCD Type 1 — просто перезаписываем:
| client_id | name | segment | credit_limit |
|---|---|---|---|
| 1001 | Ержан Сагынбаев | Средний бизнес | 2 000 000 |
Проблема: мы не знаем, что раньше он был в «Малом бизнесе». Исторические отчёты будут показывать его как «Средний бизнес» даже за тот период, когда он таким не был.
SCD Type 2 — создаём версии:
| surrogate_key | client_id | name | segment | credit_limit | valid_from | valid_to | is_current |
|---|---|---|---|---|---|---|---|
| 10001 | 1001 | Ержан Сагынбаев | Малый бизнес | 500 000 | 2024-01-15 | 2024-11-30 | false |
| 10002 | 1001 | Ержан Сагынбаев | Средний бизнес | 2 000 000 | 2024-12-01 | 9999-12-31 | true |
Теперь можем построить отчёт за октябрь — и увидим Ержана в «Малом бизнесе». Отчёт за декабрь — в «Среднем бизнесе». Полная история сохранена.
SCD Type 3 — добавляем колонку для предыдущего значения:
| client_id | name | segment | previous_segment | segment_change_date |
|---|---|---|---|---|
| 1001 | Ержан Сагынбаев | Средний бизнес | Малый бизнес | 2024-12-01 |
Компромисс: знаем предыдущий сегмент, но если было три смены — увидим только последнюю.
На практике для CRM-данных в Казахстане мы чаще всего рекомендуем SCD Type 2 для:
- Статусов сделок (важно видеть, сколько времени сделка была на каждом этапе)
- Сегментов клиентов (для корректной исторической аналитики)
- Ответственных менеджеров (кто реально вёл клиента в конкретный период)
- Ценовых условий и тарифов
SCD Type 1 подходит для:
- Исправления ошибок (неправильно введённое имя)
- Технических полей (email, телефон — если история не важна)
- Справочников, где изменение = ошибка (коды валют, единицы измерения)
История статусов сделок: особый случай
Отдельно поговорим про историю статусов — это, пожалуй, самый частый запрос от бизнеса. Руководителю продаж важно знать не только «сколько сделок закрыто», но и:
- Сколько времени сделка провела на каждом этапе?
- На каком этапе чаще всего «застревают»?
- Какие сделки «откатывались» на предыдущий этап?
- Как менялась вероятность закрытия по мере продвижения?
Для этого недостаточно просто хранить текущий статус. Нужна полная история переходов.
Структура таблицы истории статусов
| deal_id | status_from | status_to | changed_at | changed_by | time_in_status_hours |
|---|---|---|---|---|---|
| D-2024-1234 | NULL | Новая | 2024-12-01 09:15 | system | — |
| D-2024-1234 | Новая | Квалификация | 2024-12-01 11:30 | Айгуль М. | 2.25 |
| D-2024-1234 | Квалификация | КП отправлено | 2024-12-02 14:00 | Айгуль М. | 26.5 |
| D-2024-1234 | КП отправлено | Квалификация | 2024-12-03 10:00 | Айгуль М. | 20.0 |
| D-2024-1234 | Квалификация | КП отправлено | 2024-12-04 16:30 | Айгуль М. | 30.5 |
| D-2024-1234 | КП отправлено | Успешно закрыта | 2024-12-10 11:00 | Айгуль М. | 138.5 |
Обратите внимание: сделка «откатилась» с «КП отправлено» на «Квалификация» (выделено оранжевым) — клиент попросил уточнить требования. Это важная информация для анализа воронки.
Теперь можем ответить на вопросы бизнеса:
- Среднее время на этапе «Квалификация» — считаем AVG(time_in_status_hours) WHERE status_to = 'Квалификация'
- Процент «откатов» — считаем сделки, где status_from > status_to (в терминах порядка этапов)
- Воронка во времени — можем построить, как менялось количество сделок на каждом этапе день за днём
Если ваша CRM не хранит историю изменений статусов нативно (а многие не хранят!), нужно реализовать это на уровне ETL/ELT-процесса. При каждой синхронизации сравниваем текущий статус с предыдущим и записываем переход в отдельную таблицу.
Нужна помощь с интеграцией данных?
Спроектируем архитектуру ETL/ELT для вашей CRM: выберем правильные ключи, настроим историю изменений, построим витрины для аналитики. Расскажем, что можно сделать быстро, а что требует более серьёзных изменений.
Обсудить интеграциюПравильные ключи: основа надёжной интеграции
А теперь о том, о чём часто забывают — о ключах. Как связать запись о клиенте в CRM с записью того же клиента в 1С? Как понять, что сделка №12345 в CRM — это тот же заказ №ORD-2024-12345 в интернет-магазине?
Выбор ключей — это стратегическое решение. Ошибка здесь приводит к дубликатам, потерянным связям и тем самым «трём правдам» из начала статьи.
Типы ключей в интеграции данных
Natural Key (естественный ключ)
Реальный бизнес-идентификатор: ИИН, БИН, номер договора, email
Surrogate Key (суррогатный ключ)
Технический идентификатор, генерируемый системой: автоинкремент, UUID
Composite Key (составной ключ)
Комбинация нескольких полей: источник + ID в источнике, дата + номер
Master Key (мастер-ключ)
Единый идентификатор из системы MDM или CRM как «источник правды»
На практике лучше всего работает комбинация подходов. Вот как мы рекомендуем организовывать ключи для типичной интеграции CRM:
Для клиентов (контактов)
Natural key: БИН для юрлиц, ИИН для физлиц, email как резервный вариант. Но осторожно: email меняется чаще, чем кажется. ИИН в Казахстане стабилен, но могут быть ошибки ввода.
Рекомендация: используйте БИН/ИИН как основной идентификатор, но храните также surrogate key из CRM (client_id) для связи с внутренними данными. Создайте mapping-таблицу: client_id (CRM) ↔ БИН/ИИН ↔ code_1c (1С).
Для сделок
Composite key: источник (CRM, сайт, колл-центр) + ID в источнике. Это важно, когда лиды приходят из разных систем и могут иметь пересекающуюся нумерацию.
Пример: deal_source = 'CRM' + deal_source_id = '12345' → единый deal_key = 'CRM_12345'.
Для товаров/услуг
Natural key: SKU или артикул. Обычно это уже унифицированный идентификатор, который одинаков в CRM, 1С и интернет-магазине. Если нет — первым делом унифицируйте справочники!
Пример: mapping-таблица клиентов
| master_client_id | crm_client_id | 1c_code | bin_iin | created_at | last_verified | |
|---|---|---|---|---|---|---|
| MC-00001 | CRM-1234 | КОН-000123 | 123456789012 | erjan@company.kz | 2023-05-15 | 2024-12-01 |
| MC-00002 | CRM-1235 | КОН-000124 | 987654321098 | aizhan@firm.kz | 2023-06-20 | 2024-11-15 |
| MC-00003 | CRM-1236 | NULL | 456789012345 | new@client.kz | 2024-12-10 | — |
Третья строка (оранжевая) — новый клиент в CRM, который ещё не создан в 1С. Mapping-таблица позволяет отслеживать такие расхождения.
О том, как организовать единый профиль клиента и решить проблему дубликатов, читайте в статье CRM vs CDP vs DWH: архитектура Customer 360 и кто является SSOT. А про matching rules и дедупликацию — в статье MDM в CRM: golden record, matching rules, дедупликация.
Типичные ошибки в ETL/ELT для CRM
За годы работы с казахстанскими компаниями мы насмотрелись на разные «творческие» решения в области интеграции данных. Вот топ ошибок, которых стоит избегать.
Ошибка 1: Полная перезагрузка каждую ночь
Вместо инкрементальной загрузки (только изменения) — полное удаление и загрузка всех данных. Работает, пока данных мало. С ростом базы время загрузки растёт экспоненциально, нагрузка на источник огромная.
Ошибка 2: Использование auto-increment как бизнес-ключа
ID = 12345 в CRM и ID = 12345 в 1С — это разные записи! Auto-increment уникален только в рамках одной таблицы одной системы. Для связи между системами нужен бизнес-ключ.
Ошибка 3: Игнорирование часовых поясов
В Казахстане нет перехода на летнее время, но есть разница с Москвой. Если CRM хранит время в UTC, а 1С — в локальном, получите расхождения в отчётах. Всегда фиксируйте часовой пояс!
Ошибка 4: Нет механизма обработки ошибок
Запись не загрузилась? Процесс молча продолжает работу. В итоге 5% данных теряется, и никто не знает об этом до очередного аудита.
Ошибка 5: Отсутствие аудита изменений
Кто, когда и почему изменил данные? Без audit trail невозможно расследовать проблемы и доказать корректность данных регулятору или аудитору.
Ошибка 6: Жёсткая связь с форматом источника
Источник добавил поле — процесс сломался. Поле переименовали — процесс сломался. Используйте абстракции и контракты данных для изоляции от изменений в источниках.
Про контракты данных и как договориться о схемах между системами — отдельная большая тема. Мы разбирали её в статье Data contracts для интеграций: как договориться о схемах и не ломать отчёты релизами.
Практические рекомендации: с чего начать
Если вы только начинаете строить интеграцию данных CRM, вот пошаговый план.
План построения ETL/ELT для CRM
Определите «источник правды» для каждой сущности
Клиенты — CRM или 1С? Товары — 1С или интернет-магазин? Сделки — CRM. Зафиксируйте это в документации и следуйте правилу: правки только в источнике правды, остальные системы — приёмники.
Выберите ключи для связи между системами
БИН/ИИН для клиентов, SKU для товаров. Создайте mapping-таблицу, где хранятся соответствия ID из разных систем. Это ваш «словарь перевода».
Решите, какая история нужна
Для каких атрибутов важна история изменений (SCD Type 2)? Обычно это: статусы сделок, сегменты клиентов, ответственные менеджеры, цены. Для остального достаточно Type 1.
Выберите batch или streaming для каждого потока
Новые лиды, изменения статусов — streaming (если нужна реакция в реальном времени). Исторические данные, справочники, финансы — batch (раз в день достаточно).
Настройте мониторинг и алерты
Процесс загрузки упал — нужен алерт. Количество записей резко изменилось — нужен алерт. Появились дубликаты — нужен алерт. Лучше знать о проблеме сразу, чем обнаружить через месяц.
Документируйте и тестируйте
Каждый процесс должен быть задокументирован: что делает, откуда берёт данные, куда кладёт, как часто запускается. Автотесты на качество данных — must have.
Инструменты для ETL/ELT в Казахстане
Несколько слов об инструментах. Выбор зависит от масштаба, бюджета и технической зрелости команды.
Для небольших команд
- Airbyte — open-source, много готовых коннекторов, включая 1С и Bitrix
- n8n — визуальный конструктор, хорош для простых интеграций
- dbt — для трансформаций, отлично работает с PostgreSQL и BigQuery
Для enterprise
- Apache Airflow — оркестрация, scheduling, мониторинг
- Kafka + Debezium — для streaming и CDC
- Spark — для больших объёмов и сложных трансформаций
Облачные решения
- Google Cloud Dataflow — полностью управляемый ETL
- Azure Data Factory — хорошая интеграция с Microsoft-стеком
- AWS Glue — serverless ETL от Amazon
Хранилища данных
- PostgreSQL — для начала, бесплатно, знакомо
- ClickHouse — для аналитики, очень быстрый
- BigQuery/Snowflake — облачные DWH, масштабируемые
Для большинства казахстанских компаний среднего размера хорошо работает связка: Airbyte для извлечения данных → PostgreSQL/ClickHouse для хранения → dbt для трансформаций → Metabase/DataLens для визуализации. Это достаточно просто в поддержке и не требует команды из 10 data engineers.
Заключение: данные — это инфраструктура
Вернёмся к истории из начала статьи. Три системы показывали три разных числа роста среднего чека. После аудита выяснилось:
- CRM считала средний чек только по закрытым сделкам, без учёта возвратов
- 1С учитывала все счета, включая авансы и частичные оплаты
- Аналитическая система брала данные из CRM, но с задержкой в 3 дня
Проблема была не в цифрах — проблема была в отсутствии единой архитектуры данных. После того как компания:
- Определила CRM как источник правды для сделок
- Настроила SCD Type 2 для истории статусов
- Перешла на streaming для критичных данных
- Создала единую витрину с согласованными метриками
— все три системы стали показывать одинаковые цифры. Не потому что подогнали, а потому что данные стали правильными.
ETL/ELT для CRM — это не про технологии. Это про то, чтобы бизнес мог доверять своим данным. А доверие — это основа для принятия решений.
Готовы навести порядок в данных?
Проведём аудит вашей текущей архитектуры данных, определим «источники правды», спроектируем ETL/ELT-процессы и настроим мониторинг качества. Первая консультация — бесплатно.
Обсудить проектЧасто задаваемые вопросы
Читайте также
CRM vs CDP vs DWH: архитектура Customer 360
Как выбрать «источник правды» для данных о клиентах
Data contracts для интеграций
Как договориться о схемах и не ломать отчёты релизами
Event model для продаж и поддержки
Какие события собирать с сайта, продукта, чатов, звонков
MDM в CRM: golden record и дедупликация
Matching rules и жизненный цикл контактов
BI вокруг CRM: DWH, ETL, витрины данных
Архитектура аналитического слоя для CRM
Data quality в CRM: DQ score
Как измерять и повышать качество данных