July 8, 2014 at 3:17 am
Hi all, I have been given the job to create a query that will help show the lifetime value of customers.
Each set of customers will be grouped by first buy date and split into quarters. Each month a new result is appended to that row showing AVG value.
The reason for the question is just to get some pointers to any analytical already baked SQL 2012 T-SQL that could help me out.
Thanks in advance
Scott
July 8, 2014 at 3:49 am
what is the business definition of "lifetime value of customers"?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 8, 2014 at 4:10 am
It would be very helpfull if you can give us some sample data and the desired result.
See this link on how to post sample data: http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 8, 2014 at 4:11 am
I gather its a marketing tool.
So you get a group of customers who bought in Year 2010 Q1 and you track the same groups spent through the years.
2010 Q2 - £50 AVG
2010 Q3 - £30
2010 Q4 - £25
so each month a new group appears and you track them.
YearQInitial#Q1Q2Q3Q4Q5Q6
20134$239 171
20133$338 19848
20132$268 2241515
20124$203 1608611
20123$229 20630846
20122$273 2021489512
20121$254 322241017102215
-----
So far I am getting the accounts by year and quarter and think to use that data in a CTE??
select
o.[account_id]
,orderyear
,orderquarter
from
[dbo].[orders] o
inner join [dbo].[orderlines] ol on o.[order_id] = ol.[order_id]
cross apply(values(year(o.[ship_date]))) as a1(orderyear)
cross apply(values(DatePart(quarter,(o.[ship_date])))) as a2(orderquarter)
where
product_code like ('ll%') and ship_date = (select min(ship_date) from [dbo].[orders] mo where mo.[order_id] = ol.order_id)
and total_value>0 AND order_status NOT LIKE 'return%' AND order_status NOT LIKE 'cancel%' AND order_status NOT LIKE 'freeze%'
Thanks
July 8, 2014 at 7:12 am
If anyone is interested. I solved it by creating the following query. I will loop this through each quarter and populate another table.
with res
as
(
select
o.[account_id]
,orderyear
,orderquarter
from
[dbo].[orders] o
inner join [dbo].[orderlines] ol on o.[order_id] = ol.[order_id]
cross apply(values(year(o.[ship_date]))) as a1(orderyear)
cross apply(values(DatePart(quarter,(o.[ship_date])))) as a2(orderquarter)
where
product_code like ('ll%') and ship_date = (select min(ship_date) from [dbo].[orders] mo where [account_id] = o.account_id)
and total_value>0 AND order_status NOT LIKE 'return%' AND order_status NOT LIKE 'cancel%' AND order_status NOT LIKE 'freeze%' and
(SELECT CASE WHEN EXISTS(SELECT NULL FROM [FMTables].[dbo].[orderlines] WHERE order_id = (Select MIN(order_id) from [FMTables].[dbo].[orders] where account_id = o.account_id)
AND weborder_prod_code IN('LL/CS50','LL/CS510','LL/CS520','LL/CS530','LL/CS540','wls/cs540/3pack', 'LL/S12','LL/HW291N','LL/HW251N','LL/JABRA/9450 PRO FLEX'))
THEN 'Y' ELSE 'N' END) = 'Y' and
orderyear = 2013 and orderquarter = 3
)
select
'2013 Q3' as StartGroup
,(select count(*) from res) as totalStart
,datepart(year,[ship_date]) as [Year]
,datepart(q,[ship_date]) as [quarter]
,Sum([total_price]) / (select count(*) from res) as AvgSpend
from
[dbo].[orders] o
inner join [dbo].[orderlines] ol on o.[order_id] = ol.[order_id]
where
[account_id] in(select account_id from res) and [ship_date] is not null
group by
datepart(year,[ship_date])
,datepart(q,[ship_date])
order by
datepart(year,[ship_date])
,datepart(q,[ship_date]);
Thanks Scott
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply