LinkedIn Ads dbt Modeling

LinkedIn Ads data requires explicit handling in the intermediate layer before it can join a cross-platform reporting layer: the inverted campaign hierarchy needs renaming, click definitions need normalization, and the 90-day attribution window requires a lookback incremental strategy.

The Hierarchy Rename

LinkedIn’s campaign structure is inverted compared to Google Ads and Meta. LinkedIn’s “Campaign Group” maps to what other platforms call a “Campaign.” LinkedIn’s “Campaign” maps to an “Ad Group.” LinkedIn’s “Creative” maps to an “Ad.”

Any cross-platform model that UNION ALLs LinkedIn data alongside Google and Meta requires consistent terminology. Using LinkedIn’s native names in the mart layer produces incompatible results when queried alongside other platforms.

The rename belongs in the intermediate layer — applied once, before any downstream mart model or dashboard touches the data:

-- models/intermediate/int_linkedin_ads__campaigns.sql
SELECT
  campaign_group_id   AS campaign_id,
  campaign_group_name AS campaign_name,
  campaign_id         AS ad_group_id,
  campaign_name       AS ad_group_name,
  creative_id         AS ad_id
FROM {{ ref('base__linkedin_ads__ad_analytics') }}

Pick one convention and commit to it. The Google/Meta standard (Campaign → Ad Group → Ad) is the right choice because it matches what most people expect, it’s what the Fivetran dbt_ad_reporting package uses, and it’s what stakeholders who’ve seen Google Ads dashboards will be looking for.

Document the rename in your model’s dbt description. Stakeholders who came from a LinkedIn background will expect LinkedIn’s native terminology; they need to know the translation happened.

Metric Normalization

Five metrics compose the cross-platform unified layer: clicks, impressions, spend, conversions, and conversions_value. LinkedIn maps to these, but with specific translation decisions:

Spend: LinkedIn reports costInLocalCurrency. Rename to spend in the intermediate layer. If you’re combining multiple ad accounts in different currencies, add explicit currency standardization here.

Clicks: LinkedIn’s raw clicks metric includes social actions — likes, shares, comments, and follows. This inflates LinkedIn’s CTR compared to Google and Meta, which only count clicks that navigate the user somewhere. For cross-platform comparability, use externalWebsiteClicks or landingPageClicks instead of total clicks. If your extraction tool doesn’t provide these narrower fields, document the divergence explicitly in your model description so downstream consumers know LinkedIn CTR isn’t directly comparable.

Conversions: The dbt_ad_reporting package defaults to external_website_conversions + one_click_leads as the conversions metric for LinkedIn. This is a reasonable default for B2B pipelines where both website conversions and lead form completions count as pipeline. If your conversion definition differs — for example, if you only want to count one type — customize this through the package’s passthrough variable configuration rather than overriding the model directly.

Impressions: LinkedIn uses the IAB viewability standard (50% of pixels in-view for at least 1 second). Google counts any served ad. The numbers aren’t directly comparable, but there’s no normalization you can apply retroactively. Document it in the model description. See Ad Platform Metric Divergence for the full cross-platform divergence picture.

The dbt_ad_reporting Package

Fivetran’s dbt_ad_reporting package handles LinkedIn as one of its 11 platforms. If you’re using Fivetran for extraction, the package sits on top of Fivetran’s staging tables and produces a unified ad_reporting__ad_report model with the five standard metrics.

The package handles the hierarchy rename, the conversions calculation, and the UNION ALL across platforms. What it doesn’t handle is LinkedIn-specific models for demographic data, social metrics, or viral metrics — those belong in platform-specific mart models that you build alongside the unified layer.

If you’re using a different extraction tool (Airbyte, dlt, or a custom pipeline), you can still adopt the same modeling pattern the package uses. The structural conventions — base, intermediate (with the hierarchy rename and metric normalization), mart (unified and platform-specific) — are worth following even if you’re not using the package’s generated code.

Incremental Strategy for 90-Day Attribution

LinkedIn’s 90-day attribution window means conversion data for a given day continues to update for three months. A conversion attributed to a January 10 click might not appear until March. Your incremental model needs to re-process at least the last 90 days on every run to capture these late-arriving conversions.

The right incremental strategy for LinkedIn conversion data is insert_overwrite with date partitioning. This replaces entire date partitions atomically on each run — no merge complexity, no duplicate risk, just a clean replacement of the days in scope:

{{ config(
  materialized='incremental',
  partition_by={"field": "date_day", "data_type": "date"},
  incremental_strategy='insert_overwrite'
) }}

{% set lookback_days = var('linkedin_lookback_days', 90) %}

SELECT
  date_day,
  campaign_group_id AS campaign_id,
  campaign_id       AS ad_group_id,
  creative_id       AS ad_id,
  impressions,
  external_website_clicks AS clicks,
  cost_in_local_currency  AS spend,
  external_website_conversions + one_click_leads AS conversions
FROM {{ ref('base__linkedin_ads__ad_analytics') }}

{% if is_incremental() %}
WHERE date_day >= DATE_SUB(CURRENT_DATE(), INTERVAL {{ lookback_days }} DAY)
{% endif %}

Make the 90-day lookback a dbt variable so you can adjust it without changing model code. Some teams use 60 days for daily runs and a quarterly full-refresh to catch anything that slipped through. The right value depends on how much your LinkedIn conversion data actually changes in the tail of the attribution window — pull a week of data from the LinkedIn UI and compare it to what your warehouse shows for the same period 30 and 60 days later to calibrate.

This pattern is a specific application of the Late-Arriving Data and the Lookback Window Pattern. The LinkedIn case is notable because the window is unusually long — three months is an aggressive lookback compared to Meta’s 7 days or the typical 3-day window for event data. Size your pipeline jobs accordingly.

Platform-Specific vs. Unified Models

LinkedIn’s professional demographic data — company, job title, seniority, function, industry, company size — has no equivalent in Google or Meta pipelines. It doesn’t belong in the unified cross-platform model, where it would sit as NULLs for every non-LinkedIn row.

Build separate LinkedIn-specific mart models for demographic analysis:

models/
  marts/
    advertising/
      unified/
        mart__ad_reporting.sql          # UNION ALL across platforms
      platform/
        linkedin/
          mart__linkedin_ads__demographics.sql  # seniority, company, job title
          mart__linkedin_ads__social.sql        # likes, shares, viral metrics
          mart__linkedin_ads__video.sql         # video engagement funnel

The unified model is for cross-platform spend and performance comparisons. The platform-specific models are for LinkedIn’s unique B2B value — which target accounts are you reaching, at which seniority levels, with what frequency? See LinkedIn Ads B2B Data Value for the analytical framing.

Without Fivetran

If you’re building custom models without Fivetran’s base tables, follow the same three-layer pattern. The intermediate layer carries the heaviest responsibility: hierarchy rename, metric normalization (clicks, spend, conversions definitions), LinkedIn-specific adjustments (click definition documentation, attribution window handling). Mart models should be thin on LinkedIn-specific logic — they consume clean, normalized intermediate data.

The extraction layer quirks (LinkedIn Ads Analytics Endpoint) — slicing requests to stay under 15,000 elements, handling query tunneling, splitting requests for more than 20 metrics — are extraction-layer problems, not transformation-layer problems. Solve them before data reaches dbt.