Counting new customers per month

Question

For some reason I was embarrassed because of this.

Basically, I'm looking for a query that will find the number of new customers per month since 2010.

I have a customer’s email address (email), all placed orders (OrderID) and the date on which it was placed (OrderDate). TblOrder table.

I know that the “new customer” is: (a) one who has never ordered before the date / month, and (b) who has at least one order after the date / month

I would like the end to be the best, with a simpler way:

01 02 03 04 05 06 07 08 09 10 11 12 2010 ## ## ## ## ## ## ## ## ## ## ## ## 2011 ## ## ## ## ## ## ## ## ## ## ## ## 2012 ## ## ## ## ## ## ## ## ## ## ## ## 

---

And they gave me this to work, but guys, I'm seriously not a programmer, and it may look just for some of you, but it’s above my head and doesn’t click on me at all.

 SELECT <customer info> FROM <customer table> WHERE (SELECT COUNT(<order info>) FROM <order table> WHERE <customer info> = <current customer> AND <date> < <target date>) = 0 AND (SELECT COUNT(<order info> FROM <order table> WHERE <customer info> = <current customer> AND <date> > <target date>) > 0 

I know this is also invalid SQL. Therefore, I do not know what to do with it. And I think that he simply picks up a list of applicable customers (that is, those who did not order before the entered month), and does not count them all and summarize them as I ultimately want.

Answer 1

Try:

 select yr, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] from (select datepart(month,minDate) mth, datepart(year,minDate) yr, count(*) cnt from (select min(OrderDate) minDate, max(OrderDate) maxDate from tblOrder group by email) sq where datediff(month, minDate, maxDate) > 0 group by datepart(month,minDate), datepart(year,minDate)) src PIVOT (max(cnt) for mth in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) ) pvt 

SQLFiddle here .

Answer 2

Start by defining a new customer

 select distinct FirstTimer.customer_id from (select customer_id,min(order_date) as FirstOrderDate from tblOrder group by customer_id having Month(min(order_date))=month(TargetDate) and year(min(order_date))=year(targetDate) ) FirstTimer join tblOrder ot on ot.customer_id=First_timer.customer_id where ot.order_date > target_date 

The first part lists all customers whose first order was indicated in the specified month. Then you only need the client that ALSO satisfies the second condition (ordered after the specified date)

Without the names of tables and structures, cannot create the whole query, but I hope that the above should give you a little start

Answer 3

This will give you a new number of customers by year and month:

 ;WITH cteCustFirstMonth As ( -- Gets the first order date for each active customer SELECT email, MIN(OrderDate) as FirstOrderDate, YEAR(MIN(OrderDate)) as Yr, MONTH(MIN(OrderDate)) as Mnth FROM tblOrder GROUP BY email ) -- Groups the first order dates into Year and Month SELECT Yr, Mnth, COUNT(email) As cnt FROM cteFirstOrderDate GROUP BY Yr, Mnth ORDER BY Yr, Mnth 

It does not return them in the same format that you requested them, but it can be done in excel or SQL with a little extra work.

Answer 4

Minor editing of @Mark Bannister's answer. OP announced its intention to record the number of new customers by the month in which they placed their first order. The OP definition of a “new customer” is confused in that it indicates:

"new customer": (a) one who has never ordered before date / month; and (b) who has at least one order after date / month (allocation added)

Perhaps this is really what the OP wants, but it can more accurately be called a “new customer who will become a repeat customer in the coming month”, as it eliminates:

• Customers who place only one order
• Customers who place all their orders on the same date / month

The @Mark solution correctly (?) Eliminates these clients as its SQL FIDDLE indicates that Fred @home is NOT considered a new client.

However, the OP also states at the end of its report that the “applicable customers” are:

those who did not order before the entered month

Thus, in order to capture ALL new customers (this is what I think OP is really worth it), we can still use the @Mark nice PIVOT app, and all we need to do is look at min (OrderDate) and just ignore maxDate comparison as below:

 select yr, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] from (select datepart(month,minDate) mth, datepart(year,minDate) yr, count(*) cnt from (select min(OrderDate) minDate from tblOrder group by email) sq group by datepart(month,minDate), datepart(year,minDate)) src PIVOT (max(cnt) for mth in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) ) pvt 

A quick check to make sure all clients are captured can be done by summing up all the calculations in the pivot table (quickly and easily in Excel) and comparing with:

 SELECT COUNT(DISTINCT(email)) FROM tblOrder 

Answer 5

 SELECT EXTRACT(MONTH FROM orderdate) AS month, EXTRACT(YEAR FROM orderdate) AS year, COUNT(*) FROM ( SELECT MIN(orderdate) AS orderdate, name FROM tblOrder GROUP BY name ) GROUP BY EXTRACT(MONTH FROM orderdate), EXTRACT(YEAR FROM orderdate)