Discussions

Issue with Grand Total of "Unique" Rates in Eloqua Insight

Nareman Abdelkader
Nareman Abdelkader Posts: 4 Green Ribbon
edited Dec 18, 2018 3:10PM in Dream It

There are issues with how Grand Totals for "Unique" values are aggregating in the Eloqua Insight reporting tool.

For example, when pulling the reports on all the calculations for each email campaign, they appear correct separately but the "Grand Total" is calculated incorrectly and appears significantly less than it should be (i.e. Correct total for unique open rate for all email campaigns YTD has a 50.55% unique open rate but Insight incorrectly shows 18.91% unique open rate).

I am aware with the new Insight reporting that contacts are de-duplicated when calculating Grand Totals compared to Classic Insight. However, that type of calculation does not display any meaningful data and takes away from the cumulative performance of all campaigns reported on. More importantly, senior level executives will question why de-duplication is treated in the Grand Total when each campaign should be treated uniquely. Given that there is no current workaround in Insight, Eloqua users will be forced to create the proper calculations in an excel separately which is an inefficient use of Insight's capabilities and of the user's bandwidth.

In essence, the unique metrics should be calculated by:

  • Unique Activities (unique opens, unique clickthroughs): Sum of Unique Opens or Clicks for EACH email
    • *Please note: I am aware you can use the "Sum" aggregation rule in Insight but recommend this be set as the default rule for all Insight reports
  • Unique Rates (unique open rate, unique clickthrough rate): Unique Open or Unique Clickthroughs divided by Total Delivered and multiply by 100 to get the percentage

Below is an example of a sample report pulled from Eloqua Insight (but I added what the correct Totals should be).

Can the Insight Product team further reexamine their current approach and please change the way "Grand Totals" are calculated for Unique values in Insight's reports?

Email NameTotal DeliveredUnique OpensUnique Open RateUnique ClickthroughsUnique Clickthrough Rate
Email A4,1202,14852.14%1222.96%
Email B4,1222,26154.85%862.09%
Email C4,1202,32556.43%1182.86%
Email D4,1151,59538.76%982.38%
Insight Total16,4773,11618.91%2701.64%
Correct/Recommended Total
16,4778,32950.55%4242.57%
Post edited by Unknown User on
75 votes

Active · Last Updated

