How to create a beast mode for email open rate

Reply
Highlighted
Major Blue Belt

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

 

 sendopenopen rate 
1-Mar20525%
2-Mar 235%
3-Mar 245%
4-Mar 150%

 

 


Accepted Solutions
Highlighted
Red Belt

Re: How to create a beast mode for email open rate

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/How-to-create-a-beast-mode-for-email-open-rate/m-p/47300#M8035 TLDR Grand Total over the entire dataset sum(...

All Replies
Highlighted
Major Blue Belt

Re: How to create a beast mode for email open rate

@WizardOz 

 

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 a

Output 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 t

Here'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_DataFlo...



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Red Belt

Re: How to create a beast mode for email open rate

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/How-to-create-a-beast-mode-for-email-open-rate/m-p/47300#M8035 TLDR Grand Total over the entire dataset sum(...
Highlighted
Major Blue Belt

Re: How to create a beast mode for email open rate

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

Highlighted
Major Blue Belt

Re: How to create a beast mode for email open rate

Thank you for your help!  I applied Jae's approach and get it work.  

 

Olivia

Highlighted
Major Blue Belt

Re: How to create a beast mode for email open rate

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 idproduct Library IDuser roleuser region
123A123ManagerUSA
234A234AssociateGermany
124B124ManagerUSA
235B235AssociateCanada
345A   
245C   
346B   
347C   
567B   


 

Productcontent usage(%)
A67% (=2/3)
B50% (=2/4)
C0% (=0/2)

 

 

When filter on User role - manager
Productcontent usage(%)
A33% (=1/3)
B25% (=1/4)
C0% (=0/2)

 

 

When filter on User Region - Germany
Productcontent usage(%)
A33% (=1/3)
B0% (=0/4)
C0% (=0/2)
Highlighted
Red Belt

Re: How to create a beast mode for email open rate

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
Major Blue Belt

Re: How to create a beast mode for email open rate

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

Highlighted
Black Belt

Re: How to create a beast mode for email open rate

@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.  

Highlighted
Red Belt

Re: How to create a beast mode for email open rate

@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)

Capture.PNG

 

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
Coming Soon: Weekly Virtual User Meet-ups to answer all your questions! Stay tuned for more details to come this week.