Отчёт по агентским сделкам

Месторасположение: Отчёты → Заявки → Отчёт по агентским сделкам

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

#Работа с отчётом

#Фильтры

По умолчанию отчёт открывается за текущий месяц по дате поступления заявки. При необходимости скорректируйте период: выберите быстрый диапазон (квартал или год) или задайте произвольные даты.

Тип даты — событие, по которому заявка попадает в выбранный период. Доступны четыре варианта:

• Дата поступления заявки — дата создания заявки. Значение по умолчанию;
• Дата начала сделки — дата начала сделки по показу;
• Дата проведения сделки — дата проведения сделки;
• Дата перевода в бронь/сделку — у каждого столбца собственное событие периода. Подробности — в разделе «Тип даты "Дата перевода в бронь/сделку"».

Агрегатор — фильтрация по агрегатору заявок. Позволяет ограничить отчёт только теми заявками, которые поступили от конкретного агрегатора. Если значение не выбрано, учитываются все заявки.

Аккредитация агентств — по умолчанию в отчёте отображаются только аккредитованные агентства. Чтобы добавить неаккредитованные, измените значение фильтра.

Группировка — способ группировки строк таблицы. По умолчанию установлена группировка по агентству недвижимости.

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

Чтобы сбросить все фильтры до значений по умолчанию, нажмите Очистить.

#Структура отчёта

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

Расчёт столбцов в таблице ниже описан для типов даты «Дата поступления заявки», «Дата начала сделки» и «Дата проведения сделки»: они отбирают заявки по одной дате, а столбцы считают текущие статусы. Расчёт для типа даты «Дата перевода в бронь/сделку» приведён в следующем разделе.

Числа в строках, выделенные синим, кликабельны — нажмите, чтобы перейти к списку конкретных заявок.

Чтобы выгрузить данные в Excel, нажмите Экспорт.

#Тип даты «Дата перевода в бронь/сделку»

Выберите этот тип даты, чтобы увидеть, сколько заявок перешло на нужный этап именно за выбранный период. Остальные типы даты отбирают заявки по одной дате и показывают их текущий статус, поэтому заявка, поступившая в октябре и забронированная в ноябре, в ноябрьский отчёт не попадёт.

При этом типе даты отчёт считает не текущие статусы, а события, произошедшие в выбранном периоде. У каждого столбца собственное событие:

Заявка попадает в столбец не более одного раза, сколько бы раз она ни проходила этот переход за период.

Ссылка из столбца открывает список заявок с тем же событием периода: в фильтрах листинга устанавливаются Тип даты — «Дата перевода в бронь/сделку» и Событие периода — название столбца. Фильтр по текущему статусу при этом не применяется, поэтому заявка, успевшая уйти дальше по воронке, из списка не пропадает, а число в столбце всегда совпадает с числом строк списка.

#Технический паспорт отчёта

В отчёт попадают заявки, у которых указан посредник (estate_buys.contacts_mediator_id) или агентство (estate_buys.mediator_agency_id) и выполнено условие периода. Условие зависит от выбранного типа даты:

При типах даты «Дата поступления заявки», «Дата начала сделки» и «Дата проведения сделки» отчёт дополнительно отбирает заявки в целевых статусах.

#Данные в системе

Агентство недвижимости (1), агент

Название агентства хранится в денормализованном поле estate_buys.agency_name; идентификатор — в estate_buys.mediator_agency_id (ссылается на contacts.contacts_id). Имя агента — в estate_buys.agent_name; идентификатор — в estate_buys.contacts_mediator_id.

Принцип выбора данных в MacroData:

SELECT
    estate_buys.mediator_agency_id,
    estate_buys.agency_name,
    estate_buys.contacts_mediator_id,
    estate_buys.agent_name
FROM estate_buys
WHERE DATE(estate_buys.created_at) >= DATE('{{start_date}}')
    AND DATE(estate_buys.created_at) <= DATE('{{end_date}}')
    AND (
        estate_buys.mediator_agency_id IS NOT NULL
        OR estate_buys.contacts_mediator_id IS NOT NULL
    )
ORDER BY estate_buys.mediator_agency_id;

Столбцы (3) ЦЗ, (4) Бронь, (5) Платная бронь, (6) Сделка в работе, (7) Сделка проведена

При типах даты «Дата поступления заявки», «Дата начала сделки» и «Дата проведения сделки» все показатели считаются по полю estate_buys.status (справочник estate_statuses). Платная бронь дополнительно фильтруется по estate_buys.is_payed_reserve.

Принцип выбора данных в MacroData:

SELECT
    estate_buys.mediator_agency_id,
    estate_buys.agency_name,
    COUNT(CASE WHEN estate_buys.status IN (5, 10, 20, 30, 50, 100) THEN 1 END) AS cnt_cz,
    COUNT(CASE WHEN estate_buys.status = 30 AND estate_buys.is_payed_reserve = 0 THEN 1 END) AS cnt_bron,
    COUNT(CASE WHEN estate_buys.status = 30 AND estate_buys.is_payed_reserve = 1 THEN 1 END) AS cnt_paid_bron,
    COUNT(CASE WHEN estate_buys.status = 50 THEN 1 END) AS cnt_deal_in_work,
    COUNT(CASE WHEN estate_buys.status = 100 THEN 1 END) AS cnt_deal_done
FROM estate_buys
WHERE DATE(estate_buys.created_at) >= DATE('{{start_date}}')
    AND DATE(estate_buys.created_at) <= DATE('{{end_date}}')
    AND estate_buys.mediator_agency_id IS NOT NULL
