Finding Regular Customers 1

[Rozza]

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. Each record records one instance of a purchase i.e if they came in the shop and bought two different products there would be two records recorded with the same date, but with different product_codes and prices.
What I want to do is identify regular customers i.e find those customers who visit once or more, every 4 weeks.
How can I do this?

Any info wouild be greatly appreciated.

Cheers

Paul

 

[SQLSister]

First you need to really define what you want, would someone who usually buys 6 to 8 items a month but skips a month be considered a regular customer? How many months at a time are you going to calculate on? A year's worth? A quarter's worth, from the beginning of time?

But breaking down what you said into steps, first you need to see how many times per month each customer buys a product. Then you need to eliminate the ones which
have a zero in any one of the totals. I used month instead of four weeks because it is much easier to manipulate data this way.

So let's do this based on one quarter's worth of data:

Select distinct a.CustomerID from
(Select CustomerID, Count(RecordID) where month(OrderDate) = 1 group by CustomerID
Union all
Select CustomerID, Count(RecordID) where month(OrderDate) = 2 group by CustomerID
Union all
Select CustomerID, Count(RecordID) where month(OrderDate) = 3 group by CustomerID) a
Where Count(a.CustomerID) = 3

 

[Rozza]

Hi SQLSister,

Thanks for your post. I've just tried your query, but when I check the syntax I get the following error:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Select'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

Also, If a customer buys two products on the same day will your query count this as two visits instead of one?

Cheers

Paul

 

[SQLSister]

Oops I forgot to specify the table to take the records from. Shouldn't post before I've had caffeine!

Select distinct a.CustomerID from
(Select CustomerID, Count(RecordID)from table1 where month(OrderDate) = 1 group by CustomerID
Union all
Select CustomerID, Count(RecordID) from table1 where month(OrderDate) = 2 group by CustomerID
Union all
Select CustomerID, Count(RecordID) from table1 where month(OrderDate) = 3 group by CustomerID) a
Where Count(a.CustomerID) = 3

Of course you substitute your actual table and field naems.

Yes it will count each record as a visit even if on the same date, but that's ok, becasue all you really wanted was the ones who ordered at least one part each month. Not sure you even need the count(recordID) parts of the statement now that I think about it.

Try this as well and see if you get the same results:

Select distinct a.CustomerID from
(Select CustomerID from table1 where month(OrderDate) = 1 group by CustomerID
Union all
Select CustomerID from table1 where month(OrderDate) = 2 group by CustomerID
Union all
Select CustomerID from table1 where month(OrderDate) = 3 group by CustomerID) a
Where Count(a.CustomerID) = 3

 

[Rozza]

Hi SQLSister,

Sorry to be a pain, but I have made the changes and substituted my table and field names, but I now get the following error:

Server: Msg 147, Level 15, State 1, Line 7
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


This error occurs for both of your queries.

Any Ideas?

Cheers

Paul

 

[SQLSister]

Try substituting Having for where in the last line.

 

[tlbroadbent]

Try this variation of SQLSister's code. It will tell you which customers made at least one purchase per month in January to March.

Select a.CustomerID from

(Select Distinct CustomerID from table1
where month(OrderDate) = 1

Union all

Select Distinct CustomerID from table1
where month(OrderDate) = 2

Union all

Select Distinct CustomerID from table1
where month(OrderDate) = 3) a

Group By CustomerID
Having Count(*) = 3


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[Rozza]

Thanks for that tblbroadbent!!

I just want to check though, if I wanted to do this over a greater time period, would I just add in:

"Select Distinct CustomerID from table1
where month(OrderDate) = 4
Union all"

For each additional month and then increase the:

"Having count(*) = 3"

by one for each additional month that I add?

Cheers

Paul

 

[SQLSister]

yep, that's what you'd do. Remember the month numbers are for specific months, 1 for Jan, 2 for feb etc. If you have multiple years in there, you may have to alter the code to also specify the year.

 

[Rozza]

Thanks!