Attach business fields

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:

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; 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):

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:

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

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,,

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. 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 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.

Script

Node 18 or later, with npm install papaparse.

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
import fs from 'node:fs';
import Papa from 'papaparse';

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'}`);

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.”