Skip to main content
Parable
Open Parable

Basic Planning Center Publishing Queries

Simple SQL for Planning Center Publishing: recent episodes, episodes in a series, series episode counts, speaker frequency, and totals by channel.

Simple, powerful queries to get started with your Publishing data. Copy, paste, and customize these for your needs!

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. when querying.

✅ 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 – returns active records by default

Do not add these filters manually—RLS already applies them and redundant predicates can suppress data or hurt performance:

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

Focus on content-specific filters (series, published dates, media metadata) while relying on RLS for tenancy and status.

Recent Episodes

Latest Published Episodes

See your most recent content with key details.

-- Get the 20 most recent published episodes
SELECT 
    episode_id,
    title,
    description,
    published_live_at,
    video_url,
    church_center_url
FROM planning_center.publishing_episodes
WHERE published_live_at IS NOT NULL
ORDER BY published_live_at DESC
LIMIT 20;

Episodes by Series

Find all episodes in a specific series.

-- Get all episodes from a series (replace series name)
SELECT 
    e.title as episode_title,
    e.published_live_at,
    e.description,
    s.title as series_title,
    s.started_at as series_start,
    s.ended_at as series_end
FROM planning_center.publishing_episodes e
JOIN planning_center.publishing_episodes_relationships er
    ON er.episode_id = e.episode_id AND er.relationship_type = 'series'
JOIN planning_center.publishing_series s ON s.series_id = er.relationship_id
WHERE s.title LIKE '%Christmas%'  -- Change series name here
ORDER BY e.published_live_at;

Episodes This Year

All episodes published in the current year.

-- Episodes published this year
SELECT 
    title,
    published_live_at,
    video_url,
    DATE_PART('month', published_live_at) as month,
    DATE_PART('week', published_live_at) as week
FROM planning_center.publishing_episodes
WHERE published_live_at >= DATE_TRUNC('year', CURRENT_DATE)
    AND published_live_at IS NOT NULL
ORDER BY published_live_at DESC;

Series Analytics

Active Series List

All series with episode counts.

-- List all series with episode counts
SELECT 
    s.series_id,
    s.title,
    s.description,
    s.started_at,
    s.ended_at,
    s.episodes_count,
    s.published,
    COUNT(er.episode_id) as actual_episode_count
FROM planning_center.publishing_series s
LEFT JOIN planning_center.publishing_episodes_relationships er
    ON er.relationship_id = s.series_id AND er.relationship_type = 'series'
GROUP BY s.series_id, s.title, s.description, 
         s.started_at, s.ended_at, s.episodes_count, s.published
ORDER BY s.started_at DESC;

Current/Recent Series

Series that are currently active or recently ended.

-- Current and recent series (last 6 months)
SELECT 
    title,
    description,
    started_at,
    ended_at,
    episodes_count,
    CASE 
        WHEN ended_at IS NULL THEN 'Ongoing'
        WHEN ended_at > CURRENT_DATE THEN 'Upcoming'
        ELSE 'Completed'
    END as status
FROM planning_center.publishing_series
WHERE started_at >= CURRENT_DATE - INTERVAL '6 months'
   OR ended_at IS NULL
   OR ended_at >= CURRENT_DATE - INTERVAL '1 month'
ORDER BY started_at DESC;

Series Duration Analysis

How long do your series typically run?

-- Average series length and episode count
SELECT 
    COUNT(*) as total_series,
    AVG(episodes_count) as avg_episodes_per_series,
    AVG(
        CASE 
            WHEN ended_at IS NOT NULL 
            THEN EXTRACT(DAY FROM (ended_at - started_at))
            ELSE NULL
        END
    ) as avg_series_duration_days,
    MAX(episodes_count) as max_episodes_in_series,
    MIN(episodes_count) as min_episodes_in_series
FROM planning_center.publishing_series
WHERE published = true
    AND started_at IS NOT NULL;

Speaker Insights

Speaker Frequency

How often does each speaker teach?

-- Speaker frequency in the last year
SELECT 
    sp.speaker_id,
    sp.formatted_name as speaker_name,
    COUNT(DISTINCT e.episode_id) as episode_count,
    MIN(e.published_live_at) as first_episode,
    MAX(e.published_live_at) as latest_episode,
    COUNT(DISTINCT ser_er.relationship_id) as series_count
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_speakerships ship ON ship.speakership_id = spr.speakership_id
JOIN planning_center.publishing_episodes_relationships er_ship
    ON er_ship.relationship_id = ship.speakership_id AND er_ship.relationship_type = 'speakerships'
JOIN planning_center.publishing_episodes e ON e.episode_id = er_ship.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 >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY sp.speaker_id, sp.formatted_name
ORDER BY episode_count DESC;

Recent Speakers

Who has spoken in the last few months?

-- Speakers in the last 3 months
SELECT DISTINCT
    sp.formatted_name as speaker_name,
    COUNT(e.episode_id) as recent_episodes,
    MAX(e.published_live_at) as most_recent_episode
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_speakerships ship ON ship.speakership_id = spr.speakership_id
JOIN planning_center.publishing_episodes_relationships er_ship
    ON er_ship.relationship_id = ship.speakership_id AND er_ship.relationship_type = 'speakerships'
JOIN planning_center.publishing_episodes e ON e.episode_id = er_ship.episode_id
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
GROUP BY sp.speaker_id, sp.formatted_name
ORDER BY most_recent_episode DESC;

Speaker and Series Combination

Which speakers taught which series?

-- Speakers by series
SELECT 
    s.title as series_title,
    sp.formatted_name as speaker_name,
    COUNT(e.episode_id) as episodes_in_series,
    MIN(e.published_live_at) as first_episode,
    MAX(e.published_live_at) as last_episode
FROM planning_center.publishing_series s
JOIN planning_center.publishing_episodes_relationships er_series
    ON er_series.relationship_id = s.series_id AND er_series.relationship_type = 'series'
JOIN planning_center.publishing_episodes e ON e.episode_id = er_series.episode_id
JOIN planning_center.publishing_episodes_relationships er_ship
    ON er_ship.episode_id = e.episode_id AND er_ship.relationship_type = 'speakerships'
JOIN planning_center.publishing_speakerships ship ON ship.speakership_id = er_ship.relationship_id
JOIN planning_center.publishing_speakerships_relationships spr
    ON spr.speakership_id = ship.speakership_id AND spr.relationship_type = 'speaker'
JOIN planning_center.publishing_speakers sp ON sp.speaker_id = spr.relationship_id
GROUP BY s.series_id, s.title, s.started_at, sp.speaker_id, sp.formatted_name
ORDER BY s.started_at DESC, episodes_in_series DESC;

Content Performance

Episode View Counts

Track engagement metrics for episodes.

-- Episode performance metrics (library and live watch counts)
-- Note: episode_statistics join uses pattern 'es-' || episode_id
SELECT 
    e.title as episode_title,
    e.published_live_at,
    est.library_watch_count,
    est.live_watch_count,
    COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0) as total_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
    ON er.episode_id = e.episode_id AND er.relationship_type = 'series'
LEFT JOIN planning_center.publishing_series s ON s.series_id = er.relationship_id
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
ORDER BY total_watch_count DESC NULLS LAST
LIMIT 25;

Top Performing Content

Your most viewed episodes.

-- Top 10 most watched episodes of all time
SELECT 
    e.title,
    e.published_live_at,
    est.library_watch_count,
    est.live_watch_count,
    COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0) as total_watch_count,
    s.title as series_title,
    EXTRACT(DAY FROM (CURRENT_DATE - e.published_live_at)) as days_since_published
FROM planning_center.publishing_episodes e
JOIN planning_center.publishing_episode_statistics est 
    ON est.episode_statistics_id = 'es-' || e.episode_id
LEFT JOIN planning_center.publishing_episodes_relationships er
    ON er.episode_id = e.episode_id AND er.relationship_type = 'series'
LEFT JOIN planning_center.publishing_series s ON s.series_id = er.relationship_id
WHERE est.library_watch_count IS NOT NULL OR est.live_watch_count IS NOT NULL
ORDER BY total_watch_count DESC
LIMIT 10;

How does engagement change over time?

-- Weekly engagement summary
SELECT 
    DATE_TRUNC('week', e.published_live_at) as week,
    COUNT(DISTINCT e.episode_id) as episodes_published,
    SUM(COALESCE(est.library_watch_count, 0)) as total_library_watches,
    SUM(COALESCE(est.live_watch_count, 0)) as total_live_watches,
    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 '12 weeks'
GROUP BY week
ORDER BY week DESC;

Channel Distribution

Channel Overview

See all your distribution channels.

-- List all channels with settings
SELECT 
    channel_id,
    name,
    description,
    published,
    enable_audio,
    enable_on_demand_video,
    enable_watch_live,
    podcast_feed_url
FROM planning_center.publishing_channels
ORDER BY name;

Episodes by Channel

Content distribution across channels.

-- Episode count by channel
SELECT 
    c.name as channel_name,
    COUNT(DISTINCT er.episode_id) as episode_count,
    MIN(e.published_live_at) as first_episode,
    MAX(e.published_live_at) as latest_episode
FROM planning_center.publishing_channels c
LEFT JOIN planning_center.publishing_episodes_relationships er
    ON er.relationship_id = c.channel_id AND er.relationship_type = 'channel'
LEFT JOIN planning_center.publishing_episodes e ON e.episode_id = er.episode_id
GROUP BY c.channel_id, c.name
ORDER BY episode_count DESC;

Resources and Materials

Episode Resources

Find downloadable materials for episodes.

-- Resources for recent episodes
SELECT 
    e.title as episode_title,
    e.published_live_at,
    er.title as resource_title,
    er.kind as resource_type,
    er.url
FROM planning_center.publishing_episodes e
JOIN planning_center.publishing_episodes_relationships erel
    ON erel.episode_id = e.episode_id AND erel.relationship_type = 'episode_resources'
JOIN planning_center.publishing_episode_resources er
    ON er.episode_resource_id = erel.relationship_id
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '1 month'
ORDER BY e.published_live_at DESC, er.title;

Resource Types Summary

What types of resources are you providing?

-- Count resources by type
SELECT 
    er.kind as resource_type,
    COUNT(*) as resource_count,
    COUNT(DISTINCT erel.episode_id) as episodes_with_type
FROM planning_center.publishing_episode_resources er
JOIN planning_center.publishing_episodes_relationships erel
    ON erel.relationship_id = er.episode_resource_id AND erel.relationship_type = 'episode_resources'
GROUP BY er.kind
ORDER BY resource_count DESC;

Publishing Schedule

Upcoming Publishing Times

When will content be published next?

-- Next scheduled publishing times by channel
SELECT 
    c.name as channel_name,
    cnt.starts_at as next_publish_at,
    EXTRACT(DAY FROM (cnt.starts_at - CURRENT_TIMESTAMP)) as days_until_publish
FROM planning_center.publishing_channel_next_times cnt
JOIN planning_center.publishing_channels_relationships cr
    ON cr.relationship_id = cnt.channel_next_time_id AND cr.relationship_type = 'next_times'
JOIN planning_center.publishing_channels c ON c.channel_id = cr.channel_id
WHERE cnt.starts_at > CURRENT_TIMESTAMP
ORDER BY cnt.starts_at;

Episode Publishing Schedule

When episodes are scheduled to go live.

-- Episode publishing schedule (upcoming episode times)
SELECT 
    e.title as episode_title,
    et.starts_at as scheduled_time,
    et.current_state as publish_status,
    e.published_live_at as actual_publish_time
FROM planning_center.publishing_episode_times et
JOIN planning_center.publishing_episodes_relationships er
    ON er.relationship_id = et.episode_time_id AND er.relationship_type = 'episode_times'
JOIN planning_center.publishing_episodes e ON e.episode_id = er.episode_id
WHERE et.starts_at >= CURRENT_DATE
ORDER BY et.starts_at;

Quick Metrics

Publishing Summary Dashboard

Key metrics at a glance.

-- Publishing dashboard metrics
SELECT 
    (SELECT COUNT(*) FROM planning_center.publishing_episodes 
     WHERE published_live_at IS NOT NULL) as total_episodes,
    
    (SELECT COUNT(*) FROM planning_center.publishing_series 
     WHERE published = true) as total_series,
    
    (SELECT COUNT(*) FROM planning_center.publishing_speakers) as total_speakers,
    
    (SELECT COUNT(*) FROM planning_center.publishing_channels) as total_channels,
    
    (SELECT SUM(library_watch_count) + SUM(live_watch_count) 
     FROM planning_center.publishing_episode_statistics) as total_watches,
    
    (SELECT COUNT(*) FROM planning_center.publishing_episodes 
     WHERE published_live_at >= CURRENT_DATE - INTERVAL '30 days') as episodes_last_30_days,
    
    (SELECT COUNT(*) FROM planning_center.publishing_episode_resources) as total_resources;

Monthly Publishing Cadence

How consistent is your publishing schedule?

-- Monthly publishing statistics
SELECT 
    DATE_TRUNC('month', e.published_live_at) as month,
    COUNT(DISTINCT e.episode_id) as episodes_published,
    COUNT(DISTINCT er.relationship_id) as active_series,
    COUNT(DISTINCT DATE_TRUNC('week', e.published_live_at)) as weeks_with_content,
    ARRAY_AGG(DISTINCT EXTRACT(DOW FROM e.published_live_at)) as publishing_days
FROM planning_center.publishing_episodes e
LEFT JOIN planning_center.publishing_episodes_relationships er
    ON er.episode_id = e.episode_id AND er.relationship_type = 'series'
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '12 months'
    AND e.published_live_at IS NOT NULL
GROUP BY month
ORDER BY month DESC;

Tips for Using These Queries

  1. Customize Date Ranges - Change INTERVAL values to match your needs
  2. Filter by Channel - Add AND channel_id = 'YOUR_CHANNEL_ID' to focus on specific channels
  3. Add Speaker Filters - Join with speakerships to analyze specific speakers
  4. Export Results - Use your SQL client's export feature for reports
  5. Schedule Reports - Many of these work great as automated weekly/monthly reports

Next Steps

Ready for more complex analysis? Check out:

Start simple, build confidence, then explore more complex queries as you grow!

Planning Center Publishing Data Model

Complete reference for all Planning Center Publishing tables in Parable: episodes, series, speakers, channels, and the relationships between them.

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.

On this page

Query RequirementsSchema PrefixRow Level Security (RLS)Recent EpisodesLatest Published EpisodesEpisodes by SeriesEpisodes This YearSeries AnalyticsActive Series ListCurrent/Recent SeriesSeries Duration AnalysisSpeaker InsightsSpeaker FrequencyRecent SpeakersSpeaker and Series CombinationContent PerformanceEpisode View CountsTop Performing ContentWeekly Engagement TrendsChannel DistributionChannel OverviewEpisodes by ChannelResources and MaterialsEpisode ResourcesResource Types SummaryPublishing ScheduleUpcoming Publishing TimesEpisode Publishing ScheduleQuick MetricsPublishing Summary DashboardMonthly Publishing CadenceTips for Using These QueriesNext Steps