База Sina

ClickHouse Sina Gear — снимок схемы

Дата снимка: 2026-09-29, после утренней загрузки. Сервер: контейнер clickhouse-sina. Это описание живой базы, не черновик.

С 29.09 макро-стадия и флаги задаются в sql/007_dds.sql (stage_map, stage_flag, deal_stage). Витрины: sql/008_mart_fct_sales.sql, sql/009_smm_mart.sql, sql/010_mart_cohort.sql. Меры Superset: knowledge/bi-metrics-spec.md. Блоки SQL ниже по файлу — снимок 28.09; если они расходятся с этими файлами, верны файлы.

Другой модели: не выдумывай таблицы и поля. Имена, типы и числа ниже сняты с сервера. Персональных данных нет: ни имён, ни телефонов, ни почты, ни текста дел.

Слои

Старые тестовые таблицы sina_mart (deal_fact, product_sale, funnel_now, stage_role, b2c_deal, ld_account, ld_day, ld_post, ld_post_rubric) удалены.

Рядом с каждой витриной ClickHouse держит служебную таблицу с именем .inner_…. Её не открывать. Для запросов — имена из таблицы слоёв выше.

Как обновляется

Утро 5:00–9:00 по Москве. В 5:00 грузятся Битрикс, LiveDune, Директ (последние 14 дней) и визиты Метрики (три полных дня, без сегодня). Повтор в 6, 7 и 8 только для источника, который не загрузился. В 9:00 Telegram молчит, если все источники легли.

После загрузки Битрикса пересобираются продажи, сделка, путь и клиент (клиент — последним). После загрузки LiveDune пересобираются дневная витрина, посты, сторис, видео, аудитория и период:

SYSTEM REFRESH VIEW sina_mart.fct_sales;
SYSTEM WAIT VIEW sina_mart.fct_sales;
SYSTEM REFRESH VIEW sina_mart.fct_deal;
SYSTEM WAIT VIEW sina_mart.fct_deal;
SYSTEM REFRESH VIEW sina_mart.fct_stage_path;
SYSTEM WAIT VIEW sina_mart.fct_stage_path;
SYSTEM REFRESH VIEW sina_mart.fct_client;
SYSTEM WAIT VIEW sina_mart.fct_client;
SYSTEM REFRESH VIEW sina_mart.fct_smm_daily;
SYSTEM WAIT VIEW sina_mart.fct_smm_daily;
SYSTEM REFRESH VIEW sina_mart.fct_smm_posts;
SYSTEM WAIT VIEW sina_mart.fct_smm_posts;
SYSTEM REFRESH VIEW sina_mart.fct_smm_stories;
SYSTEM WAIT VIEW sina_mart.fct_smm_stories;
SYSTEM REFRESH VIEW sina_mart.fct_smm_videos;
SYSTEM WAIT VIEW sina_mart.fct_smm_videos;
SYSTEM REFRESH VIEW sina_mart.fct_smm_audience;
SYSTEM WAIT VIEW sina_mart.fct_smm_audience;
SYSTEM REFRESH VIEW sina_mart.fct_smm_period;
SYSTEM WAIT VIEW sina_mart.fct_smm_period;

Запасной проход сам по себе, часовой пояс сервера ClickHouse: продажи в 06:30, соцсети в 06:45.

fct_sales не дописывается по одной вставке. Иначе смена стадии сделки оставляла бы старую строку.

Объёмы на 2026-09-29, после утра

Свежесть сырья: последняя сделка и лид созданы 29.09 в 04:12, контакт менялся до 04:57. Дни LiveDune доходят до 29.09, посты — до 28.09. Директ загружен вручную 29.09: sina_raw.ya_direct_costs, дни с 15.06 по 29.09, 703 строки, расход 3 629 524 ₽. В утренний прогон Директ ещё не входит. VK и логов Метрики на сервере нет.

Версия замены ReplacingMergeTree(modified_at) есть у btx_deal, btx_lead, btx_contact, btx_company. У справочников Битрикса и у всех ld_* версия не задана: ReplacingMergeTree(). btx_activity и btx_stage_history — журналы, дубли не схлопываются, представления в sina_dds для них нет. У LiveDune в sina_dds есть ld_account, ld_history, ld_post, ld_story, ld_video, ld_audience, ld_analytics. История и посты в представлении уже без payload: пересылки, голоса, Reels, клики и просмотры видео разобраны в колонки. Сохранений в JSON нет.

fct_sales: 3 636 строк товара в 1 541 сделке. Сделка без товарных строк в эту витрину не попадает: таких сделок 2 690, они есть в fct_deal.

is_in_transit с 29.09 — вся группа «6. Логистика (В пути)», не только C1:40. На 29.09 в товарных строках 18 таких сделок и 452 363 ₽. Выкуп (is_paid) — 646 сделок и 17 348 633 ₽.

Деньги

• price, quantity, discount_sum, line_amount — поля строки товара. line_amount = price * quantity.
• amount — сумма всей сделки. Она повторяется на каждой строке товара этой сделки. Суммировать amount по товарам нельзя: сделка посчитается столько раз, сколько в ней строк.
• catalog_price — цена из каталога, не цена сделки.

SQL, которым собраны DDS и MART

Файлы в репозитории: sql/007_dds.sql, sql/008_mart_fct_sales.sql. Ниже тот же код.

DDS

CREATE DATABASE IF NOT EXISTS sina_dds;

CREATE OR REPLACE VIEW sina_dds.deal AS
SELECT *
FROM sina_raw.btx_deal
FINAL;

CREATE OR REPLACE VIEW sina_dds.deal_product AS
SELECT *
FROM sina_raw.btx_deal_product
FINAL;

CREATE OR REPLACE VIEW sina_dds.lead AS
SELECT *
FROM sina_raw.btx_lead
FINAL;

CREATE OR REPLACE VIEW sina_dds.stage AS
SELECT *
FROM sina_raw.btx_stage
FINAL;

CREATE OR REPLACE VIEW sina_dds.funnel AS
SELECT *
FROM sina_raw.btx_funnel
FINAL;

CREATE OR REPLACE VIEW sina_dds.product AS
SELECT *
FROM sina_raw.btx_product
FINAL;

Представлений для контактов, компаний, источников, дел и истории стадий нет. Эти таблицы лежат только в sina_raw.

Соцсети

Файл: sql/009_smm_mart.sql. Названия сети и аккаунта через ifNull(..., ''), иначе пустая связь с аккаунтом делает поле сортировки пустым и ClickHouse не принимает ORDER BY.

CREATE OR REPLACE VIEW sina_dds.ld_account AS
SELECT * FROM sina_raw.ld_account FINAL;

CREATE OR REPLACE VIEW sina_dds.ld_history AS
SELECT * FROM sina_raw.ld_history FINAL;

CREATE OR REPLACE VIEW sina_dds.ld_post AS
SELECT * FROM sina_raw.ld_post FINAL;

CREATE MATERIALIZED VIEW sina_mart.fct_smm_daily
REFRESH EVERY 1 DAY OFFSET 6 HOUR 45 MINUTE
ENGINE = MergeTree
ORDER BY (network, account_id, day)
SETTINGS index_granularity = 8192
AS
SELECT
    h.day AS day,
    h.account_id AS account_id,
    ifNull(a.project, '') AS project,
    ifNull(a.network, '') AS network,
    ifNull(a.name, '') AS account_name,
    ifNull(h.followers, 0) AS followers,
    ifNull(h.gained, 0) AS gained,
    ifNull(h.lost, 0) AS lost,
    ifNull(h.reach, 0) AS reach,
    ifNull(h.impressions, 0) AS impressions,
    ifNull(h.visitors, 0) AS visitors,
    ifNull(h.avg_likes, 0) AS avg_likes,
    ifNull(h.avg_comments, 0) AS avg_comments,
    ifNull(h.avg_reposts, 0) AS avg_reposts,
    ifNull(h.avg_views, 0) AS avg_views,
    ifNull(h.avg_reactions, 0) AS avg_reactions
FROM sina_dds.ld_history AS h
LEFT JOIN sina_dds.ld_account AS a ON a.account_id = h.account_id;

CREATE MATERIALIZED VIEW sina_mart.fct_smm_posts
REFRESH EVERY 1 DAY OFFSET 6 HOUR 45 MINUTE
ENGINE = MergeTree
ORDER BY (network, account_id, day, post_id)
SETTINGS index_granularity = 8192
AS
SELECT
    p.day AS day,
    ifNull(p.created, toDateTime('1970-01-01 00:00:00')) AS created,
    p.post_id AS post_id,
    p.account_id AS account_id,
    ifNull(a.project, '') AS project,
    ifNull(a.network, '') AS network,
    ifNull(a.name, '') AS account_name,
    p.url AS url,
    p.text AS text,
    p.post_type AS post_type,
    ifNull(p.views, 0) AS views,
    ifNull(p.likes, 0) AS likes,
    ifNull(p.comments, 0) AS comments,
    ifNull(p.reposts, 0) AS reposts,
    ifNull(p.reach, 0) AS reach
FROM sina_dds.ld_post AS p
LEFT JOIN sina_dds.ld_account AS a ON a.account_id = p.account_id;

MART

Почему не обычное материализованное представление без REFRESH: оно дописывает строки на каждую вставку в одну таблицу и не пересобирает строку, если у сделки сменилась стадия. REFRESH каждый раз заново выполняет SELECT и заменяет содержимое. Движок хранения — MergeTree.

Сортировка (category_id, created_date, deal_id, row_id): сначала воронка (их четыре), потом дата создания сделки, потом ключи строки. Дата в витрине называется created_date, не date.

DROP VIEW IF EXISTS sina_mart.b2c_deal;
DROP TABLE IF EXISTS sina_mart.deal_fact;
DROP TABLE IF EXISTS sina_mart.product_sale;
DROP TABLE IF EXISTS sina_mart.funnel_now;
DROP TABLE IF EXISTS sina_mart.stage_role;
DROP TABLE IF EXISTS sina_mart.ld_account;
DROP TABLE IF EXISTS sina_mart.ld_day;
DROP TABLE IF EXISTS sina_mart.ld_post;
DROP TABLE IF EXISTS sina_mart.ld_post_rubric;
DROP VIEW IF EXISTS sina_mart.fct_sales;

CREATE DATABASE IF NOT EXISTS sina_mart;

CREATE MATERIALIZED VIEW sina_mart.fct_sales
REFRESH EVERY 1 DAY OFFSET 6 HOUR 30 MINUTE
ENGINE = MergeTree
ORDER BY (category_id, created_date, deal_id, row_id)
SETTINGS index_granularity = 8192
AS
SELECT
    p.row_id AS row_id,
    p.deal_id AS deal_id,
    p.product_id AS product_id,
    ifNull(toDate(d.created_at), toDate('1970-01-01')) AS created_date,
    ifNull(d.created_at, toDateTime('1970-01-01 00:00:00')) AS created_at,
    ifNull(d.category_id, '') AS category_id,
    ifNull(f.name, '') AS funnel_name,
    ifNull(d.stage_id, '') AS stage_id,
    ifNull(s.name, '') AS stage_name,
    ifNull(d.stage_semantic, '') AS stage_semantic,
    ifNull(p.product_name, '') AS product_name,
    trimBoth(replaceRegexpOne(replaceRegexpOne(extract(ifNull(p.product_name, ''), '\\(([^)]+)\\)\\s*$'), '(?i)^Размер:\\s*', ''), '\\s*,.*$', '')) AS product_size,
    ifNull(cat.name, '') AS catalog_name,
    ifNull(cat.price, 0) AS catalog_price,
    ifNull(p.price, 0) AS price,
    ifNull(p.quantity, 0) AS quantity,
    ifNull(p.discount_sum, 0) AS discount_sum,
    ifNull(p.price, 0) * ifNull(p.quantity, 0) AS line_amount,
    ifNull(d.amount, 0) AS amount,
    ifNull(d.currency, '') AS currency,
    ifNull(d.contact_id, 0) AS contact_id,
    ifNull(d.company_id, 0) AS company_id,
    ifNull(d.lead_id, 0) AS lead_id,
    ifNull(d.source_id, '') AS source_id,
    ifNull(d.assigned_by_id, 0) AS assigned_by_id,
    lowerUTF8(ifNull(d.utm_source, '')) AS utm_source,
    lowerUTF8(ifNull(d.utm_medium, '')) AS utm_medium,
    lowerUTF8(ifNull(d.utm_campaign, '')) AS utm_campaign,
    lowerUTF8(ifNull(d.utm_content, '')) AS utm_content,
    lowerUTF8(ifNull(d.utm_term, '')) AS utm_term,
    d.begin_date AS begin_date,
    d.close_date AS close_date,
    ifNull(d.closed, 0) AS closed,
    ifNull(d.yandex_client_id, '') AS yandex_client_id,
    ifNull(d.calltouch_first_order, 0) AS calltouch_first_order,
    CASE WHEN ifNull(d.source_id, '') IN ('UC_9PG86R', 'UC_LURKNL')
        OR lowerUTF8(ifNull(d.utm_source, '')) LIKE '%vk%'
        OR lowerUTF8(ifNull(d.utm_source, '')) LIKE '%vkontakte%'
        OR lowerUTF8(ifNull(d.utm_source, '')) LIKE '%вконтак%' THEN 1 ELSE 0 END AS is_vk,
    CASE WHEN ifNull(d.stage_id, '') = 'C1:42' THEN 1 ELSE 0 END AS is_preorder,
    CASE WHEN ifNull(d.stage_id, '') = 'C1:40' THEN 1 ELSE 0 END AS is_in_transit,
    CASE WHEN ifNull(d.stage_id, '') IN ('C1:37', 'C1:30') THEN 1 ELSE 0 END AS is_refusal,
    CASE WHEN ifNull(d.stage_id, '') IN ('C1:37', 'C1:30') THEN 1 ELSE 0 END AS is_exchange_return,
    CASE WHEN ifNull(d.stage_id, '') = 'C1:WON' OR ifNull(s.name, '') = 'Подтвержден' THEN 1 ELSE 0 END AS is_confirmed,
    CASE WHEN ifNull(d.stage_id, '') = 'C1:32' THEN 1 ELSE 0 END AS is_paid_full,
    CASE WHEN ifNull(d.stage_id, '') = 'C1:38' THEN 1 ELSE 0 END AS is_paid_partial,
    ifNull(d.calltouch_first_order, 0) AS is_new_client
FROM sina_dds.deal_product AS p
LEFT JOIN sina_dds.deal AS d ON d.deal_id = p.deal_id
LEFT JOIN sina_dds.funnel AS f ON f.category_id = d.category_id
LEFT JOIN sina_dds.stage AS s ON s.stage_id = d.stage_id
LEFT JOIN sina_dds.product AS cat ON cat.product_id = p.product_id;

Пустая дата создания в витрине заменяется на 1970-01-01. begin_date и close_date остаются пустыми, если их нет в сделке. product_id = 0 — строка без карточки товара в каталоге.

Колонки

sina_mart.fct_sales

stage_semantic: S успех, F провал, P в работе. Это пометка Битрикса, не оплата и не выкуп.

stage_group сводит названия стадий в 10 макро-этапов: от «1. Старт и Квалификация» до «10. Отказы и Провал». Незнакомое имя попадает в «Нераспределенное».

Флаги на 28.09.2026, по справочнику стадий и источников:

• is_vk — источник UC_9PG86R («VK - сообщение в группе») или UC_LURKNL («VK - форма»), либо utm_source содержит vk, vkontakte или вконтак. Новый код источника без этих слов сам не попадёт.
• is_preorder — C1:42 или имя «Предзаказ». is_in_transit — группа «6. Логистика (В пути)». is_paid — полный или частичный выкуп.
• is_refusal и is_exchange_return — C1:37 «Отказ при вручении» или C1:30 «Возврат товара».
• is_confirmed — C1:WON «Подтвержден». Успех B2B «Оплачено» и Озон «Доставлено» сюда не входят.
• is_paid_full — только C1:32 «Доставлен полностью». is_paid_partial — только C1:38 «Клиент забрал часть заказа».
• is_new_client — галочка Calltouch calltouch_first_order. Ноль значит «галочки нет», а не доказанный повторный клиент.
• product_size — текст в последних круглых скобках названия строки. Префикс «Размер:» и хвост после запятой срезаются. Если скобок нет, поле пустое.
• UTM приведены через lowerUTF8. Обычный lower() кириллицу не опускает.

