`scripts/lead-list.js`

84 lines
#!/usr/bin/env node
// Lead list — studios running paid ads, ranked by platform count.
// Output: pretty terminal table, or CSV with --csv.
//
// Usage:
//   node scripts/lead-list.js               # all paid-ads studios, pretty print
//   node scripts/lead-list.js --csv         # CSV to stdout
//   node scripts/lead-list.js --min=2       # only studios on 2+ paid platforms
//   node scripts/lead-list.js --state=CA    # filter by state

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

const CSV = process.argv.includes('--csv');
const MIN = parseInt((process.argv.find(a => a.startsWith('--min=')) || '--min=1').split('=')[1], 10);
const STATE = (process.argv.find(a => a.startsWith('--state=')) || '').split('=')[1] || null;

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`;
  if (STATE) {
    params.push(STATE.toUpperCase());
    where += ` AND state = $${params.length}`;
  }
  const rows = await db.many(
    `SELECT business_name, city, state, website, instagram_handle, ad_signals
       FROM installers
      WHERE ${where}
      ORDER BY (ad_signals->>'paid_ads_count')::int DESC, business_name`,
    params
  );

  if (CSV) {
    console.log('business_name,city,state,website,instagram,paid_count,platforms');
    for (const r of rows) {
      const platforms = paidPlatforms(r.ad_signals).join('|');
      console.log([
        csv(r.business_name), csv(r.city), csv(r.state), csv(r.website),
        csv(r.instagram_handle ? '@' + r.instagram_handle : ''),
        r.ad_signals.paid_ads_count, csv(platforms)
      ].join(','));
    }
  } else {
    console.log(`\n=== Lead list — ${rows.length} unclaimed studios running paid ads ===\n`);
    for (const r of rows) {
      const platforms = paidPlatforms(r.ad_signals);
      const tag = '💰'.repeat(Math.min(r.ad_signals.paid_ads_count, 5));
      console.log(`${tag} ${r.business_name}`);
      console.log(`   ${r.city || '?'}, ${r.state || '?'} · ${r.website}`);
      if (r.instagram_handle) console.log(`   IG @${r.instagram_handle}`);
      console.log(`   Platforms: ${platforms.join(', ')}`);
      console.log('');
    }
    if (rows.length === 0) console.log('  (none yet — run scripts/scan-ad-signals.js --commit first)');
  }

  await db.pool.end();
}

function paidPlatforms(s) {
  if (!s) return [];
  const map = {
    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'
  };
  return Object.entries(map).filter(([k]) => s[k] === true).map(([, v]) => v);
}

function csv(v) {
  if (v == null) return '';
  const s = String(v);
  if (/[",\n]/.test(s)) return '"' + s.replace(/"/g, '""') + '"';
  return s;
}

main().catch(e => { console.error(e); process.exit(1); });