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

Options
user060355
user060355 Member
in Beast Mode

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

Best Answer

  • nmizzell
    nmizzell Contributor
    Answer ✓
    Options

    • 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.

Answers

  • nmizzell
    nmizzell Contributor
    Answer ✓
    Options

    • 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
    user060355 Member
    Options

    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
    nmizzell Contributor
    Options

    Happy to help!

Categories