​
Registrations Reporting Examples

​
Production-Ready Reports for Event Management

Copy these complete report templates directly into your BI tools or use them as-is for comprehensive event insights. Each report is optimized for performance and includes all necessary business logic.

​
Query Requirements

​
Schema Prefix

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

​
Row Level Security (RLS)

Row Level Security automatically filters results by:
  • tenant_organization_id – limits data to your organization
  • system_status – active records returned by default
Skip manual filters for these columns—RLS already enforces them and redundant predicates can hide data or slow execution:
  • ❌ WHERE tenant_organization_id = 1
  • ❌ WHERE system_status = 'active'
Focus on event-specific logic (archived flags, registration status, waitlist counts) while letting RLS manage tenancy and system status.

​
Executive Dashboard Reports

​
Monthly Event Summary Dashboard

-- Executive summary of all event activity for the current month
WITH monthly_events AS (
    SELECT 
        s.signup_id,
        s.name as event_name,
        s.archived,
        cat.name as category,
        camp.name as campus,
        loc.name as location,
        st.starts_at as event_date,
        st.ends_at as event_end,
        s.open_at as registration_open,
        s.close_at as registration_close
    FROM planning_center.registrations_signups s
    LEFT JOIN planning_center.registrations_signup_relationships sr_cat 
        ON sr_cat.signup_id = s.signup_id 
        AND sr_cat.relationship_type = 'category'
    LEFT JOIN planning_center.registrations_categories cat 
        ON cat.category_id = sr_cat.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_camp 
        ON sr_camp.signup_id = s.signup_id 
        AND sr_camp.relationship_type = 'campus'
    LEFT JOIN planning_center.registrations_campuses camp 
        ON camp.campus_id = sr_camp.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_loc 
        ON sr_loc.signup_id = s.signup_id 
        AND sr_loc.relationship_type = 'signup_location'
    LEFT JOIN planning_center.registrations_signup_locations loc 
        ON loc.signup_location_id = sr_loc.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type = 'signup_time'
    LEFT JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr_time.relationship_id
    WHERE DATE_PART('month', st.starts_at) = DATE_PART('month', CURRENT_DATE)
      AND DATE_PART('year', st.starts_at) = DATE_PART('year', CURRENT_DATE)
),
registration_metrics AS (
    SELECT 
        me.signup_id,
        COUNT(DISTINCT a.attendee_id) as total_registrations,
        COUNT(CASE WHEN a.active = true THEN 1 END) as active_registrations,
        COUNT(CASE WHEN a.canceled = true THEN 1 END) as cancellations,
        COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlisted,
        COUNT(DISTINCT ec.emergency_contact_id) as emergency_contacts_provided
    FROM monthly_events me
    LEFT JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = me.signup_id
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id
    LEFT JOIN planning_center.registrations_attendee_relationships ar_ec 
        ON ar_ec.attendee_id = a.attendee_id 
        AND ar_ec.relationship_type = 'emergency_contact'
    LEFT JOIN planning_center.registrations_emergency_contacts ec 
        ON ec.emergency_contact_id = ar_ec.relationship_id
    GROUP BY me.signup_id
),
financial_metrics AS (
    SELECT 
        me.signup_id,
        AVG(selt.price_cents) / 100.0 as avg_price,
        MAX(selt.price_cents) / 100.0 as max_price,
        MIN(selt.price_cents) / 100.0 as min_price
    FROM monthly_events me
    LEFT JOIN planning_center.registrations_selection_types selt 
        ON EXISTS (
            SELECT 1 FROM planning_center.registrations_signup_relationships sr
            WHERE sr.signup_id = me.signup_id
            AND sr.relationship_type = 'selection_type'
            AND sr.relationship_id = selt.selection_type_id
        )
    GROUP BY me.signup_id
)
SELECT 
    me.event_name,
    me.event_date::date as date,
    EXTRACT(DOW FROM me.event_date) as day_of_week,
    COALESCE(me.category, 'Uncategorized') as category,
    COALESCE(me.campus, 'All Campuses') as campus,
    COALESCE(me.location, 'TBD') as location,
    CASE 
        WHEN me.archived = true THEN 'Archived'
        WHEN me.event_date < CURRENT_TIMESTAMP THEN 'Completed'
        WHEN me.registration_close < CURRENT_TIMESTAMP THEN 'Registration Closed'
        WHEN me.registration_open > CURRENT_TIMESTAMP THEN 'Not Yet Open'
        ELSE 'Open for Registration'
    END as status,
    COALESCE(rm.total_registrations, 0) as total_registrations,
    COALESCE(rm.active_registrations, 0) as confirmed,
    COALESCE(rm.cancellations, 0) as canceled,
    COALESCE(rm.waitlisted, 0) as waitlisted,
    CASE 
        WHEN rm.total_registrations > 0 
        THEN ROUND(rm.cancellations * 100.0 / rm.total_registrations, 1)
        ELSE 0 
    END as cancellation_rate,
    CASE 
        WHEN rm.active_registrations > 0 
        THEN ROUND(rm.emergency_contacts_provided * 100.0 / rm.active_registrations, 1)
        ELSE 0 
    END as emergency_contact_completion,
    COALESCE(fm.avg_price, 0) as avg_ticket_price,
    COALESCE(fm.avg_price * rm.active_registrations, 0) as estimated_revenue
FROM monthly_events me
LEFT JOIN registration_metrics rm ON rm.signup_id = me.signup_id
LEFT JOIN financial_metrics fm ON fm.signup_id = me.signup_id
ORDER BY me.event_date;

​
Year-to-Date Performance Report

-- Comprehensive YTD metrics with comparisons to last year
WITH ytd_data AS (
    SELECT 
        DATE_PART('year', st.starts_at) as year,
        DATE_PART('month', st.starts_at) as month,
        cat.name as category,
        COUNT(DISTINCT s.signup_id) as event_count,
        COUNT(DISTINCT a.attendee_id) as total_attendees,
        COUNT(CASE WHEN a.active = true THEN 1 END) as active_registrations,
        COUNT(CASE WHEN a.canceled = true THEN 1 END) as cancellations,
        COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlisted,
        SUM(selt.price_cents * CASE WHEN a.active = true THEN 1 ELSE 0 END) / 100.0 as revenue
    FROM planning_center.registrations_signups s
    LEFT JOIN planning_center.registrations_signup_relationships sr_cat 
        ON sr_cat.signup_id = s.signup_id 
        AND sr_cat.relationship_type = 'category'
    LEFT JOIN planning_center.registrations_categories cat 
        ON cat.category_id = sr_cat.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type = 'signup_time'
    LEFT JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr_time.relationship_id
    LEFT JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = s.signup_id
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id
    LEFT JOIN planning_center.registrations_selection_types selt 
        ON EXISTS (
            SELECT 1 FROM planning_center.registrations_signup_relationships sr
            WHERE sr.signup_id = s.signup_id
            AND sr.relationship_type = 'selection_type'
            AND sr.relationship_id = selt.selection_type_id
        )
    WHERE s.archived = false
      AND st.starts_at >= DATE_TRUNC('year', CURRENT_DATE - INTERVAL '1 year')
      AND st.starts_at <= CURRENT_DATE
    GROUP BY DATE_PART('year', st.starts_at), DATE_PART('month', st.starts_at), cat.name
),
comparison AS (
    SELECT 
        COALESCE(category, 'Uncategorized') as category,
        SUM(CASE WHEN year = DATE_PART('year', CURRENT_DATE) THEN event_count ELSE 0 END) as events_this_year,
        SUM(CASE WHEN year = DATE_PART('year', CURRENT_DATE) - 1 THEN event_count ELSE 0 END) as events_last_year,
        SUM(CASE WHEN year = DATE_PART('year', CURRENT_DATE) THEN active_registrations ELSE 0 END) as registrations_this_year,
        SUM(CASE WHEN year = DATE_PART('year', CURRENT_DATE) - 1 THEN active_registrations ELSE 0 END) as registrations_last_year,
        SUM(CASE WHEN year = DATE_PART('year', CURRENT_DATE) THEN cancellations ELSE 0 END) as cancellations_this_year,
        SUM(CASE WHEN year = DATE_PART('year', CURRENT_DATE) - 1 THEN cancellations ELSE 0 END) as cancellations_last_year,
        SUM(CASE WHEN year = DATE_PART('year', CURRENT_DATE) THEN waitlisted ELSE 0 END) as waitlisted_this_year,
        SUM(CASE WHEN year = DATE_PART('year', CURRENT_DATE) - 1 THEN waitlisted ELSE 0 END) as waitlisted_last_year,
        SUM(CASE WHEN year = DATE_PART('year', CURRENT_DATE) THEN revenue ELSE 0 END) as revenue_this_year,
        SUM(CASE WHEN year = DATE_PART('year', CURRENT_DATE) - 1 THEN revenue ELSE 0 END) as revenue_last_year
    FROM ytd_data
    GROUP BY category
)
SELECT 
    category,
    events_this_year,
    events_last_year,
    CASE 
        WHEN events_last_year > 0 
        THEN ROUND((events_this_year - events_last_year) * 100.0 / events_last_year, 1)
        ELSE NULL 
    END as event_growth_pct,
    registrations_this_year,
    registrations_last_year,
    CASE 
        WHEN registrations_last_year > 0 
        THEN ROUND((registrations_this_year - registrations_last_year) * 100.0 / registrations_last_year, 1)
        ELSE NULL 
    END as registration_growth_pct,
    ROUND(cancellations_this_year * 100.0 / NULLIF(registrations_this_year + cancellations_this_year, 0), 1) as cancellation_rate_this_year,
    ROUND(cancellations_last_year * 100.0 / NULLIF(registrations_last_year + cancellations_last_year, 0), 1) as cancellation_rate_last_year,
    waitlisted_this_year,
    waitlisted_last_year,
    ROUND(revenue_this_year, 2) as revenue_this_year,
    ROUND(revenue_last_year, 2) as revenue_last_year,
    CASE 
        WHEN revenue_last_year > 0 
        THEN ROUND((revenue_this_year - revenue_last_year) * 100.0 / revenue_last_year, 1)
        ELSE NULL 
    END as revenue_growth_pct
