I want to understand average sales within 1 year of client sign up

user060355

I have a dataset where each row is an individual sale that contains the sale date, client start date, client ID, and sale total. I want to be able to chart the amount an average client spends within X time of their start date where X is a variable that I can alter from the card.

Ideally, I'd be able to split the ouput by year so I can compare how much an average client spent during their first 6 months in 2021 vs. 2022 vs 2023.

I've made a very simplified version of the dataset so you can get an idea of what I mean. any help is greatly appreciated

Book1.xlsx

nmizzell

• 1. Define a variable for the number of days after client start date that you want to include. This variable can be added in the dashboard as a control and can be manipulated. I will refer to this variable as `Number of Days To Include`

• 2. Create a beastmode to calculate total client spend within the specified days:

• sum(case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `Total` end)

3. If you want to find the average client spend, divide this value by the distinct number of clients in the time interval.

(sum(case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `Total` end))

/

(count(distinct case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `ClientID` end))

4. To split the output by year, make sure that the "graph by" setting on your visual is by year.

nmizzell

• 1. Define a variable for the number of days after client start date that you want to include. This variable can be added in the dashboard as a control and can be manipulated. I will refer to this variable as `Number of Days To Include`

• 2. Create a beastmode to calculate total client spend within the specified days:

• sum(case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `Total` end)

3. If you want to find the average client spend, divide this value by the distinct number of clients in the time interval.

(sum(case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `Total` end))

/

(count(distinct case when `date` >= `Client Start Date` and `date` ⇐ date_add(`Client Start Date`, interval `Number of Days to Include` day) then `ClientID` end))

4. To split the output by year, make sure that the "graph by" setting on your visual is by year.

user060355

Wow, @nmizzell, that was everything I was looking for and more. Incredible stuff and I truly appreciate the walkthough you provided. 🙌. Many, many thanks!

nmizzell

Happy to help!