`scripts/generate-ig-dm-drafts.js`

204 lines
#!/usr/bin/env node
// Generate Instagram DM drafts for unclaimed studios that are running paid ads.
//
// Why IG DM (not email) for this cohort?
//   The dream-team-fast panel ruled IG DM is the right channel for the studios
//   we caught running paid ads:
//     - It's a business channel, not commercial email — zero CAN-SPAM exposure.
//     - Higher response rate than cold email in the small luxury-trade niche.
//     - The recipient sees Steve's IG profile (signal of legitimacy) before
//       the message text — the message lands as one trade peer to another.
//
// IMPORTANT — this script is READ-ONLY.
//   - No DB writes.
//   - No IG API calls. Meta TOS forbids unauthorized automation; Steve sends
//     each DM by hand from his own IG account.
//   - We just generate the personalized text he'll paste.
//
// Usage:
//   node scripts/generate-ig-dm-drafts.js                  # CSV to stdout
//   node scripts/generate-ig-dm-drafts.js --out=batch.csv  # CSV to file
//   node scripts/generate-ig-dm-drafts.js --min=2          # studios on 2+ paid platforms
//   node scripts/generate-ig-dm-drafts.js --state=CA       # state filter
//   node scripts/generate-ig-dm-drafts.js --limit=5        # cap for the day's batch
//
// CSV columns (7):
//   business_name, ig_handle, city, state, paid_platforms, dm_message, claim_url
//
// The dm_message ends with the claim_url so Steve just pastes the whole field
// into the IG conversation and hits send.

require('dotenv').config();
const fs = require('fs');
const path = require('path');
const db = require('../lib/db');

const ARGS = process.argv.slice(2);
const arg = (k, def = null) => {
  const a = ARGS.find(x => x.startsWith(`--${k}=`));
  return a ? a.split('=').slice(1).join('=') : def;
};

const OUT = arg('out');
const MIN = parseInt(arg('min', '1'), 10);
const STATE = (arg('state') || '').toUpperCase() || null;
const LIMIT = parseInt(arg('limit', '0'), 10); // 0 = no cap

const PUBLIC_URL = (process.env.PUBLIC_URL || 'https://nationalpaperhangers.com').replace(/\/+$/, '');

// Map raw signal keys to human-readable platform names.
const PLATFORM_LABEL = {
  google_ads: 'Google Ads',
  meta_pixel: 'Meta',
  tiktok_pixel: 'TikTok',
  pinterest_tag: 'Pinterest',
  linkedin_insight: 'LinkedIn',
  twitter_pixel: 'Twitter/X',
  reddit_pixel: 'Reddit',
  bing_uet: 'Bing',
  snap_pixel: 'Snap'
};

function paidPlatforms(signals) {
  if (!signals) return [];
  return Object.entries(PLATFORM_LABEL)
    .filter(([k]) => signals[k] === true)
    .map(([, v]) => v);
}

// Format the platforms list as a readable English clause.
//   ['Google Ads']                  → 'Google Ads'
//   ['Google Ads', 'Pinterest']     → 'Google Ads + Pinterest'
//   ['Google Ads','Meta','TikTok']  → 'Google Ads, Meta, and TikTok'
function platformsClause(list) {
  if (list.length === 0) return '';
  if (list.length === 1) return list[0];
  if (list.length === 2) return `${list[0]} + ${list[1]}`;
  return list.slice(0, -1).join(', ') + ', and ' + list[list.length - 1];
}

function locationClause(city, state) {
  if (city && state) return `over in ${city}`;
  if (city) return `over in ${city}`;
  if (state) return `out in ${state}`;
  return 'on your end';
}

