​

Configuring LinkedIn as a Source

​

1. Add account access

• Extract data from organization account: (Default: disabled) When enabled, extracts data from a LinkedIn organization page (posts, followers, page statistics). When disabled, extracts analytics data from the authenticated member’s personal profile.
• Organization ID: (Conditional) The LinkedIn organization ID you want to extract data from. This is required only if you enabled the option to extract data from an organization account. This is the numeric ID that identifies your company page.
• Start Date: The earliest date from which records will be synced. Note that LinkedIn only returns up to 1 year of historical data for daily reports and per-post statistics.
• Lookback Window: (Default: 30 days) Number of days to look back on each incremental run for the posts stream. This is applicable if extracting organization data. It ensures post statistics stay up to date for recently published posts. The lookback ensures that posts modified in the last N days have their latest metrics captured, while also preventing data loss during gaps between runs.

​

2. Select streams

• Organization Details - Company profile information

• Daily Follower Statistics - Daily follower gains
• Daily Page Statistics - Daily page views and visitor analytics
• Daily Share Statistics - Daily aggregate engagement metrics

• Lifetime Follower Statistics - Total follower count
• Follower Demographics - Lifetime follower demographic breakdown
• Lifetime Page Statistics - Lifetime page views and analytics
• Lifetime Share Statistics - Lifetime engagement metrics

• Posts - All posts from your page
• Post Statistics - Individual post performance
• Video Analytics - Lifetime video metrics per organic video post

• Daily Member Follower Statistics - Daily follower count for the member
• Lifetime Member Follower Count - Total follower count for the member
• Daily Member Post Analytics - Daily post engagement metrics for the member
• Total Member Post Analytics - Lifetime post engagement metrics for the member

Tip: The stream can be found more easily by typing its name.

​

3. Configure data streams

• Layer: Choose between the existing layers on your catalog. This is where you will find your new extracted tables as the extraction runs successfully.
• Folder: A folder can be created inside the selected layer to group all tables being created from this new data source.
• Table name: We suggest a name, but feel free to customize it. You have the option to add a prefix to all tables at once and make this process faster!
• Sync Type: You can choose between INCREMENTAL and FULL_TABLE.
  • Incremental: Every time the extraction happens, we’ll get only the new data - which is good if, for example, you want to keep every record ever fetched.
  • Full table: Every time the extraction happens, we’ll get the current state of the data - which is good if, for example, you don’t want to have deleted data in your catalog.

​

4. Configure data source

• Configure Delta Log Retention and determine for how long we should store old states of this table as it gets updated. Read more about this resource here.
• Determine when to execute an Additional Full Sync. This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while.

​

5. Check your new source

​

Streams and Fields

​

Data Model

​

Use Cases for Data Analysis

​

Content Performance Analysis

​

1. Post Engagement Overview

• Identify which content types drive the most engagement
• Understand optimal posting patterns
• Inform content strategy with data-driven insights

WITH post_metrics AS (
    SELECT
        p.id AS post_id,
        p.commentary AS post_text,
        FROM_UNIXTIME(p."publishedAt" / 1000) AS published_date,
        p."lifecycleState" AS status,
        CASE
            WHEN p.content.media IS NOT NULL THEN 'MEDIA'
            WHEN p.content.article IS NOT NULL THEN 'ARTICLE'
            WHEN p.content.poll IS NOT NULL THEN 'POLL'
            WHEN p.content."multiImage" IS NOT NULL THEN 'MULTI_IMAGE'
            WHEN p.content.carousel IS NOT NULL THEN 'CAROUSEL'
            ELSE 'TEXT_ONLY'
        END AS content_type,
        ps."totalShareStatistics"."impressionCount" AS impressions,
        ps."totalShareStatistics"."uniqueImpressionsCount" AS unique_impressions,
        ps."totalShareStatistics"."clickCount" AS clicks,
        ps."totalShareStatistics"."likeCount" AS likes,
        ps."totalShareStatistics"."commentCount" AS comments,
        ps."totalShareStatistics"."shareCount" AS shares,
        ps."totalShareStatistics".engagement AS engagement_rate
    FROM
        nekt_raw.linkedin_posts p
        LEFT JOIN nekt_raw.linkedin_post_statistics ps 
            ON p.id = ps."postId"
    WHERE
        p."lifecycleState" = 'PUBLISHED'
)
SELECT
    post_id,
    SUBSTRING(post_text, 1, 100) AS post_preview,
    published_date,
    content_type,
    impressions,
    unique_impressions,
    clicks,
    likes,
    comments,
    shares,
    ROUND(engagement_rate * 100, 2) AS engagement_rate_pct,
    (likes + comments + shares) AS total_engagements
FROM
    post_metrics
ORDER BY
    total_engagements DESC
LIMIT 20

WITH post_metrics AS (
    SELECT
        p.id AS post_id,
        p.commentary AS post_text,
        TIMESTAMP_MILLIS(CAST(p.publishedAt AS INT64)) AS published_date,
        p.lifecycleState AS status,
        CASE
            WHEN p.content.media IS NOT NULL THEN 'MEDIA'
            WHEN p.content.article IS NOT NULL THEN 'ARTICLE'
            WHEN p.content.poll IS NOT NULL THEN 'POLL'
            WHEN p.content.multiImage IS NOT NULL THEN 'MULTI_IMAGE'
            WHEN p.content.carousel IS NOT NULL THEN 'CAROUSEL'
            ELSE 'TEXT_ONLY'
        END AS content_type,
        ps.totalShareStatistics.impressionCount AS impressions,
        ps.totalShareStatistics.uniqueImpressionsCount AS unique_impressions,
        ps.totalShareStatistics.clickCount AS clicks,
        ps.totalShareStatistics.likeCount AS likes,
        ps.totalShareStatistics.commentCount AS comments,
        ps.totalShareStatistics.shareCount AS shares,
        ps.totalShareStatistics.engagement AS engagement_rate
    FROM
        `nekt_raw.linkedin_posts` p
        LEFT JOIN `nekt_raw.linkedin_post_statistics` ps 
            ON p.id = ps.postId
    WHERE
        p.lifecycleState = 'PUBLISHED'
)
SELECT
    post_id,
    SUBSTR(post_text, 1, 100) AS post_preview,
    published_date,
    content_type,
    impressions,
    unique_impressions,
    clicks,
    likes,
    comments,
    shares,
    ROUND(engagement_rate * 100, 2) AS engagement_rate_pct,
    (likes + comments + shares) AS total_engagements
FROM
    post_metrics
ORDER BY
    total_engagements DESC
LIMIT 20

​

2. Follower Growth Analysis

• Monitor organic vs paid follower acquisition
• Track growth trends over time
• Measure the impact of content and campaigns on follower growth

WITH daily_followers AS (
    SELECT
        DATE(date) AS report_date,
        "followerGains"."organicFollowerGain" AS organic_gained,
        "followerGains"."paidFollowerGain" AS paid_gained,
        ("followerGains"."organicFollowerGain" + "followerGains"."paidFollowerGain") AS total_gained
    FROM
        nekt_raw.linkedin_daily_follower_statistics
    WHERE
        date >= CURRENT_DATE - INTERVAL '30' DAY
),
running_totals AS (
    SELECT
        report_date,
        organic_gained,
        paid_gained,
        total_gained,
        SUM(total_gained) OVER (ORDER BY report_date) AS cumulative_growth,
        AVG(total_gained) OVER (
            ORDER BY report_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS seven_day_avg
    FROM
        daily_followers
)
SELECT
    report_date,
    organic_gained,
    paid_gained,
    total_gained,
    cumulative_growth,
    ROUND(seven_day_avg, 1) AS seven_day_moving_avg,
    ROUND(organic_gained * 100.0 / NULLIF(total_gained, 0), 1) AS organic_pct
FROM
    running_totals
ORDER BY
    report_date DESC

WITH daily_followers AS (
    SELECT
        DATE(date) AS report_date,
        followerGains.organicFollowerGain AS organic_gained,
        followerGains.paidFollowerGain AS paid_gained,
        (followerGains.organicFollowerGain + followerGains.paidFollowerGain) AS total_gained
    FROM
        `nekt_raw.linkedin_daily_follower_statistics`
    WHERE
        date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
),
running_totals AS (
    SELECT
        report_date,
        organic_gained,
        paid_gained,
        total_gained,
        SUM(total_gained) OVER (ORDER BY report_date) AS cumulative_growth,
        AVG(total_gained) OVER (
            ORDER BY report_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS seven_day_avg
    FROM
        daily_followers
)
SELECT
    report_date,
    organic_gained,
    paid_gained,
    total_gained,
    cumulative_growth,
    ROUND(seven_day_avg, 1) AS seven_day_moving_avg,
    ROUND(SAFE_DIVIDE(organic_gained * 100.0, total_gained), 1) AS organic_pct
FROM
    running_totals
ORDER BY
    report_date DESC

​

3. Page Visitor Demographics

• Understand your audience composition
• Identify high-value visitor segments
• Inform content targeting decisions

WITH seniority_stats AS (
    SELECT
        s.seniority,
        SUM(s."pageStatistics".views."allPageViews"."pageViews") AS total_views,
        SUM(s."pageStatistics".views."allPageViews"."uniquePageViews") AS unique_views
    FROM
        nekt_raw.linkedin_daily_page_statistics ps
        CROSS JOIN UNNEST(ps."pageStatisticsBySeniority") AS t(s)
    WHERE
        DATE(ps.date) >= CURRENT_DATE - INTERVAL '30' DAY
    GROUP BY
        s.seniority
),
total AS (
    SELECT SUM(total_views) AS grand_total FROM seniority_stats
)
SELECT
    seniority,
    total_views,
    unique_views,
    ROUND(total_views * 100.0 / grand_total, 1) AS pct_of_total
FROM
    seniority_stats, total
ORDER BY
    total_views DESC

WITH seniority_stats AS (
    SELECT
        s.seniority,
        SUM(s.pageStatistics.views.allPageViews.pageViews) AS total_views,
        SUM(s.pageStatistics.views.allPageViews.uniquePageViews) AS unique_views
    FROM
        `nekt_raw.linkedin_daily_page_statistics` ps,
        UNNEST(ps.pageStatisticsBySeniority) AS s
    WHERE
        DATE(ps.date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    GROUP BY
        s.seniority
),
total AS (
    SELECT SUM(total_views) AS grand_total FROM seniority_stats
)
SELECT
    seniority,
    total_views,
    unique_views,
    ROUND(SAFE_DIVIDE(total_views * 100.0, grand_total), 1) AS pct_of_total
FROM
    seniority_stats, total
ORDER BY
    total_views DESC

​

Implementation Notes

engagement = (clicks + likes + comments + shares) / impressions

​

Skills for agents