[Bug] Linkedin Ads URL not showing up due to API changes

ryan-loveland commented 5 months ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

I am using the dbt ad_reporting package and while testing I noticed the ad_reporting__url_report has no data for LinkedIn. Looking at the underlying models - the URL value is being pulled and parsed from the creative_history.click_uri value which doesn't appear to exist in our connector.

I reached out to support and they told me that there was an API change:

Your click_uri should now be spotlight_landing_page and text_ad_landing_page in the CREATIVE_HISTORY table based on the LinkedIn API Creative migration.

This would mean the models starting from the source model: https://github.com/fivetran/dbt_linkedin_source/blob/main/models/stg_linkedin_ads__creative_history.sql and all that rely on it would need to be updated to ensure the URL is being passed to the final ad_report model.

Relevant error log or model output

No response

Expected behavior

The dbt package uses the updated fields to support the final URL report.

dbt Project configurations

NA

Package versions

package: fivetran/ad_reporting version: 1.7.1

What database are you using dbt with?

snowflake

dbt Version

latest version

Additional Context

No response

Are you willing to open a PR to help address this issue?

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

fivetran-catfritz commented 5 months ago

Hi @ryan-loveland thank you for bringing this to our attention! A quick question for you while I look into this--when did you first set up your Linkedin Ads connector? Also out of curiosity, do you have the column creative_history.click_uri with no data populated, or do you not have this column at all?

ryan-loveland commented 5 months ago

@fivetran-catfritz

I added the LinkedIn Ads connector on April 30th 2024.
I do not see the creative_history.click_uri as a column in the connector Schema.

fivetran-catfritz commented 5 months ago

@ryan-loveland thank you for the information. This is definitely something we want to update on our end. I'll discuss this internally to get a better sense of when we plan to take this on, so stay tuned!

fivetran-catfritz commented 5 months ago

Hi @ryan-loveland we still need to verify a few items in order to create the right fix for all customers, but in the meantime I created a test branch you can try for your case. Would you be able to give this a run and see if the data is brought through correctly? You can install the test branch by replacing the linkedin lines in your packages.yml with:

packages:
  - git: https://github.com/fivetran/dbt_linkedin.git
    revision: feature/click-uri-deprecation
    warn-unpinned: false

Let me know how it goes!

fivetran-catfritz commented 5 months ago

Hi @ryan-loveland hope you had a nice weekend! Just checking in if you're interested in testing out the release branch I mentioned?

georgios-ntanakas commented 4 months ago

Hi there! We are also facing the same issue. Fivetran connector added in March 14 and the click_uri field exists in the table but is null everywhere. Of course, the field cannot be seen in the Schema tab of the Fivetran connector. @fivetran-catfritz I had the look at the branch but it only seems to update 2 yml files. Wouldn't an update to the ad_reporting__url_report model be necessary to fix this?

fivetran-joemarkiewicz commented 4 months ago

Hi @georgios-ntanakas, thanks for chiming in on this ticket and sharing you are also experiencing a similar issue.

In regards to the branch @fivetran-catfritz shared, the primary changes actually take place in the dbt_linkedin_source package branch which this development branch is now referencing. You can see the source changes here.

If you are currently using the Fivetran Ad Reporting package and would like to test the branch @fivetran-catfritz suggested, then you can attempt to use the below Ad Reporting branch which installs the above Linkedin Ads development changes which include the click_uri field change.

packages:
  - git: https://github.com/fivetran/dbt_ad_reporting.git
    revision: feature/click-uri-deprecation
    warn-unpinned: false

Let me know if you are able to test either the LinkedIn or Ad Reporting development branches and if this address this highlighted issue. Thanks!

fivetran-catfritz commented 4 months ago

Hi @ryan-loveland and @georgios-ntanakas! Checking in here if either of you were able to or are interested to try out the test branch. Let me know if you have any questions!

georgios-ntanakas commented 4 months ago

Hey @fivetran-catfritz, thanks for follow-up! I would be happy to try the test branch. Unfortunately, however, we get null data also for the text_ad_landing_page and spotlight_landing_page columns, so the fix won't help us. We're looking into LinkedIn to understand whether there is smth wrong with our setup. I'll let you know for any updates.

fivetran-catfritz commented 4 months ago

Hi @georgios-ntanakas thank you for trying it out and the extra information. That is helpful! I just learned that our engineering team is working on improvements to this connector that may provide an alternative solution for this issue. I will keep you posted.

georgios-ntanakas commented 4 months ago

Thank you for the update @fivetran-catfritz and for keeping us in the loop!

ryan-loveland commented 4 months ago

@fivetran-catfritz my apologies for the delayed response. Given the team is working on some improvements to the connector, should I hold off on testing the changes?

fivetran-catfritz commented 4 months ago

Hi @ryan-loveland no worries and thanks for the response! Do you have data in your text_ad_landing_page and spotlight_landing_page source table's columns? If not, then it wouldn't make sense to try out the changes. If you do, would you be able to try out the branch? I am thinking it could be useful to provide an intermediate fix for some customers while engineering makes their updates.

ryan-loveland commented 3 months ago

@fivetran-catfritz I reviewed our connector data and do see URL info in those two columns if the ad type is either a text_ad or spotlight_ad. I tested the changes to the package and verified the values are showing up in the final model: ad_reporting__url_report 👍

fivetran-catfritz commented 3 months ago

@ryan-loveland Thank you for confirming--super helpful! We will go ahead with the proposed fix most likely around the end of the month.

fivetran-joemarkiewicz commented 2 months ago

Hey @ryan-loveland, I just wanted to chime in here and let you know I'll be taking this on in the current sprint and will plan to have the changes @fivetran-catfritz suggested available in the next two weeks.

Thanks everyone for chiming in here and helping us come up with a viable and accurate solution!

fivetran-joemarkiewicz commented 2 months ago

Hi All,

I wanted to share that we have these PRs above prepped and ready to rollout. We do have a few other changes which are slated to go live for our LinkedIn Ads and Ad Reporting packages in October. Therefore, I will plan to hold off on these releases until then. Please let me know if you have any concerns with this timeline. Thanks!

fivetran / dbt_linkedin