// Build the personalized 4-6 sentence DM. Hand-paste-ready.
//
// Voice notes:
//   - Trade-peer tone, not sales-pitch tone.
//   - Reference one specific signal we caught (the platforms they're spending on).
//   - Mention the Designer Wallcoverings → NPH context (DW already routes
//     trade installs to the directory — Steve owns DW).
//   - Soft CTA: "if it's interesting, here's the claim link" — no pressure.
//   - End with the claim URL so the whole field can be pasted at once.
function buildDmMessage({ businessName, city, state, paidPlatforms: plats, claimUrl }) {
  const platformsBit = plats.length
    ? `noticed you're running ${platformsClause(plats)} — that tells me you're actively chasing the trade buyer`
    : `saw you doing real marketing work`;
  const where = locationClause(city, state);

  // 5 sentences, ~75 words. Designed to read in 10 seconds.
  return [
    `Hey — Steve from National Paper Hangers (also Designer Wallcoverings).`,
    `${platformsBit}, which is exactly the cohort our directory is built for.`,
    `I listed ${businessName} ${where} so designers and hospitality buyers searching outside LA can find you, and DW is already routing trade installs to NPH listings.`,
    `If you want to take control of the page — bio, portfolio, availability, accept self-booked consults — claim it here:`,
    `${claimUrl}`,
    `Basic listing is free, no obligation either way. Happy to answer anything.`
  ].join(' ');
}

// CSV-escape a single field. RFC 4180.
function csv(v) {
  if (v == null) return '';
  const s = String(v);
  if (/[",\n\r]/.test(s)) return '"' + s.replace(/"/g, '""') + '"';
  return s;
}

function header() {
  return [
    '# IG DM drafts — National Paper Hangers',
    '# Generated: ' + new Date().toISOString(),
    '# How to use:',
    '#   1. Open IG (web or phone) on Steve\'s personal/business account.',
    '#   2. For each row, navigate to https://instagram.com/<ig_handle>',
    '#   3. Click Message → paste the entire dm_message field → send.',
    '#   4. Log the send in comms_send_audit (channel=\'ig_dm\').',
    '#   5. 5/day cadence max — see outreach/IG_DM_PLAYBOOK.md.',
    '# Rule: NEVER use the IG API to automate sends — Meta TOS forbids it.',
    ''
  ].join('\n');
}

async function main() {
  const params = [MIN];
  let where = `claim_status = 'unclaimed'
               AND ad_signals IS NOT NULL
               AND (ad_signals->>'paid_ads_count')::int >= $1
               AND instagram_handle IS NOT NULL
               AND instagram_handle <> ''
               AND website IS NOT NULL
               AND website <> ''`;
  if (STATE) {
    params.push(STATE);
    where += ` AND state = $${params.length}`;
  }

  let query = `SELECT slug, business_name, city, state, website, instagram_handle, ad_signals
                 FROM installers
                WHERE ${where}
                ORDER BY (ad_signals->>'paid_ads_count')::int DESC, business_name ASC`;
  if (LIMIT > 0) {
    params.push(LIMIT);
    query += ` LIMIT $${params.length}`;
  }

  const rows = await db.many(query, params);

  const lines = [];
  lines.push(header());
  lines.push('business_name,ig_handle,city,state,paid_platforms,dm_message,claim_url');
  for (const r of rows) {
    const plats = paidPlatforms(r.ad_signals);
    const claimUrl = `${PUBLIC_URL}/installer/${r.slug}/claim`;
    const dm = buildDmMessage({
      businessName: r.business_name,
      city: r.city,
      state: r.state,
      paidPlatforms: plats,
      claimUrl
    });
    lines.push([
      csv(r.business_name),
      csv('@' + r.instagram_handle),
      csv(r.city),
      csv(r.state),
      csv(plats.join('|')),
      csv(dm),
      csv(claimUrl)
    ].join(','));
  }

  const output = lines.join('\n') + '\n';

  if (OUT) {
    const abs = path.resolve(OUT);
    fs.writeFileSync(abs, output, 'utf8');
    // Stderr summary so a redirected stdout stays clean if someone passes both.
    process.stderr.write(`[ig-dm-drafts] wrote ${rows.length} drafts → ${abs}\n`);
  } else {
    process.stdout.write(output);
    process.stderr.write(`[ig-dm-drafts] generated ${rows.length} drafts\n`);
  }

  await db.pool.end();
}

main().catch(err => {
  console.error('[ig-dm-drafts] error:', err.message);
  process.exit(1);
});