How to create a beast mode for email open rate
I wanted to draw a chart to show email open rate over time.
For a specific email, it was send on a day, then opver the next fews, emials were opened. So the open rate will be #of email opened/# of email sent. # of email opened should sum up the total opens of everyday, but # of email sent is the same over the time.
How to create such beast mode? Thank you
send | open | open rate | |
1-Mar | 20 | 5 | 25% |
2-Mar | 2 | 35% | |
3-Mar | 2 | 45% | |
4-Mar | 1 | 50% |
Best Answer
-
No need to do ETL!!
Create a Beast Mode with (swap the correct spellings for column names). You'll have to have the feature switch that allows you to use aggregate functions enabled (talk to your CSM).
sum(sum(`Opens`)) over (Partition by month(`Date`) ORDER BY `Date`)
/
sum(sum(`Sent`)) over (partition by MONTH(`Date`) order by `Date`)have a quick google for 'Windowed Functions' it's a normal SQL thing that allows you to perform aggregations over a ... window... of rows. In your case, the aggregation, sum(amount) should take place over the window 'all of the rows up to a point in time (SORT BY clause) for that month (PARTITION BY clause).'
longer youtube video: http://www.youtube.com/watch?v=ZPf41Fjn1H8
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"6
Answers
-
This is a very common question. The limitation of beast modes is that it can only look at a single row at a time so you can't do a cumulative sum like you'd need in this case via a beast mode. You can however utilize a MySQL ETL data flow to get the information you'd need. The following is untested back of napkin code but should illustrate the steps necessary. email_id is the unique identifier for the email if you have more than one email in your dataset.
Transform (call it whatever you want but for this example it'll be transform_data_1)
SELECT a.`date`
, a.`email_id`
, a.`send`
, a.`open`
, (SELECT SUM(c.`send`) from input_DataSet c where c.`email_id` = a.`email_id) total_sent
, (SELECT SUM(b.`open`) FROM input_DataSet b WHERE b.`date` <= a.`date` AND b.`date` >= a.`date` and b.`email_id` = a.`email_id`) AS cumulative_open
FROM input_DataSet aOutput Dataflow:
SELECT t.`date`
, t.`email_id`
, t.`send`
, t.`open`
, t.`cumulative_open`
, t.`total_sent`
, t.`cumulative_open` / a.`total_sent` as open_rate
FROM transform_data_1 tHere's also a link to a KB article about rolling averages / cumulative sums: https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Creating_a_Rolling_Average_Using_DataFlows
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
No need to do ETL!!
Create a Beast Mode with (swap the correct spellings for column names). You'll have to have the feature switch that allows you to use aggregate functions enabled (talk to your CSM).
sum(sum(`Opens`)) over (Partition by month(`Date`) ORDER BY `Date`)
/
sum(sum(`Sent`)) over (partition by MONTH(`Date`) order by `Date`)have a quick google for 'Windowed Functions' it's a normal SQL thing that allows you to perform aggregations over a ... window... of rows. In your case, the aggregation, sum(amount) should take place over the window 'all of the rows up to a point in time (SORT BY clause) for that month (PARTITION BY clause).'
longer youtube video: http://www.youtube.com/watch?v=ZPf41Fjn1H8
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"6 -
thank you very much Jae! I watched your whole section of youtube on this subject, you explained it very well. I am able to modify it and get it work for different emails within the same campaign and show the performance.
I am going to apply this to another senario I have, hope it will work. But may reach out to you again for help!
Thank you.
Olivia
0 -
Thank you for your help! I applied Jae's approach and get it work.
Olivia
2 -
Hello, Jae, Need your help with another scenario. I could not figure it out. Can you please let me know how to do it or if it is possible to do it? Thank you very much!
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 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) 0 -
can you make a new dojo topic for this? I LOVE THE QUESTION and am happy to dig in... but just to keep things separate.
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 -
https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/How-to-calculate-usage/m-p/47478#M8067
Thank you Jae, I have created a new topic.
Olivia
0 -
@jaeW_at_Onyx I am attempting to apply what you described in your video here. I built my beast modes and put them in a table card (as you recommended) and they are producing the values I expect. When I attempt to use them in a multi-line card type I am getting an error. Here is a short video I put together: https://youtu.be/ioeYEukR8g4
What I am trying to do:
Want to produce a multi-line card that displays the number of orders (as a running total) by day of the quarter. Displaying each quarter as a series so we can see how the performance might vary by quarter.
0 -
@swagner ,
Any columns you use in your WINDOWED functions must appear in the chart.
In the example below you're partitioning by the contact of YearQuarter and sorting by order date... therefore you must have date represented in the chart else it tries to SORT on a column that doesn't exist. (I believe)
Try this, go back to your table, and only include the 3x columns you were planning to put in your visualization, I'll bet it doesn't work until you add the Date column back into the table.
THE ANSWER YOU DIDN'T ASK FOR
Instead of trying to use a windowed function to calculate the day of the quarter, build / use a Date Dimension that has Day of the Quarter added to it already. The Day of the Calendar Quarter is never going to change, so there's no reason to do that expensive count distinct windowed function.
Also, I suspect eventually you'll want to do a trend analysis of THIS Quarter versus LAST QUARTER, you'll have the least headache and most flexibility if you get comfortable building a data dimension where
date = April 1,
dayOfQuarter = 1
YearQuarter_DOQ_str = 2020-2-01
YearQuarter_DOQ_num = 2020201
etc.
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 -
Thanks Jae!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive