search

fivetran / dbt_facebook_ads_source

Fivetran data models for Facebook Ads built using dbt.
https://fivetran.github.io/dbt_facebook_ads_source/
Apache License 2.0
14 stars 16 forks source link

[Bug] Can't Pull Through Reach #34

Closed alexmacartney closed 6 months ago

alexmacartney commented 6 months ago

Is there an existing issue for this?

Describe the issue

reach is already being pulled through manually in stg_facebook_ads__basic_ad.sql so when I try to pass through reach using the below I get a duplicate field and the model can't run. I need to pass through the field for subsequent models using the dbt_facebook_ads repo. I believe the same would be true for frequency.

facebook_ads__basic_ad_passthrough_metrics: 
  - name: "reach"

Relevant error log or model output

with base as (
    select * 
    from `analytics-dev-406710`.`alex_analytics_dev_package_fivetran_ads_source`.`stg_facebook_ads__basic_ad_tmp`
),
fields as (
    select

    ad_id

 as 

    ad_id

, 

    ad_name

 as 

    ad_name

, 

    adset_name

 as 

    adset_name

, 

    date

 as 

    date

, 

    account_id

 as 

    account_id

, 

    impressions

 as 

    impressions

, 

    inline_link_clicks

 as 

    inline_link_clicks

, 

    spend

 as 

    spend

, 

    reach

 as 

    reach

, 

    frequency

 as 

    frequency

, 

    reach

 as 

    reach

, cast('' as STRING) as source_relation
    from base
),
final as (
    select
        source_relation, 
        cast(ad_id as bigint) as ad_id,
        ad_name,
        adset_name as ad_set_name,
        date as date_day,
        cast(account_id as bigint) as account_id,
        impressions,
        coalesce(inline_link_clicks,0) as clicks,
        spend,
        reach,
        frequency

                , reach

    from fields
)
select * 
from final

Expected behavior

I would expect reach to not be in the initial list of variables so that when the macro adds it there are no duplicate fields. This would then allow the model to run.

dbt Project configurations

facebook_ads__basic_ad_passthrough_metrics: 
  - name: "reach"

Package versions

packages:

  - package: dbt-labs/dbt_utils
    version: [">=1.0.0", "<2.0.0"]

  - package: Velir/ga4
    version: 5.1.0

  - package: fivetran/google_ads
    version: [">=0.10.0", "<0.11.0"]

  - package: fivetran/facebook_ads
    version: [">=0.7.0", "<0.8.0"]

  - package: fivetran/instagram_business
    version: [">=0.2.0", "<0.3.0"]

What database are you using dbt with?

bigquery

dbt Version

Core:

Plugins:

Additional Context

No response

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

alexmacartney commented 6 months ago

Sorry if I have done anything wrong. This is my first time reporting an issue on GitHub!

fivetran-catfritz commented 6 months ago

Hi @alexmacartney thank you for opening this issue--the info you provided is perfect! We agree that the issue with the reach and frequency metrics needs to be addressed. The next step is for us internally to find the best way to deprecate those columns in stg_facebook_ads__basic_ad while maintaining backwards compatibility for all users. We'll keep you posted.

alexmacartney commented 6 months ago

@fivetran-catfritz Great! Please let me know if there is anything I can do to help.

fivetran-catfritz commented 6 months ago

Hi @alexmacartney thanks for the ping. There is nothing you need to do for now! We are accepting this issue and plan to release an update in the coming weeks. We might reach out to you to test our proposed changes in the next week or so as well.

fivetran-jamie commented 6 months ago

hey @alexmacartney - the fix for this is live and will be automatically installed with facebook_ads [">=0.7.0", "<0.8.0"]; you should be able to pass reach and frequency to the facebook_ads__basic_ad_passthrough_metrics var

going to close this issue but please reopen if the fix isn't working for you 🤠