Условие

Дано две таблицы:

cnt_delivered(dt, assembly_type, type_store_delivery, cnt_delivered) — заказы по типу сборки и типу магазина (внутренняя/внешняя × planned/on_demand/marketplace).
request_agg(dt, type_appeal, direction, theme, subtheme, assembly_type, type_store_delivery, total_bid, numerator_csi, total_rates) — обращения в поддержку с CSI.

CSI = numerator_csi / total_rates.

Что показать бизнесу и какие точки роста сервиса предложить?

Решение

Метрики

Contact rate = total_bid / cnt_delivered (доля заказов, по которым было обращение).
CSI = numerator_csi / total_rates.
Bad-rate темы = total_bid_theme / total_bid.

Подход

WITH base AS (
  SELECT r.dt, r.assembly_type, r.type_store_delivery,
         SUM(r.total_bid)       AS bids,
         SUM(r.numerator_csi)   AS csi_num,
         SUM(r.total_rates)     AS csi_den
  FROM request_agg r
  GROUP BY r.dt, r.assembly_type, r.type_store_delivery
),
joined AS (
  SELECT b.*,
         d.cnt_delivered,
         100.0 * b.bids / NULLIF(d.cnt_delivered, 0) AS contact_rate_pct,
         CAST(b.csi_num AS FLOAT) / NULLIF(b.csi_den, 0) AS csi
  FROM base b
  LEFT JOIN cnt_delivered d
    ON d.dt = b.dt
   AND d.assembly_type = b.assembly_type
   AND d.type_store_delivery = b.type_store_delivery
)
SELECT * FROM joined ORDER BY dt;

Драйверы CSI

CSI vs тема: theme = 'Качество продуктов' обычно с самым низким CSI.
CSI vs тип сборки: внешняя (силами ритейлера) обычно ниже внутренней.
CSI vs тип доставки: marketplace/on_demand отличаются от planned.
Опоздание (theme='Опоздание') сильно влияет на CSI.

Точки роста

Темы с высоким bids × low CSI — приоритет для product-улучшений (контроль качества, штраф за опоздания).
В неделю на 1k заказов: убрать topic «Где мой заказ?» = автоматизировать статусы (ETA-push), снизит contact rate.
CSI на marketplace ниже — пересмотр SLA с ритейлерами.

Питон-визуализация

import pandas as pd, seaborn as sns
df = (request_agg.merge(cnt_delivered, on=['dt','assembly_type','type_store_delivery'], how='left'))
df['csi']          = df.numerator_csi / df.total_rates
df['contact_rate'] = df.total_bid    / df.cnt_delivered
sns.barplot(data=df.groupby('theme').agg(csi='csi','total_bid':'sum').reset_index(),
            y='theme', x='csi')

Подводные камни

CSI с малым числом оценок — шумен. Фильтр total_rates >= 30 обязателен.
numerator_csi может быть отрицательным (см. данные -2, -1) — это нестандартная схема. Уточнить, что значит знак.
Темы вроде «Чаевые» — мало касаются CSI; не учитывать в общей цифре.
CR (contact rate) включает дубли — клиент мог трижды позвонить по одной проблеме. Дедуп по (client_id, order_id) если есть.
CSI «партнёрских» обращений (исход) ≠ CSI «клиентских» — это разные метрики; не смешивать.

Эталонный ответ

Считаем CSI и contact rate в разрезе assembly × delivery × theme. Низкий CSI обычно у marketplace и тем «Опоздание / Качество». Точки роста — автоматизация статусов и пересмотр SLA с ритейлерами.

Сбермаркет: углублённый CSI — драйверы оценки и точки роста