Audience Overview

Implementation

import sqlite3
import json
from collections import defaultdict, Counter

def get_db_connection():
    # See _shared-preamble.md
    pass

def get_audience_overview(include_word_cloud=True):
    """Main skill function."""
    conn = get_db_connection()

    try:
        cursor = conn.cursor()

        # Step 1: Get all segments
        cursor.execute('''
            SELECT id, name, description, estimated_size
            FROM audience_segments
            ORDER BY estimated_size DESC
        ''')
        segments = cursor.fetchall()

        if not segments:
            return {'error': 'No segments configured. Run /setup-audience-simulator first.'}

        segments_overview = []
        topic_affinity_matrix = {}
        role_distribution = Counter()

        for seg_row in segments:
            segment_id = seg_row['id']
            segment_name = seg_row['name']
            segment_size = seg_row['estimated_size']

            # Get seniority breakdown
            cursor.execute('''
                SELECT parsed_seniority, COUNT(*) as count
                FROM connection_profiles
                WHERE segment_id = ?
                GROUP BY parsed_seniority
                ORDER BY count DESC
            ''', (segment_id,))

            seniority_breakdown = {}
            for row in cursor.fetchall():
                seniority_breakdown[row['parsed_seniority']] = row['count']

            # Get industries
            cursor.execute('''
                SELECT DISTINCT parsed_industry
                FROM connection_profiles
                WHERE segment_id = ?
                LIMIT 5
            ''', (segment_id,))

            industries = [row['parsed_industry'] for row in cursor.fetchall()]

            # Get activity distribution
            cursor.execute('''
                SELECT activity_status, COUNT(*) as count
                FROM connection_profiles
                WHERE segment_id = ?
                GROUP BY activity_status
            ''', (segment_id,))

            activity = {'active': 0, 'occasional': 0, 'dormant': 0, 'unknown': 0}
            for row in cursor.fetchall():
                activity[row['activity_status']] = row['count']

            total = sum(activity.values())
            activity_pct = {k: int(v / total * 100) if total > 0 else 0 for k, v in activity.items()}

            # Get avg engagement
            cursor.execute('''
                SELECT AVG(engagement_score) as avg_eng
                FROM connection_profiles
                WHERE segment_id = ? AND engagement_score > 0
            ''', (segment_id,))

            avg_eng_row = cursor.fetchone()
            avg_engagement = float(avg_eng_row['avg_eng']) if avg_eng_row['avg_eng'] else 0.0

            # Get top topics
            cursor.execute('''
                SELECT topic_name, affinity_score
                FROM segment_topic_affinity
                WHERE segment_id = ?
                ORDER BY affinity_score DESC
                LIMIT 5
            ''', (segment_id,))

            top_topics = [row['topic_name'] for row in cursor.fetchall()]

            # Collect role data for word cloud
            cursor.execute('''
                SELECT parsed_role, COUNT(*) as count
                FROM connection_profiles
                WHERE segment_id = ?
                GROUP BY parsed_role
            ''', (segment_id,))

            for row in cursor.fetchall():
                role_distribution[row['parsed_role']] += row['count']

            # Build segment overview
            segments_overview.append({
                'name': segment_name,
                'size': segment_size,
                'percentage': round(segment_size / sum(s['estimated_size'] for s in segments) * 100, 1),
                'seniority_breakdown': seniority_breakdown,
                'industries': industries,
                'activity': activity,
                'activity_percentage': activity_pct,
                'top_topics': top_topics,
                'average_engagement': round(avg_engagement, 1)
            })

            # Build topic affinity row
            cursor.execute('''
                SELECT topic_name, affinity_score
                FROM segment_topic_affinity
                WHERE segment_id = ?
            ''', (segment_id,))

            affinities = {}
            for row in cursor.fetchall():
                affinities[row['topic_name']] = round(row['affinity_score'], 2)

            topic_affinity_matrix[segment_name] = affinities

        # Step 2: Activity summary
        cursor.execute('''
            SELECT activity_status, COUNT(*) as count
            FROM connection_profiles
            GROUP BY activity_status
        ''')

        activity_summary = {'total_active': 0, 'total_occasional': 0, 'total_dormant': 0}
        total_connections = 0

        for row in cursor.fetchall():
            if row['activity_status'] == 'active':
                activity_summary['total_active'] = row['count']
            elif row['activity_status'] == 'occasional':
                activity_summary['total_occasional'] = row['count']
            elif row['activity_status'] == 'dormant':
                activity_summary['total_dormant'] = row['count']
            total_connections += row['count']

        activity_summary['total_connections'] = total_connections

        # Step 3: Coverage metrics
        cursor.execute('SELECT COUNT(*) as count FROM connection_profiles')
        parsed_count = cursor.fetchone()['count']

        cursor.execute('SELECT COUNT(*) as count FROM segment_topic_affinity')
        affinity_count = cursor.fetchone()['count']

        # Step 4: Generate recommendations
        recommendations = []

        # Most engaged segment
        best_segment = max(segments_overview, key=lambda s: s['average_engagement'])
        if best_segment['top_topics']:
            top_topic = best_segment['top_topics'][0]
            recommendations.append(
                f"Your {best_segment['name']} segment is most engaged. "
                f"They love {top_topic} (affinity: {int(topic_affinity_matrix[best_segment['name']].get(top_topic, 0)*100)}%). "
                f"Consider posting more about this."
            )

        recommendations.append(
            f"Run /deep-dive {segments_overview[0]['name']} to understand engagement patterns."
        )

        return {
            'segments_overview': segments_overview,
            'topic_affinity_matrix': topic_affinity_matrix,
            'activity_summary': activity_summary,
            'coverage_metrics': {
                'connections_parsed': parsed_count,
                'connections_with_engagement_data': total_connections,
                'segments_configured': len(segments_overview),
                'topics_identified': len(topic_affinity_matrix.get(segments_overview[0]['name'], {})) if segments_overview else 0
            },
            'role_distribution': dict(role_distribution.most_common(15)) if include_word_cloud else {},
            'recommendations': recommendations
        }

    except Exception as e:
        return {'error': f'Error generating overview: {str(e)}'}

    finally:
        conn.close()

Example Output

{
  "segments_overview": [
    {
      "name": "Executives",
      "size": 150,
      "percentage": 7.6,
      "seniority_breakdown": {"VP": 64, "C-Level": 42, "Director": 38, "Founder": 6},
      "industries": ["Tech", "Finance", "Healthcare"],
      "activity": {"active": 108, "occasional": 27, "dormant": 15, "unknown": 0},
      "activity_percentage": {"active": 72, "occasional": 18, "dormant": 10, "unknown": 0},
      "top_topics": ["Leadership", "Strategy", "Thought Leadership", "Team Culture"],
      "average_engagement": 12.3
    }
  ],
  "topic_affinity_matrix": {
    "Executives": {
      "Leadership": 0.92,
      "Strategy": 0.87,
      "Thought Leadership": 0.81,
      "Fundraising": 0.72
    }
  },
  "activity_summary": {
    "total_active": 1136,
    "total_occasional": 458,
    "total_dormant": 372,
    "total_connections": 1966
  },
  "coverage_metrics": {
    "connections_parsed": 1966,
    "connections_with_engagement_data": 1247,
    "segments_configured": 4,
    "topics_identified": 15
  },
  "role_distribution": {
    "Engineer": 234,
    "Senior Engineer": 128,
    "Product Manager": 103
  },
  "recommendations": [
    "Your Executives segment is most engaged (avg 12.3 reactions/post). They love Leadership (92% affinity). Post more leadership content.",
    "Run /deep-dive Executives to understand what drives their engagement."
  ]
}