MACRO

Реестр броней

Реестр броней в MacroCRM расположен в блоке Отчеты → Продажи: ассортимент:

Отчет позволяет оценить ситуацию с бронями: на сколько часто и в каком количестве ставятся и снимаются брони в разрезе домов, менеджеров и разной категории недвижимости.

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

1. Выставите интересующий вас период действия с бронями в фильтре Дата.

2. В фильтре Действие с бронью укажите Снятие, Постановка, Пролонгация, Перевод в платную бронь.

3. Дополнительно можно выставить Тип действия: Автоматическое или Ручное, чтобы исключить действия системы, например.

4. Оставьте фильтр ЖК/Дом и Менеджер пустыми, чтобы оценить ситуацию по компании в целом, или, наоборот, укажите, чтобы посмотреть более конкретную статистику.

5. Фильтр Текущий статус позволяет выбрать действия с теми заявками, которые сейчас находятся в выбранном статусе.

Так как отчет фиксирует буквально все действия с Бронями, полезными могут оказаться дополнительные опции, такие как:

  • Платная бронь — показывает действия по тем заявкам, в которых включена платная бронь, вручную или через проведение финансовой операции Задаток.
  • Только последнее действие по заявке — если по одной и тоже же заявке ставили и снимали бронь несколько раз (при этом возможно на разные объекты), то по включенной опции отчет покажет только последнее действие с этой заявкой.
  • Исключать действия, где бронь на другой объект — в ситуации, когда постановка брони была на один объект, затем бронь сняли и поставили на другой объект, эта опция может быть полезной. Она исключит из отчета первые действия по заявке, если в итоге бронь сейчас стоит на другой объект. Если опция выключена, то в таблице отображается «!»
  • Показать UTM (xls) — показывает UTM-метки в скачанном отчете в xls-формате.
  • Без агента — показывает действия по тем заявкам, в которых не указан контакт-агент.
  • С агентом — показывает действия по тем заявкам, в которых указан контакт-агент.
  • Уникальные действия — единожды ставили и снимали Бронь.
  • Уникальные первичные обращения — заявки со статусом Бронь с уникальным первичным обращением.
  • Показывать площади в сводной таблице — расширяет сводную таблицу в отчёте, добавляя в неё суммарную площадь забронированных помещений.
  • Показывать стоимость отделки — расширяет основную таблицу отчёта, добавляя в неё соответствующий столбец, который выводит стоимость отделки забронированного объекта.

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

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

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

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

Каждая строка детальной таблицы — это одно событие, отражающее изменение статуса заявки с привязкой к объекту и, при наличии, к сделке. Основные данные берутся из сущности `estate_buys_statuses_log`, с дополнительными связями к `estate_deals`, `estate_buys`, `estate_sells`, `users`.

Связь с жилым комплексом устанавливается через цепочку: `estate_sells` → `estate_houses` → `geo_city_complex`.

Сводная таблица по ЖК

Рабочие брони: Платные (1), Бесплатные (2), Все (3)

Рабочие брони — это брони, поставленные за указанный период (фильтр Дата действия с бронью — `log_date`), у которых текущий статус заявки равен «Бронь» (`estate_buys.status = 30`).

  • Платные брони (1): `estate_buys.is_payed_reserve = 1`
  • Бесплатные брони (2): `estate_buys.is_payed_reserve = 0` или `NULL`
  • Все брони (3): сумма платных и бесплатных

Количество — `COUNT(estate_buys.id)`. Сумма рассчитывается по формуле:

SUM(CASE WHEN estate_deals.deal_sum > 0 THEN estate_deals.deal_sum ELSE estate_sells.estate_price END)

Выборка данных в MacroData:

SELECT
    geo_city_complex.name AS `Жилой комплекс`,
    COUNT(CASE WHEN estate_buys.is_payed_reserve = 1 THEN estate_buys.id END) AS `Платные брони, шт`,
    SUM(CASE WHEN estate_buys.is_payed_reserve = 1
        THEN (CASE WHEN estate_deals.deal_sum > 0 THEN estate_deals.deal_sum ELSE estate_sells.estate_price END)
        ELSE 0 END) AS `Платные брони, руб`,
    COUNT(CASE WHEN (estate_buys.is_payed_reserve = 0 OR estate_buys.is_payed_reserve IS NULL) THEN estate_buys.id END) AS `Бесплатные брони, шт`,
    SUM(CASE WHEN (estate_buys.is_payed_reserve = 0 OR estate_buys.is_payed_reserve IS NULL)
        THEN (CASE WHEN estate_deals.deal_sum > 0 THEN estate_deals.deal_sum ELSE estate_sells.estate_price END)
        ELSE 0 END) AS `Бесплатные брони, руб`,
    COUNT(estate_buys.id) AS `Все брони, шт`,
    SUM(CASE WHEN estate_deals.deal_sum > 0 THEN estate_deals.deal_sum ELSE estate_sells.estate_price END) AS `Все брони, руб`
