[Feature] Add additional Click URI fields to creative history models

bthomson22 commented 1 year ago

Is there an existing feature request for this?

[X] I have searched the existing issues

Describe the Feature

Currently, the creative history models only look for the click_uri field to generate UTM parameters on a per-creative basis. Unfortunately, this field is only populated to TEXT_AD types. Therefore, in downstream URL reports, no ads with the SPONSORED_STATUS_UPDATE or SPONSORED_VIDEO (among others) will populate.

If the org has the LinkedInCreativeURL Feature Flag turned on in Fivetran, there are several other columns where this information can be parsed from. These fields include:

SPONSORED_UPDATE_SHARE_CONTENT_CONTENT_ENTITIES
SPONSORED_VIDEO_USER_GENERATED_CONTENT_POST_VALUE_SPECIFIC_CONTENT_SHARE_CONTENT_MEDIA
SPONSORED_UPDATE_CAROUSEL_SHARE_CONTENT_CONTENT_ENTITIES

The values in those fields resemble the below structure (example data):

[
    {
        "entity": "urn:li:article:12345",
        "entityLocation": "https://example.com/example-page/?utm_medium=medium1&utm_source=source2&utm_campaign=campaign3&utm_term=term4",
        "landingPageTitle": "LEARN_MORE",
        "landingPageUrl": "https://example.com/example-page/?utm_medium=medium1&utm_source=source2&utm_campaign=campaign3&utm_term=term4",
        "thumbnails": [
            {
                "imageSpecificContent": {
                    "height": 627,
                    "width": 627
                },
            "resolvedUrl": "https://media.fakeimageurl.com/12345"
            }
        ],
        "title": "Get started for free."
    }
]

Describe alternatives you've considered

To resolve this in our internal project, we created a model immediately before stg_linkedin_ads__creative_history_tmp that does the following:

with

source as (

    select * from {{ source('linkedin_ads','creative_history') }}

),

click_uris_parsed as (

    select
        *,

        -- sponsored update, video, and carousel ad types don't contain a `click_uri` value
        -- therefore, we need to extract this from the entities field via the Landing Page Url
        sponsored_update_share_content_content_entities[0]:landingPageUrl::string as sponsored_status_update_uri,
        sponsored_video_user_generated_content_post_value_specific_content_share_content_media[0]:landingPageUrl::string as sponsored_video_uri,
        sponsored_update_carousel_share_content_content_entities[0]:landingPageUrl::string as sponsored_update_carousel_uri

    from source

),

final as (

    select
        * exclude (click_uri),

        -- this ensures every ad type has a Click URI - because these are mutually exclusive, the Coalesce function will work here
        coalesce(
            sponsored_status_update_uri,
            sponsored_video_uri,
            sponsored_update_carousel_uri,
            click_uri
        ) as click_uri

    from click_uris_parsed
)

select * from final

This requires the following update in dbt_project.yml to override the creative_history var:

vars:
  linkedin_source:
    creative_history: "{{ ref('base_linkedin_ads__creative_history') }}"

DAG for reference:

Are you interested in contributing this feature?

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

Anything else?

Before putting up a PR, it would be great to know if Fivetran is planning to enable this feature flag by default for all customers. If so, there won't be any need for variable logic so that someone can turn this on/off. Knowing this would be ideal before contributing to the fix.

fivetran-sheringuyen commented 1 year ago

Hi @bthomson22 ! Thank you for bringing this to our attention. I am currently working closely with engineering and product for Linkedin Ads and will discuss this with them. I will circle back with you as soon as I get more information!

fivetran-sheringuyen commented 1 year ago

Hey there @bthomson22! I spoke to our product manager for Linkedin Ads and wanted to cue you in on the next release of the Linkedin Ads connector, in case you are not already aware. We are currently in the process of switching our APIs for the Linkedin Ads connector for all of our customers (see more information in the release notes).

I double checked the fields that you have used in this issue, and unfortunately, these fields are a part of a larger set of fields that will be deprecated with the new API migration. In the meantime, this solution should still continue to work for you, but once your connector updates, these values will start to populate as null going forward (your company should be receiving communications around this before it happens, of course).

That being said, once your connector is updated, we would be happy to work with you to brainstorm a potentially new solution given the new resulting data from the API change.

sfc-gh-cjwehtje commented 1 year ago

Are there any further updates on this topic?

Tracking Advertising based on URLs is essential to us as it is how we tie the dataset together with other sources.

fivetran-joemarkiewicz commented 1 year ago

Hi @sfc-gh-cjwehtje thanks for chiming into this thread! @bthomson22 and I have chatted a bit regarding some possible solutions to adding click uri fields to the models of this package.

One solution that Brandon suggested was to leverage the linkedin company pages connector and package to look up the associated share_history record for an ad and then grab the landing page url from the LinkedIn Company Pages connector data.

This is definitely a viable solution; however, it would require a dependency on another connector and would prefer we can obtain this information directly from the LinkedIn Ads connector. I am still working internally to uncover if there is an alternative solution we can leverage to obtain this information without the use of an additional connector. I will continue to share more as I investigate.

bthomson22 commented 1 year ago

Thanks @fivetran-joemarkiewicz! Just a heads up, we recently had to update our models to leverage the share_content table instead. This is due to connector changes made toward the end of May. Still, there are some ad types (inmail, for example) that aren't populating with landing pages - so we will likely need to leverage more Company Pages tables to get full coverage, specifically inmail_content_history.

I'm happy to share more on how we've worked around this - but to your point, it would be great if it was solved within the LinkedIn Ads connector itself.

fivetran / dbt_linkedin_source