· 4 years ago · Jun 23, 2021, 01:22 PM
1use orders;
2
3-- В качестве ДЗ делам прогноз ТО на 05.2017. В качестве метода прогноза - считаем сколько денег тратят группы клиентов в день:
4
5-- ТО по месяцам
6select YEAR(o_date) as s_year,
7MONTH(o_date) as s_month,
8round(sum(price), 2) as sum_price
9from orders
10group by s_year, s_month;
11
12-- Прогнозируем данные по ТО за 2017-05 - 217075552.51
13
14/* Вычисляем количество и суммы заказов пользователей за период до 05-2017
15 * Для того, чтобы видеть промежуточные данные и выполнять проверку делаем через временные таблицы
16 */
17
18drop table if EXISTS user_orders_count;
19CREATE TEMPORARY table IF NOT exists user_orders_count as (
20select
21user_id,
22count(user_id) as num_orders,
23sum(price) as total,
24max(o_date) as o_last,
25min(o_date) as o_first
26from orders
27group by user_id
28HAVING o_last < '2017-05-01' and o_first < '2017-05-01');
29
30-- Находим количество пользователей и суммы заказов для категорий по количеству заказов >=3, =2 и =1 и по датам последних заказов
31select
32case
33 when num_orders >= 3 and DATEDIFF('2017-05-01', o_last) <= 30 then 'A'
34 when num_orders >= 3 and DATEDIFF('2017-05-01', o_last) > 30 and DATEDIFF('2017-05-01', o_last) <= 90 then 'B'
35 when num_orders = 2 and DATEDIFF('2017-05-01', o_last) <= 30 then 'C'
36 when num_orders = 2 and DATEDIFF('2017-05-01', o_last) > 30 and DATEDIFF('2017-05-01', o_last) <= 90 then 'D'
37 when num_orders = 1 and DATEDIFF('2017-05-01', o_last) <= 30 then 'E'
38 when num_orders = 1 and DATEDIFF('2017-05-01', o_last) > 30 and DATEDIFF('2017-05-01', o_last) <= 90 then 'F'
39 ELSE 'None'
40end
41as category,
42count(user_id) as num_users,
43round(sum(total), 2) as sum_orders
44from user_orders_count
45group by category
46order by category;
47 /*
48A 5322 72418107,98 1,05% 4,45%
49B 9029 110744260,93 1,78% 6,80% 58,94%
50C 4192 20926425,80 0,82% 1,29% 126,96% 61,37%
51D 8114 40960968,99 1,60% 2,52% 51,66% 65,59%
52E 29667 71543512,63 5,83% 4,39% 14,13% 7,11%
53F 59722 141248313,47 11,75% 8,67% 49,68% 7,02%
54None 392423 1170383597,18 77,18% 71,88%
55 508469 1628225186,98 100,00% 100,00%
56
57*/
58
59-- 1. Группа часто покупающих (3 и более покупок) и которые последний раз покупали не так давно. Считаем сколько денег оформленного заказа приходится на 1 день. Умножаем на 30.
60select sum(total / DATEDIFF(o_last, o_first)) * 31 as total_for_A from user_orders_count where num_orders >= 3 and DATEDIFF('2017-05-01', o_last) <= 30; -- 34686963.68664071
61
62-- Пользователей, которые делали заказы более 90 дней назад исключаем, вероятнее всего они ушли.
63
64-- 2. Группа часто покупающих, но которые не покупали уже значительное время. Так же можем сделать вывод, из такой группы за след месяц сколько купят и на какую сумму. (постараться продумать логику)
65-- Которые покупали более 30, но менее 90 дней назад.
66-- Соотношение групп B и A 58,94%, вероятность возможного перехода покупателя из группы B в A 58,94%, но данная вероятность снидается на 1% за кадждый день более 30 дней
67select sum(total / DATEDIFF(o_last, o_first)) * 31 * 0.5894 * (DATEDIFF('2017-05-01', o_last) / 100) as total_for_B
68from user_orders_count where num_orders >= 3 and DATEDIFF('2017-05-01', o_last) > 30 and DATEDIFF('2017-05-01', o_last) <= 90; -- 24291461.311776076
69
70-- 3. Отдельно разобрать пользователей с 1 и 2 покупками за все время, прогнозируем их.
71-- Делим пользователей c 2 покупками на подгруппы:
72-- которые покупали меньше 30 дней назад. По соотношению групп C и A 126,96% (вероятность того, что поокупатель, совершивший 2 покупки совершит 3ю в периоде менее 30 дней)
73-- и по соотношению групп C и D 51,66%, вероятность того, что данные пользователи останутся с 2 покупками на более длительный срок итоговая вероятность покупки данной группы = 126,96% * (100% - 51,66%) = 61.37%
74select sum(total / DATEDIFF(o_last, o_first)) * 31 * 0.6137 as total_for_C from user_orders_count where num_orders = 2 and DATEDIFF('2017-05-01', o_last) <= 30; -- 32054833.766665775
75-- Которые покупали более 30, но менее 90 дней назад.
76-- Соотношение групп D и A 65,59%, вероятность возможного перехода покупателя из группы D в A 65,59%, но данная вероятность снидается на 1% за кадждый день более 30 дней
77select sum(total / DATEDIFF(o_last, o_first)) * 31 * 0.6559 * (DATEDIFF('2017-05-01', o_last) / 100) as total_for_D
78from user_orders_count where num_orders = 2 and DATEDIFF('2017-05-01', o_last) > 30 and DATEDIFF('2017-05-01', o_last) <= 90; -- 38904134.24618455
79
80-- Делим пользователей с 1 покупкой на группы:
81-- которые покупали меньше 30 дней назад. По соотношению групп E и C 14,13% (вероятность того, что поокупатель, совершивший 1 покупки совершит 2ю в периоде менее 30 дней)
82-- и по соотношению групп E и F 49.68%, вероятность того, что данные пользователи останутся с 1 покупками на более длительный срок итоговая вероятность покупки данной группы = 14,13% * (100% - 49,68%) = 7.11%
83select sum(total / DATEDIFF('2017-05-01', o_first)) * 31 * 0.0711 as total_for_E from user_orders_count where num_orders = 1 and DATEDIFF('2017-05-01', o_last) <= 30; -- 19015931.05448108
84-- Которые покупали более 30, но менее 90 дней назад.
85-- Соотношение групп C и F 7,02%, вероятность возможного перехода покупателя из группы F в C 7.02%, но данная вероятность снидается на 1% за кадждый день более 30 дней
86select sum(total / DATEDIFF('2017-05-01', o_first)) * 31 * 0.0702 * (DATEDIFF('2017-05-01', o_last) / 100) as total_for_f
87from user_orders_count where num_orders = 1 and DATEDIFF('2017-05-01', o_last) > 30 and DATEDIFF('2017-05-01', o_last) <= 90; -- 5008960.448073409
88
89
90-- К прогнозу по данным имеющихся пользователей следует добавить прогнозные данные по новым пользователеям, которые совершат покупки впервые за указанный период
91drop table if EXISTS new_users_count;
92CREATE TEMPORARY table IF NOT exists new_users_count as(
93select YEAR(o_date) as s_year,
94MONTH(o_date) as s_month,
95user_id,
96min(o_date),
97price
98from orders
99where o_date < '2017-05-01'
100group by user_id);
101
102
103select s_year,
104s_month,
105count(user_id)as users,
106sum(price)
107from new_users_count
108group by s_year, s_month;
109
110/*
1112016 1 35225 75835442.28460693
1122016 2 27892 61370215.94893646
1132016 3 33281 69744915.15527344
1142016 4 33751 77359914.12964916
1152016 5 24932 57242008.629873276
1162016 6 26515 59246135.948207855
1172016 7 25427 58627435.25072956
1182016 8 28953 66041833.224998474
1192016 9 31858 77547801.81107712
1202016 10 43475 110316978.08640623
1212016 11 61099 141240991.50376892
1222016 12 72682 146535134.17528778
1232017 1 35740 89690680.83057737
1242017 2 33728 82841224.15319824
1252017 3 41721 104491605.38817787
1262017 4 38121 94831433.24714088
127*/
128
129-- Новые покупатели принесли 05-2016 года 57242008.629873276 / 77359914.12964916 = 74% от 04-2016
130-- Прогноз ТО от новых покупатеей за 05-2017 = 94831433.24714088 * 0,74 = 70175260.6028842
131
132/* Итого прогноз ТО на 05-2017
133New 70175260,60
134A 34686963,69
135B 24291461,31
136C 32054833,77
137D 38904134,25
138E 19015931,05
139F 5008960,45
140Итого: 224137545,12
141
142 * Полученное значение ТО на 3,15% превысило реальный показатель за данный период - 217075552.51
143 */
144