245 lines
17 KiB
SQL
245 lines
17 KiB
SQL
-- admin_comms_funnel — воронка коммуникаций для страницы «Аналитика» (mega_admin/admin).
|
||
-- Один вызов отдаёт всё, что нужно блокам «Воронка коммуникаций» и «Путь заказа»:
|
||
-- stages — этапы цепочки по когорте групп за период (push → SMS → ссылка → звонки → согласование → ручная → выдача);
|
||
-- attempts — с какой попытки берут трубку и согласовывают;
|
||
-- channels — КТО подтвердил (робот / логист / ссылка / push): считается по событию, а не по текущему статусу;
|
||
-- lists — «не поднимают трубку» и «открыли ссылку, но не подтвердили» (по 10 записей, для работы менеджера);
|
||
-- silent — кого не достали ни ссылкой, ни звонком (отдельно: ещё в работе и уже закрытые).
|
||
-- Конвенция периода — как у admin_automation_economics: p_days=30 → группы, созданные за 30 дней;
|
||
-- p_days=0 → всё время; p_date_from/p_date_to — явное окно (перекрывает p_days).
|
||
--
|
||
-- ⚠️ Правила, без которых цифры врут (проверено на боевых данных 02.10.2026):
|
||
-- 1. Тестовые звонки (autocall_log.was_test_mode) в статистику НЕ идут: клиенту не звонили.
|
||
-- 2. «Открыли ссылку» нельзя считать по opened_at — его пишет только edge-функция
|
||
-- get-delivery-invitation, а страница клиента ходит RPC. Фактический вход в ссылку —
|
||
-- access_count > 0 / last_accessed_at, подтверждение — confirmed_at.
|
||
-- 3. «Согласовано» и канал согласования считаются по СОБЫТИЮ (delivery_date_source /
|
||
-- подтверждённое приглашение / успешный звонок робота), а не по текущему
|
||
-- notification_status: после выдачи заказ уходит в 'completed' и из старой
|
||
-- статистики «сгорал» задним числом.
|
||
-- 4. Канал согласования у обоих блоков («Чем подтвердили» и «Как согласовали дату»)
|
||
-- считается ОДНИМ классификатором (confirm_channels) — иначе блоки расходятся.
|
||
|
||
create or replace function public.admin_comms_funnel(
|
||
p_days integer default 30,
|
||
p_date_from timestamp with time zone default null,
|
||
p_date_to timestamp with time zone default null
|
||
) returns jsonb
|
||
language sql
|
||
stable
|
||
security definer
|
||
set search_path to 'public', 'extensions'
|
||
as $function$
|
||
with cohort as (
|
||
select og.*
|
||
from order_groups og
|
||
where og.created_at >= coalesce(
|
||
p_date_from,
|
||
case when p_days = 0 then '-infinity'::timestamptz
|
||
else now() - make_interval(days => p_days) end)
|
||
and og.created_at <= coalesce(p_date_to, now())
|
||
),
|
||
calls as (
|
||
-- только боевые звонки: тестовые набирают номер владельца и к клиенту отношения не имеют
|
||
select l.* from autocall_log l join cohort c on c.id = l.order_group_id
|
||
where not coalesce(l.was_test_mode, false)
|
||
),
|
||
inv as (
|
||
select i.* from delivery_invitations i join cohort c on c.id = i.order_group_id
|
||
),
|
||
-- ссылка реально открыта (клиент заходил на страницу выбора слота)
|
||
opened_inv as (
|
||
select i.* from inv i
|
||
where i.opened_at is not null
|
||
or coalesce(i.access_count, 0) > 0
|
||
or i.last_accessed_at is not null
|
||
or i.confirmed_at is not null
|
||
),
|
||
-- кто подтвердил: один классификатор для обоих блоков аналитики
|
||
confirm_channels as (
|
||
select c.id,
|
||
case
|
||
when c.delivery_date_source = 'autocall' then 'call'
|
||
when c.delivery_date_source = 'manual' then 'manual'
|
||
when c.delivery_date_source = 'push' then 'push'
|
||
when c.delivery_date_source in ('sms', 'client', 'messenger') then 'sms'
|
||
when exists (select 1 from inv i
|
||
where i.order_group_id = c.id and i.confirmed_at is not null) then 'link'
|
||
when c.manual_confirmation_at is not null then 'manual'
|
||
when exists (select 1 from calls l
|
||
where l.order_group_id = c.id
|
||
and l.outcome in ('confirmed', 'confirmed_silent', 'confirmed_kept_previous')) then 'call'
|
||
else null
|
||
end as channel,
|
||
(c.delivery_date is not null or c.pickup_date is not null) as has_date
|
||
from cohort c
|
||
),
|
||
-- не достали ни ссылкой, ни ответом на звонок
|
||
unreached as (
|
||
select c.id,
|
||
(c.delivery_status in ('delivered', 'picked_up', 'cancelled', 'paid_storage')) as closed
|
||
from cohort c
|
||
where c.notification_status not in ('confirmed', 'opt_out')
|
||
and not exists (select 1 from opened_inv i where i.order_group_id = c.id)
|
||
and not exists (select 1 from calls l
|
||
where l.order_group_id = c.id
|
||
and l.outcome in ('confirmed', 'no_input_day', 'no_input_half'))
|
||
),
|
||
stages as (
|
||
select
|
||
(select count(*) from cohort) as groups_cnt,
|
||
(select count(*) from cohort c where exists (select 1 from push_log p where p.order_group_id = c.id)) as push_cnt,
|
||
(select count(*) from cohort c where c.first_sms_sent_at is not null) as sms1_cnt,
|
||
(select count(*) from cohort c where c.second_sms_sent_at is not null) as sms2_cnt,
|
||
(select count(distinct i.order_group_id) from opened_inv i) as opened_cnt,
|
||
(select count(*) from cohort c where exists (select 1 from calls l where l.order_group_id = c.id)) as called_cnt,
|
||
(select count(*) from cohort c where exists (
|
||
select 1 from calls l where l.order_group_id = c.id
|
||
and l.outcome in ('confirmed', 'no_input_day', 'no_input_half'))) as answered_cnt,
|
||
-- согласовано = есть подтверждение по событию (сумма каналов), а не текущий статус
|
||
(select count(*) from confirm_channels cc where cc.channel is not null) as confirmed_cnt,
|
||
(select count(*) from cohort c where c.notification_status = 'manual_required') as manual_cnt,
|
||
(select count(*) from cohort c where c.delivery_status in ('delivered', 'picked_up')) as done_cnt,
|
||
(select count(*) from unreached where not closed) as silent_open_cnt,
|
||
(select count(*) from unreached where closed) as silent_closed_cnt,
|
||
(select count(*) from unreached) as silent_cnt
|
||
),
|
||
pct as (
|
||
select
|
||
case when groups_cnt > 0 then round(push_cnt::numeric / groups_cnt * 100, 1) else 0 end as push_pct,
|
||
case when groups_cnt > 0 then round(sms1_cnt::numeric / groups_cnt * 100, 1) else 0 end as sms1_pct,
|
||
case when groups_cnt > 0 then round(opened_cnt::numeric / groups_cnt * 100, 1) else 0 end as opened_pct,
|
||
case when groups_cnt > 0 then round(called_cnt::numeric / groups_cnt * 100, 1) else 0 end as called_pct,
|
||
case when groups_cnt > 0 then round(answered_cnt::numeric / groups_cnt * 100, 1) else 0 end as answered_pct,
|
||
case when groups_cnt > 0 then round(confirmed_cnt::numeric / groups_cnt * 100, 1) else 0 end as confirmed_pct,
|
||
case when groups_cnt > 0 then round(manual_cnt::numeric / groups_cnt * 100, 1) else 0 end as manual_pct,
|
||
case when groups_cnt > 0 then round(silent_open_cnt::numeric / groups_cnt * 100, 1) else 0 end as silent_pct
|
||
from stages
|
||
),
|
||
path as (
|
||
select
|
||
count(*) as created,
|
||
count(*) filter (where first_sms_sent_at is not null) as sms,
|
||
count(*) filter (where delivery_date is not null or pickup_date is not null) as dated,
|
||
count(*) filter (where (delivery_date is not null or pickup_date is not null)
|
||
and delivery_status in ('delivered', 'picked_up')) as chain_done,
|
||
count(*) filter (where delivery_status in ('delivered', 'picked_up')
|
||
and delivery_date is null and pickup_date is null) as bypass_done,
|
||
count(*) filter (where delivery_status = 'paid_storage') as paid_storage,
|
||
count(*) filter (where delivery_status = 'cancelled') as cancelled,
|
||
-- закрыли без согласования (в т.ч. выдача, закрытая из 1С): клиент дату не выбирал
|
||
count(*) filter (where notification_status = 'closed_without_confirmation') as no_confirmation,
|
||
count(*) filter (where delivery_type = 'pickup') as pickup_cnt,
|
||
count(*) filter (where delivery_type = 'delivery') as delivery_cnt
|
||
from cohort
|
||
),
|
||
attempt_rows as (
|
||
select attempt_no,
|
||
count(*) as total,
|
||
count(*) filter (where outcome in ('confirmed', 'confirmed_silent', 'confirmed_kept_previous',
|
||
'no_input_day', 'no_input_half')) as answered,
|
||
-- все три подтверждающих исхода, как в KPI (иначе «молчание» не видно)
|
||
count(*) filter (where outcome in ('confirmed', 'confirmed_silent', 'confirmed_kept_previous')) as confirmed,
|
||
count(*) filter (where outcome = 'no_answer') as no_answer
|
||
from calls
|
||
group by attempt_no
|
||
order by attempt_no
|
||
),
|
||
no_answer_rows as (
|
||
select c.customer_phone as phone, coalesce(nullif(c.town, ''), '—') as town,
|
||
c.delivery_type as kind, count(*) as attempts,
|
||
max(l.created_at) as last_call, c.notification_status as status
|
||
from calls l join cohort c on c.id = l.order_group_id
|
||
group by 1, 2, 3, c.id, c.notification_status
|
||
having count(*) filter (where l.outcome <> 'no_answer') = 0
|
||
order by attempts desc, max(l.created_at) desc
|
||
limit 10
|
||
),
|
||
opened_rows as (
|
||
select i.order_number as num, coalesce(nullif(i.customer_name, ''), '—') as name,
|
||
i.customer_phone as phone,
|
||
coalesce(i.opened_at, i.last_accessed_at) as opened,
|
||
greatest(coalesce(i.access_count, 0), case when i.opened_at is not null then 1 else 0 end) as opens
|
||
from inv i
|
||
where i.confirmed_at is null
|
||
and (i.opened_at is not null or coalesce(i.access_count, 0) > 0 or i.last_accessed_at is not null)
|
||
order by coalesce(i.last_accessed_at, i.opened_at) desc nulls last
|
||
limit 10
|
||
)
|
||
select jsonb_build_object(
|
||
'period', jsonb_build_object('days', p_days, 'from', p_date_from, 'to', p_date_to),
|
||
'stages', jsonb_build_array(
|
||
jsonb_build_object('key', 'groups', 'label', 'Групп в работе', 'value', s.groups_cnt, 'pct', 100),
|
||
jsonb_build_object('key', 'push', 'label', 'Push отправлен', 'value', s.push_cnt, 'pct', p.push_pct),
|
||
jsonb_build_object('key', 'sms1', 'label', 'Первое SMS', 'value', s.sms1_cnt, 'pct', p.sms1_pct),
|
||
jsonb_build_object('key', 'opened', 'label', 'Открыли ссылку', 'value', s.opened_cnt, 'pct', p.opened_pct),
|
||
jsonb_build_object('key', 'called', 'label', 'Робот позвонил', 'value', s.called_cnt, 'pct', p.called_pct),
|
||
jsonb_build_object('key', 'answered', 'label', 'Взяли трубку', 'value', s.answered_cnt, 'pct', p.answered_pct),
|
||
jsonb_build_object('key', 'confirmed', 'label', 'Согласовано', 'value', s.confirmed_cnt,'pct', p.confirmed_pct),
|
||
jsonb_build_object('key', 'manual', 'label', 'В ручную работу', 'value', s.manual_cnt, 'pct', p.manual_pct),
|
||
jsonb_build_object('key', 'done', 'label', 'Выдано/доставлено','value', s.done_cnt, 'pct', case when s.groups_cnt > 0 then round(s.done_cnt::numeric / s.groups_cnt * 100, 1) else 0 end)
|
||
),
|
||
'silent', jsonb_build_object(
|
||
'count', s.silent_open_cnt, -- ещё в работе, ответа не было
|
||
'pct', p.silent_pct,
|
||
'closed_count', s.silent_closed_cnt, -- уже закрыты, но клиент так и не ответил
|
||
'unreached_total', s.silent_cnt
|
||
),
|
||
'push_subs', (select count(*) from push_subscriptions),
|
||
'second_sms', jsonb_build_object('count', s.sms2_cnt,
|
||
'pct', case when s.groups_cnt > 0 then round(s.sms2_cnt::numeric / s.groups_cnt * 100, 1) else 0 end),
|
||
'attempts', coalesce((select jsonb_agg(jsonb_build_object(
|
||
'attempt', a.attempt_no, 'total', a.total, 'answered', a.answered,
|
||
'confirmed', a.confirmed, 'no_answer', a.no_answer,
|
||
'answered_pct', case when a.total > 0 then round(a.answered::numeric / a.total * 100, 1) else 0 end,
|
||
'confirmed_pct', case when a.total > 0 then round(a.confirmed::numeric / a.total * 100, 1) else 0 end
|
||
) order by a.attempt_no) from attempt_rows a), '[]'::jsonb),
|
||
-- Кто подтвердил за период (не зависит от текущего статуса заказа)
|
||
'channels', coalesce((select jsonb_agg(jsonb_build_object('source', ch.channel, 'count', ch.cnt)
|
||
order by ch.cnt desc)
|
||
from (select cc.channel, count(*) as cnt from confirm_channels cc
|
||
where cc.channel is not null group by 1) ch), '[]'::jsonb),
|
||
-- Сколько подтверждений всего (с датой и без) — знаменатель для каналов
|
||
'confirm_total', (select count(*) from confirm_channels cc where cc.channel is not null),
|
||
'confirm_no_date', (select count(*) from confirm_channels cc
|
||
where cc.channel is not null and not cc.has_date),
|
||
'no_answer_list', coalesce((select jsonb_agg(jsonb_build_object(
|
||
'phone', n.phone, 'town', n.town, 'kind', n.kind, 'attempts', n.attempts,
|
||
'last_call', n.last_call, 'status', n.status)) from no_answer_rows n), '[]'::jsonb),
|
||
'opened_no_confirm', coalesce((select jsonb_agg(jsonb_build_object(
|
||
'num', o.num, 'name', o.name, 'phone', o.phone,
|
||
'opened', o.opened, 'opens', o.opens)) from opened_rows o), '[]'::jsonb),
|
||
-- Путь заказа: воронка по цепочке (без даты водитель не едет) + то, что прошло мимо неё.
|
||
'order_path', jsonb_build_object(
|
||
'stages', jsonb_build_array(
|
||
jsonb_build_object('key', 'created', 'label', 'Групп в работе', 'value', pp.created, 'pct', 100),
|
||
jsonb_build_object('key', 'sms', 'label', 'Первое SMS', 'value', pp.sms,
|
||
'pct', case when pp.created > 0 then round(pp.sms::numeric / pp.created * 100, 1) else 0 end),
|
||
jsonb_build_object('key', 'dated', 'label', 'Дата согласована', 'value', pp.dated,
|
||
'pct', case when pp.created > 0 then round(pp.dated::numeric / pp.created * 100, 1) else 0 end),
|
||
jsonb_build_object('key', 'done', 'label', 'Выдано по цепочке', 'value', pp.chain_done,
|
||
'pct', case when pp.created > 0 then round(pp.chain_done::numeric / pp.created * 100, 1) else 0 end)
|
||
),
|
||
'bypass', jsonb_build_object('delivered', pp.bypass_done, 'paid_storage', pp.paid_storage,
|
||
'cancelled', pp.cancelled, 'no_confirmation', pp.no_confirmation),
|
||
'split', jsonb_build_object('pickup', pp.pickup_cnt, 'delivery', pp.delivery_cnt),
|
||
'sources', coalesce((select jsonb_agg(jsonb_build_object('source', ps.source, 'count', ps.cnt)
|
||
order by ps.cnt desc)
|
||
from (select coalesce(delivery_date_source, 'none') as source, count(*) as cnt
|
||
from cohort
|
||
where delivery_date is not null or pickup_date is not null
|
||
group by 1) ps), '[]'::jsonb),
|
||
-- Каким каналом согласовали дату: тот же классификатор, что и в блоке «Чем подтвердили»,
|
||
-- но только по заказам, где дата есть.
|
||
'channels', coalesce((select jsonb_agg(jsonb_build_object('channel', pc.channel, 'count', pc.cnt)
|
||
order by pc.cnt desc)
|
||
from (select cc.channel, count(*) as cnt from confirm_channels cc
|
||
where cc.channel is not null and cc.has_date group by 1) pc), '[]'::jsonb)
|
||
)
|
||
)
|
||
from stages s, pct p, path pp;
|
||
$function$;
|
||
|
||
comment on function public.admin_comms_funnel(integer, timestamp with time zone, timestamp with time zone) is
|
||
'Воронка коммуникаций для аналитики: этапы push → SMS → ссылка → звонки → согласование → ручная работа → выдача. Тестовые звонки исключены, канал согласования считается по событию.';
|