How Do You Get LinkedIn Ads Data Into PostgreSQL?

Three routes, and who each one is for

• Build against the Marketing API. Full control over fields, granularity and schema. Right if you have a data engineer, want creative-level daily data, or need to join it to CRM in your own warehouse. This is most of the post.
• ETL connector. Airbyte has an open-source LinkedIn Ads source; Fivetran has a managed one. Both land in Postgres with the schema handled. Right if you want it running this week and can live with their field selection and refresh cadence.
• Kiin's pipeline. Our LinkedIn Ads MCP already syncs connected accounts into a Postgres store and applies the interpretation rules below — landing page clicks not clicks, submissions not form opens, currency normalised. Right if you want the data to arrive meaning something, not just sitting in a table. Everything in this post is what we learned building it.

Authentication and the three headers

Create an app in the LinkedIn Developer Portal, request Marketing Developer Platform access, and complete the OAuth 2.0 flow with the r_ads_reporting and r_ads scopes. You get an access token that expires in 60 days and, for the standard flow, a refresh token.

Every request needs three headers, and the second is where most first attempts fail:

Authorization: Bearer {access_token}
Linkedin-Version: 202603
X-Restli-Protocol-Version: 2.0.0

Linkedin-Version is a year-month string and old versions are sunset on a rolling basis — 202503 no longer works. Pin the version in config, not in code, and expect to bump it. Test a token against GET /rest/me before trusting it: a token can be unexpired and still revoked or wrong-scoped, and that failure is silent until your sync job starts returning empty pages.

The entity hierarchy is your table design

LinkedIn Ads is a strict parent–child tree, and every entity references its parent by URN:

Ad Account         urn:li:sponsoredAccount:{id}
└─ Campaign Group  urn:li:sponsoredCampaignGroup:{id}
   └─ Campaign     urn:li:sponsoredCampaign:{id}
      └─ Creative  urn:li:sponsoredCreative:{id}
         └─ Post   urn:li:ugcPost:{id}   (or urn:li:share, urn:li:video …)

Mirror it. Four dimension tables keyed on the numeric id extracted from the URN, and one fact table of daily stats keyed on (creative, date). Do not flatten campaign attributes into the stats table; campaigns change objective, bid and targeting mid-flight and you want the history.

A schema that survives the data

create table ad_accounts (
  id              bigint primary key,
  name            text,
  currency        char(3) not null,       -- billing currency; 14 in our store
  status          text,
  synced_at       timestamptz
);

create table campaign_groups (
  id              bigint primary key,
  account_id      bigint references ad_accounts,
  name            text, status text
);

create table campaigns (
  id              bigint primary key,
  account_id      bigint references ad_accounts,
  group_id        bigint references campaign_groups,
  name            text,
  type            text,                   -- SPONSORED_UPDATES, SPONSORED_INMAILS, TEXT_AD, DYNAMIC
  format          text,                   -- STANDARD_UPDATE, SINGLE_VIDEO, CAROUSEL, THOUGHT_LEADER …
  objective_type  text,                   -- ENGAGEMENT, BRAND_AWARENESS, LEAD_GENERATION …
  cost_type       text,                   -- CPC, CPM
  daily_budget    numeric(14,4),
  daily_budget_ccy char(3),
  targeting       jsonb,                  -- keep the raw targeting criteria; you will need it
  audience_size   integer,
  created_at      timestamptz, updated_at timestamptz
);

create table creatives (
  id              bigint primary key,
  campaign_id     bigint references campaigns,
  post_urn        text,                   -- content.reference
  author_urn      text,                   -- urn:li:person:… = thought leader ad
  status          text
);

create table creative_daily_stats (
  creative_id     bigint references creatives,
  date            date not null,
  impressions     bigint, clicks bigint,
  landing_page_clicks bigint,             -- the only traffic field
  cost_local      numeric(14,4),          -- costInLocalCurrency, cast from string
  cost_usd        numeric(14,4),          -- costInUsd, cast from string
  one_click_leads bigint,                 -- submissions
  one_click_lead_form_opens bigint,       -- opens; NOT leads
  opens bigint, sends bigint,             -- conversation / message ads
  likes bigint, comments bigint, shares bigint, reactions bigint, follows bigint,
  video_views bigint, video_completions bigint,
  external_website_conversions bigint,
  viral_impressions bigint, viral_clicks bigint,
  approximate_unique_impressions bigint, frequency numeric(8,3),
  average_dwell_time numeric(8,3),
  primary key (creative_id, date)
);

Two design notes. targeting as jsonb is not laziness — targeting criteria are nested facets (locations, seniorities, job functions, uploaded lists, retargeting segments) and you will query them in ways you cannot predict. And keep both cost columns: costInUsd is what lets you sum across a multi-currency portfolio, and it is not derivable later if you only stored local.

The adAnalytics call that actually works

One endpoint does the reporting. The parameters that matter:

