Search API Connector Documentation

Print

Import LinkedIn Ads Data to Google Sheets

premium

In this guide, we will pull data from the LinkedIn API directly into Google Sheets, using the API Connector add-on for Sheets.

The LinkedIn API limits data for privacy reasons; don't expect a way to pull out profile information for anyone but yourself. However, this API is perfect for pulling out advertising performance data from the LinkedIn Ads Reporting API.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Connect to the LinkedIn Ads API

The easiest way to get started with the LinkedIn Ads API is through API Connector’s built-in integration.

LinkedIn has created a new versioned API, and deprecated the old API in early 2023 (info). In API Connector, the old unversioned API is now marked as LinkedIn Ads (deprecated) in the application menu, and will be removed entirely once LinkedIn removes access. Please switch to the updated API as soon as possible to avoid disrupting your reports.
  1. Select LinkedIn Ads from the drop-down list of applications
    linkedinads-application
  2. Under Authorization, click Connect to LinkedIn
    linkedinads-authorization
  3. You will be directed to LinkedIn and asked to authorize the connection. Click Allow.
    linkedinads-allow
  4. You'll now be returned to your Google Sheet, and can verify that your LinkedIn API connection is active.

Part 2: Pull Data from LinkedIn Ads to Sheets

Now that we’re connected, let’s pull some data into Sheets.

  1. In the Endpoint section, choose /adAnalytics?q=analytics to get reporting data in your sheet.
    linkedinads-endpoints
  2. In the Request parameters section, select one or more of your accounts from the dropdown menu.
  3. Fill in the other required parameters: dateRange, fields, pivot, and timeGranularity. For dateRange, select fixed dates from the calendar input or use dynamic values from the cells in your sheet.
    linkedinads-dateinputs
  4. Optionally enter the text (,elements(*,pivotValue~())) under the projection field. This will retrieve some additional useful fields like campaign names (without using projection, you'll get just the IDs).
  5. Click Edit fields to select just the fields you want and assign them to specific columns in your report.
  6.  Run your request.
    linkedinads-response2

Part 3: Create a Custom API Request

Alternatively, you can create a custom request instead of using API Connector’s built-in integration, using any of the API URLs shown in the API documentation

Example 1: Fetch metrics with Statistics Finder

Here's a complete example using LinkedIn's X-Restli protocol and their new versioned API. The LinkedIn Statistics Finder method allows you to include up to three "pivots" (i.e. dimensions). Substitute in your own account ID where it says your_account_id, and your own dates into the dateRange parameter..

  • Application: Custom
  • Method: GET
  • Request URL: https://api.linkedin.com/rest/adAnalytics?q=statistics&fields=pivotValues,dateRange,clicks,impressions,likes,shares,costInLocalCurrency&pivots=List(CREATIVE,CAMPAIGN,ACCOUNT)&dateRange=(start:(day:01,month:08,year:2023),end:(day:31,month:08,year:2023))&timeGranularity=DAILY&accounts=List(urn%3Ali%3AsponsoredAccount%3Ayour_account_id)
  • OAuth: LinkedIn Ads
  • Headers
    • X-Restli-Protocol-Version: 2.0.0
    • LinkedIn-Version: 202311
linkedinads-statisticsresponse

Note the values returned in the elements.pivotValues fields as these are the URNs (IDs) that we'll look up next.

Example 2: Fetch campaign names with URN Resolution

LinkedIn's API is rather inconveniently designed as it generally returns URNs (IDs) instead of names. The request above will return URNs for the three pivots listed (CREATIVE, CAMPAIGN, and ACCOUNT). To convert these URNs into meaningful names, we need to make a separate requests, using the LinkedIn URN resolution lookup table to determine the correct endpoint.

For this example, we'll look at the elements.pivotValues field reading urn:li:sponsoredCampaign:xxxxxxxx . From the URN resolution lookup table, we can see that the sponsoredCampaign string means we call the /adCampaigns endpoint.

Plug your account ID and campaign ID into the request URL below.

  • Application: Custom
  • Method: GET
  • Request URL: https://api.linkedin.com/rest/adAccounts/your_account_id/adCampaigns/your_campaign_id
  • OAuth: LinkedIn Ads
  • Headers
    • X-Restli-Protocol-Version: 2.0.0
    • LinkedIn-Version: 202311

As you can see, that returned the campaign name.

Example 3: Fetch ad creative names with the content reference field

OK, now it gets a bit complicated 😀

Ad creatives are returned with an URN that looks like this urn:li:sponsoredCreative:xxxxxxxxx

Just as with sponsoredCampaign, you can use the URN resolution table to find the correct endpoint for sponsoredCreative expansion, which is /creatives. Run a request like this to the /creatives endpoint: https://api.linkedin.com/rest/adAccounts/your_account_id/creatives/urn%3Ali%3AsponsoredCreative%3Ayour_creative_id

However, unlike the /adAccounts and /adCampaigns endpoints, running a request to the /creatives endpoint does not directly return the ad name. Instead, check the value returned in the content.reference field, and make a second call to the following "sub content" endpoints based on that value (source: cheat sheet provided by LinkedIn).

content.reference examplesub content endpoint
nonenot needed
urn:li:adInMailContent:1020065https://api.linkedin.com/rest/inMailContents/urn%3Ali%3AadInMailContent%3A1020065
urn:li:share:6334225903081525248https://api.linkedin.com/rest/posts/urn%3Ali%3Ashare%3A6334225903081525248
urn:li:ugcPost:6364155080052219904https://api.linkedin.com/rest/posts/urn%3Ali%3AugcPost%3A6364155080052219904

For example, my content.reference value above contains the share string, so it corresponds to the https://api.linkedin.com/rest/posts/urn%3Ali%3Ashare%3A6334225903081525248 example endpoint. If I plug in my own reference ID, it returns the ad name.

Ta-da! That's the name of my ad. Hopefully this is useful/interesting for those confused or unsure of how to fetch ad creative names from the new LinkedIn APIs.

Part 4: Handle Pagination

By default, LinkedIn's /adAnalytics endpoint will return just 1000 records. To return more, apply API Connector's automatic pagination handling as follows

  • Pagination: next page URL
  • Next page path: paging.links.href
  • Run until: choose when to stop fetching data
    linkedinads-nextpage

Part 5: Notes

  • Use the visual field editor to re-arrange columns (just click Edit Fields before running your request).
  • LinkedIn sends back dates split into separate year, month, and day fields. To transform these into a regular Sheets date, set a data destination of cell B1, and then add the following function into cell A1: =arrayformula(if(J2:J<>"", date(J2:J,H2:H,I2:I),"")). The function assumes years are in column J, months are in H, and days are in I; adjust as needed.
  • To view or manage the connection on LinkedIn, click here: https://www.linkedin.com/psettings/permitted-services

Part 6: API Documentation

Official API documentation: https://docs.microsoft.com/en-us/linkedin/marketing/integrations/ads-reporting/ads-reporting

51 thoughts on “Import LinkedIn Ads Data to Google Sheets”

  1. Hi! This was incredibly helpful. Unfortunately all the campaign, campaign groups, creatives, and companies are coming in as numbers instead of using the actual names. So it's very hard to match up which campaign goes with which campaign code. Is there an API that pulls in the name instead of just the value?

    Reply
    • Hey Kat! Thanks for the message. Can you please try this?
      https://api.linkedin.com/v2/adCampaignsV2/?q=search

      I believe it will return a list of all your campaigns with both names and IDs, so you can use that to match up the data from the other queries. There might be a better way but that's how I did it :p. Let me know if that works for you.
      Edit: you can get this all in one go using projection, I put some examples above.
      Edit 2: LinkedIn has now deprecated their v2 API as well as projection, so you need to run a separate request for each URN to fetch its associated value. I updated the custom request section with info on how that works, and we'll be updating our preset integration to make those fetches automatically in the background.

      Reply
  2. Hi I was hoping you might be able to give me some guidance.
    I have all the data coming in fine, GREAT JOB, but i'm not able to pull any lead or conversion data from an Ad Campaign that is counting conversions as a download from LinkedIn itself.
    Do you have any ideas the ExternalConversions won't work because its happening on LinkedIn. Thanks in Advance.

    Reply
    • If you click this #metrics-available link, you can see all the metrics that LinkedIn provides. I just looked through and some of these look they might be what you're looking for: actionClicks, adUnitClicks, cardClicks, clicks, companyPageClicks, landingPageClicks, oneClickLeadFormOpens, oneClickLeads, totalEngagements. If you check the link you can see the descriptions of each metric, so please check and see if they get you what you need. If so, you'd just add them into the fields parameter of your request URL. Let me know if that works or you have any questions.

      Reply
  3. I cant seem to do a POST request using LinkedIn Connection?
    What is the reason for this? Is it because it is a paid feature or something else?

    Reply
    • Hey Ben, this is because LinkedIn's API requires approval for each specific scope (permission). API Connector limits the scopes it requests to r_ads, r_ads_reporting, and r_basicprofile, which only allow getting, not posting, data. Sorry for the inconvenience. One option would be for you to create your own custom OAuth2 connection to Linkedin, then you could request whatever scopes you need. Or could you please let me know a bit more about what you're trying to do? I will consider requesting additional scopes in the future.

      Reply
      • Hi Ana, I'm looking to generate many ads & campaigns in bulk for an ABM use case where we cite a company name. I have a Custom App with the necessary permissions.
        Maybe I can connect using that Connection using your custom Connection option?

      • Yeah, if you've already made your app on LinkedIn's side, you should be able to set up API Connector's custom OAuth2 connection like this:
        Name: Custom LinkedIn
        Authorization Base URL: https://www.linkedin.com/oauth/v2/authorization?scope=r_ads%2Cr_ads_reporting%2Cr_basicprofile (add on other scopes here)
        Token URL: https://www.linkedin.com/oauth/v2/accessToken
        Client ID: provided by LinkedIn
        Client Secret: provided by LinkedIn

        Custom OAuth gets added in the Manage Connections screen. As long as you add your ad management scopes to the base URL, you will be able to access whatever you need in LinkedIn's API. Can you please try that and see how it goes?

  4. Hi. I have built a request for data with Account and Campaign pivots for multiple accounts - I am observing that the projection decoration does only work for single accounts - so now I do multiple requests for each account to get account + campaign details. Have you observed this too?

    Reply
      • Single accounts request URL (= the one I am working with now):

        https://api.linkedin.com/v2/adAnalyticsV2?q=statistics&dateRange=(start:(day:1,month:1,year:2020))&timeGranularity=MONTHLY&pivots=List(CAMPAIGN,ACCOUNT)&fields=dateRange,impressions,clicks,costInLocalCurrency,costInUsd,totalEngagements,likes,comments,follows,shares,companyPageClicks,landingPageClicks,opens,externalWebsiteConversions,pivot,pivotValues&accounts=List(urn%3Ali%3AsponsoredAccount%3A${accountId})&count=500&projection=(*,elements*(*,pivotValues(*~sponsoredCampaign(id,name,type,status)~sponsoredAccount(id,name,type,status))))

        -

        URL with a multiple advertising accounts - where I do not receive any data from the projected pivot fields:

        https://api.linkedin.com/v2/adAnalyticsV2?q=statistics&dateRange=(start:(day:1,month:1,year:2020))&timeGranularity=MONTHLY&pivots=List(CAMPAIGN,ACCOUNT)&fields=dateRange,impressions,clicks,costInLocalCurrency,costInUsd,totalEngagements,likes,comments,follows,shares,companyPageClicks,landingPageClicks,opens,externalWebsiteConversions,pivot,pivotValues&accounts=List(urn%3Ali%3AsponsoredAccount%3A${accountId1},urn%3Ali%3AsponsoredAccount%3A${accountId2},urn%3Ali%3AsponsoredAccount%3A${accountId3})&count=500&projection=(*,elements*(*,pivotValues(*~sponsoredCampaign(id,name,type,status)~sponsoredAccount(id,name,type,status))))

      • Both requests actually worked fine for me, I got the projected fields back even with multiple account IDs. I wonder if the issue you're having is related to encoding. Their docs say "The values and the resource parameters must be URL encoded but not the , grouping the fields and values. The single resource key had the , encoded because it was part of the whole value."

    • Sure, you can use the costInLocalCurrency and costInUsd fields, e.g. https://api.linkedin.com/v2/adAnalyticsV2?q=analytics&timeGranularity=MONTHLY&dateRange.start.year=2021&dateRange.start.month=1&dateRange.start.day=1&dateRange.end.year=2021&dateRange.end.month=2&dateRange.end.day=1&pivot=CAMPAIGN&fields=costInLocalCurrency,costInUsd,dateRange,pivotValues&accounts[0]=urn:li:sponsoredAccount:123456789

      Reply
    • It depends on the specific ad type, but generally you can get it through projection or by directly calling the adCreatives endpoint: https://api.linkedin.com/v2/adCreativesV2?q=search

      Reply
      • This should work:
        1. After you run https://api.linkedin.com/v2/adCreativesV2?q=search you should see a field called elements.reference, where the values look like urn:li:ugcPost:6861589830204235777.
        2. Take the value you're interested in and plug it into this URL like this:
        https://api.linkedin.com/v2/adDirectSponsoredContents/urn:li:ugcPost:6861589830204235777

        When I do that I get a video title back, please check if it works for you. You can also loop through multiple ugcPost ideas to get all the video names.

  5. Hi Ana,
    last week you supported me to connect Linkedin Pages and it has worked correctly. I am currently trying to connect Linkedin Ads but when I connect it, in Linkedin Pages requests I get an error: {"serviceErrorCode":65601,"message":"The token used in the request has been revoked by the user","status ":401}. When I connect Linkedin Pages again I get the same error for Ads requests. Is there a problem with having both connected simultaneously?

    I really appreciate your support.

    Reply
    • I just tested and was able to connect and run requests from both LinkedIn Ads and LinkedIn Pages, so I'm not yet sure what the issue is. Are you connecting each connection to the same LinkedIn account?

      Reply
    • You can enter projection=(,elements(*,pivotValue~())) to retrieve ad names and other metadata. You can find the projection field in the integration menu, or enter it manually in a custom request.
      Edit: projection has been deprecated, so you'll need to a) run a request to the /creatives endpoint, and b) take the returned content.reference field and run a second request to the endpoint associated with the content.reference value, as shown in this cheat sheet provided by LinkedIn. Each type of ad creative has its own endpoint.

      Reply
  6. @Ana, thanks for good example. i am trying to get all the performance metrics for all my campaing and using this endpoint :
    https://api.linkedin.com/v2/adAnalyticsV2?q=campaign&dateRange.start.day=01&dateRange.start.month=01&dateRange.start.year=2023&dateRange.end.day=31&dateRange.end.month=01&dateRange.end.year=2023&pivot=DEMOGRAPHICS&fields=impressions,clicks,ctr,socialActions,videoViews,cpc,cpm,conversions,conversionRate,averageEngagement,engagementRate,frequency,spend

    But does not work, where am i going wrong?

    Reply
    • Looks like you're using the old v2 endpoints, which are now deprecated. Please check the custom request example above for an example using their new versioned API. You can also try using our preset integration instead as it's a lot easier to set up the requests.

      Reply
      • Connect to LinkedIn as shown here, and then select LinkedIn from the dropdown menu as shown here. Just let me know if anything isn't clear and I'll be happy to help.

  7. These are the fields that i want to bring into my worksheet.
    Spent,Impressions,Clicks,Average CTR,Bid,Average CPM,Average CPC,Conversions,Cost Per,Conversion,Leads,Cost Per Lead

    /adAnalytics?q=analytics is correct. Pivot =ACCOUNT but in the fields i cannot find the fields mentioned above. How do i achieve this?

    Reply
  8. https://api.linkedin.com/v2/adAnalyticsV2?q=statistics&pivots[0]=CAMPAIGN&pivots[1]=CREATIVE&dateRange.start.day=1&dateRange.start.month=1&dateRange.start.year=2019&timeGranularity=DAILY&campaigns[0]=urn:li:sponsoredCampaign:124782804&fields=externalWebsiteConversions,dateRange,impressions,landingPageClicks,likes,shares,costInLocalCurrency,pivot,pivotValues

    Hi Ana, can you help me revise the above to get the

    all campaign names
    all creative names
    daily segments
    and dates by cell reference?

    Reply
    • There's no way to get all campaign & creative names in one API call, since they return "urn" IDs from the main reporting endpoints, which then need to be looked up using additional endpoints to get the associated names. LinkedIn gave me a "cheat sheet" which you can see here, and I added some more detailed instructions on using it in the custom request section.

      I suggest using API Connector's built-in integration, because this is a very complex API that's not easy to understand or customize. In addition, please note that the example you've provided uses LinkedIn's deprecated API that will be removed soon.

      Reply
  9. Hey there! Is there any way to search for all Campaign names within a specific Campaign Group?

    Cannot for the life of me get this to work: https://api.linkedin.com/v2/adCampaignsV2?q=search to work, just throws error:
    {"message":"Request would return too many entities. .","status":400}

    Also, alternatively... and this is a lonnnnng shot... any way to extract links from individual ads? Would love to be able to see and parse UTM parameters from listed ads. Thanks!

    Reply
    • {"message":"Request would return too many entities. .","status":400}
      This error occurs when you have more than 1000 records (info).
      Can you please try running that request using API Connector's preset integration instead? (Select LinkedIn Ads > Get Available Campaigns from API Connector's dropdown application menu). That way you'll a) be using LinkedIn's newest API (the v2 syntax you used will be deprecated soon), and b) can set the "count" parameter to 1000 or lower, and use pagination to get all the records.

      any way to extract links from individual ads?
      I think you can also do this using API Connector's preset integration. Select your account, date range, and fields, set pivot to CREATIVE, and set projection to (*,elements*(*,pivotValue~())) . That should retrieve a field named "content.textAd.landingPage" that contains the full landing page URL.

      Reply
  10. I only recieve this as the output when following custom GET request with Linkedin OAUTH copied and pasted from the example. This is what it was giving me previously with no changes when the query criteria changed

    paging.start paging.count paging.links.1 elements.1
    0 10

    Reply
    • That means the request worked but LinkedIn didn't send back any data. I'd double check that the account you authenticated with has ads data available for the time period you've selected.

      Reply

Leave a Reply to Ana Cancel reply

Jump To