Combine several exports

When you need it

You have more than one export for the same customers: a chat export and an email export, or monthly files that overlap by a few days. You can upload each as its own source, or together under the Omni Channel type. To combine them into one file, three things go wrong if you just concatenate:

• Ids collide. Chat 1001 and email ticket 1001 are different conversations, but the same sessionId would merge them.
• Overlapping exports repeat messages. The February and March files both contain the message sent on the last day of February. Dimension Labs removes duplicates when it ingests a file, so the upload is safe, but your own row counts will be off unless you remove them too.
• Columns differ. One file has store, the other account_id.

Input

Example input: three files already in the Dimension Labs format, from earlier recipes or the toolkit.

chat-feb.csv

sessionId,userId,incoming,text,dimensionlabs_timestamp,conversationId,store
1001,cus_51,true,Is the Pearl store open late?,1771340400000,1001,Portland Pearl
1001,cus_51,false,It's open until 8 PM.,1771340401000,1001,Portland Pearl

email-feb.csv

sessionId,userId,incoming,text,dimensionlabs_timestamp,conversationId,account_id
1001,cus_51,true,My wholesale invoice is wrong.,1771344000000,1001,ACCT-204
1001,cus_51,false,Fixed. A new invoice is attached.,1771347600000,1001,ACCT-204

email-mar.csv

sessionId,userId,incoming,text,dimensionlabs_timestamp,conversationId,account_id
1001,cus_51,false,Fixed. A new invoice is attached.,1771347600000,1001,ACCT-204
1002,cus_90,true,Please pause my subscription.,1772812800000,1002,ACCT-311

Output

combined.csv

sessionId,userId,incoming,text,dimensionlabs_timestamp,conversationId,channel,store,account_id
chat-1001,cus_51,true,Is the Pearl store open late?,1771340400000,chat-1001,chat,Portland Pearl,
chat-1001,cus_51,false,It's open until 8 PM.,1771340401000,chat-1001,chat,Portland Pearl,
email-1001,cus_51,true,My wholesale invoice is wrong.,1771344000000,email-1001,email,,ACCT-204
email-1001,cus_51,false,Fixed. A new invoice is attached.,1771347600000,email-1001,email,,ACCT-204
email-1002,cus_90,true,Please pause my subscription.,1772812800000,email-1002,email,,ACCT-311

Rules

• Prefix ids with their source (chat-1001, email-1001) so conversations from different systems never share a sessionId. Use the same prefix every time, so monthly files line up.
• Add a channel column. It lets the agent compare channels (omni-channel analysis).
• Remove duplicates on a message key: session, direction, timestamp and text. If your exports have a message id, key on that instead. Ingestion removes duplicates too; doing it here keeps your counts honest.
• Give replies the customer’s id. The agent’s reply in 1001 carries the customer’s userId, cus_51, as the conversation format asks.
• Take the union of the columns. A column missing from one source is left empty on its rows.
• Keep each conversation its own session. To see everything about one customer or account across channels, keep a shared key such as account_id as a column and ask the agent to group by it. Merging different conversations into one session would also make enrichment read them as one conversation.
• Keep real timestamps. Don’t renumber messages to be one second apart across a merged history: weeks between two emails would become seconds. Only move messages that collide, as in Fix timestamps.
• Compare counts. The script prints rows read, added and skipped as duplicates per file. Rows added across files should equal the rows in the output.

Script

Node 18 or later, with npm install papaparse. Pass each file as channel=path.

combine.mjs

// Combine several converted exports into one upload file.
// Usage: node combine.mjs combined.csv chat=chat-feb.csv email=email-feb.csv email=email-mar.csv
import fs from 'node:fs';
import Papa from 'papaparse';

const [output, ...inputs] = process.argv.slice(2);
const REQUIRED = ['sessionId', 'userId', 'incoming', 'text', 'dimensionlabs_timestamp', 'conversationId'];

const seen = new Set();
const rows = [];
const extra = new Set();
for (const arg of inputs) {
  const [channel, file] = arg.split('=');
  const { data } = Papa.parse(fs.readFileSync(file, 'utf8'), { header: true, skipEmptyLines: 'greedy' });
  let added = 0, dupes = 0;
  for (const r of data) {
    // Prefix ids with the channel: ticket 1001 and chat 1001 are different conversations.
    const sessionId = `${channel}-${r.sessionId}`;
    const conversationId = `${channel}-${r.conversationId || r.sessionId}`;
    const key = [sessionId, r.incoming, r.dimensionlabs_timestamp, r.text].join('|');
    if (seen.has(key)) { dupes++; continue; } // the same message in two overlapping exports
    seen.add(key);
    Object.keys(r).filter((k) => !REQUIRED.includes(k)).forEach((k) => extra.add(k));
    rows.push({ ...r, sessionId, conversationId, channel });
    added++;
  }
  console.log(`${file}: ${data.length} rows, ${added} added, ${dupes} ${dupes === 1 ? 'duplicate' : 'duplicates'} skipped`);
}

rows.sort((a, b) => a.sessionId.localeCompare(b.sessionId) || a.dimensionlabs_timestamp - b.dimensionlabs_timestamp);
const fields = [...REQUIRED, 'channel', ...[...extra].filter((k) => k !== 'channel')];
fs.writeFileSync(output, Papa.unparse({ fields, data: rows.map((r) => fields.map((f) => r[f] ?? '')) }, { newline: '\n' }) + '\n');
console.log(`${rows.length} rows in ${new Set(rows.map((r) => r.sessionId)).size} conversations -> ${output}`);

node combine.mjs combined.csv chat=chat-feb.csv email=email-feb.csv email=email-mar.csv
# chat-feb.csv: 2 rows, 2 added, 0 duplicates skipped
# email-feb.csv: 2 rows, 2 added, 0 duplicates skipped
# email-mar.csv: 2 rows, 1 added, 1 duplicate skipped
# 5 rows in 3 conversations -> combined.csv

Variations

• Exports in different shapes: convert each one first (with its own recipe or toolkit mapping), then combine the results.
• The same customer under different ids in each system: attach a shared customer or account id to both before combining, as in Attach business fields.
• A new month every month: upload the new file to the same source. Messages that were already in last month’s upload are removed as duplicates when the file is ingested, as long as their ids, timestamps and text are unchanged, so you don’t need to trim the overlap. Then run a backfill over the new dates.