GROUP BY estate_buys.mediator_agency_id, estate_buys.agency_name;

Столбцы (3)–(7) при типе даты «Дата перевода в бронь/сделку»

Переходы берутся из истории изменения статусов заявок — estate_buys_statuses_log. Продвижение по воронке отличается от возврата условием status_from < status_to. У платной брони нет собственного статуса: её включение пишется в историю записью «бронь → бронь» с признаком is_payed_reserve = 1.

Проверка через EXISTS учитывает заявку один раз независимо от числа повторных переходов. Столбец «Сделка проведена» считается не по истории, а по дате проведения сделки с проверкой текущего статуса: расторжение сделки после регистрации дату проведения не стирает, и без проверки статуса расторгнутая сделка попала бы в столбец.

Принцип выбора данных в MacroData:

SELECT
    eb.mediator_agency_id,
    eb.agency_name,
    COUNT(CASE WHEN eb.status IN (5, 10, 20, 30, 50, 100)
                AND DATE(eb.created_at) BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
               THEN 1 END) AS cnt_cz,
    SUM(EXISTS(SELECT 1 FROM estate_buys_statuses_log l
                WHERE l.estate_buy_id = eb.id
                  AND l.status_from < 30 AND l.status_to = 30
                  AND l.log_date BETWEEN '{{start_date}}' AND '{{end_date}}')) AS cnt_bron,
    SUM(EXISTS(SELECT 1 FROM estate_buys_statuses_log l
                WHERE l.estate_buy_id = eb.id
                  AND l.status_from = 30 AND l.status_to = 30 AND l.is_payed_reserve = 1
                  AND l.log_date BETWEEN '{{start_date}}' AND '{{end_date}}')) AS cnt_paid_bron,
    SUM(EXISTS(SELECT 1 FROM estate_buys_statuses_log l
                WHERE l.estate_buy_id = eb.id
                  AND l.status_from < 50 AND l.status_to = 50
                  AND l.log_date BETWEEN '{{start_date}}' AND '{{end_date}}')) AS cnt_deal_in_work,
    COUNT(CASE WHEN eb.deal_date BETWEEN '{{start_date}}' AND '{{end_date}}'
                AND eb.status = 100
               THEN 1 END) AS cnt_deal_done
FROM estate_buys eb
WHERE eb.mediator_agency_id IS NOT NULL
GROUP BY eb.mediator_agency_id, eb.agency_name;

Столбец (2) — Сотрудников

Количество активных сотрудников агентства. Связь агента с агентством хранится в contacts_links с link_type = 253 (Агент). Роль агентства в паре определяется по полю roles_set таблицы contacts: значение accepted соответствует аккредитованному АН, agent_org — посреднику-компании.

Принцип выбора данных в MacroData:

SELECT
    sub.agency_contacts_id,
    COUNT(*) AS employees_count
FROM (
    SELECT
        CASE
            WHEN c1.roles_set LIKE '%accepted%' OR c1.roles_set LIKE '%agent_org%'
                THEN cl.contacts_1
            ELSE cl.contacts_2
        END AS agency_contacts_id
    FROM contacts_links cl
    LEFT JOIN contacts c1 ON cl.contacts_1 = c1.contacts_id
    WHERE cl.link_type = 253
) sub
GROUP BY sub.agency_contacts_id;

Столбец (9) — Активность

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

Принцип выбора данных в MacroData:

SELECT
    estate_buys.mediator_agency_id,
    estate_buys.agency_name,
    MAX(DATE(estate_buys.created_at)) AS last_activity
FROM estate_buys
WHERE DATE(estate_buys.created_at) >= DATE('{{start_date}}')
    AND DATE(estate_buys.created_at) <= DATE('{{end_date}}')
    AND estate_buys.mediator_agency_id IS NOT NULL
GROUP BY estate_buys.mediator_agency_id, estate_buys.agency_name;

#Расчётные данные

Столбец (8) — ЦЗ → Сделка проведена

Конверсия рассчитывается как отношение числа проведённых сделок к числу целевых заявок, округлённое до 2 знаков. При нулевом значении ЦЗ конверсия выводится как 0,00%. Формула одна для всех типов даты: при типе «Дата перевода в бронь/сделку» в неё подставляются целевые заявки периода и проведённые сделки периода, то есть конверсия считается за период.

Принцип выбора данных в MacroData:

SELECT
    sub.mediator_agency_id,
    sub.agency_name,
    sub.cnt_cz,
    sub.cnt_deal_done,
    CASE
        WHEN sub.cnt_cz > 0 THEN ROUND(sub.cnt_deal_done / sub.cnt_cz * 100, 2)
        ELSE 0
    END AS conversion
FROM (
    SELECT
        estate_buys.mediator_agency_id,
        estate_buys.agency_name,
        COUNT(CASE WHEN estate_buys.status IN (5, 10, 20, 30, 50, 100) THEN 1 END) AS cnt_cz,
        COUNT(CASE WHEN estate_buys.status = 100 THEN 1 END) AS cnt_deal_done
    FROM estate_buys
    WHERE DATE(estate_buys.created_at) >= DATE('{{start_date}}')
        AND DATE(estate_buys.created_at) <= DATE('{{end_date}}')
        AND estate_buys.mediator_agency_id IS NOT NULL
    GROUP BY estate_buys.mediator_agency_id, estate_buys.agency_name
) sub;