Web data analysis, from raw data to insight

Home / Web crawling / Web data analysis, from raw data to insight

Introduction

Collecting and storing data is only half the story. The real value emerges when you transform raw data into actionable insights. This post covers practical approaches to analyzing crawled data, spotting trends, and surfacing meaningful patterns.

We’ll explore:

Exploratory data analysis (EDA)
Statistical techniques for time-series and categorical data
Pattern detection and anomalies
Visualization strategies
Building dashboards and reports
Connecting insights back to business questions

By the end, you’ll be able to move beyond “data collection” to “data-driven decisions.”

Exploratory Data Analysis (EDA)

Before diving into analysis, understand your data.

Basic Statistics

import { Pool } from 'pg';

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

// Summary statistics for articles
const getSummaryStats = async () => {
  const result = await pool.query(`
    SELECT
      COUNT(*) as total_articles,
      COUNT(DISTINCT author_id) as unique_authors,
      COUNT(DISTINCT DATE(published_at)) as days_published,
      
      -- Text length statistics
      AVG(LENGTH(content)) as avg_content_length,
      MIN(LENGTH(content)) as min_content_length,
      MAX(LENGTH(content)) as max_content_length,
      
      -- Publishing patterns
      MIN(published_at) as earliest_published,
      MAX(published_at) as latest_published,
      
      -- Quality metrics
      COUNT(*) FILTER (WHERE content IS NOT NULL) as with_content,
      COUNT(*) FILTER (WHERE published_at IS NOT NULL) as with_dates
      
    FROM articles
  `);
  
  return result.rows[0];
};

// Distribution of articles by author
const getAuthorDistribution = async (limit: number = 20) => {
  const result = await pool.query(`
    SELECT
      a.name as author,
      COUNT(ar.id) as article_count,
      ROUND(100.0 * COUNT(ar.id) / (SELECT COUNT(*) FROM articles), 2) as percentage,
      AVG(LENGTH(ar.content)) as avg_length,
      MIN(ar.published_at) as first_article,
      MAX(ar.published_at) as last_article
    FROM authors a
    LEFT JOIN articles ar ON a.id = ar.author_id
    GROUP BY a.id, a.name
    ORDER BY article_count DESC
    LIMIT $1
  `, [limit]);
  
  return result.rows;
};

// Tag co-occurrence analysis
const getTagCooccurrence = async (minCo: number = 10) => {
  const result = await pool.query(`
    SELECT
      t1.name as tag1,
      t2.name as tag2,
      COUNT(*) as cooccurrence_count
    FROM article_tags at1
    JOIN article_tags at2 ON at1.article_id = at2.article_id 
      AND at1.tag_id < at2.tag_id
    JOIN tags t1 ON at1.tag_id = t1.id
    JOIN tags t2 ON at2.tag_id = t2.id
    GROUP BY t1.id, t1.name, t2.id, t2.name
    HAVING COUNT(*) >= $1
    ORDER BY cooccurrence_count DESC
  `, [minCo]);
  
  return result.rows;
};

Data Quality Assessment

// Check for missing/anomalous data
const assessDataQuality = async () => {
  const result = await pool.query(`
    SELECT
      COUNT(*) as total_records,
      
      -- Null counts
      COUNT(*) FILTER (WHERE title IS NULL) as null_titles,
      COUNT(*) FILTER (WHERE content IS NULL) as null_content,
      COUNT(*) FILTER (WHERE author_id IS NULL) as null_authors,
      COUNT(*) FILTER (WHERE published_at IS NULL) as null_dates,
      
      -- Out of range
      COUNT(*) FILTER (WHERE LENGTH(title) < 3) as short_titles,
      COUNT(*) FILTER (WHERE LENGTH(content) < 100) as short_content,
      
      -- Duplicates
      COUNT(*) - COUNT(DISTINCT slug) as duplicated_slugs,
      COUNT(*) - COUNT(DISTINCT source_url) as duplicated_urls,
      
      -- Entity completeness
      ROUND(100.0 * COUNT(*) FILTER (WHERE title IS NOT NULL and content IS NOT NULL) / COUNT(*), 2) as completeness_percent
      
    FROM articles
  `);
  
  return result.rows[0];
};

