[Feature] Add final_url_suffix and include in campaign reports.

fivetran-joemarkiewicz commented 1 year ago

Is there an existing feature request for this?

[X] I have searched the existing issues

Describe the Feature

Add final_url_suffix into stg_google_ads__campaign_history and stg_google_ads__ad_history and modified logic for extracting the UTMs.

Describe alternatives you've considered

Editing the package manually as opposed to being integrated in the official live version of the package.

Are you interested in contributing this feature?

[ ] Yes.
[ ] Yes, but I will need assistance and will schedule time during your office hours for guidance.
[ ] No.

Anything else?

Please see PR #34 and PR #42 for more details

fivetran-joemarkiewicz commented 1 year ago

@dlubawy and/or @jocelyn-metricbox I wanted to clue you in here as I have been able to make the updates highlighted within the conversation form PR #33. Before moving forward with these changes and opening a new PR to incorporate the updates in the next release, I would like for you to confirm that these updates look appropriate with your data.

To test the working branch, would you be able to update your packages.yml to reference the following WIP branch in place of the official google_ads package version.

packages:
# - package: fivetran/google_ads
#   version: [">=0.9.0", "<0.10.0"]
  - git: https://github.com/fivetran/dbt_google_adse.git
    revision: customer/final-url-suffix-add
    warn-unpinned: false

Once you add this to your packages.yml, you will want to run dbt clean && dbt deps to ensure you are using the WIP version of the package and not the official version. If all looks good on your end, I can move forward with opening a PR to incorporate these changes into the next release.

For a full list of all the changes in this WIP branch, you can view the comparisons below:

dlubawy commented 1 year ago

Hi @fivetran-joemarkiewicz,

We took a look on our end and realized that we may be missing the integration of final_url_suffix on the other levels of the Google Ads settings. You can set the final_url_suffix on five different levels: account, campaign, ad, (ad group, dynamic ads target, keyword), and the sitelink levels. The PRs linked so far have only covered the campaign and ad level, but we should have this implemented for all other levels being sourced in the google_ads_source package as well:

stg_google_ads__account_history
stg_google_ads__ad_group_criterion_history
stg_google_ads__ad_group_history

Then the google_ads package should likely source UTM parameters for the google_ads__url_report table from the bottom up because the settings for the various levels do not trickle down into the sub-levels (i.e. final_url_suffix in ad_history does not get inherited from ad_group_history and final_urls in the table does not show URLs that are constructed from the final_url_suffix values where tracking params are intended to be set). This makes the most sense to me as an org would set UTM params in the final_url_suffix on the ad group or campaign levels to auto-create default URLs for ads in those sets, but an org may also overwrite this by setting a final_url_suffix on the ad level itself. All of this being necessary because Google Ads does not provide a field in the ad level combining all of this for us.

To summarize:

Should have UTM columns built from the final_url_suffix in all levels for staging
- stg_google_ads__ad_history
- stg_google_ads__campaign_history
- stg_google_ads__account_history
- stg_google_ads__ad_group_criterion_history
- stg_google_ads__ad_group_history
  1. The google_ads package should source UTM params in the google_ads__url_report table from each of those staging tables by using the hierarchy of levels to determine the order of priority from the bottom up: ad -> ad group -> campaign -> account

Does this make sense?

fivetran-joemarkiewicz commented 1 year ago

Hi @dlubawy thanks for the great response here and my apologies for my delay in getting back to you.

So if I am understanding this correctly, there is a waterfall relationship with how these final_url_suffix may be assigned. Google Ads allows you to set the final_url_suffix at either the account, campaign, ad group, criterion, and ad levels. These then can be flowed down into the "child" groups, but they also allow for overrides at the independent levels. So to picture this in more detail (please pardon my absolutely rudimentary sketching. I am no artist 🧑‍🎨)

If this is true, I have a much better understanding of how these final_url_suffix should be inherited. Additionally, the chart above is only for the ad level. I imagine the same waterfall effect holds true as you go upstream. If this is the case, I see a path forward with applying these updates to the remaining staging models and applying this logic.

However, I do have on remaining question on this topic before going forward. Does the business care about these upstream final_url_suffixs? Or are these only here to be sourced by the google_ads__url_report? If they truly are only necessary to be sourced and reference by the google_ads__url_report in this case, then I feel fairly confident in the approach to update that model in the dbt_google_ads package with the waterfall logic outlined above.

Let me know if I am misunderstanding any points. If not, then I feel we can look to incorporate these full changes in our upcoming sprint.

Thanks for collaborating with us!

dlubawy commented 1 year ago

Hi @fivetran-joemarkiewicz,

Sorry for taking so long to get back to you.

I think you have the right general understanding in how it should work, but the workflow needs to be changed to after parsing the final_url_suffix. Our understanding is that the final_url_suffix is parsed on each level and then parameters are then picked in order of priority. For example, utm_content=campaign_content would be superseded by any utm_content=ad_content, but any additional parameters in the campaign level would still be used so long as they aren't being overridden in the ad level. This is why we should add utm_ columns for each level in staging (stg_google_ads__account_history, stg_google_ads__campaign_history, ...). That way the google_ads package can then do the logic of coalescing UTM parameters from the various levels' staging models into the google_ads__url_report table based on the order of priority with ad level being the highest priority and account level being the lowest. Does that make sense?

fivetran / dbt_google_ads_source