Search API Connector Documentation

Print

Import LinkedIn Ads Data to Google Sheets

Last Updated On
byAna
< Knowledge Base
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 is extremely limited and blocked off; 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.

Related article: Import LinkedIn Pages Data to Google Sheets

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.

  1. Select LinkedIn Ads from the drop-down list of applications
    linkedinads-application
  2. Under Authorization, click Connect to LinkedIn Ads
    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. Under Endpoint, choose /adAccountsV2?q=search. This will return your account ID(s), which we can use in subsequent requests.
    linkedinads-endpoints
  2. There are no other required parameters for this endpoint, so just select a destination sheet, name your request, and click Run. The account IDs are listed in the elements.id field.
    linkedinads-response
  3. Now select the /adAnalyticsV2?q=analytics endpoint to get reporting data in your sheet.
  4. Under Request parameters, add your account ID using this format: List(urn%3Ali%3AsponsoredAccount%3A111111111). LinkedIn will only recognize this encoded value so copy and enter it exactly as written, substituting in your own account ID for 111111111.
  5. Fill in the other required parameters: dateRange, fields, pivot, and timeGranularity. 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).
  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. To create a custom request, add the complete URL into the request URL field, select LinkedIn Ads from the OAuth menu, and add a Header of Key = X-Restli-Protocol-Version, Value = 2.0.0

To easily convert your preset request to a custom request, click Output Options > More Options > Add Request URL before running your request. This will print your URL into your sheet where you can copy/paste it.

Part 4: Fetch and Append New Data

