Determine percentage of repeat callers (beast Mode or ETL?)
Good morning. We've been trying to create a beast mode to help us determine the percentage of repeat callers and while I can picture in my mind what needs to be done, we've been unable to determine a working calcuation for it.
In the image below, there are 14 distinct companies/phone#'s and a total of 33 calls. Of the 14 distinct companies/phone#'s, a total of 8 have called in more than once, so that would equate to 57% of our callers being repeat callers.
Can this be accomplished with a beast mode, or is it something we'd need to do in ETL, i.e. collpase columns to aggregate and then create a beast mode at the card level? I'm not a math whiz, but thought it would be easier than this!
If anything is needed to help, please don't hesitate to ask, and any assistance will be greatly appreciated; thanks in advance...
Just a few of the many attempts made are:
- CASE WHEN
(COUNT(DISTINCT`PhoneNumber`) - COUNT(`PhoneNumber`)) = COUNT(DISTINCT `PhoneNumber`) THEN 1 ELSE 0 END / `Callsid` - CASE WHEN
(COUNT(DISTINCT`PhoneNumber`) - COUNT(`PhoneNumber`)) THEN 1 ELSE 0 END / `Callsid` - 100 - (COUNT(`PhoneNumber`) - COUNT(DISTINCT `PhoneNumber`)) / COUNT(DISTINCT `PhoneNumber`)
Helpful links we thought could help us resolve this:
1) https://dojo.domo.com/t5/Domo-Dojo-8482-Documentation/Calculate-percentage-of-a-subset-to-the-total-in-Beast-Mode/ta-p/1922 (this would likely work fine for a single bar chart, but unable to get it to work since we want the % of total in a bar, and the total # of calls as a line)
2) https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Repeat-and-Distinct-Count/m-p/18047#M1699
Best Answer
-
We're working on doing this in an ETL. Thanks
0
Answers
-
This would be the top level of the card, and we'll use a table to drill down to view the repeat offenders in the hopes of finding ways to minimize the issue.
0 -
Any thoughts or suggestions on this? Apologies if the submission was long winded or possibly confusing, trying to simplify the request here:
It's understood COUNT(DISTINCT `PhoneNumber`) will return a count of unique phones #'s, which obviously we'll require, but the problem we're having is determining the count of the unique phone #'s returned that have called in to our call center more than 1x.
From there, it's likely a simple calculation of dividing those distinct phone#'s that called in more than 1x by either total calls/phone #'s to provide us a percentage of repeat callers, but we've been unable to figure out how to get that piece of the equation, i.e. a count of unique phone #'s that have more than one (1) call in.
Thank you...
0 -
We're working on doing this in an ETL. Thanks
0 -
Why hasn't anyone responded to this?
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