Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Analytical Help - Customer Lifetime Value


Analytical Help - Customer Lifetime Value

Author
Message
scottsanpedro
scottsanpedro
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 332
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



J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3456 Visits: 33048
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

HanShi
HanShi
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3051 Visits: 3633
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/

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
scottsanpedro
scottsanpedro
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 332
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.

Year Q Initial # Q1 Q2 Q3 Q4 Q5 Q6
2013 4 $239 171
2013 3 $338 198 48
2013 2 $268 224 15 15
2012 4 $203 160 8 6 11
2012 3 $229 206 30 8 4 6
2012 2 $273 202 14 8 9 5 12
2012 1 $254 322 24 10 17 10 22 15

-----
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



scottsanpedro
scottsanpedro
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 332
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search