How to create a beast mode for email open rate

Reply
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
Major Brown 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

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
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
Major Brown 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

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
Major Brown 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. 

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

Announcements
Want to join our weekly online meet up? Please visit this board here and let me know. Click here for more details!