Строка витрины — товар в сделке. Сделка без товарных строк в fct_sales не попадает: из 1 320 сделок с источником VK в витрине 49.

sina_raw.btx_deal

deal_id UInt64, contact_id UInt64, company_id UInt64, lead_id UInt64, category_id String, stage_id String, stage_semantic LowCardinality(String), assigned_by_id UInt64, source_id String, amount Float64, currency LowCardinality(String), created_at DateTime, modified_at DateTime, begin_date Nullable(Date), close_date Nullable(Date), closed UInt8, utm_source String, utm_medium String, utm_campaign String, utm_content String, utm_term String, yandex_client_id String, calltouch_first_order UInt8.

Представление sina_dds.deal повторяет эти колонки.

sina_raw.btx_deal_product

row_id UInt64, deal_id UInt64, product_id UInt64, product_name String, price Float64, quantity Float64, discount_sum Float64.

Представление sina_dds.deal_product повторяет эти колонки.

sina_raw.btx_lead

lead_id UInt64, contact_id UInt64, company_id UInt64, status_id String, status_semantic LowCardinality(String), source_id String, amount Float64, currency LowCardinality(String), created_at DateTime, modified_at DateTime, closed_at Nullable(DateTime), has_phone UInt8, has_email UInt8, utm_source String, utm_medium String, utm_campaign String, utm_content String, utm_term String.

Представление sina_dds.lead повторяет эти колонки.

Справочники DDS

• sina_dds.funnel: category_id String, name String.
• sina_dds.stage: stage_id String, category_id String, name String, semantics LowCardinality(String). Значения semantics: process, success, failure, apology.
• sina_dds.product: product_id UInt64, name String, price Float64, currency LowCardinality(String), active UInt8.

Сырьё без представления DDS

sina_raw.btx_contact: contact_id UInt64, created_at DateTime, modified_at DateTime, has_phone UInt8, has_email UInt8, source_id String, utm_source String, utm_medium String, utm_campaign String, utm_content String, utm_term String, yandex_client_id String.

sina_raw.btx_company: company_id UInt64, title String, created_at DateTime, modified_at DateTime.

sina_raw.btx_source: source_id String, name String.

sina_raw.btx_lead_status: status_id String, name String, semantics LowCardinality(String).

sina_raw.btx_activity: activity_id UInt64, type_id UInt16 (1 встреча, 2 звонок, 3 задача, 4 письмо), direction UInt8 (1 входящее, 2 исходящее), owner_type_id UInt16 (1 лид, 2 сделка, 3 контакт, 4 компания), owner_id UInt64, completed UInt8, created_at DateTime.

sina_raw.btx_stage_history: event_id UInt64, entity_type LowCardinality(String) (deal или lead), owner_id UInt64, category_id String, stage_id String, stage_semantic LowCardinality(String), created_at DateTime.

LiveDune, только sina_raw

Идентификаторы аккаунта, поста, сторис и видео — String, не число.

• ld_account: account_id, project, network, type, name, url, is_linked UInt8, followers_now Float64, payload String.
• ld_history: account_id, day Date, followers, gained, lost, posts, post_views, reach, reach_known UInt8, impressions, visitors, stories, story_views, avg_likes, avg_comments, avg_reposts, avg_views, avg_reactions, avg_forwards, payload. Метрики — Float64.
• ld_post: account_id, post_id, day Date, created DateTime, url, text, post_type, views, likes, comments, reposts, reach, reach_known UInt8, payload.
• ld_story: account_id, story_id, day Date, created DateTime, story_type, views, reach, payload.
• ld_video: account_id, video_id, day Date, created DateTime, url, text, views, payload.
• ld_audience: account_id, day Date, payload String.
• ld_analytics: account_id, date_from Date, date_to Date, followers, followers_diff, posts, views, views_avg, likes, comments, reposts, er, payload.

Пустой метрики в LiveDune нет: вместо пустоты пишется 0. Полный ответ API лежит в payload.