Groups Reporting Examples

This guide provides complete, production-ready SQL reports for Planning Center Groups data. These reports are designed to be run regularly for leadership meetings, ministry planning, and strategic decision-making.

Query Requirements

Schema Prefix

IMPORTANT: All tables in the Planning Center Groups module live in the planning_center schema. Always prefix table names with planning_center. in your reports. ✅ CORRECT: SELECT * FROM planning_center.groups_groups ❌ INCORRECT: SELECT * FROM groups_groups

Row Level Security (RLS)

Row Level Security automatically enforces:

tenant_organization_id – results scoped to your organization
system_status – active records returned by default

Skip manual filters for these columns—RLS already applies them and redundant predicates can suppress data or slow execution:

❌ WHERE tenant_organization_id = 1
❌ WHERE system_status = 'active'

Direct your filters toward ministry context (archived status, roles, attendance timeframes) while trusting RLS for tenancy and status.

Executive Dashboard Report

Weekly Groups Executive Summary

-- Executive summary report for groups ministry leadership
WITH current_week AS (
    SELECT
        COUNT(DISTINCT g.group_id) as total_active_groups,
        SUM(g.memberships_count) as total_members,
        COUNT(DISTINCT CASE WHEN g.created_at >= DATE_TRUNC('week', CURRENT_DATE) THEN g.group_id END) as new_groups,
        COUNT(DISTINCT e.event_id) as total_events,
        COUNT(DISTINCT CASE WHEN e.canceled = true THEN e.event_id END) as canceled_events
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_event_relationships er
        ON g.group_id = er.relationship_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON er.event_id = e.event_id
        AND e.starts_at >= DATE_TRUNC('week', CURRENT_DATE)
        AND e.starts_at < DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week'
    WHERE g.archived_at IS NULL
),
previous_week AS (
    SELECT
        COUNT(DISTINCT g.group_id) as total_active_groups,
        SUM(g.memberships_count) as total_members,
        COUNT(DISTINCT e.event_id) as total_events
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_event_relationships er
        ON g.group_id = er.relationship_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON er.event_id = e.event_id
        AND e.starts_at >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '1 week'
        AND e.starts_at < DATE_TRUNC('week', CURRENT_DATE)
    WHERE g.archived_at IS NULL
        AND g.created_at < DATE_TRUNC('week', CURRENT_DATE)
),
engagement_metrics AS (
    SELECT
        COUNT(DISTINCT a.person_id) as unique_attendees,
        AVG(CASE WHEN a.attended = true THEN 1.0 ELSE 0 END) * 100 as attendance_rate
    FROM planning_center.groups_attendances a
    JOIN planning_center.groups_events e
        ON a.event_id = e.event_id
    WHERE e.starts_at >= DATE_TRUNC('week', CURRENT_DATE)
)
SELECT
    '=== WEEKLY GROUPS EXECUTIVE SUMMARY ===' as report_header,
    TO_CHAR(DATE_TRUNC('week', CURRENT_DATE), 'Month DD, YYYY') as week_beginning,
    '' as blank1,
    '--- GROUP METRICS ---' as section1,
    cw.total_active_groups as active_groups,
    pw.total_active_groups as active_groups_last_week,
    cw.total_active_groups - pw.total_active_groups as group_change,
    cw.new_groups as new_groups_this_week,
    '' as blank2,
    '--- MEMBERSHIP METRICS ---' as section2,
    cw.total_members as total_members,
    pw.total_members as total_members_last_week,
    cw.total_members - pw.total_members as member_change,
    ROUND(cw.total_members::NUMERIC / NULLIF(cw.total_active_groups, 0), 1) as avg_group_size,
    '' as blank3,
    '--- EVENT METRICS ---' as section3,
    cw.total_events as events_this_week,
    cw.canceled_events as canceled_events,
    ROUND((cw.total_events - cw.canceled_events)::NUMERIC / NULLIF(cw.total_active_groups, 0), 1) as avg_events_per_group,
    '' as blank4,
    '--- ENGAGEMENT METRICS ---' as section4,
    em.unique_attendees as unique_attendees_this_week,
    ROUND(em.attendance_rate, 1) as attendance_percentage
FROM current_week cw, previous_week pw, engagement_metrics em;

Monthly Group Health Report

-- Monthly comprehensive group health assessment
WITH group_health AS (
    SELECT
        g.group_id,
        g.name,
        g.memberships_count,
        g.location_type_preference,
        DATE_PART('month', AGE(CURRENT_DATE, g.created_at)) as months_active,
        COUNT(DISTINCT m.person_id) as actual_members,
        COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN m.person_id END) as leaders,
        COUNT(DISTINCT e.event_id) as events_last_month,
        AVG(CASE WHEN a.attended = true THEN 1.0 ELSE 0 END) * 100 as avg_attendance_rate,
        MAX(e.starts_at) as last_event_date,
        CURRENT_DATE - MAX(e.starts_at)::DATE as days_since_last_event
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships m
        ON g.group_id = m.group_id
    LEFT JOIN planning_center.groups_event_relationships er
        ON g.group_id = er.relationship_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON er.event_id = e.event_id
        AND e.starts_at >= CURRENT_DATE - INTERVAL '30 days'
    LEFT JOIN planning_center.groups_attendances a
        ON e.event_id = a.event_id
    WHERE g.archived_at IS NULL
    GROUP BY g.group_id, g.name, g.memberships_count, g.location_type_preference, g.created_at
),
health_categories AS (
    SELECT
        name,
        memberships_count,
        actual_members,
        leaders,
        events_last_month,
        ROUND(avg_attendance_rate, 1) as attendance_rate,
        days_since_last_event,
        months_active,
        CASE
            WHEN days_since_last_event > 30 OR days_since_last_event IS NULL THEN '🚨 Inactive'
            WHEN leaders = 0 THEN '⚠️ No Leader'
            WHEN actual_members < 3 THEN '⚠️ Too Small'
            WHEN actual_members > 15 THEN '⚠️ Consider Splitting'
            WHEN avg_attendance_rate < 50 THEN '⚠️ Low Attendance'
            WHEN events_last_month = 0 THEN '⚠️ No Recent Events'
            ELSE '✓ Healthy'
        END as health_status
    FROM group_health
)
SELECT
    health_status,
    COUNT(*) as group_count,
    STRING_AGG(name, ', ' ORDER BY name) as groups
FROM health_categories
GROUP BY health_status
ORDER BY 
    CASE health_status
        WHEN '🚨 Inactive' THEN 1
        WHEN '⚠️ No Leader' THEN 2
        WHEN '⚠️ Too Small' THEN 3
        WHEN '⚠️ Consider Splitting' THEN 4
        WHEN '⚠️ Low Attendance' THEN 5
        WHEN '⚠️ No Recent Events' THEN 6
        ELSE 7
    END;

Member Engagement Reports

Member Participation Analysis

-- Comprehensive member engagement tracking across all groups
WITH member_activity AS (
    SELECT
        p.person_id,
        COUNT(DISTINCT m.group_id) as groups_joined,
        COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN m.group_id END) as groups_leading,
        MIN(m.joined_at) as first_group_joined,
        COUNT(DISTINCT e.event_id) as events_invited,
        COUNT(DISTINCT CASE WHEN a.attended = true THEN e.event_id END) as events_attended,
        MAX(e.starts_at) as last_event_date
    FROM planning_center.groups_people p
    LEFT JOIN planning_center.groups_memberships m
        ON p.person_id = m.person_id
    LEFT JOIN planning_center.groups_groups g
        ON m.group_id = g.group_id
        AND g.archived_at IS NULL
    LEFT JOIN planning_center.groups_event_relationships er
        ON g.group_id = er.relationship_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON er.event_id = e.event_id
        AND e.starts_at >= CURRENT_DATE - INTERVAL '90 days'
    LEFT JOIN planning_center.groups_attendances a
        ON e.event_id = a.event_id
        AND a.person_id = p.person_id
    GROUP BY p.person_id
),
engagement_categories AS (
    SELECT
        person_id,
        groups_joined,
        groups_leading,
        events_invited,
        events_attended,
        CASE 
            WHEN events_invited > 0 THEN 
                ROUND(events_attended::NUMERIC / events_invited * 100, 1)
            ELSE 0
        END as attendance_percentage,
        DATE_PART('month', AGE(CURRENT_DATE, first_group_joined)) as months_in_groups,
        CURRENT_DATE - last_event_date::DATE as days_since_last_event,
        CASE
            WHEN groups_joined = 0 THEN 'Not Connected'
            WHEN events_attended = 0 AND events_invited > 0 THEN 'Inactive'
            WHEN events_invited > 0 AND (events_attended::NUMERIC / events_invited) < 0.25 THEN 'Low Engagement'
            WHEN events_invited > 0 AND (events_attended::NUMERIC / events_invited) < 0.5 THEN 'Moderate Engagement'
            WHEN groups_leading > 0 THEN 'Leader'
            ELSE 'Highly Engaged'
        END as engagement_level
    FROM member_activity
)
SELECT
    engagement_level,
    COUNT(*) as member_count,
    ROUND(AVG(groups_joined), 1) as avg_groups_per_member,
    ROUND(AVG(attendance_percentage), 1) as avg_attendance_rate,
    ROUND(AVG(months_in_groups), 0) as avg_months_in_groups
