My company has come up with the start of an interesting list cleaning excercise.
Marketers and accountants being the way they are would not like to lose anyone from the list – except the people who never purchase or did anything.
Ok Sure! 😐
What we aggreed on is to use groups…
We’re breaking the list up into a safe list (new subscribers) and then grouping 6 month groups for 5+ years. Everyone else are the members we want to segment for the cleaning.
We’ve this query very manually using the UNION operator. I’m looking for a more elegant way to do this. It would be nice if I could control the time groups (perhaps select 3 month groups for paid members) and it would be nice to easily change the safe list from 1 year to 6 months if I wanted to play around a bit.
Before coding we broke it down into steps (groups) and then just union’d them together and connected excel to it.
1st group
– anyone on the list, clicked or confirmed inside of one year AND never purchased.
2nd group
– Purchased 0-6 months
3rd group
– Purchased 6-12 months
4th group
– Purchased 12-18 months
5th group
– Purchased 18-24 months
6th group
– Purchased 24-30 months
7th group
– Purchased 30-36 months
8th group
– Purchased 36-42 months
9th group
– Purchased 42-48 months
10th group
– Purchased 48-54 months
11th group
– Purchased 54-60 months
11th group
– Purchased 60+ months
12th group (aka – the ‘crap’ list)
– never purchased, never clicked, never confirmed, older than 1 year
—————
Here’s the code
select
list_
, ADV_ctm_nbr
, ’01.ActiveInLastYearUnpaid’ as [Segment]
from
members_
where
membertype_ = ‘normal’
and
(
DateJoined_ > getdate() – 365 or
FSP_Last_click > getdate() – 365 or
(ConfirmDat_ > getdate() – 14 and confirmDat_ is not null) –issue here
)
and FSP_dt_lastbuy is null
union all
select list_, ADV_ctm_nbr, ’02.Bought 0-6months’ as [Segment] from members_ where membertype_ = ‘normal’ and
FSP_dt_lastbuy > getdate() – 365.0/2
union all
select list_, ADV_ctm_nbr, ’03.Bought 6-12months’ as [Segment] from members_ where membertype_ = ‘normal’ and
FSP_dt_lastbuy > getdate() – 365 and FSP_dt_lastbuy <= getdate() - 365.0/2 union all select list_, ADV_ctm_nbr, '04.Bought 12-18months' as [Segment] from members_ where membertype_ = 'normal' and FSP_dt_lastbuy > getdate() – 365*(3.0/2.0) and FSP_dt_lastbuy <= getdate() - 365.0 union all select list_, ADV_ctm_nbr, '05.Bought 18-24months' as [Segment] from members_ where membertype_ = 'normal' and FSP_dt_lastbuy > getdate() – 365*2 and FSP_dt_lastbuy <= getdate() - 365.0 *(3.0/2.0) union all select list_, ADV_ctm_nbr, '06.Bought 24-30months' as [Segment] from members_ where membertype_ = 'normal' and FSP_dt_lastbuy > getdate() – 365*(5.0/2.0) and FSP_dt_lastbuy <= getdate() - 365.0 *2.0 union all select list_, ADV_ctm_nbr, '07.Bought 30-36months' as [Segment] from members_ where membertype_ = 'normal' and FSP_dt_lastbuy > getdate() – 365*3 and FSP_dt_lastbuy <= getdate() - 365.0 *(5.0/2.0) union all select list_, ADV_ctm_nbr, '08.Bought 36-42months' as [Segment] from members_ where membertype_ = 'normal' and FSP_dt_lastbuy > getdate() – 365*(7.0/2.0) and FSP_dt_lastbuy <= getdate() - 365.0 *3.0 union all select list_, ADV_ctm_nbr, '09.Bought 42-48months' as [Segment] from members_ where membertype_ = 'normal' and FSP_dt_lastbuy > getdate() – 365*4 and FSP_dt_lastbuy <= getdate() - 365.0 *(7.0/2.0) union all select list_, ADV_ctm_nbr, '10.Bought 48-54months' as [Segment] from members_ where membertype_ = 'normal' and FSP_dt_lastbuy > getdate() – 365*(9.0/2.0) and FSP_dt_lastbuy <= getdate() - 365.0 *4.0 union all select list_, ADV_ctm_nbr, '11.Bought 54-60months' as [Segment] from members_ where membertype_ = 'normal' and FSP_dt_lastbuy > getdate() – 365*5 and FSP_dt_lastbuy <= getdate() - 365.0 *(9.0/2.0) union all select list_, ADV_ctm_nbr, '12.Bought 60months+' as [Segment] from members_ where membertype_ = 'normal' and FSP_dt_lastbuy <= getdate() - 365.0 *5.0 union all select list_, ADV_ctm_nbr, '13.NeverConfirmedNeverBought' as [Segment] from members_ where membertype_ = 'normal' and FSP_dt_lastbuy is null and (DateJoined_ < getdate() - 365 or DateJoined_ IS NULL) AND (FSP_Last_click < getdate() - 365 OR FSP_Last_click IS NULL ) AND (ConfirmDat_ < getdate() - 14 OR confirmDat_ is null) --------------- (It comes out in a nice bar chart in excel - import data:piviot table graph wizard - showing the groups in the order given in the groups. The last bar is the list to be segmented for list cleaning.) Any insightful help would be most welcome! I'm looking for a real SQL Guru or anyone who enjoys a really good T-SQL puzzle. Thanks VERY much for your thoughts! dg