Build a Weekly Social Media Report That Emails Itself

Nobody reads dashboards.

I've built dashboards with real-time charts, sparklines, color-coded KPIs — the works. Usage logs showed the team checked them twice the first week, then never again.

You know what people actually read? Emails.

So I stopped building dashboards and started building reports that email themselves every Monday at 9am. One email. Key metrics. Week-over-week changes. Done.

Here's how.

What We're Building

A Node.js script that:

1. Fetches this week's social media stats for your accounts
2. Compares them to last week
3. Generates a clean HTML email with the delta
4. Sends it to your team via Resend (or any email provider)
5. Runs automatically every Monday morning via cron

The Stack

• Node.js – runtime
• SociaVault API – fetch profile stats and post performance
• Resend – send HTML emails ($0 for first 3,000/month)
• node-cron – scheduling
• better-sqlite3 – store weekly snapshots for comparison

Setup

mkdir weekly-report && cd weekly-report
npm init -y
npm install axios resend node-cron better-sqlite3 dotenv

# .env
SOCIAVAULT_API_KEY=your_key
RESEND_API_KEY=re_your_key
REPORT_RECIPIENTS=[e-mail],[e-mail]

Step 1: Store Weekly Snapshots

We need last week's numbers to calculate deltas. SQLite handles this perfectly.

// db.js
const Database = require('better-sqlite3');
const db = new Database('./snapshots.db');

db.exec(`
  CREATE TABLE IF NOT EXISTS snapshots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    platform TEXT NOT NULL,
    username TEXT NOT NULL,
    followers INTEGER,
    following INTEGER,
    posts_count INTEGER,
    avg_likes REAL,
    avg_comments REAL,
    engagement_rate REAL,
    snapshot_date DATE DEFAULT (date('now')),
    UNIQUE(platform, username, snapshot_date)
  )
`);

const insertSnapshot = db.prepare(`
  INSERT OR REPLACE INTO snapshots
  (platform, username, followers, following, posts_count, avg_likes, avg_comments, engagement_rate, snapshot_date)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, date('now'))
`);

const getSnapshot = db.prepare(`
  SELECT * FROM snapshots
  WHERE platform = ? AND username = ? AND snapshot_date = date('now', ?)
`);

module.exports = {
  saveSnapshot: (data) => insertSnapshot.run(
    data.platform, data.username, data.followers, data.following,
    data.postsCount, data.avgLikes, data.avgComments, data.engagementRate
  ),
  getLastWeek: (platform, username) => getSnapshot.get(platform, username, '-7 days'),
  getThisWeek: (platform, username) => getSnapshot.get(platform, username, '0 days'),
};

Step 2: Fetch Current Stats

// fetcher.js
const axios = require('axios');

const api = axios.create({
  baseURL: 'https://api.sociavault.com/v1/scrape',
  headers: { 'x-api-key': process.env.SOCIAVAULT_API_KEY },
});

async function getAccountStats(platform, username) {
  const profileEndpoint = platform === 'instagram'
    ? `/instagram/profile?username=${username}`
    : `/tiktok/profile?username=${username}`;

  const postsEndpoint = platform === 'instagram'
    ? `/instagram/posts?username=${username}&limit=12`
    : `/tiktok/profile-videos?username=${username}&limit=12`;

  const [profileRes, postsRes] = await Promise.all([
    api.get(profileEndpoint),
    api.get(postsEndpoint),
  ]);

  const profile = profileRes.data.data || profileRes.data;
  const posts = postsRes.data.data || postsRes.data.posts || [];

  // Calculate averages from recent posts
  const totalLikes = posts.reduce((sum, p) => sum + (p.likesCount || p.diggCount || 0), 0);
  const totalComments = posts.reduce((sum, p) => sum + (p.commentsCount || p.commentCount || 0), 0);
  const avgLikes = posts.length > 0 ? totalLikes / posts.length : 0;
  const avgComments = posts.length > 0 ? totalComments / posts.length : 0;

  const followers = profile.followersCount || profile.followerCount || 0;
  const engagementRate = followers > 0 ? ((avgLikes + avgComments) / followers) * 100 : 0;

  return {
    platform,
    username,
    followers,
    following: profile.followingCount || 0,
    postsCount: profile.postsCount || profile.videoCount || 0,
    avgLikes: Math.round(avgLikes),
    avgComments: Math.round(avgComments),
    engagementRate: parseFloat(engagementRate.toFixed(2)),
  };
}

module.exports = { getAccountStats };

Step 3: Calculate Week-over-Week Changes

// compare.js
function delta(current, previous) {
  if (!previous || previous === 0) return { value: current, change: null, percent: null };

  const change = current - previous;
  const percent = ((change / previous) * 100).toFixed(1);

  return {
    value: current,
    change,
    percent: parseFloat(percent),
    direction: change > 0 ? 'up' : change < 0 ? 'down' : 'flat',
  };
}

function compareSnapshots(current, lastWeek) {
  if (!lastWeek) {
    return {
      followers: { value: current.followers, change: null },
      avgLikes: { value: current.avgLikes, change: null },
      avgComments: { value: current.avgComments, change: null },
      engagementRate: { value: current.engagementRate, change: null },
      isFirstWeek: true,
    };
  }

  return {
    followers: delta(current.followers, lastWeek.followers),
    avgLikes: delta(current.avgLikes, lastWeek.avg_likes),
    avgComments: delta(current.avgComments, lastWeek.avg_comments),
    engagementRate: delta(current.engagementRate, lastWeek.engagement_rate),
    isFirstWeek: false,
  };
}

module.exports = { compareSnapshots };

Step 4: Generate the HTML Email

This is where most automated reports fail. They send ugly plain text. We're sending something that looks like it was designed.

// email-template.js
function arrow(direction) {
  if (direction === 'up') return '↑';
  if (direction === 'down') return '↓';
  return '→';
}

function changeColor(direction) {
  if (direction === 'up') return '#22c55e';
  if (direction === 'down') return '#ef4444';
  return '#94a3b8';
}

function formatChange(metric) {
  if (metric.change === null) return '<span style="color:#94a3b8">First week</span>';

  const color = changeColor(metric.direction);
  const sign = metric.change > 0 ? '+' : '';
  return `<span style="color:${color}">${arrow(metric.direction)} ${sign}${metric.percent}%</span>`;
}

function buildEmailHTML(reports) {
  const date = new Date().toLocaleDateString('en-US', {
    weekday: 'long', month: 'long', day: 'numeric', year: 'numeric',
  });

  const accountRows = reports.map(r => `
    <tr>
      <td style="padding:12px 16px;border-bottom:1px solid #e2e8f0">
        <strong>${r.platform}</strong> @${r.username}
      </td>
      <td style="padding:12px 16px;border-bottom:1px solid #e2e8f0;text-align:right">
        ${r.comparison.followers.value.toLocaleString()} ${formatChange(r.comparison.followers)}
      </td>
      <td style="padding:12px 16px;border-bottom:1px solid #e2e8f0;text-align:right">
        ${r.comparison.engagementRate.value}% ${formatChange(r.comparison.engagementRate)}
      </td>
      <td style="padding:12px 16px;border-bottom:1px solid #e2e8f0;text-align:right">
        ${r.comparison.avgLikes.value.toLocaleString()} ${formatChange(r.comparison.avgLikes)}
      </td>
    </tr>
  `).join('');

  return `
<!DOCTYPE html>
<html>
<head><meta charset="utf-8"></head>
<body style="font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,sans-serif;max-width:600px;margin:0 auto;padding:20px;color:#1e293b">

  <h1 style="font-size:24px;margin-bottom:4px">📊 Weekly Social Report</h1>
  <p style="color:#64748b;margin-top:0">${date}</p>

  <table style="width:100%;border-collapse:collapse;margin:24px 0">
    <thead>
      <tr style="background:#f8fafc">
        <th style="padding:12px 16px;text-align:left;border-bottom:2px solid #e2e8f0">Account</th>
        <th style="padding:12px 16px;text-align:right;border-bottom:2px solid #e2e8f0">Followers</th>
        <th style="padding:12px 16px;text-align:right;border-bottom:2px solid #e2e8f0">Eng. Rate</th>
        <th style="padding:12px 16px;text-align:right;border-bottom:2px solid #e2e8f0">Avg Likes</th>
      </tr>
    </thead>
    <tbody>
      ${accountRows}
    </tbody>
  </table>

  <p style="color:#94a3b8;font-size:12px;margin-top:32px">
    Powered by <a href="https://sociavault.com" style="color:#3b82f6">SociaVault</a> • Data pulled from public APIs
  </p>
</body>
</html>`;
}

module.exports = { buildEmailHTML };

Step 5: Send It

// index.js
require('dotenv').config();
const cron = require('node-cron');
const { Resend } = require('resend');
const { getAccountStats } = require('./fetcher');
const { compareSnapshots } = require('./compare');
const { buildEmailHTML } = require('./email-template');
const db = require('./db');

const resend = new Resend(process.env.RESEND_API_KEY);

// Your accounts to track
const ACCOUNTS = [
  { platform: 'instagram', username: 'your_brand' },
  { platform: 'tiktok', username: 'your_brand' },
];

async function generateAndSend() {
  console.log('Generating weekly report...');

  const reports = [];

  for (const account of ACCOUNTS) {
    const current = await getAccountStats(account.platform, account.username);
    const lastWeek = db.getLastWeek(account.platform, account.username);
    const comparison = compareSnapshots(current, lastWeek);

    // Save this week's snapshot
    db.saveSnapshot(current);

    reports.push({ ...account, current, comparison });
  }

  const html = buildEmailHTML(reports);
  const recipients = process.env.REPORT_RECIPIENTS.split(',').map(e => e.trim());

  await resend.emails.send({
    from: '[e-mail]',
    to: recipients,
    subject: `📊 Weekly Social Report — ${new Date().toLocaleDateString()}`,
    html,
  });

  console.log(`Report sent to ${recipients.length} recipients`);
}

// Every Monday at 9am
cron.schedule('0 9 * * 1', generateAndSend);

// Run manually with: node index.js --now
if (process.argv.includes('--now')) {
  generateAndSend().then(() => process.exit(0));
}

console.log('Weekly report scheduler started. Next run: Monday 9am.');

Test it:

node index.js --now

You should get a clean email with a table showing each account's followers, engagement rate, average likes — all with week-over-week arrows.

What the Email Looks Like

📊 Weekly Social Report
Monday, April 7, 2026

Account              Followers        Eng. Rate      Avg Likes
─────────────────────────────────────────────────────────────
Instagram @brand     124,500 ↑ +2.1%  3.4% ↑ +0.3%  4,230 ↑ +8.2%
TikTok @brand        89,200 ↑ +4.7%   5.1% ↓ -1.2%  12,100 ↓ -3.1%

Green arrows for growth, red for decline. Your CEO reads this in 10 seconds and knows exactly what's happening.

Extending It

• Add top performing post of the week — include a thumbnail and link
• Add competitor comparison — show your numbers next to theirs
• Add monthly roll-up — first Monday of each month includes 4-week trend
• Switch to React Email for more complex layouts if needed

Read the Full Guide

Build a Self-Emailing Social Report → SociaVault Blog

---

Automate your social media analytics with SociaVault — one API for TikTok, Instagram, YouTube, and 10+ platforms. Pull profiles, posts, and engagement data programmatically.

Discussion

How do you handle social media reporting for your team or clients? Still exporting CSVs from native analytics? I'd love to hear what other people have automated.

javascript #automation #email #webdev #productivity