Skip to main content
Documentation
Feature Requests Trust Center Status Page

Community

Connector Improvement: Join fields for Linkedin Company Pages + Linkedin Ads Analytics

Answered
Kevin Offemaria User
  • Edited

Hello - Please add a common ID field between the Linkedin Company Pages and Ad Analytics, in order to join tables between the two schemas. 

The business reason is we gather URL's from 3 different Linkedin endpoints, which are currently separated for each Linkedin connector.

 

Only available in Ads Analytics connector:

  1. InMail - https://api.linkedin.com/v2/adInMailContentsV2/123

Only available in Company Pages connector:

  1. Shares - https://api.linkedin.com/v2/shares/urn:li:share:123
  2. Sponsored Vid URN- https://api.linkedin.com/v2/ugcPosts/urn:li:ugcPost:123

 

Another option is to include all 3 endpoints and data outputs into one connector.

Please sign in to leave a comment.

Comments

2 comments

Date Votes
  • Official comment
    Drew Fivetranner

    Hi Kevin Offemaria, Drew from the product team here!

    Thanks for sharing this feature request. I'd love to have a bit more information about how you would want to map these fields across schemas. Is joining at the account level not enough? How would these be more closely related in your use case?

    I'll also ask our contacts at LinkedIn if they have any ideas about this.

  • Kevin Offemaria User

    Hi Drew - Unfortunately, joins on account_id level are not enough in this case due to the granularity on the Page Content level.

    But the great news is we actually found ID's that we can use to join data on both connectors. See sample query below. This requires further testing to confirm. Once confirmed, it would be great to have them on the FT documentation.

    SELECT  t4.reference, t6.id, t10.ugc_post_id
        FROM (select id, campaign_id, status, type, text_ad_title, text_ad_text, click_uri, reference, row_number() over (partition by id order by last_modified_time desc) as t4_rank
                    from linkedin_Ads.creative_history) as t4
        LEFT JOIN (SELECT id, content_content_entities, row_number() over (partition by id order by last_modified_time desc) as t6_rank
                    FROM linkedin_company_pages.share_history) as t6
            ON REGEXP_REPLACE(t4.reference, '[^0-9]+') = t6.id
                   AND t6.t6_rank = 1
        LEFT JOIN linkedin_company_pages.ugc_post_share_content_media AS t10
            ON t6.id = REGEXP_REPLACE(t10.ugc_post_id, '[^0-9]+');