Historical Experience

The Data Dictionary has the most up-to-date field definitions for all RevenueBase tables. Use it as your primary reference for column names and descriptions.

The historical_experience table tracks career history for contacts in the RevenueBase database. Each row represents one LinkedIn profile with a nested array structure: a jobs array containing job objects, each with an experience array of position records. Use it to understand career trajectories, identify recent job changers, find alumni of specific organizations, and build more personalized outreach.

Where the data comes from

Historical experience is derived from professional profiles and public career history. Positions are normalized to titles, seniority, and department where possible. Start and end dates are captured with the precision available from the source (sometimes year-only for older roles). Tenure is calculated from those dates.

Counts and coverage

Each row represents one LinkedIn profile with nested arrays: a jobs array containing job objects, each with an experience array of position records. Not every contact has historical experience; fill rates are higher for recent roles and for people with detailed public profiles. Use the fill-rate query below to see coverage and average positions per profile.

Table stats

Metric	Value
Total records	One row per LinkedIn profile (with array of jobs)
Update frequency	Monthly (aligned with contacts release)
Primary key	`linkedin_url`
Main foreign keys	Join to `PER_LATEST` table on `linkedin_url`, join to `ORG_LATEST` table via `rbid_org`

Data dictionary

For the complete and most current field reference, see the Data Dictionary. Fill rates for historical data are typically lower than for current-position data. Older positions, smaller organizations, and non-US contacts tend to have less complete records. The table structure uses one row per LinkedIn profile with an array of job positions.

Top-level fields

Field	Type	Description
`unique_id`	VARCHAR	Unique identifier for this record
`linkedin_url`	VARCHAR	LinkedIn profile URL (primary key)
`job_count`	INTEGER	Maximum number of jobs for this profile
`jobs`	ARRAY	Array of job objects containing experience data

Jobs array structure

Each element in the jobs array is an object containing:

Field	Type	Description
`rbid`	VARCHAR	RevenueBase person identifier (from `PER_LATEST` table)
`rbid_pao`	VARCHAR	RevenueBase person-at-organization identifier
`rbid_org`	VARCHAR	RevenueBase organization identifier
`linkedin_id`	INTEGER	LinkedIn organization page ID (from `ORG_LATEST` table)
`email_address`	VARCHAR	Email address (from `PER_LATEST` table)
`experience`	ARRAY	Array of experience objects containing position details

Experience array structure

Each element in the experience array within a job object contains:

Field	Type	Description
`job_title`	VARCHAR	Job title for this position
`job_org_name`	VARCHAR	Organization name for this position
`job_org_linkedin_url`	VARCHAR	LinkedIn organization page URL
`job_function`	VARCHAR	Department or functional area
`job_is_current`	BOOLEAN	Whether this is the person’s current position

One row per LinkedIn profile with nested arrays. Each row contains a jobs array. Each job object contains an experience array with one or more position records. A person may have multiple positions at the same organization (different roles over time), which is why experience is an array. Use nested array functions to query and filter individual positions.

The experience array structure may include additional fields like start_date, end_date, and other position-related attributes depending on data availability. Query the nested arrays to access individual job positions.

Joining to other tables

The jobs array contains rbid, rbid_org, and email_address that you can use to join to other tables. The experience array within each job contains position-specific details:

Approach	When to use
Join to person table	Use `jobs[].rbid` or join on `linkedin_url` to get current contact information
Join to organization table	Use `jobs[].rbid_org` to get organization firmographics and HQ address for that employer
Access position details	Flatten both `jobs` and `experience` arrays to access individual position fields like `job_title`, `job_org_name`, `job_function`, `job_is_current`
Contact’s current location	For “where does this person live now,” use contacts (`CITY`, `STATE_NAME`, `COUNTRY_NAME`), not historical experience

To analyze “contacts who worked in California” or “alumni of organizations in EMEA,” flatten both the jobs and experience arrays, join to org_latest on rbid_org, and filter on organization location fields.

Fill rates for historical data are inherently lower than for current-position data. Older positions, smaller organizations, and non-US contacts tend to have less complete records. The experience object may not contain all fields for every position, and rbid_org may be null when the employer is not in the RevenueBase organizations table.

Common use cases

Find recent job changers

People who just started a new role are often more receptive to new tools. Flatten both the jobs and experience arrays and filter for positions where experience.job_is_current = TRUE. Join to the person table and filter by UPDATED_AT to find recently updated profiles.

Organization alumni targeting

Find everyone who used to work at a specific organization by flattening both arrays and filtering for experience.job_org_linkedin_url matching the target organization, or use jobs[].rbid_org to join to the organization table. Filter for experience.job_is_current = FALSE. Useful for selling to former champions who already know your space.

Career trajectory analysis

Flatten both arrays and compare job_title, job_function, and job_org_name across positions for a profile. Use job_count to identify profiles with multiple positions, then analyze the progression in the nested arrays.

Multiple positions at same organization

Some profiles may have multiple entries in the experience array within the same job object, representing different roles at the same organization over time. Flatten both arrays to see all positions and their progression.

Joining this table

Join HISTORICAL_EXPERIENCE_LATEST to PER_LATEST table on linkedin_url to get current contact info. Flatten both the jobs and experience arrays to access individual positions. Join to org_latest table using jobs[].rbid_org when you need firmographics or HQ address for a specific role.

Flattening the nested arrays

To work with individual positions, you need to flatten both the jobs array and the experience array within each job. Here’s a pattern for Snowflake:

SELECT
    he.linkedin_url,
    he.unique_id,
    job.value:rbid AS rbid,
    job.value:rbid_org AS rbid_org,
    job.value:rbid_pao AS rbid_pao,
    job.value:email_address AS email_address,
    job.value:linkedin_id AS linkedin_id,
    exp.value:job_title AS job_title,
    exp.value:job_org_name AS job_org_name,
    exp.value:job_org_linkedin_url AS job_org_linkedin_url,
    exp.value:job_function AS job_function,
    exp.value:job_is_current AS job_is_current
FROM RELEASE.HISTORICAL_EXPERIENCE_LATEST he,
LATERAL FLATTEN(input => he.jobs) job,
LATERAL FLATTEN(input => job.value:experience) exp
WHERE exp.value IS NOT NULL;

Historical experience + contacts (recent job changers with contact info)

SELECT
    per.FIRST_NAME,
    per.LAST_NAME,
    per.EMAIL_ADDRESS,
    exp.value:job_title AS new_title,
    exp.value:job_org_name AS new_company,
    per.JOB_TITLE AS current_title_in_feed
FROM RELEASE.HISTORICAL_EXPERIENCE_LATEST he
JOIN RELEASE.PER_LATEST per ON he.linkedin_url = per.LINKEDIN_URL
, LATERAL FLATTEN(input => he.jobs) job
, LATERAL FLATTEN(input => job.value:experience) exp
WHERE exp.value:job_is_current = TRUE
  AND per.EMAIL_ADDRESS IS NOT NULL
ORDER BY per.UPDATED_AT DESC;

Historical experience + contacts + organizations (alumni with HQ location)

SELECT
    per.FIRST_NAME,
    per.LAST_NAME,
    per.EMAIL_ADDRESS,
    exp.value:job_org_name AS former_employer,
    exp.value:job_title AS former_title,
    org.HEADQUARTERS_COUNTRY_NAME_ORG,
    org.HEADQUARTERS_CITY_ORG
FROM RELEASE.HISTORICAL_EXPERIENCE_LATEST he
JOIN RELEASE.PER_LATEST per ON he.linkedin_url = per.LINKEDIN_URL
, LATERAL FLATTEN(input => he.jobs) job
, LATERAL FLATTEN(input => job.value:experience) exp
LEFT JOIN RELEASE.ORG_LATEST org ON job.value:rbid_org = org.RBID
WHERE exp.value:job_org_linkedin_url LIKE '%salesforce%'
  AND exp.value:job_is_current = FALSE
  AND per.EMAIL_ADDRESS IS NOT NULL
ORDER BY per.UPDATED_AT DESC;

How to calculate fill rates

Use this to see how many profiles you have, how many positions total, and how complete key fields are.

SELECT
    COUNT(*) AS total_profiles,
    SUM(job_count) AS total_positions,
    ROUND(AVG(job_count), 1) AS avg_positions_per_profile,
    COUNT(DISTINCT linkedin_url) AS unique_profiles
FROM RELEASE.HISTORICAL_EXPERIENCE_LATEST;

-- To analyze individual positions, flatten both arrays:
SELECT
    COUNT(*) AS total_positions,
    ROUND(COUNT(job.value:rbid_org) * 100.0 / COUNT(*), 1) AS rbid_org_fill_pct,
    ROUND(COUNT(exp.value:job_title) * 100.0 / COUNT(*), 1) AS job_title_fill_pct,
    ROUND(COUNT(exp.value:job_org_name) * 100.0 / COUNT(*), 1) AS job_org_name_fill_pct,
    ROUND(COUNT(exp.value:job_function) * 100.0 / COUNT(*), 1) AS job_function_fill_pct
FROM RELEASE.HISTORICAL_EXPERIENCE_LATEST he,
LATERAL FLATTEN(input => he.jobs) job,
LATERAL FLATTEN(input => job.value:experience) exp
WHERE exp.value IS NOT NULL;

Sample queries

Recent job changers (last 90 days)

What you’re finding: People who started a new job in the last 90 days, for outreach or lead scoring. Why these fields: We need current contact info from the person table and the new role from the flattened jobs array. Filter for current positions with recent start dates. Logic: Flatten both the jobs and experience arrays, filter to current positions (job_is_current = TRUE). Join to person table for contact info. Use UPDATED_AT to identify recently updated profiles.

