← back to Marketing Command Center
CC Phase 1 (DTD verdict A): CSV import (contacts/campaigns/opens, no token) + ranked opens (campaigns by open-rate, most-engaged contacts) — normalized ctct-store, same shape the live v3 API will write
54fd8bb1b2bc748bf36618dadc839e195ae892fa · 2026-06-13 22:36:25 -0700 · Steve Abrams
Files touched
M modules/constant-contact/index.jsM public/panels/constant-contact.htmlM public/panels/constant-contact.js
Diff
commit 54fd8bb1b2bc748bf36618dadc839e195ae892fa
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sat Jun 13 22:36:25 2026 -0700
CC Phase 1 (DTD verdict A): CSV import (contacts/campaigns/opens, no token) + ranked opens (campaigns by open-rate, most-engaged contacts) — normalized ctct-store, same shape the live v3 API will write
---
modules/constant-contact/index.js | 79 +++++++++++++++++++++++++++++++++++++
public/panels/constant-contact.html | 33 ++++++++++++++++
public/panels/constant-contact.js | 34 ++++++++++++++++
3 files changed, 146 insertions(+)
diff --git a/modules/constant-contact/index.js b/modules/constant-contact/index.js
index ec4c58a..4721413 100644
--- a/modules/constant-contact/index.js
+++ b/modules/constant-contact/index.js
@@ -7,9 +7,58 @@
// Owns nothing on disk persistently; draft ids are returned to the caller.
// Secrets come from process.env only — never hardcode keys.
const brand = require('../../lib/brand.js');
+const fs = require('fs');
+const path = require('path');
const API = 'https://api.cc.email/v3';
+// ── Phase-1 CSV store (no token needed) ───────────────────────────────────────
+// Constant Contact's UI exports contacts + campaign reports as CSV today, with
+// zero credentials. We ingest those into a normalized store so ranked opens +
+// old contacts work NOW; the live v3 API (when a token lands) writes the same
+// shape. Runtime state, gitignored.
+const CTCT_STORE = path.join(__dirname, '..', '..', 'data', 'ctct-store.json');
+function readCtct() { try { return { contacts: [], campaigns: [], opens: [], ...JSON.parse(fs.readFileSync(CTCT_STORE, 'utf8')) }; } catch { return { contacts: [], campaigns: [], opens: [] }; } }
+function writeCtct(s) { fs.mkdirSync(path.dirname(CTCT_STORE), { recursive: true }); fs.writeFileSync(CTCT_STORE, JSON.stringify(s, null, 2)); }
+
+// minimal RFC-4180-ish CSV parser (handles quoted fields + embedded commas/newlines)
+function parseCSV(text) {
+ const rows = []; let row = [], field = '', q = false;
+ text = String(text).replace(/\r\n?/g, '\n');
+ for (let i = 0; i < text.length; i++) {
+ const c = text[i];
+ if (q) { if (c === '"') { if (text[i + 1] === '"') { field += '"'; i++; } else q = false; } else field += c; }
+ else if (c === '"') q = true;
+ else if (c === ',') { row.push(field); field = ''; }
+ else if (c === '\n') { row.push(field); rows.push(row); row = []; field = ''; }
+ else field += c;
+ }
+ if (field.length || row.length) { row.push(field); rows.push(row); }
+ return rows.filter(r => r.length > 1 || (r.length === 1 && r[0].trim()));
+}
+function rowsToObjects(rows) {
+ if (!rows.length) return [];
+ const hdr = rows[0].map(h => h.trim().toLowerCase());
+ return rows.slice(1).map(r => { const o = {}; hdr.forEach((h, i) => { o[h] = (r[i] || '').trim(); }); return o; });
+}
+const pickCol = (o, ...needles) => { for (const k of Object.keys(o)) if (needles.some(n => k.includes(n))) return o[k]; return ''; };
+const numOf = s => { const m = String(s).replace(/[,%$]/g, '').match(/-?[\d.]+/); return m ? parseFloat(m[0]) : 0; };
+function normalizeCsv(kind, objs) {
+ if (kind === 'contacts') return objs.map(o => ({
+ email: (pickCol(o, 'email') || '').toLowerCase(), first: pickCol(o, 'first'), last: pickCol(o, 'last'),
+ lists: (pickCol(o, 'list') || '').split(/[;|]/).map(s => s.trim()).filter(Boolean), opens: numOf(pickCol(o, 'open')),
+ })).filter(c => c.email);
+ if (kind === 'campaigns') return objs.map(o => {
+ const sends = numOf(pickCol(o, 'sent', 'sends', 'recipients', 'delivered'));
+ const opens = numOf(pickCol(o, 'opens', 'opened'));
+ const clicks = numOf(pickCol(o, 'clicks', 'clicked'));
+ let or = numOf(pickCol(o, 'open rate', 'open %', 'open%')); if (or > 1) or = or / 100; if (!or) or = sends ? opens / sends : 0;
+ return { name: pickCol(o, 'campaign', 'name', 'subject', 'email name') || '(untitled)', sends, opens, clicks, open_rate: Number(or.toFixed(4)) };
+ }).filter(c => c.name && c.name !== '(untitled)');
+ if (kind === 'opens') return objs.map(o => ({ email: (pickCol(o, 'email') || '').toLowerCase(), campaign: pickCol(o, 'campaign', 'email name', 'subject') })).filter(x => x.email);
+ return [];
+}
+
const live = () => Boolean(process.env.CTCT_ACCESS_TOKEN);
// ── thin v3 fetch helper ──────────────────────────────────────────────────────
@@ -312,6 +361,36 @@ function mount(router) {
message: `Scheduled draft ${draft_id} for ${scheduled_time || 'immediate'} send.`,
});
}));
+
+ // ── Phase 1: CSV import (no token) ──────────────────────────────────────────
+ // POST { kind: 'contacts'|'campaigns'|'opens', csv: '<raw csv text>' }
+ router.post('/import/csv', guard(async (req, res) => {
+ const { kind, csv } = req.body || {};
+ if (!['contacts', 'campaigns', 'opens'].includes(kind)) return res.status(400).json({ error: 'kind must be contacts | campaigns | opens' });
+ if (!csv || typeof csv !== 'string') return res.status(400).json({ error: 'csv text required' });
+ const rows = normalizeCsv(kind, rowsToObjects(parseCSV(csv)));
+ const store = readCtct();
+ if (kind === 'contacts') { const m = new Map(store.contacts.map(c => [c.email, c])); rows.forEach(c => m.set(c.email, { ...m.get(c.email), ...c })); store.contacts = [...m.values()]; }
+ else if (kind === 'campaigns') { const m = new Map(store.campaigns.map(c => [c.name, c])); rows.forEach(c => m.set(c.name, c)); store.campaigns = [...m.values()]; }
+ else { store.opens = store.opens.concat(rows); }
+ store._meta = { last_import: new Date().toISOString(), contacts: store.contacts.length, campaigns: store.campaigns.length, opens: store.opens.length };
+ writeCtct(store);
+ res.json({ ok: true, kind, imported: rows.length, totals: store._meta });
+ }));
+
+ // ── Phase 1: ranked opens (campaigns by open-rate + most-engaged contacts) ──
+ router.get('/opens-ranked', guard(async (_req, res) => {
+ const store = readCtct();
+ const campaigns = store.campaigns.slice()
+ .sort((a, b) => b.open_rate - a.open_rate || b.clicks - a.clicks)
+ .map((c, i) => ({ rank: i + 1, ...c }));
+ const eng = new Map();
+ for (const o of store.opens) eng.set(o.email, (eng.get(o.email) || 0) + 1);
+ if (eng.size === 0) for (const c of store.contacts) if (c.opens) eng.set(c.email, c.opens);
+ const contacts = [...eng.entries()].map(([email, opens]) => ({ email, opens }))
+ .sort((a, b) => b.opens - a.opens).slice(0, 100).map((c, i) => ({ rank: i + 1, ...c }));
+ res.json({ source: (store.campaigns.length || store.contacts.length) ? 'csv' : 'empty', campaigns, contacts, meta: store._meta || {} });
+ }));
}
function groupByDate(entries) {
diff --git a/public/panels/constant-contact.html b/public/panels/constant-contact.html
index d55b55a..c508173 100644
--- a/public/panels/constant-contact.html
+++ b/public/panels/constant-contact.html
@@ -16,6 +16,39 @@
</div>
+<div class="card" id="cc-import">
+ <h2>Import old emails & campaigns (CSV)</h2>
+ <div class="muted" style="font-size:12px">No API token needed — export from Constant Contact (Contacts → Export, or Reporting → Export) and drop the CSV here. <b id="cc-store-meta"></b></div>
+ <div class="row" style="margin-top:12px;align-items:flex-end">
+ <div style="flex:0;min-width:160px">
+ <label for="cc-imp-kind">CSV type</label>
+ <select id="cc-imp-kind">
+ <option value="campaigns">Campaign report</option>
+ <option value="contacts">Contacts</option>
+ <option value="opens">Per-contact opens</option>
+ </select>
+ </div>
+ <div style="flex:1;min-width:220px">
+ <label for="cc-imp-file">Choose CSV file</label>
+ <input id="cc-imp-file" type="file" accept=".csv,text/csv">
+ </div>
+ <button class="btn gold" id="cc-imp-go" style="height:38px">Import</button>
+ </div>
+ <details style="margin-top:10px"><summary class="muted" style="font-size:12px;cursor:pointer">…or paste CSV text</summary>
+ <textarea id="cc-imp-text" rows="4" style="margin-top:8px" placeholder="Paste CSV rows (header row first)…"></textarea>
+ </details>
+ <div id="cc-imp-msg" class="muted" style="font-size:12.5px;margin-top:8px"></div>
+</div>
+
+<div class="card" id="cc-ranked">
+ <h2>Ranked opens</h2>
+ <div class="muted" style="font-size:12px">Best-performing campaigns by open rate, and your most-engaged contacts.</div>
+ <div class="grid" style="margin-top:12px;grid-template-columns:1fr 1fr;gap:18px">
+ <div><div style="font-weight:600;font-size:13px;margin-bottom:6px">Top campaigns</div><div id="cc-rank-campaigns" class="muted">—</div></div>
+ <div><div style="font-weight:600;font-size:13px;margin-bottom:6px">Most-engaged contacts</div><div id="cc-rank-contacts" class="muted">—</div></div>
+ </div>
+</div>
+
<div class="card" id="cc-campaigns">
<h2>Recent Campaigns</h2>
<div class="muted" style="font-size:12px">Name · status · sends · open rate</div>
diff --git a/public/panels/constant-contact.js b/public/panels/constant-contact.js
index 664737f..e775821 100644
--- a/public/panels/constant-contact.js
+++ b/public/panels/constant-contact.js
@@ -162,5 +162,39 @@ window.MCC_PANELS['constant-contact'] = {
} catch (e) { setMsg(`Error: ${e.message}`, true); }
finally { $('#cc-schedule').disabled = false; }
};
+
+ // ── Phase 1: CSV import + ranked opens ──────────────────────────────────────
+ const impMsg = (t, warn) => { const el = $('#cc-imp-msg'); el.textContent = t || ''; el.style.color = warn ? 'var(--accent)' : 'var(--mut)'; };
+ async function loadRanked() {
+ let d; try { d = await api('/opens-ranked'); } catch { return; }
+ const meta = d.meta || {};
+ if (meta.last_import) $('#cc-store-meta').textContent = `Imported: ${meta.campaigns || 0} campaigns · ${meta.contacts || 0} contacts · ${meta.opens || 0} open-events.`;
+ $('#cc-rank-campaigns').innerHTML = (d.campaigns || []).slice(0, 12).map(c =>
+ `<div class="row" style="justify-content:space-between;border-top:1px solid var(--line);padding:6px 0;font-size:12.5px">
+ <span>${c.rank}. ${esc(c.name)}</span>
+ <span><b>${pct(c.open_rate)}</b> <span class="muted">· ${Number(c.sends || 0).toLocaleString()} sent</span></span>
+ </div>`).join('') || '<div class="muted">No campaign data yet — import a campaign report CSV.</div>';
+ $('#cc-rank-contacts').innerHTML = (d.contacts || []).slice(0, 12).map(c =>
+ `<div class="row" style="justify-content:space-between;border-top:1px solid var(--line);padding:6px 0;font-size:12.5px">
+ <span>${c.rank}. ${esc(c.email)}</span><span class="pill">${Number(c.opens || 0).toLocaleString()} opens</span>
+ </div>`).join('') || '<div class="muted">No contact-engagement data — import a contacts CSV (with an Opens column) or a per-contact opens CSV.</div>';
+ }
+ async function doImport(csv) {
+ const kind = $('#cc-imp-kind').value;
+ if (!csv || !csv.trim()) { impMsg('Choose a CSV file or paste CSV text first.', true); return; }
+ impMsg('Importing…');
+ try {
+ const r = await api('/import/csv', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ kind, csv }) });
+ if (r.error) impMsg('✗ ' + r.error, true);
+ else { impMsg(`✓ Imported ${r.imported} ${kind} row(s).`); $('#cc-imp-text').value = ''; $('#cc-imp-file').value = ''; loadRanked(); }
+ } catch (e) { impMsg('Error: ' + e.message, true); }
+ }
+ $('#cc-imp-go').onclick = () => {
+ const f = $('#cc-imp-file').files[0];
+ const pasted = $('#cc-imp-text').value;
+ if (f) { const rd = new FileReader(); rd.onload = () => doImport(rd.result); rd.readAsText(f); }
+ else doImport(pasted);
+ };
+ loadRanked();
},
};
← 0c63f9f Image Bank: finish Repost UI — gated modal (image preview, r
·
back to Marketing Command Center
·
MCC: stage Bold Blush + Custom Mural launch posts — 8 social 5d49b72 →