Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

When developing #84, I forgot to change the variable name in ad_reporting__url_report.

We decided, together with Joe, that we would keep the same variable name for both Ad and URL reports, but I never changed the variable name in the URL report (see models/ad_reporting__url_report.sql):

{{ fivetran_utils.persist_pass_through_columns(pass_through_variable='ad_reporting__url_passthrough_metrics', transform = 'sum') }}

Instead, it should be:

{{ fivetran_utils.persist_pass_through_columns(pass_through_variable='ad_reporting__ad_passthrough_metrics', transform = 'sum') }}

Relevant error log or model output

No response

Expected behavior

I would expect, when using the ad_reporting__ad_passthrough_metrics variable, for the model ad_reporting__url_report to include any metric specified in that variable when compiled.

dbt Project configurations

google_ads__ad_stats_passthrough_metrics:
    - name: conversions
linkedin_ads__creative_passthrough_metrics:
    - name: external_website_conversions
      alias: conversions
microsoft_ads__ad_passthrough_metrics:
    - name: conversions
facebook_ads__basic_ad_passthrough_metrics:
    - name: conversions
ad_reporting__ad_passthrough_metrics:
    - name: conversions

Package versions

- package: fivetran/ad_reporting
  version: [">=1.4.0", "<1.5.0"]

What database are you using dbt with?

snowflake

dbt Version

v1.5.2

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.

Are you a current Fivetran customer?
Andrew Woelfel, Analyst, Xometry

Describe the bug
Below should be the adjusted query for LinkedIn Campaigns query. Campaign groups can have multiple campaigns, so to match the aggregation of across other platforms, this should be switched. Below is documentation from LinkedIn noting this is the hierarchy. In the current state when used for reporting this hierarchy doesn't make any sense when multiple platforms are shown and causes confusion.

https://docs.microsoft.com/en-us/linkedin/marketing/integrations/ads/account-structure/create-and-manage-campaign-groups?tabs=http

select 'LinkedIn Ads' as platform, cast(date_day as date) as date_day, account_name, account_id, campaign_group_name as campaign_name, cast(campaign_group_id as {{ dbt_utils.type_string() }}) as campaign_id, ---Campaign Group can contain multiple campaigns campaign_name as ad_group_name, cast(campaign_id as {{ dbt_utils.type_string() }}) as ad_group_id, base_url, url_host, url_path, utm_source, utm_medium, utm_campaign, utm_content, utm_term, coalesce(clicks, 0) as clicks, coalesce(impressions, 0) as impressions, coalesce(cost, 0) as spend from base

Steps to reproduce
Adjust stg_linkedin_ads.sql fields CTE to the above query

Expected behavior
Switching campaign group and campaign will show correct hierarchy when comparing across every platform

Please indicate the level of urgency
Yes, this is blocking a release of a looker dashboard

Are you interested in contributing to this package?

• [ X] Yes, I can do this and open a PR for your review. YES

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Turning off the usage of Apple ads platform as stated in the package README doesn't actually turn off source freshness tests for the models, although the config is conditioned on the variable set to False

sources:
  - name: apple_search_ads
    schema: "{{ var('apple_search_ads_schema', 'apple_search_ads') }}"
    database: "{% if target.type != 'spark'%}{{ var('apple_search_ads_database', target.database) }}{% endif %}"

    loader: Fivetran
    loaded_at_field: _fivetran_synced

    freshness: 
      warn_after: {count: 48, period: hour}
      error_after: {count: 168, period: hour}

    config:
      enabled: "{{ var('ad_reporting__apple_search_ads_enabled', true) }}"

Relevant error log or model output

15:27:40  1 of 36 START freshness of apple_search_ads.search_term_report ................. [RUN]
15:27:41  1 of 36 ERROR freshness of apple_search_ads.search_term_report ................. [ERROR in 0.68s]

15:27:50  Runtime Error in source search_term_report (models/src_apple_search_ads.yml)
15:27:50    404 Not found: Dataset xxxxx:apple_search_ads was not found in location US
15:27:50    
15:27:50    (job ID: b656a397-d0c4-4f36-b651-898edfaf03cb)
15:27:50  
15:27:50  
15:27:50  Done.

Expected behavior

I would expect dbt not running source freshness tests for Apple because we are not creating those.

dbt Project configurations

vars:
  ad_reporting__apple_search_ads_enabled: False
  ad_reporting__pinterest_ads_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

  apple_search_ads__using_search_terms: 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.7.0", "<0.8.0"]
  - package: fivetran/shopify
    version: [">=0.6.0", "<0.7.0"]
  - package: fivetran/google_ads
    version: [">=0.8.0", "<0.9.0"]
  - package: fivetran/google_ads_source
    version: [">=0.8.0", "<0.9.0"]
  - package: fivetran/microsoft_ads
    version: [">=0.5.0", "<0.6.0"]
  - package: fivetran/facebook_ads
    version: [">=0.5.0", "<0.6.0"]
  - package: fivetran/ad_reporting
    version: [">=1.0.0", "<1.1.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"]
  - package: fivetran/fivetran_utils
    version: [">=0.3.0", "<0.4.0"]
  - package: dbt-labs/metrics
    version: [">=0.3.0", "<0.4.0"]

What database are you using dbt with?

bigquery

dbt Version

❯ dbt --version
Core:
  - installed: 1.2.0
  - latest:    1.3.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.2.0 - Update available!

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.

The latest version of fivetran/google_ads is 0.2.0. This version is not supported by fivetran/ad_reporting. Any plans to do so?

Are you a current Fivetran customer?
Yes. TOCC.

Describe the bug
You recently updated the dbt_ad_reporting package to v0.6.0. Installation instructions are:

packages:
  - package: fivetran/ad_reporting
    version: [">=0.6.0", "<0.7.0"]

Having added this to my packages.yml file and running dbt deps I get the following error:

12:37:04  Running with dbt=1.0.0
12:37:10  Encountered an error:
Version error for package dbt-labs/dbt_utils: Could not find a satisfactory version from options: ['=0.8.0', '>=0.6.2', '<0.8.0']

Is the above error an issue with my packages.yml file or is there some sort of dependency error in Fivetran's dbt packages?

Expected behavior
I expect ad_reporting to update to v0.6.0

Package Version

packages:
  - package: fivetran/ad_reporting
    version: [">=0.6.0", "<0.7.0"]
  - package: fivetran/zendesk_source
    version: [">=0.4.0", "<0.5.0"]
  - package: fivetran/zendesk
    version: 0.7.0
  - package: dbt-labs/codegen
    version: 0.4.0

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

My company is using Databricks' Unity Catalog as metastore. The Fivetran connectors are currently writing to the 'ingestion' catalog, and processed in the 'dev' or 'prod' catalog. Thus, we are required to change the database variables as described in the package documentation.

However, there seems to be a bug with the google_ads_source package that do not correctly change the default database variable from the target database to a custom ('ingestion' in our case). Changing the src_google_ads.yml in the google_ads_source package seems to solve the problem:

Before:
database: "{% if target.type not in ['spark', 'databricks'] %}{{ var('google_ads_database', target.database) }}{% endif %}"

After
`database: ingestion

Alternatively, changing it to the format used for the other x_ads_source packages also seems to be working fine
database: "{% if target.type != 'spark' %}{{ var('google_ads_database', target.database) }}{% endif %}"

Relevant error log or model output

No response

Expected behavior

The google_ads_source package seems to have logic for reading the database variable that differs from the other source packages.

dbt Project configurations

vars:
  facebook_ads_schema: facebook_ads
  facebook_ads_database: ingestion

  google_ads_schema: google_ads
  google_ads_database: ingestion

  linkedin_ads_schema: linkedin_ads
  linkedin_ads_database: ingestion

  apple_search_ads_schema: apple_search_ads
  apple_search_ads_database: ingestion

  ad_reporting__amazon_ads_enabled: False # by default this is assumed to be True
  ad_reporting__pinterest_ads_enabled: False # by default this is assumed to be True
  ad_reporting__microsoft_ads_enabled: False # by default this is assumed to be True
  ad_reporting__twitter_ads_enabled: False # by default this is assumed to be True
  ad_reporting__snapchat_ads_enabled: False # by default this is assumed to be True
  ad_reporting__tiktok_ads_enabled: False # by default this is assumed to be True
  ad_reporting__reddit_ads_enabled: False # by default this is assumed to be True

models:
  ad_reporting:
    +enabled: true
    +schema: mart

  facebook_ads:
    +enabled: true
    +schema: intermediate
  facebook_ads_source:
    +enabled: true
    +schema: staging

  google_ads_source:
    +enabled: true
    +schema: staging # leave blank for just the target_schema
  google_ads:
    +enabled: true
    +schema: intermediate # leave blank for just the target_schema

  linkedin:
    +enabled: true
    +schema: intermediate
  linkedin_source:
    +enabled: true
    +schema: staging

  apple_search_ads:
    +enabled: true
    +schema: intermediate
  apple_search_ads_source:
    +enabled: true
    +schema: staging

Package versions

1.3.1

What database are you using dbt with?

databricks

dbt Version

1.4.6

Additional Context

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.

Are you a current Fivetran customer?

Yes, Red Fox Analytics

Describe the bug

I cloned this repo today. I ran dbt deps, I edited my project.yml to only include the connectors that are in my profile/database I hit "dbt run". I got the following error.

Compilation Error
  dbt found two resources with the name "int__facebook_ads__carousel_media_prep". Since these resources have the same name,
  dbt will be unable to find the correct resource when ref("int__facebook_ads__carousel_media_prep") is used. To fix this,
  change the name of one of these resources:
  - model.facebook_ads_creative_history.int__facebook_ads__carousel_media_prep (models\postgres\int__facebook_ads__carousel_media_prep.sql)
  - model.facebook_ads_creative_history.int__facebook_ads__carousel_media_prep (models\bigquery\int__facebook_ads__carousel_media_prep.sql)

Steps to reproduce

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected behavior

Project variables configuration

name: 'ad_reporting'
profile: 'ad_reporting'
version: '0.5.1'
config-version: 2

require-dbt-version: [">=0.20.0"]

vars:
  ad_reporting__pinterest_enabled: false
  ad_reporting__microsoft_ads_enabled: true
  ad_reporting__linkedin_ads_enabled: false
  ad_reporting__google_ads_enabled: true
  ad_reporting__twitter_ads_enabled: false
  ad_reporting__facebook_ads_enabled: true
  ad_reporting__snapchat_ads_enabled: false

models:
  ad_reporting:
    +materialized: view
  
  pinterest:
    enabled: false
  pinterest_source:
    enabled: false
  
  microsoft_ads:
    enabled: true
  microsoft_ads_source:
    enabled: true
  
  linkedin:
    enabled: false
  linkedin_source:
    enabled: false
  
  twitter_ads:
    enabled: false
  twitter_ads_source:
    enabled: false
  
  facebook_ads:
    enabled: true
  facebook_ads_source:
    enabled: true
  facebook_ads_creative_history:
    enabled: true
  
  google_ads:
    enabled: true
  google_ads_source:
    enabled: true

  snapchat_ads:
    enabled: false
  snapchat_ads_source:
    enabled: false
|```

**Package Version**
<!---Copy and paste the contents of your `packages.yml` file below.-->
```yml
copy packages.yml here

Warehouse

• BigQuery
• Redshift
• Snowflake
• [X ] Postgres
• Databricks
• Other (provide details below)

Additional context

Screenshots

Please indicate the level of urgency

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

Are you a current Fivetran customer?
Oscar Lukersmith at SafetyCulture

Describe the bug
We have started receiving failed test results for unique_stg_google_ads__final_url_performance_final_url_performance_id

Completed with 1 error and 0 warnings:

Failure in test unique_stg_google_ads__final_url_performance_final_url_performance_id (models/stg_google_ads.yml)
  Got 81684 results, configured to fail if != 0

  compiled SQL at target/compiled/google_ads_source/models/stg_google_ads.yml/schema_test/unique_stg_google_ads__final_u_3521f620458941ffffe40e69979b6e54.sql

The unique key doesn't appear to be unique in the underlying table

Steps to reproduce
N/A

Expected behavior
For the test to pass, and to have a unique key

Project variables configuration

name: 'analytics'
version: '2.0.0'
config-version: 2


profile: 'default'


source-paths: ["models"]
analysis-paths: ["analysis"]
test-paths: ["tests"]
data-paths: ["data"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

vars:
  'dbt_date:time_zone': 'UTC'
  ad_reporting__pinterest_enabled: False
  ad_reporting__microsoft_ads_enabled: True
  ad_reporting__linkedin_ads_enabled: True
  ad_reporting__google_ads_enabled: True
  ad_reporting__twitter_ads_enabled: False
  ad_reporting__facebook_ads_enabled: True
  ad_reporting__snapchat_ads_enabled: False

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



models:
  +pre-hook: "{{ alter_quota(1000) }}"
  +post-hook: "{{ grant_permissions('select', 'ia', 'yes') }}"
  +bind: false
  analytics:
    exposure:
      tableau:
        +post-hook: "{{ grant_permissions('select', 'tableau') }}"
  
  # disable both pinterest models if not using pinterest ads
  pinterest:
    enabled: false
    
  pinterest_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 google ads models if not using google ads
  linkedin:
    enabled: true
  linkedin_source:
    enabled: true
   
  
  google_ads:
    enabled: true
  google_ads_source:
    enabled: true
  
  microsoft_ads:
    enabled: true
  microsoft_ads_source:
    enabled: true

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context

Screenshots

Please indicate the level of urgency
High Urgency

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Currently there are variables in all of the upstream platform specific ad reporting packages that allow for user defined metrics to be included in the end models. However, this same functionality is not available to persist through to the final roll up ad reporting models.

This feature is to explore creating a way (or a new variables) to allow these upstream passthrough variables to persist to the roll up models.

Describe alternatives you've considered

Making custom adjustments or doing further model builds following the dbt package models.

Are you interested in contributing this feature?

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

Anything else?

PR #84 is a great start at tackling this feature update.

Are you a Fivetran customer?
Yes.. Billy Neal, Sr. Director of Development

Is your feature request related to a problem? Please describe.
No problems, just a feature request.

Describe the solution you'd like
include account_name and account_id in the stg_facebook_ads model so it rolls up to the ad_reporting table

Describe alternatives you've considered
I can run this locally by directly modifying the package file models/stg_facebook-ads.sql

Additional context

Please indicate the level of urgency and business impact of this request
Somewhat urgent as it is holding up some account level reporting we are trying to surface.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this work implemented.
• [ x] No, I'd prefer if someone else did this. I don't have the time and/or don't know how to incorporate the changes necessary.

Are you a current Fivetran customer?
Chris Soule, Data Architect Interworks

Describe the bug
In the Ad_Reporting model there was no Account, AdGroup, or Campaign data in the table even though it is in the source.

Steps to reproduce

1. Target a Google Ad Connector
2. Run package
3. See Nulls in the Account, AdGroup, or Campaign fields

Expected behavior
Account, AdGroup, or Campaign data carried forward to the final Ad_Reporting model

Project variables configuration

copy dbt_project.yml here

Package Version

name: 'ad_reporting'
version: '0.5.0'
config-version: 2

Warehouse

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Additional context
In the get_ad_stats_columns macro in google_ads_source package a lot of columns didn't have an alias specified properly and instead used the staging column name as the alias name which didn't match with the source fields.
Example: AD_ID = ID in the source table
Data Error:

Original get_ad_stats_columns macro:

What is needs to be corrected:

Screenshots

Please indicate the level of urgency
Medium/High. Performing a POC

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this fixed.
• No, I'd prefer if someone else fixed this. I don't have the time and/or don't know what the root cause of the problem is.

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The documentation listed here indicates that the user must include the "linkedin_schema" and "linkedin_database" vars in dbt_project.dbt.

The correct vars are:

linkedin_ads_schema
linkedin_ads_database

Relevant error log or model output

No response

Expected behavior

Expected the ad models to build successfully

dbt Project configurations

vars:
prod_database: analytics

ad_reporting__apple_search_ads_enabled: False # by default this is assumed to be True
ad_reporting__pinterest_ads_enabled: False # by default this is assumed to be True
ad_reporting__twitter_ads_enabled: False # by default this is assumed to be True
ad_reporting__facebook_ads_enabled: False # by default this is assumed to be True
ad_reporting__snapchat_ads_enabled: False # by default this is assumed to be True
ad_reporting__tiktok_ads_enabled: False # by default this is assumed to be True

linkedin_schema: linkedin_ads
linkedin_database: source_raw

google_ads_schema: google_ads
google_ads_database: source_raw

microsoft_ads_schema: bingads
microsoft_ads_database: source_raw

Package versions

packages:

• package: fivetran/salesforce_formula_utils
  version: 0.6.4

• package: calogica/dbt_date
  version: 0.5.7

• package: fivetran/ad_reporting
  version: [">=1.0.0", "<1.1.0"]

What database are you using dbt with?

snowflake

dbt Version

1.0.1

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.

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Most of the ad platforms have the option to include geography data in the reporting API exports. This is often a very useful dimension for marketers.

Columns on the country/state/metro level would be excellent. If those columns were in the campaign report table all the better. If they needed to be their own table then that would be better than nothing. I'm sure some of the ad networks don't provide this info, in which case we should note that it will be null for those platforms.

Describe alternatives you've considered

The practical alternative now would be for marketers to spool up different campaigns for targeted geos and encode that in the utm_campaign string. This is far from ideal because it forces them to maintain parallel campaigns just to be able to differentiate geos.

Are you interested in contributing this feature?

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

Anything else?

No response

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

DBT compilation error when run in Fivetran dbt Core transformation.
Code is executed successfully in local machine

Relevant error log or model output

21:06:55  Encountered an error:
'<' not supported between instances of 'NoneType' and 'int'
21:06:55  Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/dbt/cli/requires.py", line 91, in wrapper
    result, success = func(*args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/dbt/cli/requires.py", line 76, in wrapper
    return func(*args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/dbt/cli/requires.py", line 169, in wrapper
    return func(*args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/dbt/cli/requires.py", line 198, in wrapper
    return func(*args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/dbt/cli/requires.py", line 245, in wrapper
    return func(*args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/dbt/cli/requires.py", line 271, in wrapper
    ctx.obj["manifest"] = parse_manifest(
  File "/usr/local/lib/python3.8/dist-packages/dbt/parser/manifest.py", line 1790, in parse_manifest
    manifest = ManifestLoader.get_full_manifest(
  File "/usr/local/lib/python3.8/dist-packages/dbt/parser/manifest.py", line 318, in get_full_manifest
    manifest = loader.load()
  File "/usr/local/lib/python3.8/dist-packages/dbt/parser/manifest.py", line 478, in load
    self.parse_project(
  File "/usr/local/lib/python3.8/dist-packages/dbt/parser/manifest.py", line 674, in parse_project
    parser.parse_file(block)
  File "/usr/local/lib/python3.8/dist-packages/dbt/parser/base.py", line 483, in parse_file
    self.parse_node(file_block)
  File "/usr/local/lib/python3.8/dist-packages/dbt/parser/base.py", line 436, in parse_node
    config: ContextConfig = self.initial_config(fqn)
  File "/usr/local/lib/python3.8/dist-packages/dbt/parser/base.py", line 397, in initial_config
    config_version = min([self.project.config_version, self.root_project.config_version])
TypeError: '<' not supported between instances of 'NoneType' and 'int'

Expected behavior

Run without compilation erros

dbt Project configurations

# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'fivetran_dbt'
version: "1.0"

# This setting configures which "profile" dbt uses for this project.
profile: 'fivetran_dbt'

# These configurations specify where dbt should look for different types of files.
# The `model-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"]

clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models
vars:
  ad_reporting__amazon_ads_enabled: False # by default this is assumed to be True
  ad_reporting__apple_search_ads_enabled: False # by default this is assumed to be True
  ad_reporting__facebook_ads_enabled: True # by default this is assumed to be True
  ad_reporting__google_ads_enabled: True # by default this is assumed to be True
  ad_reporting__linkedin_ads_enabled: False # by default this is assumed to be True
  ad_reporting__microsoft_ads_enabled: False # by default this is assumed to be True
  ad_reporting__pinterest_ads_enabled: False # by default this is assumed to be True
  ad_reporting__reddit_ads_enabled: False # by default this is assumed to be True
  ad_reporting__snapchat_ads_enabled: False # by default this is assumed to be True
  ad_reporting__tiktok_ads_enabled: False # by default this is assumed to be True
  ad_reporting__twitter_ads_enabled: False # by default this is assumed to be True

Package versions

packages:

• package: fivetran/ad_reporting
  version: [">=1.7.0", "<1.8.0"] # we recommend using ranges to capture non-breaking changes automatically

What database are you using dbt with?

databricks

dbt Version

1.8.1

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.

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I'm attempting to set up the ad reporting model package in Fivetran, but I'm encountering issues getting it to execute. While it works smoothly in my local environment, the same error persists when I try to run it in Fivetran.

Relevant error log or model output

The node semantic_model.ad_reporting.ad_report not found

Expected behavior

Create ad reporting models and schedule them in fivetran transformations screen

dbt Project configurations

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

profile: 'fivetran_ad_reports'

vars:
  amazon_ads_schema: amz_ads_schema
  amazon_ads_database: ad_db
  facebook_ads_schema: facebookads_schema
  facebook_ads_database: ad_db
  google_ads_schema: googleads_schema
  google_ads_database: ad_db

  microsoft_ads_schema: bingads_schema
  microsoft_ads_database: ad_db
  pinterest_schema: pinterestads_schema
  pinterest_database: ad_db
  tiktok_ads_schema: tiktokads_schema
  tiktok_ads_database: ad_db

  
  ad_reporting__apple_search_ads_enabled: False
  ad_reporting__linkedin_ads_enabled: False
  ad_reporting__reddit_ads_enabled: False
  ad_reporting__snapchat_ads_enabled: False
  ad_reporting__twitter_ads_enabled: False


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

clean-targets:    
  - "target"
  - "dbt_packages"

models:  
  ad_reporting:
    +schema:

  amazon_search_ads:
    +schema:
  amazon_ads_source:
    +schema:
  
  facebook_ads:
    +schema:
  facebook_ads_source:
    +schema:
  
  google_ads:
    +schema:
  google_ads_source:
    +schema:

  microsoft_ads:
    +schema:
  microsoft_ads_source:
    +schema:

  pinterest:
    +schema:
  pinterest_source:
    +schema:
  
  tiktok_ads:
    +schema:
  tiktok_ads_source:
    +schema:

Package versions

packages:

• package: fivetran/ad_reporting
  version: 1.7.0

What database are you using dbt with?

bigquery

dbt Version

1.7.3

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.

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Because of the required joins detailed in this package, the requirement for ad group level details means that the new Google Pmax campaigns are lost early on in this model and consequently don't make it to the reporting layer.

because of the nature of this new ML assisted campaign type, it doesn't produce data in all the api tables the same way other campaign types have in the past.

Can we develop a parameter on setup to not use the ad group level details and keep to campaign level only?

Describe alternatives you've considered

short of starting an attempt to re-designing the package from scratch to fit our business need, we haven't explored any other options.

We have some time booked in with Joe already to discuss this issue

Are you interested in contributing this feature?

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

Anything else?

No response

Are you a Fivetran customer?

Fivetran created PR, but inspired by PR #33

Is your feature request related to a problem? Please describe.

This is not a problem, but rather an enhancement to the package to integrate the respective ad_type of the platforms to the roll up model.

Describe the solution you'd like

All ad platforms integrate the relevant ad_type to the final reporting model.

Describe alternatives you've considered

N/A

Additional context

PR #33 is a great starting point for how we will want to integrate this within the package. However, we need to add these fields at the base packages first.

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Context
Current the Ad Reporting package only allows you to use one connector per source. However, there are situations in which customers have multiple connectors for a particular source -- for example, I can have my Fivetran account attached to two separate Facebook Ads account, each with their own connector.

The Ask
I would like the ability to include multiple connectors for a single source within the Ad Reporting package.

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

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

Anything else?

No response

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

DBT unable to compile project due to below error,

Server error: RPC server failed to compile project, call the "status" method for compile status: Compilation Error
Could not render {{ metric('spend') }} / {{ metric('clicks') }}: 'metric' is undefined

Running DBT cloud
Packages versions:

• package: fivetran/ad_reporting
  version: [">=1.0.0", "<1.1.0"]

Relevant error log or model output

upon DBT DEPS command:
Installing fivetran/netsuite
09:04:48    Installed from version 0.6.0
09:04:48    Updated version available: 0.6.2
09:04:48  Installing fivetran/ad_reporting
09:04:51    Installed from version 1.0.1
09:04:51    Up to date!
09:04:51  Installing fivetran/fivetran_log
09:04:53    Installed from version 0.6.3
09:04:53    Up to date!
09:04:53  Installing fivetran/netsuite_source
09:04:56    Installed from version 0.5.1
09:04:56    Up to date!
09:04:56  Installing fivetran/apple_search_ads
09:04:57    Installed from version 0.1.0
09:04:57    Up to date!
09:04:57  Installing fivetran/snapchat_ads
09:04:58    Installed from version 0.4.0
09:04:58    Up to date!
09:04:58  Installing fivetran/facebook_ads
09:04:59    Installed from version 0.5.0
09:04:59    Up to date!
09:04:59  Installing fivetran/google_ads
09:05:01    Installed from version 0.8.0
09:05:01    Up to date!
09:05:01  Installing fivetran/pinterest
09:05:02    Installed from version 0.6.0
09:05:02    Up to date!
09:05:02  Installing fivetran/linkedin
09:05:03    Installed from version 0.5.0
09:05:03    Up to date!
09:05:03  Installing fivetran/microsoft_ads
09:05:04    Installed from version 0.5.1
09:05:04    Up to date!
09:05:04  Installing fivetran/tiktok_ads
09:05:05    Installed from version 0.2.1
09:05:05    Up to date!
09:05:05  Installing fivetran/twitter_ads
09:05:06    Installed from version 0.5.0
09:05:06    Up to date!
09:05:06  Installing fivetran/fivetran_utils
09:05:07    Installed from version 0.3.9
09:05:07    Updated version available: 0.4.0
09:05:07  Installing dbt-labs/spark_utils
09:05:08    Installed from version 0.3.0
09:05:08    Up to date!
09:05:08  Installing fivetran/apple_search_ads_source
09:05:10    Installed from version 0.1.2
09:05:10    Up to date!
09:05:10  Installing fivetran/snapchat_ads_source
09:05:11    Installed from version 0.4.0
09:05:11    Up to date!
09:05:11  Installing fivetran/facebook_ads_source
09:05:13    Installed from version 0.5.0
09:05:13    Up to date!
09:05:13  Installing fivetran/google_ads_source
09:05:14    Installed from version 0.8.0
09:05:14    Up to date!
09:05:14  Installing fivetran/pinterest_source
09:05:16    Installed from version 0.6.0
09:05:16    Up to date!
09:05:16  Installing fivetran/linkedin_source
09:05:17    Installed from version 0.5.0
09:05:17    Up to date!
09:05:17  Installing fivetran/microsoft_ads_source
09:05:19    Installed from version 0.6.0
09:05:19    Up to date!
09:05:19  Installing fivetran/tiktok_ads_source
09:05:20    Installed from version 0.2.0
09:05:20    Up to date!
09:05:20  Installing fivetran/twitter_ads_source
09:05:21    Installed from version 0.5.0
09:05:21    Up to date!
09:05:21  Installing dbt-labs/dbt_utils
09:05:28    Installed from version 0.8.6
09:05:28    Updated version available: 0.9.2
09:05:28  Installing calogica/dbt_expectations
09:05:31    Installed from version 0.5.8
09:05:31    Updated version available: 0.7.0
09:05:31  Installing calogica/dbt_date
09:05:32    Installed from version 0.5.7
09:05:32    Updated version available: 0.6.2
09:05:32  
09:05:32  Updates available for packages: ['fivetran/netsuite', 'fivetran/fivetran_utils', 'dbt-labs/dbt_utils', 'calogica/dbt_expectations', 'calogica/dbt_date']                 
Update your versions in packages.yml, then run dbt deps
Compilation Error
  Could not render {{ metric('spend') }} / {{ metric('clicks') }}: 'metric' is undefined

Expected behavior

expecting project to compile and allow for editing within DBT cloud IDE

dbt Project configurations

`

Name your project! Project names should contain only lowercase characters

and underscores. A good package name should reflect your organization's

name or the intended use of these models

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

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

profile: 'default'

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_{{ env_var('HOSTNAME') }}" # directory which will store compiled SQL files
clean-targets: # directories to be removed by dbt clean

• "target_{{ env_var('HOSTNAME') }}"
• "dbt_packages"

Configuring models

Full documentation: https://docs.getdbt.com/docs/configuring-models

In this example config, we tell dbt to build all models in the example/ directory

as tables. These settings can be overridden in the individual model files

using the {{ config(...) }} macro.

models:

elvie_bi:

stg_amazon:
  schema: stg_amazon
  materialized: table

stg_netsuite:
  schema: stg_netsuite
  materialized: table

stg_finance:
  schema: stg_finance
  materialized: table
  
stg_hierarchies:
  schema: stg_hierarchies
  materialized: table

stg_operations:
  schema: stg_operations
  materialized: table

reporting_marketing:
  schema: reporting_marketing
  materialized: table

reporting_trading:
  schema: reporting_trading
  materialized: table

netsuite:
+schema: reporting_trading
netsuite_source:
+schema: stg_netsuite

ad_reporting:
+schema: reporting_marketing

facebook_ads:
+schema: stg_facebook_ads
facebook_ads_source:
+schema: stg_facebook_ads
facebook_ads_creative_history:
+schema: stg_facebook_ads

google_ads:
+schema: stg_google_ads
google_ads_source:
+schema: stg_google_ads

fivetran_log:
+schema: reporting_bi # leave blank for just the target_schema

#ADS MODELS

# disable both pinterest models if not using pinterest ads

pinterest:

enabled: false

pinterest_source:

enabled: false

# disable both microsoft ads models if not using microsoft ads

microsoft_ads:

enabled: false

microsoft_ads_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

vars:
netsuite_data_model: netsuite2 #netsuite by default
netsuite_schema: raw_netsuite

netsuite_source:

# netsuite_database: your_database_name

# netsuite_schema: src_netsuite

netsuite_schema: netsuite_suiteanalytics

#ADS MODEL
ad_reporting__apple_search_ads_enabled: False
ad_reporting__pinterest_ads_enabled: False
ad_reporting__microsoft_ads_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

facebook_ads_schema: raw_facebook_ads

google_ads_schema: raw_google_ads

#Fivetran Log DBT MODEL
fivetran_log_schema: raw_fivetran_log
fivetran_log_using_transformations: false # this will disable all transformation + trigger_table logic
fivetran_log_using_triggers: false # this will disable only trigger_table logic

fivetran_log_using_account_membership: false # this will disable only the account membership logic

fivetran_log_using_destination_membership: false # this will disable only the destination membership logic

fivetran_log_using_user: false # this will disable only the user logic

fivetran_log_using_sync_alert_messages: false # this will disable only the sync alert messages within the connector status model`

Package versions

packages:

• package: fivetran/netsuite
  version: 0.6.0

• package: fivetran/ad_reporting
  version: [">=1.0.0", "<1.1.0"]

• package: fivetran/fivetran_log
  version: 0.6.3

What database are you using dbt with?

bigquery

dbt Version

Unable to perform this request as project cannot compile

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.

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

There are 3 ad sources for now, they are:

1. Google ads
2. Facebook ads
3. LinkedIn ads

I am trying a build a query for reporting purposes that shows:

1. Video impressions
2. Video views

break down by:

1. date
2. UTM Source
3. UTM Medium
4. UTM Campaign
5. Ad Group Name
6. Campaign Name

As an analogy, it is similar to what the ad reporting model achieves for image campaigns but the data is needed for video kind of campaigns.

Please help me with a query that can achieve the same on a priority basis.

If this seems to be a good addition to the reporting from a generalized perspective, then you can then think of adding to the future releases of this package.

Describe alternatives you've considered

I am struggling to come with a query first.

1. I couldn't think of any alternative at the moment
2. Even after looking at the Fivetran ERD diagram for these 3 ads, I am unable to come up with a query to serve the purpose that I am trying to achieve

Are you interested in contributing this feature?

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

Anything else?

No response

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

We're running dbt Cloud Versionless in our dev and prod environments, and yesterday we have started getting the metricflow_time_spine related error that does not allow us to run any run/compile/build commands.

We have fivetran ad_reporting package that seems to have a semantic model and generates metricflow_time_spine inside.

We don’t have a spine model in our own project and don’t specify it.

Relevant error log or model output

Running dbt...
22:13:53 Time spines without YAML configuration are in the process of deprecation. Please add YAML configuration for your 'metricflow_time_spine' model. See documentation to configure: https://docs.getdbt.com/docs/build/metricflow-time-spine
22:13:54 Encountered an error:
Object of type date is not JSON serializable

Expected behavior

The models should continue building.

dbt Project configurations

vars:
ad_reporting__amazon_ads_enabled: False # by default this is assumed to be True
ad_reporting__apple_search_ads_enabled: True # by default this is assumed to be True
ad_reporting__pinterest_ads_enabled: False # by default this is assumed to be True
ad_reporting__microsoft_ads_enabled: False # by default this is assumed to be True
ad_reporting__linkedin_ads_enabled: True # by default this is assumed to be True
ad_reporting__google_ads_enabled: True # by default this is assumed to be True
ad_reporting__twitter_ads_enabled: False # by default this is assumed to be True
ad_reporting__facebook_ads_enabled: False # by default this is assumed to be True
ad_reporting__snapchat_ads_enabled: False # by default this is assumed to be True
ad_reporting__tiktok_ads_enabled: False # by default this is assumed to be True
ad_reporting__reddit_ads_enabled: False
vars:
'dbt_date:time_zone': 'America/Chicago'

Package versions

packages:

• package: fivetran/app_reporting # https://github.com/fivetran/dbt_app_reporting
  version: 0.3.2

• package: fivetran/ad_reporting # https://github.com/fivetran/dbt_ad_reporting
  version: 1.9.0

What database are you using dbt with?

snowflake

dbt Version

2024.8.221

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.

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The microsoft_ads__keyword_report model uses the daily keyword report as its base and left joins to the keyword history table on keyword_id. There is a subsequent "not null" test for keyword ID, which can fail (since keyword history is left joined).

It is possible for keyword_id values to exist in the daily report and not in the keyword history table. This happens when a keyword is deleted (keyword_status = 'Deleted' in the daily table).

For Example:

I've investigated this issue and have come up with two solutions:

1. Add a filter in microsoft_ads__keyword_report (this requires a change to get_keyword_daily_report_columns.sql and stg_microsoft_ads__keyword_daily_report_tmp.sql): where report.keyword_status != 'Deleted'

2. Alternatively, change microsoft_ads__keyword_report to inner join with keyword history. This is the simplest, and imo preferred solution. As you can see, this inner join will only exclude deleted records:

Relevant error log or model output

test failures in multiple keyword models

Expected behavior

tests pass without failure

dbt Project configurations

vars:
prod_database: analytics
prod_schema: business
operdb_schema_pattern: 'operdb%'
raw_data_db: GONG
"dbt_date:time_zone": "America/Los_Angeles"
timezone_constant: "America/Los_Angeles"

ad_reporting__apple_search_ads_enabled: False # by default this is assumed to be True
ad_reporting__pinterest_ads_enabled: False # by default this is assumed to be True
ad_reporting__twitter_ads_enabled: False # by default this is assumed to be True
ad_reporting__facebook_ads_enabled: False # by default this is assumed to be True
ad_reporting__snapchat_ads_enabled: False # by default this is assumed to be True
ad_reporting__tiktok_ads_enabled: False # by default this is assumed to be True

linkedin_ads_schema: linkedin_ads
linkedin_ads_database: source_raw

google_ads_schema: google_ads
google_ads_database: source_raw

microsoft_ads_schema: bingads
microsoft_ads_database: source_raw

Package versions

packages:

• package: fivetran/salesforce_formula_utils
  version: 0.6.4

• package: calogica/dbt_date
  version: 0.5.7

• package: fivetran/ad_reporting
  version: [">=1.0.0", "<1.1.0"]

What database are you using dbt with?

snowflake

dbt Version

1.0.1

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.

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

