[Bug] Unique events ( opens, clicks, conversions) are being counted multiple times

[jaimealopez]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I run the table fivetran.iterable.campaign_metrics on snowflake to get me the opens, clicks, sent, delivered, conversions per campaign I have on Iterable.

I downloaded a CSV to make a dashboard on Google Sheets and noticed that column AB of the CSV or column "UNIQUE_EMAIL_OPENS" on snowflake counts the unique email opens per day of a specific campaign.

The problem with this is that we are counting unique email opens per day and not total unique email opens for the campaign. That is, on Day 1 we can be counting an instance where a user uniquely opened the email and then counting the same user uniquely opening the email on Day 7.

Iterable calculates their open rate as unique opens / total sends. See the data for Iterable Campaign ID 5972384 below, as reported by Iterable.

Cell K13 is way off that number. You can review this on the following google sheet by looking at the Campaign ID 5972384 on Welcome Series Email sheet.

This occurs throughout the rest of the csv and on other snowflake columns like UNIQUE_EMAIL_BOUNCED, UNIQUE_EMAIL_CLICKS, UNIQUE_EMAIL_OPENS_OR_CLICKS, UNIQUE_HOSTED_UNSUBSCRIBE_CLICKS, UNIQUE_PURCHASES, UNIQUE_PUSHES_BOUNCED, UNIQUE_PUSHES_OPENED, UNIQUE_PUSHES_SENT, UNIQUE_UNSUBSCRIBES, UNIQUE_EMAIL_OPENS_FILTERED_

Relevant error log or model output

No response

Expected behavior

to be reported on the UNIQUE_EMAIL_OPENS column, a open by a user must occur only once throughout the lifetime of the campaign reported.

dbt Project configurations

n/a

Package versions

fivetran/iterable

What database are you using dbt with?

snowflake

dbt Version

[“>=0.6.0”, “<0.7.0"]

Additional Context

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[fivetran-jamie]

hey there! so i think the issue here lies with this line

i wonder if changing it to the below would do the trick...

, count(distinct case when {{ col.name }} > 0 then user_email else null end) as {{ 'unique_' ~ col.name }}

We will fold this into our next sprint (starts in a week). But also feel free to try out/fiddle around with the above in the meantime if you'd like!