Discussions

Insight Tips | How to Create a Unique Open Rate Grand Total Similar to Classic Insight

Otilia Antipa-Oracle
Otilia Antipa-Oracle Product ManagerPosts: 106 Employee
edited May 6, 2022 2:09PM in Eloqua

Hey Insight users!

While making the transition from Classic Insight to OBIEE Insight you may have noticed the change in Unique Open Rate calculations when it comes to the Grand Total line of your reports.

For more info on how unique rates are calculated check out this post:

https://community.oracle.com/topliners/discussion/4351932/insight-obiee-demystifying-unique-totals

In this post, we’ll look at how you can create a Unique Open Rate grand total that matches the unique rates you saw in Classic Insight.

As a quick refresh the current Unique Grand total will dedupe records to give you a true grand total. Meaning that if Contact A was in Campaign A and Campaign B they will only be counted 1 time towards your grand total.

Solution overview:

We will create a custom formula that uses the sum totals to generate a Unique Open Rate grand total that matches the logic of Classic Insight.

We will display that custom grand total in a second table to easily view grand totals in your report.

**Watch out for**

- Unique Open Rate is calculated by Unique Opens / Total Delivered.

-Note that although this solution can be used across subject areas, you need to ensure you use the metrics within that subject area to build your formula.

Ie. if you are in the Campaigns subject areas your metric for total Unique Opens will use the following format “Email Activities"."Unique Opens"    vs the Email Analysis by Send Date subject area which looks like this “Activities"."Unique Opens

(we will revisit this in more detail with the example below).

-          Also don’t forget.. because we are working with a percentage calculation you will need to ensure you multiple by 1.0*   for more information on that check out this post:

Steps:

1.       Within your Results tab select “New Calculated Measure”

pastedImage_6png

2.       Now we can build the formula to calculate the new Unique Open Rate Grand Total :

In this example, I am working in the Email Analysis by Send Date subject area. If you’re using another subject area, as mentioned above, you will need to ensure you pull in the corresponding metrics as indicated by the highlighted portion)

SUM(1.0*("Activities"."Unique Opens"))/SUM(1.0*("Activities"."Total Delivered"))*100

3. Give your new column a custom heading name > Select OK

4.       You will now see your column included in the report. Because this is a grand total you’re going to see the same number repeated in the column, so we should clean up this report a little bit right?

Recommended steps to format:

1.       On your table, select the pencil icon (edit view) and exclude the new Unique Open Rate column

2.       Hit Save

3.       Select New View > Table. This will create a new table which we will use to show the grand totals and expose the new calculation for unique open rates

4.       Remove all the attribute columns; this would be either your Email Name, Campaign name etc. This is so that we reduce the rows to a single total line

5. You’ll also need to change the data format to show your result as a percent:

Select column properties > Data Format and enter the following:

You should now have  a report where you can see the calculation for the unique open rates as well as the new (aka old) Classic Insight calculation.

pastedImage_3png

Hope this helps !

Otilia


Post edited by JodyMooney-Oracle on

Comments

  • Great Solution..

    Just adding one more pointer. This is working for individual email/campaign metrics, but not working for the grand total or aggregate total rate.

    Hence, we just added the same formula in actual Unique Clickthrough Rate formula section and it worked. Thank you..!

  • Otilia Antipa-Oracle
    Otilia Antipa-Oracle Product Manager Posts: 106 Employee

    Great Solution..

    Just adding one more pointer. This is working for individual email/campaign metrics, but not working for the grand total or aggregate total rate.

    Hence, we just added the same formula in actual Unique Clickthrough Rate formula section and it worked. Thank you..!

    Hey SreeBalaji Manickam - the Grand Total cannot be changed in the report; hence this post is to outline how to create a secondary table where you can mimic a grand total with custom calculations.

    thanks!

    OA