Skip to content

    • Beast Mode

    Beast Mode

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

    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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • Contributor
      Answer ✓

      • 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

    • Contributor
      Answer ✓

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

    • Member

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

    • Contributor

      Happy to help!

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories