# 03 — Production Tracker (Google Sheet)

**Purpose:** Single source of truth for every active customer-package. Offshore team updates daily. US team reviews daily. Catches anything slipping the 5-business-day SLA.

**Location:** Google Sheet titled `BT Web Group — Production Tracker [YYYY]`, shared with US team (edit) + offshore team (edit).

## Sheet structure — one row per active customer-package

| Column | Type | Required | Notes |
|---|---|---|---|
| A. Record ID | Text | Yes | Auto-generated `2026-06-001`, increments |
| B. Customer name | Text | Yes | From intake |
| C. Customer email | Text | Yes | From intake |
| D. Package SKU | Dropdown | Yes | `tripwire-5k-email-blast`, `tripwire-ai-seo-audit-fix`, `email-starter`, `bundle-lead-engine`, etc. — full list per spec |
| E. Purchase date | Date | Yes | When payment received |
| F. Intake completed | Date | Yes | When customer submitted intake form |
| G. Kickoff date | Date | Yes | When kickoff call happened |
| H. Day-5 deadline | Date | Yes | Auto-calculated: max(F, G) + 5 business days |
| I. Production start | Date | Auto | Day after kickoff |
| J. Production status | Dropdown | Yes | `Awaiting intake` · `Awaiting kickoff` · `In production` · `Ready for US review` · `In US review` · `Approved` · `Delivered` · `Cancelled` · `Refunded` |
| K. Offshore lead | Text | Yes | Person responsible for production |
| L. US reviewer | Text | Yes | US team member reviewing |
| M. Deliverable links | Text | Yes when status ≥ "Ready for US review" | Google Drive folder URL |
| N. US review checklist done | Checkbox | Yes when delivered | Per 04 |
| O. Delivery date | Date | Yes | When customer received deliverable |
| P. On-time? | Auto | Yes | `O <= H ? "YES" : "NO"` |
| Q. Customer satisfaction | Number 1-5 | After delivery | Captured via 1-question follow-up email |
| R. Upsell offer sent (date) | Date | Day 14 post-delivery | Per 06 |
| S. Upsell outcome | Dropdown | When known | `Accepted` · `Declined` · `No response` · `Reschedule` |
| T. Notes | Text | Optional | Anything quirky about this customer |
| U. CRM lead ID | Text | Yes | For cross-reference |

## Conditional formatting

- Row H (deadline) within 1 business day: highlight yellow
- Row H past deadline + status ≠ "Delivered": highlight red
- Status = "Cancelled" or "Refunded": strikethrough whole row
- Status = "Delivered" + on-time: highlight green

## Daily review (US team standup, 10 min)

Every morning:
1. Sort by Day-5 deadline ascending
2. Confirm all rows due "today" or "tomorrow" are in `Ready for US review` or `Approved`
3. Address any red rows immediately — escalate to offshore + customer if needed
4. Mark `Delivered` rows + send the 1-question CSAT email
5. Queue Day-14 upsell offers (rows where today = delivery date + 14)

## Filters / views to create

- **View 1: Active work** — status NOT in (Delivered, Cancelled, Refunded)
- **View 2: This week's deadlines** — Day-5 deadline within 7 days
- **View 3: Upsell queue** — delivered + Day-14 due
- **View 4: Cancellation in progress** — status = Cancelled, data export pending

## When to add a row

- Triggered by: lead modal → kickoff call → payment received → row created with status "Awaiting intake"
- Customer cancellations from existing subscriptions: do NOT add a row; handle in 05 export workflow

## When to archive

- After delivery + Day-14 upsell outcome captured: move to "Archive" tab at end of quarter