Skip to main content
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 companies, 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

MetricValue
Total recordsOne row per LinkedIn profile (with array of jobs)
Update frequencyMonthly (aligned with contacts release)
Primary keylinkedin_url
Main foreign keysJoin to person table on linkedin_url, join to organization table via rbid_org

​
Data dictionary

Fill rates for historical data are typically lower than for current-position data. Older positions, smaller companies, 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

FieldTypeDescription
unique_idVARCHARUnique identifier for this record
linkedin_urlVARCHARLinkedIn profile URL (primary key)
job_countINTEGERMaximum number of jobs for this profile
jobsARRAYArray of job objects containing experience data

​
Jobs array structure

Each element in the jobs array is an object containing:
FieldTypeDescription
rbidVARCHARRevenueBase person identifier (from person table)
rbid_paoVARCHARRevenueBase person-at-organization identifier
rbid_orgVARCHARRevenueBase organization identifier
linkedin_idINTEGERLinkedIn company page ID (from organization table)
email_addressVARCHAREmail address (from person table)
experienceARRAYArray of experience objects containing position details

​
Experience array structure

Each element in the experience array within a job object contains:
FieldTypeDescription
job_titleVARCHARJob title for this position
job_org_nameVARCHARCompany name for this position
job_org_linkedin_urlVARCHARLinkedIn company page URL
job_functionVARCHARDepartment or functional area
job_is_currentBOOLEANWhether 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 company (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:
ApproachWhen to use
Join to person tableUse jobs[].rbid or join on linkedin_url to get current contact information
Join to organization tableUse jobs[].rbid_org to get company firmographics and HQ address for that employer
Access position detailsFlatten both jobs and experience arrays to access individual position fields like job_title, job_org_name, job_function, job_is_current
Contact’s current locationFor “where does this person live now,” use contacts (CITY_PER, STATE_NAME_PER, COUNTRY_NAME_PER), not historical experience
To analyze “contacts who worked in California” or “alumni of companies in EMEA,” flatten both the jobs and experience arrays, join to ORG_LATEST on rbid_org, and filter on company location fields.
Fill rates for historical data are inherently lower than for current-position data. Older positions, smaller companies, 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 companies table.

​
Common use cases

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_PER to find recently updated profiles.
Find everyone who used to work at a specific company by flattening both arrays and filtering for experience.job_org_linkedin_url matching the target company, 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.
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.
Some profiles may have multiple entries in the experience array within the same job object, representing different roles at the same company over time. Flatten both arrays to see all positions and their progression.

​
Joining this table

Join historical_experience 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 RELEASES.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,
    per.LAST_NAME_PER,
    per.EMAIL_ADDRESS_PER,
    exp.value:job_title AS new_title,
    exp.value:job_org_name AS new_company,
    per.JOB_TITLE_PER AS current_title_in_feed
FROM RELEASES.RELEASE.HISTORICAL_EXPERIENCE_LATEST he
JOIN RELEASES.RELEASE.PER_LATEST per ON he.linkedin_url = per.LINKEDIN_URL_PER
, LATERAL FLATTEN(input => he.jobs) job
, LATERAL FLATTEN(input => job.value:experience) exp
WHERE exp.value:job_is_current = TRUE
  AND per.EMAIL_ADDRESS_PER IS NOT NULL
ORDER BY per.UPDATED_AT_PER DESC;

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

SELECT
    per.FIRST_NAME_PER,
    per.LAST_NAME_PER,
    per.EMAIL_ADDRESS_PER,
    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 RELEASES.RELEASE.HISTORICAL_EXPERIENCE_LATEST he
JOIN RELEASES.RELEASE.PER_LATEST per ON he.linkedin_url = per.LINKEDIN_URL_PER
, LATERAL FLATTEN(input => he.jobs) job
, LATERAL FLATTEN(input => job.value:experience) exp
LEFT JOIN RELEASES.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_PER IS NOT NULL
ORDER BY per.UPDATED_AT_PER 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 RELEASES.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 RELEASES.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_PER to identify recently updated profiles. 
SELECT
    per.FIRST_NAME_PER,
    per.LAST_NAME_PER,
    per.EMAIL_ADDRESS_PER,
    exp.value:job_title AS new_title,
    exp.value:job_org_name AS new_company,
    per.UPDATED_AT_PER AS profile_updated_at
FROM RELEASES.RELEASE.HISTORICAL_EXPERIENCE_LATEST he
JOIN RELEASES.RELEASE.PER_LATEST per ON he.linkedin_url = per.LINKEDIN_URL_PER
, LATERAL FLATTEN(input => he.jobs) job
, LATERAL FLATTEN(input => job.value:experience) exp
WHERE exp.value:job_is_current = TRUE
  AND per.UPDATED_AT_PER >= DATEADD(day, -90, CURRENT_DATE())
  AND per.EMAIL_ADDRESS_PER IS NOT NULL
ORDER BY per.UPDATED_AT_PER DESC;

​
Alumni of a specific company

What you’re finding: People who used to work at a given company (e.g. Salesforce), with current contact info, for alumni campaigns or competitive targeting. Why these fields: Filter the jobs array for the target company domain and non-current positions. Join to person table for current contact info. Logic: Flatten both arrays, filter by target company 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,
    per.LAST_NAME_PER,
    per.EMAIL_ADDRESS_PER,
    per.JOB_TITLE_PER AS current_title,
    per.COMPANY_NAME_ORG 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 RELEASES.RELEASE.HISTORICAL_EXPERIENCE_LATEST he
JOIN RELEASES.RELEASE.PER_LATEST per ON he.linkedin_url = per.LINKEDIN_URL_PER
, 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_PER IS NOT NULL
ORDER BY per.UPDATED_AT_PER 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,
    per.LAST_NAME_PER,
    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 RELEASES.RELEASE.HISTORICAL_EXPERIENCE_LATEST he
JOIN RELEASES.RELEASE.PER_LATEST per ON he.linkedin_url = per.LINKEDIN_URL_PER
, 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;