8 Replies Latest reply on Jan 31, 2019 9:42 AM by Tijan Senghore

# # new customers

Deepak helped me work this out yesterday (see Calculating # of new customers) but I have a part that I omitted in the data.  I have some orders that are considered free (Discount Code = "Free" or "Sample") in the data, so I don't want to count those as actual orders.  If a customer's first interaction with us was a free order, then we do not want to count them as a new customer based on that order.  I've attached a newly packaged workbook to continue from.  Thanks.

Tijan

• ###### 1. Re: # new customers

Tijan,

would change your "New Customers" formula to:

{ FIXED [Customer Key]: min(if IFNULL([Discount Code],"") <> "Free" and IFNULL([Discount Code],"") <> "Sample" then [Date] END)}

Thanks,

Scott

• ###### 2. Re: # new customers

That one is not giving the correct answers.  For example, if I do a countd using that formula, new customers for Sept 2010 = 6 instead of 11.

• ###### 3. Re: # new customers

Tijan,

You are correct I wrongly assumed that was part of your New Customers By Month Formula.

sum(

IF

{ FIXED [Customer Key]: min(if IFNULL([Discount Code],"") <> "Free" and IFNULL([Discount Code],"") <> "Sample" then [Date] END)}

=

{ FIXED [Customer Key],[Date (Month / Year)]: min(if IFNULL([Discount Code],"") <> "Free" and IFNULL([Discount Code],"") <> "Sample" then [Date] END)}

then 1 else 0 END

)

Thanks,

Scott

1 of 1 people found this helpful
• ###### 4. Re: # new customers

This is perfect Scott!! I will mark as correct.  I was wondering, if I had several codes that qualify as free, could I make a calculated field for the free orders and then reference that field in New Customer By Month Formula? That way I will only have to update the "free" calculated field as more codes come in.  Thanks for all your help!!!

• ###### 5. Re: # new customers

Tijan,

I attached the workbook using an easier to manage alternate formula where you can add other discount types in the "valid first orders date" calculation.  If this is going to be an extensive list there may be a better way to do this with sets.

Note that as you add discount codes be sure to include and ifnull() around them to ensure you get the number you are looking for.

Thanks,

Scott

• ###### 6. Re: # new customers

If you can help with the sets alternative, that would be wonderful.  Unfortunately I do have an extensive list.  Thanks.

Tijan

• ###### 7. Re: # new customers

Tijan,

See attached for the doing this with sets.  You can use the Discount Code Sets to Exclude Values:

Thanks,

Scott

1 of 1 people found this helpful
• ###### 8. Re: # new customers

Thanks again Scott!  You're a life saver!