MACRO

Отчёт по продажам менеджеров (Финансы)

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

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

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

#Фильтры

Тип даты и период — дата сделки, по которой отбираются сделки:

  • дата проведения сделки (по умолчанию);
  • дата сдачи в Росреестр;
  • дата регистрации;
  • дата начала сделки.

По умолчанию период — с 1-го числа текущего месяца по сегодня. Сделка с незаполненной выбранной датой в отчёт не попадёт.

Тип недвижимости — «Жилая» (по умолчанию), «Коммерческая» или «Все».

Категория — категория объекта: квартира, машино-место, кладовая и т. д.

ЖК/Дом — отбор по дому объекта.

Менеджер — отбор по менеджеру сделки.

Статус — по умолчанию выбрано «Все»: в отчёт попадают сделки в статусах Сделка в работе и Сделка проведена. Брони и расторгнутые сделки в отчёт не попадают.

Программа покупки — отбор сделок по программе покупки. Столбцы других программ в первой таблице при этом остаются с нулями.

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

Тип поступлений — типы финансовых операций компании. Фильтр работает в две стороны:

  • в отчёт попадают только сделки, у которых есть платёж выбранного типа в статусе «Проведено» или «К оплате»;
  • суммы оплат, платежей к оплате, просрочки и графика считаются только по выбранным типам.

Теги — теги объекта.

Чтобы построить отчёт, нажмите Показать отчёт. Чтобы сбросить фильтры, нажмите Очистить.

#Опции

  • Только сделки с платежами в указанную дату — оставляет только объекты с платежами в выбранном периоде и добавляет третью таблицу «Поступления за период». В графике платежей выводятся только операции из периода.
  • С посредниками / Без посредников — отбор сделок по наличию посредника в заявке.
  • Без программы покупки — только сделки без программы покупки.
  • Не учитывать наценки — сумма по договорам считается без суммы наценок.
  • Нет 100% оплаты — скрывает объекты, оплаченные полностью.
  • Группировать по месяцам — в первой таблице вместо менеджеров выводятся месяцы по дате сделки.
  • Не учитывать сделки с тегами — вместе с фильтром «Теги» исключает объекты с выбранными тегами.

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

Сводная по менеджерам

Одна строка — один менеджер сделки. Внизу — строка «Итого».

  • Менеджер (1) — менеджер сделки. Нажмите на имя, чтобы построить отчёт только по нему.
  • Договоров (2) — количество объектов со сделками. Наличие номера договора не проверяется.
  • Сумма по договорам (3) — общая сумма сделок.
  • Сумма по графикам (4) — сумма платежей по графикам: проведённые и к оплате. Платежи сверх суммы сделки и прочие доходы прибавляются, возвраты вычитаются. Сумма может отличаться от (3), если график заполнен не полностью или в нём есть платежи сверх суммы сделки.
  • Разбивка по программам покупки (5):
    • «Уступка» — оплачено и к оплате по сделкам уступки;
    • «Без программы покупки» — сумма сделок без программы;
    • далее — по столбцу на каждую программу покупки компании с суммой сделок по ней. Набор программ в каждой компании свой. Выводятся все программы справочника, включая архивные, поэтому программы с одинаковыми названиями могут повторяться.
  • Оплачено в указанные даты (6) — проведённые операции по сделкам, дата которых попадает в период отчёта.
  • Оплачено всего (7) — все проведённые поступления по сделкам за всё время.
  • К оплате всего (8) — сумма платежей графика в статусе «К оплате».
  • Просрочено (9) — платежи «К оплате», срок которых уже прошёл.

Детализация по договорам

Сделки сгруппированы по менеджерам, внутри — по ЖК. После каждой группы идут подытоги «Итого по комплексу», «Итого по менеджеру», в конце — «Итого по компании». В подытогах выводятся количество, площадь, цена за м², стоимость и оплачено.

  • Менеджер (10) — строка менеджера, под ней — адрес объекта со ссылкой на карточку.
  • Тип (11) — тип или категория объекта.
  • № (12) — номер объекта.
  • Кмн. (13) — количество комнат.
  • Площ. (14) — площадь объекта из карточки, а не площадь по сделке.
  • Номер договора (15), Дата договора (16) — из сделки.
  • Инвестор (17) — основной покупатель со ссылкой на карточку контакта. Для сделок уступки ниже стоит пометка «Уступка».
  • Цена, ₽/м² (18) — стоимость, делённая на площадь объекта. В подытогах — сумма стоимостей, делённая на сумму площадей.
  • Стоимость (по договору) (19) — сумма сделки.
  • Оплачено всего (20) — проведённые операции по объекту.
  • Просрочено всего (21) — операции «К оплате», срок которых прошёл.
  • График (22) — список операций по объекту в формате «дата: сумма: статус». Нажмите на статус, чтобы открыть карточку операции.

Поступления за период

Таблица появляется при включённой опции «Только сделки с платежами в указанную дату».

Нажмите → XLS, чтобы выгрузить отчёт. Каждая таблица выгружается на отдельный лист: «По менеджерам», «По объектам» и, при включённой опции, «Поступления за указанный период» с датой оплаты, суммой, источником денег и примечанием.

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

#Условия попадания в отчёт

Одна строка выборки — один объект со сделкой. Объект попадает в отчёт, если выполнены все условия:

  • статус сделки estate_deals.deal_status — 110 (Сделка в работе) или 150 (Сделка проведена). Брони (105) и расторгнутые (140) не входят;
  • estate_sells.activity — sell или rent;
  • дом не удалён и не в архиве: estate_houses.status > 1;
  • тип объекта по умолчанию — жилая недвижимость (estate_sells.estate_sell_type = 'living');
  • выбранная дата сделки попадает в период.

Финансовые операции берутся из finances со статусом 1 (Проведено) или 3 (К оплате). Учитываются операции по сделкам в статусах 110 и 150 и объектам в статусах 50 и 100, поэтому платежи по расторгнутым сделкам не входят. Просроченным считается платёж в статусе 3, у которого date_to раньше текущего момента.

Поля фильтров в MacroData

ФильтрПоле
Тип недвижимостиestate_sells.estate_sell_type
Категорияestate_sells.estate_sell_category
ЖК/Домestate_sells.house_id
Менеджерestate_deals.deal_manager_id
Программа покупкиestate_deals.deal_program_name
Источник денегДанные не выгружаются в MacroData
Тип поступленийfinances.types_id. Признаки типов (поступление, возврат, прочий доход) не выгружаются в MacroData
Тегиestate_tags.estate_id = estate_sells.estate_sell_id, название — tags.tags_name
С посредниками / Без посредниковestate_buys.contacts_mediator_id
Тип датыПоле
Дата проведения сделкиestate_deals.deal_date
Дата сдачи в Росреестрestate_deals.justice_date_send
Дата регистрацииestate_deals.justice_date
Дата начала сделкиestate_deals.deal_date_start

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

Менеджер (1), Договоров (2), Сумма по договорам (3), Оплачено всего (7), К оплате всего (8)

Менеджер — estate_deals.deal_manager_id, имя — из users. Сумма — estate_deals.deal_sum. Оплачено и к оплате — агрегаты поступлений по сделке estate_deals.finances_income и estate_deals.finances_income_reserved.

Принцип выбора данных в MacroData без учёта дополнительных фильтров:

SELECT
  users.users_name AS manager,
  COUNT(*) AS agreements_count,
  SUM(estate_deals.deal_sum) AS deals_sum,
  SUM(estate_deals.finances_income) AS paid_total,
  SUM(estate_deals.finances_income_reserved) AS to_pay_total
FROM
  estate_deals
  JOIN estate_sells ON estate_sells.estate_sell_id = estate_deals.estate_sell_id
  JOIN estate_houses ON estate_houses.house_id = estate_sells.house_id
  LEFT JOIN users ON users.id = estate_deals.deal_manager_id
WHERE
  estate_deals.deal_status IN (110, 150)
  AND estate_sells.activity IN ('sell', 'rent')
  AND estate_houses.status > 1
  AND estate_sells.estate_sell_type = 'living'
  AND estate_deals.deal_date BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
GROUP BY
  estate_deals.deal_manager_id,
  users.users_name;

Разбивка по программам покупки (5)

Сумма estate_deals.deal_sum в разрезе программы покупки estate_deals.deal_program_name. Учитываются объекты в статусах 30, 50 и 100. В системе столбцы строятся по каждой программе справочника компании отдельно. В MacroData выгружается только название программы, поэтому программы с одинаковыми названиями объединяются в один столбец.

«Уступка» — сумма finances_income и finances_income_reserved по сделкам с estate_deals.is_concession = 1.

Принцип выбора данных в MacroData без учёта дополнительных фильтров:

SELECT
  users.users_name AS manager,
  IF(estate_deals.deal_program_name IS NULL OR estate_deals.deal_program_name = '',
    'Без программы покупки', estate_deals.deal_program_name) AS program,
  SUM(estate_deals.deal_sum) AS program_sum
FROM
  estate_deals
  JOIN estate_sells ON estate_sells.estate_sell_id = estate_deals.estate_sell_id
  JOIN estate_houses ON estate_houses.house_id = estate_sells.house_id
  LEFT JOIN users ON users.id = estate_deals.deal_manager_id
WHERE
  estate_deals.deal_status IN (110, 150)
  AND estate_sells.estate_sell_status IN (30, 50, 100)
  AND estate_sells.activity IN ('sell', 'rent')
  AND estate_houses.status > 1
  AND estate_sells.estate_sell_type = 'living'
  AND estate_deals.deal_date BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
GROUP BY
  estate_deals.deal_manager_id,
  users.users_name,
  program;

Сумма по графикам (4)

Данные не выгружаются в MacroData: признаки типов операций (поступление, возврат, платёж сверх суммы сделки) не выгружаются.

Оплачено в указанные даты (6)

Сумма finances.summa по операциям со статусом 1, у которых finances.date_added попадает в период отчёта. Тип операции не проверяется. Раз в неделю date_added проведённых операций выравнивается по дате проведения (date_to).

Принцип выбора данных в MacroData без учёта дополнительных фильтров:

SELECT
  users.users_name AS manager,
  SUM(finances.summa) AS paid_in_period
FROM
  estate_deals
  JOIN estate_sells ON estate_sells.estate_sell_id = estate_deals.estate_sell_id
  JOIN estate_houses ON estate_houses.house_id = estate_sells.house_id
  JOIN finances ON finances.deal_id = estate_deals.deal_id
    AND finances.status = 1
    AND finances.date_added >= DATE('{{start_date}}')
    AND finances.date_added < DATE('{{end_date}}') + INTERVAL 1 DAY
  LEFT JOIN users ON users.id = estate_deals.deal_manager_id
WHERE
  estate_deals.deal_status IN (110, 150)
  AND estate_sells.estate_sell_status IN (50, 100)
  AND estate_sells.activity IN ('sell', 'rent')
  AND estate_houses.status > 1
  AND estate_sells.estate_sell_type = 'living'
  AND estate_deals.deal_date BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
GROUP BY
  estate_deals.deal_manager_id,
  users.users_name;

Просрочено (9)

Без фильтра «Тип поступлений» значение берётся из агрегата просрочки по сделке, который не выгружается в MacroData. С фильтром — сумма операций выбранных типов со статусом 3 и date_to раньше текущего момента, как в запросе для (21).

Менеджер (10), Тип (11), № (12), Кмн. (13), Площ. (14), Номер договора (15), Дата договора (16), Инвестор (17), Стоимость (19)

Данные берутся из estate_deals со связями с estate_sells, estate_houses, geo_city_complex, users и contacts. ЖК — geo_city_complex.geo_complex_name; если ЖК не указан, — группа домов estate_houses.complex_name. Тип — категория объекта estate_sells.estate_sell_category; если у объекта заполнен EAV-атрибут estate_category_type, выводится его значение.

Принцип выбора данных в MacroData без учёта дополнительных фильтров:

SELECT
  users.users_name AS manager,
  IFNULL(geo_city_complex.geo_complex_name, estate_houses.complex_name) AS complex,
  CONCAT(estate_houses.geo_street_short_name, ' ', estate_houses.geo_street_name, ', д. ', estate_houses.geo_house) AS address,
  estate_sells.estate_sell_category,
  estate_sells.geo_flatnum,
  estate_sells.estate_rooms,
  estate_sells.estate_area,
  estate_deals.agreement_number,
  estate_deals.agreement_date,
  investor.contacts_buy_name AS investor,
  estate_deals.is_concession,
  estate_deals.deal_sum
FROM
  estate_deals
  JOIN estate_sells ON estate_sells.estate_sell_id = estate_deals.estate_sell_id
  JOIN estate_houses ON estate_houses.house_id = estate_sells.house_id
  LEFT JOIN geo_city_complex ON geo_city_complex.geo_complex_id = estate_houses.geo_city_complex_id
  LEFT JOIN users ON users.id = estate_deals.deal_manager_id
  LEFT JOIN contacts AS investor ON investor.contacts_id = estate_deals.contacts_buy_id
WHERE
  estate_deals.deal_status IN (110, 150)
  AND estate_sells.activity IN ('sell', 'rent')
  AND estate_houses.status > 1
  AND estate_sells.estate_sell_type = 'living'
  AND estate_deals.deal_date BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
ORDER BY
  manager,
  complex;

Оплачено всего (20), Просрочено всего (21)

Сумма finances.summa по объекту. Оплачено — операции со статусом 1, просрочено — операции со статусом 3 и date_to раньше текущего момента. Без фильтра «Тип поступлений» учитываются операции всех типов.

Принцип выбора данных в MacroData без учёта дополнительных фильтров:

SELECT
  estate_deals.estate_sell_id,
  SUM(IF(finances.status = 1, finances.summa, 0)) AS paid_total,
  SUM(IF(finances.status = 3 AND finances.date_to < NOW(), finances.summa, 0)) AS overdue_total
FROM
  estate_deals
  JOIN estate_sells ON estate_sells.estate_sell_id = estate_deals.estate_sell_id
  JOIN estate_houses ON estate_houses.house_id = estate_sells.house_id
  JOIN finances ON finances.deal_id = estate_deals.deal_id
    AND finances.status IN (1, 3)
WHERE
  estate_deals.deal_status IN (110, 150)
  AND estate_sells.estate_sell_status IN (50, 100)
  AND estate_sells.activity IN ('sell', 'rent')
  AND estate_houses.status > 1
  AND estate_sells.estate_sell_type = 'living'
  AND estate_deals.deal_date BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
GROUP BY
  estate_deals.estate_sell_id;

График (22)

Список операций по объекту со статусами 1 и 3: дата finances.date_added, сумма finances.summa, статус finances.status_name.

Принцип выбора данных в MacroData без учёта дополнительных фильтров:

SELECT
  estate_deals.estate_sell_id,
  finances.id AS finance_id,
  finances.date_added,
  finances.summa,
  finances.status_name
FROM
  estate_deals
  JOIN estate_sells ON estate_sells.estate_sell_id = estate_deals.estate_sell_id
  JOIN estate_houses ON estate_houses.house_id = estate_sells.house_id
  JOIN finances ON finances.deal_id = estate_deals.deal_id
    AND finances.status IN (1, 3)
WHERE
  estate_deals.deal_status IN (110, 150)
  AND estate_sells.estate_sell_status IN (50, 100)
  AND estate_sells.activity IN ('sell', 'rent')
  AND estate_houses.status > 1
  AND estate_sells.estate_sell_type = 'living'
  AND estate_deals.deal_date BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
ORDER BY
  estate_deals.estate_sell_id,
  finances.date_added;

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

Цена, ₽/м² (18)

Стоимость по договору, делённая на площадь объекта: ROUND(estate_deals.deal_sum / estate_sells.estate_area). В подытогах — ROUND(SUM(deal_sum) / SUM(estate_area)). Объекты без площади увеличивают числитель, но не знаменатель.

Принцип расчёта в MacroData:

SELECT
  users.users_name AS manager,
  ROUND(SUM(estate_deals.deal_sum) / SUM(estate_sells.estate_area)) AS price_m2
FROM
  estate_deals
  JOIN estate_sells ON estate_sells.estate_sell_id = estate_deals.estate_sell_id
  JOIN estate_houses ON estate_houses.house_id = estate_sells.house_id
  LEFT JOIN users ON users.id = estate_deals.deal_manager_id
WHERE
  estate_deals.deal_status IN (110, 150)
  AND estate_sells.activity IN ('sell', 'rent')
  AND estate_houses.status > 1
  AND estate_sells.estate_sell_type = 'living'
  AND estate_deals.deal_date BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
GROUP BY
  estate_deals.deal_manager_id,
  users.users_name;

Итого

Строки «Итого» и подытоги — сумма значений строк. Цена за м² в подытогах считается по формуле выше.