Calculate % of Repeat Customersvia Beast Mode
Trying to help co-worker with a beast mode... and first not sure if this is possible at the card level (maybe better handled with a dataset view).
Here's the business question: How many of our eCommerce customers are repeat customers? Displayed at a % of total eCommerce customer count. Example: 25 of 100 customers are repeat customers, or 25%
- The card is filtered on the eCommerce transaction type
- The order data is at the line item level, and we are today counting "Customers" via COUNT(DISTINCT `Customer Name`)
Logically this would be something like...
HAVING(COUNT(DISTINCT `Customer Name`) >1) / COUNT(DISTINCT `Customer Name`)
Best Answer
-
This will be much simpler to do within an ETL than attempting to do it within a beast mode.
I'd recommend using a rand and window tile in Magic partitioned on the customer, sorted by the order time and calculate a Dense Rank to determine the order number / count for each specific user. You can also partition on the transaction type to determine this within each transaction type. Then in your ETL, calculate the max value of this count for each customer and if it's greater than one then mark them as a repeat customer. Finally in your beast mode you can then calculate the number of distinct customers with:
COUNT(DISTINCT CASE WHEN `Is Repeat` = 1 THEN `Email` END) / COUNT(DISTINCT `Email`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
Do you want this to be affected by filtering the transaction type or is this to be across all transaction types?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
There will be filtering on the card (in analyzer) for the eCommerce transaction type. That would narrow the field down to customers who have ordered (all in the data) and customers who have ordered more than once.
0 -
This will be much simpler to do within an ETL than attempting to do it within a beast mode.
I'd recommend using a rand and window tile in Magic partitioned on the customer, sorted by the order time and calculate a Dense Rank to determine the order number / count for each specific user. You can also partition on the transaction type to determine this within each transaction type. Then in your ETL, calculate the max value of this count for each customer and if it's greater than one then mark them as a repeat customer. Finally in your beast mode you can then calculate the number of distinct customers with:
COUNT(DISTINCT CASE WHEN `Is Repeat` = 1 THEN `Email` END) / COUNT(DISTINCT `Email`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith thanks!
0 -
To do aggregate (as opposed to row-level) calculations in a BeastMode, you'll need to use FIXED functions:
Yours would be something like:
(case when sum(count(Distinct `Customer Name`) fixed())>=2 then 1 else 0 END)/sum(count(distinct `Customer Name`) fixed())
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 737 Beast Mode
- 56 App Studio
- 40 Variables
- 684 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 35 Predict
- 14 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive