New Customers/

zuchu
zuchu Member
in Charting

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?

Answers

  • Aditya_Jain
    Aditya_Jain Contributor 
    (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

    'Happy to Help'
  • zuchu
    zuchu Member

    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
    zuchu Member
    edited October 2022

    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
    zuchu Member

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

  • GrantSmith
    GrantSmith Coach

    Hi @zuchu

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • zuchu
    zuchu Member

    @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
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • zuchu
    zuchu Member

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

Categories