search

fivetran / dbt_mailchimp

Fivetran's Mailchimp dbt package
https://fivetran.github.io/dbt_mailchimp/
Apache License 2.0
3 stars 6 forks source link

[Bug] unsubscribes column empty in mailchimp__campaigns table #39

Closed abermanvirgil closed 1 year ago

abermanvirgil commented 1 year ago

Is there an existing issue for this?

Describe the issue

On the mailchimp__campaigns table, the column "unsubscribes" is equal to 0 for all my raws, it's wrong Don't know how to investigate

Relevant error log or model output

No response

Expected behavior

"unsubscribes" not equal to 0 for all rows

dbt Project configurations

name: 'dbt_etl' version: '1.0.0' config-version: 2

profile: 'dbt_etl'

model-paths: ["models"] analysis-paths: ["analyses"] test-paths: ["tests"] seed-paths: ["seeds"] macro-paths: ["macros"] snapshot-paths: ["snapshots"]

clean-targets: # directories to be removed by dbt clean

models: dbt_etl:

vars: mailchimp_using_automations: false # disable if you do not have the automation_email, automation_email, or automation_recipient_activity tables mailchimp_using_segments: false # disable if you do not have the segment table

Package versions

packages:

What database are you using dbt with?

bigquery

dbt Version

Core:

Plugins:

Additional Context

No response

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

fivetran-joemarkiewicz commented 1 year ago

Hi @abermanvirgil thanks for opening this issue and sharing that you are seeing no unsubscribes for your mailchimp__campaigns table. Before diving in, you mention that this is incorrect. Do you have an example campaign that should have unsubscribe events, but does not? This should help isolating what may be occurring and aid in troubleshooting.

I did noticed that there was an update to the connector this month that changed the sync strategy for the unsubscribes table and also updated the composite key to be a combination of the list_id and member_id. That being said, I don't believe this should have any impact on your data.

When investigating the code, the unsubscribes metric ultimately is generated within the int_mailchimp__campaign_activities_by_campaign model in the following line

https://github.com/fivetran/dbt_mailchimp/blob/8a23f23f4db6b9211d74add3183b2804e2808f18/models/intermediate/int_mailchimp__campaign_activities_by_campaign.sql#L15

This was_unsubscribed field is then generated as a result of the mailchimp__campaign_recipients model in the following line

https://github.com/fivetran/dbt_mailchimp/blob/8a23f23f4db6b9211d74add3183b2804e2808f18/models/mailchimp__campaign_recipients.sql#L61

To investigate further, I was wondering if you could explore the mailchimp__campaign_recipients model and let me know if you see any was_unsubscribed records that are true. If you do not, then I would want to investigate the source a bit further and confirm that the following query results in not null member_id fields.

    select 
        member_id,
        campaign_id
    from unsubscribes
    group by 1,2
abermanvirgil commented 1 year ago

Hi ! Thanks for your answer. I confirm you that was_unsubscribed is also set to false for every rows in mailchimp__campaign_recipients Moreover, your query (select member_id, campaign_id from unsubscribes group by 1,2) returns a lot of rows :) Thanks !

Le mer. 28 juin 2023 à 16:15, Joe Markiewicz @.***> a écrit :

Hi @abermanvirgil https://github.com/abermanvirgil thanks for opening this issue and sharing that you are seeing no unsubscribes for your mailchimp__campaigns table. Before diving in, you mention that this is incorrect. Do you have an example campaign that should have unsubscribe events, but does not? This should help isolating what may be occurring and aid in troubleshooting.

I did noticed that there was an update to the connector this month https://fivetran.com/docs/applications/mailchimp/changelog that changed the sync strategy for the unsubscribes table and also updated the composite key to be a combination of the list_id and member_id. That being said, I don't believe this should have any impact on your data.

When investigating the code, the unsubscribes metric ultimately is generated within the int_mailchimp__campaign_activities_by_campaign model in the following line

https://github.com/fivetran/dbt_mailchimp/blob/8a23f23f4db6b9211d74add3183b2804e2808f18/models/intermediate/int_mailchimp__campaign_activities_by_campaign.sql#L15

This was_unsubscribed field is then generated as a result of the mailchimp__campaign_recipients model in the following line

https://github.com/fivetran/dbt_mailchimp/blob/8a23f23f4db6b9211d74add3183b2804e2808f18/models/mailchimp__campaign_recipients.sql#L61

To investigate further, I was wondering if you could explore the mailchimp__campaign_recipients model and let me know if you see any was_unsubscribed records that are true. If you do not, then I would want to investigate the source a bit further and confirm that the following query results in no null member_id fields.

select
    member_id,
    campaign_id
from unsubscribes
group by 1,2

