Overview

This document describes the algorithms and methodologies used for analytics aggregation, calculation, and reporting in TendSocial.

Daily Aggregation

Data Collection

Daily aggregation runs at 2:00 AM UTC to collect the previous day's metrics from all connected social platforms.

Process:

1. Account Discovery: Find all active social accounts with analytics capability
2. Metric Fetching: For each account, call platform adapter's getAccountAnalytics() method
3. Follower Change Calculation: Compare with previous day's follower count to calculate delta
4. Storage: Upsert record in AccountAnalytics table with date as unique key

Metrics Collected

• Follower Count: Total followers/connections at end of day
• Follower Change: Net change from previous day (positive or negative)
• Following Count: Accounts/pages being followed
• Impressions: Total content views
• Reach: Unique accounts reached
• Engagements: Total interactions (likes + comments + shares + saves)
• Engagement Rate: Calculated as engagements / impressions (when impressions > 0)
• Breakdown: individual counts for likes, comments, shares, saves, clicks
• Platform Metrics: Platform-specific JSON data (e.g., YouTube watch time, Instagram story views)

Error Handling

• Rate Limiting: If rate limited, job logs warning and continues with other accounts
• Expired Tokens: Updates account status to 'expired' and notifies user
• Missing Data: Logs warning but doesn't fail entire job
• Backfill Support: Provides backfillAnalytics() function for historical data recovery

Best Times to Post Algorithm

Purpose

Analyzes historical post performance to determine optimal posting times for maximum engagement.

Algorithm Steps

1. Data Collection

   • Query all published posts for a given company/platform
   • Exclude posts less than 24 hours old (insufficient engagement data)
   • Group by day of week (0-6) and hour (0-23) → 168 possible time slots

2. Engagement Calculation

   • For each post: calculate engagement score = likes + (comments × 2) + (shares × 3)
   • The weighting gives more importance to higher-intent actions

3. Time Slot Aggregation

   • Group posts by their publishedAt time slot
   • Calculate average engagement per time slot
   • Count number of posts per slot (sample size)

4. Recency Weighting

   • Posts from last 30 days: weight = 2.0
   • Posts older than 30 days: weight = 1.0
   • Apply weighted average: weighted_score = (sum(engagement × weight)) / sum(weight)

5. Statistical Validity

   • Require minimum 5 posts per slot for the slot to be considered
   • Slots below threshold are excluded from results

6. Normalization

   • Normalize scores to 0-100 scale for easier comparison
   • Score = (slot_avg - min_avg) / (max_avg - min_avg) × 100

7. Storage

   • Results stored in BestTimeToPost table
   • Each record includes: dayOfWeek, hour, score, avgEngagement, postCount

Recommendations

• Top 10 time slots are presented to users sorted by score
• Heatmap visualization shows all 168 slots with opacity based on score
• Results are recomputed weekly to adapt to changing audience behavior

Configuration Options

{
  minSampleSize: 5,      // Minimum posts per slot
  recencyDays: 30,       // Days considered "recent"
  recencyWeight: 2.0     // Multiplier for recent posts
}

Metric Normalization

Cross-Platform Consistency

Different platforms use different terminology and provide different metrics. We normalize these into a consistent schema:

Platform	Followers	Engagements	Impressions	Reach
LinkedIn	Connections	Reactions + Comments + Shares	Impressions	Unique views
Twitter	Followers	Likes + Retweets + Replies	Impressions	Reach
Facebook	Page Likes	Reactions + Comments + Shares	Impressions	Reach
Instagram	Followers	Likes + Comments + Saves	Impressions	Reach
YouTube	Subscribers	Likes + Comments + Shares	Views	Unique viewers

Engagement Rate Calculation

Formula: engagement_rate = (total_engagements / impressions) × 100

Notes:

• If impressions = 0, engagement rate is stored as null
• Engagement rate is percentage (0-100+)
• Some platforms may have engagement rates > 100% if posts are shared widely

Platform-Specific Metrics

Metrics that don't fit the common schema are stored in platformMetrics JSON field:

YouTube:

{
  "watchTime": 12345,
  "averageViewDuration": 234,
  "subscribersGained": 10,
  "subscribersLost": 2
}

Instagram:

{
  "storyImpressions": 1234,
  "storyReach": 890,
  "profileViews": 456
}

LinkedIn:

{
  "clickThroughRate": 2.5,
  "uniqueImpressionsCount": 850
}

Report Generation Calculations

Time Period Aggregation

When generating reports, metrics are aggregated based on the selected date range:

1. Sum Metrics: Impressions, reach, engagements, clicks
2. Average Metrics: Engagement rate, follower count
3. Calculated Metrics:
   • Total follower growth = final day followers - first day followers
   • Average daily growth = total growth / number of days
   • Growth rate percentage = (total growth / starting followers) × 100

Platform Comparison

For multi-platform reports:

• Calculate totals per platform
• Calculate percentages of total (e.g., "LinkedIn drove 35% of total engagement")
• Rank platforms by selected metric
• Show platform-specific strengths (e.g., "Best engagement rate: Instagram at 5.2%")

Trend Detection

Reports include simple trend indicators:

• Compare current period to previous period of same length
• Calculate percentage change
• Classify as: "Up", "Down", or "Flat" (< 5% change)
• Display with appropriate icons/colors

Data Freshness

• Account Analytics: Updated daily at 2 AM UTC
• Best Times: Recomputed weekly on Sundays
• Live Metrics: Some platforms support near real-time queries (not stored, used for dashboards)
• Content Metrics: Updated when content is published or on-demand sync

Limitations and Considerations

1. API Rate Limits: Platform APIs have daily/hourly limits
2. Historical Data: Most platforms only provide 90 days of detailed analytics
3. Sampling: Some platforms return sampled data for high-volume accounts
4. Delays: Metrics may take 24-48 hours to be finalized by platforms
5. Timezone Handling: All timestamps stored in UTC, converted for display

References

• Platform API Documentation links
• Prisma schema: apps/backend/prisma/schema.prisma
• Analytics service: apps/backend/src/services/analytics/
• Best times service: apps/backend/src/services/analytics/bestTimes.service.ts