Import LinkedIn Ads Data to Google Sheets

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 only supports authentication via OAuth2, so we’ll be connecting using API Connector’s built-in OAuth2 integration for LinkedIn Ads. 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.

OAuth2 is a paid feature, so please upgrade your account or install API Connector for a free trial.

CONTENTS

• What Does This Integration Do?
• Before You Begin
• Part 1: Connect to the LinkedIn Ads API
• Part 2: Create a LinkedIn API Request URL
• Part 3: Pull LinkedIn Profile Data into Sheets
• Part 4: More Example API URLs
  • Get Metadata
  • Get Ad Performance
• Part 5: Notes
• Appendix: API Request URL Builder (NEW)

WHAT DOES THIS INTEGRATION DO?

This integration pulls advertising data from LinkedIn’s API into Google Sheets. It enables the following scopes: r_ads (retrieve your advertising accounts), r_ads_reporting (retrieve reporting for your advertising accounts), and r_basicprofile (use your basic profile including your name, photo, headline, and current positions)

BEFORE YOU BEGIN

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

PART 1: CONNECT TO THE LINKEDIN ADS API

If you haven’t connected API Connector to LinkedIn before, you’ll first need to initiate the connection as follows:

1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
2. In the list of available connections, find LinkedIn Ads and click Connect.
   
3. You will be directed to LinkedIn and asked to authorize the connection. Click Allow.
   
4. You’ll now be returned to your Google Sheet, and can verify that your LinkedIn API connection is active in the Connections screen.
   

PART 2: CREATE A LINKEDIN API REQUEST URL

For our first request, we’ll get some basic information about your own LinkedIn account.

• API root: https://api.linkedin.com/v2
• Endpoint: /me

Putting it together, we get the full API Request URL.

https://api.linkedin.com/v2/me

PART 3: PULL LINKEDIN API DATA INTO SHEETS

We can now enter our values into API Connector and start importing LinkedIn data into Google Sheets.

1. In the Create Request interface, enter the Request URL we just created.
   
2. We don’t need any headers for this API, so just leave that section blank.
3. Under Authentication, choose LinkedIn Ads from the Connections dropdown.
   
4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
5. Name your request and click Run. A moment later you’ll see some information about your account populate your sheet.
   

PART 4: MORE EXAMPLE API URLS

Now that we’ve validated the connection, we can start pulling some more data. You can access the full list of metrics available in the ads reporting API here, but if you just want to get started, you can try the following URLs.

GET METADATA

LinkedIn has a rather inconveniently designed API that returns IDs without names, so I suggest first running all four of these metadata requests so that they’re available when you make your summary reports or dashboards. The metadata is where you can get campaign names, statuses, and other descriptive values.

• List of ad accounts (note the ID that gets returned in the elements » id field, we can use this for subsequent requests).
  

  https://api.linkedin.com/v2/adAccountsV2?q=search

• List of ad campaign groups
  

  https://api.linkedin.com/v2/adCampaignGroupsV2?q=search

• List of ad campaigns (this produces a ton of columns so you can clean it up a bit with a fields parameter like this)

  https://api.linkedin.com/v2/adCampaignsV2?q=search&fields=id,account,status,format,locale,type,version,runSchedule,campaignGroup,changeAuditStamps,dailyBudget,costType,creativeSelection,unitCost,name,offsiteDeliveryEnabled,id,account,status

  
• List of ad creatives
  

  https://api.linkedin.com/v2/adCampaignGroupsV2?q=search

To filter for active campaigns or creatives, you’d add a filter like this to the end of your URL: &search.status.values[0]=ACTIVE. Other filters are shown in LinkedIn’s API documentation under “Search for Campaigns” (link).

GET AD PERFORMANCE

Take a look at these examples, but it’s probably easiest to use the API request builder to create your ad performance requests.

