​
Basic Publishing Queries

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_series s 
    ON e.series_id = s.series_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(e.episode_id) as actual_episode_count
FROM planning_center.publishing_series s
LEFT JOIN planning_center.publishing_episodes e 
    ON s.series_id = e.series_id
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.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 e.series_id) as series_count
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 >= CURRENT_DATE - INTERVAL '1 year'
GROUP BY sp.speaker_id, sp.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.name as speaker_name,
    sp.description,
    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 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 >= CURRENT_DATE - INTERVAL '3 months'
GROUP BY sp.speaker_id, sp.name, sp.description
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.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 e 
    ON s.series_id = e.series_id
JOIN planning_center.publishing_speakerships ship 
    ON e.episode_id = ship.episode_id
JOIN planning_center.publishing_speakers sp 
    ON ship.speaker_id = sp.speaker_id
GROUP BY s.series_id, s.title, sp.speaker_id, sp.name
ORDER BY s.started_at DESC, episodes_in_series DESC;

​
Content Performance

​
Episode View Counts

Track engagement metrics for episodes. 
-- Episode performance metrics
SELECT 
    e.title as episode_title,
    e.published_live_at,
    est.view_count,
    est.download_count,
    est.unique_viewers,
    est.average_watch_time / 60 as avg_watch_minutes,
    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 >= CURRENT_DATE - INTERVAL '3 months'
ORDER BY est.view_count DESC NULLS LAST
LIMIT 25;

​
Top Performing Content

Your most viewed episodes. 
-- Top 10 most viewed episodes of all time
SELECT 
    e.title,
    e.published_live_at,
    est.view_count,
    est.download_count,
    est.unique_viewers,
    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 e.episode_id = est.episode_id
LEFT JOIN planning_center.publishing_series s 
    ON e.series_id = s.series_id
WHERE est.view_count IS NOT NULL
ORDER BY est.view_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(est.view_count) as total_views,
    SUM(est.download_count) as total_downloads,
    AVG(est.view_count) as avg_views_per_episode,
    SUM(est.unique_viewers) as total_unique_viewers
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 '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,
    kind,
    description,
    streaming_service,
    church_center_enabled,
    default_publish_time,
    time_zone
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,
    c.kind as channel_type,
    COUNT(DISTINCT e.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 e 
    ON c.channel_id = e.channel_id
GROUP BY c.channel_id, c.name, c.kind
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.name as resource_name,
    er.kind as resource_type,
    er.url,
    er.file_size / 1048576 as size_mb
FROM planning_center.publishing_episodes e
JOIN planning_center.publishing_episode_resources er 
    ON e.episode_id = er.episode_id
WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '1 month'
ORDER BY e.published_live_at DESC, er.name;

​
Resource Types Summary

What types of resources are you providing? 
-- Count resources by type
SELECT 
    kind as resource_type,
    COUNT(*) as resource_count,
    COUNT(DISTINCT episode_id) as episodes_with_type,
    SUM(file_size) / 1073741824 as total_size_gb
FROM planning_center.publishing_episode_resources
GROUP BY 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.next_publish_at,
    EXTRACT(DAY FROM (cnt.next_publish_at - CURRENT_TIMESTAMP)) as days_until_publish,
    c.default_publish_time
FROM planning_center.publishing_channel_next_times cnt
JOIN planning_center.publishing_channels c 
    ON cnt.channel_id = c.channel_id
WHERE cnt.next_publish_at > CURRENT_TIMESTAMP
ORDER BY cnt.next_publish_at;

​
Episode Publishing Schedule

When episodes are scheduled to go live. 
-- Episode publishing schedule
SELECT 
    e.title as episode_title,
    et.published_at as scheduled_time,
    c.name as channel_name,
    et.status as publish_status,
    e.published_live_at as actual_publish_time
FROM planning_center.publishing_episode_times et
JOIN planning_center.publishing_episodes e 
    ON et.episode_id = e.episode_id
JOIN planning_center.publishing_channels c 
    ON et.channel_id = c.channel_id
WHERE et.published_at >= CURRENT_DATE
ORDER BY et.published_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(view_count) FROM planning_center.publishing_episode_statistics) as total_views,
    
    (SELECT SUM(download_count) FROM planning_center.publishing_episode_statistics) as total_downloads,
    
    (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', published_live_at) as month,
    COUNT(*) as episodes_published,
    COUNT(DISTINCT series_id) as active_series,
    COUNT(DISTINCT DATE_TRUNC('week', published_live_at)) as weeks_with_content,
    ARRAY_AGG(DISTINCT EXTRACT(DOW FROM published_live_at)) as publishing_days
FROM planning_center.publishing_episodes
WHERE published_live_at >= CURRENT_DATE - INTERVAL '12 months'
    AND 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!