Advanced Planning Center Publishing Queries

Complex queries for deep analysis, cross-module insights, and sophisticated reporting. These queries demonstrate advanced SQL techniques and integration patterns.

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 writing advanced queries. ✅ 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, and cross-module joins while trusting RLS for tenancy and system status.

Time-Based Analytics

Engagement Decay Analysis

How quickly do views drop off after publishing?

-- Analyze watch count by episode for recent content
-- Note: episode_statistics is a point-in-time snapshot (no created_at for decay analysis)
WITH episode_watch_metrics AS (
    SELECT 
        e.episode_id,
        e.title,
        e.published_live_at,
        COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0) as total_watches,
        est.library_watch_count,
        est.live_watch_count,
        EXTRACT(DAY FROM (CURRENT_DATE - e.published_live_at)) as days_since_publish,
        CASE 
            WHEN EXTRACT(DAY FROM (CURRENT_DATE - e.published_live_at)) <= 7 THEN '0-1 weeks'
            WHEN EXTRACT(DAY FROM (CURRENT_DATE - e.published_live_at)) <= 14 THEN '1-2 weeks'
            WHEN EXTRACT(DAY FROM (CURRENT_DATE - e.published_live_at)) <= 28 THEN '2-4 weeks'
            ELSE '4+ weeks'
        END as age_bucket
    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 '6 months'
        AND e.published_live_at IS NOT NULL
)
SELECT 
    age_bucket,
    COUNT(DISTINCT episode_id) as episodes_measured,
    AVG(total_watches) as avg_total_watches,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_watches) as median_watches,
    SUM(total_watches) as total_watches
FROM episode_watch_metrics
GROUP BY age_bucket
ORDER BY age_bucket;

Year-over-Year Growth Analysis

Compare publishing metrics across years.

-- Year-over-year publishing comparison
WITH yearly_metrics AS (
    SELECT 
        DATE_PART('year', e.published_live_at) as year,
        DATE_PART('month', e.published_live_at) as 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,
        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
    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('year', CURRENT_DATE - INTERVAL '2 years')
    GROUP BY year, month
)
SELECT 
    ym.month,
    ym.episodes_published as current_year_episodes,
    ym_prev.episodes_published as last_year_episodes,
    ROUND(((ym.episodes_published::numeric - ym_prev.episodes_published) / 
           NULLIF(ym_prev.episodes_published, 0)) * 100, 2) as episode_growth_pct,
    ym.total_watches as current_year_watches,
    ym_prev.total_watches as last_year_watches,
    ROUND(((ym.total_watches::numeric - ym_prev.total_watches) / 
           NULLIF(ym_prev.total_watches, 0)) * 100, 2) as watch_growth_pct,
    ym.avg_watches_per_episode as current_avg_watches,
    ym_prev.avg_watches_per_episode as last_year_avg_watches
FROM yearly_metrics ym
LEFT JOIN yearly_metrics ym_prev 
    ON ym.month = ym_prev.month 
    AND ym.year = ym_prev.year + 1
WHERE ym.year = DATE_PART('year', CURRENT_DATE)
ORDER BY ym.month;

Publishing Consistency Score

Measure how consistently you publish content.

-- Publishing consistency analysis
WITH weekly_publishing AS (
    SELECT 
        DATE_TRUNC('week', published_live_at) as week,
        COUNT(*) as episodes_published,
        ARRAY_AGG(DISTINCT EXTRACT(DOW FROM published_live_at)) as publishing_days,
        ARRAY_AGG(title ORDER BY published_live_at) as episode_titles
    FROM planning_center.publishing_episodes
    WHERE published_live_at >= CURRENT_DATE - INTERVAL '52 weeks'
        AND published_live_at IS NOT NULL
    GROUP BY week
),
consistency_metrics AS (
    SELECT 
        COUNT(*) as total_weeks,
        COUNT(CASE WHEN episodes_published > 0 THEN 1 END) as weeks_with_content,
        AVG(episodes_published) as avg_episodes_per_week,
        STDDEV(episodes_published) as stddev_episodes,
        MODE() WITHIN GROUP (ORDER BY episodes_published) as mode_episodes_per_week,
        MAX(episodes_published) as max_episodes_in_week,
        MIN(CASE WHEN episodes_published > 0 THEN episodes_published END) as min_episodes_in_week
    FROM weekly_publishing
)
SELECT 
    total_weeks,
    weeks_with_content,
    ROUND((weeks_with_content::numeric / total_weeks) * 100, 2) as consistency_percentage,
    ROUND(avg_episodes_per_week::numeric, 2) as avg_episodes_per_week,
    ROUND(stddev_episodes::numeric, 2) as publishing_variance,
    mode_episodes_per_week as typical_weekly_episodes,
    max_episodes_in_week,
    min_episodes_in_week,
    CASE 
        WHEN (weeks_with_content::numeric / total_weeks) >= 0.95 THEN 'Excellent'
        WHEN (weeks_with_content::numeric / total_weeks) >= 0.85 THEN 'Good'
        WHEN (weeks_with_content::numeric / total_weeks) >= 0.70 THEN 'Fair'
        ELSE 'Needs Improvement'
    END as consistency_rating
FROM consistency_metrics;

Speaker Analytics

Speaker Collaboration Patterns

Which speakers frequently teach together?

-- Find speaker collaboration patterns
WITH episode_speakers AS (
    SELECT 
        e.episode_id,
        e.title as episode_title,
        e.published_live_at,
        ARRAY_AGG(sp.formatted_name ORDER BY sp.formatted_name) as speakers,
        ARRAY_AGG(sp.speaker_id ORDER BY sp.speaker_id) as speaker_ids,
        COUNT(sp.speaker_id) as speaker_count
    FROM planning_center.publishing_episodes e
    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_relationships spr
        ON spr.speakership_id = er_ship.relationship_id AND spr.relationship_type = 'speaker'
    JOIN planning_center.publishing_speakers sp ON sp.speaker_id = spr.relationship_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY e.episode_id, e.title, e.published_live_at
    HAVING COUNT(sp.speaker_id) > 1
),
speaker_pairs AS (
    SELECT 
        s1.speaker_id as speaker1_id,
        s1.formatted_name as speaker1_name,
        s2.speaker_id as speaker2_id,
        s2.formatted_name as speaker2_name,
        COUNT(DISTINCT e.episode_id) as episodes_together
    FROM planning_center.publishing_episodes e
    JOIN planning_center.publishing_episodes_relationships er_ship1
        ON er_ship1.episode_id = e.episode_id AND er_ship1.relationship_type = 'speakerships'
    JOIN planning_center.publishing_speakerships_relationships spr1
        ON spr1.speakership_id = er_ship1.relationship_id AND spr1.relationship_type = 'speaker'
    JOIN planning_center.publishing_speakers s1 ON s1.speaker_id = spr1.relationship_id
    JOIN planning_center.publishing_episodes_relationships er_ship2
        ON er_ship2.episode_id = e.episode_id AND er_ship2.relationship_type = 'speakerships'
    JOIN planning_center.publishing_speakerships_relationships spr2
        ON spr2.speakership_id = er_ship2.relationship_id AND spr2.relationship_type = 'speaker'
    JOIN planning_center.publishing_speakers s2 ON s2.speaker_id = spr2.relationship_id
    WHERE s1.speaker_id < s2.speaker_id  -- Avoid duplicates
        AND e.published_live_at >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY s1.speaker_id, s1.formatted_name, s2.speaker_id, s2.formatted_name
)
SELECT 
    speaker1_name,
    speaker2_name,
    episodes_together,
    ROUND(episodes_together::numeric / 52 * 100, 1) as pct_of_year_together
FROM speaker_pairs
ORDER BY episodes_together DESC;

Speaker Topic Analysis

What topics does each speaker cover? (Based on series)

-- Analyze speaker topics through series
WITH speaker_series_stats AS (
    SELECT 
        sp.speaker_id,
        sp.formatted_name as speaker_name,
        s.series_id,
        s.title as series_title,
        s.description as series_description,
        COUNT(DISTINCT e.episode_id) as episodes_in_series,
        MIN(e.published_live_at) as first_episode,
        MAX(e.published_live_at) as last_episode,
        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
    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 er_ship
        ON er_ship.relationship_id = spr.speakership_id AND er_ship.relationship_type = 'speakerships'
    JOIN planning_center.publishing_episodes e ON e.episode_id = er_ship.episode_id
    JOIN planning_center.publishing_episodes_relationships er_series
        ON er_series.episode_id = e.episode_id AND er_series.relationship_type = 'series'
    JOIN planning_center.publishing_series s ON s.series_id = er_series.relationship_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    GROUP BY sp.speaker_id, sp.formatted_name, s.series_id, s.title, s.description
)
SELECT 
    speaker_name,
    COUNT(DISTINCT series_id) as series_count,
    SUM(episodes_in_series) as total_episodes,
    ARRAY_AGG(series_title ORDER BY total_watches DESC) as series_taught,
    ROUND(AVG(avg_watches)::numeric, 0) as avg_watches_per_episode,
    SUM(total_watches) as total_career_watches,
    MIN(first_episode) as teaching_since,
    MAX(last_episode) as most_recent_teaching
FROM speaker_series_stats
GROUP BY speaker_id, speaker_name
ORDER BY total_episodes DESC;

Speaker Performance Benchmarking

Compare speaker engagement metrics.

-- Speaker performance benchmarking
WITH speaker_metrics AS (
    SELECT 
        sp.speaker_id,
        sp.formatted_name as speaker_name,
        COUNT(DISTINCT e.episode_id) as episode_count,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches,
        STDDEV(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as watch_stddev,
        MAX(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as max_watches,
        MIN(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as min_watches
    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 er_ship
        ON er_ship.relationship_id = spr.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_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY sp.speaker_id, sp.formatted_name
    HAVING COUNT(DISTINCT e.episode_id) >= 3  -- Minimum episodes for comparison
),
benchmarks AS (
    SELECT 
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_watches) as median_watches,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY avg_watches) as q3_watches,
        AVG(avg_watches) as overall_avg_watches
    FROM speaker_metrics
)
SELECT 
    sm.speaker_name,
    sm.episode_count,
    ROUND(sm.avg_watches::numeric, 0) as avg_watches,
    ROUND(sm.watch_stddev::numeric, 0) as watch_consistency,
    CASE 
        WHEN sm.avg_watches > b.q3_watches THEN 'Top Performer'
        WHEN sm.avg_watches > b.median_watches THEN 'Above Average'
        ELSE 'Below Average'
    END as performance_tier,
    ROUND(((sm.avg_watches - b.overall_avg_watches) / NULLIF(b.overall_avg_watches, 0)) * 100, 1) as pct_vs_average
FROM speaker_metrics sm
CROSS JOIN benchmarks b
ORDER BY sm.avg_watches DESC;

Series Deep Dive

Series Performance Trajectory

How do views change throughout a series?

-- Analyze watch trajectory within series
WITH series_episodes AS (
    SELECT 
        s.series_id,
        s.title as series_title,
        e.episode_id,
        e.title as episode_title,
        e.published_live_at,
        COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0) as watch_count,
        ROW_NUMBER() OVER (PARTITION BY s.series_id ORDER BY e.published_live_at) as episode_number,
        COUNT(*) OVER (PARTITION BY s.series_id) as total_episodes_in_series,
        FIRST_VALUE(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) 
            OVER (PARTITION BY s.series_id ORDER BY e.published_live_at) as first_episode_watches,
        LAST_VALUE(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) 
            OVER (PARTITION BY s.series_id ORDER BY e.published_live_at
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_episode_watches
    FROM planning_center.publishing_series s
    JOIN planning_center.publishing_episodes_relationships er
        ON er.relationship_id = s.series_id AND er.relationship_type = 'series'
    JOIN planning_center.publishing_episodes e ON e.episode_id = er.episode_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    WHERE s.episodes_count >= 4  -- Series with at least 4 episodes
        AND e.published_live_at IS NOT NULL
)
SELECT 
    series_title,
    total_episodes_in_series,
    first_episode_watches,
    last_episode_watches,
    ROUND(AVG(CASE WHEN episode_number = 1 THEN watch_count END)::numeric, 0) as ep1_avg_watches,
    ROUND(AVG(CASE WHEN episode_number = 2 THEN watch_count END)::numeric, 0) as ep2_avg_watches,
    ROUND(AVG(CASE WHEN episode_number = 3 THEN watch_count END)::numeric, 0) as ep3_avg_watches,
    ROUND(AVG(CASE WHEN episode_number = total_episodes_in_series THEN watch_count END)::numeric, 0) as final_ep_avg_watches,
    ROUND(((last_episode_watches::numeric - first_episode_watches) / 
           NULLIF(first_episode_watches, 0)) * 100, 1) as watch_change_pct,
    CASE 
        WHEN last_episode_watches > first_episode_watches * 1.1 THEN 'Growing Engagement'
        WHEN last_episode_watches < first_episode_watches * 0.9 THEN 'Declining Engagement'
        ELSE 'Stable Engagement'
    END as engagement_trend
FROM series_episodes
GROUP BY series_id, series_title, total_episodes_in_series, first_episode_watches, last_episode_watches
ORDER BY total_episodes_in_series DESC, series_title;

Optimal Series Length Analysis

What’s the ideal number of episodes for a series?

-- Analyze engagement by series length
WITH series_performance AS (
    SELECT 
        s.series_id,
        s.title,
        s.episodes_count,
        COUNT(DISTINCT e.episode_id) as actual_episodes,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches_per_episode,
        SUM(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as total_series_watches,
        STDDEV(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as watch_variance,
        EXTRACT(DAY FROM (s.ended_at - s.started_at)) as series_duration_days
    FROM planning_center.publishing_series s
    JOIN planning_center.publishing_episodes_relationships er
        ON er.relationship_id = s.series_id AND er.relationship_type = 'series'
    JOIN planning_center.publishing_episodes e ON e.episode_id = er.episode_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    WHERE s.published = true
        AND s.ended_at IS NOT NULL
    GROUP BY s.series_id, s.title, s.episodes_count, s.started_at, s.ended_at
),
length_buckets AS (
    SELECT 
        CASE 
            WHEN episodes_count <= 2 THEN '1-2 Episodes'
            WHEN episodes_count <= 4 THEN '3-4 Episodes'
            WHEN episodes_count <= 6 THEN '5-6 Episodes'
            WHEN episodes_count <= 8 THEN '7-8 Episodes'
            ELSE '9+ Episodes'
        END as series_length_bucket,
        episodes_count,
        COUNT(*) as series_count,
        AVG(avg_watches_per_episode) as avg_watches,
        AVG(total_series_watches) as avg_total_watches,
        AVG(watch_variance) as avg_watch_variance,
        AVG(series_duration_days) as avg_duration_days
    FROM series_performance
    GROUP BY series_length_bucket, episodes_count
)
SELECT 
    series_length_bucket,
    series_count,
    ROUND(avg_watches::numeric, 0) as avg_watches_per_episode,
    ROUND(avg_total_watches::numeric, 0) as avg_total_series_watches,
    ROUND(avg_watch_variance::numeric, 0) as watch_consistency_score,
    ROUND(avg_duration_days::numeric, 0) as avg_series_days,
    RANK() OVER (ORDER BY avg_watches DESC) as performance_rank
FROM length_buckets
ORDER BY series_length_bucket;

Resource and Distribution Analysis

Multi-Channel Performance Comparison

How does content perform across different channels?

-- Compare performance across distribution channels
WITH channel_episodes AS (
    SELECT 
        c.channel_id,
        c.name as channel_name,
        e.episode_id,
        e.title as episode_title,
        e.published_live_at,
        et.starts_at as channel_publish_time,
        COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0) as total_watches,
        est.library_watch_count,
        est.live_watch_count
    FROM planning_center.publishing_channels c
    JOIN planning_center.publishing_episodes_relationships er_ch
        ON er_ch.relationship_id = c.channel_id AND er_ch.relationship_type = 'channel'
    JOIN planning_center.publishing_episodes e ON e.episode_id = er_ch.episode_id
    JOIN planning_center.publishing_episodes_relationships er_et
        ON er_et.episode_id = e.episode_id AND er_et.relationship_type = 'episode_times'
    JOIN planning_center.publishing_episode_times et ON et.episode_time_id = er_et.relationship_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 '6 months'
)
SELECT 
    channel_name,
    COUNT(DISTINCT episode_id) as episodes_published,
    AVG(total_watches) as avg_watches,
    SUM(total_watches) as total_watches,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_watches) as median_watches,
    MAX(total_watches) as best_performing_episode_watches,
    MIN(total_watches) as worst_performing_episode_watches,
    AVG(EXTRACT(HOUR FROM (channel_publish_time - published_live_at))) as avg_delay_hours
FROM channel_episodes
GROUP BY channel_id, channel_name
ORDER BY total_watches DESC;

Resource Utilization Analysis

Which resources are most popular?

-- Analyze resource types and associated episode watches
WITH resource_metrics AS (
    SELECT 
        eresrc.kind as resource_type,
        eresrc.title as resource_name,
        eresrc.url,
        e.title as episode_title,
        s.title as series_title,
        e.published_live_at,
        COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0) as episode_watches,
        CASE 
            WHEN eresrc.kind ILIKE '%note%' THEN 'Notes'
            WHEN eresrc.kind ILIKE '%slide%' OR eresrc.kind ILIKE '%presentation%' THEN 'Slides'
            WHEN eresrc.kind ILIKE '%guide%' OR eresrc.kind ILIKE '%study%' THEN 'Study Materials'
            WHEN eresrc.kind ILIKE '%video%' THEN 'Video'
            WHEN eresrc.kind ILIKE '%audio%' THEN 'Audio'
            ELSE 'Other'
        END as resource_category
    FROM planning_center.publishing_episode_resources eresrc
    JOIN planning_center.publishing_episodes_relationships er_res
        ON er_res.relationship_id = eresrc.episode_resource_id AND er_res.relationship_type = 'episode_resources'
    JOIN planning_center.publishing_episodes e ON e.episode_id = er_res.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
    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'
)
SELECT 
    resource_category,
    COUNT(*) as resource_count,
    COUNT(DISTINCT episode_title) as episodes_with_resource,
    AVG(episode_watches) as avg_episode_watches_with_resource,
    ARRAY_AGG(DISTINCT resource_type) as resource_types
FROM resource_metrics
GROUP BY resource_category
ORDER BY resource_count DESC;

Cross-Module Integration

Publishing and Check-Ins Correlation

Compare online watches with in-person attendance week by week.

Planning Center Publishing reports watch counts in aggregate only — it does not identify individual viewers. There is no viewer-to-person join, so online audiences cannot be broken down by membership status or campus. Correlate the two channels by week instead, as shown below.

-- Online watches vs. in-person check-ins, week by week
WITH online AS (
    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) + COALESCE(est.live_watch_count, 0)) as total_watches
    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 '6 months'
    GROUP BY DATE_TRUNC('week', e.published_live_at)
),
in_person AS (
    SELECT
        DATE_TRUNC('week', c.created_at) as week,
        COUNT(DISTINCT c.check_in_id) as check_ins,
        COUNT(DISTINCT cr.relationship_id) as unique_attendees
    FROM planning_center.checkins_check_ins c
    JOIN planning_center.checkins_check_ins_relationships cr
        ON cr.check_in_id = c.check_in_id AND cr.relationship_type = 'Person'
    WHERE c.created_at >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY DATE_TRUNC('week', c.created_at)
)
SELECT
    COALESCE(o.week, i.week) as week,
    COALESCE(o.episodes_published, 0) as episodes_published,
    COALESCE(o.total_watches, 0) as total_watches,
    COALESCE(i.check_ins, 0) as check_ins,
    COALESCE(i.unique_attendees, 0) as unique_attendees,
    ROUND(
        COALESCE(o.total_watches, 0)::numeric
        / NULLIF(COALESCE(i.unique_attendees, 0), 0),
        2
    ) as watches_per_attendee
FROM online o
FULL OUTER JOIN in_person i ON i.week = o.week
ORDER BY week DESC;

Publishing and Giving Correlation

Analyze giving patterns during sermon series.

-- Correlate sermon series with giving patterns (requires Giving module)
WITH series_giving_periods AS (
    SELECT 
        s.series_id,
        s.title as series_title,
        s.started_at,
        s.ended_at,
        COUNT(DISTINCT e.episode_id) as episode_count,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_episode_watches
    FROM planning_center.publishing_series s
    JOIN planning_center.publishing_episodes_relationships er
        ON er.relationship_id = s.series_id AND er.relationship_type = 'series'
    JOIN planning_center.publishing_episodes e ON e.episode_id = er.episode_id
    LEFT JOIN planning_center.publishing_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    WHERE s.started_at >= CURRENT_DATE - INTERVAL '1 year'
        AND s.ended_at IS NOT NULL
    GROUP BY s.series_id, s.title, s.started_at, s.ended_at
),
giving_during_series AS (
    SELECT 
        sgp.series_id,
        sgp.series_title,
        sgp.started_at,
        sgp.ended_at,
        sgp.episode_count,
        sgp.avg_episode_watches,
        -- Join with giving data
        COUNT(DISTINCT d.donation_id) as donations_during_series,
        SUM(d.amount_cents) / 100.0 as total_giving_amount,
        COUNT(DISTINCT dr.relationship_id) as unique_donors,
        AVG(d.amount_cents) / 100.0 as avg_donation_amount
    FROM series_giving_periods sgp
    LEFT JOIN planning_center.giving_donations d 
        ON d.received_at BETWEEN sgp.started_at AND COALESCE(sgp.ended_at, CURRENT_DATE)
    LEFT JOIN planning_center.giving_donations_relationships dr
        ON dr.donation_id = d.donation_id AND dr.relationship_type = 'Person'
    GROUP BY sgp.series_id, sgp.series_title, sgp.started_at, sgp.ended_at, 
             sgp.episode_count, sgp.avg_episode_watches
)
SELECT 
    series_title,
    started_at,
    ended_at,
    episode_count,
    ROUND(avg_episode_watches::numeric, 0) as avg_watches,
    donations_during_series,
    ROUND(total_giving_amount::numeric, 2) as total_giving,
    unique_donors,
    ROUND(avg_donation_amount::numeric, 2) as avg_donation,
    ROUND((total_giving_amount / NULLIF(episode_count, 0))::numeric, 2) as giving_per_episode
FROM giving_during_series
ORDER BY started_at DESC;

Performance Optimization Queries

Identify Slow-Performing Content

Find content that needs promotion or improvement.

-- Identify underperforming content for optimization
WITH performance_benchmarks AS (
    SELECT 
        PERCENTILE_CONT(0.25) WITHIN GROUP (
            ORDER BY COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)
        ) as q1_watches,
        PERCENTILE_CONT(0.5) WITHIN GROUP (
            ORDER BY COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)
        ) as median_watches,
        PERCENTILE_CONT(0.75) WITHIN GROUP (
            ORDER BY COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)
        ) as q3_watches,
        AVG(COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0)) as avg_watches
    FROM planning_center.publishing_episodes e
    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'
),
episode_performance AS (
    SELECT 
        e.episode_id,
        e.title,
        e.published_live_at,
        s.title as series_title,
        sp.formatted_name as speaker_name,
        COALESCE(est.library_watch_count, 0) + COALESCE(est.live_watch_count, 0) as watch_count,
        resource_counts.resource_count,
        EXTRACT(DAY FROM (CURRENT_DATE - e.published_live_at)) as days_since_published
    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
    LEFT JOIN planning_center.publishing_episodes_relationships er_ship
        ON er_ship.episode_id = e.episode_id AND er_ship.relationship_type = 'speakerships'
    LEFT JOIN planning_center.publishing_speakerships_relationships spr
        ON spr.speakership_id = er_ship.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_episode_statistics est 
        ON est.episode_statistics_id = 'es-' || e.episode_id
    LEFT JOIN (
        SELECT er.episode_id, COUNT(*) as resource_count
        FROM planning_center.publishing_episodes_relationships er
        WHERE er.relationship_type = 'episode_resources'
        GROUP BY er.episode_id
    ) resource_counts ON resource_counts.episode_id = e.episode_id
    WHERE e.published_live_at >= CURRENT_DATE - INTERVAL '3 months'
)
SELECT 
    ep.title,
    ep.series_title,
    ep.speaker_name,
    ep.published_live_at,
    ep.days_since_published,
    ep.watch_count,
    pb.median_watches as expected_watches,
    ROUND((((ep.watch_count::numeric - pb.median_watches) / NULLIF(pb.median_watches, 0)) * 100)::numeric, 1) as performance_vs_median_pct,
    CASE 
        WHEN ep.watch_count < pb.q1_watches THEN 'Critical - Bottom 25%'
        WHEN ep.watch_count < pb.median_watches THEN 'Below Average'
        WHEN ep.watch_count < pb.q3_watches THEN 'Above Average'
        ELSE 'Top Performer'
    END as performance_tier,
    COALESCE(ep.resource_count, 0) as resources_available,
    CASE 
        WHEN ep.watch_count < pb.q1_watches AND ep.resource_count = 0 THEN 'Add Resources'
        WHEN ep.watch_count < pb.q1_watches AND ep.days_since_published < 7 THEN 'Needs Promotion'
        WHEN ep.watch_count < pb.q1_watches THEN 'Review Content Quality'
        ELSE 'No Action Needed'
    END as recommended_action
FROM episode_performance ep
CROSS JOIN performance_benchmarks pb
WHERE ep.watch_count < pb.median_watches
ORDER BY ep.watch_count ASC, ep.published_live_at DESC;

Tips for Advanced Queries

Use CTEs (WITH clauses) for complex multi-step analysis
Window Functions for running totals and rankings
PERCENTILE_CONT for statistical analysis
ARRAY_AGG to collect related values
CASE statements for conditional logic and categorization
Cross-module joins require understanding your data relationships

Performance Considerations

Add indexes on frequently joined columns
Use EXPLAIN ANALYZE to optimize slow queries
Consider materialized views for complex reports
Partition large tables by date if needed

Next Steps

Reporting Examples - Production-ready report templates
Data Model - Complete schema reference
Basic Queries - Simpler query examples

Advanced analysis leads to advanced insights. Use these patterns to unlock the full potential of your publishing data.

Getting started

Release Notes

Giving Engagement

Planning Center

Advanced Planning Center Publishing Queries

Query Requirements

Schema Prefix

Row Level Security (RLS)

Time-Based Analytics

Engagement Decay Analysis

Year-over-Year Growth Analysis

Publishing Consistency Score

Speaker Analytics

Speaker Collaboration Patterns

Speaker Topic Analysis

Speaker Performance Benchmarking

Series Deep Dive

Series Performance Trajectory

Optimal Series Length Analysis

Resource and Distribution Analysis

Multi-Channel Performance Comparison

Resource Utilization Analysis

Cross-Module Integration

Publishing and Check-Ins Correlation

Publishing and Giving Correlation

Performance Optimization Queries

Identify Slow-Performing Content

Tips for Advanced Queries

Performance Considerations

Next Steps

​Query Requirements

​Schema Prefix

​Row Level Security (RLS)

​Time-Based Analytics

​Engagement Decay Analysis

​Year-over-Year Growth Analysis

​Publishing Consistency Score

​Speaker Analytics

​Speaker Collaboration Patterns

​Speaker Topic Analysis

​Speaker Performance Benchmarking

​Series Deep Dive

​Series Performance Trajectory

​Optimal Series Length Analysis

​Resource and Distribution Analysis

​Multi-Channel Performance Comparison

​Resource Utilization Analysis

​Cross-Module Integration

​Publishing and Check-Ins Correlation

​Publishing and Giving Correlation

​Performance Optimization Queries

​Identify Slow-Performing Content

​Tips for Advanced Queries

​Performance Considerations

​Next Steps

Query Requirements

Schema Prefix

Row Level Security (RLS)

Time-Based Analytics

Engagement Decay Analysis

Year-over-Year Growth Analysis

Publishing Consistency Score

Speaker Analytics

Speaker Collaboration Patterns

Speaker Topic Analysis

Speaker Performance Benchmarking

Series Deep Dive

Series Performance Trajectory

Optimal Series Length Analysis

Resource and Distribution Analysis

Multi-Channel Performance Comparison

Resource Utilization Analysis

Cross-Module Integration

Publishing and Check-Ins Correlation

Publishing and Giving Correlation

Performance Optimization Queries

Identify Slow-Performing Content

Tips for Advanced Queries

Performance Considerations

Next Steps