Counting new and exsting customer customers by month

[#1]

Hi,

I need to flag the new customers and existing customers. If customers places an order for the first time he is new customer. If customer already placed order he is new customer. Need to display report by monthly to show new and existing customers in that month. How can I compare every record loaded with previously loaded records and flag the new record? Appreciate your help.

[Jason_Michaelides]

To get you started...

Add a field and a sort order to your fact table load script:

LOAD

   *,

   If(CustomerID=Previous(CustomerID),0,1).  AS FirstOrderFlag

FROM DataSource

ORDER BY CustomerID, OrderDate;

Now, assuming you have a Customer_Counter field in your customer table (1 AS Customer_Counter on every record) you can create a chart that has OrderMonth as the dimension and the expression:

Sum({<FirstOrderFlag={1} Customer_Counter)

will give you the number of new customers per month.

Sum({<FirstOrderFlag={0} Customer_Counter)

will give you the number of returning customers per month.

I think (it's late!)

Hope this helps,

Jason

Message was edited by: Jason Michaelides I missed a parenthesis in the IF statement - now corrected.  Thanks to paulyeo for pointing it out.

[#3]

I would modify my SQL import query to be something like this:

Select               CustomerID,

                         RecordDate,

                         etc....,

                        case when CustomerID in (select distinct CustomerID from YourTable where max(RecordDate) < FirstDayCurrentMonth) then 'N' else 'Y' end as IsNewCustomer,

                         etc...

from                    YourTable

I'm too lazy to write out the exact SQL syntax but I trust you will get the general idea. If you need the exact code just let me know.