Feature/bneal dsa ads

[bnealdefero]

Are you a current Fivetran customer? Billy Neal, Sr. Director of Development at Defero

What change(s) does this PR introduce? This is an attempt to pull in missing dynamic search ads.

Does this PR introduce a breaking change?

• [] Yes (please provide breaking change details below.)
• [ X] No (please provide explanation how the change is non breaking below.) @fivetran-joemarkiewicz After we spoke last, I ended up creating a new model vs. trying to do anything with the existing url_ad_adapter so while I'm hoping this is a start to a solution I don't think it's in any type of final form, but technically probably wouldn't "break" anything.

Is this PR in response to a previously created Issue

• [ X] Yes, Issue [#7]
• [ ] No

How did you test the PR changes?

• [ ] CircleCi
• [X ] Other (please provide additional testing details below) dbt cloud to build the models and some SQL. Can verify, at least for us, we are able to successfully tie things out to our internal spend reporting at the campaign level.

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

- [ ] BigQuery - [X ] Redshift - [ ] Snowflake - [ ] Postgres - [ ] Databricks - [ ] Other (provide details below) **Provide an emoji that best describes your current mood** :smile: **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](https://www.surveymonkey.com/r/DQ7K7WW) on our existing dbt packages or what you'd like to see next.

[fivetran-joemarkiewicz]

@bnealdefero thanks so much for opening this PR! 🎉 💯

We are currently in the midst of working to upgrade all of our packages to be compatible with dbt v1.0.0! Our team will review this PR and let you know if we have any questions once we complete our v1.0.0 upgrades (most likely by mid next week 🤞 ).

I'll post back here once we are able to take the time to review this PR. Thanks again so much for your contributions. We are extremely grateful for your efforts here and look forward to making this package better for yourself and the community 😄 .

[fivetran-joemarkiewicz]

@bnealdefero thank you again for putting in all the hard work to integrate this feature into the Google Ads package.

I have been able to begin reviewing this PR and test it locally. I had to make a few configuration changes locally to allow this to work, but soon then ended up with a succesful run of my dbt job with the creation of a new google_ads__campaign_adapter model. I also could confirm that this new model included all the campaigns from the original google_ads__url_adapter as well as the missing campaigns. Following the succesful run, I looked further into the result and had a few questions I wanted to confirm with you.

While this does achieve the integration of the Dynamic Search Ads (DSAs) metrics into the final model, I noticed it also brings in a few other ad group criterion types (eg. USER_LIST, USER_INTEREST). As such, I don't think it will work for us to hardcode dsa as the utm_term in the stg_google_ads__campaign_spend_tmp model. This then brings up a larger question. To keep the grain consistent, would it make more sense to simply create a ad_campaign_report roll up model which performs the same logic showcased in this report, but simply aggregates on the campaign level? This way we would have a rolled up report of all campaigns (which will include DSAs in Google) across platforms. This would then continue to keep the original url adapter grain, but still provide more insight into all your campaigns across platforms, even if they do not leverage DSAs.

A final note/though: Our team is currently working on a new dbt package for Apple Search Ads and have determined it does not make sense to roll these records into the ad_reporting final model. However, it may make more sense to create a second model that rolls up search ads and keywords into a reporting model. Do you know if DSAs have specified keywords? Or is the clue in the name and the keywords are ever dynamically changing?

[bnealdefero]

Hi @fivetran-joemarkiewicz , this is great.

No problem at all on not hardcoding 'dsa' into the utm_term column. I should have pulled that out as it was really just for testing purposes.

I think keeping the grain consistent and creating an ad_campaign_report roll up with same logic would be perfect..

I did some additional testing and we can actually get this down to the ad_group level as well with similar logic. If you are able to get the ad_campaign_report into the package once I see how you've structured things I'd be happy to work on contributing what I have back here to the package.

To your question on DSAs having specific keywords, correct, they are ever dynamically changing and not specified as part of the ad setup so nothing to report on that I'm aware of.

Thanks, much appreciated..

[fivetran-joemarkiewicz]

Hi @bnealdefero thanks again so much for opening this PR! Unfortunately we ended up taking a different approach to capture dynamic search ads. You should be able to capture these in the ad group or campaign level reports.

Since we took a different approach, I will close this PR. Please feel free to reopen if you feel there is more to add here. 😄