Publishing Reporting Examples

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 e.series_id) as active_series,
        COUNT(DISTINCT sp.speaker_id) as unique_speakers,
        COUNT(DISTINCT c.channel_id) as active_channels,
        SUM(est.view_count) as total_views,
        SUM(est.download_count) as total_downloads,
        SUM(est.unique_viewers) as total_unique_viewers,
        AVG(est.view_count) as avg_views_per_episode,
        COUNT(DISTINCT er.episode_resource_id) as resources_created
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    LEFT JOIN planning_center.publishing_speakerships ship 
        ON e.episode_id = ship.episode_id
    LEFT JOIN planning_center.publishing_speakers sp 
        ON ship.speaker_id = sp.speaker_id
    LEFT JOIN planning_center.publishing_channels c 
        ON e.channel_id = c.channel_id
    LEFT JOIN planning_center.publishing_episode_resources er 
        ON e.episode_id = er.episode_id
    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(est.view_count) as total_views,
        SUM(est.download_count) as total_downloads,
        AVG(est.view_count) as avg_views_per_episode
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.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(est.view_count) as total_views,
        SUM(est.download_count) as total_downloads,
        AVG(est.view_count) as avg_views_per_episode
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.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,
        est.view_count,
        s.title as series_title
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    LEFT JOIN planning_center.publishing_series s 
        ON e.series_id = s.series_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 est.view_count DESC NULLS LAST
    LIMIT 3
)
SELECT 
    TO_CHAR(cm.report_month, 'Month 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_views as total_views_this_month,
    pm.total_views as total_views_last_month,
    ROUND(((cm.total_views::numeric - pm.total_views) / 
           NULLIF(pm.total_views, 0)) * 100, 1) as view_growth_mom_pct,
    ya.total_views as total_views_year_ago,
    ROUND(((cm.total_views::numeric - ya.total_views) / 
           NULLIF(ya.total_views, 0)) * 100, 1) as view_growth_yoy_pct,
    cm.total_downloads as total_downloads_this_month,
    cm.total_unique_viewers as unique_viewers_this_month,
    ROUND(cm.avg_views_per_episode::numeric, 0) as avg_views_per_episode,
    '=== TOP PERFORMING CONTENT ===' as section_3,
    (SELECT STRING_AGG(title || ' (' || COALESCE(view_count::text, 'N/A') || ' views)', ', ' 
                       ORDER BY view_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 e.series_id) as series,
        COUNT(DISTINCT sp.speaker_id) as speakers,
        SUM(est.view_count) as views,
        SUM(est.download_count) as downloads,
        SUM(est.unique_viewers) as unique_viewers,
        AVG(est.view_count) as avg_views,
        AVG(est.average_watch_time) / 60 as avg_watch_minutes,
        COUNT(DISTINCT er.episode_resource_id) as resources
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    LEFT JOIN planning_center.publishing_speakerships ship 
        ON e.episode_id = ship.episode_id
    LEFT JOIN planning_center.publishing_speakers sp 
        ON ship.speaker_id = sp.speaker_id
    LEFT JOIN planning_center.publishing_episode_resources er 
        ON e.episode_id = er.episode_id
    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.views as total_views_this_quarter,
    ROUND(cq.avg_views::numeric, 0) as avg_views_per_episode,
    cq.downloads as total_downloads_this_quarter,
    cq.unique_viewers as unique_viewers_this_quarter,
    ROUND(cq.avg_watch_minutes::numeric, 1) as avg_watch_minutes,
    ROUND(((cq.views::numeric - pq.views) / NULLIF(pq.views, 0)) * 100, 1) as view_qoq_change_pct,
    ROUND(((cq.views::numeric - yaq.views) / NULLIF(yaq.views, 0)) * 100, 1) as view_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.views > pq.views * 1.1 THEN 'Strong Growth'
        WHEN cq.views > pq.views THEN 'Moderate Growth'
        WHEN cq.views > pq.views * 0.9 THEN 'Stable'
        ELSE 'Declining'
    END as quarter_performance_rating,
    CASE 
        WHEN cq.avg_watch_minutes > 20 THEN 'Excellent'
        WHEN cq.avg_watch_minutes > 15 THEN 'Good'
        WHEN cq.avg_watch_minutes > 10 THEN 'Fair'
        ELSE 'Needs Improvement'
    END as engagement_quality_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(est.view_count) as total_views,
        AVG(est.view_count) as avg_views_per_episode,
        SUM(est.download_count) as total_downloads,
        SUM(est.unique_viewers) as total_unique_viewers,
        MAX(est.view_count) as best_episode_views,
        MIN(est.view_count) as worst_episode_views,
        STDDEV(est.view_count) as view_stddev,
        COUNT(DISTINCT sp.speaker_id) as speaker_count,
        STRING_AGG(DISTINCT sp.name, ', ' ORDER BY sp.name) as speakers,
        COUNT(DISTINCT er.episode_resource_id) as total_resources
    FROM planning_center.publishing_series s
    LEFT JOIN planning_center.publishing_episodes e 
        ON s.series_id = e.series_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    LEFT JOIN planning_center.publishing_speakerships ship 
        ON e.episode_id = ship.episode_id
    LEFT JOIN planning_center.publishing_speakers sp 
        ON ship.speaker_id = sp.speaker_id
    LEFT JOIN planning_center.publishing_episode_resources er 
        ON e.episode_id = er.episode_id
    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_views DESC) as view_rank,
        RANK() OVER (ORDER BY avg_views_per_episode DESC) as avg_view_rank,
        RANK() OVER (ORDER BY total_downloads DESC) as download_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_views,
    view_rank as view_ranking,
    ROUND(avg_views_per_episode::numeric, 0) as avg_views,
    avg_view_rank as avg_view_ranking,
    total_downloads,
    download_rank as download_ranking,
    total_unique_viewers,
    best_episode_views,
    worst_episode_views,
    ROUND(view_stddev::numeric, 0) as view_consistency_score,
    total_resources,
    ROUND(total_resources::numeric / NULLIF(actual_episodes, 0), 1) as resources_per_episode,
    CASE 
        WHEN avg_views_per_episode > 1000 THEN '⭐⭐⭐⭐⭐'
        WHEN avg_views_per_episode > 750 THEN '⭐⭐⭐⭐'
        WHEN avg_views_per_episode > 500 THEN '⭐⭐⭐'
        WHEN avg_views_per_episode > 250 THEN '⭐⭐'
        ELSE '⭐'
    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.

-- 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_series s 
        ON e.series_id = s.series_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.name as speaker_name,
        sp.description as speaker_bio,
        COUNT(DISTINCT e.episode_id) as total_episodes,
        COUNT(DISTINCT e.series_id) as series_count,
        MIN(e.published_live_at) as first_episode,
        MAX(e.published_live_at) as latest_episode,
        AVG(est.view_count) as avg_views,
        SUM(est.view_count) as total_views,
        AVG(est.download_count) as avg_downloads,
        SUM(est.download_count) as total_downloads,
        AVG(est.unique_viewers) as avg_unique_viewers,
        AVG(est.average_watch_time) / 60 as avg_watch_minutes,
        STDDEV(est.view_count) as view_consistency
    FROM planning_center.publishing_speakers sp
    JOIN planning_center.publishing_speakerships ship 
        ON sp.speaker_id = ship.speaker_id
    JOIN planning_center.publishing_episodes e 
        ON ship.episode_id = e.episode_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    WHERE e.published_live_at IS NOT NULL
    GROUP BY sp.speaker_id, sp.name, sp.description
),
speaker_recent_metrics AS (
    SELECT 
        sp.speaker_id,
        COUNT(DISTINCT e.episode_id) as recent_episodes,
        AVG(est.view_count) as recent_avg_views,
        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 ship 
        ON sp.speaker_id = ship.speaker_id
    JOIN planning_center.publishing_episodes e 
        ON ship.episode_id = e.episode_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.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_views,
        srm.recent_episode_titles,
        RANK() OVER (ORDER BY sbm.total_episodes DESC) as episode_rank,
        RANK() OVER (ORDER BY sbm.avg_views DESC NULLS LAST) as view_rank,
        RANK() OVER (ORDER BY sbm.total_views DESC NULLS LAST) as total_view_rank,
        RANK() OVER (ORDER BY sbm.avg_watch_minutes DESC NULLS LAST) as engagement_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_views::numeric, 0) as avg_views,
    view_rank as avg_view_ranking,
    total_views,
    total_view_rank as total_view_ranking,
    ROUND(avg_downloads::numeric, 0) as avg_downloads,
    ROUND(avg_unique_viewers::numeric, 0) as avg_unique_viewers,
    ROUND(avg_watch_minutes::numeric, 1) as avg_watch_minutes,
    engagement_rank as engagement_ranking,
    ROUND(view_consistency::numeric, 0) as consistency_score,
    CASE 
        WHEN avg_views > 1000 AND avg_watch_minutes > 20 THEN 'Star Performer'
        WHEN avg_views > 750 OR avg_watch_minutes > 15 THEN 'Strong Performer'
        WHEN avg_views > 500 THEN 'Solid Performer'
        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.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 ship 
        ON sp.speaker_id = ship.speaker_id
    JOIN planning_center.publishing_episodes e 
        ON ship.episode_id = e.episode_id
    WHERE e.published_live_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 months')
    GROUP BY month, sp.speaker_id, sp.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, 'Month 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,
        c.kind as channel_type,
        c.church_center_enabled,
        COUNT(DISTINCT e.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(est.view_count) as avg_views,
        SUM(est.view_count) as total_views,
        AVG(est.download_count) as avg_downloads,
        SUM(est.download_count) as total_downloads,
        AVG(est.unique_viewers) as avg_unique_viewers,
        AVG(est.average_watch_time) / 60 as avg_watch_minutes,
        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 e 
        ON c.channel_id = e.channel_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.episode_id
    LEFT JOIN planning_center.publishing_episode_times et 
        ON c.channel_id = et.channel_id
    GROUP BY c.channel_id, c.name, c.kind, c.church_center_enabled
),
channel_rankings AS (
    SELECT 
        *,
        RANK() OVER (ORDER BY total_views DESC NULLS LAST) as view_rank,
        RANK() OVER (ORDER BY total_downloads DESC NULLS LAST) as download_rank,
        RANK() OVER (ORDER BY avg_watch_minutes DESC NULLS LAST) as engagement_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_type,
    channel_status,
    CASE WHEN church_center_enabled THEN 'Yes' ELSE 'No' END as church_center,
    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_views::numeric, 0) as avg_views,
    view_rank,
    total_views,
    ROUND(avg_downloads::numeric, 0) as avg_downloads,
    download_rank,
    total_downloads,
    ROUND(avg_unique_viewers::numeric, 0) as avg_unique_viewers,
    ROUND(avg_watch_minutes::numeric, 1) as avg_watch_minutes,
    engagement_rank,
    scheduled_publishes as future_scheduled,
    CASE 
        WHEN total_views > 10000 THEN '🏆 High Impact'
        WHEN total_views > 5000 THEN '📈 Growing'
        WHEN total_views > 1000 THEN '📊 Moderate'
        ELSE '🌱 Developing'
    END as channel_tier
FROM channel_rankings
ORDER BY channel_status, total_views 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(est.view_count) as total_views,
        SUM(est.download_count) as total_downloads,
        SUM(est.unique_viewers) as unique_viewers,
        AVG(est.view_count) as avg_views_per_episode,
        AVG(est.average_watch_time) / 60 as avg_watch_minutes
    FROM planning_center.publishing_episodes e
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON e.episode_id = est.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_views,
        total_downloads,
        unique_viewers,
        avg_views_per_episode,
        avg_watch_minutes,
        LAG(total_views, 1) OVER (ORDER BY month) as prev_month_views,
        LAG(total_views, 12) OVER (ORDER BY month) as year_ago_views,
        AVG(total_views) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as three_month_avg,
        AVG(total_views) 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_views,
    ROUND(((total_views::numeric - prev_month_views) / 
           NULLIF(prev_month_views, 0)) * 100, 1) as mom_growth_pct,
    ROUND(((total_views::numeric - year_ago_views) / 
           NULLIF(year_ago_views, 0)) * 100, 1) as yoy_growth_pct,
    total_downloads,
    unique_viewers,
    ROUND(avg_views_per_episode::numeric, 0) as avg_views_per_episode,
    ROUND(avg_watch_minutes::numeric, 1) as avg_watch_minutes,
    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_views > prev_month_views * 1.2 THEN '📈 Strong Growth'
        WHEN total_views > prev_month_views THEN '➡️ Moderate Growth'
        WHEN total_views > prev_month_views * 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 e.series_id) as active_series,
        COUNT(DISTINCT sp.speaker_id) as speakers_used,
        SUM(est.view_count) as weekly_views,
        AVG(est.view_count) as avg_views,
        ARRAY_AGG(DISTINCT EXTRACT(DOW FROM e.published_live_at)::int ORDER BY EXTRACT(DOW FROM e.published_live_at)) 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 e.episode_id = est.episode_id
    LEFT JOIN planning_center.publishing_speakerships ship 
        ON e.episode_id = ship.episode_id
    LEFT JOIN planning_center.publishing_speakers sp 
        ON ship.speaker_id = sp.speaker_id
    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_views,
        avg_views,
        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_views,
    ROUND(avg_views::numeric, 0) as avg_views,
    days_with_content,
    primary_publish_day,
    episode_sample
FROM weekly_analysis
ORDER BY week DESC;

Export and Automation Tips

Scheduling These Reports

Daily Reports: Executive Dashboard (for high-activity churches)
Weekly Reports: Publishing Cadence, Speaker Schedule
Monthly Reports: Monthly Executive Summary, Series Performance
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

-- Create a view for automated reporting
CREATE VIEW planning_center.publishing_weekly_summary AS
SELECT 
    DATE_TRUNC('week', CURRENT_DATE) as report_week,
    COUNT(DISTINCT e.episode_id) as episodes_this_week,
    SUM(est.view_count) as total_views,
    AVG(est.view_count) as avg_views,
    COUNT(DISTINCT sp.speaker_id) as speakers,
    COUNT(DISTINCT e.series_id) as series
FROM planning_center.publishing_episodes e
LEFT JOIN planning_center.publishing_episode_statistics est 
    ON e.episode_id = est.episode_id
LEFT JOIN planning_center.publishing_speakerships ship 
    ON e.episode_id = ship.episode_id
LEFT JOIN planning_center.publishing_speakers sp 
    ON ship.speaker_id = sp.speaker_id
WHERE e.published_live_at >= DATE_TRUNC('week', CURRENT_DATE)
    AND e.published_live_at < DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '1 week';

-- Then schedule: SELECT * FROM planning_center.publishing_weekly_summary;

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.

​Publishing Reporting Examples

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Executive Dashboard Reports

​Monthly Publishing Executive Summary

​Quarterly Publishing Performance Report

​Content Analysis Reports

​Sermon Series Performance Report

​Biblical Text Coverage Report

​Speaker Reports

​Speaker Performance Dashboard

​Speaker Schedule Report

​Channel and Distribution Reports

​Multi-Channel Performance Report

​Engagement and Growth Reports

​Audience Growth Trajectory Report

​Weekly Publishing Cadence Report

​Export and Automation Tips

​Scheduling These Reports

​Export Formats

​Automation Ideas

​Next Steps