[Bug] <Google Ads Manager Account with multiple accounts have null as account ID >

[ekawakami97]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

We recently connected our Google Ads manager account with multiple Google Ads accounts to our dbt project. We just changed the dbt_project.yml file and added the line google_ads_schema: google_ads_mccsince the source schema was named differently. After running the project, we saw that our project updated to have both of the accounts and had all the same values as our google ads account (spend, clicks, impressions, etc), however, our second google ads account we recently added has a null value for the account_id.

When running the command below, we get the following output.

SELECT 
    distinct account_id 
FROM dbt_ekawakami.ad_reporting_extended
where platform = 'Google Ads'

Relevant error log or model output

No response

Expected behavior

The expected output is for both of the account_ids to have the valid account IDs that appear on our Google Ads account. Currently we are hardcoding the account ID into our models as a temporary fix.

dbt Project configurations

# These are base configs for dbt.
# You probably won't need to change these!
name: 'vb_dbt'
version: '1.1.0'
config-version: 2
profile: 'vb_dbt'
model-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
seed-paths: ["data"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
target-path: "target"
clean-targets: ["target", "dbt_packages"]

# Add a dynamic query comment providing context of execution
query-comment:
  comment: "{{ query_comment(node) }}"
  append: true

# Require dbt 1.1.x
require-dbt-version: [">=1.1.0", "<1.2.0"]

# Variables
vars:
  api_source: google_ads

  shopify_database: fivetran-data-culture-big-vv9p
  shopify_schema: shopify

  klaviyo_database: fivetran-data-culture-big-vv9p
  klaviyo_schema: klaviyo

  klaviyo:
    klaviyo__email_attribution_lookback: 48

  segment:
    segment_page_views_table: "{{ source('shopify_littledata', 'pages') }}"

  shopify_source:
    # TODO: The status column is not supported by the package, adding
    # manually.
    product_pass_through_columns: ['status']
    order_line_pass_through_columns: ['property_acadaca_sku',
                                      'property_pre_order_date',
                                      'properties']

  google_ads_source:
    google_ads_database: fivetran-data-culture-big-vv9p
    google_ads_schema: google_ads_mcc
    google_auto_tagging_enabled: true  # False by default

  microsoft_ads_schema: bingads
  microsoft_ads_database: fivetran-data-culture-big-vv9p

  microsoft_ads_source:
    microsoft_auto_tagging_enabled: true  # False by default

  facebook_ads_database: fivetran-data-culture-big-vv9p

  # Fivetran Ad Reporting Package
  ad_reporting__pinterest_enabled: false
  ad_reporting__linkedin_ads_enabled: false
  ad_reporting__twitter_ads_enabled: false
  ad_reporting__snapchat_ads_enabled: false
  ad_reporting__tiktok_ads_enabled: false

  # Instagram Business Package
  instagram_business_schema: instagram_business
  instagram_business_database: fivetran-data-culture-big-vv9p

# Configuring snapshots
snapshots:
  +target_schema: snapshots
  +target_database: analytics-prod-318520

# Configuring models
models:
  vb_dbt:
    # persist resource descriptions as column
    # and relation comments in the database
    # https://docs.getdbt.com/reference/resource-configs/persist_docs
    +persist_docs:
      relation: true
      columns: true

    # materialize all models as tables by default unless otherwise specified.
    +materialized: table

    staging:
      +schema: staging
      +materialized: view

    utils:
      +schema: utils
      +materialized: table

    marts:
      core:
        intermediate:
          +schema: staging
      inventory:
        intermediate:
          +schema: staging
      marketing:
        intermediate:
          +schema: staging
          +materialized: view
          performance_marketing_aggregated:
            +materialized: table
          performance_marketing_campaign_mapping:
            +materialized: table

  # Configuring models from Fivetran's Klaviyo package
  klaviyo_source:
    +schema: staging

  klaviyo:
    +schema: klaviyo
    intermediate:
      +schema: staging

  # Configuring models from Fivetran's Shopify package
  shopify_source:
    +schema: staging
    +materialized: view

  shopify:
    +schema: staging

  # Configuring models from Fivetran's Google Ads package
  google_ads_source:
    +schema: staging
    +materialized: view

  google_ads:
    +schema: google_ads

  # Configuring models from Fivetran's Bing Ads package
  microsoft_ads_source:
    +schema: staging
    +materialized: view

  microsoft_ads:
    +schema: microsoft_ads

  # Configuring models from Fivetran's Facebook Ads package
  facebook_ads:
    +schema: facebook_ads
  facebook_ads_creative_history:
    +schema: facebook_ads
  facebook_ads_source:
    +schema: staging
    +materialized: view

  ## Ad Reporting Fivetran Package
  ad_reporting:
    +schema: staging

  # disable both pinterest models if not using pinterest ads
  pinterest:
    enabled: false
  pinterest_source:
    enabled: false

  # disable both linkedin ads models if not using linkedin ads
  linkedin:
    enabled: false
  linkedin_source:
    enabled: false

  # disable both twitter ads models if not using twitter ads
  twitter_ads:
    enabled: false
  twitter_ads_source:
    enabled: false

  # disable both snapchat ads models if not using snapchat ads
  snapchat_ads:
    enabled: false
  snapchat_ads_source:
    enabled: false

  # disable both tiktok ads models if not using tiktok ads
  tiktok_ads:
    enabled: false
  tiktok_ads_source:
    enabled: false

Package versions

packages:
  - package: calogica/dbt_date
    version: [">=0.5.0", "<0.6.0"]
  - package: dbt-labs/dbt_utils
    version: [">=0.8.0", "<0.9.0"]
  - package: dbt-labs/codegen
    version: [">=0.6.0", "<0.7.0"]
  - package: fivetran/shopify
    version: [">=0.6.0", "<0.7.0"]
  - package: fivetran/google_ads
    version: [">=0.6.0", "<0.7.0"]
  - package: fivetran/google_ads_source
    version: [">=0.6.0", "<0.7.0"]
  - package: fivetran/microsoft_ads
    version: [">=0.4.0", "<0.5.0"]
  - package: fivetran/facebook_ads
    version: [">=0.4.0", "<0.5.0"]
  - package: fivetran/ad_reporting
    version: [">=0.7.0", "<0.8.0"]
  - package: calogica/dbt_expectations
    version: [">=0.5.0", "<0.6.0"]
#  - package: fivetran/klaviyo
#    version: [">=0.4.0", "<0.5.0"]
  - package: dbt-labs/segment
    version: [">=0.8.0", "<0.9.0"]
  - package: fivetran/instagram_business
    version: [">=0.1.0", "<0.2.0"]

What database are you using dbt with?

bigquery

dbt Version

Core:
  - installed: 1.1.0
  - latest:    1.1.1 - 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.1.0 - Up to date!

Additional Context

No response

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

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

[fivetran-joemarkiewicz]

Hi @ekawakami97 thanks so much for opening this issue!

I have one quick question before we proceed further into the investigation (especially as I don't have much experience with the Google Ads Manager). When you query the raw data Fivetran syncs for this account do you see null records as well? I want to confirm if this is a package issue or a source issue.

[ekawakami97]

Hi @fivetran-joemarkiewicz, on the raw data synced from fivetran both accounts have valid account IDs

[fivetran-joemarkiewicz]

Thanks for the update @ekawakami97. To try and investigate a bit closer I am curious what you queried to see that both accounts are valid in the source. Would you be able to query the source ad_stats table within your google schema to see if both of these accounts are reflected there. The ad_reporting final model for Google pulls from the ad_stats table primarily.

If the accounts are both included within that table I would be curious what is happening. However, if they are not, then that may be the culprit.

[ekawakami97]

@fivetran-joemarkiewicz I can check the ad_stats table :) This is what I queried and it gave me valid customer_ids for both the accounts

SELECT 
  distinct customer_id 
FROM `fivetran-data-culture-big-vv9p.google_ads_mcc.ad_stats` 
LIMIT 1000

[fivetran-joemarkiewicz]

Hi @ekawakami97 thank you so much for sharing this info and sorry for the delay in my response.

I am currently looking into this issue and hope to get back to you later this week with more details! Thank you for your patience and I apologize for the strange behavior you are seeing here.

[fivetran-joemarkiewicz]

Hi all,

This issue should be resolved within the latest release of the package. As such, I will close out this issue.

Please feel free to reopen if you are still experiencing the issue following an upgrade or if there is more to discuss.