fivetran / fivetran/dbt_linkedin

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

Open
#53 5 comments 0 reactions 0 assignees View on GitHub
Dominant language
No language data
Stars
3
Forks
14
PR merge metrics
No merged PRs in 30d

Description

### 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:
![image](https://user-images.githubusercontent.com/16456253/217979397-a05b33aa-4be8-48f2-b37a-29103f86f67b.png)

### Are you interested in contributing this feature?

- [X] Yes.
- [ ] Yes, but I will need assistance and will schedule time during your [office hours](https://calendly.com/fivetran-solutions-team/fivetran-solutions-team-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.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.