Social Media Competitor Analysis: Reverse-Engineer Their Strategy (Step-by-Step)

Your competitor launched 3 months ago. They're already at 50K followers.

You've been posting for a year. You have 5K.

What are they doing that you're not?

You could guess. You could try to "be authentic" and "stay in your lane." You could hope the algorithm finally notices you.

Or you could look at exactly what they're doing and reverse-engineer it.

Every viral post they create. Every hashtag they use. Every posting time that works. Every content format that drives engagement.

It's all public data. They can't hide it. You just need to know where to look and how to analyze it.

This guide shows you how to build a complete competitor analysis system. You'll extract their social data across all platforms, find patterns, identify gaps, and build a strategy that works.

No guessing. No hoping. Just data.

Why Competitor Analysis Actually Matters

Before we dive into the how, let's be clear about the why.

The Reality of Social Media in 2025

Here's what doesn't work anymore:

• Posting randomly and hoping for the best
• Copying viral content from other industries
• Following generic "social media tips"
• Guessing what your audience wants

Here's what works:

• Seeing what resonates with YOUR audience (by watching competitors in your niche)
• Understanding content patterns that drive engagement
• Identifying gaps your competitors aren't filling
• Timing your content based on data, not intuition

The Business Case

Sprout Social's research (2024):

• 68% of marketers say competitive analysis is crucial to their strategy
• Brands that analyze competitors grow 3.2x faster
• 77% of successful campaigns were informed by competitive insights

What this means in practice:

Coffee brand example:

• Competitor A posts 3 TikToks/day, averages 50K views
• Competitor B posts 1 TikTok/day, averages 200K views
• You post 5 TikToks/day, averaging 5K views

The data tells you: It's not about quantity. Competitor B is doing something specific that resonates. What is it?

Competitor analysis answers that question.

What You Can Learn

Content Strategy:

• What topics get the most engagement?
• What formats work best (Reels, carousels, stories)?
• What length performs better?

Audience Insights:

• Who's engaging with competitors?
• What questions are people asking in comments?
• What pain points keep coming up?

Timing & Frequency:

• When do they post for maximum reach?
• How often do they post on each platform?
• What's their response time to comments?

Channel Strategy:

• Which platforms do they prioritize?
• Where do they get the most engagement?
• Where are they absent (opportunity gaps)?

Content Gaps:

• What topics do they avoid?
• What questions go unanswered?
• What audience segments do they ignore?

This isn't about copying. It's about understanding the landscape and finding your edge.

Identifying Your Real Competitors

First mistake: assuming you know who your competitors are.

You don't want to analyze random accounts in your industry. You want to analyze accounts competing for YOUR audience's attention.

Three Types of Competitors

1. Direct Competitors

Same product/service, same audience.

If you sell email marketing software, your direct competitors are other email marketing platforms.

2. Content Competitors

Different product, same audience.

If you're a fitness coach, your content competitors include:

• Other fitness creators
• Health food brands
• Fitness apps
• Wellness influencers

They're all competing for the same eyeballs.

3. Aspirational Competitors

Bigger brands you want to emulate.

These are the accounts crushing it in your niche. You're not competing with them yet, but you will be.

How to Find Your Competitors

Method 1: Platform Search

Search your main keywords on each platform:

// Search TikTok for your niche
const tiktokSearch = await searchTikTok('fitness coach');

// Instagram hashtags
const instagramSearch = await searchInstagram('#fitnessmotivation');

// YouTube search
const youtubeSearch = await searchYouTube('workout routines');

Look for accounts with:

