search

fivetran / dbt_ad_reporting

Fivetran's ad reporting dbt package. Combine your Facebook, Google, Pinterest, LinkedIn, Twitter, Snapchat, Microsoft, TikTok, Reddit, Amazon, and Apple Search advertising metrics using this package.
https://fivetran.github.io/dbt_ad_reporting/#!/overview
Apache License 2.0
146 stars 55 forks source link

BUG - Incorrect LinkedIn Campaign Hierarchy #21

Closed andrew-woelfel closed 3 years ago

andrew-woelfel commented 3 years ago

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?

fivetran-joemarkiewicz commented 3 years ago

Hi @andrew-woelfel thanks so much for opening this issue!

Would you be able to let me know which other platforms you are using within the ad_reporting package? I am able to see based off the Microsoft documentation and in our own data that campaign groups may contain multiple campaigns. I have also checked a few of the other platforms and notice that the behavior of multiple campaigns may apply to a single group as well.

I wonder if the hierarchy is off for a different platform instead? I want to make sure we end up making the appropriate change to the relevant model. Especially since this proposed change flip the fields.

andrew-woelfel commented 3 years ago

We use adwords, facebook, linkedIn and bing. Typically in my experience this is the hierarchy tree: Campaign > Ad Group > Ad > Creative.

I have confirmed with our data, AdWords/Bing/Facebook query setup is correct using this model. I noticed this because we have internal naming conventions and these are only flipped for the LinkedIn data with this model, below is the output.

Campaign: PB:L|AN:|GN:|CN: AdGroup: PB:L|AN:|GN:

It should be reserved from what I am seeing

fivetran-joemarkiewicz commented 3 years ago

@andrew-woelfel thank so much for this additional context. After diving into this within our own Bing, Adwords, and LinkedIn data I found exactly what you noted.

With this additional context I completely understand how this is causing confusion and should be addressed within the ad_reporting package. I want to do some additional testing to confirm the hierarchy for the other platforms we have not mentioned.

Since you noted that you would be open to contributing, and it looks like you have the code on hand, would you be interested in opening a PR with your proposed changes? Our team can then look further into the other platforms and integrate your changes along with any others that may need to be applied.

Thanks!

andrew-woelfel commented 3 years ago

Great, I'd be glad to create a PR if I get access to do so

fivetran-joemarkiewicz commented 3 years ago

You can actually fork this repo, make changes, and then open a PR against this repo's master branch without having access. Once our team reviews/approves the PR it will be good to merge!

andrew-woelfel commented 3 years ago

Great, PR created: https://github.com/fivetran/dbt_ad_reporting/pull/22

fivetran-joemarkiewicz commented 3 years ago

@andrew-woelfel I just release v0.4.0 of the ad_reporting package which incorporates your change. This should be live at the top of the hour. Thanks again for your contribution!!