FROM estate_buys_statuses_log log
LEFT JOIN estate_buys ON log.estate_buy_id = estate_buys.id
LEFT JOIN estate_deals ON estate_buys.deal_id = estate_deals.id
LEFT JOIN estate_sells ON estate_deals.estate_sell_id = estate_sells.id
LEFT JOIN estate_houses ON estate_sells.house_id = estate_houses.id
LEFT JOIN geo_city_complex ON estate_houses.geo_city_complex_id = geo_city_complex.id
WHERE estate_buys.status = 30
  AND DATE(log.log_date) BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
  AND log.status_from_name IN ('Неразобранное', 'Проверка', 'Отложено', 'Подбор')
  AND log.status_to_name = 'Бронь'
GROUP BY geo_city_complex.name;

Постановка (4), Снятие (5), Продление (6), Перевод в платную (7)

Подсчёт действий с бронями производится на основе переходов между статусами из `estate_buys_statuses_log`. Фильтр периода — по `log_date` (дата действия). Для каждого типа действия применяется фильтрация по `status_from_name` и `status_to_name`.

Количество — `COUNT(id)`. Сумма — аналогично рабочим броням через `estate_deals.deal_sum` / `estate_sells.estate_price`.

Пример для подсчёта постановок в бронь:

SELECT COUNT(id) AS `Постановка`
FROM estate_buys_statuses_log
WHERE status_from_name IN ('Неразобранное', 'Проверка', 'Отложено', 'Подбор')
  AND status_to_name = 'Бронь'
  AND DATE(log_date) BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}');

Доля снятых броней, % вычисляется как отношение числа снятий к числу постановок в разрезе ЖК:

COUNT(Снятие) / COUNT(Постановка) * 100

Детальная таблица

Действие/тип (8) и Дата (9)

Тип действия определяется через `CASE` по переходам статусов. Дата события — `log_date`.

SELECT
    CASE
        WHEN status_from_name IN ('Неразобранное', 'Проверка', 'Отложено', 'Подбор')
            AND status_to_name = 'Бронь' THEN 'Постановка'
        WHEN status_from_name = 'Бронь'
            AND status_to_name NOT IN ('Бронь', 'Сделка в работе', 'Сделка расторгнута', 'Сделка проведена') THEN 'Снятие'
        WHEN status_from_name = 'Бронь'
            AND status_to_name = 'Бронь' THEN 'Продление'
        WHEN status_from_name = 'Бронь'
            AND status_custom_to_name = 'Платная бронь' THEN 'Перевод в платную бронь'
    END AS `Действие/тип`,
    log_date AS `Дата`
FROM estate_buys_statuses_log
WHERE
    (status_from_name IN ('Неразобранное', 'Проверка', 'Отложено', 'Подбор') AND status_to_name = 'Бронь') OR
    (status_from_name = 'Бронь' AND status_to_name NOT IN ('Бронь', 'Сделка в работе', 'Сделка расторгнута', 'Сделка проведена')) OR
    (status_from_name = 'Бронь' AND status_to_name = 'Бронь') OR
    (status_from_name = 'Бронь' AND status_custom_to_name = 'Платная бронь')
ORDER BY log_date DESC;

Дата окончания брони (10)

Хранится в `estate_deals.reserve_date`. Получается через `LEFT JOIN` с `estate_deals`:

LEFT JOIN estate_deals ON estate_buys_statuses_log.deal_id = estate_deals.id
estate_deals.reserve_date AS `Дата окончания брони`

Категория (11), Объект (12), S м² (13), Цена по прайсу (14)

Для получения данных по объекту необходимо через `estate_deals` установить связь с `estate_sells`.

LEFT JOIN estate_deals ON estate_buys_statuses_log.deal_id = estate_deals.id
LEFT JOIN estate_sells ON estate_deals.estate_sell_id = estate_sells.id
CASE estate_sells.estate_sell_category
    WHEN 'flat' THEN 'Квартира'
    WHEN 'garage' THEN 'Парковка'
    WHEN 'storageroom' THEN 'Кладовка'
    WHEN 'house' THEN 'Дом'
    WHEN 'comm' THEN 'Коммерция'
