search

fivetran / dbt_iterable

Fivetran transformation models for Iterable using dbt.
https://fivetran.github.io/dbt_iterable/
Apache License 2.0
3 stars 5 forks source link

[Feature] User Campaign First Open or Click Time #48

Open justin-fundrise opened 1 week ago

justin-fundrise commented 1 week ago

Is there an existing feature request for this?

Describe the Feature

We like to understand not only the time that a user was sent a communication from Iterable first_event_at, but we also like to know the time that they could have first saw the campaign. The reason we need this field is because we want to understand their behavior/actions that take place after the communication. Ex; are there any trends in behavior after a user receives a certain campaign. In order to do this, we take the user_campaign level data and join it with other 3rd party data in our warehouse and need to join based upon the time they first interacted with the communication. This can be done through either an open of the email (unreliable) or a click within the message (more accurate).

We've calculated this by taking the "First Open or Click Time" based upon those events only, similar to how this package is taking the first event time but we limit the event names we look at.

How would you implement this feature?

In the iterable__user_campaign model we can add a new field:

min(case when event_name in ('emailOpen', 'emailClick', 'pushOpen') then created_at end) as first_open_or_click_time,

Describe alternatives you've considered

An alternative would be for me to query the iterable__events model directly and join this data back onto iterable__user_campaign.

This approach would work, but it is not as efficient or clean as it would be do it within the iterable__user_campaign model.

Are you interested in contributing this feature?

Anything else?

Some examples of behavior that we track using the first open or click time. After seeing the communication did the user:

This can be useful not only for our use case, but also anyone else who is tracking user behavior based on communications.

fivetran-joemarkiewicz commented 1 week ago

@justin-fundrise thanks so much for the well documented feature request and for providing the PR to accompany it! I don't see any issue with adding this new calculated field to the end model. I also see this being something that others could benefit from as well.

We currently have a few other issues which we need to prioritize. However, I will make sure someone from my team reviews your PR and works to integrate this into an upcoming release. I imagine we will be able to get around to fully reviewing and integrating your contribution early next month. I'll let you know once we start taking a closer look and when we have a more clear timeline for when this will be able to be integrated into the live package.

For the time being, the alternative you mentioned should do the trick. Like you mentioned it will be better once this is neatly integrated into the code for all to access. Thanks so much for this contribution!