Skip to main content
Parable
Open Parable

Planning Center Publishing Report Examples

Production-ready Publishing reports for leadership: series performance, speaker frequency, and distribution summaries ready to export or schedule.

Production-ready report templates for Publishing data. These queries are designed to be scheduled, exported, and shared with leadership.

Query Requirements

Schema Prefix

IMPORTANT: All tables in the Planning Center Publishing module live in the planning_center schema. Always prefix table names with planning_center. in your reports.

✅ CORRECT: SELECT * FROM planning_center.publishing_episodes ❌ INCORRECT: SELECT * FROM publishing_episodes

Row Level Security (RLS)

Row Level Security automatically governs:

  • tenant_organization_id – restricts results to 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'

Focus on media analytics, engagement trends, and channel performance while trusting RLS for tenancy and system status.

Executive Dashboard Reports

Monthly Publishing Executive Summary

Complete monthly overview for leadership.

-- Monthly Executive Publishing Summary Report
WITH current_month_metrics AS (
    SELECT 
        DATE_TRUNC('month', CURRENT_DATE) as report_month,
        COUNT(DISTINCT e.episode_id) as episodes_published,
        COUNT(DISTINCT ser_er.relationship_id) as active_series,
        COUNT(DISTINCT spr.relationship_id) as unique_speakers,
        COUNT(DISTINCT ch_er.relationship_id) as active_channels,
        SUM(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as total_watches,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches_per_episode,
        COUNT(DISTINCT res_er.relationship_id) as resources_created
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    LEFT JOIN planning_center.publishing_episodes_relationships ser_er
        ON ser_er.episode_id = e.episode_id AND ser_er.relationship_type = 'series'
    LEFT JOIN planning_center.publishing_episodes_relationships ship_er
        ON ship_er.episode_id = e.episode_id AND ship_er.relationship_type = 'speakerships'
    LEFT JOIN planning_center.publishing_speakerships_relationships spr
        ON spr.speakership_id = ship_er.relationship_id AND spr.relationship_type = 'speaker'
    LEFT JOIN planning_center.publishing_episodes_relationships ch_er
        ON ch_er.episode_id = e.episode_id AND ch_er.relationship_type = 'channel'
    LEFT JOIN planning_center.publishing_episodes_relationships res_er
        ON res_er.episode_id = e.episode_id AND res_er.relationship_type = 'episode_resources'
    WHERE e.published_live_at >= DATE_TRUNC('month', CURRENT_DATE)
        AND e.published_live_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
),
previous_month_metrics AS (
    SELECT 
        COUNT(DISTINCT e.episode_id) as episodes_published,
        SUM(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as total_watches,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches_per_episode
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    WHERE e.published_live_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
        AND e.published_live_at < DATE_TRUNC('month', CURRENT_DATE)
),
year_ago_metrics AS (
    SELECT 
        COUNT(DISTINCT e.episode_id) as episodes_published,
        SUM(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as total_watches,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches_per_episode
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    WHERE e.published_live_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 year')
        AND e.published_live_at < DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 year') + INTERVAL '1 month'
),
top_episodes AS (
    SELECT 
        e.title,
        COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0) as watch_count,
        s.title as series_title
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    LEFT JOIN planning_center.publishing_episodes_relationships er_series
        ON er_series.episode_id = e.episode_id AND er_series.relationship_type = 'series'
    LEFT JOIN planning_center.publishing_series s ON s.series_id = er_series.relationship_id
    WHERE e.published_live_at >= DATE_TRUNC('month', CURRENT_DATE)
        AND e.published_live_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
    ORDER BY watch_count DESC NULLS LAST
    LIMIT 3
)
SELECT 
    TO_CHAR(cm.report_month, 'FMMonth YYYY') as reporting_period,
    '=== CONTENT PRODUCTION ===' as section_1,
    cm.episodes_published as episodes_published_this_month,
    pm.episodes_published as episodes_published_last_month,
    ROUND(((cm.episodes_published::numeric - pm.episodes_published) / 
           NULLIF(pm.episodes_published, 0)) * 100, 1) as episode_change_pct,
    cm.active_series as active_series_count,
    cm.unique_speakers as unique_speakers_count,
    cm.resources_created as resources_created_count,
    '=== ENGAGEMENT METRICS ===' as section_2,
    cm.total_watches as total_watches_this_month,
    pm.total_watches as total_watches_last_month,
    ROUND(((cm.total_watches::numeric - pm.total_watches) / 
           NULLIF(pm.total_watches, 0)) * 100, 1) as watch_growth_mom_pct,
    ya.total_watches as total_watches_year_ago,
    ROUND(((cm.total_watches::numeric - ya.total_watches) / 
           NULLIF(ya.total_watches, 0)) * 100, 1) as watch_growth_yoy_pct,
    ROUND(cm.avg_watches_per_episode::numeric, 0) as avg_watches_per_episode,
    '=== TOP PERFORMING CONTENT ===' as section_3,
    (SELECT STRING_AGG(title || ' (' || COALESCE(watch_count::text, 'N/A') || ' watches)', ', ' 
                       ORDER BY watch_count DESC NULLS LAST) FROM top_episodes) as top_3_episodes,
    '=== DISTRIBUTION ===' as section_4,
    cm.active_channels as active_channel_count
FROM current_month_metrics cm
CROSS JOIN previous_month_metrics pm
CROSS JOIN year_ago_metrics ya;

Quarterly Publishing Performance Report

Comprehensive quarterly analysis for board meetings.

-- Quarterly Publishing Performance Report
WITH quarterly_data AS (
    SELECT 
        DATE_TRUNC('quarter', e.published_live_at) as quarter,
        COUNT(DISTINCT e.episode_id) as episodes,
        COUNT(DISTINCT ser_er.relationship_id) as series,
        COUNT(DISTINCT spr.relationship_id) as speakers,
        SUM(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as watches,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches,
        COUNT(DISTINCT res_er.relationship_id) as resources
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    LEFT JOIN planning_center.publishing_episodes_relationships ser_er
        ON ser_er.episode_id = e.episode_id AND ser_er.relationship_type = 'series'
    LEFT JOIN planning_center.publishing_episodes_relationships ship_er
        ON ship_er.episode_id = e.episode_id AND ship_er.relationship_type = 'speakerships'
    LEFT JOIN planning_center.publishing_speakerships_relationships spr
        ON spr.speakership_id = ship_er.relationship_id AND spr.relationship_type = 'speaker'
    LEFT JOIN planning_center.publishing_episodes_relationships res_er
        ON res_er.episode_id = e.episode_id AND res_er.relationship_type = 'episode_resources'
    WHERE e.published_live_at >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '1 year')
    GROUP BY quarter
),
current_quarter AS (
    SELECT * FROM quarterly_data 
    WHERE quarter = DATE_TRUNC('quarter', CURRENT_DATE)
),
previous_quarter AS (
    SELECT * FROM quarterly_data 
    WHERE quarter = DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months')
),
year_ago_quarter AS (
    SELECT * FROM quarterly_data 
    WHERE quarter = DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '1 year')
)
SELECT 
    '=== QUARTERLY PUBLISHING REPORT ===' as report_header,
    TO_CHAR(cq.quarter, 'Q YYYY') as current_quarter,
    '' as blank_line_1,
    '--- Content Production ---' as production_header,
    cq.episodes as episodes_this_quarter,
    pq.episodes as episodes_last_quarter,
    yaq.episodes as episodes_year_ago_quarter,
    ROUND(((cq.episodes::numeric - pq.episodes) / NULLIF(pq.episodes, 0)) * 100, 1) as episode_qoq_change_pct,
    ROUND(((cq.episodes::numeric - yaq.episodes) / NULLIF(yaq.episodes, 0)) * 100, 1) as episode_yoy_change_pct,
    '' as blank_line_2,
    '--- Engagement Metrics ---' as engagement_header,
    cq.watches as total_watches_this_quarter,
    ROUND(cq.avg_watches::numeric, 0) as avg_watches_per_episode,
    ROUND(((cq.watches::numeric - pq.watches) / NULLIF(pq.watches, 0)) * 100, 1) as watch_qoq_change_pct,
    ROUND(((cq.watches::numeric - yaq.watches) / NULLIF(yaq.watches, 0)) * 100, 1) as watch_yoy_change_pct,
    '' as blank_line_3,
    '--- Resource Metrics ---' as resource_header,
    cq.series as active_series_count,
    cq.speakers as unique_speakers_count,
    cq.resources as resources_created_count,
    ROUND(cq.resources::numeric / NULLIF(cq.episodes, 0), 2) as resources_per_episode,
    '' as blank_line_4,
    '--- Performance Indicators ---' as performance_header,
    CASE 
        WHEN cq.watches > pq.watches * 1.1 THEN 'Strong Growth'
        WHEN cq.watches > pq.watches THEN 'Moderate Growth'
        WHEN cq.watches > pq.watches * 0.9 THEN 'Stable'
        ELSE 'Declining'
    END as quarter_performance_rating
FROM current_quarter cq
CROSS JOIN previous_quarter pq
CROSS JOIN year_ago_quarter yaq;

Content Analysis Reports

Sermon Series Performance Report

Detailed analysis of each series.

-- Sermon Series Performance Analysis Report
WITH series_metrics AS (
    SELECT 
        s.series_id,
        s.title as series_title,
        s.description,
        s.started_at,
        s.ended_at,
        s.episodes_count as planned_episodes,
        COUNT(DISTINCT e.episode_id) as actual_episodes,
        MIN(e.published_live_at) as first_episode_date,
        MAX(e.published_live_at) as last_episode_date,
        SUM(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as total_watches,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches_per_episode,
        MAX(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as best_episode_watches,
        MIN(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as worst_episode_watches,
        STDDEV(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as watch_stddev,
        COUNT(DISTINCT spr.relationship_id) as speaker_count,
        STRING_AGG(DISTINCT sp.formatted_name, ', ' ORDER BY sp.formatted_name) as speakers,
        COUNT(DISTINCT res_er.relationship_id) as total_resources
    FROM planning_center.publishing_series s
    LEFT JOIN planning_center.publishing_episodes_relationships ep_er
        ON ep_er.relationship_id = s.series_id AND ep_er.relationship_type = 'series'
    LEFT JOIN planning_center.publishing_episodes e ON e.episode_id = ep_er.episode_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    LEFT JOIN planning_center.publishing_episodes_relationships ship_er
        ON ship_er.episode_id = e.episode_id AND ship_er.relationship_type = 'speakerships'
    LEFT JOIN planning_center.publishing_speakerships_relationships spr
        ON spr.speakership_id = ship_er.relationship_id AND spr.relationship_type = 'speaker'
    LEFT JOIN planning_center.publishing_speakers sp ON sp.speaker_id = spr.relationship_id
    LEFT JOIN planning_center.publishing_episodes_relationships res_er
        ON res_er.episode_id = e.episode_id AND res_er.relationship_type = 'episode_resources'
    WHERE s.published = true
    GROUP BY s.series_id, s.title, s.description, s.started_at, 
             s.ended_at, s.episodes_count
),
series_rankings AS (
    SELECT 
        *,
        RANK() OVER (ORDER BY total_watches DESC) as watch_rank,
        RANK() OVER (ORDER BY avg_watches_per_episode DESC) as avg_watch_rank,
        CASE 
            WHEN ended_at IS NULL THEN 'Ongoing'
            WHEN ended_at > CURRENT_DATE THEN 'Upcoming'
            ELSE 'Completed'
        END as series_status,
        EXTRACT(DAY FROM (COALESCE(ended_at, CURRENT_DATE) - started_at)) as duration_days
    FROM series_metrics
)
SELECT 
    series_title,
    series_status,
    TO_CHAR(started_at, 'Mon DD, YYYY') as start_date,
    TO_CHAR(ended_at, 'Mon DD, YYYY') as end_date,
    actual_episodes || '/' || planned_episodes as episode_progress,
    duration_days as series_duration_days,
    speakers,
    speaker_count,
    total_watches,
    watch_rank as watch_ranking,
    ROUND(avg_watches_per_episode::numeric, 0) as avg_watches,
    avg_watch_rank as avg_watch_ranking,
    best_episode_watches,
    worst_episode_watches,
    ROUND(watch_stddev::numeric, 0) as watch_consistency_score,
    total_resources,
    ROUND(total_resources::numeric / NULLIF(actual_episodes, 0), 1) as resources_per_episode,
    CASE 
        WHEN avg_watches_per_episode > 100 THEN 'High Engagement'
        WHEN avg_watches_per_episode > 50 THEN 'Good Engagement'
        WHEN avg_watches_per_episode > 20 THEN 'Moderate Engagement'
        ELSE 'Low Engagement'
    END as performance_rating
FROM series_rankings
ORDER BY 
    CASE series_status 
        WHEN 'Ongoing' THEN 1 
        WHEN 'Upcoming' THEN 2 
        ELSE 3 
    END,
    started_at DESC;

Biblical Text Coverage Report

Track scripture coverage across teaching.

This report requires recent episode descriptions to contain references in a Book chapter format such as John 3. It returns no rows when descriptions use another convention or do not include scripture references; customize the regular expression for your content.

-- Biblical Text Coverage Analysis Report
-- Note: This assumes you track scripture references in episode descriptions or a separate table
WITH scripture_extraction AS (
    SELECT 
        e.episode_id,
        e.title,
        e.description,
        e.published_live_at,
        s.title as series_title,
        -- Extract potential scripture references from description
        -- This is a simplified pattern - enhance based on your needs
        REGEXP_MATCHES(
            e.description, 
            '(Genesis|Exodus|Leviticus|Numbers|Deuteronomy|Joshua|Judges|Ruth|Samuel|Kings|Chronicles|Ezra|Nehemiah|Esther|Job|Psalms|Proverbs|Ecclesiastes|Song|Isaiah|Jeremiah|Lamentations|Ezekiel|Daniel|Hosea|Joel|Amos|Obadiah|Jonah|Micah|Nahum|Habakkuk|Zephaniah|Haggai|Zechariah|Malachi|Matthew|Mark|Luke|John|Acts|Romans|Corinthians|Galatians|Ephesians|Philippians|Colossians|Thessalonians|Timothy|Titus|Philemon|Hebrews|James|Peter|John|Jude|Revelation)\s+\d+',
            'gi'
        ) as scripture_refs
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episodes_relationships er_series
        ON er_series.episode_id = e.episode_id AND er_series.relationship_type = 'series'
    LEFT JOIN planning_center.publishing_series s ON s.series_id = er_series.relationship_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '1 year'
        AND e.description IS NOT NULL
),
scripture_summary AS (
    SELECT 
        CASE 
            WHEN scripture_refs[1] IN ('Genesis','Exodus','Leviticus','Numbers','Deuteronomy') THEN 'Pentateuch'
            WHEN scripture_refs[1] IN ('Joshua','Judges','Ruth','Samuel','Kings','Chronicles','Ezra','Nehemiah','Esther') THEN 'Historical Books'
            WHEN scripture_refs[1] IN ('Job','Psalms','Proverbs','Ecclesiastes','Song') THEN 'Wisdom Literature'
            WHEN scripture_refs[1] IN ('Isaiah','Jeremiah','Lamentations','Ezekiel','Daniel','Hosea','Joel','Amos','Obadiah','Jonah','Micah','Nahum','Habakkuk','Zephaniah','Haggai','Zechariah','Malachi') THEN 'Prophets'
            WHEN scripture_refs[1] IN ('Matthew','Mark','Luke','John') THEN 'Gospels'
            WHEN scripture_refs[1] = 'Acts' THEN 'Acts'
            WHEN scripture_refs[1] IN ('Romans','Corinthians','Galatians','Ephesians','Philippians','Colossians','Thessalonians','Timothy','Titus','Philemon') THEN 'Pauline Epistles'
            WHEN scripture_refs[1] IN ('Hebrews','James','Peter','John','Jude') THEN 'General Epistles'
            WHEN scripture_refs[1] = 'Revelation' THEN 'Revelation'
            ELSE 'Other'
        END as book_category,
        scripture_refs[1] as book_name,
        COUNT(DISTINCT episode_id) as episode_count,
        STRING_AGG(DISTINCT series_title, ', ') as series_using_book
    FROM scripture_extraction
    WHERE scripture_refs IS NOT NULL
    GROUP BY book_category, scripture_refs[1]
)
SELECT 
    book_category,
    COUNT(DISTINCT book_name) as books_covered,
    SUM(episode_count) as total_episodes,
    STRING_AGG(book_name || ' (' || episode_count || ')', ', ' ORDER BY episode_count DESC) as books_and_frequency,
    ROUND(SUM(episode_count)::numeric / 52, 1) as episodes_per_week_avg
FROM scripture_summary
GROUP BY book_category
ORDER BY total_episodes DESC;

Speaker Reports

Speaker Performance Dashboard

Comprehensive speaker metrics and analysis.

-- Speaker Performance Dashboard Report
WITH speaker_base_metrics AS (
    SELECT 
        sp.speaker_id,
        sp.formatted_name as speaker_name,
        COUNT(DISTINCT e.episode_id) as total_episodes,
        COUNT(DISTINCT ser_er.relationship_id) as series_count,
        MIN(e.published_live_at) as first_episode,
        MAX(e.published_live_at) as latest_episode,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches,
        SUM(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as total_watches,
        STDDEV(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as watch_consistency
    FROM planning_center.publishing_speakers sp
    JOIN planning_center.publishing_speakerships_relationships spr
        ON spr.relationship_id = sp.speaker_id AND spr.relationship_type = 'speaker'
    JOIN planning_center.publishing_episodes_relationships ship_er
        ON ship_er.relationship_id = spr.speakership_id AND ship_er.relationship_type = 'speakerships'
    JOIN planning_center.publishing_episodes e ON e.episode_id = ship_er.episode_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    LEFT JOIN planning_center.publishing_episodes_relationships ser_er
        ON ser_er.episode_id = e.episode_id AND ser_er.relationship_type = 'series'
    WHERE e.published_live_at IS NOT NULL
    GROUP BY sp.speaker_id, sp.formatted_name
),
speaker_recent_metrics AS (
    SELECT 
        sp.speaker_id,
        COUNT(DISTINCT e.episode_id) as recent_episodes,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as recent_avg_watches,
        STRING_AGG(e.title, ', ' ORDER BY e.published_live_at DESC) as recent_episode_titles
    FROM planning_center.publishing_speakers sp
    JOIN planning_center.publishing_speakerships_relationships spr
        ON spr.relationship_id = sp.speaker_id AND spr.relationship_type = 'speaker'
    JOIN planning_center.publishing_episodes_relationships ship_er
        ON ship_er.relationship_id = spr.speakership_id AND ship_er.relationship_type = 'speakerships'
    JOIN planning_center.publishing_episodes e ON e.episode_id = ship_er.episode_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
    GROUP BY sp.speaker_id
),
speaker_rankings AS (
    SELECT 
        sbm.*,
        srm.recent_episodes,
        srm.recent_avg_watches,
        srm.recent_episode_titles,
        RANK() OVER (ORDER BY sbm.total_episodes DESC) as episode_rank,
        RANK() OVER (ORDER BY sbm.avg_watches DESC NULLS LAST) as watch_rank,
        RANK() OVER (ORDER BY sbm.total_watches DESC NULLS LAST) as total_watch_rank,
        EXTRACT(MONTH FROM AGE(CURRENT_DATE, sbm.latest_episode)) as months_since_last_episode
    FROM speaker_base_metrics sbm
    LEFT JOIN speaker_recent_metrics srm 
        ON sbm.speaker_id = srm.speaker_id
)
SELECT 
    speaker_name,
    CASE 
        WHEN months_since_last_episode = 0 THEN 'Active'
        WHEN months_since_last_episode <= 3 THEN 'Recent'
        WHEN months_since_last_episode <= 6 THEN 'Occasional'
        ELSE 'Inactive'
    END as speaker_status,
    total_episodes,
    episode_rank as episode_ranking,
    series_count,
    TO_CHAR(first_episode, 'Mon YYYY') as teaching_since,
    TO_CHAR(latest_episode, 'Mon DD, YYYY') as last_taught,
    months_since_last_episode,
    COALESCE(recent_episodes, 0) as episodes_last_3_months,
    ROUND(avg_watches::numeric, 0) as avg_watches,
    watch_rank as avg_watch_ranking,
    total_watches,
    total_watch_rank as total_watch_ranking,
    ROUND(watch_consistency::numeric, 0) as consistency_score,
    CASE 
        WHEN avg_watches > 50 THEN 'High Engagement'
        WHEN avg_watches > 20 THEN 'Good Engagement'
        WHEN avg_watches > 5 THEN 'Moderate Engagement'
        ELSE 'Developing'
    END as performance_tier,
    LEFT(recent_episode_titles, 100) as recent_episodes_sample
FROM speaker_rankings
ORDER BY 
    CASE 
        WHEN months_since_last_episode = 0 THEN 1
        WHEN months_since_last_episode <= 3 THEN 2
        ELSE 3
    END,
    total_episodes DESC;

Speaker Schedule Report

Track speaking frequency and patterns.

-- Speaker Schedule and Rotation Report
WITH speaker_calendar AS (
    SELECT 
        DATE_TRUNC('month', e.published_live_at) as month,
        sp.speaker_id,
        sp.formatted_name as speaker_name,
        COUNT(DISTINCT e.episode_id) as episodes_in_month,
        STRING_AGG(
            TO_CHAR(e.published_live_at, 'DD') || ': ' || 
            LEFT(e.title, 30), 
            '; ' ORDER BY e.published_live_at
        ) as episode_schedule
    FROM planning_center.publishing_speakers sp
    JOIN planning_center.publishing_speakerships_relationships spr
        ON spr.relationship_id = sp.speaker_id AND spr.relationship_type = 'speaker'
    JOIN planning_center.publishing_episodes_relationships ship_er
        ON ship_er.relationship_id = spr.speakership_id AND ship_er.relationship_type = 'speakerships'
    JOIN planning_center.publishing_episodes e ON e.episode_id = ship_er.episode_id
    WHERE e.published_live_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 months')
    GROUP BY month, sp.speaker_id, sp.formatted_name
),
speaker_summary AS (
    SELECT 
        speaker_id,
        speaker_name,
        COUNT(DISTINCT month) as months_active,
        SUM(episodes_in_month) as total_episodes,
        AVG(episodes_in_month) as avg_episodes_per_active_month,
        MAX(episodes_in_month) as max_episodes_in_month,
        STRING_AGG(
            TO_CHAR(month, 'Mon') || '(' || episodes_in_month || ')',
            ', ' ORDER BY month DESC
        ) as monthly_distribution
    FROM speaker_calendar
    GROUP BY speaker_id, speaker_name
)
SELECT 
    TO_CHAR(sc.month, 'FMMonth YYYY') as month,
    sc.speaker_name,
    sc.episodes_in_month as episodes,
    ROUND(sc.episodes_in_month::numeric * 100.0 / 
          SUM(sc.episodes_in_month) OVER (PARTITION BY sc.month), 1) as pct_of_month,
    ss.total_episodes as six_month_total,
    ss.avg_episodes_per_active_month as avg_per_month,
    sc.episode_schedule as schedule_details,
    CASE 
        WHEN sc.episodes_in_month >= 3 THEN 'Heavy'
        WHEN sc.episodes_in_month = 2 THEN 'Moderate'
        ELSE 'Light'
    END as workload_level
FROM speaker_calendar sc
JOIN speaker_summary ss 
    ON sc.speaker_id = ss.speaker_id
ORDER BY sc.month DESC, sc.episodes_in_month DESC;

Channel and Distribution Reports

Multi-Channel Performance Report

Compare content performance across distribution channels.

-- Multi-Channel Distribution Performance Report
WITH channel_metrics AS (
    SELECT 
        c.channel_id,
        c.name as channel_name,
        COUNT(DISTINCT er_ch.episode_id) as total_episodes,
        COUNT(DISTINCT CASE 
            WHEN e.published_live_at >= CURRENT_DATE - INTERVAL '30 days' 
            THEN e.episode_id 
        END) as recent_episodes,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches,
        SUM(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as total_watches,
        COUNT(DISTINCT et.episode_time_id) as scheduled_publishes,
        MIN(e.published_live_at) as first_publish,
        MAX(e.published_live_at) as latest_publish
    FROM planning_center.publishing_channels c
    LEFT JOIN planning_center.publishing_episodes_relationships er_ch
        ON er_ch.relationship_id = c.channel_id AND er_ch.relationship_type = 'channel'
    LEFT JOIN planning_center.publishing_episodes e ON e.episode_id = er_ch.episode_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    LEFT JOIN planning_center.publishing_episodes_relationships er_et
        ON er_et.episode_id = e.episode_id AND er_et.relationship_type = 'episode_times'
    LEFT JOIN planning_center.publishing_episode_times et ON et.episode_time_id = er_et.relationship_id
    GROUP BY c.channel_id, c.name
),
channel_rankings AS (
    SELECT 
        *,
        RANK() OVER (ORDER BY total_watches DESC NULLS LAST) as watch_rank,
        CASE 
            WHEN latest_publish >= CURRENT_DATE - INTERVAL '7 days' THEN 'Active'
            WHEN latest_publish >= CURRENT_DATE - INTERVAL '30 days' THEN 'Recent'
            ELSE 'Inactive'
        END as channel_status
    FROM channel_metrics
)
SELECT 
    channel_name,
    channel_status,
    total_episodes,
    recent_episodes as episodes_last_30_days,
    TO_CHAR(first_publish, 'Mon YYYY') as active_since,
    TO_CHAR(latest_publish, 'Mon DD, YYYY') as last_publish,
    ROUND(avg_watches::numeric, 0) as avg_watches,
    watch_rank,
    total_watches,
    scheduled_publishes as future_scheduled,
    CASE 
        WHEN total_watches > 1000 THEN 'High Impact'
        WHEN total_watches > 500 THEN 'Growing'
        WHEN total_watches > 100 THEN 'Moderate'
        ELSE 'Developing'
    END as channel_tier
FROM channel_rankings
ORDER BY channel_status, total_watches DESC NULLS LAST;

Engagement and Growth Reports

Audience Growth Trajectory Report

Track audience growth over time.

-- Audience Growth Trajectory Report
WITH monthly_audience AS (
    SELECT 
        DATE_TRUNC('month', e.published_live_at) as month,
        COUNT(DISTINCT e.episode_id) as episodes_published,
        SUM(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as total_watches,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches_per_episode
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '24 months'
        AND e.published_live_at < DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 month')
    GROUP BY month
),
growth_metrics AS (
    SELECT 
        month,
        episodes_published,
        total_watches,
        avg_watches_per_episode,
        LAG(total_watches, 1) OVER (ORDER BY month) as prev_month_watches,
        LAG(total_watches, 12) OVER (ORDER BY month) as year_ago_watches,
        AVG(total_watches) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as three_month_avg,
        AVG(total_watches) OVER (ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) as twelve_month_avg
    FROM monthly_audience
)
SELECT 
    TO_CHAR(month, 'Mon YYYY') as month,
    episodes_published,
    total_watches,
    ROUND(((total_watches::numeric - prev_month_watches) / 
           NULLIF(prev_month_watches, 0)) * 100, 1) as mom_growth_pct,
    ROUND(((total_watches::numeric - year_ago_watches) / 
           NULLIF(year_ago_watches, 0)) * 100, 1) as yoy_growth_pct,
    ROUND(avg_watches_per_episode::numeric, 0) as avg_watches_per_episode,
    ROUND(three_month_avg::numeric, 0) as three_month_rolling_avg,
    ROUND(twelve_month_avg::numeric, 0) as twelve_month_rolling_avg,
    CASE 
        WHEN total_watches > prev_month_watches * 1.2 THEN 'Strong Growth'
        WHEN total_watches > prev_month_watches THEN 'Moderate Growth'
        WHEN total_watches > prev_month_watches * 0.9 THEN 'Stable'
        ELSE 'Declining'
    END as trend,
    CASE 
        WHEN month = DATE_TRUNC('month', CURRENT_DATE) THEN '*** CURRENT MONTH ***'
        ELSE ''
    END as note
FROM growth_metrics
ORDER BY month DESC
LIMIT 24;

Weekly Publishing Cadence Report

Analyze your publishing consistency.

-- Weekly Publishing Cadence and Consistency Report
WITH weekly_publishing AS (
    SELECT 
        DATE_TRUNC('week', e.published_live_at) as week,
        COUNT(DISTINCT e.episode_id) as episodes_published,
        COUNT(DISTINCT ser_er.relationship_id) as active_series,
        COUNT(DISTINCT spr.relationship_id) as speakers_used,
        SUM(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as weekly_watches,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches,
        ARRAY_AGG(DISTINCT EXTRACT(DOW FROM e.published_live_at)::int ORDER BY EXTRACT(DOW FROM e.published_live_at)::int) as publishing_days,
        STRING_AGG(e.title, '; ' ORDER BY e.published_live_at) as episode_titles
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    LEFT JOIN planning_center.publishing_episodes_relationships ser_er
        ON ser_er.episode_id = e.episode_id AND ser_er.relationship_type = 'series'
    LEFT JOIN planning_center.publishing_episodes_relationships ship_er
        ON ship_er.episode_id = e.episode_id AND ship_er.relationship_type = 'speakerships'
    LEFT JOIN planning_center.publishing_speakerships_relationships spr
        ON spr.speakership_id = ship_er.relationship_id AND spr.relationship_type = 'speaker'
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '12 weeks'
    GROUP BY week
),
weekly_analysis AS (
    SELECT 
        week,
        episodes_published,
        active_series,
        speakers_used,
        weekly_watches,
        avg_watches,
        publishing_days,
        CASE 
            WHEN 0 = ANY(publishing_days) THEN 'Sunday'
            WHEN 1 = ANY(publishing_days) THEN 'Monday'
            WHEN 2 = ANY(publishing_days) THEN 'Tuesday'
            WHEN 3 = ANY(publishing_days) THEN 'Wednesday'
            WHEN 4 = ANY(publishing_days) THEN 'Thursday'
            WHEN 5 = ANY(publishing_days) THEN 'Friday'
            WHEN 6 = ANY(publishing_days) THEN 'Saturday'
        END as primary_publish_day,
        ARRAY_LENGTH(publishing_days, 1) as days_with_content,
        LEFT(episode_titles, 100) as episode_sample
    FROM weekly_publishing
)
SELECT 
    TO_CHAR(week, 'Week of Mon DD, YYYY') as week,
    episodes_published,
    CASE 
        WHEN episodes_published = 0 THEN '❌ No Content'
        WHEN episodes_published = 1 THEN '✅ Standard'
        WHEN episodes_published = 2 THEN '⭐ Above Average'
        ELSE '🔥 High Output'
    END as publishing_level,
    active_series,
    speakers_used,
    weekly_watches,
    ROUND(avg_watches::numeric, 0) as avg_watches,
    days_with_content,
    primary_publish_day,
    episode_sample
FROM weekly_analysis
ORDER BY week DESC;

Export and Automation Tips

Scheduling These Reports

  1. Daily Reports: Executive Dashboard (for high-activity churches)
  2. Weekly Reports: Publishing Cadence, Speaker Schedule
  3. Monthly Reports: Monthly Executive Summary, Series Performance
  4. Quarterly Reports: Quarterly Performance, Biblical Coverage

Export Formats

  • CSV: Best for Excel analysis and pivot tables
  • PDF: Best for board presentations
  • JSON: Best for dashboard integrations
  • HTML: Best for email reports

Automation Ideas

Your Parable database connection is read-only. You cannot create views, materialized views, or indexes through it. Save this query in your BI tool (as a dataset or extract) or as a Parable report instead.

-- Weekly publishing summary — save as a scheduled report or BI dataset
SELECT 
    DATE_TRUNC('week', CURRENT_DATE) as report_week,
    COUNT(DISTINCT e.episode_id) as episodes_this_week,
    SUM(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as total_watches,
    AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches,
    COUNT(DISTINCT spr.relationship_id) as speakers,
    COUNT(DISTINCT ser_er.relationship_id) as series
FROM planning_center.publishing_episodes e
LEFT JOIN planning_center.publishing_episode_statistics est 
    ON est.episode_statistics_id = 'es-' || e.episode_id
LEFT JOIN planning_center.publishing_episodes_relationships ser_er
    ON ser_er.episode_id = e.episode_id AND ser_er.relationship_type = 'series'
LEFT JOIN planning_center.publishing_episodes_relationships ship_er
    ON ship_er.episode_id = e.episode_id AND ship_er.relationship_type = 'speakerships'
LEFT JOIN planning_center.publishing_speakerships_relationships spr
    ON spr.speakership_id = ship_er.relationship_id AND spr.relationship_type = 'speaker'
WHERE e.published_live_at >= DATE_TRUNC('week', CURRENT_DATE)
    AND e.published_live_at < DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week';

Next Steps

  • Customize these reports with your church's specific KPIs
  • Add filters for specific campuses or ministries
  • Create dashboards using these queries as data sources
  • Set up alerts for unusual patterns or milestones

Transform your publishing data into actionable insights. These reports help you make data-driven decisions about your content strategy.

Advanced Planning Center Publishing Queries

Advanced Publishing SQL for cross-module analysis: connect sermon series to attendance and giving, and measure content reach across channels.

Planning Center Registrations SQL Queries

Query Planning Center Registrations data with SQL to track event signups, manage waitlists, and analyze attendance patterns across church events.

On this page

Query RequirementsSchema PrefixRow Level Security (RLS)Executive Dashboard ReportsMonthly Publishing Executive SummaryQuarterly Publishing Performance ReportContent Analysis ReportsSermon Series Performance ReportBiblical Text Coverage ReportSpeaker ReportsSpeaker Performance DashboardSpeaker Schedule ReportChannel and Distribution ReportsMulti-Channel Performance ReportEngagement and Growth ReportsAudience Growth Trajectory ReportWeekly Publishing Cadence ReportExport and Automation TipsScheduling These ReportsExport FormatsAutomation IdeasNext Steps