Dragon Lady – AI System (High-Level Overview for Jerry)
What This Does
One sentence: You add bookings to a Google Sheet, organizers paste in guest emails, system auto-reminds guests to sign waivers, collects photos during cruise, and burns USB drives with AI-organized memories in 2 minutes.
The Google Sheets (Your Control Panel)
You Already Have:
- VIP_Requests – Sales pipeline (keep using this)
- Waivers – Legal signatures (keep using this)
We Add:
- Charter_Bookings – Your operational dashboard (add confirmed charters here)
- Charter_Guest_Lists – Auto-filled when organizer pastes guest emails
- Waiver_Status – Auto-generated "shame list" of who hasn't signed
- Marketing_List – Auto-built from past guests, sends "come back" emails every 3-6 months
The Flow (Dead Simple)
1. You Book Charter
- Add row to Charter_Bookings sheet
- System creates:
dragonladysf.com/sarahs-party-2025 - Email organizer the link
Your time: 2 minutes
2. Organizer Adds Guests
- Visits link, pastes guest names/emails
- System starts sending reminders (2 weeks, 1 week, 3 days, 1 day)
Your time: 0 minutes (they do it)
3. Guests Sign Waivers
- Click link in reminder email
- Sign from home before cruise
- Goes to Waivers sheet automatically
Your time: 0 minutes (system handles it)
4. Day Before – Check Status
- Open Waiver_Status sheet
- See who hasn't signed (your "shame list")
- Maybe text 1-2 stragglers
Your time: 1 minute
5. Charter Day
- 2-3 people sign on device (those who didn't pre-sign)
- Photos auto-sync to Razer via WiFi
- AI captions everything in real-time
Your time: 5 minutes for last waivers
6. End of Cruise
- Click "Finalize Charter"
- System builds complete website with AI story
- Burns USB drives (one per guest)
- Takes 2 minutes
Your time: 1 click
7. Post-Cruise Marketing (Automatic)
- Guests who opted in → Marketing_List
- System emails them every 3-6 months: "Come back!"
- They book their own parties
Your time: 0 minutes (set and forget)
Time Savings Per Charter
Before:
- Pre-cruise calls/emails: 2 hours
- Day-of waiver chaos: 30 min
- Post-cruise photo organizing: 3-4 hours
- Total: ~6 hours hands-on
After:
- Add booking: 2 min
- Check waiver status: 1 min
- Finalize charter: 5 sec
- Total: ~3 minutes hands-on
You save 6 hours per charter.
What's on the USB (Guests Get This)
Complete website (works offline):
- AI-generated story of their evening
- All photos, organized by AI
- Timeline with photos
- GPS map of where photos were taken
- "Then & Now" (if they uploaded old photos)
- Download all photos as ZIP
Marketing ROI
Year 1 conservative estimate:
- 100 charters × 12 guests = 1,200 people
- 60% opt-in = 720 in marketing list
- 5% conversion = 36 new bookings/year
- 36 × $2,000 = $72,000 new revenue
Cost to run: $0 (uses your existing Google Sheets + email)
Why Google Sheets?
- You already use them
- Staff already knows how to use them
- See everything at a glance
- Easy backup (Download as CSV)
- No "the database is down"
- You own your data, always
The website and automation just READ from your sheets. You're in control.
Build Timeline
5 weeks, phased approach:
- Week 1: Booking system
- Week 2: Waiver automation
- Week 3: Photo system
- Week 4: USB generation
- Week 5: Marketing automation
Then: One test charter, then go live
What Changes for You
Before:
- Chase waivers day-of (chaos)
- Organize photos manually (hours)
- No follow-up (one-time customers)
After:
- Most waivers done before cruise (smooth)
- Photos organized automatically (AI)
- Auto-marketing to past guests (repeat business)
The Goods
- No monthly fees
- No subscriptions
- All your existing hardware
- Total hands-on time: 3 min per charter
- Automatic marketing to past guests
- Professional USB memories in 2 minutes
Questions?
- Sheet structure make sense?
- Reminder timing good? (2 weeks, 1 week, 3 days, 1 day)
- Marketing OK? (3-6 month follow-ups)
Bottom line: Everything runs through Google Sheets you control. Saves 6 hours per charter. Auto-markets to past guests. Builds $72K/year in repeat business.
DANGER – LONG VERSION
Dragon Lady – AI-Powered Charter System
Complete System Overview for Jerry
What This System Does
This system uses Google Sheets as your control panel for everything. You already have two sheets working (VIP Requests and Waivers) – we're adding a few more that become your operational dashboard.
The magic: If you need to fix a phone number, change a date, or update guest info – you do it once in the sheet, and it updates everywhere automatically. Website, emails, photo systems, USB drives – all read from your sheets.
Plus: Every guest who gives an email gets added to your marketing list. System automatically sends them "Come back!" emails every 3-6 months. No extra work for you.
The Complete Sheet System
Sheet 1: "VIP_Requests" (YOU ALREADY HAVE THIS)
What it's for: Sales pipeline – people who are interested but haven't booked yet
You use this to:
- Track inquiries from website, referrals, etc.
- Vet potential clients
- Decide who's a good fit
- See notes about what they want
What happens: When someone pays and you confirm the booking → you create an entry in Charter_Bookings sheet
| Timestamp | First Name | Last Name | Phone | Company | Start Date | End Date | Guest Count | Excursion Types | Notes | VIP Flag | Source Page | Referred By | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1/7/2026 | Regular | Person | regula@email.com | 4155551212 | Jack&Sons | 2026-01-09 | 2026-01-12 | 4 | Special Occasion | Just a regular person for special occasion | modal | ||
| 1/7/2026 | Manly | VIP | vip@vip.com | 7877777777 | VIPThatMe! | 2026-01-09 | 2026-01-15 | 3 | VVVIP Experience (24-hour excl would liek to see the nicest fish in the sea | VIP | lair | Friendly VipFinder |
Sheet 2: "Charter_Bookings" (NEW – YOUR OPERATIONAL DASHBOARD)
What it's for: Confirmed, paid charters that are actually happening
This is your day-to-day control panel. Each row is one charter:
| charter_id | slug | event_name | organizer_name | organizer_email | organizer_phone | date | boarding | departure | return | guest_count | excursion_type | vip_request_id | waivers_signed | waivers_pending | status | notes |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| DL-2025-001 | sarahs-party-2025 | Sarah's 40th Birthday | Sarah Johnson | sarah@email.com | 415-555-1234 | 2025-06-15 | 18:00 | 18:30 | 21:30 | 12 | Special Occasion | 123 | 9 | 3 | confirmed | Birthday cake at 7pm |
| DL-2025-002 | corporate-jan-2025 | TechCorp Team Event | Mike Chen | mike@techcorp.com | 415-555-5678 | 2025-01-20 | 17:00 | 17:30 | 20:30 | 25 | Corporate | 156 | 25 | 0 | confirmed | Need extra wine |
| DL-2024-156 | wedding-aug-2024 | Martinez Wedding | Maria Martinez | maria@email.com | 415-555-9999 | 2024-08-10 | 19:00 | 19:30 | 22:30 | 50 | Wedding | 89 | 50 | 0 | completed | Beautiful sunset |
The "slug" column creates the custom link:
dragonladysf.com/sarahs-party-2025dragonladysf.com/corporate-jan-2025
At a glance you see:
waivers_signed: 9 people have signedwaivers_pending: 3 still need to signstatus: confirmed = upcoming, completed = finished
Sheet 3: "Charter_Guest_Lists" (NEW – AUTO-FILLED BY ORGANIZERS)
What it's for: Expected guests for each charter (with whatever contact info organizer provides)
How it gets filled: Organizer visits their custom link, pastes in guest list
| charter_slug | guest_name | guest_email | guest_phone | added_date | marketing_opt_in |
|---|---|---|---|---|---|
| sarahs-party-2025 | John Smith | john@email.com | 415-555-0001 | 2025-05-01 | yes |
| sarahs-party-2025 | Mary Chen | mary@email.com | 2025-05-01 | yes | |
| sarahs-party-2025 | Mike Wong | 415-555-0003 | 2025-05-01 | ||
| sarahs-party-2025 | Lisa Bond | lisa@email.com | 2025-05-01 | yes |
Note the variety:
- John: has email and phone ✅
- Mary: has email only ✅
- Mike: has phone only (can text, can't email reminders)
- Lisa: has email ✅
Marketing opt-in: When guests sign waiver or submit info, checkbox: "Yes, send me info about future charters!" → Goes in this column
Sheet 4: "Waivers" (YOU ALREADY HAVE THIS)
What it's for: Legal documentation – every person who signs a waiver
Current columns you have:
| Timestamp | First Name | Last Name | Phone | Street Address | City | State | DOB Month | DOB Day | DOB Year | Signature Provided | Text Signature | Charter Name | Tour Name | Charter Notes | Submission Date | Signature Image | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 4/22/2025 22:46:54 | first1 | last1 | email1@test.com | street1 | city1 | DE | 1 | 2 | 2000 | insta1 | Yes | charter1 | tour1 | notes1 notes1 | 2025-04-23T05:46:05.062Z | [signature_image] |
How it connects to Charter_Bookings:
- The "Charter Name" column = the "slug" from Charter_Bookings
- Example: All waivers for Sarah's party have
Charter Name = "sarahs-party-2025"
What changes:
- Before: people signed day-of on your device
- After: guests can pre-sign from home (less chaos day-of)
- Structure stays the same, just gets filled earlier
Sheet 5: "Waiver_Status" (NEW – AUTO-GENERATED VIEW)
What it's for: Your "shame list" – who still needs to sign
You never edit this sheet – it auto-generates by comparing Charter_Guest_Lists vs Waivers
| Charter Name | Days Until | Guest Name | Guest Email | Guest Phone | Can Email? | Can Text? | Waiver Status | Last Reminder Sent | Charter Status |
|---|---|---|---|---|---|---|---|---|---|
| sarahs-party-2025 | 5 days | Mike Chen | mike@email.com | ✅ Yes | ❌ No | ❌ Not signed | 3 days ago | confirmed | |
| sarahs-party-2025 | 5 days | Lisa Wong | lisa@email.com | 415-555-0002 | ✅ Yes | ✅ Yes | ❌ Not signed | 3 days ago | confirmed |
| sarahs-party-2025 | 5 days | Tom Brady | tom@email.com | ✅ Yes | ❌ No | ❌ Not signed | 1 day ago | confirmed | |
| sarahs-party-2025 | 5 days | Mike Wong | 415-555-0003 | ❌ No | ✅ Yes | ❌ Not signed | – | confirmed | |
| corporate-jan-2025 | 2 days | All guests | – | – | – | – | ✅ All signed | – | confirmed |
Jerry's quick view:
- Sarah's party: 3 people we can email, 1 we can only text, 8 already signed
- Corporate event: Everyone's good to go
- Sort by "Days Until" to see urgent ones first
Sheet 6: "Marketing_List" (NEW – AUTO-BUILT FROM GUESTS)
What it's for: Everyone who's been on the boat and opted in to future emails
Auto-populated from:
- Waiver sheet (anyone who signed, if they opted in)
- Charter_Guest_Lists (if they checked marketing opt-in)
| first_name | last_name | phone | first_charter_date | last_charter_date | total_charters | charter_types | last_email_sent | opted_in | notes | |
|---|---|---|---|---|---|---|---|---|---|---|
| john@email.com | John | Smith | 415-555-0001 | 2024-08-10 | 2025-06-15 | 2 | Wedding, Birthday | 2025-09-15 | yes | Great guest |
| mary@email.com | Mary | Chen | 2025-06-15 | 2025-06-15 | 1 | Birthday | 2025-09-15 | yes | ||
| sarah@email.com | Sarah | Johnson | 415-555-1234 | 2025-06-15 | 2025-06-15 | 1 | Birthday | 2025-09-15 | yes | Organizer |
The system tracks:
- When they first came aboard
- When they last came aboard
- How many times they've chartered
- What types of events they've done
- When we last emailed them
How Everything Flows Together
STEP 1: Sale (VIP_Requests → Charter_Bookings)
Someone fills out form on website
↓
Row appears in VIP_Requests
↓
You vet them, they pay
↓
You add row to Charter_Bookings:
- charter_id: DL-2025-001
- slug: sarahs-party-2025 (you pick this)
- Copy: guest_count, excursion_type, notes from VIP sheet
- Add: date, times, organizer contact info
- vip_request_id: (links back to VIP sheet row)
- status: confirmed
↓
System auto-generates: dragonladysf.com/sarahs-party-2025
↓
You email organizer: "Confirmed! Your link: dragonladysf.com/sarahs-party-2025"
Time investment: 2-3 minutes to add the booking
STEP 2: Organizer Adds Guest List
Organizer visits: dragonladysf.com/sarahs-party-2025
Sees beautiful page with:
┌─────────────────────────────────────┐
│ Sarah's 40th Birthday Cruise │
│ Dragon Lady - June 15, 2025 │
├─────────────────────────────────────┤
│ YOUR TRIP INFORMATION │
│ • Date: Saturday, June 15 │
│ • Boarding: 6:00 PM │
│ • Location: Pier 40, SF │
│ │
│ [Add Your Guest List] ← Button │
└─────────────────────────────────────┘
Clicks button, sees form:
┌─────────────────────────────────────┐
│ Add Guest List │
│ (So we can remind them!) │
├─────────────────────────────────────┤
│ Paste guest info below: │
│ Format: Name, Email, Phone │
│ (Email OR phone - we'll work with │
│ whatever you have!) │
│ │
│ ┌─────────────────────────────────┐ │
│ │ John Smith, john@email.com, │ │
│ │ 415-555-0001 │ │
│ │ Mary Chen, mary@email.com │ │
│ │ Mike Wong, 415-555-0003 │ │
│ │ Lisa Bond, lisa@email.com │ │
│ └─────────────────────────────────┘ │
│ │
│ ☑ Yes, my guests can receive info │
│ about future Dragon Lady charters │
│ │
│ [Submit Guest List] │
└─────────────────────────────────────┘
Submits
↓
All guests added to Charter_Guest_Lists sheet
↓
System starts reminder email schedule
Organizer's time: 2 minutes to paste guest list
STEP 3: Automated Reminder Emails (System Handles This)
System checks Charter_Guest_Lists daily
↓
For each charter coming up:
- 2 weeks before: Send "Sign your waiver!" email
- 1 week before: Send "Get prepared" email
- 3 days before: Send "Don't forget" email
- 1 day before: Send "Tomorrow!" email
↓
Only emails people who:
✅ Have email address
✅ Haven't signed yet
✅ Haven't already gotten that reminder
↓
Logs "Last Reminder Sent" in Waiver_Status sheet
Example email (2 weeks before):
Subject: Sarah's 40th Birthday Yacht Cruise - Sign Your Waiver!
Hi John,
You're invited to Sarah's 40th birthday cruise on the Dragon Lady!
📅 Saturday, June 15, 2025
🕕 Boarding at 6:00 PM
📍 Pier 40, San Francisco
Please sign your waiver now (takes 2 minutes):
👉 dragonladysf.com/sarahs-party-2025
What to bring:
- Valid ID
- Light jacket (it gets breezy on the Bay!)
- Camera (or just your phone)
- Dancing shoes
See you on the water!
Captain Jerry & the Dragon Lady Crew
P.S. You can also upload old photos you'd like included
in your cruise memories (wedding photos, old vacations, etc.)
Your time investment: Zero. System handles it.
STEP 4: Guests Sign Waivers (Before Cruise)
Guest clicks link in reminder email
↓
Arrives at: dragonladysf.com/sarahs-party-2025
↓
Sees trip info + [Sign Waiver] button
↓
Fills out waiver form + signature
↓
Submits
↓
New row in Waivers sheet:
- All their info
- Signature capture
- Charter Name: sarahs-party-2025
- Timestamp
↓
Waiver_Status sheet updates:
- John Smith: ✅ Signed
- System stops sending John reminders
↓
If they checked marketing opt-in:
- Added to Marketing_List sheet
STEP 5: Jerry Checks Status (Day Before Charter)
Opens Waiver_Status sheet
↓
Filters by: sarahs-party-2025
↓
Sees:
✅ 9 people signed
❌ 3 people haven't signed:
- Mike Chen (has email, last reminded 1 day ago)
- Lisa Wong (has email + phone)
- Mike Wong (only has phone - can't email)
↓
Actions Jerry can take:
- Text Lisa/Mike Wong directly if urgent
- Trust the system (1 day reminder already sent)
- Have waivers ready on device for the 3 people
↓
Prints shame list: 3 names
Jerry's time: 1 minute to check, maybe 2 texts if urgent
STEP 6: Guest Portal Features (Optional, Before Cruise)
Guests can also:
Visit their link anytime before cruise
↓
See options:
┌─────────────────────────────────────┐
│ Sarah's 40th Birthday Cruise │
├─────────────────────────────────────┤
│ ✍️ Sign Waiver (2 minutes) │
│ 📸 Upload Old Photos │
│ (wedding pics, old vacations?) │
│ 🎵 Song Requests │
│ 🍽️ Dietary Restrictions │
│ 📋 Trip Details │
│ 🚗 Parking & Directions │
└─────────────────────────────────────┘
If they upload old photos:
↓
Stored in: /uploads/sarahs-party/memories/
↓
AI captions them
↓
Ready to merge with cruise photos later
STEP 7: Charter Day – Photo Collection
6:00 PM - Guests board
↓
Connect to "Dragon Lady Guest" WiFi
↓
Photos start auto-syncing from phones
↓
Photo Watcher script on Razer:
↓
→ Detects new photos
→ AI captions: "sunset from bow", "group at stern"
→ Facial recognition (learns from names in Waiver_Status)
→ GPS tags location on SF Bay
→ Categorizes automatically
→ Updates live gallery
↓
Guests can view in real-time:
dragonladysf.local/sarahs-party/photos
↓
"Look! Our photo from 5 minutes ago is already online!"
Jerry's actions:
- 2-3 stragglers sign waiver on device (Mike Chen, Lisa Wong, Mike Wong)
- Otherwise just captain the boat
- Photos handle themselves
STEP 8: End of Cruise – USB Generation
9:30 PM - Boat returns to dock
↓
Jerry clicks "Finalize Charter" button
(or system auto-triggers at return time)
↓
System reads from sheets:
• Charter_Bookings: Event details
• Waivers: All guest names (who was actually there)
• Charter_Guest_Lists: Organizer info
↓
Gathers photos:
• Tonight's photos (from WiFi sync)
• Old photos guests uploaded
• GPS data for map
↓
AI generates:
1. Complete story of the evening
"Sarah's 40th birthday began at sunset..."
2. Timeline with photos
"6:00 PM - Boarding & champagne..."
3. "Then & Now" page (old photos + tonight)
4. Highlights reel
5. Full gallery (organized by AI categories)
6. Interactive SF Bay map (GPS-tagged photos)
7. Guest info page (memories of the trip)
↓
Builds complete HTML website
↓
Burns to USB drives (one per guest from Waivers)
↓
Takes ~2 minutes total
↓
Crew hands USB to each guest as they leave:
"Your photos are ready! Plug into any computer."
What's on each USB:
DRAGON_LADY_SARAHS_PARTY/
├── START_HERE.html (double-click to open)
├── website/
│ ├── index.html (AI story of the evening)
│ ├── info.html (trip details from Charter_Bookings)
│ ├── gallery.html (all photos, AI organized)
│ ├── timeline.html (cruise timeline with photos)
│ ├── then-and-now.html (old + new photos merged)
│ ├── map.html (GPS-tagged locations on SF Bay)
│ └── photos/
│ ├── tonight/ (cruise photos)
│ ├── memories/ (old photos they uploaded)
│ └── all/ (everything together)
└── downloads/
└── all-photos.zip (download all at once)
Self-contained website. Works offline. No internet needed. Keep forever.
STEP 9: Post-Cruise Updates
Jerry updates Charter_Bookings:
- Status: confirmed → completed
- Takes 5 seconds
↓
System automatically:
- Adds all guests to Marketing_List (if opted in)
- Sends "Thank you!" email with link to re-book
- Starts 3-month marketing clock
STEP 10: Marketing Follow-Up (Automatic)
System checks Marketing_List every night
↓
For anyone where:
- last_email_sent was 3-6 months ago
- opted_in = yes
↓
Sends personalized email:
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Subject: Miss the San Francisco Bay? 🌊
Hi John,
It's been 4 months since you cruised with us
on Sarah's 40th birthday party!
Ready for another adventure on the Bay?
🎉 Host your own event
🏢 Corporate team building
💑 Anniversary celebration
🎂 Birthday party
Special repeat guest discount: 10% off your
next charter when you book this month.
Book your next cruise:
dragonladysf.com/book
See you on the water!
Captain Jerry & Dragon Lady Crew
P.S. Don't want these emails? [Unsubscribe]
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
↓
Logs "last_email_sent" = today
↓
Won't email again for another 3-6 months
Your time investment: Zero. Set it and forget it.
Result: Past guests book their own parties, corporate events, anniversaries.
Why This System Works
For Jerry & Staff:
Everything in familiar Google Sheets:
- No "black box" database
- No mysterious backend
- No "you need to learn our system"
- Just spreadsheets you already use
Anyone can help:
- Office staff can add bookings
- Crew can check waiver status
- Everyone sees the same data
- No training needed
Troubleshooting is visual:
- "Why didn't Mike get email?" → Check sheet, see no email
- "Did Lisa sign?" → Search Waivers, see timestamp
- "How many charters last month?" → Filter by date, count rows
Backup is native:
- File → Download as CSV (any sheet, anytime)
- Google's automatic version history
- Export old charters easily
- No special procedures
For Organizers (Like Sarah):
One simple link controls everything:
- Add guest list
- Guests sign waivers
- Upload old photos
- See trip details
- Make song requests
No phone tag:
- Don't have to collect waivers herself
- System reminds her guests
- She just pastes email list once
For Guests:
Before cruise:
- Get helpful reminders
- Sign waiver from home (2 minutes)
- See trip details anytime
- Can upload memories to include
During cruise:
- Photos appear in real-time
- Can browse as they're taken
- See their own photos instantly
After cruise:
- Leave with USB in hand
- Complete website with all photos
- AI-generated story of the evening
- Keep forever, works offline
Months later:
- Get friendly "come back" email
- Book their own charter
- Bring their friends
For You (Business-wise):
Old way:
- Chase waivers day-of (30 min chaos)
- Manually collect photos
- Hours organizing after cruise
- Guests wait days for photos
- Zero follow-up marketing
New way:
- 70-80% waivers done before cruise (smooth boarding)
- Photos organized automatically
- USB ready 2 minutes after docking
- Guests leave with memories
- Automatic marketing to past guests
Your actual time per charter:
- Add booking: 2-3 minutes
- Check waiver status: 1 minute
- Click "Finalize": 1 click
- Total hands-on time: ~5 minutes per charter
Everything else is automatic.
The Six Google Sheets (Complete Reference)
1. VIP_Requests (Existing)
- Purpose: Sales pipeline
- Who fills it: Website forms, you manually
- Who uses it: You, to vet leads
- Updates: When leads come in
2. Charter_Bookings (New – Your Control Panel)
- Purpose: Confirmed charters
- Who fills it: You (when booking confirmed)
- Who uses it: Everyone (staff, systems, you)
- Updates: Add bookings, mark completed
3. Charter_Guest_Lists (New – Auto-filled)
- Purpose: Expected guests per charter
- Who fills it: Organizers via website
- Who uses it: Reminder system, waiver matching
- Updates: When organizer submits list
4. Waivers (Existing)
- Purpose: Legal signatures
- Who fills it: Guests (before or day-of)
- Who uses it: Legal records, waiver status
- Updates: When anyone signs
5. Waiver_Status (New – Auto-generated)
- Purpose: "Shame list" of missing waivers
- Who fills it: System (auto-calculates)
- Who uses it: You, to see who's missing
- Updates: Real-time (as waivers come in)
6. Marketing_List (New – Auto-built)
- Purpose: Past guests who opted in
- Who fills it: System (from waivers + guest lists)
- Who uses it: Marketing email system
- Updates: After each charter, ongoing
What Needs to Be Built
✅ Already Working:
- VIP_Requests sheet
- Waivers sheet + signing system
- Photo AI (captions, categorization)
- Gallery builder
- HTML site builder
- Boat WiFi
🔨 To Build:
Phase 1: Booking System (Week 1)
- Charter_Bookings sheet structure
- Custom link generator (slug → URL)
- Pre-charter info portal (what guests see)
- Guest list submission form
Phase 2: Waiver Automation (Week 2)
- Charter_Guest_Lists sheet structure
- Waiver_Status sheet (auto-calculation)
- Reminder email system (scheduled sends)
- Email tracking & logging
Phase 3: Photo System (Week 3)
- Photo watcher script (monitors incoming)
- AI caption integration (Llama)
- Facial recognition setup
- Old photo upload interface
Phase 4: USB Generation (Week 4)
- Auto-merge system (old + new photos)
- AI story generation
- Timeline builder
- USB package generator
Phase 5: Marketing (Week 5)
- Marketing_List sheet structure
- Auto-population from waivers
- Follow-up email system (3-6 month schedule)
- Opt-out management
Total build time: ~5 weeks
Setup Steps (One Time Only)
Week 1: Foundation
- Create Charter_Bookings sheet with columns
- Link to your Google account (same as VIP_Requests)
- Test with one fake charter
- Verify custom link generation works
Week 2: Guest Management
- Create Charter_Guest_Lists sheet
- Create Waiver_Status sheet with formulas
- Test reminder email system
- Verify waiver matching works
Week 3: Photos
- Set up photo watcher on Razer
- Configure AI captioning
- Test facial recognition
- Test old photo upload
Week 4: USB System
- Test auto-merge (old + new photos)
- Test AI story generation
- Test full USB package
- Burn test USBs, verify they work
Week 5: Marketing
- Create Marketing_List sheet
- Configure auto-population
- Write email templates
- Test send schedule
Then: Run one real charter end-to-end to verify everything
Common Questions
Q: What if I need to cancel a charter? A: Change status in Charter_Bookings to "cancelled" – system stops all reminders
Q: What if guest email bounces? A: You'll see in Waiver_Status – can text them if you have phone number
Q: Can we change the reminder schedule? A: Yes, easy to adjust (maybe 1 week, 3 days, 1 day instead of 2 weeks, 1 week, 3 days, 1 day)
Q: What if someone doesn't have email? A: They sign day-of on device (like before). System just reduces that number.
Q: Can we add custom questions to waiver? A: Yes, just add columns to Waivers sheet
Q: Do I manage Charter_Bookings during cruise? A: No – once set up, only touch it to add bookings or mark completed
Q: What if I mess up the slug? A: Edit it in sheet, new link works immediately. Old link stops working.
Q: Can office staff help? A: Yes – share sheets with them, they can add bookings, check status
Q: What about people who unsubscribe from marketing? A: System respects it, sets opted_in = no, never emails again
Q: Can we see marketing campaign success? A: Yes – Marketing_List tracks total_charters, shows repeat customers
Q: What if the Razer is off during cruise? A: Photos still come in via WiFi, just get processed later when you turn it on
Q: Do guests need an app? A: No – everything is web-based, just visit the link
Q: What if two charters happen same day? A: Each has unique slug, system keeps them completely separate
Q: How do we handle no-shows? A: Their waiver is still in system, just mark charter as completed normally
Q: Can we export guest lists for insurance? A: Yes – Waivers sheet, filter by charter, download as CSV
The Big Picture
Before This System:
- Everyone signs waivers day-of → 30 min chaos at boarding
- Manual photo collection → crew running around
- Hours organizing photos → late nights after cruise
- Guests wait days/weeks → "when will we get photos?"
- No follow-up → one-time customers
- Hard to track who's coming → surprises at the dock
After This System:
- 70-80% waivers pre-signed → smooth boarding in 10 min
- Photos auto-organized → AI handles it
- USB ready 2 min after docking → instant gratification
- Guests leave with memories → "wow, this is amazing!"
- Auto marketing to past guests → repeat business
- Complete tracking in sheets → you know everything
Your Time Per Charter:
Before:
- Pre-cruise: Calls, emails, chasing info (2 hours)
- Day-of: Waiver chaos (30 min)
- Post-cruise: Photo organizing (3-4 hours)
- Total: 5.5-6.5 hours hands-on work
After:
- Add booking to sheet (3 min)
- Check waiver status (1 min)
- Click finalize button (5 sec)
- Total: 4-5 minutes hands-on work
That's 6 hours saved per charter.
At 100 charters/year = 600 hours saved = 15 full work weeks
Marketing ROI
Conservative estimate:
Past guest pool after 1 year: 1,200 people (100 charters × 12 guests avg)
Opt-in rate: 60% = 720 people in Marketing_List
Email every 4 months = 3 touches/year
Conversion rate: 5% book their own charter = 36 new bookings/year
Average charter value: $2,000
New revenue from marketing automation: $72,000/year
Cost to build: One-time development
Cost to run: $0 (uses existing Google Sheets + email)
Why Google Sheets Is The Right Choice
Not a database because:
- Jerry is not a database admin
- Staff needs to understand it instantly
- Needs to be exportable/archivable easily
- Needs to be transparent (see everything)
- Needs to be collaborative (multiple people)
Sheets wins because:
- Everyone already knows how to use it
- Built-in collaboration
- Built-in version history
- Built-in backup (Google's infrastructure)
- Easy to export/import
- Can add columns without "migrations"
- Can filter/sort/search natively
- Works on phone/tablet/desktop
- No "the database is down" scenarios
Plus: The website and automation just READ from sheets. They don't lock you in. Jerry owns his data, always.
Next Steps
-
Review this document with staff
- Make sure everyone understands the flow
- Get buy-in on the sheet structure
- Identify any missing pieces
-
Approve the build plan
- 5-week development timeline
- Phased approach (can use parts before everything is done)
- One test charter to verify
-
Set up test charter
- Pick a friendly client for first real test
- Let them know they're helping refine the system
- Get their feedback
-
Go live
- Start with new bookings
- Migrate existing bookings if needed
- Monitor first few charters closely
-
Optimize
- Adjust reminder timing if needed
- Fine-tune email copy
- Add features based on real usage
Support & Maintenance
Ongoing needs:
- Google Sheets: Free (you already have)
- Email sending: Free tier covers 100s of emails/day
- Photo storage: Your existing infrastructure
- Razer computer: You already have
- WiFi: You already have
No monthly fees. No subscriptions. No "software license."
If something breaks:
- Sheets are always accessible (you can fix data yourself)
- Photo system is local (reboot Razer if needed)
- Email system is simple (adjust templates in sheets)
- USB generation is standalone (run manually if needed)
System is designed to degrade gracefully:
- If reminders fail → guests can still sign day-of
- If photo AI fails → you still have the photos
- If USB generator fails → can burn manually
- Nothing is a "single point of catastrophic failure"
Questions for Jerry
Before we proceed, want to confirm:
- Sheet structure make sense? Any columns we're missing?
- Reminder timing good? Or prefer different schedule?
- Marketing approach OK? Comfortable with 3-6 month follow-ups?
- USB contents right? Anything else guests would want?
- Staff access plan? Who needs to see/edit what?
- Test charter timing? Ready to try this soon or wait until [specific date]?
This is the complete system. Everything runs through your Google Sheets. You're in control. The tech serves you, not the other way around.