How to create a beast mode for email open rate

WizardOz

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%

 

 

jaeW_at_Onyx

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

GrantSmith

@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

jaeW_at_Onyx

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

WizardOz

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

WizardOz

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

 

Olivia

WizardOz

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)

jaeW_at_Onyx

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. 

WizardOz

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

swagner

@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

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

 

swagner

Thanks Jae!