Skip to main content
Parable
Open Parable

Planning Center Groups Report Examples

Production-ready Groups reports for ministry leaders: participation summaries, inactive group lists, and attendance trends ready to schedule.

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_events_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_events_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 apr.relationship_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_attendances_relationships aer
        ON aer.attendance_id = a.attendance_id AND aer.relationship_type = 'Event'
    JOIN planning_center.groups_events e
        ON e.event_id = aer.relationship_id
    LEFT JOIN planning_center.groups_attendances_relationships apr
        ON apr.attendance_id = a.attendance_id AND apr.relationship_type = 'Person'
    WHERE e.starts_at >= DATE_TRUNC('week', CURRENT_DATE)
)
SELECT
    '=== WEEKLY GROUPS EXECUTIVE SUMMARY ===' as report_header,
    TO_CHAR(DATE_TRUNC('week', CURRENT_DATE), 'FMMonth 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 mr.membership_id) as actual_members,
        COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN mr.membership_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_relationships mr
        ON mr.relationship_id = g.group_id AND mr.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_memberships m
        ON m.membership_id = mr.membership_id
    LEFT JOIN planning_center.groups_events_relationships er
        ON er.relationship_id = g.group_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON e.event_id = er.event_id
        AND e.starts_at >= CURRENT_DATE - INTERVAL '30 days'
    LEFT JOIN planning_center.groups_attendances_relationships aer
        ON aer.relationship_type = 'Event' AND aer.relationship_id = e.event_id
    LEFT JOIN planning_center.groups_attendances a
        ON a.attendance_id = aer.attendance_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 mr_group.relationship_id) as groups_joined,
        COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN mr_group.relationship_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_relationships mr_person
        ON mr_person.relationship_id = p.person_id AND mr_person.relationship_type = 'Person'
    LEFT JOIN planning_center.groups_memberships m
        ON m.membership_id = mr_person.membership_id
    LEFT JOIN planning_center.groups_memberships_relationships mr_group
        ON mr_group.membership_id = m.membership_id AND mr_group.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_groups g
        ON g.group_id = mr_group.relationship_id
        AND g.archived_at IS NULL
    LEFT JOIN planning_center.groups_events_relationships er
        ON er.relationship_id = g.group_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON e.event_id = er.event_id
        AND e.starts_at >= CURRENT_DATE - INTERVAL '90 days'
    LEFT JOIN planning_center.groups_attendances_relationships aer
        ON aer.relationship_type = 'Event' AND aer.relationship_id = e.event_id
    LEFT JOIN planning_center.groups_attendances a
        ON a.attendance_id = aer.attendance_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)::NUMERIC, 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,
        mr_group.relationship_id as 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_relationships mr_person
        ON mr_person.relationship_id = p.person_id AND mr_person.relationship_type = 'Person'
    JOIN planning_center.groups_memberships m
        ON m.membership_id = mr_person.membership_id
    JOIN planning_center.groups_memberships_relationships mr_group
        ON mr_group.membership_id = m.membership_id AND mr_group.relationship_type = 'Group'
    JOIN planning_center.groups_groups g
        ON g.group_id = mr_group.relationship_id
        AND g.archived_at IS NULL
    LEFT JOIN planning_center.groups_events_relationships er
        ON er.relationship_id = g.group_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON e.event_id = er.event_id
        AND e.starts_at >= CURRENT_DATE - INTERVAL '6 months'
    LEFT JOIN planning_center.groups_attendances_relationships aer
        ON aer.relationship_type = 'Event' AND aer.relationship_id = e.event_id
    LEFT JOIN planning_center.groups_attendances a
        ON a.attendance_id = aer.attendance_id
    WHERE m.role = 'member'  -- Only look at current members, not leaders
    GROUP BY p.person_id, mr_group.relationship_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)::NUMERIC, 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_groups_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_events_relationships er
        ON er.relationship_id = g.group_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON e.event_id = er.event_id
        AND e.starts_at >= CURRENT_DATE - INTERVAL '30 days'
    LEFT JOIN planning_center.groups_attendances_relationships aer
        ON aer.relationship_type = 'Event' AND aer.relationship_id = e.event_id
    LEFT JOIN planning_center.groups_attendances a
        ON a.attendance_id = aer.attendance_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_type_preference,
        e.virtual_location_url,
        e.canceled,
        g.name as group_name,
        g.memberships_count as group_size,
        COUNT(DISTINCT apr.relationship_id) as expected_attendees,
        COUNT(DISTINCT CASE WHEN a.attended = true THEN apr.relationship_id END) as actual_attendees
    FROM planning_center.groups_events e
    JOIN planning_center.groups_events_relationships er
        ON er.event_id = e.event_id
        AND er.relationship_type = 'Group'
    JOIN planning_center.groups_groups g
        ON g.group_id = er.relationship_id
    LEFT JOIN planning_center.groups_attendances_relationships aer
        ON aer.relationship_type = 'Event' AND aer.relationship_id = e.event_id
    LEFT JOIN planning_center.groups_attendances a
        ON a.attendance_id = aer.attendance_id
    LEFT JOIN planning_center.groups_attendances_relationships apr
        ON apr.attendance_id = a.attendance_id AND apr.relationship_type = 'Person'
    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_type_preference, e.virtual_location_url, 
             e.canceled, g.name, g.memberships_count
)
SELECT
    TO_CHAR(starts_at, 'FMDay') 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 '📍 In Person'
        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 mr_person.relationship_id) as new_members
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships_relationships mr
        ON mr.relationship_id = g.group_id AND mr.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_memberships m
        ON m.membership_id = mr.membership_id
        AND DATE_TRUNC('quarter', m.joined_at) = DATE_TRUNC('quarter', g.created_at)
    LEFT JOIN planning_center.groups_memberships_relationships mr_person
        ON mr_person.membership_id = m.membership_id AND mr_person.relationship_type = 'Person'
    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 mr_group.relationship_id) as total_groups_joined,
        COUNT(DISTINCT CASE WHEN g.archived_at IS NULL THEN mr_group.relationship_id END) as active_groups
    FROM planning_center.groups_people p
    JOIN planning_center.groups_memberships_relationships mr_person
        ON mr_person.relationship_id = p.person_id AND mr_person.relationship_type = 'Person'
    JOIN planning_center.groups_memberships m
        ON m.membership_id = mr_person.membership_id
    JOIN planning_center.groups_memberships_relationships mr_group
        ON mr_group.membership_id = m.membership_id AND mr_group.relationship_type = 'Group'
    JOIN planning_center.groups_groups g
        ON g.group_id = mr_group.relationship_id
    LEFT JOIN planning_center.groups_attendances_relationships apr
        ON apr.relationship_id = p.person_id AND apr.relationship_type = 'Person'
    LEFT JOIN planning_center.groups_attendances a
        ON a.attendance_id = apr.attendance_id
    LEFT JOIN planning_center.groups_attendances_relationships aer
        ON aer.attendance_id = a.attendance_id AND aer.relationship_type = 'Event'
    LEFT JOIN planning_center.groups_events e
        ON e.event_id = aer.relationship_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_groups_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_events_relationships er
        ON er.relationship_id = g.group_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON e.event_id = er.event_id
        AND e.starts_at >= CURRENT_DATE - INTERVAL '30 days'
    LEFT JOIN planning_center.groups_attendances_relationships aer
        ON aer.relationship_type = 'Event' AND aer.relationship_id = e.event_id
    LEFT JOIN planning_center.groups_attendances a
        ON a.attendance_id = aer.attendance_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 mr_person.relationship_id) as unique_members,
        COUNT(DISTINCT CASE WHEN m.role = 'leader' THEN mr_person.relationship_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_relationships mr
        ON mr.relationship_id = g.group_id AND mr.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_memberships m
        ON m.membership_id = mr.membership_id
    LEFT JOIN planning_center.groups_memberships_relationships mr_person
        ON mr_person.membership_id = m.membership_id AND mr_person.relationship_type = 'Person'
    LEFT JOIN planning_center.groups_events_relationships er
        ON er.relationship_id = g.group_id
        AND er.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_events e
        ON e.event_id = er.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_relationships aer
        ON aer.relationship_type = 'Event' AND aer.relationship_id = e.event_id
    LEFT JOIN planning_center.groups_attendances a
        ON a.attendance_id = aer.attendance_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 mr_person.relationship_id 
        END) as new_members_this_year
    FROM planning_center.groups_groups g
    LEFT JOIN planning_center.groups_memberships_relationships mr
        ON mr.relationship_id = g.group_id AND mr.relationship_type = 'Group'
    LEFT JOIN planning_center.groups_memberships m
        ON m.membership_id = mr.membership_id
    LEFT JOIN planning_center.groups_memberships_relationships mr_person
        ON mr_person.membership_id = m.membership_id AND mr_person.relationship_type = 'Person'
)
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;

Export Tips

These reports can be exported in various formats:

  1. CSV Export: Add \copy (SELECT ...) TO 'report.csv' CSV HEADER;
  2. Excel-Ready: Most results can be copied directly into Excel
  3. Automated Delivery: Schedule these queries to run weekly/monthly
  4. Dashboard Integration: Use these queries as data sources for BI tools

Next Steps

Advanced Planning Center Groups Queries

Advanced Groups SQL combining multiple tables and window functions to analyze group health, attendance consistency, and member retention over time.

Planning Center People SQL Queries

Query Planning Center People data with SQL to understand your congregation: households, demographics, custom fields, and engagement across ministries.

On this page

Query RequirementsSchema PrefixRow Level Security (RLS)Executive Dashboard ReportWeekly Groups Executive SummaryMonthly Group Health ReportMember Engagement ReportsMember Participation AnalysisLeadership Development PipelineGroup Type Analysis ReportsGroup Type Performance ComparisonEvent Management ReportsWeekly Event Schedule ReportGrowth and Retention ReportsQuarterly Growth AnalysisMember Retention Cohort AnalysisLocation Analysis ReportGeographic Distribution and OptimizationYear-End Summary ReportAnnual Groups Ministry Impact ReportExport TipsNext Steps