Анализ пайплайнов сделок

Или как один раз написать запрос, а потом радоваться

Posted by snakers41 on May 2, 2017




Красивая и бессмысленная картинка очень красива и очень бессмысленна!


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

Но для начала пара слов про то, что такое "пайплайн сделок". In a nutshell, если у вас много всяческих "opportunities", то вы как правило склонны записывать их в табличку. Это могут быть сделки, проекты, инвестиционные возможности, клиенты - все что угодно. На этой почве как правило даже построено много софта, который "помогает" принимать решения, но по факту является очень сложным аналогом табличного процессора с разделением прав. Если у всех ваших коллег есть мозг, то псевдо-реляционная база в Excel и гугл-доке как правило лучше, чем совсем "проприетарные" решения. Почему? Потому что они писались под некоторую "marketing persona", которой вы возможно не являетесь. А в табличном процессоре вы и аналитик, и DBA и продажник. Простыми словами - ну решили 5 лет назад основатели какого-нибудь CRM, что у американцев мозги работают именно так, и сейчас ни шагу в сторону.

Короче, пайплайн сделок это:

  • Таблица (или в особо запущенном случае целый поток таблиц с логами, допустим за каждый день);
  • В ней есть постоянно меняющиеся данные, например дата последнего обновления и статус "сделки";
  • История / логи как правило не хранятся;


Про сложность анализа:

  • Даже такие простые данные - весьма динамические;
  • Если логи не хранятся (в 99% у вас нет кастомного самописного решения именно под вас), то анализ надо делать или эвристиками (статус "в работе" и "дата обновления такая-то") или просто фильтрами, что не отражает движения проекта;
  • Временная ось в файле и временная ось в реальности - две разные вещи, что усложняет анализ. Если вы хотите посмотреть "а что было в декабре", а у вас только дата последнего изменения статуса, то придется попотеть;
  • Если проект более менее большой, то становятся интересны такие метрики:
    • Сколько в среднем "живет" сделка;
    • Какой сейчас беклог сделок (сделки, которые начинают "протухать");
    • Список сделок с самой большой вероятностью "протухания";
    • Всё это в разрезе на 1 сотрудника;
    • ...


Про случай:

  • На вакансию с относительно большой долларовой зарплатой попросили написать SQL запрос, который бы считал беклог по сделкам (это самое динамическое изменение);
  • Я написал в формате "сделать брут-форс за ~30 минут, чтобы быстро ответить";
  • Мне написали "ваш запрос слишком длинный и непонятный,  упростите его с with секцией, он дает верный ответ, но мне непонятно почему и мне лень разбираться";
  • У причесал запрос, на что в итоге получил витиеватую аппеляцию к моему интеллекту, мол "раз запрос такой длинный и некрасивый, то вы не умеете мыслить";
  • Я попросил "правильный" запрос - он действительно был лаконичнее раза в три, но ответ давал на 99% идентичный;
  • Сделал для себя жизненный вывод, что есть люди про "суть" и про "форму";


Фишка самого запроса

  • Считает динамический беклог (число сделок, которые "зависли") по календарным неделям (это не тривиально);
  • Оригинальный текст ниже;
  • Может его логика или сам запрос поможет в вашей работе с пайплайном (SQL все таки simple query language);

Оригинальный текст задания

Here is the test task:

Our customer pipeline consists of many stages. We calculate conversion and transition time between each pair of consequent stages which is quite simple, and for some stages we calculate an additional metric which is called the average backlog. It is a weekly average of daily count of customers that are still waiting to transit to the next stage. High number means there is a bottleneck that should be improved.

Logic:
1) We consider the customer being in a backlog if he doesn't reach stage #2 between 1 and 14 days after reaching stage #1
2) If customer reaches stage #2 on the same day or the next day he never appears in the backlog
3) If customer doesn't reach stage #2 at all he's in the backlog until day 14, then we forget about this customer
4) If customer reaches stage #2 later than 14 days after stage #1 he's appearing in backlog only until day 14

Example: if a customer reached stage 1 on 2017-01-01 and stage 2 on 2017-01-08, he appears in daily backlog on 2017-01-02,2017-01-03,2017-01-04,2017-01-05,2017-01-06 and 2017-01-07

We provide you a dataset of dummy customer records that have ID and 2 timestamps for entering into 2 stages. Your goal is to write a PostgreSQL query that calculates weekly average backlog and returns result looking like this:

week, backlog
2016-02-01, 5.5	
2016-02-08, 9.2	


Данные - по ссылке.


Мой запрос

WITH 
days_list AS 
(
SELECT
i :: DATE AS current_day
FROM
generate_series (
(
SELECT
MIN (d.state_1) :: DATE
FROM
question_data d
),
(
SELECT
MAX (d.state_1) :: DATE
FROM
question_data d
),
'1 day' :: INTERVAL
) i
),
basic_data AS 
(
SELECT
d."id" AS customer_id,
d.state_1,
d.state_2,
CASE
WHEN (
date_trunc('day', d.state_2) - date_trunc('day', d.state_1)
) < ('2 day' :: INTERVAL) THEN 1
ELSE 0
END AS never_in_bl,
CASE
WHEN d.state_2 ISNULL THEN 1
ELSE 0
END AS never_st_2,
d.state_1 + '14 days' :: INTERVAL AS max_bl_day_never_reach,
CASE
WHEN (
date_trunc('day', d.state_2) - date_trunc('day', d.state_1)
) > ('14 days' :: INTERVAL) THEN 1
ELSE 0
END AS reached_late,
d.state_1 + '14 days' :: INTERVAL AS max_bl_day_late_reach
FROM
question_data d
),
raw_data AS 
(
SELECT
*
FROM
basic_data
JOIN days_list ON 1 = 1
),
final_calcs AS (
SELECT
raw_data.customer_id AS customer_id,
CASE
WHEN (
raw_data.current_day - date_trunc('day', raw_data.state_1)
) < ('0 days' :: INTERVAL) THEN 0
ELSE
1
END AS stats_applicable,
raw_data.current_day AS current_day,
CASE
WHEN raw_data.never_in_bl = 1 THEN 0
ELSE
CASE
WHEN raw_data.never_st_2 = 1
AND (
raw_data.current_day BETWEEN date_trunc('day', raw_data.state_1) + '1 day' :: INTERVAL
AND raw_data.max_bl_day_never_reach
) THEN 1
ELSE
CASE
WHEN raw_data.reached_late = 1
AND (
raw_data.current_day BETWEEN date_trunc('day', raw_data.state_1) + '1 day' :: INTERVAL
AND raw_data.max_bl_day_never_reach
) THEN 1
ELSE
CASE
WHEN raw_data.current_day BETWEEN date_trunc('day', raw_data.state_1) + '1 day' :: INTERVAL
AND raw_data.state_2 - '1 day' :: INTERVAL THEN 1
ELSE 0
END
END
END
END AS is_backlog
FROM
raw_data
ORDER BY
customer_id ASC,
current_day ASC


-------------------------
SELECT
average2.weekly AS week,
ROUND(AVG(average2.backlog), 1) AS backlog
FROM
(
SELECT
SUM (average.is_backlog) AS backlog,
average.weekly,
average.current_day
FROM
(
SELECT
final_calcs.customer_id AS customer_id,
final_calcs.stats_applicable * final_calcs.is_backlog AS is_backlog,
final_calcs.current_day AS current_day,
date_trunc(
'week',
final_calcs.current_day :: DATE
) :: DATE AS weekly
FROM
final_calcs
) average
GROUP BY
average.current_day,
average.weekly
ORDER BY
average.current_day ASC
) average2
GROUP BY
average2.weekly

Запрос работодателя

with
entries as (
select 
id
,state_1 -- for testing the subquery
,state_2 -- for testing the subquery
,(state_1+interval '1 day')::date as backlog_start
,least(state_2-interval '1 day',state_1+interval '14 day')::date as backlog_end
from question_data
where coalesce(state_2,state_1+interval '14 day')::date-state_1::date>1
)
,daily_backlog as (
select 
generate_series(backlog_start, backlog_end,'1 day')::date as day
,count(id) as daily_backlog 
from entries
group by 1
)
select 
 date_trunc('week',day)::date as week
,avg(daily_backlog)::numeric(5,1) as backlog
from daily_backlog
group by 1
order by 1;

Можно бесконечно дискутировать по поводу правильности / подхода к написанию /  логики, но в моей системе координат достижение результата максимально быстро и просто - первый приоритет, а наведение красоты - второй. Также сказывается влияние того, что на практике как правило приходится иметь дело с априори более большими запросами, что отбивает страх к их монструозности.