# Podcast Production & Guest Management Dashboard

## Overview
A turnkey Notion workspace that lets you **plan, record, edit, publish, and analyze** every podcast episode from concept to sponsor payout. All core entities—Episodes, Guests, Show‑Notes, Analytics, Sponsors, and Launch‑Checklist—live in relational databases. KPI roll‑ups surface health metrics, while AI‑generated show‑notes and Zapier/CSV hooks automate repetitive tasks.

> 💡 Tip: Duplicate this template for each new season to keep historic data clean while preserving KPI trends.

## Databases
Below are the core tables you’ll import. After import, convert each Markdown table into a Notion database (use “Table – Full Page”) and enable the indicated property types.

### Episodes
| Episode # | Title | Guest(s) | Record Date | Publish Date | Status | Audio File (URL) | Sponsor(s) |
|-----------|-------|----------|-------------|--------------|--------|------------------|------------|
| 12 | “Future of AI” | Dr. Maya Patel | 2024‑05‑10 | 2024‑05‑17 | ✅ Published | https://example.com/audio/ep12.mp3 | TechCo |
| 13 | “Remote Work Trends” | Carlos Ruiz | 2024‑05‑20 | 2024‑05‑27 | ⏳ Editing | https://example.com/audio/ep13.mp3 | FlexDesk |
| 14 | “Health Tech” | Dr. Lena Kim | 2024‑06‑02 | — | 📅 Scheduled | — | HealthPlus |

**Properties**  
- **Episode #** (Number)  
- **Title** (Title)  
- **Guest(s)** (Relation → Guests)  
- **Record Date** (Date)  
- **Publish Date** (Date)  
- **Status** (Select: “Scheduled”, “Recording”, “Editing”, “Published”)  
- **Audio File (URL)** (URL)  
- **Sponsor(s)** (Relation → Sponsors)

### Guests
| Guest ID | Name | Company | Role | Email | Episode(s) | Bio |
|----------|------|---------|------|-------|-----------|-----|
| G001 | Maya Patel | AI Labs | Chief Scientist | maya@ailabs.com | 12 | AI researcher with 15+ years in ML. |
| G002 | Carlos Ruiz | RemoteCo | Founder | carlos@remoteco.io | 13 | Pioneer of distributed teams. |
| G003 | Lena Kim | MedTech Inc. | VP of Innovation | lena@medtech.com | 14 | Leads health‑tech product strategy. |

**Properties**  
- **Guest ID** (Formula: `prop("Name") + "-" + formatDate(now(), "YY")`)  
- **Name** (Title)  
- **Company** (Text)  
- **Role** (Text)  
- **Email** (Email)  
- **Episode(s)** (Relation → Episodes, many)  
- **Bio** (Text)

### Show‑Notes
| Note ID | Episode # | Draft (AI) | Final Copy | Word Count | Published? |
|---------|-----------|-----------|------------|------------|------------|
| SN-12-01 | 12 | ✅ | ✅ | 1 250 | ✅ |
| SN-13-01 | 13 | ✅ | — | 1 100 | — |
| SN-14-01 | 14 | ✅ | — | 1 300 | — |

**Properties**  
- **Note ID** (Formula: `"SN-" + prop("Episode #") + "-01"`)  
- **Episode #** (Relation → Episodes)  
- **Draft (AI)** (Checkbox) – toggled by OpenAI integration.  
- **Final Copy** (Rich Text)  
- **Word Count** (Number, rollup from Final Copy)  
- **Published?** (Checkbox)

### Analytics
| Episode # | Downloads | Avg. Listen Time (min) | Listener Countries | CTR % |
|-----------|-----------|------------------------|--------------------|------|
| 12 | 8 432 | 27.4 | US, CA, GB, AU | 4.2 |
| 13 | 5 019 | 22.1 | US, DE, IN | 3.7 |
| 14 | — | — | — | — |

**Properties**  
- **Episode #** (Relation → Episodes)  
- **Downloads** (Number)  
- **Avg. Listen Time (min)** (Number)  
- **Listener Countries** (Multi‑Select)  
- **CTR %** (Number, format as percent)

### Sponsors
| Sponsor ID | Name | Package | Contract Start | Contract End | Total Spend | Episodes Sponsored |
|------------|------|---------|----------------|--------------|------------|--------------------|
| S001 | TechCo | Premium | 2024‑01‑01 | 2024‑12‑31 | $12,000 | 12 |
| S002 | FlexDesk | Standard | 2024‑03‑15 | 2025‑03‑14 | $6,500 | 13 |
| S003 | HealthPlus | Premium | 2024‑05‑01 | 2025‑04‑30 | $9,000 | 14 |

**Properties**  
- **Sponsor ID** (Auto‑Number)  
- **Name** (Title)  
- **Package** (Select)  
- **Contract Start/End** (Date)  
- **Total Spend** (Currency)  
- **Episodes Sponsored** (Relation → Episodes, many)

### Launch‑Checklist
| Step | Description | Assigned To | Due Date | Completed |
|------|-------------|-------------|----------|-----------|
| 1 | Upload audio to hosting | Audio Engineer | 2024‑05‑15 | ✅ |
| 2 | Generate AI show‑notes | Content Writer | 2024‑05‑16 | ✅ |
| 3 | Review sponsor copy | Marketing Lead | 2024‑05‑16 | ✅ |
| 4 | Publish to platforms | Publisher | 2024‑05‑17 | ✅ |
| 5 | Push analytics CSV to Notion | Zapier Bot | 2024‑05‑18 | — |

**Properties**  
- **Step** (Number)  
- **Description** (Title)  
- **Assigned To** (Person)  
- **Due Date** (Date)  
- **Completed** (Checkbox)

## Views & Linked Views
1. **Episode Masterboard** – Kanban grouped by *Status* (Scheduled, Recording, Editing, Published).  
2. **Guest Calendar** – Calendar view of *Record Date* from Episodes, filtered by Guest relation.  
3. **Sponsor ROI Dashboard** – Table view of Sponsors with a roll‑up of total *Downloads* from linked Episodes; add a formula `prop("Downloads") / prop("Total Spend")` to get cost‑per‑download.  
4. **Analytics Snapshot** – Gallery view of Episodes showing key KPI cards (Downloads, Avg. Listen Time, CTR).  
5. **Launch Checklist – Today** – Filtered view showing only items due today or overdue.

> 💡 Tip: Use Notion’s “Sync” block to embed the *Analytics Snapshot* on a separate “Executive Summary” page for quick stakeholder updates.

## Automation & AI
| Trigger (Zapier/CSV) | Action in Notion | Result |
|----------------------|------------------|--------|
| New episode row added (CSV) | Create linked Show‑Notes row | AI Draft auto‑generated via OpenAI API (prompt: “Write 1500‑word show notes for episode {Title} featuring {Guest Names}.”) |
| Episode status set to “Published” | Update Analytics row with CSV import from hosting platform | KPI roll‑ups refresh automatically |
| Sponsor contract end approaching (30 days) | Create task in Launch‑Checklist | Prevent missed renewal |

**Setup notes**  
- **OpenAI Integration**: Use Notion’s API to call `POST https://api.openai.com/v1/chat/completions` with system prompt “You are a podcast show‑notes writer…”. Store response in the *Draft (AI)* checkbox and *Final Copy* field.  
- **Zapier CSV Import**: Create a “New File in Google Drive” trigger, parse CSV, and use “Create Database Item” actions for Episodes, Analytics, and Sponsors.  
- **Bi‑directional sync**: Enable “Allow duplicate entries” in the Episodes database to let Zapier update existing rows based on *Episode #*.

> 💡 Tip: Keep the AI prompt in a separate “Prompt Library” page so you can tweak tone without editing the Zap.

## KPI Dashboard
Create a **Dashboard** page with the following roll‑up blocks:

| KPI | Source | Formula |
|-----|--------|---------|
| Total Episodes (Season) | Episodes → Count | `prop("Episodes")` |
| Avg. Downloads per Episode | Analytics → Avg of Downloads | `average(prop("Downloads"))` |
| Sponsor Revenue per Episode | Sponsors → Rollup of Total Spend / Episodes Sponsored | `prop("Total Spend") / length(prop("Episodes Sponsored"))` |
| Completion Rate (Launch Checklist) | Launch‑Checklist → Percent Completed | `format(round(prop("Completed") / length(prop("Step")) * 100), 0) + "%" ` |

Add a **Progress Bar** using Notion’s “Formula” property: `slice("▓▓▓▓▓▓▓▓▓▓", 0, floor(prop("Completion Rate") / 10)) + slice("░░░░░░░░░░", 0, 10 - floor(prop("Completion Rate") / 10))`.

> 💡 Tip: Pin the KPI Dashboard to the sidebar for daily visibility.

## Workflow Guide
1. **Pre‑Production**  
   - Add a new Episode row → assign Guest(s) → set *Record Date*.  
   - Sponsor(s) auto‑populate via relation if they have a contract covering the episode.  

2. **Recording**  
   - Update *Status* to “Recording”.  
   - Zapier watches the status change and notifies the audio engineer via Slack.  

3. **Post‑Production**  
   - Change *Status* to “Editing”.  
   - AI generates a draft Show‑Notes row; content writer refines and checks *Final Copy*.  

4. **Launch Checklist**  
   - Checklist items appear automatically (linked via formula to Episode).  
   - Mark each step as completed; the *Completed* roll‑up updates the KPI Dashboard.  

5. **Publishing & Analytics**  
   - When *Status* becomes “Published”, Zapier pulls download stats from the hosting platform CSV and updates the Analytics database.  
   - KPI Dashboard refreshes; sponsor ROI roll‑ups recalculate.  

6. **Post‑Launch Review**  
   - Review *Analytics Snapshot* and sponsor performance.  
   - If a sponsor’s cost‑per‑download exceeds a threshold, create a “Renewal/Negotiation” task in the Launch‑Checklist.

> 💡 Tip: Schedule a weekly “Dashboard Review” meeting and embed the KPI Dashboard view directly in the meeting notes page.

## How to import into Notion
1. **Create a new page** in your Notion workspace where you want the template.  
2. **Copy the entire Markdown content** (starting from `# Podcast Production & Guest Management Dashboard`).  
3. In Notion, click **“Import” → “Markdown & CSV”**, paste the copied text, and hit **Import**.  
4. For each table (Episodes, Guests, Show‑Notes, Analytics, Sponsors, Launch‑Checklist):  
   - Click the table → **“Turn into → Database”** (choose “Table – Full Page”).  
   - Adjust each column to the property type indicated in the *Properties* sections (Number, Date, Select, Relation, etc.).  
5. **Set up Relations**:  
   - Open the *Relation* property, link to the target database, and enable “Show on both sides”.  
6. **Create the Views** listed in the *Views & Linked Views* section (Kanban, Calendar, Gallery, etc.).  
7. **Add the KPI Dashboard** page, insert roll‑up and formula blocks as described, and pin it to the sidebar.  
8. **Connect Zapier** (or your preferred automation tool) using the triggers and actions outlined in the *Automation & AI* table.  
9. **Test the workflow** by adding a dummy episode, running the AI show‑notes Zap, and confirming KPI roll‑ups update.  

Your production‑ready Podcast Dashboard is now ready to power every episode from concept to sponsor payout!