If you've ever manually copied LinkedIn Ad Analytics into a spreadsheet, you know it's one of the most soul-draining tasks in digital marketing. Sure, LinkedIn gives you the data, but getting it into a useful format involves endless clicks, scrolling through reports, exporting CSVs, and then trying to organize it all into something your team or clients can actually read.
It's tedious, it's time-consuming, and worst of all, it's repetitive. Every week, every month, you're stuck doing the same thing. But what if you could just… not? What if you could automate the whole process and have the data show up exactly where you want it—without even lifting a finger?
That’s where Python and Google Sheets API come in. I built a simple script that:
Let’s dive into how to set this up and save yourself a ton of time.
First things first, you need to get access to Google Sheets from your Python script. It sounds complex, but once it's set up, it’s a breeze. Here’s how to do it:
You’ll need to install gspread and google-auth to connect your Python script to Google Sheets. Fire up your terminal and install them using pip:
bashCopy codepip install gspread google-auth google-auth-oauthlib google-auth-httplib2
This will take care of the API connection part.
Now, let’s write the Python script that will:
Here’s the code:
pythonCopy code<code>import requests
from datetime import datetime, timedelta
import gspread
from google.oauth2.service_account import Credentials
# Step 1: Set up Google Sheets API
scope = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]
creds = Credentials.from_service_account_file('path_to_credentials.json', scopes=scope)
client = gspread.authorize(creds)
# Open your Google Sheet
spreadsheet = client.open("LinkedIn Analytics Data") # Change to your sheet's name
sheet = spreadsheet.sheet1 # You can access other sheets if needed
# Clear the sheet before writing new data
sheet.clear()
# Step 2: Fetch LinkedIn Campaign Details
headers = {
"Authorization": "Bearer YOUR_LINKEDIN_API_TOKEN"
}
campaign_url = "https://api.linkedin.com/v2/adCampaignsV2?q=search&search.account.values[0]=urn:li:sponsoredAccount:506383483"
response = requests.get(campaign_url, headers=headers)
campaign_id_to_name = {}
if response.status_code == 200:
campaigns = response.json().get('elements', [])
for campaign in campaigns:
campaign_id = str(campaign.get('id', 'N/A'))
campaign_name = campaign.get('name', 'N/A')
campaign_id_to_name[campaign_id] = campaign_name
else:
print(f"Failed to fetch campaigns. Status Code: {response.status_code}, Response: {response.text}")
# Step 3: Fetch Analytics Data for the Last 90 Days
current_date = datetime.today()
days_to_fetch = 90
all_data = []
for i in range(days_to_fetch):
day_to_fetch = current_date - timedelta(days=i)
start_day = day_to_fetch.day
start_month = day_to_fetch.month
start_year = day_to_fetch.year
analytics_url = (f"https://api.linkedin.com/v2/adAnalyticsV2?q=analytics&dateRange.start.day={start_day}&"
f"dateRange.start.month={start_month}&dateRange.start.year={start_year}&dateRange.end.day={start_day}&"
f"dateRange.end.month={start_month}&dateRange.end.year={start_year}&"
"timeGranularity=DAILY&pivot=CAMPAIGN&accounts[0]=urn:li:sponsoredAccount:506383483&"
"fields=clicks,costInLocalCurrency,impressions,pivotValues")
response = requests.get(analytics_url, headers=headers)
if response.status_code == 200:
data = response.json().get('elements', [])
for campaign in data:
clicks = campaign.get('clicks', 'N/A')
impressions = campaign.get('impressions', 'N/A')
cost = f"{float(campaign.get('costInLocalCurrency', 'N/A')):.2f}" if campaign.get('costInLocalCurrency', 'N/A') != 'N/A' else 'N/A'
campaign_id = campaign.get('pivotValues', ['N/A'])[0].split(':')[-1]
campaign_name = campaign_id_to_name.get(campaign_id, "Unknown Campaign")
all_data.append({
'Date': day_to_fetch.strftime('%Y-%m-%d'),
'Campaign': campaign_name,
'Clicks': clicks,
'Impressions': impressions,
'Cost': cost
})
else:
print(f"Failed to fetch analytics for {day_to_fetch.strftime('%Y-%m-%d')}. Status Code: {response.status_code}, Response: {response.text}")
# Step 4: Write Data to Google Sheets
headers = ['Date', 'Campaign', 'Clicks', 'Impressions', 'Cost']
sheet.append_row(headers)
for entry in all_data:
row = [entry['Date'], entry['Campaign'], entry['Clicks'], entry['Impressions'], entry['Cost']]
sheet.append_row(row)
print("Data successfully dumped into Google Sheets!")
</code>When you run the script, it will:
Once set up, you can schedule this script using cron or any other scheduling tool to run daily, weekly, or however frequently you need it.
Automating your LinkedIn Ads data export is a game changer, especially if you’re juggling multiple campaigns. It saves you time and lets you focus on analyzing performance instead of being bogged down by manual data handling. With this Python script, your LinkedIn data goes straight to Google Sheets, making your reporting process smoother than ever.
You’re welcome. 😉