interface DataQualityIssue {
  field: string;
  issue: string;
  count: number;
  severity: 'warning' | 'error' | 'info';
}

const generateQualityReport = async (): Promise<DataQualityIssue[]> => {
  const quality = await assessDataQuality();
  const issues: DataQualityIssue[] = [];
  
  if (quality.null_titles > 0) {
    issues.push({
      field: 'title',
      issue: `${quality.null_titles} articles missing titles`,
      count: quality.null_titles,
      severity: 'error'
    });
  }
  
  if (quality.short_titles > 0) {
    issues.push({
      field: 'title',
      issue: `${quality.short_titles} articles with title < 3 chars`,
      count: quality.short_titles,
      severity: 'warning'
    });
  }
  
  if (quality.duplicated_urls > 0) {
    issues.push({
      field: 'source_url',
      issue: `${quality.duplicated_urls} duplicate source URLs`,
      count: quality.duplicated_urls,
      severity: 'error'
    });
  }
  
  return issues;
};

Time-Series Analysis

Trends Over Time

// Publishing volume over time
const getPublishingTrend = async (
  interval: '1 day' | '1 week' | '1 month' = '1 week'
) => {
  const result = await pool.query(`
    SELECT
      DATE_TRUNC('${interval}', published_at) as period,
      COUNT(*) as article_count,
      COUNT(DISTINCT author_id) as unique_authors,
      AVG(LENGTH(content))::INT as avg_content_length
    FROM articles
    WHERE published_at IS NOT NULL
    GROUP BY period
    ORDER BY period DESC
  `);
  
  return result.rows;
};

interface TrendPoint {
  period: Date;
  articleCount: number;
  uniqueAuthors: number;
  avgContentLength: number;
  trend?: 'increasing' | 'decreasing' | 'stable';
}

// Detect trend direction
const analyzeTrendDirection = (
  points: TrendPoint[]
): 'increasing' | 'decreasing' | 'stable' => {
  if (points.length < 2) return 'stable';
  
  const recent = points[0].articleCount;
  const previous = points[Math.min(3, points.length - 1)].articleCount;
  
  const percentChange = ((recent - previous) / previous) * 100;
  
  if (percentChange > 10) return 'increasing';
  if (percentChange < -10) return 'decreasing';
  return 'stable';
};

// Moving average (smoothing noise)
const calculateMovingAverage = (
  points: number[],
  windowSize: number = 7
): number[] => {
  return points.map((_, index, arr) => {
    const start = Math.max(0, index - windowSize + 1);
    const window = arr.slice(start, index + 1);
    return window.reduce((a, b) => a + b, 0) / window.length;
  });
};

// Detect anomalies using standard deviation
const detectAnomalies = (
  points: number[],
  threshold: number = 2
): Array<{ index: number; value: number; deviation: number }> => {
  const mean = points.reduce((a, b) => a + b, 0) / points.length;
  const variance = points.reduce((sum, val) => sum + Math.pow(val - mean, 2), 0) / points.length;
  const stdDev = Math.sqrt(variance);
  
  return points
    .map((value, index) => ({
      index,
      value,
      deviation: Math.abs((value - mean) / stdDev)
    }))
    .filter(point => point.deviation > threshold);
};

Growth Metrics

// Calculate growth rate
const calculateGrowthRate = async (
  startDate: Date,
  endDate: Date,
  periodDays: number = 7
): Promise<{ period: string; growthRate: number }[]> => {
  const result = await pool.query(`
    WITH daily_counts AS (
      SELECT
        DATE(published_at) as day,
        COUNT(*) as count,
        SUM(COUNT(*)) OVER (ORDER BY DATE(published_at)) as cumulative
      FROM articles
      WHERE published_at >= $1 AND published_at <= $2
      GROUP BY DATE(published_at)
    )
    SELECT
      to_char(day, 'YYYY-MM-DD') as period,
      ROUND(
        100.0 * (cumulative - LAG(cumulative, $3) OVER (ORDER BY day)) / 
                LAG(cumulative, $3) OVER (ORDER BY day),
        2
      ) as growthRate
    FROM daily_counts
    WHERE cumulative - LAG(cumulative, $3) OVER (ORDER BY day) IS NOT NULL
    ORDER BY day
  `, [startDate, endDate, periodDays]);
  
  return result.rows;
};

// Forecast using simple exponential smoothing
const simpleExponentialSmoothing = (
  values: number[],
  alpha: number = 0.3,
  forecastSteps: number = 5
): number[] => {
  if (values.length === 0) return [];
  
  const smoothed: number[] = [values[0]];
  
  // Smooth historical data
  for (let i = 1; i < values.length; i++) {
    const previousSmoothed = smoothed[i - 1];
    const current = values[i];
    smoothed.push(alpha * current + (1 - alpha) * previousSmoothed);
  }
  
  // Forecast
  const lastSmoothed = smoothed[smoothed.length - 1];
  const forecast: number[] = [];
  
  for (let i = 0; i < forecastSteps; i++) {
    forecast.push(lastSmoothed);
  }
  
  return forecast;
};

Categorical Analysis

Frequency Analysis

// Most common values
const getTagFrequency = async (limit: number = 30) => {
  const result = await pool.query(`
    SELECT
      t.name,
      t.slug,
      COUNT(at.article_id) as frequency,
      ROUND(
        100.0 * COUNT(at.article_id) / 
        (SELECT COUNT(*) FROM articles),
        2
      ) as percentage
    FROM tags t
    LEFT JOIN article_tags at ON t.id = at.tag_id
    GROUP BY t.id, t.name, t.slug
    ORDER BY frequency DESC
    LIMIT $1
  `, [limit]);
  
  return result.rows;
};

// Diversity metrics (entropy)
const calculateEntropy = (frequencies: number[]): number => {
  const total = frequencies.reduce((a, b) => a + b, 0);
  const probabilities = frequencies.map(f => f / total);
  
  return -probabilities.reduce((entropy, p) => {
    if (p === 0) return entropy;
    return entropy + p * Math.log2(p);
  }, 0);
};

// Concentration metrics (Herfindahl index)
const calculateHerfindahlIndex = (frequencies: number[]): number => {
  const total = frequencies.reduce((a, b) => a + b, 0);
  const squares = frequencies.reduce((sum, f) => {
    const share = f / total;
    return sum + share * share;
  }, 0);
  
  return squares;
};

interface DiversityMetrics {
  entropy: number;        // 0-1, higher = more diverse
  herfindahl: number;     // 0-1, higher = more concentrated
  dominantShare: number;  // Top item percentage
  topNPercent: number;    // % of items in top N
}

const analyzeDiversity = async (
  topN: number = 10
): Promise<DiversityMetrics> => {
  const frequencies = await getTagFrequency(50);
  
  const frequencyValues = frequencies.map(f => f.frequency);
  const topNFrequencies = frequencyValues.slice(0, topN);
  const total = frequencyValues.reduce((a, b) => a + b, 0);
  const topNTotal = topNFrequencies.reduce((a, b) => a + b, 0);
  
  return {
    entropy: calculateEntropy(frequencyValues),
    herfindahl: calculateHerfindahlIndex(frequencyValues),
    dominantShare: (frequencyValues[0] / total) * 100,
    topNPercent: (topNTotal / total) * 100
  };
};

Pattern Detection

Correlation Analysis

// Correlation between article length and engagement
const getContentLengthCorrelation = async () => {
  const result = await pool.query(`
    SELECT
      a.id,
      LENGTH(a.content) as content_length,
      COUNT(DISTINCT c.id) as comment_count,
      COALESCE(SUM(r.rating), 0) as total_rating
    FROM articles a
    LEFT JOIN comments c ON a.id = c.article_id
    LEFT JOIN ratings r ON a.id = r.article_id
    GROUP BY a.id, a.content
  `);
  
  const data = result.rows;
  
  // Calculate Pearson correlation
  const pearsonCorrelation = (x: number[], y: number[]): number => {
    const n = Math.min(x.length, y.length);
    const sumX = x.slice(0, n).reduce((a, b) => a + b);
    const sumY = y.slice(0, n).reduce((a, b) => a + b);
    const sumXY = x.slice(0, n).reduce((sum, xi, i) => sum + xi * y[i], 0);
    const sumX2 = x.slice(0, n).reduce((sum, xi) => sum + xi * xi, 0);
    const sumY2 = y.slice(0, n).reduce((sum, yi) => sum + yi * yi, 0);
    
    const numerator = n * sumXY - sumX * sumY;
    const denominator = Math.sqrt(
      (n * sumX2 - sumX * sumX) * (n * sumY2 - sumY * sumY)
    );
    
    return denominator === 0 ? 0 : numerator / denominator;
  };
  
  const contentLengths = data.map(d => d.content_length);
  const commentCounts = data.map(d => d.comment_count);
  
  return {
    lengthVsComments: pearsonCorrelation(contentLengths, commentCounts),
    correlation: pearsonCorrelation(contentLengths, data.map(d => d.total_rating))
  };
};

Clustering and Segmentation

// K-means clustering for article similarity
import { default as similarity } from 'compute-cosine-similarity';

interface Article {
  id: string;
  title: string;
  tags: string[];
  length: number;
}

const kMeansClustering = (
  articles: Article[],
  k: number = 5,
  maxIterations: number = 100
) => {
  // Feature extraction (simplified)
  const features = articles.map(a => [
    a.tags.length,      // Number of tags
    a.length / 1000,    // Content length in KB
    a.title.length / 10 // Title length
  ]);
  
  // Initialize centroids randomly
  const centroids = Array.from(
    { length: k },
    () => Array.from({ length: features[0].length }, () => Math.random() * 10)
  );
  
  let assignments = new Array(articles.length).fill(0);
  
  for (let iteration = 0; iteration < maxIterations; iteration++) {
    // Assign articles to nearest centroid
    for (let i = 0; i < articles.length; i++) {
      let minDistance = Infinity;
      let bestCluster = 0;
      
      for (let j = 0; j < k; j++) {
        const distance = euclideanDistance(features[i], centroids[j]);
        if (distance < minDistance) {
          minDistance = distance;
          bestCluster = j;
        }
      }
      
      assignments[i] = bestCluster;
    }
    
    // Update centroids
    for (let j = 0; j < k; j++) {
      const members = assignments
        .map((c, i) => c === j ? features[i] : null)
        .filter(f => f !== null) as number[][];
      
      if (members.length > 0) {
        centroids[j] = members[0].map((_, dim) => {
          const avg = members.reduce((sum, m) => sum + m[dim], 0) / members.length;
          return avg;
        });
      }
    }
  }
  
  return assignments;
};

const euclideanDistance = (a: number[], b: number[]): number => {
  return Math.sqrt(
    a.reduce((sum, val, i) => sum + Math.pow(val - b[i], 2), 0)
  );
};

Visualization and Reporting

Dashboard Queries

// Comprehensive dashboard data
const getDashboardData = async () => {
  const [stats, trends, topTags, authors] = await Promise.all([
    getSummaryStats(),
    getPublishingTrend('1 week'),
    getTagFrequency(10),
    getAuthorDistribution(10)
  ]);
  
  return {
    stats: {
      totalArticles: stats.total_articles,
      uniqueAuthors: stats.unique_authors,
      avgContentLength: Math.round(stats.avg_content_length),
      dateRange: {
        from: stats.earliest_published,
        to: stats.latest_published
      }
    },
    trends: trends.map(t => ({
      period: t.period,
      articles: t.article_count,
      authors: t.unique_authors
    })),
    topTags: topTags.map(t => ({
      name: t.name,
      count: t.frequency,
      percentage: t.percentage
    })),
    topAuthors: authors.map(a => ({
      name: a.author,
      count: a.article_count,
      percentage: a.percentage
    }))
  };
};

Export Formats

// CSV export
const exportToCSV = (
  data: Array<Record<string, unknown>>,
  filename: string
) => {
  const headers = Object.keys(data[0]);
  const csv = [
    headers.join(','),
    ...data.map(row =>
      headers.map(h => JSON.stringify(row[h])).join(',')
    )
  ].join('\n');
  
  return csv;
};

// JSON export
const exportToJSON = (
  data: any,
  filename: string
) => {
  return JSON.stringify(data, null, 2);
};

// Chart data for visualization libraries
const formatForChart = (
  trendData: Array<{ period: Date; article_count: number }>
) => {
  return {
    labels: trendData.map(t => t.period.toISOString().split('T')[0]),
    datasets: [
      {
        label: 'Articles Published',
        data: trendData.map(t => t.article_count),
        borderColor: 'rgb(75, 192, 192)',
        tension: 0.1
      }
    ]
  };
};

Actionable Insights Framework

interface Insight {
  title: string;
  description: string;
  metric: number | string;
  trend?: 'up' | 'down' | 'stable';
  recommendation: string;
  importance: 'critical' | 'high' | 'medium' | 'low';
}

const generateInsights = async (): Promise<Insight[]> => {
  const [quality, diversity, growth] = await Promise.all([
    generateQualityReport(),
    analyzeDiversity(),
    calculateGrowthRate(
      new Date(Date.now() - 30 * 24 * 60 * 60 * 1000),
      new Date()
    )
  ]);
  
  const insights: Insight[] = [];
  
  // Quality insight
  const criticalIssues = quality.filter(q => q.severity === 'error');
  if (criticalIssues.length > 0) {
    insights.push({
      title: 'Data Quality Issues',
      description: `Found ${criticalIssues.length} critical issues in your data`,
      metric: criticalIssues.length,
      recommendation: 'Review and fix critical issues immediately',
      importance: 'critical'
    });
  }
  
  // Diversity insight
  if (diversity.herfindahl > 0.3) {
    insights.push({
      title: 'Low Content Diversity',
      description: 'Content is concentrated in few categories',
      metric: `${(diversity.dominantShare).toFixed(1)}%`,
      recommendation: 'Consider expanding into underrepresented topics',
      importance: 'high'
    });
  }
  
  // Growth insight
  const latestGrowth = growth[0]?.growthRate ?? 0;
  insights.push({
    title: 'Growth Rate',
    description: `${latestGrowth > 0 ? 'Positive' : 'Negative'} growth detected`,
    metric: `${latestGrowth.toFixed(1)}%`,
    trend: latestGrowth > 0 ? 'up' : 'down',
    recommendation: latestGrowth > 0
      ? 'Maintain current publishing pace'
      : 'Increase content creation to reverse trend',
    importance: 'high'
  });
  
  return insights;
};

Discussion Questions

What analytical questions do you want to answer from your crawled data?
How do you typically present data insights to stakeholders?
What dashboards or metrics are most useful for your use case?

Next Steps

Now that you can extract insights, you’re ready for Part 6: Best Practices and Production Lessons, where we’ll cover scalability, reliability, and lessons learned from production crawling systems.

Part of the Web Crawling and Data Analysis Series

Previous: Part 4: Storage Strategies
Next: Part 6: Best Practices and Production Lessons
Series Overview: Web Crawling and Data Analysis Series

Further Resources:

You also might like

About this post

This post was written by a human with AI assistance, which was used for research, outlining, editing and code suggestions. Claims, code samples and references were reviewed before publication, and the sources are cited so you can judge them yourself.