— Reply to this email directly, view it on GitHub https://github.com/fivetran/dbt_mailchimp/issues/39#issuecomment-1611497513, or unsubscribe https://github.com/notifications/unsubscribe-auth/BAZBZUO7NCP25GIB5FURGRTXNQ4B5ANCNFSM6AAAAAAZW3EN3M . You are receiving this because you were mentioned.Message ID: @.***>

fivetran-joemarkiewicz commented 1 year ago

Thanks @abermanvirgil for the quick reply. My apologies, do you see any results in the following query

    select 
        member_id,
        campaign_id
    from unsubscribes
    group by 1,2
   where member_id is not null

Do you see any records for with this query? The unsubscribe metric is generated by inspecting the unsubscribe source table and when it identifies a member_id associated with a campaign then it tracks as an unsubscribe. I would assume if you see only false in the mailchimp__campaign_recipients model, then the source table may have null member_ids.

abermanvirgil commented 1 year ago

Thanks Joe ! I see a lot of records with your latest query, with not null member_id Stay available if you have other tests to do :)

Le mer. 28 juin 2023 à 16:37, Joe Markiewicz @.***> a écrit :

Thanks @abermanvirgil https://github.com/abermanvirgil for the quick reply. My apologies, do you see any results in the following query

select
    member_id,
    campaign_id
from unsubscribes
group by 1,2

where member_id is not null

Do you see any records for with this query? The unsubscribe metric is generated by inspecting the unsubscribe source table and when it identifies a member_id associated with a campaign then it tracks as an unsubscribe. I would assume if you see only false in the mailchimp__campaign_recipients model, then the source table may have null member_ids.

— Reply to this email directly, view it on GitHub https://github.com/fivetran/dbt_mailchimp/issues/39#issuecomment-1611559962, or unsubscribe https://github.com/notifications/unsubscribe-auth/BAZBZUJ65DONJHMGMSKMVRTXNQ6RXANCNFSM6AAAAAAZW3EN3M . You are receiving this because you were mentioned.Message ID: @.***>

fivetran-joemarkiewicz commented 1 year ago

@abermanvirgil thanks for sharing and this is very interesting. If that is the case, then I assume this join is failing for you

https://github.com/fivetran/dbt_mailchimp/blob/8a23f23f4db6b9211d74add3183b2804e2808f18/models/mailchimp__campaign_recipients.sql#L63-L65

Are you able to associated any of these member_id/campaign_id combinations from the unsubscribes source to your source campaign data?

abermanvirgil commented 1 year ago

Thanks ! I just noticed that when I run dbt build, there is an error :

09:26:10 26 of 43 START test unique_stg_mailchimpmembers_member_id .................... [RUN] 09:26:12 26 of 43 FAIL 1594 unique_stg_mailchimpmembers_member_id ..................... [FAIL 1594 in 1.20s]

Completed with 1 error and 0 warnings: 09:26:45 09:26:45 Failure in test unique_stg_mailchimp__members_member_id (models/stg_mailchimp.yml) 09:26:45 Got 1594 results, configured to fail if != 0 09:26:45 09:26:45 compiled Code at target/compiled/mailchimp_source/models/stg_mailchimp.yml/unique_stg_mailchimp__members_member_id.sql

Do you know how to fix it ? Thanks

Le mer. 28 juin 2023 à 20:34, Joe Markiewicz @.***> a écrit :

@abermanvirgil https://github.com/abermanvirgil thanks for sharing and this is very interesting. If that is the case, then I assume this join is failing for you

https://github.com/fivetran/dbt_mailchimp/blob/8a23f23f4db6b9211d74add3183b2804e2808f18/models/mailchimp__campaign_recipients.sql#L63-L65

Are you able to associated any of these member_id/campaign_id combinations from the unsubscribes source to your source campaign data?

— Reply to this email directly, view it on GitHub https://github.com/fivetran/dbt_mailchimp/issues/39#issuecomment-1611894846, or unsubscribe https://github.com/notifications/unsubscribe-auth/BAZBZUNWGRXGHHDD6JGXXM3XNR2JRANCNFSM6AAAAAAZW3EN3M . You are receiving this because you were mentioned.Message ID: @.***>

fivetran-joemarkiewicz commented 1 year ago

Hi @abermanvirgil thanks for sharing, this test error is definitely eye opening. Your mailchimp unsubscribes table should not contain any nulls for member_id. I also confirmed this intended behavior with our product manager. This also informs me why you are seeing empty unsubscribes columns in your end model.

Unfortunately this is not something that we can solve at the transformation layer and will need to be explored and resolved within the Mailchimp connector itself. I would recommend opening a Support Ticket and sharing the fact that you are seeing null member_ids in your unsubscribes table for the Mailchimp connector. Our customer support and engineering teams will then be able to help you from there.

Ideally once this is resolved within the connector you will be able to see accurate data in the end model generated from this dbt package. As this is not something that can be resolved in the package, I will mark this as won't fix and can be resolved within the connector. I hope this helps!