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%

 

 

Best Answer

Answers

  • GrantSmith
    GrantSmith Indiana 🔴

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

  • 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

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

     

    Olivia

  • 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)
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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. 

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

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

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

     

  • Thanks Jae!