GET https://api.linkedin.com/rest/adAnalytics
  ?q=analytics
  &pivot=CREATIVE
  &timeGranularity=DAILY
  &dateRange=(start:(year:2026,month:6,day:1),end:(year:2026,month:8,day:31))
  &accounts=List(urn%3Ali%3AsponsoredAccount%3A{id})
  &fields=impressions,clicks,landingPageClicks,costInLocalCurrency,costInUsd,
          oneClickLeads,oneClickLeadFormOpens,opens,sends,
          likes,comments,shares,reactions,follows,
          videoViews,videoCompletions,externalWebsiteConversions,
          viralImpressions,viralClicks,approximateUniqueImpressions,
          averageDwellTime,pivotValues,dateRange

Run it per account, per 92-day window, pivoted on CREATIVE with DAILY granularity. That is the grain everything else aggregates from. Use q=statistics if you need up to three pivots at once — creative by member country, say — but it is a separate data cost.

The six constraints that break naive pipelines

1. No pagination. The response is capped at 15,000 elements and there is no next-page token. Creative-level daily data on a busy account blows past that in a quarter. Tighten the date range until it fits; the cap is the reason the call above uses 92-day windows rather than a year.
2. 92-day maximum date range on the analytics endpoints. A year of history is five calls per account, not one. Schedule them.
3. fields= defaults to two metrics. Leave it off and you get impressions and clicks and nothing else. Every field you want must be named. The upside: naming fewer fields costs less against the rate limit, which is 45 million metric values per five-minute window.
4. Viral is baked into totals. impressions and clicks already include organic amplification. Paid-only is impressions − viralImpressions. Store both and derive; do not store the derived figure only.
5. Demographic pivots are delayed and noisy. MEMBER_* pivots lag 12–24 hours and LinkedIn adds noise to protect privacy. Never sum daily demographic rows — request timeGranularity=ALL for the period you care about, and treat the result as approximate.
6. Types. Every money value arrives as a string. Every timestamp is Unix epoch milliseconds. Cast on load, and cast cost to numeric, never float.

Tokens die quietly

Access tokens last 60 days. The standard OAuth flow gives you a refresh token; some scopes do not, and those tokens simply expire. The failure mode is not an error — it is a sync job that starts returning empty responses while your dashboard shows last month's numbers with today's date. Store expires_at, refresh at seven days, and run a daily health check that actually calls /rest/me per token. We learned this the expensive way.

Making the data mean something

Landing the rows is half the job. The other half is not misreading them, and the LinkedIn API is unusually easy to misread. These are the rules we apply on the way in:

• clicks is not traffic. It counts likes, comments, shares, profile views and CTA clicks together. landingPageClicks is traffic. CTR is landingPageClicks / impressions; CPC is cost / landingPageClicks. On thought leader ads the two click fields diverge by a factor of ten or more.
• Form opens are not leads. oneClickLeadFormOpens is the form being displayed. oneClickLeads is a submission. Cost per lead uses the second.
• Conversation ads are measured on opens, not either click field. Open rate is opens / sends.
• Detect thought leader ads structurally. campaigns.format = 'THOUGHT_LEADER', or the creative's post author URN is a urn:li:person. Never from the campaign name.
• Normalise currency before summing. Our store has 14 billing currencies. An account that looked like the top spender at 139,669 was NOK, and about $15,000.

Every one of those is a view in our Postgres, not a rule in a document. The full metric definitions are in what a good LinkedIn Ads CTR actually is.

What we run

Kiin Intelligence syncs 1,000+ connected accounts through exactly this pipeline into Supabase Postgres, and the MCP server sits on top so Claude or ChatGPT can query it directly. If you would rather ask the question than build the pipeline, that is the shorter route — and it is how the analysis in our thought leader ads research was run.

Frequently asked questions

Does the LinkedIn Ads API paginate analytics results?

No. The adAnalytics endpoint returns up to 15,000 elements in one response and does not page beyond that. If a request would exceed the cap, tighten the date range or pivot at a coarser level. Some endpoints also cap the date range at 92 days, so a year of daily creative-level data is several calls, not one.

Why does the LinkedIn API only return impressions and clicks?

Because you did not pass a fields parameter. With no fields specified the adAnalytics endpoint returns only impressions and clicks by default. Every other metric — landingPageClicks, costInLocalCurrency, oneClickLeads, opens, videoViews — must be requested explicitly by name.

Why are the LinkedIn Ads cost values strings?

All monetary values in the API come back as strings, not numbers, and timestamps as Unix epoch milliseconds. Cast cost to numeric and timestamps to timestamptz on load. Store both costInLocalCurrency and costInUsd; accounts bill in many currencies and a naive sum of local cost across accounts is meaningless.

Should I store clicks or landingPageClicks?

Both, and never confuse them. The clicks field counts every click type including likes, comments, shares and profile views; landingPageClicks is the only field that means traffic. For thought leader ads the gap between the two is usually large. The same applies to lead forms: oneClickLeadFormOpens are opens, oneClickLeads are submissions, and only the second is a lead.

Is there a faster way than building the pipeline yourself?

Two. ETL connectors such as Airbyte and Fivetran ship a LinkedIn Ads source that lands in Postgres with the schema handled, at the cost of a subscription and less control over fields. Or use Kiin's LinkedIn Ads MCP, which already syncs accounts into a Postgres store and applies the metric rules — so the data arrives interpreted, not just stored.