FROM comparison
ORDER BY registrations_this_year DESC;

​
Operational Reports

​
Event Roster with Emergency Contacts

-- Complete attendee roster for event operations
WITH event_roster AS (
    SELECT 
        s.name as event_name,
        st.starts_at as event_date,
        loc.name as location_name,
        loc.formatted_address as location_address,
        p.person_id,
        p.name as attendee_name,
        p.first_name,
        p.last_name,
        CASE 
            WHEN a.waitlisted = true THEN 'Waitlisted'
            WHEN a.canceled = true THEN 'Canceled'
            WHEN a.active = true THEN 'Confirmed'
            ELSE 'Unknown'
        END as registration_status,
        a.created_at as registered_date,
        a.waitlisted_at,
        ec.name as emergency_contact_name,
        ec.phone_number as emergency_contact_phone,
        ROW_NUMBER() OVER (
            PARTITION BY s.signup_id 
            ORDER BY 
                CASE WHEN a.active = true THEN 1 
                     WHEN a.waitlisted = true THEN 2 
                     ELSE 3 END,
                a.created_at
        ) as roster_number
    FROM planning_center.registrations_signups s
    -- Join to time
    JOIN planning_center.registrations_signup_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type = 'signup_time'
    JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr_time.relationship_id
    -- Join to location
    LEFT JOIN planning_center.registrations_signup_relationships sr_loc 
        ON sr_loc.signup_id = s.signup_id 
        AND sr_loc.relationship_type = 'signup_location'
    LEFT JOIN planning_center.registrations_signup_locations loc 
        ON loc.signup_location_id = sr_loc.relationship_id
    -- Join to attendees
    JOIN planning_center.registrations_attendee_relationships ar_signup 
        ON ar_signup.relationship_type = 'signup' 
        AND ar_signup.relationship_id = s.signup_id
    JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar_signup.attendee_id
    -- Join to person
    JOIN planning_center.registrations_attendee_relationships ar_reg 
        ON ar_reg.attendee_id = a.attendee_id 
        AND ar_reg.relationship_type = 'registration'
    JOIN planning_center.registrations_registration_relationships rr 
        ON rr.registration_id = ar_reg.relationship_id 
        AND rr.relationship_type = 'person'
    JOIN planning_center.registrations_people p 
        ON p.person_id = rr.relationship_id
    -- Join to emergency contact
    LEFT JOIN planning_center.registrations_attendee_relationships ar_ec 
        ON ar_ec.attendee_id = a.attendee_id 
        AND ar_ec.relationship_type = 'emergency_contact'
    LEFT JOIN planning_center.registrations_emergency_contacts ec 
        ON ec.emergency_contact_id = ar_ec.relationship_id
    WHERE s.archived = false
      AND st.starts_at >= CURRENT_DATE
      AND st.starts_at <= CURRENT_DATE + INTERVAL '30 days'
)
SELECT 
    event_name,
    event_date::date as date,
    TO_CHAR(event_date, 'HH:MI AM') as start_time,
    location_name,
    location_address,
    roster_number,
    attendee_name,
    first_name,
    last_name,
    registration_status,
    registered_date::date as registration_date,
    CASE 
        WHEN registration_status = 'Waitlisted' 
        THEN DATE_PART('day', CURRENT_TIMESTAMP - waitlisted_at) || ' days'
        ELSE NULL 
    END as days_on_waitlist,
    emergency_contact_name,
    emergency_contact_phone,
    CASE 
        WHEN emergency_contact_name IS NULL 
        THEN 'Missing'
        ELSE 'Provided'
    END as emergency_contact_status
FROM event_roster
ORDER BY event_date, event_name, roster_number;

​
Waitlist Management Report

-- Active waitlists with contact information for follow-up
WITH waitlist_details AS (
    SELECT 
        s.signup_id,
        s.name as event_name,
        st.starts_at as event_date,
        cat.name as category,
        p.person_id,
        p.name as attendee_name,
        a.waitlisted_at,
        CURRENT_TIMESTAMP - a.waitlisted_at as time_waiting,
        ROW_NUMBER() OVER (
            PARTITION BY s.signup_id 
            ORDER BY a.waitlisted_at
        ) as waitlist_position,
        COUNT(*) OVER (PARTITION BY s.signup_id) as total_waitlisted,
        -- Get contact info from People module if available
        pp.primary_email,
        pp.primary_phone
    FROM planning_center.registrations_attendees a
    JOIN planning_center.registrations_attendee_relationships ar_signup 
        ON ar_signup.attendee_id = a.attendee_id 
        AND ar_signup.relationship_type = 'signup'
    JOIN planning_center.registrations_signups s 
        ON s.signup_id = ar_signup.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_cat 
        ON sr_cat.signup_id = s.signup_id 
        AND sr_cat.relationship_type = 'category'
    LEFT JOIN planning_center.registrations_categories cat 
        ON cat.category_id = sr_cat.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type = 'signup_time'
    LEFT JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr_time.relationship_id
    JOIN planning_center.registrations_attendee_relationships ar_reg 
        ON ar_reg.attendee_id = a.attendee_id 
        AND ar_reg.relationship_type = 'registration'
    JOIN planning_center.registrations_registration_relationships rr 
        ON rr.registration_id = ar_reg.relationship_id 
        AND rr.relationship_type = 'person'
    JOIN planning_center.registrations_people p 
        ON p.person_id = rr.relationship_id
    LEFT JOIN planning_center.people_people pp 
        ON pp.person_id = p.person_id
    WHERE a.waitlisted = true
      AND s.archived = false
      AND st.starts_at >= CURRENT_DATE
),
recent_activity AS (
    SELECT 
        s.signup_id,
        COUNT(CASE 
            WHEN a.canceled = true 
            AND a.updated_at >= CURRENT_DATE - INTERVAL '7 days' 
            THEN 1 
        END) as recent_cancellations
    FROM planning_center.registrations_signups s
    JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = s.signup_id
    JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id
    GROUP BY s.signup_id
)
SELECT 
    wd.event_name,
    wd.event_date::date as event_date,
    DATE_PART('day', wd.event_date - CURRENT_DATE) as days_until_event,
    COALESCE(wd.category, 'Uncategorized') as category,
    wd.waitlist_position,
    wd.total_waitlisted,
    wd.attendee_name,
    COALESCE(wd.primary_email, 'No email on file') as email,
    COALESCE(wd.primary_phone, 'No phone on file') as phone,
    wd.waitlisted_at::date as waitlist_date,
    EXTRACT(DAY FROM wd.time_waiting) as days_waiting,
    COALESCE(ra.recent_cancellations, 0) as spots_opened_this_week,
    CASE 
        WHEN wd.waitlist_position <= ra.recent_cancellations 
        THEN 'High - Likely to get spot'
        WHEN wd.waitlist_position <= wd.total_waitlisted * 0.3 
        THEN 'Medium - Possible opening'
        ELSE 'Low - Unlikely this week'
    END as conversion_likelihood
FROM waitlist_details wd
LEFT JOIN recent_activity ra ON ra.signup_id = wd.signup_id
ORDER BY 
    wd.event_date,
    wd.event_name,
    wd.waitlist_position;

​
Financial Reports

​
Event Revenue Analysis

-- Comprehensive revenue report with pricing tier breakdown
WITH event_financials AS (
    SELECT 
        s.signup_id,
        s.name as event_name,
        cat.name as category,
        st.starts_at as event_date,
        selt.selection_type_id,
        selt.name as pricing_tier,
        selt.price_cents / 100.0 as unit_price,
        selt.publicly_available,
        COUNT(a.attendee_id) as quantity_sold,
        (selt.price_cents * COUNT(a.attendee_id)) / 100.0 as tier_revenue
    FROM planning_center.registrations_signups s
    LEFT JOIN planning_center.registrations_signup_relationships sr_cat 
        ON sr_cat.signup_id = s.signup_id 
        AND sr_cat.relationship_type = 'category'
    LEFT JOIN planning_center.registrations_categories cat 
        ON cat.category_id = sr_cat.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type = 'signup_time'
    LEFT JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr_time.relationship_id
    JOIN planning_center.registrations_selection_types selt 
        ON EXISTS (
            SELECT 1 FROM planning_center.registrations_signup_relationships sr
            WHERE sr.signup_id = s.signup_id
            AND sr.relationship_type = 'selection_type'
            AND sr.relationship_id = selt.selection_type_id
        )
    LEFT JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = s.signup_id
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id 
        AND a.active = true
    WHERE s.archived = false
    GROUP BY 
        s.signup_id, s.name, cat.name, st.starts_at,
        selt.selection_type_id, selt.name, selt.price_cents, selt.publicly_available
),
event_summary AS (
    SELECT 
        event_name,
        COALESCE(category, 'Uncategorized') as category,
        event_date,
        COUNT(DISTINCT selection_type_id) as pricing_tiers,
        SUM(quantity_sold) as total_tickets_sold,
        SUM(tier_revenue) as total_revenue,
        AVG(unit_price) as avg_ticket_price,
        MIN(unit_price) as min_price,
        MAX(unit_price) as max_price,
        STRING_AGG(
            pricing_tier || ': $' || unit_price || ' x ' || quantity_sold || ' = $' || tier_revenue,
            '; ' ORDER BY unit_price DESC
        ) as tier_breakdown
    FROM event_financials
    GROUP BY event_name, category, event_date
)
SELECT 
    event_name,
    category,
    event_date::date as date,
    CASE 
        WHEN event_date < CURRENT_DATE THEN 'Past'
        WHEN event_date < CURRENT_DATE + INTERVAL '7 days' THEN 'This Week'
        WHEN event_date < CURRENT_DATE + INTERVAL '30 days' THEN 'This Month'
        ELSE 'Future'
    END as timing,
    pricing_tiers,
    total_tickets_sold,
    ROUND(total_revenue, 2) as total_revenue,
    ROUND(avg_ticket_price, 2) as avg_price,
    ROUND(min_price, 2) as min_price,
    ROUND(max_price, 2) as max_price,
    ROUND(total_revenue / NULLIF(total_tickets_sold, 0), 2) as actual_avg_paid,
    tier_breakdown
FROM event_summary
ORDER BY event_date DESC, total_revenue DESC;

​
Category Performance Report

-- Category-level metrics for strategic planning
WITH category_metrics AS (
    SELECT 
        COALESCE(cat.name, 'Uncategorized') as category,
        DATE_PART('quarter', st.starts_at) as quarter,
        DATE_PART('year', st.starts_at) as year,
        COUNT(DISTINCT s.signup_id) as events,
        COUNT(DISTINCT a.attendee_id) as unique_attendees,
        COUNT(CASE WHEN a.active = true THEN 1 END) as registrations,
        COUNT(CASE WHEN a.canceled = true THEN 1 END) as cancellations,
        COUNT(CASE WHEN a.waitlisted = true THEN 1 END) as waitlisted,
        AVG(selt.price_cents) / 100.0 as avg_price,
        SUM(CASE WHEN a.active = true THEN selt.price_cents ELSE 0 END) / 100.0 as revenue
    FROM planning_center.registrations_signups s
    LEFT JOIN planning_center.registrations_signup_relationships sr_cat 
        ON sr_cat.signup_id = s.signup_id 
        AND sr_cat.relationship_type = 'category'
    LEFT JOIN planning_center.registrations_categories cat 
        ON cat.category_id = sr_cat.relationship_id
    LEFT JOIN planning_center.registrations_signup_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type = 'signup_time'
    LEFT JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr_time.relationship_id
    LEFT JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = s.signup_id
    LEFT JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id
    LEFT JOIN planning_center.registrations_selection_types selt 
        ON EXISTS (
            SELECT 1 FROM planning_center.registrations_signup_relationships sr
            WHERE sr.signup_id = s.signup_id
            AND sr.relationship_type = 'selection_type'
            AND sr.relationship_id = selt.selection_type_id
        )
    WHERE s.archived = false
      AND st.starts_at >= DATE_TRUNC('year', CURRENT_DATE)
    GROUP BY cat.name, DATE_PART('quarter', st.starts_at), DATE_PART('year', st.starts_at)
),
category_rankings AS (
    SELECT 
        category,
        'Q' || quarter || ' ' || year as period,
        events,
        unique_attendees,
        registrations,
        ROUND(cancellations * 100.0 / NULLIF(registrations + cancellations, 0), 1) as cancellation_rate,
        ROUND(waitlisted * 100.0 / NULLIF(registrations, 0), 1) as waitlist_rate,
        ROUND(avg_price, 2) as avg_ticket_price,
        ROUND(revenue, 2) as total_revenue,
        ROUND(revenue / NULLIF(registrations, 0), 2) as revenue_per_attendee,
        RANK() OVER (PARTITION BY quarter, year ORDER BY revenue DESC) as revenue_rank,
        RANK() OVER (PARTITION BY quarter, year ORDER BY registrations DESC) as attendance_rank
    FROM category_metrics
)
SELECT 
    category,
    period,
    events,
    unique_attendees,
    registrations,
    cancellation_rate,
    waitlist_rate,
    avg_ticket_price,
    total_revenue,
    revenue_per_attendee,
    revenue_rank,
    attendance_rank,
    CASE 
        WHEN revenue_rank <= 3 AND attendance_rank <= 3 THEN 'Star Performer'
        WHEN revenue_rank <= 3 OR attendance_rank <= 3 THEN 'Strong Performer'
        WHEN cancellation_rate > 20 THEN 'Needs Attention'
        ELSE 'Standard'
    END as performance_tier
FROM category_rankings
ORDER BY period DESC, revenue_rank;

​
Trend Analysis Reports

-- Track registration patterns to optimize marketing timing
WITH daily_registrations AS (
    SELECT 
        s.signup_id,
        s.name as event_name,
        st.starts_at as event_date,
        DATE(a.created_at) as registration_date,
        st.starts_at::date - a.created_at::date as days_before_event,
        COUNT(*) as daily_registrations,
        SUM(COUNT(*)) OVER (
            PARTITION BY s.signup_id 
            ORDER BY DATE(a.created_at)
            ROWS UNBOUNDED PRECEDING
        ) as cumulative_registrations
    FROM planning_center.registrations_signups s
    JOIN planning_center.registrations_signup_relationships sr_time 
        ON sr_time.signup_id = s.signup_id 
        AND sr_time.relationship_type = 'signup_time'
    JOIN planning_center.registrations_signup_times st 
        ON st.signup_time_id = sr_time.relationship_id
    JOIN planning_center.registrations_attendee_relationships ar 
        ON ar.relationship_type = 'signup' 
        AND ar.relationship_id = s.signup_id
    JOIN planning_center.registrations_attendees a 
        ON a.attendee_id = ar.attendee_id 
        AND a.active = true
    WHERE s.archived = false
      AND st.starts_at >= CURRENT_DATE - INTERVAL '90 days'
      AND st.starts_at <= CURRENT_DATE + INTERVAL '90 days'
    GROUP BY s.signup_id, s.name, st.starts_at, DATE(a.created_at)
),
velocity_patterns AS (
    SELECT 
        CASE 
            WHEN days_before_event >= 60 THEN '60+ days out'
            WHEN days_before_event >= 30 THEN '30-59 days out'
            WHEN days_before_event >= 14 THEN '14-29 days out'
            WHEN days_before_event >= 7 THEN '7-13 days out'
            WHEN days_before_event >= 1 THEN '1-6 days out'
            WHEN days_before_event = 0 THEN 'Day of event'
            ELSE 'After event start'
        END as registration_window,
        COUNT(DISTINCT event_name) as events,
        SUM(daily_registrations) as total_registrations,
        AVG(daily_registrations) as avg_daily_registrations,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY daily_registrations) as median_daily_registrations,
        MAX(daily_registrations) as peak_daily_registrations
    FROM daily_registrations
    WHERE days_before_event >= 0
    GROUP BY registration_window
)
SELECT 
    registration_window,
    events,
    total_registrations,
    ROUND(avg_daily_registrations, 1) as avg_daily,
    ROUND(median_daily_registrations, 1) as median_daily,
    peak_daily_registrations as peak_daily,
    ROUND(total_registrations * 100.0 / SUM(total_registrations) OVER (), 1) as pct_of_total,
    SUM(total_registrations) OVER (
        ORDER BY 
            CASE registration_window
                WHEN '60+ days out' THEN 1
                WHEN '30-59 days out' THEN 2
                WHEN '14-29 days out' THEN 3
                WHEN '7-13 days out' THEN 4
                WHEN '1-6 days out' THEN 5
                WHEN 'Day of event' THEN 6
                ELSE 7
            END
    ) as cumulative_total,
    ROUND(
        SUM(total_registrations) OVER (
            ORDER BY 
                CASE registration_window
                    WHEN '60+ days out' THEN 1
                    WHEN '30-59 days out' THEN 2
                    WHEN '14-29 days out' THEN 3
                    WHEN '7-13 days out' THEN 4
                    WHEN '1-6 days out' THEN 5
                    WHEN 'Day of event' THEN 6
                    ELSE 7
                END
        ) * 100.0 / SUM(total_registrations) OVER (), 
        1
    ) as cumulative_pct
FROM velocity_patterns
ORDER BY 
    CASE registration_window
        WHEN '60+ days out' THEN 1
        WHEN '30-59 days out' THEN 2
        WHEN '14-29 days out' THEN 3
        WHEN '7-13 days out' THEN 4
        WHEN '1-6 days out' THEN 5
        WHEN 'Day of event' THEN 6
        ELSE 7
    END;

​
Export Templates

​
CSV Export for Mail Merge

-- Export format for email campaigns and mail merge
SELECT 
    p.first_name,
    p.last_name,
    p.name as full_name,
    s.name as event_name,
    st.starts_at::date as event_date,
    TO_CHAR(st.starts_at, 'Day, Month DD at HH:MI AM') as event_datetime_formatted,
    loc.name as venue_name,
    loc.formatted_address as venue_address,
    CASE 
        WHEN a.waitlisted = true THEN 'You are on the waitlist'
        WHEN a.active = true THEN 'Your registration is confirmed'
        ELSE 'Registration status pending'
    END as status_message,
    s.new_registration_url as registration_link,
    pp.primary_email as email_address
FROM planning_center.registrations_attendees a
JOIN planning_center.registrations_attendee_relationships ar_signup 
    ON ar_signup.attendee_id = a.attendee_id 
    AND ar_signup.relationship_type = 'signup'
JOIN planning_center.registrations_signups s 
    ON s.signup_id = ar_signup.relationship_id
JOIN planning_center.registrations_signup_relationships sr_time 
    ON sr_time.signup_id = s.signup_id 
    AND sr_time.relationship_type = 'signup_time'
JOIN planning_center.registrations_signup_times st 
    ON st.signup_time_id = sr_time.relationship_id
LEFT JOIN planning_center.registrations_signup_relationships sr_loc 
    ON sr_loc.signup_id = s.signup_id 
    AND sr_loc.relationship_type = 'signup_location'
LEFT JOIN planning_center.registrations_signup_locations loc 
    ON loc.signup_location_id = sr_loc.relationship_id
JOIN planning_center.registrations_attendee_relationships ar_reg 
    ON ar_reg.attendee_id = a.attendee_id 
    AND ar_reg.relationship_type = 'registration'
JOIN planning_center.registrations_registration_relationships rr 
    ON rr.registration_id = ar_reg.relationship_id 
    AND rr.relationship_type = 'person'
JOIN planning_center.registrations_people p 
    ON p.person_id = rr.relationship_id
LEFT JOIN planning_center.people_people pp 
    ON pp.person_id = p.person_id
WHERE s.archived = false
  AND st.starts_at >= CURRENT_DATE
  AND st.starts_at <= CURRENT_DATE + INTERVAL '30 days'
  AND pp.primary_email IS NOT NULL
ORDER BY st.starts_at, s.name, p.last_name, p.first_name;

​
Performance Notes

  1. Materialized Views - Consider creating materialized views for frequently accessed summaries
  2. Partitioning - For large datasets, partition by event date or registration date
  3. Index Usage - These queries are optimized for the existing indexes
  4. Batch Processing - Run heavy reports during off-peak hours
  5. Caching - Cache executive dashboard queries that don’t need the most current data

​
Customization Tips

  • Replace date ranges to match your reporting periods
  • Add campus or location filters for multi-site churches
  • Modify category groupings to match your event structure
  • Adjust financial calculations based on your fee structure
  • Add custom fields from People module for deeper demographics