> ## Documentation Index
> Fetch the complete documentation index at: https://docs.revenuebase.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# Historical Experience
> Career history and job change data — past roles, tenure, and professional trajectory.
export const RBCardGroup = ({cols = 3, children}) =>
}
{tag && {tag}}
;
};
The [Data Dictionary](/docs/data-features/data-dictionary#per_latest) 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](/docs/data-features/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
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.
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.
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 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:
```sql theme={null}
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)
```sql theme={null}
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)
```sql theme={null}
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.
```sql theme={null}
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.
```sql theme={null}
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.
```sql theme={null}
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;
```
## Related
### 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.
```sql theme={null}
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;
```