[Feature] Include additional title fields in ad history and allow custom ad_name

[JeremyDOwens]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Background

stg_microsoft_ads__ad_history_tmp contains several columns that contain information related the ad, as is delivered from the initial report.

• title_part_1
• title_part_2
• title_part_3
• domain
• type

Currently, get_ad_history_columns() only selects title_part_1 and type from that list

In many cases with this ad platform, title_part_1 is not a good differentiator and the better ad_name has been configured to fit in a different column. Additionally, when type = 'Responsive Search Ad' title_part_1 is always null.

Proposed feature

Allow users to configure how the ad_name is determined from any of the fields in the list.

How would you implement this feature?

CAVEAT: This is code I haven't tested, and all snippets are for discussion purposes

1. Update get_ad_history_columns() to include additional columns

{% set columns = [
    {"name": "id", "datatype": dbt.type_int()},
    {"name": "title_part_1", "datatype": dbt.type_string()},
    {"name": "final_url", "datatype": dbt.type_string()},
    {"name": "ad_group_id", "datatype": dbt.type_int()},
    {"name": "modified_time", "datatype": dbt.type_timestamp()},
    {"name": "status", "datatype": dbt.type_string()},
    {"name": "type", "datatype": dbt.type_string()},
    {"name": "title_part_2", "datatype": dbt.type_string()},
    {"name": "title_part_3", "datatype": dbt.type_string()},
    {"name": "title", "datatype": dbt.type_string()},
    {"name": "domain", "datatype": dbt.type_string()}
] %}

2. create a variable microsoft_ads_source__ad_name_selector with a default value of title_part_1
3. Reference the ad name selector in stg_microsoft_ads__ad_history instead of explicitly referencing title_part_1

...
final as (

    select
        source_relation, 
        id as ad_id,
        {{ var.microsoft_ads_source__ad_name_selector }}  as ad_name,
        final_url,
        ad_group_id,
...

Describe alternatives you've considered

Alternative...

1. Same as 1 above

2. Create a new macro generate_ad_name() with a sensible default - title_part_1 so it's a non-breaking change

{% macro generate_ad_name() %}

    {{ return(adapter.dispatch('my_macro', 'microsoft_ads_source')()) }}

{% endmacro %}

{% macro default__generate_ad_name() %}

    title_part_1

{% endmacro %}

3. Call this macro in stg_microsoft_ads__ad_history instead of explicitly referencing title_part_1

...
final as (

    select
        source_relation, 
        id as ad_id,
        {{ microsoft_ads_source.generate_ad_name}}  as ad_name,
        final_url,
        ad_group_id,
...

With this setup (or something similar), I think a user should then be able to override the macro.

Are you interested in contributing this feature?

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

Anything else?

No response

[JeremyDOwens]

@fivetran-avinash Me again! Let me know what you think about this, and I can throw together a PR.

[fivetran-avinash]

HI @JeremyDOwens , thanks for reporting this to us!

Just to get more context on this issue before we proceed forward with working on this:

1. title_part_1 is not always considered the best value for ad_name.
2. Better options would be title_part_2, title_part_3, or maybe even combinations of all of these?
3. Your solution would be a variable configuration that allows us to choose an alternative to title_part_1, but use title_part_1 as the general default?
4. Where does domain fit into this? Is this an alternative to ad_name as well, or is it an alternative to type?

Thanks for providing any additional details! This will help us decide what the best path forward should be.

[JeremyDOwens]

Thanks @fivetran-avinash

In a real world example, but changed to avoid sharing actual specifics

At Allergan Aesthetics, Botox Cosmetic is one of our biggest brands that we promote.

We use title_part_1 in all of these ads to be the same value related to the brand name. e.g. Botox
title_part_2 is a more granular representation e.g. Botox Awareness
title_part_3 is more sparse, but might contain specific details like a catchphrase. e.g Don't wait for your next Botox treatment

This is specific to our implementation, and I'm not certain if that's the common pattern, but in all cases title_part_1 is insufficient.

After all of this, because there are cases where all are null - specifically for ads with type = 'Responsive Search Ad' we need to handle that in some way. Responsive search ads are associated with a domain that they link to. E.g. alle.com, botoxcosmetic.com

So, when I overrode the model for our marketing team, I used this selector:

        coalesce(
            title_part_3,
            title_part_2,
            title_part_1,
            title,
            type || '-' || domain
        ) as ad_name,

I think there's likely enough variation in implementations across companies that having the facility to inject a selector like that would be helpful... but my vision is limited to Allergan currently.

[fivetran-avinash]

Thanks @JeremyDOwens for elaborating.

So to break it down,

1. title_part_1 isn't always sufficient as the ad_name, but should remain the default value.
2. title_part_2, title_part_3, title, type and domain are elements that could be brought into ad_name.
3. The order in which they are brought in is predictable for your use case but could differ for others.
4. There are cases we would want to concatenate fields together, like type and domain, rather than pick just one of these fields.

I think your solution should work for your use case. But the introduction of concatenation does make it a bit more of a complex exploration I'd want to validate before merging this in. Are there any other advanced functions we should take into consideration that could be applied to the ad_name field, or do you think this covers most of the bases for the Microsoft ad_name ?

[JeremyDOwens]

Concatenation is the only thing that I can see a clear case for.

[fivetran-avinash]

Thanks @JeremyDOwens ! I think your solution mostly makes sense. The one tweak I think we would want to make is to remove the title_part_1 from the dbt_project.yml and hardcode it in as the default value of the variable instead, but otherwise it mostly looks good.

We plan to accept your PR into the coming sprint and will let you know when we're ready to start developing! In the meantime, could you also try and apply the above suggestion to the PR, as well as update integration_tests/dbt_project.yml with the new version?

[fivetran-reneeli]

Hi @JeremyDOwens ! We'll be taking this up this sprint and I'll be merging in your PR shortly; just a heads up in case you would like to make any other updates. Thank you!

[JeremyDOwens]

Thank you!