How to calculate usage %
This is what I have: Content ID and Product are meta data for a full list of documents. When an user used a document, such as download, viewed etc, there is a record for Library ID, user role, and user region etc. When there is no Library ID, it means the document has not been used. Now the stake holder wants to see the document usage %, meaning how many documents were used. I can easily get content usage % by count(distinct 'Library ID')/count(distinct`Content ID`).
However, the challenge is how to calculate the content usage % when they select a filter about user roles, user region (I have more user related variables). Hope I explained it clear.
Content id | product | Library ID | user role | user region |
123 | A | 123 | Manager | USA |
234 | A | 234 | Associate | Germany |
124 | B | 124 | Manager | USA |
235 | B | 235 | Associate | Canada |
345 | A | |||
245 | C | |||
346 | B | |||
347 | C | |||
567 | B |
Product | content usage(%) |
A | 67% (=2/3) |
B | 50% (=2/4) |
C | 0% (=0/2) |
When filter on User role - manager | |
Product | content usage(%) |
A | 33% (=1/3) |
B | 25% (=1/4) |
C | 0% (=0/2) |
When filter on User Region - Germany | |
Product | content usage(%) |
A | 33% (=1/3) |
B | 0% (=0/4) |
C | 0% (=0/2) |
Best Answers
-
Gotcha covered @WizardOz ,
EDIT: updated with sound: https://youtu.be/Xb4QgKYgaqg
the short answer, you want to create a dataset with the granularity one row per user per content. then JOIN it to the rows of actual engagement. that way you can calculate did a user engage or not and take the ratio.
this model does not use COUNT DISTINCT so will scale with data volume AND respond to filters!
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
in the enumerator you only keep columns specific to engagement (in my case just libraryID b/c that's the PK for the table, userID, contentID and count_numerator.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers
-
Gotcha covered @WizardOz ,
EDIT: updated with sound: https://youtu.be/Xb4QgKYgaqg
the short answer, you want to create a dataset with the granularity one row per user per content. then JOIN it to the rows of actual engagement. that way you can calculate did a user engage or not and take the ratio.
this model does not use COUNT DISTINCT so will scale with data volume AND respond to filters!
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Hi Jae,
Wonder if it is just me - I coudl not hear anything in this youtube? I checked other youtube videos = no problem with sound.
Can you check and let me know?
Thank you.
1 -
Oh no!! Dang it. I'll have to record it again tonight. Sorry.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Hi Jae,
Thank you fo the video - I now understand the concept now. I will work on the magic ETL .
But here is something I wanted to understand in general -
When you create this new dataset in ETL, do you only select the fields you need or all fields from the original dataset in the numerator dataset? My original dataset has about 70 columns including some measurements. when additional rows are added, the measurements are also added. So once the new dataset is ready, do you only use this new dataset to create cards for % in a dashboard, and use the original dataset for other cards?
Thank you for your help.
Olivia
0 -
in the enumerator you only keep columns specific to engagement (in my case just libraryID b/c that's the PK for the table, userID, contentID and count_numerator.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K 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
- 747 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
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 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
- 108 Community Announcements
- 4.8K Archive