FROM engagement_categories
GROUP BY engagement_level
ORDER BY 
    CASE engagement_level
        WHEN 'Not Connected' THEN 1
        WHEN 'Inactive' THEN 2
        WHEN 'Low Engagement' THEN 3
        WHEN 'Moderate Engagement' THEN 4
        WHEN 'Highly Engaged' THEN 5
        WHEN 'Leader' THEN 6
    END;

Leadership Development Pipeline

-- Identify potential leaders based on engagement and participation
WITH member_metrics AS (
    SELECT
        p.person_id,
        m.group_id,
        g.name as group_name,
        m.role,
        m.joined_at,
        DATE_PART('month', AGE(CURRENT_DATE, m.joined_at)) as months_in_group,
        COUNT(DISTINCT e.event_id) as events_count,
        COUNT(DISTINCT CASE WHEN a.attended = true THEN e.event_id END) as events_attended,
        AVG(CASE WHEN a.attended = true THEN 1.0 ELSE 0 END) as attendance_rate
    FROM planning_center.groups_people p
    JOIN planning_center.groups_memberships m
        ON p.person_id = m.person_id
    JOIN planning_center.groups_groups g
        ON m.group_id = g.group_id
        AND g.archived_at IS NULL
    LEFT JOIN planning_center.groups_event_relationships er
        ON g.group_id = er.relationship_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON er.event_id = e.event_id
        AND e.starts_at >= CURRENT_DATE - INTERVAL '6 months'
    LEFT JOIN planning_center.groups_attendances a
        ON e.event_id = a.event_id
        AND a.person_id = p.person_id
    WHERE m.role = 'member'  -- Only look at current members, not leaders
    GROUP BY p.person_id, m.group_id, g.name, m.role, m.joined_at
    HAVING COUNT(DISTINCT e.event_id) >= 5  -- Attended at least 5 events
),
leadership_candidates AS (
    SELECT
        person_id,
        group_id,
        group_name,
        months_in_group,
        events_attended,
        ROUND(attendance_rate * 100, 1) as attendance_percentage,
        CASE
            WHEN months_in_group >= 12 AND attendance_rate >= 0.8 THEN 'Ready Now'
            WHEN months_in_group >= 6 AND attendance_rate >= 0.7 THEN 'Ready Soon'
            WHEN months_in_group >= 3 AND attendance_rate >= 0.6 THEN 'Developing'
            ELSE 'Watch'
        END as leadership_readiness
    FROM member_metrics
    WHERE attendance_rate >= 0.6  -- At least 60% attendance
)
SELECT
    leadership_readiness,
    COUNT(DISTINCT person_id) as candidate_count,
    ROUND(AVG(months_in_group), 1) as avg_months_in_group,
    ROUND(AVG(attendance_percentage), 1) as avg_attendance,
    STRING_AGG(DISTINCT group_name, ', ' ORDER BY group_name) as groups_with_candidates
FROM leadership_candidates
GROUP BY leadership_readiness
ORDER BY 
    CASE leadership_readiness
        WHEN 'Ready Now' THEN 1
        WHEN 'Ready Soon' THEN 2
        WHEN 'Developing' THEN 3
        ELSE 4
    END;

Group Type Analysis Reports

Group Type Performance Comparison

