Advanced Planning Center People Queries

Master complex SQL patterns for deep insights into your congregation. These queries combine multiple tables, use window functions, and employ advanced techniques for comprehensive analysis.

Query Customization Required

These example queries demonstrate common patterns but may require adjustments to match your specific database schema and field names. Test thoroughly in your environment before use.

Query Requirements

Schema Prefix

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

Row Level Security (RLS)

Row Level Security automatically filters results for:

tenant_organization_id – only your organization’s data
system_status – active records returned by default

Do not add these filters manually—RLS already enforces them and redundant predicates can hide data or slow execution:

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

Focus your filters on ministry-specific status, engagement, and demographic criteria while relying on RLS for tenancy and system status.

Demographic Analysis
Engagement Scoring
Family Analytics
Growth and Retention
Communication Optimization
Volunteer Management
Predictive Analytics
Performance Optimization

Demographic Analysis

Comprehensive Demographic Breakdown

-- Multi-dimensional demographic analysis
WITH demographic_data AS (
    SELECT 
        p.person_id,
        p.status,
        p.membership,
        -- Age calculations
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age,
        CASE 
            WHEN p.child = true THEN 'Child'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 18 THEN 'Youth'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 30 THEN 'Young Adult'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 50 THEN 'Adult'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 65 THEN 'Middle Age'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) >= 65 THEN 'Senior'
            ELSE 'Unknown'
        END as age_group,
        -- Gender
        COALESCE(p.gender, 'Not Specified') as gender,
        -- Marital status
        ms.value as marital_status,
        -- Campus
        c.name as campus,
        -- Household info
        CASE 
            WHEN h.member_count = 1 THEN 'Single'
            WHEN h.member_count = 2 THEN 'Couple'
            WHEN h.member_count <= 4 THEN 'Small Family'
            ELSE 'Large Family'
        END as household_type
    FROM planning_center.people_people p
    LEFT JOIN planning_center.people_people_relationships ms_r
        ON ms_r.person_id = p.person_id AND ms_r.relationship_type = 'MaritalStatus'
    LEFT JOIN planning_center.people_marital_statuses ms 
        ON ms.marital_status_id = ms_r.relationship_id
    LEFT JOIN planning_center.people_people_relationships pr 
        ON p.person_id = pr.person_id 
        AND pr.relationship_type = 'PrimaryCampus'
    LEFT JOIN planning_center.people_campuses c 
        ON pr.relationship_id = c.campus_id
    LEFT JOIN planning_center.people_households_relationships hhr 
        ON hhr.relationship_type = 'Person' AND hhr.relationship_id = p.person_id
    LEFT JOIN planning_center.people_households h 
        ON h.household_id = hhr.household_id
    WHERE p.status = 'active'
),
demographic_summary AS (
    SELECT 
        age_group,
        gender,
        COUNT(*) as count,
        ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage,
        ROUND(AVG(age), 1) as avg_age_in_group,
        COUNT(*) FILTER (WHERE membership = 'Member') as members,
        COUNT(*) FILTER (WHERE marital_status = 'Married') as married,
        COUNT(DISTINCT campus) as campus_representation
    FROM demographic_data
    GROUP BY age_group, gender
)
SELECT 
    age_group,
    gender,
    count,
    percentage || '%' as pct_of_total,
    avg_age_in_group,
    ROUND(members::numeric / count * 100, 1) || '%' as membership_rate,
    ROUND(married::numeric / count * 100, 1) || '%' as married_pct,
    campus_representation
FROM demographic_summary
ORDER BY 
    CASE age_group
        WHEN 'Child' THEN 1
        WHEN 'Youth' THEN 2
        WHEN 'Young Adult' THEN 3
        WHEN 'Adult' THEN 4
        WHEN 'Middle Age' THEN 5
        WHEN 'Senior' THEN 6
        ELSE 7
    END,
    gender;

Geographic Distribution Analysis

-- Analyze where your congregation lives
WITH address_analysis AS (
    SELECT 
        a.city,
        a.state,
        a.zip,
        COUNT(DISTINCT p.person_id) as people_count,
        COUNT(DISTINCT h.household_id) as household_count,
        AVG(EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate))) as avg_age,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.membership = 'Member') as members,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.child = true) as children
    FROM planning_center.people_people p
    JOIN planning_center.people_people_relationships ar 
        ON p.person_id = ar.person_id AND ar.relationship_type = 'Address'
    JOIN planning_center.people_addresses a 
        ON a.address_id = ar.relationship_id
    LEFT JOIN planning_center.people_households_relationships hhr 
        ON hhr.relationship_type = 'Person' AND hhr.relationship_id = p.person_id
    LEFT JOIN planning_center.people_households h 
        ON h.household_id = hhr.household_id
    WHERE p.status = 'active'
      AND a.is_primary = true
    GROUP BY a.city, a.state, a.zip
),
ranked_locations AS (
    SELECT 
        *,
        RANK() OVER (ORDER BY people_count DESC) as popularity_rank,
        SUM(people_count) OVER (ORDER BY people_count DESC) as cumulative_people,
        SUM(people_count) OVER () as total_people
    FROM address_analysis
)
SELECT 
    city,
    state,
    zip,
    people_count,
    household_count,
    ROUND(avg_age, 1) as avg_age,
    ROUND(members::numeric / people_count * 100, 1) || '%' as membership_rate,
    ROUND(children::numeric / people_count * 100, 1) || '%' as children_pct,
    popularity_rank,
    ROUND(cumulative_people::numeric / total_people * 100, 1) || '%' as cumulative_pct
FROM ranked_locations
WHERE popularity_rank <= 20
ORDER BY popularity_rank;

Engagement Scoring

This query is a custom SQL example. If you are looking for the engagement score your team sees in Parable, read Engagement Scoring first.

Multi-Factor Engagement Score

-- Calculate comprehensive engagement score for each person
WITH engagement_metrics AS (
    SELECT 
        p.person_id,
        p.name,
        p.membership,
        p.created_at,
        -- Tenure score (max 20 points)
        LEAST(EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.created_at)) * 4, 20) as tenure_score,
        -- Contact completeness (max 15 points)
        (CASE WHEN e.email_id IS NOT NULL THEN 5 ELSE 0 END +
         CASE WHEN pn.phone_number_id IS NOT NULL THEN 5 ELSE 0 END +
         CASE WHEN a.address_id IS NOT NULL THEN 5 ELSE 0 END) as contact_score,
        -- Household participation (max 15 points)
        CASE 
            WHEN h.household_id IS NOT NULL AND h.member_count > 1 THEN 15
            WHEN h.household_id IS NOT NULL THEN 10
            ELSE 0
        END as household_score,
        -- List memberships (max 20 points)
        LEAST(COUNT(DISTINCT lr.list_result_id) * 5, 20) as list_score,
        -- Form submissions (max 15 points)
        LEAST(COUNT(DISTINCT fs.form_submission_id) * 3, 15) as form_score,
        -- Workflow participation (max 15 points)
        LEAST(COUNT(DISTINCT wc.workflow_card_id) * 5, 15) as workflow_score
    FROM planning_center.people_people p
    -- Contact info
    LEFT JOIN planning_center.people_people_relationships er 
        ON p.person_id = er.person_id AND er.relationship_type = 'Email'
    LEFT JOIN planning_center.people_emails e 
        ON e.email_id = er.relationship_id AND e.is_primary = true
    LEFT JOIN planning_center.people_people_relationships pnr 
        ON p.person_id = pnr.person_id AND pnr.relationship_type = 'PhoneNumber'
    LEFT JOIN planning_center.people_phone_numbers pn 
        ON pn.phone_number_id = pnr.relationship_id AND pn.is_primary = true
    LEFT JOIN planning_center.people_people_relationships ar 
        ON p.person_id = ar.person_id AND ar.relationship_type = 'Address'
    LEFT JOIN planning_center.people_addresses a 
        ON a.address_id = ar.relationship_id AND a.is_primary = true
    -- Household
    LEFT JOIN planning_center.people_households_relationships hhr 
        ON hhr.relationship_type = 'Person' AND hhr.relationship_id = p.person_id
    LEFT JOIN planning_center.people_households h 
        ON h.household_id = hhr.household_id
    -- Lists
    LEFT JOIN planning_center.people_list_results_relationships lrr 
        ON lrr.relationship_type = 'Person' AND lrr.relationship_id = p.person_id
    LEFT JOIN planning_center.people_list_results lr 
        ON lr.list_result_id = lrr.list_result_id
    -- Forms
    LEFT JOIN planning_center.people_form_submissions_relationships fsr 
        ON fsr.relationship_type = 'Person' AND fsr.relationship_id = p.person_id
    LEFT JOIN planning_center.people_form_submissions fs 
        ON fs.form_submission_id = fsr.form_submission_id
    -- Workflows
    LEFT JOIN planning_center.people_workflow_cards_relationships wcr 
        ON wcr.relationship_type = 'Person' AND wcr.relationship_id = p.person_id
    LEFT JOIN planning_center.people_workflow_cards wc 
        ON wc.workflow_card_id = wcr.workflow_card_id
    WHERE p.status = 'active'
    GROUP BY 
        p.person_id, p.name, p.membership, p.created_at,
        e.email_id, pn.phone_number_id, a.address_id,
        h.household_id, h.member_count
),
scored_people AS (
    SELECT 
        person_id,
        name,
        membership,
        tenure_score,
        contact_score,
        household_score,
        list_score,
        form_score,
        workflow_score,
        tenure_score + contact_score + household_score + 
        list_score + form_score + workflow_score as total_score,
        CASE 
            WHEN tenure_score + contact_score + household_score + 
                 list_score + form_score + workflow_score >= 75 THEN 'Highly Engaged'
            WHEN tenure_score + contact_score + household_score + 
                 list_score + form_score + workflow_score >= 50 THEN 'Engaged'
            WHEN tenure_score + contact_score + household_score + 
                 list_score + form_score + workflow_score >= 25 THEN 'Moderately Engaged'
            WHEN tenure_score + contact_score + household_score + 
                 list_score + form_score + workflow_score >= 10 THEN 'Lightly Engaged'
            ELSE 'New/Inactive'
        END as engagement_level
    FROM engagement_metrics
)
SELECT 
    engagement_level,
    COUNT(*) as people_count,
    ROUND(AVG(total_score), 1) as avg_score,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage,
    COUNT(*) FILTER (WHERE membership = 'Member') as members_in_level
FROM scored_people
GROUP BY engagement_level
ORDER BY avg_score DESC;

Engagement Trajectory

-- Track engagement changes over time
WITH monthly_activity AS (
    SELECT 
        p.person_id,
        p.name,
        DATE_TRUNC('month', activity_date) as month,
        activity_type,
        COUNT(*) as activity_count
    FROM planning_center.people_people p
    CROSS JOIN LATERAL (
        -- Forms submitted
        SELECT fs.created_at as activity_date, 'Form' as activity_type
        FROM planning_center.people_form_submissions_relationships fsr
        JOIN planning_center.people_form_submissions fs 
            ON fs.form_submission_id = fsr.form_submission_id
        WHERE fsr.relationship_type = 'Person' AND fsr.relationship_id = p.person_id
        UNION ALL
        -- Workflow cards
        SELECT wc.created_at, 'Workflow'
        FROM planning_center.people_workflow_cards_relationships wcr
        JOIN planning_center.people_workflow_cards wc 
            ON wc.workflow_card_id = wcr.workflow_card_id
        WHERE wcr.relationship_type = 'Person' AND wcr.relationship_id = p.person_id
        UNION ALL
        -- Notes added
        SELECT n.created_at, 'Note'
        FROM planning_center.people_notes_relationships nr
        JOIN planning_center.people_notes n 
            ON n.note_id = nr.note_id
        WHERE nr.relationship_type = 'Person' AND nr.relationship_id = p.person_id
    ) activities
    WHERE p.status = 'active'
      AND activity_date >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY p.person_id, p.name, month, activity_type
),
engagement_trend AS (
    SELECT 
        person_id,
        name,
        month,
        SUM(activity_count) as total_activities,
        LAG(SUM(activity_count)) OVER (PARTITION BY person_id ORDER BY month) as prev_month_activities,
        AVG(SUM(activity_count)) OVER (PARTITION BY person_id) as avg_activities
    FROM monthly_activity
    GROUP BY person_id, name, month
)
SELECT 
    person_id,
    name,
    COUNT(DISTINCT month) as active_months,
    SUM(total_activities) as total_activities_6mo,
    ROUND(AVG(total_activities), 1) as avg_monthly_activities,
    MAX(total_activities) as peak_month_activities,
    CASE 
        WHEN SUM(CASE WHEN total_activities > prev_month_activities THEN 1 ELSE 0 END) > 
             SUM(CASE WHEN total_activities < prev_month_activities THEN 1 ELSE 0 END) 
        THEN 'Increasing'
        WHEN SUM(CASE WHEN total_activities < prev_month_activities THEN 1 ELSE 0 END) > 
             SUM(CASE WHEN total_activities > prev_month_activities THEN 1 ELSE 0 END)
        THEN 'Decreasing'
        ELSE 'Stable'
    END as engagement_trend
FROM engagement_trend
GROUP BY person_id, name
HAVING COUNT(DISTINCT month) >= 3  -- At least 3 months of data
ORDER BY total_activities_6mo DESC
LIMIT 100;

Family Analytics

Family Composition Analysis

-- Detailed family structure analysis
WITH family_composition AS (
    SELECT 
        h.household_id,
        h.name as family_name,
        h.member_count,
        COUNT(DISTINCT p.person_id) as actual_members,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.child = true) as children,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.child = false OR p.child IS NULL) as adults,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.gender = 'Male' AND (p.child = false OR p.child IS NULL)) as adult_males,
        COUNT(DISTINCT p.person_id) FILTER (WHERE p.gender = 'Female' AND (p.child = false OR p.child IS NULL)) as adult_females,
        MIN(p.birthdate) FILTER (WHERE p.child = false OR p.child IS NULL) as oldest_adult_birthdate,
        MAX(p.birthdate) FILTER (WHERE p.child = true) as youngest_child_birthdate,
        ARRAY_AGG(DISTINCT p.grade ORDER BY p.grade) FILTER (WHERE p.grade IS NOT NULL) as children_grades
    FROM planning_center.people_households h
    JOIN planning_center.people_households_relationships hhr 
        ON hhr.household_id = h.household_id AND LOWER(hhr.relationship_type) = 'person'
    JOIN planning_center.people_people p 
        ON p.person_id = hhr.relationship_id
    WHERE p.status = 'active'
    GROUP BY h.household_id, h.name, h.member_count
),
family_types AS (
    SELECT 
        *,
        CASE 
            WHEN adults = 1 AND children = 0 THEN 'Single Adult'
            WHEN adults = 2 AND children = 0 THEN 'Couple'
            WHEN adults = 1 AND children > 0 THEN 'Single Parent'
            WHEN adults = 2 AND children > 0 THEN 'Nuclear Family'
            WHEN adults > 2 AND children > 0 THEN 'Extended Family'
            WHEN adults > 2 AND children = 0 THEN 'Adult Household'
            ELSE 'Other'
        END as family_type,
        CASE 
            WHEN children = 0 THEN 'No Children'
            WHEN youngest_child_birthdate > CURRENT_DATE - INTERVAL '5 years' THEN 'Young Children'
            WHEN youngest_child_birthdate > CURRENT_DATE - INTERVAL '12 years' THEN 'Elementary Age'
            WHEN youngest_child_birthdate > CURRENT_DATE - INTERVAL '18 years' THEN 'Teenagers'
            ELSE 'Adult Children'
        END as children_stage
    FROM family_composition
)
SELECT 
    family_type,
    children_stage,
    COUNT(*) as family_count,
    ROUND(AVG(actual_members), 1) as avg_size,
    ROUND(AVG(children), 1) as avg_children,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
FROM family_types
GROUP BY family_type, children_stage
ORDER BY family_count DESC;

Multi-Generational Households

-- Identify multi-generational families
WITH household_ages AS (
    SELECT 
        h.household_id,
        h.name as household_name,
        p.person_id,
        p.name as person_name,
        p.birthdate,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age,
        CASE 
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 18 THEN 'Child'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 40 THEN 'Young Adult'
            WHEN EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) < 65 THEN 'Middle Age'
            ELSE 'Senior'
        END as generation
    FROM planning_center.people_households h
    JOIN planning_center.people_households_relationships hhr 
        ON hhr.household_id = h.household_id AND LOWER(hhr.relationship_type) = 'person'
    JOIN planning_center.people_people p 
        ON p.person_id = hhr.relationship_id
    WHERE p.status = 'active'
      AND p.birthdate IS NOT NULL
),
generation_analysis AS (
    SELECT 
        household_id,
        household_name,
        COUNT(DISTINCT generation) as generation_count,
        COUNT(DISTINCT person_id) as member_count,
        MAX(age) - MIN(age) as age_span,
        STRING_AGG(DISTINCT generation, ', ' ORDER BY generation) as generations_present,
        MIN(age) as youngest_age,
        MAX(age) as oldest_age
    FROM household_ages
    GROUP BY household_id, household_name
)
SELECT 
    household_id,
    household_name,
    member_count,
    generation_count,
    generations_present,
    age_span as age_span_years,
    youngest_age,
    oldest_age,
    CASE 
        WHEN generation_count >= 3 THEN 'Multi-Generational'
        WHEN generation_count = 2 AND age_span > 30 THEN 'Likely Multi-Gen'
        WHEN generation_count = 2 THEN 'Two Generations'
        ELSE 'Single Generation'
    END as household_type
FROM generation_analysis
WHERE generation_count >= 2
ORDER BY generation_count DESC, age_span DESC;

Growth and Retention

Cohort Retention Analysis

-- Track retention by cohort over time
WITH cohort_base AS (
    SELECT 
        DATE_TRUNC('month', created_at) as cohort_month,
        person_id,
        created_at,
        status,
        inactivated_at
    FROM planning_center.people_people
    WHERE created_at >= CURRENT_DATE - INTERVAL '24 months'
),
retention_calc AS (
    SELECT 
        cohort_month,
        COUNT(DISTINCT person_id) as cohort_size,
        COUNT(DISTINCT person_id) FILTER (
            WHERE status = 'active' 
            OR inactivated_at > cohort_month + INTERVAL '1 month'
        ) as month_1,
        COUNT(DISTINCT person_id) FILTER (
            WHERE status = 'active' 
            OR inactivated_at > cohort_month + INTERVAL '3 months'
        ) as month_3,
        COUNT(DISTINCT person_id) FILTER (
            WHERE status = 'active' 
            OR inactivated_at > cohort_month + INTERVAL '6 months'
        ) as month_6,
        COUNT(DISTINCT person_id) FILTER (
            WHERE status = 'active' 
            OR inactivated_at > cohort_month + INTERVAL '12 months'
        ) as month_12,
        COUNT(DISTINCT person_id) FILTER (
            WHERE status = 'active'
        ) as still_active
    FROM cohort_base
    GROUP BY cohort_month
)
SELECT 
    TO_CHAR(cohort_month, 'YYYY-MM') as cohort,
    cohort_size as started,
    ROUND(month_1::numeric / cohort_size * 100, 1) as pct_retained_1mo,
    ROUND(month_3::numeric / cohort_size * 100, 1) as pct_retained_3mo,
    ROUND(month_6::numeric / cohort_size * 100, 1) as pct_retained_6mo,
    ROUND(month_12::numeric / cohort_size * 100, 1) as pct_retained_12mo,
    ROUND(still_active::numeric / cohort_size * 100, 1) as pct_still_active,
    still_active as currently_active
FROM retention_calc
WHERE cohort_month <= CURRENT_DATE - INTERVAL '1 month'
ORDER BY cohort_month DESC;

Growth Velocity Analysis

-- Analyze growth patterns and velocity
WITH weekly_metrics AS (
    SELECT 
        DATE_TRUNC('week', d.date) as week,
        -- New people
        COUNT(DISTINCT p.person_id) FILTER (
            WHERE DATE_TRUNC('week', p.created_at) = DATE_TRUNC('week', d.date)
        ) as new_people,
        -- Inactivated people
        COUNT(DISTINCT p.person_id) FILTER (
            WHERE DATE_TRUNC('week', p.inactivated_at) = DATE_TRUNC('week', d.date)
        ) as inactivated_people,
        -- Total active at end of week
        COUNT(DISTINCT p.person_id) FILTER (
            WHERE p.created_at <= d.date + INTERVAL '6 days'
            AND (p.inactivated_at IS NULL OR p.inactivated_at > d.date + INTERVAL '6 days')
        ) as total_active
    FROM generate_series(
        CURRENT_DATE - INTERVAL '12 weeks',
        CURRENT_DATE,
        INTERVAL '1 week'
    ) d(date)
    CROSS JOIN planning_center.people_people p
    GROUP BY DATE_TRUNC('week', d.date)
),
growth_analysis AS (
    SELECT 
        week,
        new_people,
        inactivated_people,
        new_people - inactivated_people as net_growth,
        total_active,
        LAG(total_active) OVER (ORDER BY week) as prev_week_active,
        AVG(new_people) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as avg_new_4wk,
        AVG(new_people - inactivated_people) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as avg_net_4wk
    FROM weekly_metrics
)
SELECT 
    TO_CHAR(week, 'YYYY-MM-DD') as week_starting,
    new_people,
    inactivated_people,
    net_growth,
    total_active,
    ROUND((total_active - prev_week_active)::numeric / NULLIF(prev_week_active, 0) * 100, 2) as weekly_growth_rate,
    ROUND(avg_new_4wk, 1) as rolling_avg_new,
    ROUND(avg_net_4wk, 1) as rolling_avg_net,
    CASE 
        WHEN net_growth > avg_net_4wk * 1.5 THEN 'Accelerating'
        WHEN net_growth > avg_net_4wk THEN 'Above Average'
        WHEN net_growth > 0 THEN 'Growing'
        WHEN net_growth = 0 THEN 'Flat'
        ELSE 'Declining'
    END as growth_status
FROM growth_analysis
WHERE week <= CURRENT_DATE
ORDER BY week DESC;

Communication Optimization

Communication Preference Analysis

-- Analyze best communication channels by demographic
WITH communication_channels AS (
    SELECT 
        p.person_id,
        p.name,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age,
        p.gender,
        p.membership,
        -- Email status
        CASE 
            WHEN e.email_id IS NOT NULL AND e.blocked = false THEN 'Has Email'
            WHEN e.email_id IS NOT NULL AND e.blocked = true THEN 'Email Blocked'
            ELSE 'No Email'
        END as email_status,
        -- Phone status
        CASE 
            WHEN pn.phone_number_id IS NOT NULL AND pn.carrier IS NOT NULL THEN 'SMS Capable'
            WHEN pn.phone_number_id IS NOT NULL THEN 'Voice Only'
            ELSE 'No Phone'
        END as phone_status,
        -- Physical mail
        CASE 
            WHEN a.address_id IS NOT NULL THEN 'Has Address'
            ELSE 'No Address'
        END as mail_status
    FROM planning_center.people_people p
    LEFT JOIN planning_center.people_people_relationships er 
        ON p.person_id = er.person_id AND er.relationship_type = 'Email'
    LEFT JOIN planning_center.people_emails e 
        ON e.email_id = er.relationship_id AND e.is_primary = true
    LEFT JOIN planning_center.people_people_relationships pnr 
        ON p.person_id = pnr.person_id AND pnr.relationship_type = 'PhoneNumber'
    LEFT JOIN planning_center.people_phone_numbers pn 
        ON pn.phone_number_id = pnr.relationship_id AND pn.is_primary = true
    LEFT JOIN planning_center.people_people_relationships ar 
        ON p.person_id = ar.person_id AND ar.relationship_type = 'Address'
    LEFT JOIN planning_center.people_addresses a 
        ON a.address_id = ar.relationship_id AND a.is_primary = true
    WHERE p.status = 'active'
),
channel_summary AS (
    SELECT 
        CASE 
            WHEN age < 30 THEN 'Under 30'
            WHEN age < 50 THEN '30-49'
            WHEN age < 70 THEN '50-69'
            WHEN age >= 70 THEN '70+'
            ELSE 'Unknown'
        END as age_group,
        COUNT(*) as total_people,
        COUNT(*) FILTER (WHERE email_status = 'Has Email') as has_email,
        COUNT(*) FILTER (WHERE phone_status = 'SMS Capable') as has_sms,
        COUNT(*) FILTER (WHERE phone_status IN ('SMS Capable', 'Voice Only')) as has_phone,
        COUNT(*) FILTER (WHERE mail_status = 'Has Address') as has_address,
        -- Best channel determination
        COUNT(*) FILTER (WHERE email_status = 'Has Email' AND phone_status = 'SMS Capable') as multi_channel,
        COUNT(*) FILTER (WHERE email_status != 'Has Email' AND phone_status != 'SMS Capable' AND mail_status = 'Has Address') as mail_only
    FROM communication_channels
    GROUP BY age_group
)
SELECT 
    age_group,
    total_people,
    ROUND(has_email::numeric / total_people * 100, 1) || '%' as email_reach,
    ROUND(has_sms::numeric / total_people * 100, 1) || '%' as sms_reach,
    ROUND(has_phone::numeric / total_people * 100, 1) || '%' as phone_reach,
    ROUND(has_address::numeric / total_people * 100, 1) || '%' as mail_reach,
    ROUND(multi_channel::numeric / total_people * 100, 1) || '%' as multi_channel_pct,
    mail_only as mail_only_count,
    CASE 
        WHEN has_sms::numeric / total_people > 0.7 THEN 'SMS Preferred'
        WHEN has_email::numeric / total_people > 0.8 THEN 'Email Preferred'
        WHEN has_phone::numeric / total_people > 0.9 THEN 'Phone Preferred'
        ELSE 'Mixed Channels'
    END as recommended_primary
FROM channel_summary
ORDER BY 
    CASE age_group
        WHEN 'Under 30' THEN 1
        WHEN '30-49' THEN 2
        WHEN '50-69' THEN 3
        WHEN '70+' THEN 4
        ELSE 5
    END;

Volunteer Management

Volunteer Capacity Analysis

-- Identify volunteer capacity and opportunities
-- Each person can have many list results, workflow cards and field values.
-- Joining all of them in one pass multiplies the rows together, which is slow
-- enough to time out on a large database. Summarize each dimension separately,
-- then join the summaries.
WITH background AS (
    SELECT
        bcr.relationship_id as person_id,
        -- Planning Center reports a family of statuses, not a simple
        -- pass/fail: *_clear is a pass, *_not_clear is a fail, and the rest
        -- are still in flight. There is no 'passed' value.
        MAX(CASE
            WHEN bc.status IN ('manual_clear', 'report_clear', 'complete_clear') THEN 1
            ELSE 0
        END) as has_clear,
        MAX(CASE
            WHEN bc.status IN ('manual_not_clear', 'complete_not_clear') THEN 1
            ELSE 0
        END) as has_not_clear,
        MAX(CASE
            WHEN bc.status IN ('report_consider', 'report_suspended') THEN 1
            ELSE 0
        END) as needs_review,
        COUNT(*) as check_count
    FROM planning_center.people_background_checks_relationships bcr
    JOIN planning_center.people_background_checks bc
        ON bc.background_check_id = bcr.background_check_id
    WHERE bcr.relationship_type = 'Person'
    GROUP BY bcr.relationship_id
),
list_counts AS (
    SELECT
        lrr.relationship_id as person_id,
        COUNT(DISTINCT lrr.list_result_id) as list_memberships
    FROM planning_center.people_list_results_relationships lrr
    WHERE lrr.relationship_type = 'Person'
    GROUP BY lrr.relationship_id
),
workflow_counts AS (
    SELECT
        wcr.relationship_id as person_id,
        COUNT(DISTINCT wc.workflow_card_id) FILTER (WHERE wc.completed_at IS NOT NULL) as completed_workflows
    FROM planning_center.people_workflow_cards_relationships wcr
    JOIN planning_center.people_workflow_cards wc
        ON wc.workflow_card_id = wcr.workflow_card_id
    WHERE wcr.relationship_type = 'Person'
    GROUP BY wcr.relationship_id
),
custom_fields AS (
    SELECT
        fdr.relationship_id as person_id,
        MAX(CASE WHEN fd.name = 'Skills' THEN fdat.value END) as skills,
        MAX(CASE WHEN fd.name = 'Availability' THEN fdat.value END) as availability
    FROM planning_center.people_field_data_relationships fdr
    JOIN planning_center.people_field_data fdat
        ON fdat.field_data_id = fdr.field_data_id
    JOIN planning_center.people_field_data_relationships fdr_fd
        ON fdr_fd.field_data_id = fdat.field_data_id
        AND fdr_fd.relationship_type = 'FieldDefinition'
    JOIN planning_center.people_field_definitions fd
        ON fd.field_definition_id = fdr_fd.relationship_id
        AND fd.name IN ('Skills', 'Availability')
    WHERE fdr.relationship_type = 'Person'
    GROUP BY fdr.relationship_id
),
volunteer_data AS (
    SELECT 
        p.person_id,
        p.name,
        p.membership,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age,
        CASE 
            WHEN b.has_clear = 1 THEN 'Cleared'
            WHEN b.has_not_clear = 1 THEN 'Not Cleared'
            WHEN b.needs_review = 1 THEN 'Needs Review'
            WHEN b.check_count > 0 THEN 'In Progress'
            ELSE 'Not Checked'
        END as background_status,
        COALESCE(lc.list_memberships, 0) as list_memberships,
        COALESCE(wcnt.completed_workflows, 0) as completed_workflows,
        cf.skills,
        cf.availability
    FROM planning_center.people_people p
    LEFT JOIN background b ON b.person_id = p.person_id
    LEFT JOIN list_counts lc ON lc.person_id = p.person_id
    LEFT JOIN workflow_counts wcnt ON wcnt.person_id = p.person_id
    LEFT JOIN custom_fields cf ON cf.person_id = p.person_id
    WHERE p.status = 'active'
      AND p.child = false  -- Adults only
      AND (p.membership = 'Member' OR COALESCE(lc.list_memberships, 0) > 0)
),
volunteer_segments AS (
    SELECT 
        person_id,
        name,
        age,
        background_status,
        list_memberships,
        completed_workflows,
        CASE 
            WHEN list_memberships >= 3 THEN 'Highly Active'
            WHEN list_memberships >= 1 THEN 'Active'
            WHEN membership = 'Member' THEN 'Available'
            ELSE 'Potential'
        END as volunteer_status,
        CASE 
            WHEN background_status = 'Cleared' AND age >= 18 THEN 'Children/Youth Ready'
            WHEN age >= 18 AND age < 65 THEN 'General Service Ready'
            WHEN age >= 65 THEN 'Senior Service Ready'
            ELSE 'Not Ready'
        END as service_readiness
    FROM volunteer_data
)
SELECT 
    volunteer_status,
    service_readiness,
    COUNT(*) as people_count,
    ROUND(AVG(age), 1) as avg_age,
    COUNT(*) FILTER (WHERE background_status = 'Cleared') as background_cleared,
    ROUND(AVG(list_memberships), 1) as avg_involvements,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
FROM volunteer_segments
GROUP BY volunteer_status, service_readiness
ORDER BY 
    CASE volunteer_status
        WHEN 'Highly Active' THEN 1
        WHEN 'Active' THEN 2
        WHEN 'Available' THEN 3
        ELSE 4
    END,
    service_readiness;

Predictive Analytics

Churn Risk Prediction

-- Identify people at risk of becoming inactive
-- Forms, workflow cards, notes and list results are all one-to-many against a
-- person. Joining them together multiplies the rows, so each is summarized on
-- its own before being joined back.
WITH form_activity AS (
    SELECT
        fsr.relationship_id as person_id,
        MAX(fs.created_at) as last_form_submission,
        COUNT(DISTINCT DATE_TRUNC('month', fs.created_at)) as active_months_forms
    FROM planning_center.people_form_submissions_relationships fsr
    JOIN planning_center.people_form_submissions fs
        ON fs.form_submission_id = fsr.form_submission_id
    WHERE fsr.relationship_type = 'Person'
    GROUP BY fsr.relationship_id
),
workflow_activity AS (
    SELECT
        wcr.relationship_id as person_id,
        MAX(wc.created_at) as last_workflow_activity,
        COUNT(DISTINCT DATE_TRUNC('month', wc.created_at)) as active_months_workflows
    FROM planning_center.people_workflow_cards_relationships wcr
    JOIN planning_center.people_workflow_cards wc
        ON wc.workflow_card_id = wcr.workflow_card_id
    WHERE wcr.relationship_type = 'Person'
    GROUP BY wcr.relationship_id
),
note_activity AS (
    SELECT
        nr.relationship_id as person_id,
        MAX(n.created_at) as last_note
    FROM planning_center.people_notes_relationships nr
    JOIN planning_center.people_notes n
        ON n.note_id = nr.note_id
    WHERE nr.relationship_type = 'Person'
    GROUP BY nr.relationship_id
),
list_activity AS (
    SELECT
        lrr.relationship_id as person_id,
        COUNT(DISTINCT lrr.list_result_id) as list_count
    FROM planning_center.people_list_results_relationships lrr
    WHERE lrr.relationship_type = 'Person'
    GROUP BY lrr.relationship_id
),
household_membership AS (
    SELECT DISTINCT relationship_id as person_id
    FROM planning_center.people_households_relationships
    WHERE relationship_type = 'Person'
),
activity_metrics AS (
    SELECT 
        p.person_id,
        p.name,
        p.created_at,
        p.membership,
        fa.last_form_submission,
        wa.last_workflow_activity,
        na.last_note,
        COALESCE(fa.active_months_forms, 0) as active_months_forms,
        COALESCE(wa.active_months_workflows, 0) as active_months_workflows,
        COALESCE(la.list_count, 0) as list_count,
        CASE WHEN hm.person_id IS NOT NULL THEN 1 ELSE 0 END as has_household
    FROM planning_center.people_people p
    LEFT JOIN form_activity fa ON fa.person_id = p.person_id
    LEFT JOIN workflow_activity wa ON wa.person_id = p.person_id
    LEFT JOIN note_activity na ON na.person_id = p.person_id
    LEFT JOIN list_activity la ON la.person_id = p.person_id
    LEFT JOIN household_membership hm ON hm.person_id = p.person_id
    WHERE p.status = 'active'
      AND p.created_at < CURRENT_DATE - INTERVAL '90 days'  -- Established people only
),
risk_scoring AS (
    SELECT 
        person_id,
        name,
        membership,
        -- Risk factors
        CASE WHEN COALESCE(last_form_submission, last_workflow_activity, last_note) < CURRENT_DATE - INTERVAL '90 days' 
             OR COALESCE(last_form_submission, last_workflow_activity, last_note) IS NULL 
             THEN 3 ELSE 0 END as inactivity_risk,
        CASE WHEN list_count = 0 THEN 2 ELSE 0 END as disconnection_risk,
        CASE WHEN has_household = 0 THEN 1 ELSE 0 END as isolation_risk,
        CASE WHEN active_months_forms + active_months_workflows < 3 THEN 2 ELSE 0 END as low_engagement_risk,
        -- Last activity
        GREATEST(
            COALESCE(last_form_submission, '1900-01-01'::timestamp),
            COALESCE(last_workflow_activity, '1900-01-01'::timestamp),
            COALESCE(last_note, '1900-01-01'::timestamp)
        ) as last_activity,
        -- Engagement score
        active_months_forms + active_months_workflows as total_active_months,
        list_count
    FROM activity_metrics
)
SELECT 
    person_id,
    name,
    membership,
    TO_CHAR(last_activity, 'YYYY-MM-DD') as last_seen,
    CURRENT_DATE - last_activity::date as days_inactive,
    inactivity_risk + disconnection_risk + isolation_risk + low_engagement_risk as total_risk_score,
    CASE 
        WHEN inactivity_risk + disconnection_risk + isolation_risk + low_engagement_risk >= 5 THEN 'High Risk'
        WHEN inactivity_risk + disconnection_risk + isolation_risk + low_engagement_risk >= 3 THEN 'Medium Risk'
        WHEN inactivity_risk + disconnection_risk + isolation_risk + low_engagement_risk >= 1 THEN 'Low Risk'
        ELSE 'Stable'
    END as risk_level,
    ARRAY_REMOVE(ARRAY[
        CASE WHEN inactivity_risk > 0 THEN 'Long Inactivity' END,
        CASE WHEN disconnection_risk > 0 THEN 'No Groups/Lists' END,
        CASE WHEN isolation_risk > 0 THEN 'No Household' END,
        CASE WHEN low_engagement_risk > 0 THEN 'Low Engagement' END
    ], NULL) as risk_factors
FROM risk_scoring
WHERE inactivity_risk + disconnection_risk + isolation_risk + low_engagement_risk > 0
ORDER BY total_risk_score DESC, days_inactive DESC
LIMIT 100;

Performance Optimization

Dashboard Metrics Rollup

Your Parable database connection is read-only. You cannot create materialized views or indexes through it. Run this query directly, schedule it as a Parable report, or let your BI tool cache the result set.

-- Frequently accessed people metrics — schedule as a report or BI dataset
WITH base_metrics AS (
    SELECT 
        p.person_id,
        p.status,
        p.membership,
        p.child,
        p.gender,
        p.created_at,
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birthdate)) as age,
        -- Campus
        c.name as campus,
        -- Household
        h.member_count as household_size,
        -- Contact completeness
        CASE WHEN e.email_id IS NOT NULL THEN 1 ELSE 0 END as has_email,
        CASE WHEN pn.phone_number_id IS NOT NULL THEN 1 ELSE 0 END as has_phone,
        CASE WHEN a.address_id IS NOT NULL THEN 1 ELSE 0 END as has_address
    FROM planning_center.people_people p
    LEFT JOIN planning_center.people_people_relationships pr 
        ON p.person_id = pr.person_id AND pr.relationship_type = 'PrimaryCampus'
    LEFT JOIN planning_center.people_campuses c 
        ON pr.relationship_id = c.campus_id
    LEFT JOIN planning_center.people_households_relationships hhr 
        ON hhr.relationship_type = 'Person' AND hhr.relationship_id = p.person_id
    LEFT JOIN planning_center.people_households h 
        ON h.household_id = hhr.household_id
    LEFT JOIN planning_center.people_people_relationships er 
        ON p.person_id = er.person_id AND er.relationship_type = 'Email'
    LEFT JOIN planning_center.people_emails e 
        ON e.email_id = er.relationship_id AND e.is_primary = true
    LEFT JOIN planning_center.people_people_relationships pnr 
        ON p.person_id = pnr.person_id AND pnr.relationship_type = 'PhoneNumber'
    LEFT JOIN planning_center.people_phone_numbers pn 
        ON pn.phone_number_id = pnr.relationship_id AND pn.is_primary = true
    LEFT JOIN planning_center.people_people_relationships ar 
        ON p.person_id = ar.person_id AND ar.relationship_type = 'Address'
    LEFT JOIN planning_center.people_addresses a 
        ON a.address_id = ar.relationship_id AND a.is_primary = true
)
SELECT 
    COUNT(*) FILTER (WHERE status = 'active') as total_active,
    COUNT(*) FILTER (WHERE status = 'inactive') as total_inactive,
    COUNT(*) FILTER (WHERE membership = 'Member') as total_members,
    COUNT(*) FILTER (WHERE child = true) as total_children,
    COUNT(*) FILTER (WHERE gender = 'Male') as total_males,
    COUNT(*) FILTER (WHERE gender = 'Female') as total_females,
    AVG(age) FILTER (WHERE age IS NOT NULL) as avg_age,
    COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '30 days') as new_last_30_days,
    COUNT(*) FILTER (WHERE has_email = 1) as with_email,
    COUNT(*) FILTER (WHERE has_phone = 1) as with_phone,
    COUNT(*) FILTER (WHERE has_address = 1) as with_address,
    COUNT(DISTINCT campus) as campus_count,
    AVG(household_size) as avg_household_size,
    CURRENT_TIMESTAMP as last_refreshed
FROM base_metrics;

Best Practices

Use CTEs for Clarity: Break complex queries into logical steps
Leverage Window Functions: Use OVER() for running totals and comparisons
Filter Early: Apply WHERE clauses as early as possible
Index Strategic Columns: Ensure frequently joined/filtered columns are indexed
Monitor Query Performance: Use EXPLAIN ANALYZE for optimization

Next Steps

Apply these advanced queries to real ministry scenarios:

Reporting Examples - Practical ministry applications and reports

Getting started

Release Notes

Giving Engagement

Planning Center

Advanced Planning Center People Queries

Query Customization Required

Query Requirements

Schema Prefix

Row Level Security (RLS)

Table of Contents

Demographic Analysis

Comprehensive Demographic Breakdown

Geographic Distribution Analysis

Engagement Scoring

Multi-Factor Engagement Score

Engagement Trajectory

Family Analytics

Family Composition Analysis

Multi-Generational Households

Growth and Retention

Cohort Retention Analysis

Growth Velocity Analysis

Communication Optimization

Communication Preference Analysis

Volunteer Management

Volunteer Capacity Analysis

Predictive Analytics

Churn Risk Prediction

Performance Optimization

Dashboard Metrics Rollup

Best Practices

Next Steps

Query Customization Required

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Table of Contents

​Demographic Analysis

​Comprehensive Demographic Breakdown

​Geographic Distribution Analysis

​Engagement Scoring

​Multi-Factor Engagement Score

​Engagement Trajectory

​Family Analytics

​Family Composition Analysis

​Multi-Generational Households

​Growth and Retention

​Cohort Retention Analysis

​Growth Velocity Analysis

​Communication Optimization

​Communication Preference Analysis

​Volunteer Management

​Volunteer Capacity Analysis

​Predictive Analytics

​Churn Risk Prediction

​Performance Optimization

​Dashboard Metrics Rollup

​Best Practices

​Next Steps

Query Requirements

Schema Prefix

Row Level Security (RLS)

Table of Contents

Demographic Analysis

Comprehensive Demographic Breakdown

Geographic Distribution Analysis

Engagement Scoring

Multi-Factor Engagement Score

Engagement Trajectory

Family Analytics

Family Composition Analysis

Multi-Generational Households

Growth and Retention

Cohort Retention Analysis

Growth Velocity Analysis

Communication Optimization

Communication Preference Analysis

Volunteer Management

Volunteer Capacity Analysis

Predictive Analytics

Churn Risk Prediction

Performance Optimization

Dashboard Metrics Rollup

Best Practices

Next Steps