• Similar follower counts (you can actually compete)
• High engagement (they're doing something right)
• Regular posting (they're serious)

Method 2: Audience Overlap

Look at who's commenting on your posts. Check their following list. You'll find competitors there.

Method 3: Hashtag Mining

Find accounts using the same hashtags as you:

const competitors = [];
const hashtags = ['#yourhashtag1', '#yourhashtag2'];

for (const hashtag of hashtags) {
  const posts = await searchByHashtag(hashtag);
  
  // Find accounts that frequently use this hashtag
  const accounts = posts
    .map(p => p.author)
    .filter(a => a.followers > 5000 && a.followers < 100000);
  
  competitors.push(...accounts);
}

// Remove duplicates, sort by frequency
const topCompetitors = [...new Set(competitors)]
  .reduce((acc, account) => {
    acc[account] = (acc[account] || 0) + 1;
    return acc;
  }, {});

Method 4: "Customers Also Viewed"

Check competitor followers. Who else are they following in your space?

Your Competitive Set

You don't need 50 competitors. Pick 5-10:

• 2-3 direct competitors (same size, same niche)
• 2-3 content competitors (different angle, same audience)
• 1-2 aspirational brands (where you want to be)

This gives you a manageable analysis scope.

Building Your Competitor Data Collection System

Now let's extract their data systematically.

Step 1: Profile Analysis

Start with the basics—who are they?

const axios = require('axios');

const SOCIAVAULT_API_KEY = process.env.SOCIAVAULT_API_KEY;
const BASE_URL = 'https://api.sociavault.com';

class CompetitorAnalyzer {
  constructor() {
    this.competitors = [];
  }

  // Collect profile data across platforms
  async analyzeCompetitor(username, platforms = ['tiktok', 'instagram', 'youtube', 'twitter']) {
    const profile = {
      username,
      platforms: {}
    };

    for (const platform of platforms) {
      try {
        profile.platforms[platform] = await this.getProfileData(username, platform);
        
        // Rate limiting
        await new Promise(resolve => setTimeout(resolve, 2000));
      } catch (error) {
        console.error(`Error fetching ${platform} for @${username}:`, error.message);
      }
    }

    return profile;
  }

  async getProfileData(username, platform) {
    const endpoints = {
      tiktok: `/api/scrape/tiktok/profile?handle=${username}`,
      instagram: `/api/scrape/instagram/profile?handle=${username}`,
      youtube: `/api/scrape/youtube/channel?handle=${username}`,
      twitter: `/api/scrape/twitter/profile?username=${username}`
    };

    const response = await axios.get(`${BASE_URL}${endpoints[platform]}`, {
      headers: { 'X-API-Key': SOCIAVAULT_API_KEY }
    });

    const data = response.data;

    // Normalize data across platforms
    return {
      username: username,
      followers: this.extractFollowerCount(data, platform),
      bio: this.extractBio(data, platform),
      verified: this.extractVerified(data, platform),
      links: this.extractLinks(data, platform),
      avatar: this.extractAvatar(data, platform)
    };
  }

  extractFollowerCount(data, platform) {
    const mapping = {
      tiktok: data.followerCount || data.stats?.followerCount,
      instagram: data.follower_count || data.edge_followed_by?.count,
      youtube: data.subscriberCount || data.statistics?.subscriberCount,
      twitter: data.followersCount || data.public_metrics?.followers_count
    };
    return mapping[platform] || 0;
  }

  extractBio(data, platform) {
    const mapping = {
      tiktok: data.signature,
      instagram: data.biography,
      youtube: data.description,
      twitter: data.description
    };
    return mapping[platform] || '';
  }

  extractVerified(data, platform) {
    const mapping = {
      tiktok: data.verified,
      instagram: data.is_verified,
      youtube: data.customUrl?.includes('verified'),
      twitter: data.verified
    };
    return mapping[platform] || false;
  }

  extractLinks(data, platform) {
    const mapping = {
      tiktok: data.bioLink?.link,
      instagram: data.external_url,
      youtube: data.customUrl,
      twitter: data.entities?.url?.urls?.[0]?.expanded_url
    };
    return mapping[platform] || null;
  }

  extractAvatar(data, platform) {
    const mapping = {
      tiktok: data.avatarLarger || data.avatarMedium,
      instagram: data.profile_pic_url_hd || data.profile_pic_url,
      youtube: data.thumbnails?.high?.url,
      twitter: data.profile_image_url_https
    };
    return mapping[platform] || null;
  }
}

module.exports = CompetitorAnalyzer;

Usage:

const analyzer = new CompetitorAnalyzer();

const competitors = [
  'competitor1',
  'competitor2',
  'competitor3'
];

for (const username of competitors) {
  const profile = await analyzer.analyzeCompetitor(username);
  console.log(`\n=== @${username} ===`);
  console.log('TikTok:', profile.platforms.tiktok?.followers, 'followers');
  console.log('Instagram:', profile.platforms.instagram?.followers, 'followers');
  console.log('YouTube:', profile.platforms.youtube?.followers, 'subscribers');
}

Step 2: Content Collection

Extract their recent posts across all platforms:

class ContentCollector {
  async collectAllContent(username, platforms = ['tiktok', 'instagram', 'youtube']) {
    const allContent = [];

    for (const platform of platforms) {
      console.log(`Collecting ${platform} content for @${username}...`);
      
      const content = await this.getContent(username, platform);
      allContent.push(...content);
      
      await new Promise(resolve => setTimeout(resolve, 2000));
    }

    return allContent;
  }

  async getContent(username, platform) {
    const methods = {
      tiktok: () => this.getTikTokVideos(username),
      instagram: () => this.getInstagramPosts(username),
      youtube: () => this.getYouTubeVideos(username),
      twitter: () => this.getTwitterTweets(username)
    };

    return await methods[platform]();
  }

  async getTikTokVideos(username) {
    const response = await axios.get(`${BASE_URL}/api/scrape/tiktok/videos`, {
      params: { handle: username },
      headers: { 'X-API-Key': SOCIAVAULT_API_KEY }
    });

    return response.data.aweme_list.map(video => ({
      platform: 'tiktok',
      id: video.aweme_id,
      url: `https://www.tiktok.com/@${username}/video/${video.aweme_id}`,
      caption: video.desc,
      views: video.statistics.play_count,
      likes: video.statistics.digg_count,
      comments: video.statistics.comment_count,
      shares: video.statistics.share_count,
      timestamp: new Date(video.create_time * 1000),
      duration: video.video.duration,
      hashtags: (video.desc.match(/#\w+/g) || []),
      music: video.music?.title
    }));
  }

  async getInstagramPosts(username) {
    const response = await axios.get(`${BASE_URL}/api/scrape/instagram/posts`, {
      params: { handle: username },
      headers: { 'X-API-Key': SOCIAVAULT_API_KEY }
    });

    return response.data.items.map(item => {
      const post = item.media || item;
      return {
        platform: 'instagram',
        id: post.id,
        code: post.code,
        url: `https://www.instagram.com/p/${post.code}/`,
        caption: post.caption?.text || '',
        likes: post.like_count,
        comments: post.comment_count,
        timestamp: new Date(post.taken_at * 1000),
        type: post.media_type === 8 ? 'carousel' : post.media_type === 2 ? 'video' : 'photo',
        hashtags: ((post.caption?.text || '').match(/#\w+/g) || [])
      };
    });
  }

  async getYouTubeVideos(username) {
    const response = await axios.get(`${BASE_URL}/api/scrape/youtube/channel-videos`, {
      params: { handle: username },
      headers: { 'X-API-Key': SOCIAVAULT_API_KEY }
    });

    return response.data.items.map(video => ({
      platform: 'youtube',
      id: video.videoId,
      url: `https://www.youtube.com/watch?v=${video.videoId}`,
      title: video.title,
      description: video.description,
      views: video.viewCount,
      likes: video.likeCount,
      comments: video.commentCount,
      timestamp: new Date(video.publishedAt),
      duration: video.duration,
      hashtags: ((video.description || '').match(/#\w+/g) || [])
    }));
  }

  async getTwitterTweets(username) {
    const response = await axios.get(`${BASE_URL}/api/scrape/twitter/user-tweets`, {
      params: { username: username },
      headers: { 'X-API-Key': SOCIAVAULT_API_KEY }
    });

    return response.data.tweets.map(tweet => ({
      platform: 'twitter',
      id: tweet.id,
      url: `https://twitter.com/${username}/status/${tweet.id}`,
      text: tweet.text,
      likes: tweet.likeCount,
      retweets: tweet.retweetCount,
      replies: tweet.replyCount,
      views: tweet.viewCount,
      timestamp: new Date(tweet.createdAt),
      hashtags: (tweet.text.match(/#\w+/g) || [])
    }));
  }
}

module.exports = ContentCollector;

Step 3: Database Storage

Store competitor data for historical analysis:

-- Competitors table
CREATE TABLE competitors (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  platform VARCHAR(50) NOT NULL,
  followers INTEGER,
  bio TEXT,
  verified BOOLEAN DEFAULT FALSE,
  external_link TEXT,
  last_updated TIMESTAMP DEFAULT NOW(),
  UNIQUE(username, platform)
);

-- Content table
CREATE TABLE competitor_content (
  id SERIAL PRIMARY KEY,
  competitor_id INTEGER REFERENCES competitors(id),
  platform VARCHAR(50) NOT NULL,
  external_id VARCHAR(255) NOT NULL,
  url TEXT,
  caption TEXT,
  views INTEGER DEFAULT 0,
  likes INTEGER DEFAULT 0,
  comments INTEGER DEFAULT 0,
  shares INTEGER DEFAULT 0,
  published_at TIMESTAMP,
  collected_at TIMESTAMP DEFAULT NOW(),
  content_type VARCHAR(50),
  duration INTEGER,
  hashtags TEXT[],
  UNIQUE(platform, external_id)
);

-- Analytics snapshots
CREATE TABLE competitor_snapshots (
  id SERIAL PRIMARY KEY,
  competitor_id INTEGER REFERENCES competitors(id),
  followers INTEGER,
  avg_views INTEGER,
  avg_engagement DECIMAL(10,2),
  posting_frequency DECIMAL(5,2),
  snapshot_date DATE DEFAULT CURRENT_DATE
);

CREATE INDEX idx_competitor_content_competitor ON competitor_content(competitor_id);
CREATE INDEX idx_competitor_content_published ON competitor_content(published_at);
CREATE INDEX idx_competitor_content_platform ON competitor_content(platform);

Step 4: Automated Collection

Set up scheduled extraction:

const cron = require('node-cron');
const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL
});

async function runCompetitorAnalysis() {
  console.log('Starting competitor analysis:', new Date().toISOString());

  // Get tracked competitors
  const { rows: competitors } = await pool.query(
    'SELECT DISTINCT username, platform FROM competitors WHERE active = true'
  );

  const collector = new ContentCollector();

  for (const competitor of competitors) {
    try {
      console.log(`Analyzing @${competitor.username} on ${competitor.platform}...`);

      // Get latest content
      const content = await collector.getContent(competitor.username, competitor.platform);

      // Store in database
      for (const item of content) {
        await pool.query(`
          INSERT INTO competitor_content (
            competitor_id, platform, external_id, url, caption,
            views, likes, comments, shares, published_at, content_type, hashtags
          )
          SELECT id, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12
          FROM competitors
          WHERE username = $1 AND platform = $2
          ON CONFLICT (platform, external_id) DO UPDATE SET
            views = EXCLUDED.views,
            likes = EXCLUDED.likes,
            comments = EXCLUDED.comments,
            shares = EXCLUDED.shares
        `, [
          competitor.username,
          item.platform,
          item.id,
          item.url,
          item.caption || item.text || item.title,
          item.views || 0,
          item.likes,
          item.comments || item.replies || 0,
          item.shares || item.retweets || 0,
          item.timestamp,
          item.type || 'post',
          item.hashtags
        ]);
      }

      // Rate limiting
      await new Promise(resolve => setTimeout(resolve, 3000));

    } catch (error) {
      console.error(`Error analyzing ${competitor.username}:`, error.message);
    }
  }

  console.log('Competitor analysis complete');
}

// Run every 6 hours
cron.schedule('0 */6 * * *', runCompetitorAnalysis);

// Run immediately on start
runCompetitorAnalysis();

Analyzing the Data

Now the fun part—finding insights.

Content Performance Analysis

What content performs best?

class CompetitorInsights {
  // Top performing content by platform
  static async getTopContent(username, platform, limit = 10) {
    const result = await pool.query(`
      SELECT 
        caption,
        views,
        likes,
        comments,
        shares,
        url,
        published_at,
        hashtags,
        (likes + comments * 2 + COALESCE(shares, 0) * 3)::float / NULLIF(views, 0) * 100 as engagement_rate
      FROM competitor_content cc
      JOIN competitors c ON c.id = cc.competitor_id
      WHERE c.username = $1 AND cc.platform = $2
      ORDER BY views DESC
      LIMIT $3
    `, [username, platform, limit]);

    return result.rows;
  }

  // Content type breakdown
  static async analyzeContentTypes(username) {
    const result = await pool.query(`
      SELECT 
        platform,
        content_type,
        COUNT(*) as count,
        AVG(views) as avg_views,
        AVG(likes) as avg_likes,
        AVG(comments) as avg_comments,
        AVG((likes + comments * 2)::float / NULLIF(views, 0) * 100) as avg_engagement_rate
      FROM competitor_content cc
      JOIN competitors c ON c.id = cc.competitor_id
      WHERE c.username = $1
        AND published_at > NOW() - INTERVAL '30 days'
      GROUP BY platform, content_type
      ORDER BY avg_engagement_rate DESC
    `, [username]);

    return result.rows;
  }

  // Hashtag performance
  static async analyzeHashtags(username) {
    const result = await pool.query(`
      SELECT 
        UNNEST(hashtags) as hashtag,
        COUNT(*) as usage_count,
        AVG(views) as avg_views,
        AVG(likes) as avg_likes,
        AVG((likes + comments)::float / NULLIF(views, 0) * 100) as avg_engagement_rate
      FROM competitor_content cc
      JOIN competitors c ON c.id = cc.competitor_id
      WHERE c.username = $1
        AND published_at > NOW() - INTERVAL '30 days'
        AND hashtags IS NOT NULL
      GROUP BY hashtag
      HAVING COUNT(*) >= 3
      ORDER BY avg_engagement_rate DESC
      LIMIT 20
    `, [username]);

    return result.rows;
  }

  // Posting time analysis
  static async analyzePostingTimes(username) {
    const result = await pool.query(`
      SELECT 
        EXTRACT(HOUR FROM published_at) as hour,
        EXTRACT(DOW FROM published_at) as day_of_week,
        platform,
        COUNT(*) as post_count,
        AVG(views) as avg_views,
        AVG((likes + comments)::float / NULLIF(views, 0) * 100) as avg_engagement_rate
      FROM competitor_content cc
      JOIN competitors c ON c.id = cc.competitor_id
      WHERE c.username = $1
        AND published_at > NOW() - INTERVAL '30 days'
      GROUP BY EXTRACT(HOUR FROM published_at), EXTRACT(DOW FROM published_at), platform
      ORDER BY avg_engagement_rate DESC
    `, [username]);

    return result.rows;
  }

  // Posting frequency
  static async analyzePostingFrequency(username) {
    const result = await pool.query(`
      SELECT 
        platform,
        DATE(published_at) as date,
        COUNT(*) as posts_per_day
      FROM competitor_content cc
      JOIN competitors c ON c.id = cc.competitor_id
      WHERE c.username = $1
        AND published_at > NOW() - INTERVAL '30 days'
      GROUP BY platform, DATE(published_at)
      ORDER BY date DESC
    `, [username]);

    // Calculate averages
    const frequencies = {};
    result.rows.forEach(row => {
      if (!frequencies[row.platform]) {
        frequencies[row.platform] = [];
      }
      frequencies[row.platform].push(row.posts_per_day);
    });

    return Object.entries(frequencies).map(([platform, counts]) => ({
      platform,
      avg_posts_per_day: (counts.reduce((a, b) => a + b, 0) / counts.length).toFixed(2),
      max_posts_per_day: Math.max(...counts),
      min_posts_per_day: Math.min(...counts)
    }));
  }

  // Growth trends
  static async analyzeGrowthTrends(username) {
    const result = await pool.query(`
      SELECT 
        snapshot_date,
        followers,
        avg_views,
        avg_engagement,
        posting_frequency
      FROM competitor_snapshots cs
      JOIN competitors c ON c.id = cs.competitor_id
      WHERE c.username = $1
      ORDER BY snapshot_date DESC
      LIMIT 30
    `, [username]);

    return result.rows;
  }
}

module.exports = CompetitorInsights;

Comparative Analysis

How do they stack up against each other?

async function compareCompetitors(usernames) {
  const comparison = await pool.query(`
    SELECT 
      c.username,
      c.platform,
      c.followers,
      COUNT(cc.id) as total_posts,
      AVG(cc.views) as avg_views,
      AVG(cc.likes) as avg_likes,
      AVG(cc.comments) as avg_comments,
      AVG((cc.likes + cc.comments)::float / NULLIF(cc.views, 0) * 100) as avg_engagement_rate,
      MAX(cc.views) as best_performing_views
    FROM competitors c
    LEFT JOIN competitor_content cc ON cc.competitor_id = c.id
    WHERE c.username = ANY($1)
      AND cc.published_at > NOW() - INTERVAL '30 days'
    GROUP BY c.username, c.platform, c.followers
    ORDER BY c.platform, avg_engagement_rate DESC
  `, [usernames]);

  console.log('\n=== Competitor Comparison (Last 30 Days) ===\n');
  
  const byPlatform = comparison.rows.reduce((acc, row) => {
    if (!acc[row.platform]) acc[row.platform] = [];
    acc[row.platform].push(row);
    return acc;
  }, {});

  Object.entries(byPlatform).forEach(([platform, competitors]) => {
    console.log(`\n${platform.toUpperCase()}:`);
    console.log('Username | Followers | Posts | Avg Views | Engagement Rate');
    console.log('---------|-----------|-------|-----------|----------------');
    
    competitors.forEach(comp => {
      console.log(
        `${comp.username.padEnd(20)} | ${comp.followers.toLocaleString().padEnd(9)} | ` +
        `${comp.total_posts.toString().padEnd(5)} | ${Math.round(comp.avg_views).toLocaleString().padEnd(9)} | ` +
        `${parseFloat(comp.avg_engagement_rate).toFixed(2)}%`
      );
    });
  });

  return byPlatform;
}

// Usage
const competitors = ['competitor1', 'competitor2', 'competitor3', 'yourBrand'];
const comparison = await compareCompetitors(competitors);

Real-World Use Cases

Let's see strategic applications.

Use Case 1: Content Gap Analysis

The Problem: What topics are your competitors NOT covering?

The Solution:

async function findContentGaps(yourUsername, competitorUsernames) {
  // Get your topics
  const yourContent = await pool.query(`
    SELECT caption, hashtags
    FROM competitor_content cc
    JOIN competitors c ON c.id = cc.competitor_id
    WHERE c.username = $1
      AND published_at > NOW() - INTERVAL '90 days'
  `, [yourUsername]);

  // Get competitor topics
  const competitorContent = await pool.query(`
    SELECT caption, hashtags
    FROM competitor_content cc
    JOIN competitors c ON c.id = cc.competitor_id
    WHERE c.username = ANY($1)
      AND published_at > NOW() - INTERVAL '90 days'
  `, [competitorUsernames]);

  // Extract topics/keywords (simplified - you'd want NLP here)
  const extractKeywords = (text) => {
    return (text || '')
      .toLowerCase()
      .split(/\s+/)
      .filter(word => word.length > 5)
      .filter(word => !['about', 'their', 'which', 'these'].includes(word));
  };

  const yourTopics = new Set();
  yourContent.rows.forEach(row => {
    extractKeywords(row.caption).forEach(topic => yourTopics.add(topic));
  });

  const competitorTopics = {};
  competitorContent.rows.forEach(row => {
    extractKeywords(row.caption).forEach(topic => {
      competitorTopics[topic] = (competitorTopics[topic] || 0) + 1;
    });
  });

  // Find gaps (topics competitors cover but you don't)
  const gaps = Object.entries(competitorTopics)
    .filter(([topic, count]) => !yourTopics.has(topic) && count >= 3)
    .sort((a, b) => b[1] - a[1])
    .slice(0, 20);

  console.log('\n=== Content Gap Analysis ===\n');
  console.log('Topics your competitors are covering (but you're not):\n');
  
  gaps.forEach(([topic, count]) => {
    console.log(`- ${topic} (${count} times across competitors)`);
  });

  return gaps;
}

// Usage
const gaps = await findContentGaps('yourBrand', ['comp1', 'comp2', 'comp3']);

Result: Discover untapped topics that resonate with your audience.

Use Case 2: Optimal Posting Strategy

The Problem: When should you post for maximum reach?

The Solution:

async function findOptimalPostingTimes(competitorUsernames) {
  const timingData = await pool.query(`
    SELECT 
      EXTRACT(HOUR FROM published_at) as hour,
      EXTRACT(DOW FROM published_at) as day_of_week,
      platform,
      AVG(views) as avg_views,
      AVG((likes + comments)::float / NULLIF(views, 0) * 100) as avg_engagement_rate,
      COUNT(*) as sample_size
    FROM competitor_content cc
    JOIN competitors c ON c.id = cc.competitor_id
    WHERE c.username = ANY($1)
      AND published_at > NOW() - INTERVAL '60 days'
    GROUP BY EXTRACT(HOUR FROM published_at), EXTRACT(DOW FROM published_at), platform
    HAVING COUNT(*) >= 5
    ORDER BY platform, avg_engagement_rate DESC
  `, [competitorUsernames]);

  const days = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'];

  console.log('\n=== Optimal Posting Times ===\n');

  const byPlatform = timingData.rows.reduce((acc, row) => {
    if (!acc[row.platform]) acc[row.platform] = [];
    acc[row.platform].push(row);
    return acc;
  }, {});

  Object.entries(byPlatform).forEach(([platform, times]) => {
    console.log(`\n${platform.toUpperCase()} - Top 5 Posting Times:`);
    
    times.slice(0, 5).forEach((time, i) => {
      console.log(
        `${i + 1}. ${days[time.day_of_week]} at ${time.hour}:00 ` +
        `(${parseFloat(time.avg_engagement_rate).toFixed(2)}% engagement, ${time.sample_size} posts)`
      );
    });
  });

  return byPlatform;
}

// Usage
const optimalTimes = await findOptimalPostingTimes(['comp1', 'comp2', 'comp3']);

Result: Data-driven posting schedule that maximizes engagement.

Use Case 3: Hashtag Strategy

The Problem: Which hashtags actually drive reach?

The Solution:

async function analyzeHashtagStrategy(competitorUsernames) {
  // Find top-performing hashtags
  const hashtagData = await pool.query(`
    SELECT 
      UNNEST(hashtags) as hashtag,
      COUNT(*) as usage_count,
      AVG(views) as avg_views,
      AVG(likes) as avg_likes,
      AVG((likes + comments)::float / NULLIF(views, 0) * 100) as avg_engagement_rate,
      COUNT(DISTINCT cc.competitor_id) as used_by_competitors
    FROM competitor_content cc
    JOIN competitors c ON c.id = cc.competitor_id
    WHERE c.username = ANY($1)
      AND published_at > NOW() - INTERVAL '30 days'
      AND hashtags IS NOT NULL
    GROUP BY hashtag
    HAVING COUNT(*) >= 5
    ORDER BY avg_engagement_rate DESC
    LIMIT 30
  `, [competitorUsernames]);

  console.log('\n=== Hashtag Strategy Analysis ===\n');
  console.log('Top Performing Hashtags:\n');
  console.log('Hashtag | Usage | Avg Views | Engagement | Competitors');
  console.log('--------|-------|-----------|------------|------------');

  hashtagData.rows.forEach(tag => {
    console.log(
      `${tag.hashtag.padEnd(25)} | ${tag.usage_count.toString().padEnd(5)} | ` +
      `${Math.round(tag.avg_views).toLocaleString().padEnd(9)} | ` +
      `${parseFloat(tag.avg_engagement_rate).toFixed(2)}% | ${tag.used_by_competitors}`
    );
  });

  // Categorize hashtags
  const categories = {
    highVolume: hashtagData.rows.filter(t => t.avg_views > 50000),
    highEngagement: hashtagData.rows.filter(t => t.avg_engagement_rate > 5),
    trending: hashtagData.rows.filter(t => t.usage_count > 10)
  };

  console.log('\n=== Recommended Hashtag Mix ===\n');
  console.log('High Volume (reach):', categories.highVolume.slice(0, 3).map(t => t.hashtag).join(', '));
  console.log('High Engagement (quality):', categories.highEngagement.slice(0, 3).map(t => t.hashtag).join(', '));
  console.log('Trending (momentum):', categories.trending.slice(0, 3).map(t => t.hashtag).join(', '));

  return categories;
}

// Usage
const hashtagStrategy = await analyzeHashtagStrategy(['comp1', 'comp2', 'comp3']);

Result: Proven hashtag combinations that balance reach and engagement.

Use Case 4: Content Format Analysis

The Problem: Should you focus on Reels, carousels, or single images?

The Solution:

async function analyzeContentFormats(competitorUsernames) {
  const formatData = await pool.query(`
    SELECT 
      platform,
      content_type,
      COUNT(*) as post_count,
      AVG(views) as avg_views,
      AVG(likes) as avg_likes,
      AVG(comments) as avg_comments,
      AVG((likes + comments * 2)::float / NULLIF(views, 0) * 100) as avg_engagement_rate,
      MAX(views) as max_views
    FROM competitor_content cc
    JOIN competitors c ON c.id = cc.competitor_id
    WHERE c.username = ANY($1)
      AND published_at > NOW() - INTERVAL '30 days'
    GROUP BY platform, content_type
    ORDER BY platform, avg_engagement_rate DESC
  `, [competitorUsernames]);

  console.log('\n=== Content Format Performance ===\n');

  const byPlatform = formatData.rows.reduce((acc, row) => {
    if (!acc[row.platform]) acc[row.platform] = [];
    acc[row.platform].push(row);
    return acc;
  }, {});

  Object.entries(byPlatform).forEach(([platform, formats]) => {
    console.log(`\n${platform.toUpperCase()}:`);
    console.log('Format | Posts | Avg Views | Engagement | Best Performing');
    console.log('-------|-------|-----------|------------|----------------');

    formats.forEach(format => {
      console.log(
        `${(format.content_type || 'post').padEnd(15)} | ${format.post_count.toString().padEnd(5)} | ` +
        `${Math.round(format.avg_views).toLocaleString().padEnd(9)} | ` +
        `${parseFloat(format.avg_engagement_rate).toFixed(2)}% | ${format.max_views.toLocaleString()}`
      );
    });

    // Recommendation
    const topFormat = formats[0];
    console.log(`\n→ Focus on ${topFormat.content_type} (${parseFloat(topFormat.avg_engagement_rate).toFixed(2)}% engagement)`);
  });

  return byPlatform;
}

// Usage
const formatAnalysis = await analyzeContentFormats(['comp1', 'comp2', 'comp3']);

Result: Know exactly which content formats to prioritize.

Use Case 5: Automated Competitive Dashboard

The Problem: You need weekly updates on competitor activity.

The Solution:

async function generateCompetitiveDashboard(competitorUsernames) {
  console.log('\n═══════════════════════════════════════════════');
  console.log('      COMPETITIVE INTELLIGENCE DASHBOARD');
  console.log('═══════════════════════════════════════════════\n');

  // 1. Overview metrics
  const overview = await compareCompetitors(competitorUsernames);

  // 2. Top performing content (last 7 days)
  console.log('\n\n🏆 TOP PERFORMING CONTENT (Last 7 Days)\n');
  for (const username of competitorUsernames) {
    const topContent = await pool.query(`
      SELECT caption, views, likes, url, platform
      FROM competitor_content cc
      JOIN competitors c ON c.id = cc.competitor_id
      WHERE c.username = $1
        AND published_at > NOW() - INTERVAL '7 days'
      ORDER BY views DESC
      LIMIT 3
    `, [username]);

    console.log(`\n@${username}:`);
    topContent.rows.forEach((item, i) => {
      console.log(`${i + 1}. [${item.platform}] ${item.views.toLocaleString()} views, ${item.likes.toLocaleString()} likes`);
      console.log(`   ${(item.caption || '').substring(0, 60)}...`);
      console.log(`   ${item.url}`);
    });
  }

  // 3. Posting frequency trends
  console.log('\n\n📊 POSTING FREQUENCY (Last 7 Days)\n');
  const frequency = await pool.query(`
    SELECT 
      c.username,
      platform,
      COUNT(*) as posts_this_week
    FROM competitor_content cc
    JOIN competitors c ON c.id = cc.competitor_id
    WHERE c.username = ANY($1)
      AND published_at > NOW() - INTERVAL '7 days'
    GROUP BY c.username, platform
    ORDER BY posts_this_week DESC
  `, [competitorUsernames]);

  frequency.rows.forEach(row => {
    console.log(`@${row.username} [${row.platform}]: ${row.posts_this_week} posts`);
  });

  // 4. Trending hashtags
  console.log('\n\n🔥 TRENDING HASHTAGS\n');
  const trending = await pool.query(`
    SELECT 
      UNNEST(hashtags) as hashtag,
      COUNT(*) as usage
    FROM competitor_content cc
    JOIN competitors c ON c.id = cc.competitor_id
    WHERE c.username = ANY($1)
      AND published_at > NOW() - INTERVAL '7 days'
      AND hashtags IS NOT NULL
    GROUP BY hashtag
    ORDER BY usage DESC
    LIMIT 10
  `, [competitorUsernames]);

  trending.rows.forEach((tag, i) => {
    console.log(`${i + 1}. ${tag.hashtag} (used ${tag.usage} times)`);
  });

  // 5. Growth changes
  console.log('\n\n📈 FOLLOWER GROWTH (Last 7 Days)\n');
  const growth = await pool.query(`
    SELECT 
      c.username,
      c.platform,
      c.followers as current_followers,
      (SELECT followers FROM competitor_snapshots cs 
       WHERE cs.competitor_id = c.id 
       ORDER BY snapshot_date DESC OFFSET 7 LIMIT 1) as followers_7_days_ago
    FROM competitors c
    WHERE c.username = ANY($1)
  `, [competitorUsernames]);

  growth.rows.forEach(row => {
    if (row.followers_7_days_ago) {
      const change = row.current_followers - row.followers_7_days_ago;
      const changePercent = (change / row.followers_7_days_ago * 100).toFixed(2);
      const arrow = change > 0 ? '↑' : change < 0 ? '↓' : '→';
      
      console.log(
        `@${row.username} [${row.platform}]: ${row.current_followers.toLocaleString()} ` +
        `${arrow} ${Math.abs(change).toLocaleString()} (${changePercent}%)`
      );
    }
  });

  console.log('\n═══════════════════════════════════════════════\n');
}

// Schedule weekly reports
cron.schedule('0 9 * * 1', async () => {
  const competitors = ['comp1', 'comp2', 'comp3'];
  await generateCompetitiveDashboard(competitors);
  
  // Could also email this report or send to Slack
});

Result: Weekly intelligence reports delivered automatically.

Turning Insights Into Action

Data without action is useless. Here's your framework:

1. Content Strategy

Based on your analysis:

IF competitor Reels average 100K+ views
AND they post 2x/day at 7pm and 10pm
AND use 5-7 hashtags
THEN test posting Reels at those times with similar hashtag count

Create an action plan:

const actionPlan = {
  contentTypes: 'Focus on Reels (3.2x more engagement than photos)',
  postingSchedule: 'Post at 7pm and 10pm EST (highest engagement windows)',
  hashtagStrategy: 'Use 5-7 hashtags: 3 high-volume + 2 niche + 2 trending',
  contentGaps: 'Cover "beginner tips" (competitors avoid it, high search volume)',
  frequency: 'Increase from 1/day to 2/day based on competitor benchmarks'
};

2. Monthly Review Cycle

Set up recurring analysis:

• Week 1: Collect new data
• Week 2: Analyze trends
• Week 3: Test new strategies
• Week 4: Measure results, adjust

3. Continuous Monitoring

Don't just analyze once. Track changes:

// Alert on competitor spikes
async function monitorCompetitorSpikes() {
  const spikes = await pool.query(`
    SELECT 
      c.username,
      cc.platform,
      cc.caption,
      cc.views,
      cc.url
    FROM competitor_content cc
    JOIN competitors c ON c.id = cc.competitor_id
    WHERE cc.views > (
      SELECT AVG(views) * 3
      FROM competitor_content cc2
      WHERE cc2.competitor_id = cc.competitor_id
        AND cc2.platform = cc.platform
    )
    AND cc.published_at > NOW() - INTERVAL '24 hours'
  `);

  if (spikes.rows.length > 0) {
    console.log('\n🚨 COMPETITOR SPIKE ALERT\n');
    spikes.rows.forEach(spike => {
      console.log(`@${spike.username} [${spike.platform}]: ${spike.views.toLocaleString()} views (3x their average)`);
      console.log(`${spike.url}\n`);
    });
  }
}

// Run every hour
cron.schedule('0 * * * *', monitorCompetitorSpikes);

Best Practices

1. Track the Right Competitors

Don't track 50 competitors. Track 5-10 that actually matter:

• Similar size (you can compete)
• Active (posting regularly)
• Successful (high engagement)

2. Focus on Patterns, Not Posts

One viral post means nothing. Look for consistent patterns:

• Do their Reels always hit at 7pm?
• Do they always use carousel posts?
• Do certain hashtags always work?

3. Test Before You Copy

See competitors posting 5x/day? Don't immediately do the same. Test 2x/day first, measure results, then increase.

4. Combine Quantitative + Qualitative

Numbers tell you what works. Comments tell you why:

// Analyze top competitor post comments
async function analyzePostComments(postId, platform) {
  // Extract comments
  const comments = await getComments(postId, platform);
  
  // Look for patterns
  const themes = {
    questions: comments.filter(c => c.text.includes('?')),
    praise: comments.filter(c => /love|amazing|great|awesome/.test(c.text.toLowerCase())),
    requests: comments.filter(c => /want|need|please|tutorial/.test(c.text.toLowerCase()))
  };
  
  console.log('Comment Analysis:');
  console.log(`- ${themes.questions.length} questions`);
  console.log(`- ${themes.praise.length} praise comments`);
  console.log(`- ${themes.requests.length} content requests`);
  
  return themes;
}

5. Respect Rate Limits

Don't hammer the API. Space out your requests:

async function collectCompetitorDataRespectfully(usernames) {
  for (const username of usernames) {
    console.log(`Collecting data for @${username}...`);
    
    // Collect across platforms
    await collectAllPlatforms(username);
    
    // Wait 5 seconds between competitors
    await new Promise(resolve => setTimeout(resolve, 5000));
  }
}

Cost Comparison

Traditional Tools:

Rival IQ: $239-999/month

• Competitor tracking
• Limited to 5-25 competitors
• Pre-built reports only
• No raw data export

Socialbakers: $200-800/month

• Comprehensive analytics
• Limited platforms
• Expensive for small teams

Sprout Social: $249-499/month per user

• Basic competitive reports
• Locked into their dashboard
• No custom analysis

DIY with SociaVault:

Tracking 10 competitors across 4 platforms:

• 10 competitors × 4 platforms × 30 collections/month = 1,200 credits
• Cost: $60/month
• Plus: Database ($0-15/month), Server ($10/month)
• Total: ~$75-85/month

What you get:

• Track unlimited competitors
• All platforms supported
• Full raw data access
• Custom analysis & reports
• Historical tracking
• Automated dashboards

Savings: $155-915/month vs traditional tools

Conclusion

Your competitors aren't smarter than you. They're just using data.

Every post they make is a test. Every viral hit is a proven formula. Every hashtag is a signal.

You can see all of it. You can analyze all of it. You can learn from all of it.

This guide gave you:

• Complete data collection system
• Analysis frameworks
• Real code examples
• Strategic playbooks

Now you have a choice:

Keep guessing what works. Keep wondering why competitors grow faster. Keep hoping the algorithm finally notices you.

Or extract their data, analyze their patterns, and build a strategy based on what actually works in your niche.

Ready to start? Get your SociaVault API key at sociavault.com, pick your 5 competitors, and start collecting data today.

Every day you wait, your competitors post more content. More data. More patterns you could be learning from.

Stop playing blind. Start playing smart.

Your competitive intelligence system starts now.