END AS `Категория`,

estate_sells.estate_area AS `S, м²`,

estate_sells.estate_price AS `Цена по прайсу`

Поле Объект (12) содержит агрегированное описание объекта: комнатность, категория, адрес, площадь, цена — всё это формируется из полей `estate_sells`.

Стоимость по договору (15)

Хранится в `estate_deals.deal_sum`. Если `deal_sum = 0`, используется `estate_sells.estate_price` как fallback:

CASE WHEN estate_deals.deal_sum > 0
    THEN estate_deals.deal_sum
    ELSE estate_sells.estate_price
END AS `Стоимость по договору`

Заявка/Контакт (16)

Хранится в `estate_buys`. Связь:

LEFT JOIN estate_buys ON estate_buys_statuses_log.estate_buy_id = estate_buys.id
estate_buys.id AS `Заявка/Контакт`

Посредник (17)

Данные об агенте хранятся в `estate_deals`. Выводится имя агента и, при наличии, название агентства:

estate_deals.agent_name AS `Посредник`
-- дополнительно, если нужно агентство:
-- CONCAT(estate_deals.agent_name, ' ', IFNULL(estate_deals.agency_name, '')) AS `Посредник`

Текущий статус заявки (18)

Хранится в `estate_buys.status_name`. Получается через тот же `LEFT JOIN estate_buys`:

estate_buys.status_name AS `Текущий статус заявки`

Программа покупки (19)

Хранится в `estate_deals.deal_program_name`:

estate_deals.deal_program_name AS `Программа покупки`

Менеджер (20)

`estate_buys_statuses_log` уже содержит `users_id` — инициатора события. Для получения ФИО установите связь с `users`:

LEFT JOIN users ON estate_buys_statuses_log.users_id = users.id
users.users_name AS `Менеджер`

Итоговый запрос (детальная таблица)

SELECT
    CASE
        WHEN status_from_name IN ('Неразобранное', 'Проверка', 'Отложено', 'Подбор')
            AND status_to_name = 'Бронь' THEN 'Постановка'
        WHEN status_from_name = 'Бронь'
            AND status_to_name NOT IN ('Бронь', 'Сделка в работе', 'Сделка расторгнута', 'Сделка проведена') THEN 'Снятие'
        WHEN status_from_name = 'Бронь'
            AND status_to_name = 'Бронь' THEN 'Продление'
        WHEN status_from_name = 'Бронь'
            AND status_custom_to_name = 'Платная бронь' THEN 'Перевод в платную бронь'
    END AS `Действие/тип`,
    log.log_date                      AS `Дата`,
    d.reserve_date                    AS `Дата окончания брони`,
    CASE s.estate_sell_category
        WHEN 'flat'        THEN 'Квартира'
        WHEN 'garage'      THEN 'Парковка'
        WHEN 'storageroom' THEN 'Кладовка'
        WHEN 'house'       THEN 'Дом'
        WHEN 'comm'        THEN 'Коммерция'
    END AS `Категория`,
    s.estate_area                     AS `S, м²`,
    s.estate_price                    AS `Цена по прайсу`,
    CASE WHEN d.deal_sum > 0
        THEN d.deal_sum
        ELSE s.estate_price
    END                               AS `Стоимость по договору`,
    b.id                              AS `Заявка/Контакт`,
    d.agent_name                      AS `Посредник`,
    b.status_name                     AS `Текущий статус заявки`,
    d.deal_program_name               AS `Программа покупки`,
    u.users_name                      AS `Менеджер`
FROM estate_buys_statuses_log log
LEFT JOIN estate_deals  d ON log.deal_id       = d.id
LEFT JOIN estate_sells  s ON d.estate_sell_id  = s.id
LEFT JOIN estate_buys   b ON log.estate_buy_id = b.id
LEFT JOIN users         u ON log.users_id      = u.id
WHERE
    (log.status_from_name IN ('Неразобранное', 'Проверка', 'Отложено', 'Подбор')
        AND log.status_to_name = 'Бронь') OR
    (log.status_from_name = 'Бронь'
        AND log.status_to_name NOT IN ('Бронь', 'Сделка в работе',
        'Сделка расторгнута', 'Сделка проведена')) OR
    (log.status_from_name = 'Бронь' AND log.status_to_name = 'Бронь') OR
    (log.status_from_name = 'Бронь'
        AND log.status_custom_to_name = 'Платная бронь')
ORDER BY log.log_date DESC;