> ## Documentation Index
> Fetch the complete documentation index at: https://docs.experio.cloud/llms.txt
> Use this file to discover all available pages before exploring further.

# Workshop 6: Data Mapping

> Walk each structured export column by column and decide how it lands in the graph.

This workshop decides how each structured export (HR, CRM, project and finance systems) becomes nodes and relationships in the graph. For every column you agree which [entity type](/implementation/key-concepts#entity-type) and [attribute](/implementation/key-concepts#attribute) it fills, which column identifies the record, and which columns connect one record to another. Structured data is usually the most trusted source a firm has. If it is mapped well, it becomes the backbone that every document attaches to. If it is mapped badly, you get duplicate people, orphan projects and answers that miss half the facts.

The result is a [data mapping](/implementation/key-concepts#data-mapping) per export, plus an agreed load order.

## At a glance

| | |
| - | - |
| **Purpose** | Turn every structured export into a column-by-column mapping, with match keys, relationships, sync columns and a load order |
| **When** | Phase 2 (Model), weeks 2–4, after [Workshop 3: Ontology](/implementation/ws-ontology) and [Workshop 4: Taxonomies](/implementation/ws-taxonomies) |
| **Duration** | 60–90 minutes per source system. Run one session per system owner rather than one long session |
| **Attendees** | Experio: FFE (leads), Experio SMEs as needed. Client: PM, super user, the data owner for each system (the person who can explain every column), IT admin if an API is involved |
| **Inputs** | Draft ontology saved in Experio; a real export from each system (50–200 rows is enough) with the real header row; the data inventory from [Workshop 2](/implementation/ws-data-inventory) |
| **Outputs** | Signed-off mapping worksheet per export; load order diagram; list of export fixes the data owners will make; one saved data mapping per export in Experio |
| **Admin pages** | **Model & Define > Data Mapping**, **Connect > Connectors**, **Connect > Data Sources** |

## Before the workshop

**FFE and Experio SMEs**

* Check the ontology is saved and has every entity type the exports will fill. A mapping is tied to one ontology.
* Open each export and fill in a first draft of the worksheet below. The room corrects a draft much faster than it writes one from scratch.
* Flag every column whose values must line up with another file (client names, emails, project codes). These are the columns the workshop is really about.
* Try **Auto-Map with AI** on each header row (see [Accelerate with AI](#accelerate-with-ai)) and bring its suggestions as a starting point.

**Client homework (data owners)**

* Send a sample export with the exact header row the scheduled export will use. Column names that change later break the mapping.
* Say which column is the system's own unique ID, and which column records when a row was last changed.
* Say how the export will be delivered: a file dropped in a Box, Google Drive, SharePoint or Dropbox folder, a manual upload, or a REST API. Experio has **no direct database connector**. Database data arrives as an export file or through an API.

## Agenda

| Time | Item |
| - | - |
| 0:00 | Recap the ontology and the questions this system helps answer (5 min) |
| 0:05 | Walk the export column by column: entity type, attribute, type and format (30 min) |
| 0:35 | Decide the match key for each entity type in this file (10 min) |
| 0:45 | Decide the relationship columns and check both ends exist earlier in the load order (15 min) |
| 1:00 | Record ID and timestamp columns for incremental sync (5 min) |
| 1:05 | Format checks against other files and against documents (15 min) |
| 1:20 | Confirm export fixes, owners and dates (10 min) |

## Running the workshop

<Steps>
  <Step title="Walk every column">
    Put the header row and three real rows on screen. For each column ask:

    * "What is this, in your words?"
    * "Which thing in our model does it describe?" (entity type)
    * "Which property of that thing is it?" (attribute)
    * "Does anyone ever ask a question that needs this column?" If nobody does, leave it out. Unmapped columns are ignored.
  </Step>

  <Step title="Pick the match key for each entity type">
    Ask: "If this row arrives again tomorrow, which column tells us it is the same person, client or project?" That column becomes the [match key](/implementation/key-concepts#match-key). The match property defaults to `name`; choose something more stable when you have it (an email, a project code).

    <Warning>
      **Structured matching is exact only.** The match property is compared value for value. There is no fuzzy, vector, AI or human-review step for structured rows. "Lakeshore Health" and "Lakeshore Health, Inc." are two different clients to a data mapping. Leading and trailing spaces are trimmed; treat everything else (case, punctuation, suffixes) as significant.
    </Warning>
  </Step>

  <Step title="Find the relationship columns">
    Look for columns that point at another record: a client name on a project row, a manager's email on an employee row, a project code on an assignment row. Each becomes a relationship mapping: relationship type, start entity (column and match property), end entity (column and match property).

    Then ask: "Does the thing at each end already exist when this file loads?" **A relationship mapping never creates its endpoints.** If no node has that exact key value, the relationship is skipped. Node mappings, in this file or an earlier one, must create both ends first.
  </Step>

  <Step title="Agree type and format">
    Experio converts each mapped value to the attribute's type in the ontology (text, number, date, boolean, list, enum) when it writes to the graph. You do not set transforms per column, so the export has to arrive in a format that converts cleanly. Agree with the data owner:

    * **Dates**: ISO format (`2024-03-04`) or one agreed format. Ambiguous values such as `03/04/2024` need a decision.
    * **Numbers**: plain numbers (`1250000`). Test any value that has currency symbols or thousands separators before you rely on it.
    * **Lists**: one agreed delimiter or a JSON array for list attributes (for example, a list of certifications). Test with a few rows.
    * **Enums**: values must match the ontology's allowed values exactly (`won`, not `Closed Won`).
  </Step>

  <Step title="Set up incremental sync">
    Ask: "Which column uniquely identifies a row in your system?" and "Which column changes when a row is edited?"

    * **Record ID Column**: the system's own row ID. If you leave it empty, Experio generates a hash-based ID. A stable ID from the source system is more reliable.
    * **Timestamp Column**: a last-modified date, used to filter to changed rows on later syncs. Leave it empty to track by record ID only.
  </Step>

  <Step title="Check formats across files and against documents">
    For every key column ask: "Is this value written the same way in every other export, and in the documents?" Pull five client names from `projects.csv` and look them up in `accounts.csv`. Look up five project codes in status report titles. Anything that does not match exactly goes on the export-fixes list with an owner.
  </Step>

  <Step title="Nested JSON and API sources">
    For JSON files and REST APIs, fields inside objects are addressed with dot notation: `account.owner.email`. Ask IT which authentication the API uses (API key, bearer token, basic, or OAuth2) and how it pages results. They set these on the connection.
  </Step>
</Steps>

## Load order

Because relationships never create their ends, **the order in which files load matters**. Load master data first, then the files that connect it, then documents.

```mermaid theme={null}
flowchart LR
  E["employees.csv<br/>Employee, Practice<br/>REPORTS_TO, MEMBER_OF"] --> A["accounts.csv<br/>Client"]
  A --> P["projects.csv<br/>Project<br/>FOR_CLIENT, DELIVERED_BY"]
  P --> S["assignments.csv<br/>WORKS_ON_PROJECT"]
  S --> D["Documents<br/>proposals, SOWs, MSAs,<br/>resumes, status reports"]
```

Rows are processed one at a time, in file order. That matters for relationships inside a single file. If `employees.csv` lists a report before their manager, the `REPORTS_TO` edge for that row finds no manager and is skipped. Ask HR to sort the export from the top of the hierarchy down, or load reporting lines as a separate small export after employees.

## How structured and document data converge

A structured row and a document mention end up on the **same node** only when both use the same entity type and the same key or name.

* Structured mappings create the node with its canonical key: Client `Lakeshore Health`, Project `NB-2024-117`, Employee `robert.chen@northbridge.com`.
* On the [artifact type](/implementation/key-concepts#artifact-type), set document entities to **Create or Match** (runs [matching](/implementation/key-concepts#matching-strategy) and can add new nodes) or **Match Only** (updates an existing node and never creates one).
* Document matching tolerates variants (fuzzy, synonyms, suffix removal). Structured matching does not. So the canonical form belongs in the structured file, and documents are matched onto it. That is covered in [Workshop 7: Identity & Matching](/implementation/ws-identity-and-matching).

## Worked example: Northbridge Consulting

Marcus Lee (super user) ran four short sessions: HR for Workday, Sales ops for Salesforce, and Finance/PMO for the two Deltek exports.

### employees.csv (Workday)

```csv theme={null}
employee_id,full_name,work_email,title,level,practice,location,manager_email,last_modified
E10234,Robert Chen,robert.chen@northbridge.com,Senior Consultant,Senior,Technology,Chicago,alex.romero@northbridge.com,2026-08-14T09:12:00Z
E10077,Alex Romero,alex.romero@northbridge.com,Director,Director,Technology,Chicago,priya.shah@northbridge.com,2026-07-02T16:40:00Z
```

| Column | Entity type | Attribute | Role | Operation / match | Format notes |
| - | - | - | - | - | - |
| `employee_id` | Employee | employee\_id | Record ID Column | — | Stable Workday ID |
| `full_name` | Employee | name | Attribute | — | As HR records it ("Robert", not "Bob") |
| `work_email` | Employee | email | **Match key** | Create or Match on `email` | Lowercase; HR confirmed |
| `title` | Employee | title | Attribute | — | Text |
| `level` | Employee | level | Attribute | — | Text |
| `practice` | Practice | name | Match key for Practice | Create or Match on `name` | Must read exactly `Strategy`, `Technology` or `Operations` |
| `location` | Employee | location | Attribute | — | Office city |
| `manager_email` | Employee | — | Relationship end | Employee `REPORTS_TO` Employee: start `work_email` → `email`, end `manager_email` → `email` | Export sorted top-down |
| `last_modified` | — | — | Timestamp Column | — | ISO timestamp |
| (derived) | — | — | Relationship | Employee `MEMBER_OF` Practice: start `work_email` → `email`, end `practice` → `name` | — |

### assignments.csv (Deltek)

```csv theme={null}
assignment_id,project_code,employee_email,role,start_date,end_date,hours,last_modified
A-55812,NB-2024-117,robert.chen@northbridge.com,Data Architect,2024-03-04,2024-11-29,812,2026-08-30T17:40:00Z
A-55813,NB-2024-117,alex.romero@northbridge.com,Engagement Lead,2024-02-19,2024-12-13,240,2026-08-30T17:40:00Z
```

| Column | Entity type | Attribute | Role | Operation / match | Format notes |
| - | - | - | - | - | - |
| `assignment_id` | — | — | Record ID Column | — | Stable Deltek ID |
| `project_code` | Project | — | Relationship end | End of `WORKS_ON_PROJECT`, matched on `project_code` | Same format as `projects.csv`: `NB-YYYY-NNN` |
| `employee_email` | Employee | — | Relationship end | Start of `WORKS_ON_PROJECT`, matched on `email` | Must equal Workday `work_email` |
| `role` | — | role (relationship attribute) | Relationship attribute | — | Controlled list; "Engagement Lead" spelled one way |
| `start_date`, `end_date` | — | start\_date, end\_date | Relationship attributes | — | ISO dates |
| `hours` | — | hours | Relationship attribute | — | Plain number |
| `last_modified` | — | — | Timestamp Column | — | ISO timestamp |

This file creates no nodes. Every row depends on an Employee and a Project that earlier files created. The session turned up two export fixes:

* Deltek listed contractors who are not in Workday, so their assignments would be skipped. HR agreed to include active contractors in `employees.csv`.
* Deltek wrote role titles two ways ("Engagement Lead", "Engagement Mgr"). Finance/PMO standardised them, because golden question Q1 asks "who led each?" and the assistant's Cypher instruction looks for the role `Engagement Lead`.

### The other mappings

* `accounts.csv` → Client (Create or Match on `name`, the canonical Salesforce name), with `account_id` and `industry` as attributes. Industry values match the **Industry** taxonomy.
* `projects.csv` → Project (Create or Match on `project_code`), Project `FOR_CLIENT` Client (end matched on the client name column, which must equal `accounts.csv` exactly), Project `DELIVERED_BY` Practice.
* `opportunities.csv` → Proposal outcome updates, matched on opportunity name. Optional; deferred to after the pilot.

## Accelerate with AI

**Auto-Map with AI** is available in the mapping editor today. It reads the source columns (and sample values when you upload a CSV) and suggests node mappings (column → entity type and attribute) and relationship mappings against the mapping's ontology.

1. Open or create a mapping in **Model & Define > Data Mapping** and add the source fields, either by uploading a sample CSV or with **Import from API**. The button stays disabled until the mapping has source fields and an ontology.
2. Click **Auto-Map with AI**. Choose a model and, if you want, add a user instruction, for example "Employees are matched on work email; practice is a separate Practice entity".
3. Click **Generate**. Review the node mapping and relationship mapping suggestions, clear the checkboxes for any you reject, and click **Add mapping**.
4. Columns the AI did not map are listed separately. Map them by hand or leave them out on purpose.

Treat the output as a first draft for the workshop, not a result. The AI cannot know which column your data owner trusts as the key, or whether the client names match across files. Those are the decisions this workshop exists to make. **Admin Copilot** (⌘J) can also answer questions about mapping options from these docs while you work.

## Entering it in Experio

<Steps>
  <Step title="Create the mapping">
    **Model & Define > Data Mapping > Create New Mapping**. Choose the ontology, then add source fields (upload a sample CSV, add fields by hand, or **Import from API**).
  </Step>

  <Step title="Add node mappings">
    For each column, choose the entity type and attribute. For the key column of each entity type, set the operation (**Create Only**, **Match Only** or **Create or Match**) and the match property.
  </Step>

  <Step title="Add relationship mappings">
    Choose the relationship type, the start and end entity types, and for each end the column and property used to find it. Add relationship attributes (such as `role` and `hours`).
  </Step>

  <Step title="Set incremental ingestion">
    In **Incremental Ingestion**, set the **Record ID Column** and the **Timestamp Column**. Save.
  </Step>

  <Step title="Connect the source and pick the mapping">
    Set up the connection in **Connect > Connectors** (file storage or an API connection), then create the structured data source in **Connect > Data Sources** and select the mapping definition.
  </Step>

  <Step title="Load in order and check">
    Run the scans in the agreed load order. **Process > Flows** can chain them so the order holds on every refresh. After each load, check node and relationship counts against the row counts in the file.
  </Step>
</Steps>

See [Data Mapping](/admin-guide/data-mapping) and [Data Sources](/admin-guide/data-sources) for field-level detail. Mappings show a compatibility status against the ontology. An **invalid** mapping blocks its data source's jobs until you fix it and re-validate.

## Common pitfalls

* **Key values formatted differently across files.** `Lakeshore Health` in one file and `Lakeshore Health, Inc.` in another gives two Client nodes. Structured matching will never join them.
* **Loading a connecting file before its master data.** Assignments loaded before employees produce no edges, and nothing looks broken until someone asks a staffing question.
* **Expecting a relationship mapping to create missing people or projects.** It never does. Missing ends are skipped.
* **Header rows that change between exports.** A renamed column silently stops filling its attribute. Agree a fixed export layout with the data owner.
* **Mapping every column "just in case".** Unused attributes add noise to Cypher generation. Map what the golden questions need.
* **Names as keys when a code exists.** Match on `project_code` and `email` whenever you can. Names change; codes rarely do.
* **Keys that don't match what the documents say.** If status reports cite `NB-2024-117` but Deltek exports `2024117`, documents set to **Match Only** will never find the project.

## Exit criteria

* [ ] A mapping worksheet exists for every structured export in the inventory, signed off by its data owner.
* [ ] Each entity type has one agreed match key, with the same format in every file that uses it.
* [ ] Every relationship mapping has both ends created by a node mapping earlier in the load order.
* [ ] Record ID and timestamp columns are chosen for each export (or explicitly left empty).
* [ ] Date, number, list and enum formats are agreed; export fixes have owners and dates.
* [ ] The load order diagram is agreed and recorded.
* [ ] Each mapping is saved in **Model & Define > Data Mapping** and shows a valid compatibility status.
* [ ] A test load of the sample rows gives the expected node and relationship counts.

## Next

Continue to [Workshop 7: Identity & Matching](/implementation/ws-identity-and-matching) to decide how document mentions are matched onto the nodes these mappings create.