Skip to main content
Solved

Calculating open rate using events leading to incorrect results

  • October 22, 2024
  • 1 reply
  • 17 views

Forum|alt.badge.img

Hello,

I currently have a service that syncs Klaviyo events to a data warehouse, where we calculate metrics such as open rates and click-through rates by aggregating event data.

When calculating the open rate for a given campaign, we follow this process:

  • Unique Open Rate: Group events of type 'Opened Email' by recipient email and count distinct.
  • Total Recipients: Group events of type 'Received Email' by recipient email and count distinct.

We then calculate the open rate as:
Open Rate = (Unique Open Rate / Total Recipients).

However, for one of our campaigns, we're seeing an issue where the Unique Open Rate is 36,410, while the Total Recipients is only 240. This results in an impossible open rate, as the number of opens significantly exceeds the total recipients, which shouldn't be possible.

Could you assist with this? Can you verify on your end that the events API is 100% accurate and correctly reporting all events?

Best answer by chloe.strange

Hi @stevenwithnorby

Can you share with us exactly how you are querying the events AP in order to get the Opened Email and Received Email events that are going into your data warehouse? Including arguments to the API. Please mask any sensitive data as necessary, e.g. for Ids that are the same, make sure they have the same mask. 

~Chloe

View original
Did this topic or the replies in the thread help you find an answer to your question?

1 reply

chloe.strange
Community Manager
Forum|alt.badge.img+41
  • Community Manager
  • 426 replies
  • Answer
  • November 9, 2024

Hi @stevenwithnorby

Can you share with us exactly how you are querying the events AP in order to get the Opened Email and Received Email events that are going into your data warehouse? Including arguments to the API. Please mask any sensitive data as necessary, e.g. for Ids that are the same, make sure they have the same mask. 

~Chloe