Analyzing Engagement Data for Top Content

1. From Raw Metrics to Meaningful Insights

Before we write any code, let's define what "top-performing" means. Is it just the post with the most likes? As a data engineer, you know that meaningful insights come from looking beyond surface-level numbers.

The article below provides an excellent framework for thinking about which metrics matter and why.

As the article highlights, aggregation isn't just about summing up likes. It's about segmenting your data across different dimensions (platform, topic, format) to uncover what truly drives high-quality interaction. For our system, we'll define performance by aggregating key metrics like likes, comments, and shares, which together provide a more holistic view of engagement.

2. Designing a Schema for Analytics Data

To aggregate data, we first need to store it in a structured way. Drawing on your experience with databases and system design, the first step is to define a schema. This schema will combine the metrics we fetched in the last lesson with metadata about the content itself.

Let's look at an example of a schema designed for social media analytics.

Inspired by that example, let's define a TypeScript interface for our performance records. This record will be the fundamental unit of data in our analytics system. We'll add two crucial fields not present in the raw metrics: topic and format. Your content generation agent should already know this information when it creates a post.

In a new file, src/monitoring/analysis.ts, let's define our data model:

// src/monitoring/analysis.ts

export interface PostPerformanceRecord {
  // Metadata about the post
  postId: string; // Platform-specific ID (e.g., Tweet ID, Share URN)
  platform: 'LinkedIn' | 'X' | 'Instagram';
  topic: string; // The core subject, e.g., 'TypeScript', 'LangGraph'
  format: 'article' | 'post' | 'thread' | 'carousel'; // Content format
  publishedAt: string; // ISO 8601 timestamp

  // The metrics we fetched
  metrics: {
    likes: number;
    comments: number;
    shares: number; // Includes retweets, quotes
    impressions: number;
    clicks?: number; // Primarily for LinkedIn
  };
}

For this lesson, we'll store these records in a simple JSON file (analytics_data.json). In a production system, you would leverage your expertise with databases like Postgres to store this data in a dedicated table, but a file is sufficient for our current development needs.

3. Implementing Data Storage and Aggregation

Now we have a model. The next steps are to:

1. Save the performance data for each post.
2. Aggregate the saved data to find top performers.

Storing Performance Data

Let's create a helper function that takes a PostPerformanceRecord and appends it to our JSON "database". This function will be called by your agent after it fetches the latest metrics for a post it has published.

// src/monitoring/analysis.ts (continued)
import * as fs from 'fs/promises';

const ANALYTICS_DB_PATH = './analytics_data.json';

// Helper to read the entire database
async function readAnalyticsDB(): Promise<PostPerformanceRecord[]> {
  try {
    const data = await fs.readFile(ANALYTICS_DB_PATH, 'utf-8');
    return JSON.parse(data) as PostPerformanceRecord[];
  } catch (error: any) {
    if (error.code === 'ENOENT') {
      return []; // File doesn't exist, return empty array
    }
    throw error;
  }
}

// Function to save a new performance record
export async function savePerformanceRecord(record: PostPerformanceRecord): Promise<void> {
  const allRecords = await readAnalyticsDB();
  
  // Optional: check for duplicates if you might run this multiple times for the same post
  const existingIndex = allRecords.findIndex(r => r.postId === record.postId);
  if (existingIndex !== -1) {
    allRecords[existingIndex] = record; // Update existing record
  } else {
    allRecords.push(record); // Add new record
  }

  await fs.writeFile(ANALYTICS_DB_PATH, JSON.stringify(allRecords, null, 2));
  console.log(`Saved performance data for post ${record.postId}`);
}

Aggregating by Topic and Format

This is the core of our lesson. We need a function that reads all the records and computes summary statistics for each topic and format. This is conceptually identical to running a GROUP BY query in SQL, which you'll be familiar with.

The SQL query from the Tinybird article provides a great mental model:

SELECT ..., SUM(engagement_count), COUNT() FROM ... GROUP BY ...

We will implement this logic in TypeScript using reduce. Our function will return an object that summarizes performance, allowing us to easily rank topics and formats.

// src/monitoring/analysis.ts (continued)

export interface PerformanceSummary {
  [key: string]: { // The key will be the topic or format, e.g., 'TypeScript'
    postCount: number;
    totalLikes: number;
    totalComments: number;
    totalShares: number;
    totalImpressions: number;
    // A simple engagement score: giving more weight to shares and comments
    weightedEngagementScore: number; 
  };
}

export async function analyzePerformance(): Promise<{
  byTopic: PerformanceSummary;
  byFormat: PerformanceSummary;
}> {
  const records = await readAnalyticsDB();

  const createSummary = (key: 'topic' | 'format') => {
    return records.reduce((summary: PerformanceSummary, record) => {
      const groupKey = record[key];

      if (!summary[groupKey]) {
        summary[groupKey] = {
          postCount: 0,
          totalLikes: 0,
          totalComments: 0,
          totalShares: 0,
          totalImpressions: 0,
          weightedEngagementScore: 0,
        };
      }

      const group = summary[groupKey];
      group.postCount += 1;
      group.totalLikes += record.metrics.likes;
      group.totalComments += record.metrics.comments;
      group.totalShares += record.metrics.shares;
      group.totalImpressions += record.metrics.impressions;

      // Calculate a weighted score: shares=5, comments=3, likes=1
      group.weightedEngagementScore += (record.metrics.shares * 5) + 
                                       (record.metrics.comments * 3) + 
                                       (record.metrics.likes * 1);

      return summary;
    }, {});
  };

  const byTopic = createSummary('topic');
  const byFormat = createSummary('format');
  
  // You could also calculate engagement rate here if desired:
  // (total engagement / total impressions) * 100

  return { byTopic, byFormat };
}

This analyzePerformance function processes your stored data and provides a clear, aggregated summary, much like the analytics dashboards you see in commercial tools.

4. Putting It into Practice

To test this, you can create a small script. Imagine your agent has just published two posts and later fetched their metrics.

// a test script, e.g., `run_analysis.ts`

import { savePerformanceRecord, analyzePerformance, PostPerformanceRecord } from './monitoring/analysis';

async function main() {
  // --- This part simulates data collection ---
  const post1: PostPerformanceRecord = {
    postId: 'x-123', platform: 'X', topic: 'LangGraph', format: 'thread', publishedAt: new Date().toISOString(),
    metrics: { likes: 150, comments: 20, shares: 30, impressions: 10000 }
  };
  const post2: PostPerformanceRecord = {
    postId: 'li-456', platform: 'LinkedIn', topic: 'LangGraph', format: 'article', publishedAt: new Date().toISOString(),
    metrics: { likes: 200, comments: 45, shares: 15, impressions: 8000, clicks: 120 }
  };
   const post3: PostPerformanceRecord = {
    postId: 'x-789', platform: 'X', topic: 'TypeScript', format: 'post', publishedAt: new Date().toISOString(),
    metrics: { likes: 80, comments: 5, shares: 10, impressions: 5000 }
  };
  
  await savePerformanceRecord(post1);
  await savePerformanceRecord(post2);
  await savePerformanceRecord(post3);
  // --- End of simulation ---

  // --- This is the analysis step ---
  const analysis = await analyzePerformance();

  console.log('--- Performance by Topic ---');
  console.log(JSON.stringify(analysis.byTopic, null, 2));

  console.log('\n--- Performance by Format ---');
  console.log(JSON.stringify(analysis.byFormat, null, 2));
}

main();

Running this script would output a JSON object showing that 'LangGraph' is a high-performing topic and allows you to compare the performance of 'thread' vs 'article' formats. This is the actionable insight we were looking for!

Conclusion

You have successfully built the analytical engine for your content agent. By moving from raw, single-post metrics to aggregated, categorized summaries, you've enabled your system to learn from its own performance.

Key Takeaways:

• Schema is Strategy: A well-defined data model (PostPerformanceRecord) is essential for effective analytics. It must include not just metrics but also the dimensions you want to analyze (topic, format).
• Aggregation as GROUP BY: The process of summarizing performance by category is conceptually identical to a SQL GROUP BY operation, a powerful pattern for data analysis.
• Weighted Scoring: Not all engagement is equal. Using a weighted score (e.g., giving more value to shares and comments) provides a more nuanced view of what content is truly resonating.
• Segmentation Unlocks Insights: Analyzing performance byTopic and byFormat separately reveals patterns that would be hidden in a single, overall engagement number.

Preview of the next lesson:
Now that your agent can identify which topics and formats perform best, what's next? The logical step is to feed this information back into the content generation process. In our next lesson, "Implement a feedback loop that adjusts trend-scoring parameters based on past content performance," we will use the output of analyzePerformance to influence which trends your agent chooses to write about in the future, making the entire system adaptive and self-improving.