If you wonder about the best time of day or night to send emails, Send Time Optimization can take out the guesswork. But the question is, can you achieve this report in Eloqua Insights? Today in this blog, I will be sharing a customized “Email Open Report” which can be used to determine the best time/phase to send an email to achieve high open rate.


Marketing Challenge: To understand the best time/phase when a contact opens the email.

Goal: To find the top two hours of the day where the email open rate is maximum.

Current Benchmark: As per the blog post of campaign monitor, the best time to send an email is “Afternoon”.


Marketing cloud courses that helped you achieve campaign success

The following marketing cloud courses really helped me to achieve campaign success:

1. Insight for Reporters

2. Insight for Analyzers

3. Advanced Criteria Functions

4. Working with Pivot tables

5. Advanced Dashboard Functions 


Creating your own report:

To create a personalized send time optimization report, a user needs to have access to Oracle Eloqua Insights.  These are the following steps needed to create your own report.

  1. From Eloqua home page, navigate on Analytics and click on Insights.
  2. Navigate to report catalog and click on an email.
  3. Navigate to “Email Open Overview” report, click on more and copy the report.
  4. Paste the report in the “My Folders” and rename if needed.
  5. Click on the edit button. Now, the default “Email Open Overview” report is there.
  6. Firstly Remove all the default filters and click on filters and add “Email name” filter and provide email name which is taken as a reference to achieve send time optimization. In this case, I have considered “Email name is equal to test-email” where “Test email” is the name of the email. More than one email can be added however for the start, lets select one recently deployed email.
  7. To customize the report, go to the subject area and drag any field to subject area section. In this case, “Time Span” is dragged from “Email Open calendar” folder.
  8. As we need a custom field, click on the gear icon of “Time Span” field and click on “Edit Formula”.
  9. Now, we need to add one SQL formula. Before that let’s understand the basics. Here, we are considering different phases of days as following:


Phase of the Day


Phase of the Day

06AM – 08AM

Early Morning

04PM – 06PM

Late Afternoon

08AM – 10AM


06PM – 08PM

Early Evening

10AM – 12PM

Late Morning


Late Evening

12PM – 02PM

Early Afternoon



02PM – 04PM



Late Night


The formula is explained below for one phase “Early Morning”:


When “Email open date” is between 06AM and 08AM

Then Change the value of custom field to “Early Morning”

Else change to “Night”



To clean the data we are casting column name as time and extracting hour value from the column and again casting the value as an integer. Then adding different phases of the days in the “When” and “Then” statements. Please download the attached text file to view the complete formula for all phases of the day.


10. Copy and paste the below formula in the Column formula section and rename the Column heading as “Best Phase to Open Email” by clicking on custom heading checkbox. For the convenience, the full formula is attached in the separate document to reduce the length of the blog.

11. Now, Click on Result tab and a customized “Email Open Overview” report will be displayed in the compound layout.

12. Click on “New View” and create a pivot table. Click on the edit icon and sum all the unique open against “Best Phase to Open Email” column. To represent the data cleanly, we have excluded the other columns from the report.

13. Now, Pivot table is ready.

14. With the help of “Graph Pivoted Results”, data can be presented cleanly using the pie chart. Click on “New View” and navigate to “Graph” and change the chart type to pie chart to get a simplified view of data.

15. This is the final step to customize the pie chart. Click on “Edit graph properties” [xyz] icon and update “Legends” and “Data Markers” from title and Label.

Hence, our customized “Email Open Overview” report is ready with the pie chart representing the best phase to open an email. Going forward the send time can be optimized during the data of this report.


Step by Step explanation:

Send time optimization report from insights.gif



Send Time Optimization allows marketers to send emails at the optimal time for each contact. The optimal time for contact is when they are most likely to open the email based on historical data of their email open times. In this report, we are collecting the open time of the contacts based on the data of previously sent campaign and utilizing that data to find out the best time to send next email. The powerful capability of insights is utilized for this report which will provide additional metrics for new school marketer in optimizing their campaign. In this case, Best phase to send email comes “Late Afternoon” (04PM – 06PM) however this change campaign to campaign and email to email. To get a more precise report, we can add multiple emails in the filter criteria or create a send time optimization report to a specific campaign. Also, analysis can be customized based on which day email has been sent or at what time email has been sent.