Customer Visits - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Customer Visits

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-10-18 : 14:35:04

Hi,
I have a transactions table in my daTABASE which stores info on customer purchases e.g when they bought, what they bought, how much they spent etc. The customers are identified by a cust_id field and the date field is called order_date.
What I want to do is identify regular customers i.e find those customers who visit every 4 weeks, 8 weeks etc.
How can I do this?

Any info wouild be greatly appreciated.

Cheers

Paul

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-18 : 15:36:52

Depends what you really mean. How about a customer who visits 10 times in every 4 weeks misses 10 weeks then goes back to 10 times in 4 weeks. Another customer who visits once every 4 weeks.
The first will be more regular if you take the number of visits per week between first and last. The second if you take the max time between visits.

select distinct customer_id
from tbl t
where not exists
(
select * from tbl t2 where t2.customer_id = t.customer_id
and t2.dte = (select min(dte) from tbl t3 where t3.customer_id = t2.customer_id and t3.dte > t2.dte)
and datediff(dd,t.dte,t2.dte) > 28
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-10-18 : 20:59:43

Hi nr,

thanks for your post, I need to find customers who visit one or more times every 4 weeks, to be more definitive.

Cheers

Paul

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-18 : 21:35:32

That's what the query will do - make sure that there's not a 4 week gap between visits.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-10-19 : 14:26:49

Hi nr,

Thanks for the code, could you check it please as it is returning alot of customers, which is not what I would expect. I would expect to see more customers who only visit once or a few times and then never return.

Cheers

Paul

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-19 : 15:21:48

That gives you the customers with no 4 week gaps and will include customers with a single visit
Probably also need to exclude those without a visit in the last 4 weeks.

select distinct customer_id
from tbl t
where not exists
(
select * from tbl t2 where t2.customer_id = t.customer_id
and t2.dte = (select min(dte) from tbl t3 where t3.customer_id = t2.customer_id and t3.dte > t2.dte)
and datediff(dd,t.dte,t2.dte) > 28
)
and exists (select * tbl t4 where t4.customer_id = t.customer_id and datediff(dd,t4.dte,getdate()) <= 28)

(probably could be more efficient.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-10-19 : 15:48:32

Hi nr,

Thanks for that, just to make sure I'm clear on this, does this query identify those who don't visit every 4 weeks?

Cheers

Paul

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-19 : 15:55:59

No - it identifies those who do.
The first subquery makes sure there are no gaps of more than 4 weeks.
The second that the last visit was within 4 weeks.

oops noticed a mistake - the first subquery desn't do anything
select distinct customer_id
from tbl t
where not exists
(
select * from tbl t2 where t2.customer_id = t.customer_id
and t2.dte = (select min(dte) from tbl t3 where t3.customer_id = t2.customer_id and t3.dte > t.dte)
and datediff(dd,t.dte,t2.dte) > 28
)
and exists (select * tbl t4 where t4.customer_id = t.customer_id and datediff(dd,t4.dte,getdate()) <= 28)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources