​

Planning Center Publishing SQL Queries

​

Share Your Message With The World

​

Quick Start

-- See your 10 most recent published episodes with view counts
SELECT 
    e.episode_id,
    e.title,
    s.title as series_title,
    e.published_live_at,
    est.view_count,
    est.download_count,
    e.video_url,
    c.name as channel_name
FROM planning_center.publishing_episodes e
LEFT JOIN planning_center.publishing_series s 
    ON e.series_id = s.series_id
LEFT JOIN planning_center.publishing_channels c 
    ON e.channel_id = c.channel_id
LEFT JOIN planning_center.publishing_episode_statistics est 
    ON e.episode_id = est.episode_id
ORDER BY e.published_live_at DESC
LIMIT 10;

​

What You Can Do With Publishing Queries

​

📺 Content Performance Analytics

• Track view counts and download metrics across episodes
• Analyze engagement patterns by series, speaker, or topic
• Identify your most popular content and distribution channels
• Monitor audience growth and retention trends

​

🎙️ Speaker Management

• Track speaker frequency and scheduling
• Analyze topic coverage by speaker
• Identify guest speakers vs regular teaching team
• Plan speaker rotations and sabbaticals

​

📅 Series Planning

• Analyze optimal series length based on engagement
• Track series performance over time
• Identify seasonal patterns in content consumption
• Plan future series based on historical data

​

🌐 Multi-Channel Distribution

• Monitor performance across different platforms
• Optimize publishing schedules for maximum reach
• Track channel-specific engagement metrics
• Identify best channels for different content types

​

📊 Ministry Impact Measurement

• Correlate online engagement with in-person attendance
• Track sermon series impact on giving patterns
• Measure content reach beyond regular attendees
• Analyze biblical text coverage across teaching

​

Available Tables

​

Understanding Relationships

• Episodes to Series: Episodes belong to series through series_id
• Episodes to Channels: Episodes are published to channels via channel_id
• Episodes to Speakers: Connected through the publishing_speakerships junction table
• Episodes to Resources: Linked via episode_id in publishing_episode_resources
• Episodes to Statistics: Performance metrics linked by episode_id

​

Key Concepts

​

Episode Status

• published_live_at - When the episode went live
• published_to_library_at - When added to the content library
• Episodes without these dates are draft or scheduled

​

Distribution Channels

• Church website
• YouTube channel
• Podcast platforms
• Church Center app
• Social media channels

​

Content Types

• Sermons - Weekly messages
• Series - Multi-week teaching themes
• Special Events - Holiday services, conferences
• Resources - Study guides, sermon notes

​

Metrics and Analytics

• view_count - Total views across platforms
• download_count - Audio/video downloads
• unique_viewers - Distinct audience members
• Statistics tracked over time for trending

​

Next Steps

​

Common Questions

​

How do I find episodes from a specific series?

SELECT 
    e.title as episode_title,
    e.published_live_at,
    s.title as series_title
FROM planning_center.publishing_episodes e
JOIN planning_center.publishing_series s 
    ON e.series_id = s.series_id
WHERE s.title LIKE '%Grace%'
ORDER BY e.published_live_at;

​

How do I see which speakers have taught recently?

SELECT 
    sp.name as speaker_name,
    COUNT(DISTINCT e.episode_id) as episode_count,
    MAX(e.published_live_at) as most_recent
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
ORDER BY episode_count DESC;

​

How do I track content performance over time?

SELECT 
    DATE_TRUNC('week', created_at) as week,
    SUM(view_count) as total_views,
    SUM(download_count) as total_downloads,
    COUNT(DISTINCT episode_id) as episodes_tracked
FROM planning_center.publishing_episode_statistics
WHERE created_at >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY week
ORDER BY week DESC;

​

What’s the difference between channels and series?

• Channels are distribution platforms (YouTube, Podcast, Website)
• Series are content collections (sermon series, teaching themes)
• An episode belongs to one series but can be published to multiple channels

​

How do I find resources attached to episodes?

SELECT 
    e.title as episode_title,
    er.name as resource_name,
    er.kind as resource_type,
    er.url
FROM planning_center.publishing_episodes e
JOIN planning_center.publishing_episode_resources er 
    ON e.episode_id = er.episode_id
WHERE e.episode_id = 'YOUR_EPISODE_ID';

​

Tips for Success

1. Join Through IDs - Use episode_id, series_id, speaker_id for relationships
2. Filter by Dates - Use published_live_at to focus on published content
3. Aggregate Metrics - Sum statistics across time periods for trends
4. Consider Channels - Different channels may have different performance
5. Track Over Time - Use episode_statistic_times for historical trends

​

Integration Opportunities

• With People: Track online viewers who become visitors
• With Giving: Analyze giving patterns during specific series
• With Groups: Connect sermon topics to small group curricula
• With Services: Link sermon planning to worship service elements
• With Check-ins: Correlate online viewing with attendance

​

Getting Help

• 🐛 Found an issue? Report it at github.com/getparable/parable-api/issues
• 📖 Need more examples? Check our other query guides in this folder
• 💬 Have questions? Reach out to your Parable support team