• Summary ad account performance, daily from 2021 (substitute in the account ID value from elements » id where it says 502849368).
  

  https://api.linkedin.com/v2/adAnalyticsV2?q=analytics&pivot=ACCOUNT&dateRange.start.year=2021&dateRange.start.month=1&dateRange.start.day=1&dateRange.end.year=2021&dateRange.end.month=3&dateRange.end.day=31&timeGranularity=DAILY&fields=externalWebsiteConversions,dateRange,impressions,landingPageClicks,likes,shares,costInLocalCurrency,pivot,pivotValue&accounts[0]=urn:li:sponsoredAccount:502849368

  Valid timeGranularity values include DAILY, MONTHLY, YEARLY, and ALL
  
  
• Summary performance by campaign

  https://api.linkedin.com/v2/adAnalyticsV2?q=analytics&dateRange.start.year=2021&dateRange.start.month=1&dateRange.start.day=1&dateRange.end.year=2021&dateRange.end.month=3&dateRange.end.day=31&timeGranularity=DAILY&pivot=CAMPAIGN&fields=dateRange,impressions,approximateUniqueImpressions,videoViews,clicks,videoStarts,videoFirstQuartileCompletions,videoMidpointCompletions,videoThirdQuartileCompletions,externalWebsiteConversions,comments,reactions,totalEngagements,costInLocalCurrency,pivot,pivotValue&accounts[0]=urn:li:sponsoredAccount:502849368 

  
  
• Suggested bid amounts for a campaign targeting LinkedIn members between the ages of 18 and 24
  

  https://api.linkedin.com/v2/adBudgetPricing?account=urn:li:sponsoredAccount:502849368&bidType=CPM&campaignType=SPONSORED_INMAILS&matchType=EXACT&q=criteria&target.includedTargetingFacets.ageRanges[0]=AGE_18_24

• Campaign reporting for a specific campaign, daily from 2019 (where it says 124782804, substitute in the campaign ID value retrieved from the list of ad campaigns):
  

  https://api.linkedin.com/v2/adAnalyticsV2?q=analytics&pivot=CAMPAIGN&dateRange.start.year=2021&dateRange.start.month=1&dateRange.start.day=1&dateRange.end.year=2021&dateRange.end.month=3&dateRange.end.day=31&timeGranularity=DAILY&campaigns[0]=urn:li:sponsoredCampaign:124782804&fields=externalWebsiteConversions,dateRange,impressions,landingPageClicks,likes,shares,costInLocalCurrency,pivot,pivotValue

  

PART 5: NOTES

• As mentioned above, LinkedIn’s API requires pulling IDs and names separately, and then matching them up. This is easier when you have the ID in the first column to use as a key, so I suggest sorting each response with a snippet of JMESPath. For example you can use this snippet to neatly sort the response from the example /v2/adCampaignsV2?q=search query provided above:
  elements[].{campaign_id:id,account_urn:account,campaignGroup_id_urn:campaignGroup,campaign_name:name,status:status,format:format,type:type,locale:locale,version:version,runSchedule:runSchedule,changeAuditStamps:changeAuditStamps,dailyBudget:dailyBudget,costType:costType,unitCost:unitCost,creativeSelection:creativeSelection,offsiteDeliveryEnabled:offsiteDeliveryEnabled}
• Some useful “pivot” (aka breakdown) values include the following (this, and other info comes from https://docs.microsoft.com/en-us/linkedin/marketing/integrations/ads-reporting/ads-reporting):
  • 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.
• There are 2 types of calls you can make to the Ads Reporting API: q=analytics and q=statistics. q=analytics is the method used in the examples above, and is used when you are only breaking your data down by a single pivot value. If you have two pivot values, use the q=statistics query string. According to LinkedIn’s documentation, you can pull in multiple pivot values at once with the q=statistics query string, but it doesn’t seem to work reliably.
• As of September 30, 2020, LinkedIn requires that you include the names of fields in your query, up to 20 at a time. You can do so by appending a query string like this: &fields=externalWebsiteConversions,dateRange,impressions,landingPageClicks,likes,shares,costInLocalCurrency,pivot,pivotValue

APPENDIX: API REQUEST BUILDER

This is a convenient tool for easily building LinkedIn API request URLs. Enter your account ID and select your dates, pivot dimension, and fields.