[Bug] <Keyword report and campaign report with search product different spends results>

[jkokatjuhhavoila]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

Hi hi, I believe the below queries should produce the same results:

SELECT  sum(spend) 
FROM `google_ads__campaign_report` 
where advertising_channel_type = 'SEARCH' and date_day between '2022-12-01' and '2022-12-31'

SELECT sum(spend) 
FROM `google_ads__keyword_report` 
where date_day between '2022-12-01' and '2022-12-31'

In our case we have a difference of ~1K. Could you please let me know what could cause the difference and/or why I should not expect those two queries to product the same results.

Thank you in advance.

Relevant error log or model output

No response

Expected behavior

Both above queries should produce the same result for the past days.

dbt Project configurations

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

This setting configures which "profile" dbt uses for this project.

profile: 'dev'

These configurations specify where dbt should look for different types of files.

The source-paths config, for example, states that models in this project can be

found in the "models/" directory. You probably won't need to change these!

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

target-path: "target" # directory which will store compiled SQL files clean-targets: # directories to be removed by dbt clean

• "target"
• "dbt_packages"

Package versions

packages:

• package: fivetran/facebook_ads version: [">=0.6.0", "<0.7.0"]
• package: fivetran/google_ads version: 0.9.0

What database are you using dbt with?

bigquery

dbt Version

Core:

• installed: 1.3.1

• latest: 1.3.2 - Update available!

  Your version of dbt-core is out of date! You can find instructions for upgrading here: https://docs.getdbt.com/docs/installation

Plugins:

• bigquery: 1.3.0 - Up to date!

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 @jkokatjuhhavoila thanks for opening this issue!

We actually uncovered the same nuance you identified when we were working through the v0.8.0 release of the package. The reason you are seeing this discrepancy is due to Google Ads service ads at different levels. For example, Google has a number of ad types (eg. Dynamic Search Ads, Performance Max, etc.) that are only served at the campaign level. Therefore, if you try to do aggregate sums across the different end models from this package (in your case keyword and campaign), you may not see the totals tie out.

This can be due to certain campaign ads not being served at the lower levels. You can verify this comparing the two end models and determine which campaigns are not populated in the keyword end model. I would be certain they are most likely these Performance Max, Dynamic Search, or similar ad types.

Let me know if this explanation makes sense as to why you will see this discrepancy.

[jkokatjuhhavoila]

Hi hi, thank you for a quick answer!

I am not sure I understand it completely. I am asking about advertising_channel_type = 'SEARCH'. I am not asking about Performance Max etc - as there I agree search keys don't appear there.

I still believe that spends for advertising_channel_type = 'SEARCH' from google_ads__campaign_report should match spend in google_ads__keyword_report.

In other months it does match, but for December it deviates by 1k.

[fivetran-joemarkiewicz]

Thanks for the details @jkokatjuhhavoila and providing more insight into the previous months matching in a way you expect.

Are you able to narrow down to what ads are causing that 1k discrepancy for December?

[jkokatjuhhavoila]

Yes, we were able to narrow them down, that are the Search campaigns.

[fivetran-joemarkiewicz]

Thanks for getting back @jkokatjuhhavoila!

Were these search campaigns present in previous months?

[jkokatjuhhavoila]

Yes, they were

[fivetran-joemarkiewicz]

These search campaign were present in previous months within the keywords report? My understanding is that search campaigns should not be showing in the keyword report.

I think it may be best if you schedule time with my team during an upcoming office hours to dig into this further.

[jkokatjuhhavoila]

Search campaigns that have different spends in December are present in previous months in google_adscampaign_report and in google_adskeyword_report. My understand is the contrary: search campaigns because they are being searched with the keywords and therefore should be present in the keyword report. Non-search campaigns should not be present in keyword report.

I will schedule the time :) Thank you!

[fivetran-joemarkiewicz]

Hi @jkokatjuhhavoila thanks again for working with me to understand the issue and address it with your contribution! 🎉

Your PR has since been merged and released within our v0.9.1 version of the package. You should see this version live on the dbt hub at the top of the hour. As this addresses the initial issue you identified, I will close the issue. Feel free to reopen if the issue continues to persist. Thanks again!