Find Count Distinct w. Multiple Factors
Hello!
I need to find ‘new initial customers’ by sales agent at the campaign level.
For the purpose of this report, we define a ‘new initial customer’ as a customer (Customer_ID) who entered into a campaign (Campaign_ID) within a specified time range (by calendar month).
I need to only count Customer_IDs the first time they opted in (as there can be multiple of the same Customer_ID under the same Campaign_ID) over the given calendar month, but then capture that and calculate again for the following month, regardless of the data the month prior and so on. So looking to capture the data for each month and the first occurrence that month under each campaign.
I would like to do this within an ETL. However, beast mode would be okay too.
When doing this at the card level in beast mode, I used case when statements but ran into an issue.
For example:
(Case
when Campaign_ID = 51 then count(distinct Customer_ID)
when Campaign_ID = 52 then count(distinct Customer_ID)
when Campign_ID = 53 then count(distinct Customer_ID)
End)
That seemed to work, but when I went to multiply that by order_total to get revenue, it gave me a larger number than when I sum(Order Total) for the same campaigns, unfiltered.
(Case
when Campaign_ID = 51 then count(distinct Customer_ID)
when Campaign_ID = 52 then count(distinct Customer_ID)
when Campign_ID = 53 then count(distinct Customer_ID)
End)*'Order Total'
Any insights as to why I’m getting incorrect numbers and what I could do in the ETL?
Sample data attached.
Comments
-
I think ETL is definitely the way to go on this. I think a combination of MAX and SUM would get you where you want to go. Could you provide a screenshot of what the end result of your sample data would look like? I'm having a bit of a hard time visually what you are looking for based on your description.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.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
- 60 App Studio
- 41 Variables
- 688 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 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