Rather than retrieving the entire data set each time you run your request, you can set your request to fetch new data only. There are a few approaches you could take; here’s one:

  1. For this example, we'll create a custom request. As mentioned above, you can convert your preset integration into a custom request by clicking Add Request URL. Make sure to also include the Header of Key = X-Restli-Protocol-Version, Value = 2.0.0.
  2. Run an initial request like https://api.linkedin.com/v2/adAnalyticsV2?q=analytics&accounts=List(urn%3Ali%3AsponsoredAccount%3A507152744)&dateRange=(start:(day:01,month:07,year:2022),end:(day:23,month:07,year:2022))&fields=pivot,pivotValue,dateRange,clicks,costInLocalCurrency,impressions&pivot=CAMPAIGN&timeGranularity=DAILY
  3. Use the field editor to select just the fields you want and assign them to specific columns in your report.
  4. Create a new sheet called Inputs that contains date functions in the format required by LinkedIn, e.g. =text(today()-1,"yyyy"), =text(today()-1,"mm"), and =text(today()-1,"dd"). (You can adjust these date functions to get the time slice you need, e.g. here's how to dynamically pull in the last day of the month).
    linkedinads-dynamic
  5. Switch your request to Append mode and reference those dynamic dates in your request URL like this: https://api.linkedin.com/v2/adAnalyticsV2?q=analytics&accounts=List(urn%3Ali%3AsponsoredAccount%3A507152744)&dateRange=(start:(day:+++Inputs!D2+++,month:+++Inputs!C2+++,year:+++Inputs!B2+++),end:(day:+++Inputs!D3+++,month:+++Inputs!C3+++,year:+++Inputs!B3+++))&fields=pivot,pivotValue,dateRange,clicks,costInLocalCurrency,impressions&pivot=CAMPAIGN&timeGranularity=DAILY
  6. This will ensure that each request only retrieves data from within the bounds of your dynamically updated dates, and append mode will add that new data to the end of your existing dataset.
  7. Set your request to run on a schedule. You won’t need to update your request again.

Part 5: Handle Pagination

By default, LinkedIn's /adAnalyticsV2 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 6: 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 7: API Documentation

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

Previous Import KuCoin Data to Google Sheets
Next Import LinkedIn Pages Data to Google Sheets

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

  1. Can you post an example api using the q=statistics call? I'm trying to pull in multiple pivot values but I can't seem to get it to work.

    Thanks

    Reply

    • Hey Brian! Can you please try something like this?
      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

      That seemed to work for me but I have a pretty limited data set to test on. Let me know how it goes.

      Reply

  3. 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.

      Reply

  4. 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

    • I can't say for sure, but 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

  5. 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,r_ads_reporting,r_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?

      • Hi Ana,

        Thank you so much for providing the example on how to set up the API Connector for LinkedIn Developers.

        I followed your steps but it looks like I'm missing something.

        After doing as follows:
        Name: Custom LinkedIn
        Authorization Base URL: https://www.linkedin.com/oauth/v2/authorization?scope=r_ads,r_ads_reporting,r_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

        However, I find 2 issues:

        1) When adding "https://www.linkedin.com/oauth/v2/authorization?scope=r_ads,r_ads_reporting,r_basicprofile" to my authorization base URL, I get the following message: "Redirect URLs cannot contain commas".

        2)If I just include 1 scope and try to run the connection, I get the following message from LinkedIn: "Bummer, something went wrong. The redirect_uri does not match the registered value!"

        I checked and both URLs where exactly the same.

        What am I missing?

        Thanks in advance!

      • 1) The authorization base URL should be encoded, can you please try this instead?
        https://www.linkedin.com/oauth/v2/authorization?scope=r_ads%2Cr_ads_reporting%2Cr_basicprofile
        2) For the redirect_uri, please make sure you've given them the correct URL. From what you wrote above it sounds like you may be mixing up the authorization base URLs with the redirect URL. The redirect URL is a URL that you provide to LinkedIn, it should be https://script.google.com/macros/d/12COOkin8nodCH7fZGIBu0D2jWY8-AEA0uvElt4Ph4wRbLUD4wslqQUfG/usercallback
        Let me know how that goes or feel free to send a message if you're still stuck.

  7. Hi,
    Thanks you so much for this instruction, very helpful and I came from knowing nothing how this works to almost managed to get everything I need 🙂 Could you maybe help me with the last bit? I managed to get demographic data with pivot on "member_company", where I can see the company's organization id. I need to match the ids with the organization names, but I can't get the organization endpoint to work. Do you maybe have experience with this? Thanks in advance.

    Reply

    • You can get it via the projection and pivotValue~(localizedName) parameters, like this:
      https://api.linkedin.com/v2/adAnalyticsV2?q=analytics&dateRange.start.year=2021&dateRange.start.month=5&dateRange.start.day=28&dateRange.end.year=2021&dateRange.end.month=9&dateRange.end.day=30&timeGranularity=MONTHLY&accounts=urn:li:sponsoredAccount:502849368&pivot=MEMBER_COMPANY&projection=(*,elements*(externalWebsiteConversions,dateRange(*),impressions,landingPageClicks,likes,shares,costInLocalCurrency,approximateUniqueImpressions,pivot,pivotValue~(localizedName)))&fields=externalWebsiteConversions,dateRange,impressions,landingPageClicks,likes,shares,costInLocalCurrency,pivot,pivotValue

      Reply

  9. Hey, can you please help me to what the event registration refers to which field?
    And the api to get the event registration, click event registration, and view event registration.

    Thanks.

    Reply

  10. Hi, Can you help me providing an example of an api for pivot = CONVERSION?
    “pivot” (aka breakdown) values:
    COMPANY – Group results by advertiser’s company.
    ACCOUNT – Group results by account.
    CAMPAIGN – Group results by campaign.
    CREATIVE – Group results by creative.
    CONVERSION – Group results by conversion.

    Reply

    • It should be similar to the examples above, but with fields that make sense with the Conversions pivot, e.g.
      https://api.linkedin.com/v2/adAnalyticsV2?q=analytics&pivot=CONVERSION&dateRange.start.day=1&dateRange.start.month=1&dateRange.start.year=2021&timeGranularity=DAILY&accounts[0]=urn:li:sponsoredAccount:502849368&fields=conversionValueInLocalCurrency,externalWebsiteConversions,externalWebsitePostClickConversions,externalWebsitePostViewConversions,viralExternalWebsiteConversions,viralExternalWebsitePostClickConversions,viralExternalWebsitePostViewConversions
      Let me know if that works for you!

      Reply

  11. Hi Ana,
    I am having trouble with getting Creative names. For campaigns I can use "name" as a field and pull all the names from certain account in one call. But with the adCreatives I can't seem to get it to work. Could you help? Thank you.

    Reply

    • Does https://api.linkedin.com/v2/adCreativesV2?q=search work for you? Or can you please share the URL you're using for campaigns so I can better understand what you mean?

      Reply

    • Hi Ana,
      thanks for your quick reply.

      This call still gives empty result, there is no error but the cells are empty. Could it be because we don't have any Text Ads? The most we have are Sponsored Content. I read the post you sent as well, but I don't know how to use projection in this case. Could you help? Thanks in advance.

      Reply

      • Yeah it looks like sponsored content requires a different kind of call. You'll need to do it like this:
        1) get your ad creatives for a specific campaign: https://api.linkedin.com/v2/adCreativesV2?q=search&search.campaign.values[0]=urn:li:sponsoredCampaign:11111111
        2) It will return a field called elements.reference. Plug that value into a URL like this: https://api.linkedin.com/v2/adDirectSponsoredContents/urn:li:ugcPost:11111111
        That will return the name of your sponsored content.
        This is really a very awkward and inconvenient API. Maybe there's an easier way but their documentation is too confusing for me to figure out 😛

  12. 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.

  16. 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.

      Reply

Leave a Comment

Jump To...