13 Replies Latest reply on Sep 6, 2018 10:17 AM by Kat Merriman

# How to find New Customers and Returning Customers?

Hello all,

I've been struggling with some calculations for a few days now and decided to finally ask for help. I tried to look up other topics in the community that were similar, however they all failed to accomplish what I'm after.

Appreciate any guidance you can lend.

The Goal

Obtain a customer list that can be filtered or viewed in the following ways.

Customer Category:

New Customer= Never purchased from us before except within 12 months previous to the current date.

Lost Customer= Has not made a purchase in a 9 month period

Re-activation = Follows the same rules as “Lost customer” with addition to making a purchase greater than or equal to “1,000\$” within 12 months previous to the current date.

I have been following a few links, but still cannot achieve this.

My Journey So Far

I've been able to get pretty close to finding new customers by combining these two calculations.

Date Calc:

New Customer Rule:

IF MIN([Order Date]) < [Date Calc] THEN '0'

ELSEIF MIN([Order Date])> [Date Calc] THEN '1'

END

Then I run into this issue. (I also attached the photo)

Any advice with the next steps I should take here?

Thanks!

• ###### 1. Re: How to find New Customers and Returning Customers?

Simply removing the order date field from view marks each customer appropriately with a 0 or a 1, thus showing you who is new and who isn't. Is that what you need?

1 of 1 people found this helpful
• ###### 2. Re: How to find New Customers and Returning Customers?

Kat,

Thanks for the suggestion.

That would not resolve the issue unfortunately.

I need to see only customers who would equal a "1"

In other words, they have NEVER had an order date previous to the Date parameter.

Hope this helps.

• ###### 3. Re: How to find New Customers and Returning Customers?

Sure, just add a filter to remove the 0s.

1 of 1 people found this helpful
• ###### 4. Re: How to find New Customers and Returning Customers?

I wish =)

The reason that wont work is because they would not be a true new customer.

Example.

Aaron Bergman has placed 2 orders before 8/30/17 and 1 order after 8/30/17. Therefore he is not a new customer. Filtering the '0' will show him as a new customer when he truly isn't because he has 2 other orders that are hidden from the view.

If Aaron Bergman only placed 1 order after 8/30/17 in total company history. He would then be a true new customer.

• ###### 5. Re: How to find New Customers and Returning Customers?

No, the only reason he would be showing up after the filter is if you had the dates in the view.  When the dates are in the view the calculation puts that date as the MIN, if you remove them, it looks at the min of all orders for that customer.  Remove date from view and filter to remove 0s and you will see a list of only new customers.  I promise you Aaron Bergman will no longer be in the view.

1 of 1 people found this helpful
• ###### 6. Re: How to find New Customers and Returning Customers?

Interesting,

Thanks for that. Its aggravating that it was so simple.

Now to figure out how to calculate the other 2 categories.

Any tips there?

• ###### 7. Re: How to find New Customers and Returning Customers?

Lost customers are just the opposite of new customer and instead of setting the number of months to 12, you set it to 9. Looks like you are on the right track.  I'd do 2 formulas for re-activation and then if they are both true the whole field is true.  The only thing I'm not certain on is why you need the date parameter.  Is the end user going to be selecting the number of months that denotes a "new" customer?

• ###### 8. Re: How to find New Customers and Returning Customers?

I'll give it a shot,

To answer your question regarding the parameter, we are not sure if we want to set lost customers to equal 9 months or 12 months with no purchase. This will give us the ability to play with the data.

• ###### 9. Re: How to find New Customers and Returning Customers?

Do you think I may have approached this incorrectly?

One of things we want to analyze is the order dates along with the new customer names.

I guess this inst possible since adding the order date to the view skews the results?

• ###### 10. Re: How to find New Customers and Returning Customers?

What is the end result you're looking for? You can always create a calculated field for max(date) and min(date) to look at their first and last orders in the view.

Or you can create your list of customers and then create a separate table and a dashboard action so when you click on a customer you can see all their activity by date with other relevant information.  This won't let you see all the customer, but you'll be able to view data for 1 or several selected customers.

• ###### 11. Re: How to find New Customers and Returning Customers?

We want to know who are new customers are, when they placed orders, and the dollar amounts.

Same will be said for Lost customers and Re-activated customers.

I'm working on those parts now, just hoping I don't corrupt the view by accidentally showing false new customers. haha.

• ###### 12. Re: How to find New Customers and Returning Customers?

I'm having trouble coming up with the calculations for the reactivating customers. Ill give you a detailed explanation of what we need to see.

We need to see customers that used to purchase from us, and then went on a 9 month streak of not ordering at all.

THEN, if that customer made a purchase anytime after that 9 month streak AND made a purchase larger than 1,000\$, they are Reactivated.

• ###### 13. Re: How to find New Customers and Returning Customers?

I'm sorry.  I'm really not sure how to set that one up.  Let me know if you figure it out.