· 6 years ago · Jun 24, 2019, 01:36 PM
1IF OBJECT_ID ('tempdb..#orders_single_orderers_2nd_window') is not null drop table #orders_single_orderers_2nd_window;
2select
3a.indid,
4count(distinct(a.ordno)) as orders,
5sum(a.ORDITEMQTY) as items,
6sum(a.grosreport) as grosreport,
7SUM(a.GROSREPORT)/COUNT(distinct(a.ordno)) as AOV,
8sum(a.GROSREPORT)/SUM(a.orditemqty) as AUR,
9count(distinct(a.ordno)) as freq,
10SUM(a.orditemqty)/COUNT(distinct(a.ordno)) as UPT
11into #orders_single_orderers_2nd_window
12from TALBOTS_BASE..ORDERS a
13inner join #single_orderers b
14on a.INDID = b.INDID
15where ORDDATE >= @2nd_orbit_start and ORDDATE <= @2nd_orbit_end
16and GROSREPORT > 0
17and COUNTRYFLG = 'US'
18and SUBCHANNEL not in ('RETAILOUTLET','UPSCALEOUTLET')
19and a.INDID is not null
20group by a.INDID;
21
22CREATE PROCEDURE [dbo].[BUILD_M2_MODEL]
23AS
24BEGIN
25
26declare @Start_Date datetime = getdate()
27DECLARE @orbit_start DATETIME
28DECLARE @orbit_end DATETIME
29DECLARE @2nd_orbit_start DATETIME
30DECLARE @2nd_orbit_end DATETIME
31DECLARE @standard_dev FLOAT
32DECLARE @standard_dev_2 FLOAT
33DECLARE @standard_dev_3 FLOAT
34DECLARE @standard_dev_4 FLOAT
35
36
37
38
39SELECT @2nd_orbit_end = MAX(orddate) FROM TALBOTS_BASE..orders;
40
41set @2nd_orbit_start = @2nd_orbit_end - 364
42set @orbit_end = @2nd_orbit_start - 1
43set @orbit_start = @orbit_end - 364
44
45
46
47declare @segment int
48set @segment = 1
49set @standard_dev = 0.1
50set @standard_dev_2 = 0.1
51set @standard_dev_3 = 0.25
52set @standard_dev_4 = 0.5
53
54IF OBJECT_ID('TALBOTS_RAW.dbo.M2_MODEL_SCORE') IS NOT NULL
55TRUNCATE TABLE TALBOTS_RAW.dbo.M2_MODEL_SCORE;
56
57
58IF OBJECT_ID ('tempdb..#order_FREQ_DIST') is not null drop table #order_FREQ_DIST;
59select
60indid,COUNT(distinct(ORDNO)) as ord_freq, SUM(grosreport) as tot_dollars
61into #order_FREQ_DIST
62from TALBOTS_BASE..ORDERS
63where ORDDATE >= @orbit_start and ORDDATE <= @orbit_end
64and GROSREPORT > 0
65and INDID is not null
66and COUNTRYFLG = 'US'
67and SUBCHANNEL not in ('RETAILOUTLET','UPSCALEOUTLET')
68group by INDID;
69
70
71IF OBJECT_ID ('tempdb..#order_quarts') is not null drop table #order_quarts;
72select a.indid,
73 ord_freq,
74 tot_dollars,
75 ntile(4) over (order by ord_freq,tot_dollars ) as QUARTILE
76 into #order_quarts
77 from #order_freq_dist a
78 order by ord_freq,tot_dollars;
79
80select QUARTILE,COUNT(distinct(indid)) from #order_quarts group by quartile
81
82
83IF OBJECT_ID ('tempdb..#orders') is not null drop table #orders;
84select
85a.indid,orddate,sum(grosreport) as grosreport,sum(orditemqty) as orditemqty,sum(grosreport)/SUM(orditemqty) as dolper_items,
86 ROW_NUMBER () over (partition by a.indid order by orddate asc) as rank
87into #orders
88from TALBOTS_BASE..ORDERS a
89inner join #order_quarts b
90on a.INDID = b.INDID
91where ORDDATE >= @orbit_start and ORDDATE <= @orbit_end
92and GROSREPORT > 0
93and COUNTRYFLG = 'US'
94and SUBCHANNEL not in ('RETAILOUTLET','UPSCALEOUTLET')
95and a.INDID is not null
96and b.QUARTILE = @SEGMENT
97group by a.INDID,orddate;
98
99
100IF OBJECT_ID ('tempdb..#orders_orbits') is not null drop table #orders_orbits;
101select a.*,
102datediff(day,b.orddate,a.orddate) as days_since_last,
103(b.GROSREPORT - a.GROSREPORT) as dollar_diff,
104(b.dolper_items - a.dolper_items) as aur_diff
105into #orders_orbits
106from #orders a inner join #orders b
107on a.INDID=b.INDID
108where a.rank = b.rank + 1
109order by INDID, orddate;
110
111
112IF OBJECT_ID ('tempdb..#order_quartiles') is not null drop table #order_quartiles;
113select distinct days_since_last, ntile(4) over (order by days_since_last ) as QUARTILE
114into #order_quartiles
115from #orders_orbits order by days_since_last;
116
117declare @quartile1 int
118declare @quartile2 int
119declare @quartile3 int
120declare @quartile4 int
121
122set @quartile1 = (select MAX(days_since_last) from #order_quartiles where QUARTILE = 1);
123set @quartile2 = (select MAX(days_since_last) from #order_quartiles where QUARTILE = 2);
124set @quartile3 = (select MAX(days_since_last) from #order_quartiles where QUARTILE = 3);
125set @quartile4 = (select MAX(days_since_last) from #order_quartiles where QUARTILE = 4);
126
127select 'QUARTILE 1', @quartile1;
128select 'QUARTILE 2', @quartile2;
129select 'QUARTILE 3', @quartile3;
130select 'QUARTILE 4', @quartile4;
131
132IF OBJECT_ID ('tempdb..#orders_orbits_att') is not null drop table #orders_orbits_att;
133select a.*,
134(case when days_since_last <= @quartile1 then 'H' ---------> HIGH
135 WHEN days_since_last >@quartile1 and days_since_last <= @quartile2 then 'M' ---------> MEDIUM
136 WHEN days_since_last >@quartile2 and days_since_last <= @quartile3 then 'L' ---------> LOW
137 WHEN days_since_last >@quartile3 then 'T' ---------> TINY
138 ELSE 'UNKNOWN'
139 END) as SIZE,
140 (case when dollar_diff < -50 then 'D'
141 WHEN dollar_diff >= -50 and dollar_diff <= 50 then 'S'
142 WHEN dollar_diff >50 then 'U'
143 ELSE 'UNKNOWN'
144 END) as SPEND,
145 (case when aur_diff < -50 then 'D'
146 WHEN aur_diff >= -50 and aur_diff <= 50 then 'S'
147 WHEN aur_diff >50 then 'U'
148 ELSE 'UNKNOWN'
149 END) as AUR
150into #orders_orbits_att
151from #orders_orbits a
152order by rank;
153
154
155IF OBJECT_ID ('tempdb..#sequences') is not null drop table #sequences;
156select a.indid,
157 stuff((select ','+b.SIZE
158 from #orders_orbits_att as b
159 where a.indid = b.indid order by rank desc
160 for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '') as freq_seq
161 into #sequences
162from #orders_orbits_att a
163group by a.indid;
164
165IF OBJECT_ID ('tempdb..#GRAVITY') is not null drop table #GRAVITY;
166SELECT INDID,(LEN(freq_seq) - LEN(REPLACE(freq_seq,'H','')))/COALESCE(NULLIF(LEN('H'), 0), 1) as HIGH, -----> PROTECT DIVISION FROM ZERO
167(LEN(freq_seq) - LEN(REPLACE(freq_seq,'M','')))/COALESCE(NULLIF(LEN('M'), 0), 1) as MEDIUM,
168(LEN(freq_seq) - LEN(REPLACE(freq_seq,'L','')))/COALESCE(NULLIF(LEN('L'), 0), 1) as LOW,
169(LEN(freq_seq) - LEN(REPLACE(freq_seq,'T','')))/COALESCE(NULLIF(LEN('T'), 0), 1) as TINY
170INTO #GRAVITY
171FROM #sequences;
172
173
174IF OBJECT_ID ('tempdb..#orders_2nd_Window') is not null drop table #orders_2nd_Window;
175select
176indid,orddate,sum(grosreport) as grosreport,sum(orditemqty) as orditemqty,sum(grosreport)/SUM(orditemqty) as dolper_items,
177 ROW_NUMBER () over (partition by indid order by orddate asc) as rank
178into #orders_2nd_Window
179from TALBOTS_BASE..ORDERS
180where ORDDATE >= @2nd_orbit_start and ORDDATE <= @2nd_orbit_end
181and GROSREPORT > 0
182and COUNTRYFLG = 'US'
183and SUBCHANNEL not in ('RETAILOUTLET','UPSCALEOUTLET')
184and INDID is not null
185group by INDID,orddate;
186
187
188
189IF OBJECT_ID ('tempdb..#orders_orbits_2nd_Window') is not null drop table #orders_orbits_2nd_Window;
190select a.*,
191datediff(day,b.orddate,a.orddate) as days_since_last,
192(b.GROSREPORT - a.GROSREPORT) as dollar_diff,
193(b.dolper_items - a.dolper_items) as aur_diff
194into #orders_orbits_2nd_Window
195from #orders_2nd_Window a inner join #orders_2nd_Window b
196on a.INDID=b.INDID
197where a.rank = b.rank +1
198order by INDID, orddate;
199
200
201IF OBJECT_ID ('tempdb..#orders_orbits_att_2nd_Window') is not null drop table #orders_orbits_att_2nd_Window;
202select a.*,
203(case when days_since_last <= @quartile1 then 'H'
204 WHEN days_since_last >@quartile1 and days_since_last <= @quartile2 then 'M'
205 WHEN days_since_last >@quartile2 and days_since_last <= @quartile3 then 'L'
206 WHEN days_since_last >@quartile3 then 'T'
207 ELSE 'UNKNOWN'
208 END) as SIZE,
209 (case when dollar_diff < -50 then 'D'
210 WHEN dollar_diff >= -50 and dollar_diff <= 50 then 'S'
211 WHEN dollar_diff >50 then 'U'
212 ELSE 'UNKNOWN'
213 END) as SPEND,
214 (case when aur_diff < -50 then 'D'
215 WHEN aur_diff >= -50 and aur_diff <= 50 then 'S'
216 WHEN aur_diff >50 then 'U'
217 ELSE 'UNKNOWN'
218 END) as AUR
219into #orders_orbits_att_2nd_Window
220from #orders_orbits_2nd_Window a
221order by rank;
222
223
224
225IF OBJECT_ID ('tempdb..#sequences_2nd_Window') is not null drop table #sequences_2nd_Window;
226select a.indid,
227 stuff((select ','+b.SIZE
228 from #orders_orbits_att_2nd_Window as b
229 where a.indid = b.indid order by rank desc
230 for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '') as freq_seq
231 into #sequences_2nd_Window
232from #orders_orbits_att_2nd_Window a
233group by a.indid;
234
235
236IF OBJECT_ID ('tempdb..#GRAVITY_2nd_Window') is not null drop table #GRAVITY_2nd_Window;
237SELECT INDID,(LEN(freq_seq) - LEN(REPLACE(freq_seq,'H','')))/COALESCE(NULLIF(LEN('H'), 0), 1) as HIGH,--protect division from zero
238(LEN(freq_seq) - LEN(REPLACE(freq_seq,'M','')))/COALESCE(NULLIF(LEN('M'), 0), 1) as MEDIUM,
239(LEN(freq_seq) - LEN(REPLACE(freq_seq,'L','')))/COALESCE(NULLIF(LEN('L'), 0), 1) as LOW,
240(LEN(freq_seq) - LEN(REPLACE(freq_seq,'L','')))/COALESCE(NULLIF(LEN('L'), 0), 1) as OUTERSPACE
241INTO #GRAVITY_2nd_Window
242FROM #sequences_2nd_Window;
243
244
245
246IF OBJECT_ID ('tempdb..#GRAVITY_VARIANCE') is not null drop table #GRAVITY_VARIANCE;
247select a.INDID ,b.HIGH-a.HIGH as high_veloc,
248 b.MEDIUM-a.MEDIUM as medium_veloc,
249 b.LOW-a.LOW as low_veloc
250 INTO #GRAVITY_VARIANCE
251from #GRAVITY a inner join #GRAVITY_2nd_Window b on a.indid = b.indid;
252
253
254
255IF OBJECT_ID ('tempdb..#GRAVITY_WARNING') is not null drop table #GRAVITY_WARNING;
256select distinct indid,high_veloc,medium_veloc,low_veloc,
257(CASE WHEN medium_veloc < -@standard_dev*(select STDEV (medium_veloc) from #GRAVITY_VARIANCE)
258 and high_veloc <= 0 then 'IN DANGER'
259
260 WHEN high_veloc < -@standard_dev*(select STDEV (high_veloc) from #GRAVITY_VARIANCE)
261 and medium_veloc < (select STDEV (medium_veloc) from #GRAVITY_VARIANCE) then 'IN DANGER'
262
263 WHEN low_veloc < -@standard_dev*(select STDEV (low_veloc) from #GRAVITY_VARIANCE)
264 and medium_veloc <= 0 and high_veloc <= 0 then 'IN DANGER'
265
266 ELSE 'NO DANGER'
267 end) as warning
268 INTO #GRAVITY_WARNING
269from #GRAVITY_VARIANCE;
270
271
272CREATE INDEX #INDEX1 ON #GRAVITY_WARNING (INDID);
273
274
275IF OBJECT_ID ('tempdb..#CRIT_CUSTS_REQ_INTERV') is not null drop table #CRIT_CUSTS_REQ_INTERV;
276SELECT
277distinct a.INDID,
278b.EMAILFLAG,
279b.EMAIL_ACTIVE,
280b.MAILABLE,
281b.CHANNEL12M,
282b.LSUBCHANEL,
283b.ldate
284INTO #CRIT_CUSTS_REQ_INTERV
285FROM
286#GRAVITY_WARNING a
287inner join
288TALBOTS_CAMP..DIRECTMAIL_IND b on a.INDID = b.INDID
289where a.WARNING = 'IN DANGER';
290
291IF OBJECT_ID ('tempdb..#orders_warning_1st_window') is not null drop table #orders_warning_1st_window;
292select
293a.indid,
294b.warning ,
295count(distinct(a.ordno)) as orders,
296sum(a.ORDITEMQTY) as items,
297sum(a.grosreport) as grosreport,
298SUM(a.GROSREPORT)/COUNT(distinct(a.ordno)) as AOV,
299sum(a.GROSREPORT)/SUM(a.orditemqty) as AUR,
300count(distinct(a.ordno)) as freq,
301SUM(a.orditemqty)/COUNT(distinct(a.ordno)) as UPT
302into #orders_warning_1st_window
303from TALBOTS_BASE..ORDERS a
304inner join #GRAVITY_WARNING b
305on a.INDID = b.INDID
306where ORDDATE >= @orbit_start and ORDDATE <= @orbit_end
307and GROSREPORT > 0
308and a.INDID is not null
309and COUNTRYFLG = 'US'
310and SUBCHANNEL not in ('RETAILOUTLET','UPSCALEOUTLET')
311group by a.INDID,b.warning;
312
313
314
315IF OBJECT_ID ('tempdb..#orders_warning_2nd_window') is not null drop table #orders_warning_2nd_window;
316select
317a.indid,
318b.warning ,
319count(distinct(a.ordno)) as orders,
320sum(a.ORDITEMQTY) as items,
321sum(a.grosreport) as grosreport,
322SUM(a.GROSREPORT)/COUNT(distinct(a.ordno)) as AOV,
323sum(a.GROSREPORT)/SUM(a.orditemqty) as AUR,
324count(distinct(a.ordno)) as freq,
325SUM(a.orditemqty)/COUNT(distinct(a.ordno)) as UPT
326into #orders_warning_2nd_window
327from TALBOTS_BASE..ORDERS a
328inner join #GRAVITY_WARNING b
329on a.INDID = b.INDID
330where ORDDATE >= @2nd_orbit_start and ORDDATE <= @2nd_orbit_end
331and GROSREPORT > 0
332and a.INDID is not null
333and COUNTRYFLG = 'US'
334and SUBCHANNEL not in ('RETAILOUTLET','UPSCALEOUTLET')
335group by a.INDID,b.warning;
336
337
338IF OBJECT_ID ('tempdb..#net_change_warning') is not null drop table #net_change_warning;
339select a.indid,b.grosreport-a.grosreport as spend_change, b.freq-a.freq as freq_change
340into #net_change_warning
341from #orders_warning_1st_window a inner join #orders_warning_2nd_window b on a.INDID = b.INDID where b.warning = 'In Danger';
342
343
344IF OBJECT_ID ('tempdb..#HIGH_DANGER') is not null drop table #HIGH_DANGER;
345select a.INDID,
346SUM(a.grosreport) as spend,
347COUNT(distinct(a.ORDDATE)) as freq,
348case when SUM(a.grosreport) < 100 then '0-$99'
349 when SUM(a.grosreport) >= 100 and SUM(a.grosreport) < 200 then '$100-$199'
350 when SUM(a.grosreport) >= 200 and SUM(a.grosreport) < 500 then '$200-$499'
351 when SUM(a.grosreport) >= 500 and SUM(a.grosreport) < 800 then '$500-$799'
352 when SUM(a.grosreport) >= 800 and SUM(a.grosreport) < 1000 then '$800-$999'
353 when SUM(a.grosreport) >= 1000 then '$1000+'
354 else 'unknown'
355end as spend_tier,
356d.EMAILFLAG,
357d.EMAIL_ACTIVE,
358d.MAILABLE,
359d.CHANNEL12M,
360d.LSUBCHANEL,
361d.ldate
362into #HIGH_DANGER
363from #orders_orbits a
364left join TALBOTS_CAMP..DIRECTMAIL_IND d on a.INDID = d.INDID
365where not exists (select 'x' from #orders_orbits_2nd_Window b where a.INDID = b.INDID)
366group by a.indid,d.EMAILFLAG,d.EMAIL_ACTIVE,d.MAILABLE,d.CHANNEL12M,d.LSUBCHANEL,d.LDATE;
367
368
369IF OBJECT_ID ('tempdb..#single_orderers') is not null drop table #single_orderers;
370 select distinct(indid)
371 into #single_orderers
372 from #orders a
373 where not exists (select 'x' from #orders_orbits b where a.INDID = b.indid);
374
375IF OBJECT_ID ('tempdb..#orders_single_orderers_1st_window') is not null drop table #orders_single_orderers_1st_window;
376select
377a.indid,
378count(distinct(a.ordno)) as orders,
379sum(a.ORDITEMQTY) as items,
380sum(a.grosreport) as grosreport,
381SUM(a.GROSREPORT)/COUNT(distinct(a.ordno)) as AOV,
382sum(a.GROSREPORT)/SUM(a.orditemqty) as AUR,
383count(distinct(a.ordno)) as freq,
384SUM(a.orditemqty)/COUNT(distinct(a.ordno)) as UPT
385into #orders_single_orderers_1st_window
386from TALBOTS_BASE..ORDERS a
387inner join #single_orderers b
388on a.INDID = b.INDID
389where ORDDATE >= @orbit_start and ORDDATE <= @orbit_end
390and GROSREPORT > 0
391and COUNTRYFLG = 'US'
392and SUBCHANNEL not in ('RETAILOUTLET','UPSCALEOUTLET')
393and a.INDID is not null
394group by a.INDID;
395
396
397
398IF OBJECT_ID ('tempdb..#orders_single_orderers_2nd_window') is not null drop table #orders_single_orderers_2nd_window;
399select
400a.indid,
401count(distinct(a.ordno)) as orders,
402sum(a.ORDITEMQTY) as items,
403sum(a.grosreport) as grosreport,
404SUM(a.GROSREPORT)/COUNT(distinct(a.ordno)) as AOV,
405sum(a.GROSREPORT)/SUM(a.orditemqty) as AUR,
406count(distinct(a.ordno)) as freq,
407SUM(a.orditemqty)/COUNT(distinct(a.ordno)) as UPT
408into #orders_single_orderers_2nd_window
409from TALBOTS_BASE..ORDERS a
410inner join #single_orderers b
411on a.INDID = b.INDID
412where ORDDATE >= @2nd_orbit_start and ORDDATE <= @2nd_orbit_end
413and GROSREPORT > 0
414and COUNTRYFLG = 'US'
415and SUBCHANNEL not in ('RETAILOUTLET','UPSCALEOUTLET')
416and a.INDID is not null
417group by a.INDID;
418
419 IF OBJECT_ID ('tempdb..#orders_single_orderers_1st_window_and_also_2nd') is not null drop table #orders_single_orderers_1st_window_and_also_2nd;
420select
421a.indid,
422count(distinct(a.ordno)) as orders,
423sum(a.ORDITEMQTY) as items,
424sum(a.grosreport) as grosreport,
425SUM(a.GROSREPORT)/COUNT(distinct(a.ordno)) as AOV,
426sum(a.GROSREPORT)/SUM(a.orditemqty) as AUR,
427count(distinct(a.ordno)) as freq,
428SUM(a.orditemqty)/COUNT(distinct(a.ordno)) as UPT
429into #orders_single_orderers_1st_window_and_also_2nd
430from TALBOTS_BASE..ORDERS a
431inner join #single_orderers b
432on a.INDID = b.INDID
433where ORDDATE >= @orbit_start and ORDDATE <= @orbit_end
434and GROSREPORT > 0
435and COUNTRYFLG = 'US'
436and a.INDID is not null
437and SUBCHANNEL not in ('RETAILOUTLET','UPSCALEOUTLET')
438and exists (select 'x' from #orders_single_orderers_2nd_window c where a.INDID = c.INDID)
439 group by a.INDID;
440
441--Rest of the procedure
442.
443.
444END