[Feature] Include Campaign and Ad Squad in Ad Report

[JeremyDOwens]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Currently, the ad report snapchat_ads__ad_report does not include the campaign. This leads to a downstream issue in dbt_ad_reporting where the campaign is mapped to NULL https://github.com/fivetran/dbt_ad_reporting/blob/aa5f3d9b7d2a88f5eff839e8a8c22e85bae72c7c/models/intermediate/int_ad_reporting__ad_report.sql#L99
The same goes for ad group (ad squad).

We should include these fields in the ad report.

How would you implement this feature?

In order to implement this feature, I could join relevant other tables to acquire references to the campaign and ad squad. Example query:

SELECT c.name, SUM(impressions) ct
FROM FIVETRAN_PROD.SNAPCHAT_ADS.AD_DAILY_REPORT a 
JOIN FIVETRAN_PROD.SNAPCHAT_ADS.AD_HISTORY ah
ON a.ad_id = ah.id
JOIN FIVETRAN_PROD.SNAPCHAT_ADS.AD_SQUAD_HISTORY s
ON ah.ad_squad_id = s.id
JOIN FIVETRAN_PROD.SNAPCHAT_ADS.CAMPAIGN_HISTORY c
ON c.id = s.campaign_id
WHERE a.impressions > 0
GROUP BY c.name
ORDER BY ct DESC
;

Describe alternatives you've considered

It is possible for me to solve my deeper problem by linking the ad_reporting__ad_report back to the raw source tables, however I believe that it's better to tackle the problem at its root and to avoid leapfrog transformations.

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance.
• No.

Anything else?

No response

[JeremyDOwens]

@fivetran-reneeli @fivetran-joemarkiewicz Sorry to ping you both directly here, but I see that you've done most of the work in this project so far. Do you have any thoughts?

[fivetran-avinash]

Hi @JeremyDOwens! Unfortunately we don't have capacity to review your PR in our current sprint, but are looking to pick it up in our upcoming sprint which starts next week. We will be in touch when we start making progress. Thanks for your patience.

[JeremyDOwens]

Hi @fivetran-avinash - Any updates?

[fivetran-avinash]

Hi @JeremyDOwens ! Thanks for reaching out! I'm taking a look at this now.

[JeremyDOwens]

Thanks for your help. After this change, I'll look to update this also: https://github.com/fivetran/dbt_ad_reporting/blob/aa5f3d9b7d2a88f5eff839e8a8c22e85bae72c7c/models/intermediate/int_ad_reporting__ad_report.sql#L93

Should I open a separate issue there?

[fivetran-avinash]

Hi @JeremyDOwens ! I just approved your PR and folded it into an existing branch.

With regards to the int_ad_reporting__ad_report I'm almost done working on a new branch too to implement the changes there! So hopefully I can give you that branch to test out shortly. Stay tuned!

[fivetran-avinash]

Hi @JeremyDOwens ! I set up a new branch with the changes on both dbt_snapchat_ads and dbt_ad_reporting applied.

Try updating your packages.yml file now, see if the new field data is being brought through properly?

  - git: https://github.com/fivetran/dbt_ad_reporting.git
    revision: feature/update-ad-report-with-snapchat-ad-squad-campaign-details
    warn-unpinned: false 

[fivetran-avinash]

Hi @JeremyDOwens ! Were you able to test the latest version of the ad_reporting package above? How did the data look for you? Any thoughts or questions I can help answer, or does all look well?