Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Analytical Help - Customer Lifetime Value Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2014 3:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:52 AM
Points: 88, Visits: 329
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



Post #1590243
Posted Tuesday, July 8, 2014 3:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 1,943, Visits: 20,180
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
Post #1590259
Posted Tuesday, July 8, 2014 4:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:40 AM
Points: 2,480, Visits: 3,023
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’! **
Post #1590272
Posted Tuesday, July 8, 2014 4:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:52 AM
Points: 88, Visits: 329
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



Post #1590273
Posted Tuesday, July 8, 2014 7:12 AM This worked for the OP Answer marked as solution
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:52 AM
Points: 88, Visits: 329
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



Post #1590346
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse