Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.getparable.io/llms.txt

Use this file to discover all available pages before exploring further.

​
Advanced Giving Queries

Ready to unlock deeper insights from your giving data? These advanced queries will help you perform complex analysis, track trends, and generate sophisticated reports.

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 Giving module live in the planning_center schema. Always prefix table names with planning_center. in your queries. ✅ CORRECT: SELECT * FROM planning_center.giving_donations ❌ INCORRECT: SELECT * FROM giving_donations

​
Row Level Security (RLS)

Row Level Security automatically scopes results by:
  • tenant_organization_id – only data from your organization
  • 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'
Keep your attention on domain-specific filters (date ranges, refunded flags, fund logic) while RLS handles tenancy and system status.

​
Table of Contents

​
Complex Joins and Relationships

​
Complete Donation Details with All Relationships

This query brings together donations, donors, funds, batches, and campuses: 
-- Comprehensive donation view with all related data
WITH donation_details AS (
    SELECT 
        d.donation_id,
        d.amount_cents / 100.0 as donation_amount,
        d.payment_method,
        d.received_at,
        d.fee_cents / 100.0 as fee_amount,
        d.refunded,
        -- Get person details
        p.person_id,
        p.first_name,
        p.last_name,
        p.donor_number,
        -- Get batch details
        b.batch_id,
        b.description as batch_description,
        b.total_cents / 100.0 as batch_total,
        -- Get campus details
        c.campus_id,
        c.name as campus_name
    FROM planning_center.giving_donations d
    -- Join to person
    LEFT JOIN planning_center.giving_donation_relationships dr_person
        ON d.donation_id = dr_person.donation_id
        AND dr_person.relationship_type = 'Person'
    LEFT JOIN planning_center.giving_people p
        ON dr_person.relationship_id = p.person_id
    -- Join to batch
    LEFT JOIN planning_center.giving_donation_relationships dr_batch
        ON d.donation_id = dr_batch.donation_id
        AND dr_batch.relationship_type = 'Batch'
    LEFT JOIN planning_center.giving_batches b
        ON dr_batch.relationship_id = b.batch_id
    -- Join to campus
    LEFT JOIN planning_center.giving_donation_relationships dr_campus
        ON d.donation_id = dr_campus.donation_id
        AND dr_campus.relationship_type = 'Campus'
    LEFT JOIN planning_center.giving_campuses c
        ON dr_campus.relationship_id = c.campus_id
)
SELECT * FROM donation_details
WHERE received_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY received_at DESC;

​
Multi-Fund Donations Analysis

Find donations that were split across multiple funds: 
-- Donations split across multiple funds
WITH donation_fund_counts AS (
    SELECT 
        d.donation_id,
        d.amount_cents / 100.0 as total_amount,
        d.received_at,
        COUNT(DISTINCT des.fund_id) as num_funds,
        STRING_AGG(f.name, ', ' ORDER BY des.amount_cents DESC) as fund_names
    FROM planning_center.giving_donations d
    JOIN planning_center.giving_designation_relationships dr
        ON d.donation_id = dr.designation_id
        AND dr.relationship_type = 'Donation'
    JOIN planning_center.giving_designations des
        ON dr.relationship_id = des.designation_id
    JOIN planning_center.giving_funds f
        ON des.fund_id = f.fund_id
    WHERE d.received_at >= CURRENT_DATE - INTERVAL '90 days'
        AND d.refunded = false
    GROUP BY d.donation_id, d.amount_cents, d.received_at
)
SELECT *
FROM donation_fund_counts
WHERE num_funds > 1  -- Only multi-fund donations
ORDER BY total_amount DESC;

​
Time-Based Analysis

​
Year-Over-Year Comparison by Month

-- Compare giving by month across multiple years
WITH monthly_giving AS (
    SELECT 
        DATE_TRUNC('month', received_at) as month,
        EXTRACT(YEAR FROM received_at) as year,
        EXTRACT(MONTH FROM received_at) as month_num,
        TO_CHAR(received_at, 'Month') as month_name,
        COUNT(*) as donation_count,
        COUNT(DISTINCT dr.relationship_id) as unique_donors,
        SUM(d.amount_cents) / 100.0 as total_amount
    FROM planning_center.giving_donations d
    LEFT JOIN planning_center.giving_donation_relationships dr
        ON d.donation_id = dr.donation_id
        AND dr.relationship_type = 'Person'
    WHERE d.refunded = false
        AND d.received_at >= DATE_TRUNC('year', CURRENT_DATE - INTERVAL '2 years')
    GROUP BY DATE_TRUNC('month', received_at), 
             EXTRACT(YEAR FROM received_at),
             EXTRACT(MONTH FROM received_at),
             TO_CHAR(received_at, 'Month')
)
SELECT 
    month_name,
    month_num,
    MAX(CASE WHEN year = EXTRACT(YEAR FROM CURRENT_DATE) - 2 THEN total_amount END) as two_years_ago,
    MAX(CASE WHEN year = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN total_amount END) as last_year,
    MAX(CASE WHEN year = EXTRACT(YEAR FROM CURRENT_DATE) THEN total_amount END) as this_year,
    -- Calculate year-over-year growth
    ROUND(
        ((MAX(CASE WHEN year = EXTRACT(YEAR FROM CURRENT_DATE) THEN total_amount END) / 
          NULLIF(MAX(CASE WHEN year = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN total_amount END), 0)) - 1) * 100, 
        2
    ) as yoy_growth_percent
FROM monthly_giving
GROUP BY month_name, month_num
ORDER BY month_num;

-- Calculate 12-month rolling average
WITH monthly_totals AS (
    SELECT 
        DATE_TRUNC('month', received_at) as month,
        SUM(amount_cents) / 100.0 as monthly_total,
        COUNT(*) as donation_count
    FROM planning_center.giving_donations
    WHERE refunded = false
        AND received_at >= CURRENT_DATE - INTERVAL '24 months'
    GROUP BY DATE_TRUNC('month', received_at)
),
rolling_averages AS (
    SELECT 
        month,
        monthly_total,
        donation_count,
        AVG(monthly_total) OVER (
            ORDER BY month 
            ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
        ) as rolling_12m_avg,
        SUM(monthly_total) OVER (
            ORDER BY month 
            ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
        ) as rolling_12m_total
    FROM monthly_totals
)
SELECT 
    month,
    monthly_total,
    rolling_12m_avg,
    rolling_12m_total,
    ROUND(((monthly_total / NULLIF(rolling_12m_avg, 0)) - 1) * 100, 2) as pct_vs_12m_avg
FROM rolling_averages
WHERE month >= CURRENT_DATE - INTERVAL '12 months'
ORDER BY month DESC;

​
Donor Segmentation

​
Donor Lifecycle Analysis

Categorize donors by their giving patterns: 
-- Segment donors by giving frequency and recency
WITH donor_metrics AS (
    SELECT 
        p.person_id,
        p.first_name,
        p.last_name,
        COUNT(DISTINCT d.donation_id) as total_donations,
        SUM(d.amount_cents) / 100.0 as lifetime_giving,
        MIN(d.received_at) as first_donation_date,
        MAX(d.received_at) as last_donation_date,
        CURRENT_DATE - MAX(d.received_at)::date as days_since_last_donation,
        COUNT(DISTINCT DATE_TRUNC('month', d.received_at)) as months_given
    FROM planning_center.giving_people p
    JOIN planning_center.giving_donation_relationships dr
        ON p.person_id = dr.relationship_id
        AND dr.relationship_type = 'Person'
    JOIN planning_center.giving_donations d
        ON dr.donation_id = d.donation_id
    WHERE d.refunded = false
    GROUP BY p.person_id, p.first_name, p.last_name
),
donor_segments AS (
    SELECT 
        *,
        CASE 
            WHEN days_since_last_donation <= 90 AND months_given >= 10 THEN 'Champion'
            WHEN days_since_last_donation <= 90 AND months_given >= 6 THEN 'Loyal'
            WHEN days_since_last_donation <= 90 AND months_given >= 3 THEN 'Developing'
            WHEN days_since_last_donation <= 90 THEN 'New'
            WHEN days_since_last_donation <= 180 THEN 'At Risk'
            WHEN days_since_last_donation <= 365 THEN 'Lapsed'
            ELSE 'Lost'
        END as donor_segment,
        CASE 
            WHEN lifetime_giving >= 10000 THEN 'Major'
            WHEN lifetime_giving >= 5000 THEN 'Mid-Level'
            WHEN lifetime_giving >= 1000 THEN 'Regular'
            ELSE 'Small'
        END as giving_level
    FROM donor_metrics
)
SELECT 
    donor_segment,
    giving_level,
    COUNT(*) as donor_count,
    AVG(lifetime_giving) as avg_lifetime_giving,
    SUM(lifetime_giving) as total_lifetime_giving
FROM donor_segments
GROUP BY donor_segment, giving_level
ORDER BY donor_segment, giving_level DESC;

​
First-Time Donor Retention

Track how many first-time donors give again: 
-- First-time donor retention analysis
WITH first_donations AS (
    SELECT 
        dr.relationship_id as person_id,
        MIN(d.received_at) as first_donation_date
    FROM planning_center.giving_donations d
    JOIN planning_center.giving_donation_relationships dr
        ON d.donation_id = dr.donation_id
        AND dr.relationship_type = 'Person'
    WHERE d.refunded = false
    GROUP BY dr.relationship_id
),
second_donations AS (
    SELECT 
        fd.person_id,
        fd.first_donation_date,
        MIN(d.received_at) as second_donation_date
    FROM first_donations fd
    JOIN planning_center.giving_donation_relationships dr
        ON fd.person_id = dr.relationship_id
        AND dr.relationship_type = 'Person'
    JOIN planning_center.giving_donations d
        ON dr.donation_id = d.donation_id
    WHERE d.received_at > fd.first_donation_date
        AND d.refunded = false
    GROUP BY fd.person_id, fd.first_donation_date
)
SELECT 
    DATE_TRUNC('month', first_donation_date) as cohort_month,
    COUNT(DISTINCT fd.person_id) as first_time_donors,
    COUNT(DISTINCT sd.person_id) as retained_donors,
    ROUND(COUNT(DISTINCT sd.person_id) * 100.0 / COUNT(DISTINCT fd.person_id), 2) as retention_rate,
    AVG(sd.second_donation_date - sd.first_donation_date) as avg_days_to_second_donation
FROM first_donations fd
LEFT JOIN second_donations sd ON fd.person_id = sd.person_id
WHERE fd.first_donation_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', first_donation_date)
ORDER BY cohort_month DESC;

​
Recurring Giving Analysis

​
Active Recurring Donors

-- Analyze active recurring donations
SELECT 
    rd.recurring_donation_id,
    rd.amount_cents / 100.0 as recurring_amount,
    rd.frequency,
    rd.status,
    rd.created_at as setup_date,
    rd.last_donation_received_at,
    p.first_name,
    p.last_name,
    -- Calculate expected annual value
    CASE rd.frequency
        WHEN 'weekly' THEN (rd.amount_cents / 100.0) * 52
        WHEN 'biweekly' THEN (rd.amount_cents / 100.0) * 26
        WHEN 'monthly' THEN (rd.amount_cents / 100.0) * 12
        WHEN 'quarterly' THEN (rd.amount_cents / 100.0) * 4
        WHEN 'yearly' THEN rd.amount_cents / 100.0
        ELSE 0
    END as expected_annual_value
FROM planning_center.giving_recurring_donations rd
JOIN planning_center.giving_recurringdonation_relationships rdr
    ON rd.recurring_donation_id = rdr.recurring_donation_id
    AND rdr.relationship_type = 'Person'
JOIN planning_center.giving_people p
    ON rdr.relationship_id = p.person_id
WHERE rd.status = 'active'
ORDER BY expected_annual_value DESC;

​
Recurring Giving Health Metrics

-- Key metrics for recurring giving program
WITH recurring_metrics AS (
    SELECT 
        COUNT(DISTINCT rd.recurring_donation_id) as active_recurring_count,
        SUM(
            CASE rd.frequency
                WHEN 'weekly' THEN (rd.amount_cents / 100.0) * 52
                WHEN 'biweekly' THEN (rd.amount_cents / 100.0) * 26
                WHEN 'monthly' THEN (rd.amount_cents / 100.0) * 12
                WHEN 'quarterly' THEN (rd.amount_cents / 100.0) * 4
                WHEN 'yearly' THEN rd.amount_cents / 100.0
                ELSE 0
            END
        ) as total_expected_annual,
        AVG(rd.amount_cents / 100.0) as avg_recurring_amount
    FROM planning_center.giving_recurring_donations rd
    WHERE rd.status = 'active'
),
churn_metrics AS (
    SELECT 
        COUNT(*) as churned_last_90_days
    FROM planning_center.giving_recurring_donations
    WHERE status = 'inactive'
        AND updated_at >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT 
    rm.active_recurring_count,
    rm.total_expected_annual,
    rm.avg_recurring_amount,
    cm.churned_last_90_days,
    ROUND(cm.churned_last_90_days * 100.0 / NULLIF(rm.active_recurring_count + cm.churned_last_90_days, 0), 2) as churn_rate_90d
FROM recurring_metrics rm
CROSS JOIN churn_metrics cm;

​
Pledge Campaign Tracking

​
Campaign Progress Dashboard

-- Track pledge campaign progress
WITH campaign_summary AS (
    SELECT 
        pc.pledge_campaign_id,
        pc.name as campaign_name,
        pc.description,
        pc.goal_cents / 100.0 as campaign_goal,
        pc.pledges_total_cents / 100.0 as total_pledged,
        pc.received_total_cents / 100.0 as total_received,
        COUNT(DISTINCT p.pledge_id) as pledge_count
    FROM planning_center.giving_pledge_campaigns pc
    LEFT JOIN planning_center.giving_pledges p
        ON p.pledge_campaign_id = pc.pledge_campaign_id
    GROUP BY pc.pledge_campaign_id, pc.name, pc.description, 
             pc.goal_cents, pc.pledges_total_cents, pc.received_total_cents
)
SELECT 
    campaign_name,
    campaign_goal,
    total_pledged,
    total_received,
    pledge_count,
    ROUND((total_pledged / NULLIF(campaign_goal, 0)) * 100, 2) as percent_pledged,
    ROUND((total_received / NULLIF(total_pledged, 0)) * 100, 2) as fulfillment_rate,
    campaign_goal - total_received as remaining_to_goal
FROM campaign_summary
ORDER BY campaign_goal DESC;

​
Individual Pledge Tracking

-- Track individual pledge fulfillment
SELECT 
    p.pledge_id,
    per.first_name,
    per.last_name,
    p.amount_cents / 100.0 as pledge_amount,
    p.received_cents / 100.0 as amount_received,
    pc.name as campaign_name,
    p.created_at as pledge_date,
    ROUND((p.received_cents * 100.0 / NULLIF(p.amount_cents, 0)), 2) as percent_fulfilled,
    (p.amount_cents - p.received_cents) / 100.0 as remaining_balance
FROM planning_center.giving_pledges p
JOIN planning_center.giving_pledge_campaigns pc
    ON p.pledge_campaign_id = pc.pledge_campaign_id
JOIN planning_center.giving_pledge_relationships pr
    ON p.pledge_id = pr.pledge_id
    AND pr.relationship_type = 'Person'
JOIN planning_center.giving_people per
    ON pr.relationship_id = per.person_id
WHERE (p.amount_cents - p.received_cents) > 0  -- Outstanding pledges only
ORDER BY remaining_balance DESC;

​
Window Functions and Rankings

​
Top Donors by Percentile

-- Rank donors by giving and show percentiles
WITH donor_totals AS (
    SELECT 
        p.person_id,
        p.first_name,
        p.last_name,
        SUM(d.amount_cents) / 100.0 as total_given,
        COUNT(d.donation_id) as donation_count
    FROM planning_center.giving_people p
    JOIN planning_center.giving_donation_relationships dr
        ON p.person_id = dr.relationship_id
        AND dr.relationship_type = 'Person'
    JOIN planning_center.giving_donations d
        ON dr.donation_id = d.donation_id
    WHERE d.received_at >= DATE_TRUNC('year', CURRENT_DATE)
        AND d.refunded = false
    GROUP BY p.person_id, p.first_name, p.last_name
),
ranked_donors AS (
    SELECT 
        *,
        RANK() OVER (ORDER BY total_given DESC) as giving_rank,
        NTILE(100) OVER (ORDER BY total_given DESC) as percentile,
        SUM(total_given) OVER (ORDER BY total_given DESC) as cumulative_total,
        SUM(total_given) OVER () as grand_total
    FROM donor_totals
)
SELECT 
    giving_rank,
    first_name,
    last_name,
    total_given,
    donation_count,
    percentile,
    ROUND((cumulative_total / grand_total) * 100, 2) as cumulative_percent_of_total
FROM ranked_donors
WHERE percentile >= 90  -- Top 10% of donors
ORDER BY giving_rank;

-- Track fund performance with smoothed trends
WITH daily_fund_totals AS (
    SELECT 
        f.fund_id,
        f.name as fund_name,
        DATE(d.received_at) as donation_date,
        SUM(des.amount_cents) / 100.0 as daily_total
    FROM planning_center.giving_funds f
    JOIN planning_center.giving_designations des ON f.fund_id = des.fund_id
    JOIN planning_center.giving_designation_relationships dr
        ON des.designation_id = dr.designation_id
        AND dr.relationship_type = 'Donation'
    JOIN planning_center.giving_donations d ON dr.relationship_id = d.donation_id
    WHERE d.received_at >= CURRENT_DATE - INTERVAL '90 days'
        AND d.refunded = false
    GROUP BY f.fund_id, f.name, DATE(d.received_at)
),
fund_trends AS (
    SELECT 
        fund_id,
        fund_name,
        donation_date,
        daily_total,
        AVG(daily_total) OVER (
            PARTITION BY fund_id 
            ORDER BY donation_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as seven_day_avg,
        AVG(daily_total) OVER (
            PARTITION BY fund_id 
            ORDER BY donation_date 
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
        ) as thirty_day_avg
    FROM daily_fund_totals
)
SELECT 
    fund_name,
    donation_date,
    daily_total,
    ROUND(seven_day_avg, 2) as seven_day_avg,
    ROUND(thirty_day_avg, 2) as thirty_day_avg,
    ROUND(((seven_day_avg / NULLIF(thirty_day_avg, 0)) - 1) * 100, 2) as trend_direction_pct
FROM fund_trends
WHERE donation_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY fund_name, donation_date DESC;

​
Performance Optimization

​
Using CTEs for Complex Queries

-- Optimized query using multiple CTEs to break down complexity
WITH active_donors AS (
    -- First, identify active donors
    SELECT DISTINCT dr.relationship_id as person_id
    FROM planning_center.giving_donation_relationships dr
    JOIN planning_center.giving_donations d ON dr.donation_id = d.donation_id
    WHERE dr.relationship_type = 'Person'
        AND d.received_at >= CURRENT_DATE - INTERVAL '365 days'
        AND d.refunded = false
),
donor_stats AS (
    -- Calculate statistics only for active donors
    SELECT 
        ad.person_id,
        COUNT(d.donation_id) as donation_count,
        SUM(d.amount_cents) / 100.0 as total_given,
        AVG(d.amount_cents) / 100.0 as avg_donation
    FROM active_donors ad
    JOIN planning_center.giving_donation_relationships dr
        ON ad.person_id = dr.relationship_id
        AND dr.relationship_type = 'Person'
    JOIN planning_center.giving_donations d ON dr.donation_id = d.donation_id
    WHERE d.refunded = false
    GROUP BY ad.person_id
)
-- Final result with person details
SELECT 
    p.first_name,
    p.last_name,
    ds.donation_count,
    ds.total_given,
    ds.avg_donation
FROM donor_stats ds
JOIN planning_center.giving_people p ON ds.person_id = p.person_id
WHERE ds.total_given >= 1000  -- Major donors only
ORDER BY ds.total_given DESC;

​
Efficient Date Range Queries

-- Use date functions efficiently for better performance
-- Good: Uses index-friendly date comparison
SELECT COUNT(*), SUM(amount_cents) / 100.0 as total
FROM planning_center.giving_donations
WHERE received_at >= DATE_TRUNC('month', CURRENT_DATE)
    AND received_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

-- Alternative: Using date generation for reports
WITH date_series AS (
    SELECT generate_series(
        DATE_TRUNC('week', CURRENT_DATE - INTERVAL '12 weeks'),
        DATE_TRUNC('week', CURRENT_DATE),
        '1 week'::interval
    ) as week_start
)
SELECT 
    ds.week_start,
    COALESCE(SUM(d.amount_cents) / 100.0, 0) as weekly_total
FROM date_series ds
LEFT JOIN planning_center.giving_donations d
    ON d.received_at >= ds.week_start
    AND d.received_at < ds.week_start + INTERVAL '1 week'
    AND d.refunded = false
GROUP BY ds.week_start
ORDER BY ds.week_start DESC;

​
Best Practices for Advanced Queries

​
1. Use CTEs for Readability

Break complex queries into logical steps using Common Table Expressions (WITH clauses).

​
2. Optimize JOIN Order

Join smaller result sets first, then join to larger tables.

​
3. Use Appropriate Indexes

The relationship_type and relationship_id columns are indexed for efficient joins.

​
4. Aggregate Early

When possible, aggregate data in CTEs before joining to reduce the working set size.

​
5. Handle NULL Values

Always consider NULL values in calculations and use NULLIF to prevent division by zero.

​
Next Steps