SELECT
    per.FIRST_NAME,
    per.LAST_NAME,
    per.EMAIL_ADDRESS,
    exp.value:job_title AS new_title,
    exp.value:job_org_name AS new_company,
    per.UPDATED_AT AS profile_updated_at
FROM RELEASE.HISTORICAL_EXPERIENCE_LATEST he
JOIN RELEASE.PER_LATEST per ON he.linkedin_url = per.LINKEDIN_URL
, LATERAL FLATTEN(input => he.jobs) job
, LATERAL FLATTEN(input => job.value:experience) exp
WHERE exp.value:job_is_current = TRUE
  AND per.UPDATED_AT >= DATEADD(day, -90, CURRENT_DATE())
  AND per.EMAIL_ADDRESS IS NOT NULL
ORDER BY per.UPDATED_AT DESC;

Alumni of a specific organization

What you’re finding: People who used to work at a given organization (e.g. Salesforce), with current contact info, for alumni campaigns or competitive targeting. Why these fields: Filter the jobs array for the target organization’s domain and non-current positions. Join to person table for current contact info. Logic: Flatten both arrays, filter by target organization LinkedIn URL pattern and non-current positions. Join to person table for email and current context. Order by profile update date descending.

SELECT
    per.FIRST_NAME,
    per.LAST_NAME,
    per.EMAIL_ADDRESS,
    per.JOB_TITLE AS current_title,
    per.COMPANY_NAME AS current_company,
    exp.value:job_title AS former_title,
    exp.value:job_org_name AS former_employer,
    exp.value:job_org_linkedin_url AS former_company_linkedin
FROM RELEASE.HISTORICAL_EXPERIENCE_LATEST he
JOIN RELEASE.PER_LATEST per ON he.linkedin_url = per.LINKEDIN_URL
, LATERAL FLATTEN(input => he.jobs) job
, LATERAL FLATTEN(input => job.value:experience) exp
WHERE exp.value:job_org_linkedin_url LIKE '%salesforce%'
  AND exp.value:job_is_current = FALSE
  AND per.EMAIL_ADDRESS IS NOT NULL
ORDER BY per.UPDATED_AT DESC;

Profiles with multiple positions

What you’re finding: Profiles that have held multiple positions, useful for career trajectory analysis. Why these fields: Use job_count to identify profiles with multiple positions, then flatten to see all positions. Logic: Filter for profiles with job_count > 1, then flatten both arrays to see all positions.

SELECT
    he.linkedin_url,
    he.job_count,
    per.FIRST_NAME,
    per.LAST_NAME,
    exp.value:job_title AS job_title,
    exp.value:job_org_name AS company_name,
    exp.value:job_function AS job_function,
    exp.value:job_is_current AS is_current
FROM RELEASE.HISTORICAL_EXPERIENCE_LATEST he
JOIN RELEASE.PER_LATEST per ON he.linkedin_url = per.LINKEDIN_URL
, LATERAL FLATTEN(input => he.jobs) job
, LATERAL FLATTEN(input => job.value:experience) exp
WHERE he.job_count > 1
  AND exp.value IS NOT NULL
ORDER BY he.linkedin_url, exp.value:job_is_current DESC;

Getting Started

Base Tables

Pre-Joined Tables

Joining Tables

Historical Experience

Where the data comes from

Counts and coverage

Table stats

Data dictionary

Top-level fields

Jobs array structure

Experience array structure

Joining to other tables

Common use cases

Joining this table

Flattening the nested arrays

Historical experience + contacts (recent job changers with contact info)

Historical experience + contacts + organizations (alumni with HQ location)

How to calculate fill rates

Sample queries

Recent job changers (last 90 days)

Alumni of a specific organization

Profiles with multiple positions

​Where the data comes from

​Counts and coverage

​Table stats

​Data dictionary

​Top-level fields

​Jobs array structure

​Experience array structure

​Joining to other tables

​Common use cases

​Joining this table

​Flattening the nested arrays

​Historical experience + contacts (recent job changers with contact info)

​Historical experience + contacts + organizations (alumni with HQ location)

​How to calculate fill rates

​Sample queries

​Recent job changers (last 90 days)

​Alumni of a specific organization

​Related

​Profiles with multiple positions

Where the data comes from

Counts and coverage

Table stats

Data dictionary

Top-level fields

Jobs array structure

Experience array structure

Joining to other tables

Common use cases

Joining this table

Flattening the nested arrays

Historical experience + contacts (recent job changers with contact info)

Historical experience + contacts + organizations (alumni with HQ location)

How to calculate fill rates

Sample queries

Recent job changers (last 90 days)

Alumni of a specific organization

Related

Profiles with multiple positions