1 2 Previous Next 17 Replies Latest reply on Aug 24, 2016 8:45 PM by deepesh.moad

# Need help in finding 1st purchase,2nd purchase..... of a customer!

Hi Guys,

I need help in finding 1st purchase,2ndpurchase,3rd purchase......till more than 6th purchase of unique customers.
I need something like below table structure.

I am trying with the help of index function but unable to create a count of customers.

I am attaching the workbook with the sample data.

A Customer might buy the selected product more than once per week. If a customer buys a product 2 times in the same week and never before it should be counted only

as his 2nd purchase in this week and not as his 1st purchase.

Thanks a ton !

Deepesh

• ###### 1. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

Any help or thoughts will greatly be appreciated.

• ###### 2. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

Deepesh ,

Are these the numbers you are looking for ?

see in the attached...

Michel

1 of 1 people found this helpful
• ###### 3. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

See this one.

• ###### 4. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

Hi.

I was doing at the same time and i didn't notice sales receipt. Your way seems smarter to me, but i have one doubt:

Counting sales receipt aren't you counting orders, not customer + products? Can't one order have more than one product?

• ###### 5. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

Luciano,

If we look at Deepesh   "Purchase Order"  calculation,  it seems that the count of receipts determine the count of sales.

Next, if we want to know for a specific month , how many receipts the customer reached since the beginning,  we need to compute the running sum.

Then for the  Xst  purchase, we check for a specific month how many customers had a running sum = X.

In your calculation you only count the sum of Customer-product  for that specific month.

Michel

• ###### 6. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

Thank you, Michel and Luciano, for the response.

Thanks a lot Michel for the solution.

I appreciate your time and effort to help me out.

• ###### 7. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

My calculation was wrong. It wasn't aggregating by week. I understood your idea but i still think a receipt could have more than one customer+product+week key and fail one day since it's not necessarily an unique key.

For this data is working fine.

Regards.

• ###### 8. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

Hi Michel & Luciano,

Just a small help again.

Is there any way to show the cumulative unique customers, like the below screenshot.

I am using Michel's solution and it is working fine, but I am not able to do cumulative summation (across week) with the help of that.

I would really appreciate your help.

Thank you so much.

Deepesh

• ###### 9. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

Hi.

I tried to do in Michael's version but i couldn't show cumulative value there.

I built another one, but i'm counting using this as key to identify unique order: STR([Sales Customer]) + '|' + [Sales Product] + '|' + STR( [Sales Week] )

• ###### 10. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

Hi Luciano,

Thank you for the reply.

There is one condition to show the cumulative figures.

Each Customer should only be counted once in each week. E.g. If a Customer has a 1st purchase in week 1 and a 2nd purchase in week 2, the customer will be counted in  1st Purchase in Week 1, but will be removed from the cumulative Sum  in Week 2.

In Week 2 this customer would be included in  2nd Purchase.Attaching the example.

Thanks a ton

• ###### 11. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

I believe this rule is already achieved in this last one.

• ###### 12. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

Hi,

Below is the expected output.

• ###### 13. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

They will be included in 2nd purchase only in 201502 and 28045152/29764878 left 2nd purchase in 201502.

Calculation is done by week, not by the whole.

This is what you ask since beginning.

Requirements changed?

• ###### 14. Re: Need help in finding 1st purchase,2nd purchase..... of a customer!

No, the requirement did not change.

We are reporting cumulative figures here.

Customers 14297350 and 36530604 are already present in the 1st purchase , therefore in 2nd week they should come in 2nd purchase.
But as per our report they are coming in the first purchase itself.

1 2 Previous Next