-- Compare performance metrics across different group types
WITH type_metrics AS (
    SELECT
        gt.group_type_id,
        gt.name as type_name,
        gt.color,
        gt.church_center_visible,
        COUNT(DISTINCT g.group_id) as group_count,
        SUM(g.memberships_count) as total_members,
        AVG(g.memberships_count) as avg_group_size,
        COUNT(DISTINCT CASE WHEN g.created_at >= CURRENT_DATE - INTERVAL '90 days' THEN g.group_id END) as new_groups_90_days,
        COUNT(DISTINCT e.event_id) as total_events,
        AVG(CASE WHEN a.attended = true THEN 1.0 ELSE 0 END) * 100 as avg_attendance_rate
    FROM planning_center.groups_group_types gt
    LEFT JOIN planning_center.groups_group_relationships gr
        ON gt.group_type_id = gr.relationship_id
        AND gr.relationship_type = 'GroupType'
    LEFT JOIN planning_center.groups_groups g
        ON gr.group_id = g.group_id
        AND g.archived_at IS NULL
    LEFT JOIN planning_center.groups_event_relationships er
        ON g.group_id = er.relationship_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON er.event_id = e.event_id
        AND e.starts_at >= CURRENT_DATE - INTERVAL '30 days'
    LEFT JOIN planning_center.groups_attendances a
        ON e.event_id = a.event_id
    GROUP BY gt.group_type_id, gt.name, gt.color, gt.church_center_visible
)
SELECT
    type_name,
    group_count,
    total_members,
    ROUND(avg_group_size, 1) as avg_group_size,
    new_groups_90_days,
    ROUND(total_events::NUMERIC / NULLIF(group_count, 0), 1) as avg_events_per_group,
    ROUND(avg_attendance_rate, 1) as attendance_rate,
    CASE 
        WHEN church_center_visible THEN 'Public'
        ELSE 'Private'
    END as visibility,
    CASE
        WHEN new_groups_90_days > 0 THEN '📈 Growing'
        WHEN avg_attendance_rate < 50 THEN '⚠️ Low Engagement'
        WHEN avg_group_size < 5 THEN '⚠️ Small Groups'
        ELSE '✓ Stable'
    END as status
FROM type_metrics
WHERE group_count > 0
ORDER BY total_members DESC;

Event Management Reports

Weekly Event Schedule Report

-- Comprehensive weekly event schedule with attendance tracking
WITH week_events AS (
    SELECT
        e.event_id,
        e.name as event_name,
        e.description,
        e.starts_at,
        e.ends_at,
        e.location,
        e.location_type_preference,
        e.virtual_location_url,
        e.canceled,
        g.name as group_name,
        g.memberships_count as group_size,
        COUNT(DISTINCT a.person_id) as expected_attendees,
        COUNT(DISTINCT CASE WHEN a.attended = true THEN a.person_id END) as actual_attendees
    FROM planning_center.groups_events e
    JOIN planning_center.groups_event_relationships er
        ON e.event_id = er.event_id
        AND er.relationship_type = 'Group'
    JOIN planning_center.groups_groups g
        ON er.relationship_id = g.group_id
    LEFT JOIN planning_center.groups_attendances a
        ON e.event_id = a.event_id
    WHERE e.starts_at >= DATE_TRUNC('week', CURRENT_DATE)
        AND e.starts_at < DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week'
    GROUP BY e.event_id, e.name, e.description, e.starts_at, e.ends_at, 
             e.location, e.location_type_preference, e.virtual_location_url, 
             e.canceled, g.name, g.memberships_count
)
SELECT
    TO_CHAR(starts_at, 'Day') as day_of_week,
    TO_CHAR(starts_at, 'HH12:MI AM') as time,
    event_name,
    group_name,
    CASE 
        WHEN canceled THEN '❌ CANCELED'
        WHEN location_type_preference = 'virtual' THEN '💻 Online'
        WHEN location_type_preference = 'physical' THEN '📍 ' || COALESCE(location, 'TBD')
        ELSE '🔄 Hybrid'
    END as location_info,
    group_size as group_members,
    expected_attendees as rsvps,
    CASE 
        WHEN starts_at < CURRENT_TIMESTAMP THEN actual_attendees::TEXT
        ELSE 'Upcoming'
    END as attendance,
    CASE 
        WHEN canceled THEN 'Canceled'
        WHEN starts_at < CURRENT_TIMESTAMP AND actual_attendees = 0 THEN '⚠️ No attendance recorded'
        WHEN starts_at < CURRENT_TIMESTAMP AND actual_attendees < expected_attendees * 0.5 THEN '⚠️ Low attendance'
        WHEN starts_at > CURRENT_TIMESTAMP THEN 'Scheduled'
        ELSE '✓ Completed'
    END as status
FROM week_events
ORDER BY starts_at;

Growth and Retention Reports

Quarterly Growth Analysis

-- Track group growth patterns over the last 4 quarters
WITH quarterly_data AS (
    SELECT
        DATE_TRUNC('quarter', g.created_at) as quarter,
        COUNT(DISTINCT g.group_id) as new_groups,
        COUNT(DISTINCT CASE WHEN g.archived_at IS NOT NULL THEN g.group_id END) as archived_groups,
        COUNT(DISTINCT m.membership_id) as new_memberships,
        COUNT(DISTINCT m.person_id) as new_members
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships m
        ON g.group_id = m.group_id
        AND DATE_TRUNC('quarter', m.joined_at) = DATE_TRUNC('quarter', g.created_at)
    WHERE g.created_at >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '1 year'
    GROUP BY DATE_TRUNC('quarter', g.created_at)
),
cumulative_metrics AS (
    SELECT
        quarter,
        TO_CHAR(quarter, 'Q[Q] YYYY') as quarter_label,
        new_groups,
        archived_groups,
        new_memberships,
        new_members,
        SUM(new_groups - COALESCE(archived_groups, 0)) OVER (ORDER BY quarter) as net_groups_cumulative,
        LAG(new_groups, 1) OVER (ORDER BY quarter) as prev_quarter_groups,
        LAG(new_members, 1) OVER (ORDER BY quarter) as prev_quarter_members
    FROM quarterly_data
)
SELECT
    quarter_label,
    new_groups,
    archived_groups,
    new_groups - COALESCE(archived_groups, 0) as net_new_groups,
    new_members,
    new_memberships,
    ROUND(new_memberships::NUMERIC / NULLIF(new_groups, 0), 1) as avg_members_per_new_group,
    CASE 
        WHEN prev_quarter_groups > 0 THEN 
            ROUND(((new_groups - prev_quarter_groups)::NUMERIC / prev_quarter_groups) * 100, 1)
        ELSE NULL
    END as group_growth_rate,
    CASE 
        WHEN prev_quarter_members > 0 THEN 
            ROUND(((new_members - prev_quarter_members)::NUMERIC / prev_quarter_members) * 100, 1)
        ELSE NULL
    END as member_growth_rate,
    net_groups_cumulative as total_active_groups
FROM cumulative_metrics
ORDER BY quarter DESC;

Member Retention Cohort Analysis

-- Analyze member retention by cohort (when they joined their first group)
WITH member_cohorts AS (
    SELECT
        p.person_id,
        DATE_TRUNC('month', MIN(m.joined_at)) as cohort_month,
        MIN(m.joined_at) as first_joined,
        MAX(e.starts_at) as last_activity,
        COUNT(DISTINCT m.group_id) as total_groups_joined,
        COUNT(DISTINCT CASE WHEN g.archived_at IS NULL THEN m.group_id END) as active_groups
    FROM planning_center.groups_people p
    JOIN planning_center.groups_memberships m
        ON p.person_id = m.person_id
    JOIN planning_center.groups_groups g
        ON m.group_id = g.group_id
    LEFT JOIN planning_center.groups_attendances a
        ON p.person_id = a.person_id
    LEFT JOIN planning_center.groups_events e
        ON a.event_id = e.event_id
    GROUP BY p.person_id
),
retention_analysis AS (
    SELECT
        TO_CHAR(cohort_month, 'Mon YYYY') as cohort,
        COUNT(DISTINCT person_id) as cohort_size,
        COUNT(DISTINCT CASE 
            WHEN last_activity >= cohort_month + INTERVAL '1 month' THEN person_id 
        END) as retained_1_month,
        COUNT(DISTINCT CASE 
            WHEN last_activity >= cohort_month + INTERVAL '3 months' THEN person_id 
        END) as retained_3_months,
        COUNT(DISTINCT CASE 
            WHEN last_activity >= cohort_month + INTERVAL '6 months' THEN person_id 
        END) as retained_6_months,
        COUNT(DISTINCT CASE 
            WHEN active_groups > 0 THEN person_id 
        END) as currently_active,
        AVG(total_groups_joined) as avg_groups_per_member
    FROM member_cohorts
    WHERE cohort_month >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY cohort_month
)
SELECT
    cohort,
    cohort_size,
    ROUND(retained_1_month::NUMERIC / NULLIF(cohort_size, 0) * 100, 1) as month_1_retention,
    ROUND(retained_3_months::NUMERIC / NULLIF(cohort_size, 0) * 100, 1) as month_3_retention,
    ROUND(retained_6_months::NUMERIC / NULLIF(cohort_size, 0) * 100, 1) as month_6_retention,
    ROUND(currently_active::NUMERIC / NULLIF(cohort_size, 0) * 100, 1) as currently_active_pct,
    ROUND(avg_groups_per_member, 1) as avg_groups_joined
FROM retention_analysis
ORDER BY cohort DESC;

Location Analysis Report

Geographic Distribution and Optimization

-- Analyze group distribution by location for planning purposes
WITH location_stats AS (
    SELECT
        l.location_id,
        l.name as location_name,
        l.full_formatted_address,
        l.latitude,
        l.longitude,
        COUNT(DISTINCT g.group_id) as groups_at_location,
        SUM(g.memberships_count) as total_members,
        COUNT(DISTINCT e.event_id) as events_last_month,
        AVG(CASE WHEN a.attended = true THEN 1.0 ELSE 0 END) * 100 as avg_attendance_rate
    FROM planning_center.groups_locations l
    LEFT JOIN planning_center.groups_group_relationships gr
        ON l.location_id = gr.relationship_id
        AND gr.relationship_type = 'Location'
    LEFT JOIN planning_center.groups_groups g
        ON gr.group_id = g.group_id
        AND g.archived_at IS NULL
    LEFT JOIN planning_center.groups_event_relationships er
        ON g.group_id = er.relationship_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON er.event_id = e.event_id
        AND e.starts_at >= CURRENT_DATE - INTERVAL '30 days'
    LEFT JOIN planning_center.groups_attendances a
        ON e.event_id = a.event_id
    GROUP BY l.location_id, l.name, l.full_formatted_address, l.latitude, l.longitude
)
SELECT
    location_name,
    full_formatted_address,
    groups_at_location,
    total_members,
    ROUND(total_members::NUMERIC / NULLIF(groups_at_location, 0), 1) as avg_members_per_group,
    events_last_month,
    ROUND(avg_attendance_rate, 1) as attendance_rate,
    CASE
        WHEN groups_at_location = 0 THEN '📍 Available Location'
        WHEN groups_at_location = 1 THEN '✓ Single Group'
        WHEN groups_at_location <= 3 THEN '✓ Multiple Groups'
        ELSE '🔥 High Activity Hub'
    END as location_status
FROM location_stats
ORDER BY groups_at_location DESC, total_members DESC;

Year-End Summary Report

Annual Groups Ministry Impact Report

