Updated utms and add campaign channel fields

[jocelyn-metricbox]

Are you a current Fivetran customer? Jocelyn Chan, Data Analyst, Paper Culture

What change(s) does this PR introduce?

• Added utm_source, utm_medium, and utm_campaign defined in stg_google_ads__campaign_history model from [dbt_google_ads_source] package (https://github.com/fivetran/dbt_google_ads_source) to the google_ads__campaign_report model.
• Added advertising_channel_type, advertising_channel_subtype, and campaign_statusinto google_ads__url_report model to help users easily filter ads from specific campaign types and status.
• Updated UTM extraction logic in google_ads__url_report model to leverage utm_source, utm_medium, utm_campaign, utm_content, and utm_term that are already defined using both values from final_urls and final_url_suffix in stg_google_ads__ad_history model from [dbt_google_ads_source] package (https://github.com/fivetran/dbt_google_ads_source).

The changes have dependencies on the [dbt_google_ads_source] package detailed in PR #33

Did you update the CHANGELOG?

• [x] Yes

Does this PR introduce a breaking change?

• [ ] Yes (please provide breaking change details below.)
• [x] No (please provide an explanation as to how the change is non-breaking below.) The new fields that are added or referenced in this PR exist in the upstream models from the [dbt_google_ads_source] package. As long as the PR #33 is merged before this one, users should be able to get all the required fields to run the job successfully.

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)

• [x] Yes

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

• [x] Yes, Issue/Feature PR #33
• [ ] No

How did you test the PR changes?

• [ ] Buildkite
• [x] Local (please provide additional testing details below) The test is done by referencing the new dbt_google_ads_source package proposed in PR #33 to create models in this dbt_google_ads package. The local version of the package is installed in my dbt project, and it runs smoothly with the new changes.

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

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

Provide an emoji that best describes your current mood

:dancer: **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]

Hi @jocelyn-metricbox thank you so much for opening these PRs to add the features to the Google Ads packages. At first glance, I think these additions look relevant to include. One thing I did want to highlight is that these utm fields will most likely not be able to be brought through to our final ad reporting roll up package. Is that a concern of yours that these updates would most likely be isolated to this package?

If not, then we can move forward with this review. Additionally, I saw you scheduled some time to discuss the PR during our office hours! I look forward to chatting more with you then 😄

[jocelyn-metricbox]

Hi @jocelyn-metricbox thank you so much for opening these PRs to add the features to the Google Ads packages. At first glance, I think these additions look relevant to include. One thing I did want to highlight is that these utm fields will most likely not be able to be brought through to our final ad reporting roll up package. Is that a concern of yours that these updates would most likely be isolated to this package?

If not, then we can move forward with this review. Additionally, I saw you scheduled some time to discuss the PR during our office hours! I look forward to chatting more with you then 😄

@fivetran-joemarkiewicz To your question, we are fine with the UTM fields only in the url_reporting model as is originally in the final package. So no concern there. Thank you!

[jocelyn-metricbox]

Hi Joe,

How are you? I met with your team the same week as your previous email a month ago. After I clarified a few things, it doesn't seem to be any concerns from the team. I wonder if there is any update about the PRs?

https://github.com/fivetran/dbt_google_ads_source/pull/33 https://github.com/fivetran/dbt_google_ads/pull/42

We hope they can be approved and merged soon. Looking forward to hearing from you! Thank you!

Best Regards, Jocelyn Chan

On Mon, Feb 13, 2023 at 10:56 AM Joe Markiewicz @.***> wrote:

Hi @jocelyn-metricbox https://github.com/jocelyn-metricbox thank you so much for opening these PRs to add the features to the Google Ads packages. At first glance, I think these additions look relevant to include. One thing I did want to highlight is that these utm fields will most likely not be able to be brought through to our final ad reporting roll up package. Is that a concern of yours that these updates would most likely be isolated to this package?

If not, then we can move forward with this review. Additionally, I saw you scheduled some time to discuss the PR during our office hours! I look forward to chatting more with you then 😄

— Reply to this email directly, view it on GitHub https://github.com/fivetran/dbt_google_ads/pull/42#issuecomment-1428186142, or unsubscribe https://github.com/notifications/unsubscribe-auth/AVFD6LGTSWTBYD5BNETOG6LWXJKRTANCNFSM6AAAAAAURQM7DE . You are receiving this because you were mentioned.Message ID: @.***>

[fivetran-joemarkiewicz]

HI @jocelyn-metricbox my apologies for not responding to your previous email from a month ago. If there is no intention of these fields flowing downstream to the ad reporting package, then I don't have high concerns with incorporating this into the package.

I will add this review to our next sprint. You can expect to hear from us next week and may have some follow up or small edits to apply to your PRs before merging into the main branch.

[jocelyn-metricbox]

Hi Joe,

Sounds good. Thank you!

Best, Jocelyn Chan

On Thu, Mar 16, 2023 at 4:34 PM Joe Markiewicz @.***> wrote:

HI @jocelyn-metricbox https://github.com/jocelyn-metricbox my apologies for not responding to your previous email from a month ago. If there is no intention of these fields flowing downstream to the ad reporting package, then I don't have high concerns with incorporating this into the package.

I will add this review to our next sprint. You can expect to hear from us next week and may have some follow up or small edits to apply to your PRs before merging into the main branch.

— Reply to this email directly, view it on GitHub https://github.com/fivetran/dbt_google_ads/pull/42#issuecomment-1472703091, or unsubscribe https://github.com/notifications/unsubscribe-auth/AVFD6LFIGSNKOOAXBMJLVXLW4N2NXANCNFSM6AAAAAAURQM7DE . You are receiving this because you were mentioned.Message ID: @.***>

[fivetran-joemarkiewicz]

Hi @jocelyn-metricbox just wanted to mention here that I will be able to finish this review once the comments within the dbt_google_ads_source PR are addressed.

Let me know if you have any questions on my comments within the above PR 😃

[fivetran-joemarkiewicz]

@jocelyn-metricbox thanks for working on these updates. There are a few changes I would like to make before integrating these updates into the next release. As such, I will merge them into a staging branch where more changes will be made before release. Be sure to follow the soon to accompany PR to be in the loop on when these changes will be live.