# Attach business fields

> Recipe: add customer, account or order fields to conversations as extra columns, with key cleanup, conflict checks and a match rate.

## When you need it

You want to slice what customers said by what you know about them: plan tier, region, lifetime orders, deal stage. There are [two ways in](/data/join-business-data#two-ways-to-bring-business-data-in):

1. **Extra columns on the conversation file**, best for a few fields that describe the customer at the time of the conversation. This recipe does that.
2. **A separate file under Structured data**, best for data with its own grain (one row per order, per account per month). Upload it as described in [Business data format](/data/business-data-format); the agent joins on a shared id. Business data comes in by file upload only today (an API for it is on the roadmap). Use this recipe's key cleanup and match-rate check on it too.

Orders, opportunities and other records are business data, not messages. Don't turn an order line or a CRM record into a fake `text` row: it would be enriched as if a customer had written it.

## Input

Example input: conversations, already in the Dimension Labs format, with a `customer_id` column that isn't clean (a leading space, lower case):

```csv title="conversations.csv"
sessionId,userId,incoming,text,dimensionlabs_timestamp,conversationId,customer_id
7001,cus_51,true,The grinder makes a clicking noise.,1772615700000,7001, CUS-0051
7001,cus_51,false,We'll send a replacement today.,1772616660000,7001, CUS-0051
7002,cus_77,true,Do you ship to Canada?,1772726400000,7002,cus-0077
7003,cus_90,true,Cancel my subscription please.,1772812800000,7003,CUS-0090
```

A customer list from the CRM. `CUS-0077` appears twice with different tiers, and `CUS-0090` is missing:

```csv title="customers.csv"
customer_id,tier,region,updated_at
CUS-0051,Gold,West,2026-01-10
CUS-0077,Silver,West,2025-11-02
CUS-0077,Gold,West,2026-02-20
CUS-0012,Bronze,South,2025-09-30
```

## Output

```csv title="conversations-with-fields.csv"
sessionId,userId,incoming,text,dimensionlabs_timestamp,conversationId,customer_id,tier,region
7001,cus_51,true,The grinder makes a clicking noise.,1772615700000,7001,CUS-0051,Gold,West
7001,cus_51,false,We'll send a replacement today.,1772616660000,7001,CUS-0051,Gold,West
7002,cus_77,true,Do you ship to Canada?,1772726400000,7002,CUS-0077,Gold,West
7003,cus_90,true,Cancel my subscription please.,1772812800000,7003,CUS-0090,,
```

```text
Conversations matched: 2 of 3
Unmatched keys: CUS-0090
Keys with conflicting values (most recent kept): CUS-0077
```

## Rules

- **Make the keys look the same on both sides.** Trim spaces, and agree on case. [Leading zeros, case and stray spaces all break a join](/data/join-business-data#the-key). Only change case if the ids are case-insensitive in the source system.
- **Keep the cleaned key in the file.** The agent can then join the same conversations to other business tables on `customer_id` later.
- **One record per key.** When a key appears twice with different values, decide which wins (here, the most recent `updated_at`) and list the conflicts so someone can fix the source.
- **Measure the match rate before you upload.** A low rate nearly always means the ids differ in format. The [file checker's join test](/data/check-a-file#test-a-join) does the same check in your browser.
- **Fields are constant within a conversation.** Every message in a session gets the same values.
- **Only bring what you'll use, and no personal data.** Emails, phone numbers and addresses rarely help an analysis, and must never be the join key. Join on an internal id such as `customer_id`; if the only shared key is an email, hash it the same way on both sides and join on the hash. See [PII redaction](/data/pii-redaction).

## Script

Node 18 or later, with `npm install papaparse`.

```js title="attach-fields.mjs"
// Attach customer fields to every message of a conversation as extra columns, and report the match rate.
// Usage: node attach-fields.mjs conversations.csv customers.csv conversations-with-fields.csv



const [convFile, customerFile, output] = process.argv.slice(2);
const KEY = 'customer_id';
const FIELDS = ['tier', 'region'];
// Make both sides look the same. Only uppercase if your ids are case-insensitive in the source system.
const normalize = (k) => String(k ?? '').trim().toUpperCase();

const read = (f) => Papa.parse(fs.readFileSync(f, 'utf8'), { header: true, skipEmptyLines: 'greedy' }).data;
const conversations = read(convFile);
const customers = read(customerFile);

// One record per key. When a key appears twice with different values, keep the most recent and say so.
const byKey = new Map();
const conflicts = [];
for (const c of customers) {
  const k = normalize(c[KEY]);
  const prev = byKey.get(k);
  if (prev && FIELDS.some((f) => prev[f] !== c[f])) conflicts.push(k);
  if (!prev || c.updated_at > prev.updated_at) byKey.set(k, c);
}

const sessions = new Set();
const matched = new Set();
const out = conversations.map((m) => {
  const k = normalize(m[KEY]);
  sessions.add(m.sessionId);
  const c = byKey.get(k);
  if (c) matched.add(m.sessionId);
  return { ...m, [KEY]: k, ...Object.fromEntries(FIELDS.map((f) => [f, c ? c[f] : ''])) };
});

fs.writeFileSync(output, Papa.unparse(out, { newline: '\n' }) + '\n');
console.log(`Conversations matched: ${matched.size} of ${sessions.size}`);
console.log(`Unmatched keys: ${[...new Set(out.filter((m) => !matched.has(m.sessionId)).map((m) => m[KEY]))].join(', ') || 'none'}`);
console.log(`Keys with conflicting values (most recent kept): ${[...new Set(conflicts)].join(', ') || 'none'}`);
```

```bash
node attach-fields.mjs conversations.csv customers.csv conversations-with-fields.csv
```

## Variations

- **Fields that change over time** (the plan a customer was on when they wrote): pick the record whose date range contains the conversation's `dimensionlabs_timestamp`, rather than the latest one.
- **Totals from an orders file** (orders in the last 90 days, lifetime value): compute them per key before attaching, and say in the column name what they cover, for example `orders_90d_before_chat`.
- **Many fields, or fields with their own grain**: upload the business file under **Structured data** instead and ask the agent to join, for example *"Join my conversations to the customers table on customer_id."*