[Bug] Under-reporting of metrics for Nurture Campaigns (Or campaigns with multiple emails)

[sfc-gh-sugupta]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Our Market Intelligence team was recently working on revamping our email analytics dashboard. While working through the stakeholder requirements and QC'ing the data with Marketo numbers on front end, we noticed that our nurture campaign numbers were under-reported, drastically!
While digging deeper into this, we identified that the [rank partition] we create using the "concatenated" email_send_id in the Fivetran Marketo DBT package on github, that is resulting in incorrectly filtering the data out which we want to report on.
The issue is with the concatenated "email send id" which is combination of "campaign_id || ',' || campaign_run_id || ',' || lead_id as email_send_id. It seems the concatenation assumes that a program can have one or more campaigns with multiple runs but only one email since primary_attribute_value_id is not concatenated in the email_send_id. The current setup works when the emails are one-off emails but when they are nurture emails where we have multiple emails under one nurture campaign.
We think the fix to this is to also concatenate primary_attribute_value_id as part of the email_send_id, but happy to be work through the team to discuss the issue as well as the solution.

I have created the issue in this repo because once we fix the concatenated string, the rank partition should be able to report appropriately.

Let me know if you have any questions.

Relevant error log or model output

No response

Expected behavior

When we filter the data based on rank=1 partition, our expectations are that all activities after the first interaction is filtered out at the email level, while the current code filters all the activities at a campaign level. So if a campaign had multiple emails, say E1,E2,E3 with EMAIL_OPEN activity for each of the emails. With current set up, I only see E1 EMAIL_OPEN activity in the data and all other subsequent EMAIL_OPEN activity are filtered out because the ranking of activity happens are campaign level(with lead_id) and not on email level.

The straight-forward fix to this I believe is to include primary_attribute_value as part of the surrogate/concatenated key. I have tested the new code and it works as expected and QC'd the numbers with Marketo numbers too!

Open to suggestions and quick fix to this will be appreciated.

Thanks

dbt Project configurations

project name

name: 'marketing'
version: '0.0.1'

config-version: 2

default profile for project (profiles are configured in the ~/.dbt/profiles.yml file)

profile: 'default'

how to handle quoting

quoting:
identifier: false
schema: false

location of assets in project

model-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
seed-paths: ["data"]
macro-paths: ["macros"]
target-path: "target" # directory which will store compiled SQL files
clean-targets: # directories to be removed by dbt clean
- "target"
- "dbt_modules"

post-run commands to execute

models:
marketing:
post-hook:

tests:
+store_failures: true
+schema: dbt_tests
+severity: warn

Package versions

packages:

• package: fivetran/ad_reporting
  version: [">=0.7.0", "<0.8.0"]

What database are you using dbt with?

snowflake

dbt Version

1.1.0

Additional Context

No response

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-joemarkiewicz]

Hi @sfc-gh-sugupta thanks so much for opening this issue and providing great context into the bug you are experiencing. From what you have detailed above, I believe you are correct that this could be addressed by including the primary_attribute_value_id field within the concatenation.

I see you marked that you would be willing to open a PR. If this is still of interest to you then I would highly recommend applying the changes on a forked version of this package and testing to ensure this resolves the error on your end. Once it does, you can open the PR and my team and I will review the PR and let you know if we have any other questions before integrating into the next release of the package.

If not, my team will pick this up in a future sprint. If you do want to take on the PR, please feel free to let me know if you have any questions as I will be happy to help with any questions 😄

[fivetran-reneeli]

Hey @sfc-gh-sugupta , thank you so much for raising this issue and taking the time to investigate and provide so many details! We created a PR #26 for this and will be merging it within the coming week.

[fivetran-joemarkiewicz]

This issue has been addressed in PR #26 that has since been merged and is included in the next release.