I want to understand average sales within 1 year of client sign up
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
Best 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.
2
Answers
-
- 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.
2 -
Wow, @nmizzell, that was everything I was looking for and more. Incredible stuff and I truly appreciate the walkthough you provided. 🙌. Many, many thanks!
1 -
Happy to help!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive