[Feature] Email Event Enrichment

rick1290 commented 9 months ago

Is there an existing feature request for this?

[X] I have searched the existing issues

Describe the Feature

Whenever an event enters as emailOpen or emailClicked - the channel_id, channel_name, etc. all come in as NULL values making it difficult to query. This data only comes in on the emailSent event. Additionally, the transactional_data that triggered these emails is only included on the emailSent event. So I need to end up joining the message_id on each emailClick and emailOpen event to enrich the columns with the data.

Describe alternatives you've considered

Somehow it would be great to include the transaction_data from the emailSent event on triggered emails inside the emailOpen and emailClick. I don't know the best approach to this... adding a whole new column: triggered_transaction_data OR appending the transaction_data of the emailClick and emailOpen.... open to ideas.

Are you interested in contributing this feature?

[X] Yes.
[X] Yes, but I will need assistance and will schedule time during your office hours for guidance.
[X] No.

Anything else?

Would love to see this added :)

fivetran-joemarkiewicz commented 8 months ago

Hi @rick1290 thank you for opening this feature request! It does sound like it would be beneficial to provide this enhancement in the package created end models so you may get the full picture of your event data. Before we dig into this further I have a few follow up questions so I may better understand the situation.

To confirm, are you saying in the end you are needing to perform an additional join on top of the iterable__events model? Or is there a different model you are leveraging when you encounter this data gap?
Would you be able to share a sample where you see the emailOpen and emailClick data coming in as null, but the relevant information is instead contained within the emailSent event data.
Do you see a similar behavior with other types of events in Iterable?
- Reason I ask is if we do need to add a new column or change the table then I would be curious how we could make an enhancement scalable for other types of events.
- I wonder if the concept of a "parent" event would useful. If the data does come through as null then we could coalesce to the parent event data from coming in as null.

rick1290 commented 7 months ago

@fivetran-joemarkiewicz Didn't see this message. Appreciate you following up. This would be a game changer if you can implement....

Correct
See CSV attached
I believe this would also include SMS events as well.

Really all we need is ---- when the message_id is the same - we need the transancation_data column to stay consistent with the data that triggered that event.

For instance, I need to create the below and join back up with iterable__events to get this data right now...

email_key as (

    select distinct(message_id), 
    -- JSON_EXTRACT_SCALAR(JSON_EXTRACT_SCALAR(additional_properties, "$.transactionalData"), "$.pns") as pns,
  JSON_EXTRACT_SCALAR(JSON_EXTRACT_SCALAR(transactional_data, '$'), '$.pns') AS pns
    from events
    where event_name like 'emailSend' and campaign_type like 'Triggered'
),

models_staging_iterable_stg_iterable__email_events.csv

fivetran-joemarkiewicz commented 5 months ago

Sorry for the delayed response @rick1290. The approach you shared makes sense to me and would be something we can explore adding in our next update to the Iterable package. Otherwise, if you would like to contribute a PR we would happily collaborate with you to introduce this feature! Thanks!

rick1290 commented 5 months ago

Seeing this in the next update would be awesome!!! Thanks Joe :)

fivetran / dbt_iterable