This section (https://github.com/fivetran/dbt_ad_reporting/tree/v0.7.0/#google-ads-and-adwords-api-configuration) needs updated to reflect the changes to Google's APIs.

Relevant error log or model output

N/A

Expected behavior

Google Ads API is the only API to worry about.

dbt Project configurations

N/A

Package versions

N/A

What database are you using dbt with?

other (mention it in "Additional Context")

dbt Version

N/A

Additional Context

https://fivetran.slack.com/archives/CAGK0BU0Z/p1653080137055409

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.

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

In this intermediate model: https://github.com/fivetran/dbt_ad_reporting/blob/v1.2.0/models/intermediate/int_ad_reporting__url_report.sql

Snapchat_ads contains a series of field mappings that are set to null. This is preventing these fields from populating in the model output.
`snapchat_ads as (

{{ get_query(
    platform='snapchat_ads', 
    report_type='url', 
    field_mapping={
            'account_id': 'ad_account_id',
            'account_name': 'ad_account_name',
            'campaign_id': 'null',
            'campaign_name': 'null',
            'ad_group_id': 'null',
            'ad_group_name': 'null',
            'clicks':'swipes'
        },
    relation=ref('snapchat_ads__url_report')
) }}

), `

I believe this is an error as other mappings do not contain null values.

Relevant error log or model output

No response

Expected behavior

I expect these to map to the correct field in the source table

dbt Project configurations

N/A

Package versions

N/A

What database are you using dbt with?

other (mention it in "Additional Context")

dbt Version

N/A

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.

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Add custom schema config for the ad_reporting model

Relevant error log or model output

No response

Expected behavior

README is consistent

dbt Project configurations

N/A

Package versions

N/A

What database are you using dbt with?

postgres, bigquery

dbt Version

N/A

Additional Context

N/A

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.

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Customers have been asking about a specific revenue metric for each of the ad reporting connectors they leverage. I realize we add different query granularity columns such campaign name/ID, ad group name/ID, account name/ID — so we'd really just need a metric in addition to the already present spend, clicks, impressions which could capture the return on ad spend (i.e. revenue) somehow.

Quote from customer:

As you know with each marketing channel we're looking for some pretty simple metrics (e.g. spend, impressions, clicks on ads). In addition to some of those, we really need to start pulling in revenue per campaign from each of the connectors we leverage.

Context: I have worked with some existing Powered by Fivetran customers who does not use dbt in their data stack and so they have a bunch of in-house Python scripts which run the transformations to achieve cross-platform analysis for spend, clicks, impressions, etc. I think having revenue would influence this customer (and future ones) to simply setup Fivetran Transformations and leverage Data Models.

Describe alternatives you've considered

Going to work with customer to create the report configurations and/or metadata tables which have a revenue-like metric to pull into their existing ELT pipelines via Python.

Are you interested in contributing this feature?

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

Anything else?

N/A

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The default value for twitter_ads__using_keywords is False in some places and True in others. This runs fine if a value for the variable is provided but causes an error if no value is provided since the defaults conflict.

According to the README, the defaults should all be True, but making them all False would also work I assume.

See from int_ad_reporting__keyword_report:

Relevant error log or model output

No response

Expected behavior

Runs without errors if no value is provided for twitter_ads__using_keywords.

dbt Project configurations

Value for twitter_ads__using_keywords deliberately not provided.

Package versions

packages:

• package: fivetran/ad_reporting
  version: [">=1.0.0", "<1.1.0"]

What database are you using dbt with?

bigquery

dbt Version

1.3.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.

While running v0.2.1 I can't seem to get past this error:

$ dbt test
Running with dbt=0.19.1
Encountered an error:
Compilation Error
  dbt found two resources with the name "stg_facebook_ads__carousel_media_url_tags". Since these resources have the same name,
  dbt will be unable to find the correct resource when ref("stg_facebook_ads__carousel_media_url_tags") is used. To fix this,
  change the name of one of these resources:
  - model.facebook_ads_creative_history.stg_facebook_ads__carousel_media_url_tags (models/redshift/stg_facebook_ads__carousel_media_url_tags.sql)
  - model.facebook_ads_creative_history.stg_facebook_ads__carousel_media_url_tags (models/spark/stg_facebook_ads__carousel_media_url_tags.sql)

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

I am trying to install the ad reporting model package in fivetran and I have not been able to get it to run.
It runs fine in my local environment, but once I try to run it in fivetran I get the same error.

Details:

• dbt configuration passed all checks

• package: fivetran/ad_reporting
  version: 1.7.0

dbt version:
1.6.6

Error:
The following dbt command:

dbt compile
failed during the Manifest Generation process.

The failure details:

Exit code: 2

Output:

07:46:41 Running with dbt=1.6.6
07:46:42 Registered adapter: snowflake=1.6.4
07:46:42 Unable to do partial parsing because saved manifest not found. Starting full parse.
07:47:06 Encountered an error:
Parsing Error
Invalid metrics config given in FilePath(searched_path='models', relative_path='ad_reporting_metrics.yml', modification_time=1674507072.0, project_root='/tmp/dbt/project/dbt_packages/ad_reporting')

The following internal error occurred during the Manifest Generation process:

The 'manifest.json' artifact file not found after executing the command generating the manifest.

Relevant error log or model output

No response

Expected behavior

Deploy ad reporting model

dbt Project configurations

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

# This setting configures which "profile" dbt uses for this project.
profile: 'ad_model'

# These configurations specify where dbt should look for different types of files.
# The `model-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"]

clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the example/
# directory as views. These settings can be overridden in the individual model
# files using the `{{ config(...) }}` macro.
models:
  ad_model:
    # Config indicated by + and applies to all files under models/example/
    example:
      +materialized: view
vars:
    facebook_ads_schema: FACEBOOK_ADS
    facebook_ads_database: FIVETRAN_DATABASE

    google_ads_schema: GOOGLE_ADS
    google_ads_database: FIVETRAN_DATABASE 

    microsoft_ads_schema: BINGADS
    microsoft_ads_database: FIVETRAN_DATABASE

    linkedin_ads_schema: LINKEDIN_ADS 
    linkedin_ads_database: FIVETRAN_DATABASE  

    twitter_ads_schema: TWITTER_ADS
    twitter_ads_database: FIVETRAN_DATABASE 

vars:
  ad_reporting__amazon_ads_enabled: False # by default this is assumed to be True
  ad_reporting__apple_search_ads_enabled: False # by default this is assumed to be True
  ad_reporting__pinterest_ads_enabled: False # by default this is assumed to be True
  ad_reporting__reddit_ads_enabled: False # by default this is assumed to be True
  ad_reporting__snapchat_ads_enabled: False # by default this is assumed to be True
  ad_reporting__tiktok_ads_enabled: False # by default this is assumed to be True

Package versions

package: fivetran/ad_reporting
version: 1.7.0

What database are you using dbt with?

snowflake

dbt Version

1.6.6

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.

Hi there, I'm wondering what the thinking is on making this package incremental in nature. Is this a realistic endeavour? To what extend will it scale to re-compute the consolidated tables and views with every run? I'm wondering what the experiences are so far and if someone has operated this at scale?
many thanks,
Christoph.

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

We are facing a facebook_ads issue when we try to build new facebook_ads dbt transformations in a new environment or instance. Because of this issue, we were blocked to build the full ad_reporting model of version 0.7.0 in the new environment which we created.

We tried to find the root cause for this but we couldn't find the actual issue.

Here is the actual error that we are getting when we tried to create Facebook_ads transformations in schema or instance or environment.
Here is the actual error that we are getting when we tried to create Facebook_ads transformations in schema or instance or environment.

Error message -
Completed with 1 error and 0 warnings:
 Runtime Error in model facebook_ads__creative_history_prep (models\intermediate\facebook_ads__creative_history_prep.sql)
 404 Not found: Dataset gcp-ftd-bi-sandbox:bi_chandu_practice_v3_facebook_ads_creative_history was not found in location US

Relevant error log or model output

03:21:04  Found 147 models, 117 tests, 0 snapshots, 0 analyses, 785 macros, 0 operations, 0 seed files, 117 sources, 0 exposures, 0 metrics
03:21:04  
03:21:08  Concurrency: 16 threads (target='dev')
03:21:08  
03:21:08  1 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__account_history_tmp  [RUN]
03:21:08  2 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_history_tmp  [RUN]
03:21:08  3 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_set_history_tmp  [RUN]
03:21:08  4 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__basic_ad_tmp  [RUN]
03:21:08  5 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__campaign_history_tmp  [RUN]
03:21:08  6 of 17 START view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__creative_history_tmp  [RUN]
03:21:10  4 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__basic_ad_tmp  [OK in 2.18s]
03:21:10  2 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_history_tmp  [OK in 2.19s]
03:21:10  6 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__creative_history_tmp  [OK in 2.18s]
03:21:10  7 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__basic_ad  [RUN]
03:21:10  8 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_history  [RUN]
03:21:10  9 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__creative_history  [RUN]
03:21:11  5 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__campaign_history_tmp  [OK in 2.25s]
03:21:11  3 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_set_history_tmp  [OK in 2.25s]
03:21:11  10 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__campaign_history  [RUN]
03:21:11  11 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_set_history  [RUN]
03:21:11  1 of 17 OK created view model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__account_history_tmp  [OK in 2.43s]
03:21:11  12 of 17 START table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__account_history  [RUN]
03:21:16  12 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__account_history  [CREATE TABLE (7.0 rows, 288.0 Bytes processed) in 5.02s]
03:21:16  8 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_history  [CREATE TABLE (23.2k rows, 2.8 MB processed) in 
5.35s]
03:21:16  7 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__basic_ad  [CREATE TABLE (115.9k rows, 6.2 MB processed) in 5.49s]
03:21:16  10 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__campaign_history  [CREATE TABLE (815.0 rows, 60.7 KB processed) in 5.36s]
03:21:16  11 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__ad_set_history  [CREATE TABLE (6.0k rows, 538.9 KB processed) in 5.50s]
03:21:16  9 of 17 OK created table model bi_chandu_practice_v3_facebook_ads_source_v2.stg_facebook_ads__creative_history  [CREATE TABLE (16.5k rows, 12.3 MB processed) in 5.70s]
03:21:16  13 of 17 START view model bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__creative_history_prep  [RUN]
03:21:17  13 of 17 ERROR creating view model bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__creative_history_prep  [ERROR in 1.13s]
03:21:17  14 of 17 SKIP relation bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__ad_adapter  [SKIP]
03:21:17  15 of 17 SKIP relation bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__account_report  [SKIP]
03:21:17  16 of 17 SKIP relation bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__ad_set_report  [SKIP]
03:21:17  17 of 17 SKIP relation bi_chandu_practice_v3_facebook_ads_v2.facebook_ads__campaign_report  [SKIP]
03:21:17
03:21:17  Finished running 7 view models, 10 table models in 13.23s.
03:21:18  
03:21:18  Completed with 1 error and 0 warnings:
03:21:18
03:21:18  Runtime Error in model facebook_ads__creative_history_prep (models\intermediate\facebook_ads__creative_history_prep.sql)
03:21:18    404 Not found: Dataset gcp-ftd-bi-sandbox:bi_chandu_practice_v3_facebook_ads_creative_history was not found in location US
03:21:18
03:21:18    Location: US
03:21:18    Job ID: 9ac7911f-7b36-492c-ab55-3d9bfd89152b
03:21:18
03:21:18
03:21:18  Done. PASS=12 WARN=0 ERROR=1 SKIP=4 TOTAL=17

Expected behavior

The model should not give any errors and all the dependencies should get created automatically by the facebook_ads itself

dbt Project configurations

vars:
  api_source: google_ads
  ad_reporting__pinterest_enabled: True
  ad_reporting__microsoft_ads_enabled: True
  ad_reporting__linkedin_ads_enabled: False
  ad_reporting__google_ads_enabled: True
  ad_reporting__twitter_ads_enabled: False
  ad_reporting__facebook_ads_enabled: True
  ad_reporting__snapchat_ads_enabled: True
  ad_reporting__tiktok_ads_enabled: True

  facebook_ads_schema: bi_landing_fivetran_facebook_ads_v2
  facebook_ads_database: gcp-ftd-data-int-prod
  tiktok_ads_schema: bi_landing_fivetran_tiktok_ads
  tiktok_ads_database: gcp-ftd-data-int-prod
  pinterest_schema: bi_landing_fivetran_pinterest_ads
  pinterest_database: gcp-ftd-data-int-prod
  snapchat_schema: bi_landing_fivetran_snapchat_ads_v2
  snapchat_database: gcp-ftd-data-int-prod
  google_ads_schema: bi_landing_fivetran_google_adv
  google_ads_database: gcp-ftd-data-int-prod
  microsoft_ads_schema: bi_landing_fivetran_microsoftads_v2
  microsoft_ads_database: gcp-ftd-data-int-prod

models:

  bigdata:
    # Config indicated by + and applies to all files under models/example/
    example:
      +materialized: view
    marketing:
      +materialized: view
  # disable both pinterest models if not using pinterest ads
  pinterest:
    enabled: true
    +schema: pinterest
  pinterest_source:
    enabled: true
    +schema: pinterest_source

  # disable both microsoft ads models if not using microsoft ads
  microsoft_ads:
    +schema: microsoft_ads_v2
  microsoft_ads_source:
    +schema: microsoft_ads_source_v2

  # 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 all three facebook ads models if not using facebook ads
  facebook_ads:
    enabled: true #IF YOU ARE USING FACEBOOK, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
    +schema: facebook_ads_v2
  facebook_ads_source:
    enabled: true #IF YOU ARE USING FACEBOOK, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
    +schema: facebook_ads_source_v2
  #facebook_ads_creative_history:
  #  enabled: true #IF YOU ARE USING FACEBOOK, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
  #  +schema: facebook_ads

  # disable both google ads models if not using google ads
  google_ads:
    #enabled: true #IF YOU ARE USING GOOGLE ADS, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
    +schema: google_ads
  google_ads_source:
    #enabled: true #IF YOU ARE USING GOOGLE ADS, DELETE THIS CONFIG, DO NOT SIMPLY SET TO TRUE
    +schema: google_ads_source

  # disable both snapchat ads models if not using snapchat ads
  snapchat_ads:
    enabled: true
    +schema: snapchat_ads_v2
  snapchat_ads_source:
    enabled: true
    +schema: snapchat_ads_source_v2

  # disable both tiktok ads models if not using tiktok ads
  tiktok_ads:
    enabled: true
    +schema: tiktok_ads
  tiktok_ads_source:
    enabled: true
    +schema: tiktok_ads_source

Package versions

packages:

• package: fivetran/ad_reporting
  version: 0.7.0

What database are you using dbt with?

bigquery

dbt Version

1.1.1

Additional Context

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.

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

The Microsoft ads search report model uses the following columns as a surrogate key:

        - date_day
        - account_id
        - campaign_id
        - ad_group_id
        - ad_id
        - keyword_id
        - search_query
        - device_os
        - device_type
        - network

This combination of columns does not uniquely identify a record, and the test fails. The cause of this is:

1. match_type (a concatenation of two fields) is used in the select from the base table, but not included in the surrogate key. The two fields that are concatenated into match_type are actually part of the key for the base table (bingads.keyword_performance_daily_report). This is a screenshot of Fivetran as evidence:
   

2. Additionally, keyword_id is part of the composite key, but that field is left joined from the keyword history table. Since it's left joined, and keywords are apparently hard deleted from the history table (wtf Microsoft!), the resulting value will be null, which is no bueno for a field that is used in a surrogate key.

Proposed solution:

1. add match_type to the unique test in microsoft_ads.yml
2. Inner join to keyword to remove the possibility of null keyword_id values in the surrogate key

Relevant error log or model output

Failure in test dbt_utils_unique_combination_of_columns_microsoft_ads__search_report_date_day__account_id__campaign_id__ad_group_id__ad_id__keyword_id__search_query__device_os__device_type__network (models/microsoft_ads.yml)

Expected behavior

Passing tests

dbt Project configurations

vars:
prod_database: analytics
prod_schema: business
operdb_schema_pattern: 'operdb%'
raw_data_db: GONG
"dbt_date:time_zone": "America/Los_Angeles"
timezone_constant: "America/Los_Angeles"

ad_reporting__apple_search_ads_enabled: False # by default this is assumed to be True
ad_reporting__pinterest_ads_enabled: False # by default this is assumed to be True
ad_reporting__twitter_ads_enabled: False # by default this is assumed to be True
ad_reporting__facebook_ads_enabled: False # by default this is assumed to be True
ad_reporting__snapchat_ads_enabled: False # by default this is assumed to be True
ad_reporting__tiktok_ads_enabled: False # by default this is assumed to be True

linkedin_ads_schema: linkedin_ads
linkedin_ads_database: source_raw

google_ads_schema: google_ads
google_ads_database: source_raw

microsoft_ads_schema: bingads
microsoft_ads_database: source_raw

Package versions

packages:

• package: fivetran/salesforce_formula_utils
  version: 0.6.4

• package: calogica/dbt_date
  version: 0.5.7

• package: fivetran/ad_reporting
  version: [">=1.0.0", "<1.1.0"]

What database are you using dbt with?

snowflake

dbt Version

1.0.1

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.

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

In the latest release (v1.1.0) a warning is present indicating the metric arguments within the ad_reporting_metrics.yml are out of date and going to be deprecated. As such, we should update the metric definitions to be in line with the current dbt-metrics standards.

See below for a relevant warning message:

Relevant error log or model output

17:21:35  [WARNING]: Deprecated functionality
dbt-core v1.3 renamed attributes for metrics:
  'sql'              -> 'expression'
  'type'             -> 'calculation_method'
  'type: expression' -> 'calculation_method: derived'
The old metric parameter names will be fully deprecated in v1.4.
Please remove them from the metric definition of metric 'cost_per_click'
Relevant issue here: https://github.com/dbt-labs/dbt-core/issues/5849

Expected behavior

No warning is displayed when users run the ad reporting package.

dbt Project configurations

N/A

Package versions

v1.1.0

What database are you using dbt with?

postgres, redshift, snowflake, bigquery, databricks

dbt Version

v1.3.1

Additional Context

This could be a great first issue for any community member wanting to try to contribute to the package offering!

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.

@DylanBaker can you work on adding this variable?

Note - this may change depending on the strategy taken w/ this open issue: fivetran/dbt_google_ads#7

Hi there, has anyone considered the addition of conversions to ad_reporting? Are there existing considerations/complications/work that's already started? I would be curious to understand how difficult it would be to add them and happy to contribute if that's an option.
Please let me know your thoughts.
many thanks,
Christoph.

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

The data provided by the LinkedIn ads take reports the data in the UTC timestamp. The data fetched from the Facebook ads and the Google ads reports the data in the timestamp from which time zone the accounts are created. Let's assume it is EST for now. When combining data multiple data from multiple ad sources we have LinkedIn data showing values by considering UTC time zone and the Facebook and the Google ads shows the metrics based on the EST time zone.

If we are using ads reporting package we are getting a consolidated view of the entire data but different ads report in different time zones. If we are considering to figure out conversion attribution using a tool like heap then we need to convert the heap data into EST time zone before joining the data with Facebook ads data and Google ads, when joining heap data with LinkedIn data heap data needs to be converted into UTC time zone. This seems to be like a bad idea. Wouldn't it be better if we can configure the required time zone from a single .yml file and the data from all the ads network is reported based on that time zone? Looking forward to this becoming a feature on the ad reporting package.

If this functionality is already present on this package let me know how to do achieve that and where I need to configure that?

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

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

Anything else?

No response

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Recently dbt-core was upgraded to 1.4.1 and plugin dbt-snowflake to 1.4.0. After this upgrade ad_reporting 1.2.0 does not work.

Relevant error log or model output

1) dbt deps log (top lines only):
| 07:31:50  Running with dbt=1.4.1
| 07:31:56  Installing fivetran/ad_reporting
| 07:31:57    Installed from version 1.2.0
| 07:31:57    Up to date!
| 07:31:57  Installing fivetran/apple_search_ads
| 07:31:58    Installed from version 0.2.0
| 07:31:58    Up to date!
| 07:31:58  Installing fivetran/snapchat_ads
...

dbt deps works fine. Above is just FYI.

2) dbt run error log:

| 07:32:15  Running with dbt=1.4.1
| 07:32:16  Unable to do partial parsing because saved manifest not found. Starting full parse.
| 07:32:22  [WARNING]: Deprecated functionality
| 
| dbt-core v1.3 renamed attributes for metrics:
|   'sql'              -> 'expression'
|   'type'             -> 'calculation_method'
|   'type: expression' -> 'calculation_method: derived'
| Please remove them from the metric definition of metric 'cost_per_click'
| Relevant issue here: https://github.com/dbt-labs/dbt-core/issues/5849
| 07:32:25  Encountered an error:
| 'False and True' is not of type 'boolean'
|
| Failed validating 'type' in schema['properties']['enabled']:
|     {'default': True, 'type': 'boolean'}
|
| On instance['enabled']:
|     'False and True'
| 07:32:25  jsonschema.exceptions.ValidationError: 'False and True' is not of type 'boolean'
|
| Failed validating 'type' in schema['properties']['enabled']:
|     {'default': True, 'type': 'boolean'}
|
| On instance['enabled']:
|     'False and True'
|
| The above exception was the direct cause of the following exception:
|
| Traceback (most recent call last):
|   File "/usr/local/lib/python3.10/dist-packages/dbt/main.py", line 135, in main
|     results, succeeded = handle_and_check(args)
|   File "/usr/local/lib/python3.10/dist-packages/dbt/main.py", line 198, in handle_and_check
|     task, res = run_from_args(parsed)
|   File "/usr/local/lib/python3.10/dist-packages/dbt/main.py", line 245, in run_from_args
|     results = task.run()
|   File "/usr/local/lib/python3.10/dist-packages/dbt/task/runnable.py", line 454, in run
|     self._runtime_initialize()
|   File "/usr/local/lib/python3.10/dist-packages/dbt/task/runnable.py", line 165, in _runtime_initialize
|     super()._runtime_initialize()
|   File "/usr/local/lib/python3.10/dist-packages/dbt/task/runnable.py", line 94, in _runtime_initialize
|     self.load_manifest()
|   File "/usr/local/lib/python3.10/dist-packages/dbt/task/runnable.py", line 81, in load_manifest
|     self.manifest = ManifestLoader.get_full_manifest(self.config)
|   File "/usr/local/lib/python3.10/dist-packages/dbt/parser/manifest.py", line 203, in get_full_manifest
|     manifest = loader.load()
|     patcher.construct_sources()
|   File "/usr/local/lib/python3.10/dist-packages/dbt/parser/sources.py", line 84, in construct_sources
|     parsed = self.parse_source(patched)
|   File "/usr/local/lib/python3.10/dist-packages/dbt/parser/sources.py", line 145, in parse_source
|     config = config.finalize_and_validate()
|   File "/usr/local/lib/python3.10/dist-packages/dbt/contracts/graph/model_config.py", line 353, in finalize_and_validate
|     self.validate(dct)
|   File "/usr/local/lib/python3.10/dist-packages/hologram/__init__.py", line 989, in validate
|     raise ValidationError.create_from(error) from error
| hologram.ValidationError: 'False and True' is not of type 'boolean'
|
| Failed validating 'type' in schema['properties']['enabled']:
|     {'default': True, 'type': 'boolean'}
|
| On instance['enabled']:
|     'False and True'

Expected behavior

ad_reporting package 1.2.0 works correctly with dbt-core 1.4.1 and dbt-snowflake 1.4.0.

dbt Project configurations

In my case four ads systems are enabled:

vars:
  ad_reporting__amazon_ads_enabled:       False
  ad_reporting__apple_search_ads_enabled: False
  ad_reporting__pinterest_ads_enabled:    True
  ad_reporting__microsoft_ads_enabled:    False
  ad_reporting__linkedin_ads_enabled:     False
  ad_reporting__google_ads_enabled:       True
  ad_reporting__twitter_ads_enabled:      False
  ad_reporting__facebook_ads_enabled:     True
  ad_reporting__snapchat_ads_enabled:     False
  ad_reporting__tiktok_ads_enabled:       True

No other specific settings.

Package versions

packages:
  - package: fivetran/ad_reporting
    version: [">=1.2.0"]

What database are you using dbt with?

snowflake

dbt Version

Core:
  - installed: 1.4.1
  - latest:    1.4.1 - Up to date!

Plugins:
  - snowflake: 1.4.0 - Up to date!

Additional Context

The same ad_reporting package version 1.2.0 worked fine with dbt-core version 1.3.2 and dbt-snowflake plugin 1.3.0.

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.

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

I am trying to setup the dbt package ad_reporting that is created by Fivetran. While making changes to the dbt_project.yml it is confusing, to which dbt_project.yml file do I need to make the changes? Can the documentation be updated with those details please.

Describe alternatives you've considered

Nothing

Are you interested in contributing this feature?

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

Anything else?

Nothing

Are you a current Fivetran customer?

Fivetran created PR.
What change(s) does this PR introduce?

• For use in the dbt_ad_reporting package, users can now allow records having nulls in url fields to be included in the ad_reporting__url_report model.
• Disabled the not_null test for ad_reporting__url_report when null urls are allowed

Did you update the CHANGELOG?

• Yes

Does this PR introduce a breaking change?

• Yes (please provide breaking change details below.)
• No (please provide an explanation as to how the change is non-breaking below.)

Did you update the dbt_project.yml files with the version upgrade (please leverage standard semantic versioning)? (In both your main project and integration_tests)

• Yes

Is this PR in response to a previously created Bug or Feature Request

• Yes, Issue/Feature #59
• No

How did you test the PR changes?

• BuildKite
• Local (please provide additional testing details below)

Select which warehouse(s) were used to test the PR

• BigQuery
• Redshift
• Snowflake
• Postgres
• Databricks
• Other (provide details below)

Provide an emoji that best describes your current mood

🍝

Feedback

We are so excited you decided to contribute to the Fivetran community dbt package! We continue to work to improve the packages and would greatly appreciate your feedback on our existing dbt packages or what you'd like to see next.

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Some customers have brought up concerns on why sums are different across different grains, for example why spend is not the same summed up across the ad level versus the campaign level.

Example from Joe regarding a recent customer inquiry:

"The reason for this discrepancy is due to not all ads being served at the ad level. There are some ads that are only served at the ad group/campaign/etc. levels. This means that you can have ads sent at the campaign level and that counts towards your total spend. However, since the ad was never sent at an individual ad level, it will not be included.
This was a large reason for us breaking the ad reporting package into separate hierarchical end models. Because we found when we only used the ad level, we were missing data as some ads were not served at an ad level, but were served at a campaign level."

We should add more context like above to our ad packages READMEs as a proactive measure.

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

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

Anything else?

No response

Are you a Fivetran customer?

Heidi Trimble, Manager - Marketing Analytics Engineering, Pluralsight

Is your feature request related to a problem? Please describe.

We need the Salesforce AID / OID values form the destination URLS to link Ad Performance from the Platforms back to our Salesforce Campaign and CampaignMember data

Describe the solution you'd like

I's like the AID to be included in the ad_reporting table and included as part of the level of aggregation of ad perfomance metrics. Where applicable the OID should also be surfaced in ad_reporting

Describe alternatives you've considered

Obtaining this data via other data integration

Additional context

AID is the Acquisition Campaign ID, OID is the Offer/Content Campaign ID. Both identifiers are sourced from Salesforce and embedded in the destination URL and included in the Ad Performance data from each platform.

Please indicate the level of urgency and business impact of this request

Critical - although we have the code written and will be forking and modifying to meet the immediate need.

Are you interested in contributing to this package?

• Yes, I can do this and open a PR for your review.
  We have the code for 5 or the 7 available ad platforms; Google, Microsoft, Facebook, Twitter, and LinkedIn.
• Possibly, but I'm not quite sure how to do this. I'd be happy to do a live coding session with someone to get this work implemented.
• No, I'd prefer if someone else did this. I don't have the time and/or don't know how to incorporate the changes necessary.

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Recently we set Pinterest Fivetran connector and enabled Pinterest in dbt_project.yml of our existing ad_reporting project where FB, Google and Tiktok are already enabled and working well.

However, Pinterest part fails because no table keyword_report was loaded into Fivetran target schema (which is a source schema for ad_reporting dbt project).

Relevant error log or model output

Extracts from our `dbt deps` and `dbt run` logs:

1) dbt deps

13:11:51  Running with dbt=1.3.2
13:11:55  Installing fivetran/ad_reporting
13:11:56    Installed from version 1.1.0
13:11:56    Up to date!
...

2) dbt run

13:12:54  24 of 97 START sql view model <my_schema>.stg_pinterest_ads__keyword_report_tmp  [RUN]
13:12:55  24 of 97 ERROR creating sql view model <my_schema>.stg_pinterest_ads__keyword_report_tmp  [ERROR in 1.07s]
...
13:13:28  57 of 97 SKIP relation <my_schema>.stg_pinterest_ads__keyword_report ... [SKIP]
...
13:13:52  81 of 97 SKIP relation <my_schema>.pinterest_ads__keyword_report ....... [SKIP]
...
13:14:02  91 of 97 SKIP relation <my_schema>.ad_reporting__keyword_report ........ [SKIP]
...
13:14:10  Completed with 1 error and 0 warnings:
13:14:10  
13:14:10  Database Error in model stg_pinterest_ads__keyword_report_tmp (models/tmp/stg_pinterest_ads__keyword_report_tmp.sql)
13:14:10    002003 (42S02): SQL compilation error:
13:14:10    Object '<src_database_name>.<src_schema_name>.KEYWORD_REPORT' does not exist or not authorized.
13:14:10    compiled Code at target/run/pinterest_source/models/tmp/stg_pinterest_ads__keyword_report_tmp.sql
13:14:10  
13:14:10  Done. PASS=93 WARN=0 ERROR=1 SKIP=3 TOTAL=97

Expected behavior

Fivetran DBT package ad_reporting works correctly when Pinterest Ads is enabled.

dbt Project configurations

vars:
  ad_reporting__pinterest_ads_enabled: True

Package versions

packages:
  - package: fivetran/ad_reporting
    version: [">=1.1.0"]

What database are you using dbt with?

snowflake

dbt Version

Core:
  - installed: 1.3.2   
  - latest:    1.3.2 - Up to date!

Plugins:
  - snowflake: 1.3.0 - Up to date!

Additional Context

The unclear thing here is that the mentioned table keyword_report is selected to be loaded in Schema settings of Fivetran Pinterest connector but this table is not presented in Pinterest ERD model (link) and, as a result, it's not loaded.
It's unclear what is the issue:

1. Missing table in Pinterest database model (but this table is expected by ad_reporting) or
2. ad_reporting refers to the Pinterest table keyword_report that might be obsolete at the moment

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.

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

dbt-core has a new metric spec as of 1.6. We need to update the metrics in the package to be compatible with the new spec.

Describe alternatives you've considered

Remove metrics, or only use the package on dbt versions <1.6

Are you interested in contributing this feature?

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

Anything else?

No response

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

We released an update to dbt-semantic-interfaces that reserves the dunder(__) character as a keyword, and does not allow dunders in semantic model names. The package currently has a semantic model named ad_reporting__ad_report. This is causing dbt parse jobs to fail on dbt v1.6.5.

To fix this bug we need to updated the name of the semantic models to remove dunders.

Relevant error log or model output

No response

Expected behavior

dbt parse should succeed when we remove dunders from the semantic model names.

dbt Project configurations

vars:
ad_reporting:
ad_reporting__account_passthrough_metrics: []
ad_reporting__campaign_passthrough_metrics: []
ad_reporting__ad_group_passthrough_metrics: []
ad_reporting__ad_passthrough_metrics: []
ad_reporting__keyword_passthrough_metrics: []
ad_reporting__search_passthrough_metrics: []
"dbt_date:time_zone": "America/Los_Angeles"

Package versions

packages:

• package: fivetran/apple_search_ads
  version: [">=0.2.0", "<0.3.0"]

• package: fivetran/snapchat_ads
  version: [">=0.5.0", "<0.6.0"]

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

• package: fivetran/google_ads
  version: [">=0.9.0", "<0.10.0"]

• package: fivetran/pinterest
  version: [">=0.9.0", "<0.10.0"]

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

• package: fivetran/microsoft_ads
  version: [">=0.6.0", "<0.7.0"]

• package: fivetran/tiktok_ads
  version: [">=0.4.0", "<0.5.0"]

• package: fivetran/twitter_ads
  version: [">=0.6.0", "<0.7.0"]

• package: fivetran/amazon_ads
  version: [">=0.2.0", "<0.3.0"]

• package: fivetran/reddit_ads
  version: [">=0.1.0", "<0.2.0"]

What database are you using dbt with?

snowflake

dbt Version

1.6.5

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.