-- Comprehensive year-end summary for annual reports
WITH yearly_stats AS (
    SELECT
        COUNT(DISTINCT g.group_id) as total_groups,
        COUNT(DISTINCT CASE WHEN g.archived_at IS NULL THEN g.group_id END) as active_groups,
        COUNT(DISTINCT m.person_id) as unique_members,
        COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN m.person_id END) as unique_leaders,
        COUNT(DISTINCT e.event_id) as total_events,
        COUNT(DISTINCT CASE WHEN e.canceled = false THEN e.event_id END) as completed_events,
        COUNT(DISTINCT a.attendance_id) as total_attendance_records,
        AVG(CASE WHEN a.attended = true THEN 1.0 ELSE 0 END) * 100 as overall_attendance_rate
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships m
        ON g.group_id = m.group_id
    LEFT JOIN planning_center.groups_event_relationships er
        ON g.group_id = er.relationship_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON er.event_id = e.event_id
        AND e.starts_at >= DATE_TRUNC('year', CURRENT_DATE)
        AND e.starts_at < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year'
    LEFT JOIN planning_center.groups_attendances a
        ON e.event_id = a.event_id
    WHERE g.created_at < DATE_TRUNC('year', CURRENT_DATE) + INTERVAL '1 year'
),
growth_metrics AS (
    SELECT
        COUNT(DISTINCT CASE 
            WHEN g.created_at >= DATE_TRUNC('year', CURRENT_DATE) 
            THEN g.group_id 
        END) as new_groups_this_year,
        COUNT(DISTINCT CASE 
            WHEN m.joined_at >= DATE_TRUNC('year', CURRENT_DATE) 
            THEN m.person_id 
        END) as new_members_this_year
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships m
        ON g.group_id = m.group_id
)
SELECT
    '===========================================' as divider1,
    TO_CHAR(DATE_TRUNC('year', CURRENT_DATE), 'YYYY') || ' ANNUAL GROUPS MINISTRY REPORT' as report_title,
    '===========================================' as divider2,
    '' as blank1,
    '📊 GROUP OVERVIEW' as section1,
    '-------------------------------------------' as divider3,
    ys.total_groups as total_groups_all_time,
    ys.active_groups as currently_active_groups,
    gm.new_groups_this_year as groups_launched_this_year,
    ROUND(ys.active_groups::NUMERIC / NULLIF(ys.total_groups, 0) * 100, 1) as group_retention_rate,
    '' as blank2,
    '👥 MEMBERSHIP METRICS' as section2,
    '-------------------------------------------' as divider4,
    ys.unique_members as unique_members,
    ys.unique_leaders as unique_leaders,
    gm.new_members_this_year as new_members_this_year,
    ROUND(ys.unique_members::NUMERIC / NULLIF(ys.active_groups, 0), 1) as avg_members_per_group,
    ROUND(ys.unique_leaders::NUMERIC / NULLIF(ys.active_groups, 0) * 100, 1) as leader_coverage_percentage,
    '' as blank3,
    '📅 EVENT & ENGAGEMENT' as section3,
    '-------------------------------------------' as divider5,
    ys.total_events as total_events_scheduled,
    ys.completed_events as events_completed,
    ROUND(ys.completed_events::NUMERIC / NULLIF(ys.total_events, 0) * 100, 1) as event_completion_rate,
    ROUND(ys.overall_attendance_rate, 1) as overall_attendance_rate,
    ROUND(ys.total_events::NUMERIC / NULLIF(ys.active_groups, 0), 1) as avg_events_per_group,
    '' as blank4,
    '===========================================' as divider6
FROM yearly_stats ys, growth_metrics gm;

Reporting examples

Groups Reporting Examples

Query Requirements

Schema Prefix

Row Level Security (RLS)

Executive Dashboard Report

Weekly Groups Executive Summary

Monthly Group Health Report

Member Engagement Reports

Member Participation Analysis

Leadership Development Pipeline

Group Type Analysis Reports

Group Type Performance Comparison

Event Management Reports

Weekly Event Schedule Report

Growth and Retention Reports

Quarterly Growth Analysis

Member Retention Cohort Analysis

Location Analysis Report

Geographic Distribution and Optimization

Year-End Summary Report

Annual Groups Ministry Impact Report

Export Tips

Next Steps

​Groups Reporting Examples

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Executive Dashboard Report

​Weekly Groups Executive Summary

​Monthly Group Health Report

​Member Engagement Reports

​Member Participation Analysis

​Leadership Development Pipeline

​Group Type Analysis Reports

​Group Type Performance Comparison

​Event Management Reports

​Weekly Event Schedule Report

​Growth and Retention Reports

​Quarterly Growth Analysis

​Member Retention Cohort Analysis

​Location Analysis Report

​Geographic Distribution and Optimization

​Year-End Summary Report

​Annual Groups Ministry Impact Report

​Export Tips

​Next Steps

Groups Reporting Examples

Query Requirements

Schema Prefix

Row Level Security (RLS)

Executive Dashboard Report

Weekly Groups Executive Summary

Monthly Group Health Report

Member Engagement Reports

Member Participation Analysis

Leadership Development Pipeline

Group Type Analysis Reports

Group Type Performance Comparison

Event Management Reports

Weekly Event Schedule Report

Growth and Retention Reports

Quarterly Growth Analysis

Member Retention Cohort Analysis

Location Analysis Report

Geographic Distribution and Optimization

Year-End Summary Report

Annual Groups Ministry Impact Report

Export Tips

Next Steps