Understanding the use case
There may be a situation where you need to report on global email metrics without distinguishing particular campaigns, and you want to have an average open/click rate of all emails over a specific timeframe.
When you consider what you need to do and which events you want to work with, setting up may be more challenging. The main problem is that one campaign can have multiple open/click events while having only one delivery event. The main goal of this article is to explore possibilities for how you can consider only unique open/click events per campaign so that, in the end, you will have reliable reporting numbers.
First of all, let's understand the problem. The method will be the same whether we aim to construct a report that is drill-downed by timestamp or not. The best way to understand it is to look at the example.
If a customer had multiple campaigns, such as:
Campaign A - Opened 3x
Campaign B - Opened 5x
Campaign C - Opened 2x
We need to count unique opens, so the final count of opens we're looking for is three. If we were to use the ALL operator in the metric, we would get a sum of all nine, which is incorrect, and if we were to use the FIRST operator, we would get just one, which is again useless. So we would need to group them by a sent_timestamp and count only one open per unique sent_timestamp.
1. Creating a distinct values aggregate for campaign-timestamp grouped by sent_timestamp
You need to create the following aggregate, which will create a list of distinct values of campaign event timestamps with the status clicked/opened (depending on whether you are doing a global metric for open rate or click rate). Those are grouped by sent_timestamp from the first event in each group.
It will give you a list of timestamps for clicked events, each representing the first click of a unique campaign.
2. Insert this aggregate into an event expression for the campaign event
Now, you must create an expression for the event and insert the aggregate from the first step. You'll need to use this in our report.
3. Create a report with the metric for the global open/click rate
The final step is creating a report with the desired logic in the metric for the global open/click rate.
The formula for calculating a click rate is --> Count unique clicks per campaign / Count all delivered x 100
The first part of the metric is the important and highlighted part on the screenshot. You must count all campaign events with action_type = email and status = clicked. In the last rule, you will pick the event expression from the second step and choose any item—equals = campaign > timestamp.
It will ensure that only one first click per campaign is considered, and you will have an actual global click rate for all emails.
Summary
With such a setup, you can make reliable reports for global open / click rates, as this logic only considers the first open or click per single email, which you need. Such reports can be drilled down by any period or without drilling down and just using a timeframe of the report.
You can distinguish the open or click rates by specifying different statuses in the 1st and 3rd steps.
It should help you construct such a report for your engagement project.