Comments

  • Nareman Abdelkader I experienced the same issue. I am just using Insight (no more old Insight). My total column for Open Rate had a lower rate than it should have. I tried various combinations and still the same. The individual email open rates are correct just not the aggregate total. This is a very basic functionality.

  • This is a system-wide issue. Update from my Support ticket: "Additionally, I have spoken internally with my team in regards to how to edit formulas for "rate" based calculations in Insight. It seems that the rate based calculations are not functioning as expected at the moment, and many of our clients have been having trouble configuring formulas that will suit their needs for "rate" calculations. The developers are aware of the issues all our clients are facing with this and are working to fix it for a future update. I will post a link below to the "dream it" forum which our clients use to communicate with eloqua project managers. here you can enquire further about this issue and they may be able to provide you with a workaround."

  • This is a system-wide issue. Update from my Support ticket: "Additionally, I have spoken internally with my team in regards to how to edit formulas for "rate" based calculations in Insight. It seems that the rate based calculations are not functioning as expected at the moment, and many of our clients have been having trouble configuring formulas that will suit their needs for "rate" calculations. The developers are aware of the issues all our clients are facing with this and are working to fix it for a future update. I will post a link below to the "dream it" forum which our clients use to communicate with eloqua project managers. here you can enquire further about this issue and they may be able to provide you with a workaround."

    @abaldman

    Let me clarify some things here:

    • Aggregate totals of rates and unique metrics are functioning as expected.
    • You can customize the way that Insight aggregates unique totals. See   for example.
    • You cannot customize the way that rates are aggregated. These are always aggregated using de-duplicated calculations.

    So, in the example above, I've highlighted what you can customize in an Insight report:

    pastedImage_2.png

    We are aware of the request to be able to customize the aggregated rates and are investigating it as a potential future enhancement.

  • 2 more ideas covering the same topic. In every training i present i have to explain this issue and most novice users will always be reporting on lower click/opens than reality. Would really appreciate a fix for thie long lasting issue.

  • I recently discovered the issue goes deeper than the totals.

    I made 2 reports:

    - deliverability by day

    - deliverability by week

    In each report i used a formula to group domain names into 6 groups (ms, yahoo,...) And showed unique open rate.

    In the report by day the numbers for unique open rate were fine but by week we noticee the rate was significantly lower considering the by day were all good.

    When looking into the issue we noticed that for each domain, each week the unique open rate was also following the wrong calculation and taking all sends/ unique contacts that opened  average rate should have been around 50% for the week but in the report it was 30. We noticed it as the differenfe got very drastic, we sent an internal mail 5 days in a row to the same segment so each time it was 5 contacts/1 open.

    Using aggregation rules i could not find any fix.

  • RichardGar
    RichardGar Posts: 16 Blue Ribbon

    Completely agree on this.  Makes no sense at all. Plus I always have to explain this countless times why it never equals what everyone quite rightly thinks it should be.

  • Nareman Abdelkader
    Nareman Abdelkader Posts: 4 Green Ribbon

    I recently discovered the issue goes deeper than the totals.

    I made 2 reports:

    - deliverability by day

    - deliverability by week

    In each report i used a formula to group domain names into 6 groups (ms, yahoo,...) And showed unique open rate.

    In the report by day the numbers for unique open rate were fine but by week we noticee the rate was significantly lower considering the by day were all good.

    When looking into the issue we noticed that for each domain, each week the unique open rate was also following the wrong calculation and taking all sends/ unique contacts that opened  average rate should have been around 50% for the week but in the report it was 30. We noticed it as the differenfe got very drastic, we sent an internal mail 5 days in a row to the same segment so each time it was 5 contacts/1 open.

    Using aggregation rules i could not find any fix.

    Agreed, I frequently pull our monthly benchmarks and the unique open rates are now exacerbated. For instance, monthly "internal newsletters" Eloqua campaigns used to average around a 45% unique open rate with Classic Insight but with the new Insight, averages for unique open rate drastically dropped to 5%.

  • d7edwards
    d7edwards Posts: 1 Blue Ribbon

    Here is how you change the formula to calculate the columns that way you want to:

    https://community.oracle.com/docs/DOC-1022281

  • MorganD
    MorganD Posts: 6 Blue Ribbon

      but mostly

    ^^^ The day when you realize that some aggregates can never reach 100%, ever.

    Maybe the hardest part, like @Matt Stringer-Oracle mentioned, is to explain to management why all data until now is off, and the undertaking to correct is worth it.

    > 0 traction.

    The only redeeming quality of this confusing bottom line that totally looks like a sum total of what's on top of it, but reports on something very different, is to give a sense of how the campaign messaging performed against the audience and the relative amount of effort it took to get it seen.

    But it is really a stretch...

    Brought it up to support once, and they couldn't wrap their head around the issue.

    Not helped by the fact that this is not touched upon anywhere in the documentation. Big let down.

  • derek.bell
    derek.bell Posts: 105 Silver Medal

    @JodyMooney-Oracle from what I can see, nothing has been done by the #OBIEE team to fix this, it was first raise mid July 2018. Can we assume Oracle plan to do nothing or will it be corrected? Can you please confirm that it will be fixed, yes or no and when. Surely three years and 12+ release later, there's been time.

  • @JodyMooney-Oracle Is there any update on this topic? I still see people recommending to use Campaign reports when I just know they are wrong.

    Imagine this very basic example:

    • Internal newsseries, we send 1 email every day for 5 days to the same audience (segment -> email -> wait -> email -> wait -> email
    • Nurture email, we send 1 email every week for 5 weeks (segment -> email -> wait -> email -> wait)

    Let's say we send it to 100 people and every single one of them opens and clicks on each email, it was glorious.

    I sent out my campaign, everyone is talking about my emails and I click on campaign reporting to see this:

    • Unique Click rate: 20% (should be 100)
    • Unique Open rate 20% (should be 100)
    • Emails sent: 500
    • Unique opens: 100 (should be 500)
    • Unique clicks: 100 (should be 500)

    Then the user goes into the email reporting and sees 100% open rate on all and in the totals (=campaign) part he sees 20%.

    As soon as you start sending more than 1 email to the same customer in your campaign your campaign reporting is no longer reliable. I can guarantee that there are customers severely underreporting their open/click rate for years without knowing this bug. The only way to counter it is to avoid campaign reporting at all costs and always report on email by email basis and make your own totals.

  • To be clear it is not only in these custom insights reports that you see this problem. If you send a campaign like the one from my example and just click on 'campaign analysis' in the campaign menu you will also see this false open rate.

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

    Hey @Sebastiaan Van Dingenen - Wanted to start out by clarifying first that as mentioned previously by Alexa, aggregated totals of rates and unique metrics are functioning as expected for Insight.  

    •You can customize the way that Insight aggregates unique totals.

    •You cannot customize the way that rates are aggregated. These are always aggregated using de-duplicated calculations.

    To give a bit more context, the current calculations for unique rate grand total are based on representing unique contact activity in campaigns, which is an industry best practice.

    I’ve also created a post where you can recreate Classic insight grand totals in your reports: https://community.oracle.com/topliners/discussion/4470724/insight-tips-how-to-create-a-unique-open-rate-grand-total-similar-to-classic-insight

    However I understand the concern and your use case, and we are looking at options to customize grand total calculations or create custom grand totals. Happy to chat more if you'd like to chat further. We will also ensure to communicate any progress for upcoming releases, please follow Oracle Eloqua Release Center.


    OA

  • Sebastiaan Van Dingenen-iODigital
    Sebastiaan Van Dingenen-iODigital Posts: 130 Bronze Medal
    edited May 25, 2022 8:16PM

    Hi @Otilia Antipa-Oracle ,

    Thank you for taking the time to comment.

    Let me start off by saying that I can agree the calculations are 'working as expected' when you put them next to the oracle documentation but this is as far as I can agree, the totals are not working to the expectation of any user and provide completely useless and even dangerously wrong basic information.

    Could you please point me to non-oracle documentation (industry best practices) where they use exactly this calculation to get open and click rate?

    1 / (total EMAIL DELIVERED * unique contacts that have at least 1 open or click)?

    Can you tell me the business value of this result, in plain English? Take my example from above, I see a 20% open rate (should be 100), what does this 20% tell me?

    In the case of my calculation the 100% tells me that all contacts that I have sent emails to have opened all my mails, what does the oracle calculation tell you?

    You are mixing email delivered totals with unique customers, it just doesn't work that way.

    If you are going to use unique as a unique customer you need to be consistent:

    1/ (total CUSTOMERS * unique contacts that have at least 1 open/click). This would make perfect sense, this tell me out of all the customers I targeted in my campaign which % of them have at least 1 open or click (split in 2 fields of course). It has real business value.

    I am sad to see that after all this time there is still no acknowledgement that what the calculation is doing is wrong, it is not a deeply hidden issue, the error is clear, no-one wants the bad calculation and no-one can even explain the business value of the wrong number.

    The only reason I can think of not to change this is because you don't want to go back to your customers and tell them they are using wrong metrics for multiple years or you are afraid that making these changes will mess up all kinds of dashboards and integrations people have built on the bad data (which still makes them bad).

    The solution is not to build even more 'alternatives' and custom aggregation techniques so experts can fix it but the solution is to make it work like it should on all out of the box reporting.

    Please prove me wrong by providing :

    - Business value of current unique open and click rate

    - A non-oracle definition of unique open and click rate following your exact calculation where a unique click is seen by customer and then mixed with total delivered of a campaign

    Post edited by Sebastiaan Van Dingenen-iODigital on
  • derek.bell
    derek.bell Posts: 105 Silver Medal

    We have the same problem, although it's not a "bug". It's working as "expected". The issue is that a good number of clients and partners don't agree with Oracle's calculation.

    We produce a benchmark report for an industry segment each quarter and share the results with each of the clients.

    @Otilia Antipa-Oracle we have to do our own calculations because the clients do not agree with the way Oracle chooses to present results. Something for Oracle to think about.

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

    Thank you for your feedback @derek.bell - I'm looking at how we can best address this for an upcoming release. As a former client I had similar challenges, so I understand the impact and the need to provide consistency.

    I created a post on how you can recreate a table to calculate totals similar to classic insight, I know this might not be ideal to create for every single report however it's an option to build into your more common reports.

    https://community.oracle.com/topliners/discussion/4470724/insight-tips-how-to-create-a-unique-open-rate-grand-total-similar-to-classic-insight