New Customers/

zuchu

Hello,

I'm working on visualizations that aim to show new customers within years. My database contains data from 2020 moving forward. I want to see new/unique customers that bought from us for the first team this year. If it is possibly want to compare it to 2021 too but struggle to filter that out or write a beast mode. Any recommendations? Was thinking of something like

(CASE

WHEN 'firstorder' date (that's the place where I don't know how to write it) > '01.01.2022' THEN 'New Customer 2022'

WHEN 'firstorder' date <'12.31.2021' AND 'firstorder' date '01.01.2021' THEN 'New Customer 2022'

ELSE 'old customer'

END

)

Any ideas how to handle that?

Aditya_Jain

(CASE


WHEN YEAR(`firstorderdate`) =  YEAR(TODAY()) THEN CONCAT('New Customer ',YEAR(`firstorderdate`))


WHEN YEAR(`firstorderdate` ) = YEAR(TODAY()) -1  THEN CONCAT('New Customer ',YEAR(`firstorderdate` ))


ELSE 'OLD Customer'


END


)

Hi @zuchu - please try the above pasted code, this would be dynamic, so for current year, it would categorize the customers into New Customer 2022, New Customer 2021 and rest all as Old Customer.

When you step in 2023, your categories would become New Customer 2023, New Customer 2022 and rest all as Old Customer.

So it's, Dynamic and depends on your first order date.

Hope this works. For comparative charts, I would prefer using YOY charts when comparing with date ranges.

Thanks

Aditya Jain

zuchu

Hi @Aditya_Jain , thanks for help mate! Appreciate your help, Unfortunately it doesn't work, should I create those edits in ETL or can I just create a beast mode within the analyzer?

I changed the name to my columns but I got an error saying: Calculation Error : This calculation is using a nonexistent function. Do you have any recommendations to solve that issue?

(CASE

 

 

WHEN YEAR(`DocDate`) = YEAR(TODAY()) THEN CONCAT('New Customer ',YEAR(`DocDate`))

 

 

WHEN YEAR(`DocDate` ) = YEAR(TODAY()) -1 THEN CONCAT('New Customer ',YEAR(`DocDate` ))

 

 

ELSE 'OLD Customer'

 

 

END

 

 

)

zuchu

Good Morning,

I got the syntax to work, see below:

(CASE

 WHEN YEAR(`DocDate`) = YEAR(CURRENT_DATE())THEN CONCAT('New Customer ',YEAR(`DocDate`))

 WHEN YEAR(`DocDate` ) = YEAR(CURRENT_DATE()) -1 THEN CONCAT('New Customer ',YEAR(`DocDate` ))

 ELSE 'OLD Customer'

 END

 )

But it doesn't seem to resolving the issue. This way I just can see when does an order came, same as if I just applied a filter to show current year, I wanted to depict customers who showed up for the first time in a given year (ex. count of customer who did the first purchase in 2022). Any ideas how to handle that issue?

Thank you!

zuchu

Up, anyone got any ideas? Would appreciate any input 🙏

GrantSmith

Hi @zuchu

Do you have the customer's first order date as part of your dataset or do you need it calculated?

zuchu

@GrantSmith, unfortunately, I don't have first order for customer, I need to calculate that as well, I have customer name and date for each time they put an order

GrantSmith

Ok, you can utilize a Magic ETL and the Rank & Window tile to calculate the customer's order number. Make sure you sort on the order date and partition based on the customer identifier and calculate the row number. This will then get you a number for each customer's order with their first order having a value of 1. You can call it Order Count.

Then you can utilize the Order Count field in your visualization and filter for Order Count = 1 to only show the first orders for customers. Then you can also utilize a PoP chart to display the last year vs this year comparison.

zuchu

@GrantSmith , thank you so much! It works!! Appreciate your help.