Zapier Automation: PMS Data to AI-Written Report Pipeline

What You'll Build

An automated pipeline that takes your manually-exported PMS data (pasted into a Google Sheet once per week), runs it through a ChatGPT prompt via Zapier, and delivers a formatted weekly revenue report draft directly to your email — ready to review and send. The result: your 2-3 hour Monday morning report-writing task becomes a 15-minute review-and-send.

Why This Matters for Revenue Managers

Revenue managers spend 6+ hours a week on reporting tasks that require no strategic judgment — copying numbers from PMS into Excel, formatting tables, writing the same narrative structure every week. Automating the mechanical parts returns that time to actual revenue management.

What You'll Need

• Zapier account (free tier works for basic setup; Zapier Professional at {{tool:Zapier.price}}/month for multi-step Zaps with AI)
• ChatGPT Plus or API access ({{tool:ChatGPT.plan}} — {{tool:ChatGPT.price}}/month, or OpenAI API key for lower per-use cost)
• Google Sheets (free with Google account)
• Gmail or Outlook (for report delivery)
• Your PMS report export — any format that lets you copy key numbers

Architecture Overview

The flow works in four stages: (1) You paste weekly PMS data into a designated Google Sheet (5 minutes). (2) A Zapier trigger detects new data in the sheet. (3) Zapier sends the data to ChatGPT with your report prompt template. (4) ChatGPT's response is formatted and emailed to you (and optionally your GM) as a draft for review.

The key design principle: you remain in control of every send. The automation generates the draft; you approve it before it goes anywhere.

PMS Export → Google Sheet → Zapier Trigger → ChatGPT Prompt → Gmail Draft
(5 min/week)   (data entry)   (automatic)      (automatic)      (your review)

Build Guide

Phase 1: Set Up Your Data Entry Sheet

Create a Google Sheet named "Weekly Revenue Data" with these columns on Row 1:

Row 2 onwards is your data. Each Monday morning, you'll fill in one new row with last week's numbers.

Add a second tab called "Property Context" with a text cell containing your property overview — hotel name, segment, comp set summary, and pricing philosophy. This gets included in every AI prompt automatically so you never have to re-explain context.

Tip: Add a column called "Trigger" in column J. Put the word "SEND" in that cell when you're ready to trigger the report generation. This prevents Zapier from firing on accidental edits.

Phase 2: Build the Zapier Automation

In Zapier, click Create Zap and build a 4-step Zap:

Step 1 — Trigger: Google Sheets — New or Updated Spreadsheet Row

• Connect your Google account
• Select spreadsheet: "Weekly Revenue Data"
• Select worksheet: Sheet1
• Trigger column: J (fires when "SEND" appears)
• Test trigger to confirm it pulls your most recent data row

Step 2 — Action: Google Sheets — Lookup Spreadsheet Row (Property Context)

• Same spreadsheet, "Property Context" tab
• Pull the text from cell A1 (your property overview)
• This becomes a variable you'll use in Step 3

Step 3 — Action: ChatGPT — Conversation (or OpenAI API — Send Prompt)

• Select model: GPT-4o
• System prompt (paste this exactly, filling in brackets):

You are a professional hotel revenue analyst writing weekly performance reports for hotel management. Write clearly, concisely, and professionally. Use hotel industry terminology but avoid jargon that ownership wouldn't understand. Always provide a brief forward-looking paragraph. Format reports with clear section headers.

• User prompt (use Zapier's variable syntax to insert your data):

Write a weekly revenue performance report for the week ending [Week_End_Date from Step 1].

Property context:
[Property Context text from Step 2]

Performance data:
- Occupancy: [Occupancy_Pct]%
- ADR: $[ADR]
- RevPAR: $[RevPAR] (Budget: $[RevPAR_Budget] | Prior Year: $[RevPAR_PY])
- Comp Set RevPAR: $[Comp_Set_RevPAR]
- MPI (Market Penetration Index): [MPI]
- Additional notes: [Notes]

Structure the report with these sections:
1. Performance Summary (3-4 sentences on overall results vs. budget and prior year)
2. Competitive Positioning (2-3 sentences on MPI and comp set context)
3. Key Drivers (bullet list of 3-4 factors that drove results this week)
4. Forward Outlook (2-3 sentences on what we're watching for next week)

Tone: professional, factual, confident. Length: 300-400 words.

Step 4 — Action: Gmail — Create Draft

• To: your email address (and optionally your GM's)
• Subject: "Weekly Revenue Report — Week Ending [Week_End_Date]" (use the variable)
• Body: Insert the ChatGPT output from Step 3
• Set to "Create Draft" (not "Send") — you review before sending

Save and turn on the Zap.

Phase 3: Test End-to-End

1. Add a test row to your Google Sheet with real or sample data
2. Put "SEND" in column J
3. Wait 1-2 minutes for Zapier to trigger
4. Check your Gmail drafts folder for the generated report
5. Review the output — is the tone right? Are the numbers referenced correctly?

If the tone or format is off, edit your Step 3 prompt and rerun.

Common fixes:

• Output too generic: Add more property context to the system prompt or the property context tab
• Numbers formatted wrong: Add currency/percentage formatting instructions to the user prompt ("Format all dollar values with $ and two decimal places")
• Too long or too short: Adjust the word count in the final instruction

Real-World Walkthrough: Monday Morning, 12 Minutes Total

8:00 AM — You pull your PMS weekly summary (you do this anyway). Key numbers: 78% occupancy, $198 ADR, $154 RevPAR vs. $161 budget, comp set at $149 RevPAR, MPI 103.4. Notes: "Group cancellation Wed night offset by corporate compression Thurs-Fri."

8:05 AM — You open your Google Sheet, enter the numbers into Row 7, type "SEND" in column J.

8:07 AM — Zapier fires. ChatGPT generates the report draft.

8:09 AM — You open your Gmail drafts folder. The draft reads:

8:12 AM — You make two edits, add one sentence about next week's citywide conference, and send.

Total time: 12 minutes. Previous process: 90-120 minutes.

Customization Options

• Monthly owner report version: Create a second Zap on a monthly trigger with a longer prompt template for the ownership narrative
• Add STR data: Create a second tab for STR comp set index data and pull it into the same prompt
• Slack delivery: Replace Gmail with a Slack Zap to post the draft to a private channel for GM review
• Multiple properties: Duplicate the Zap with a different spreadsheet and prompt for each property

Maintenance

• Monthly: Review 4-5 recent drafts for quality drift — if outputs become formulaic or miss important context, refresh the prompt template
• Quarterly: Update the Property Context tab with any market changes
• As needed: Adjust the prompt if your report format requirements change (new ownership, new GM preferences)

What This Won't Do

• Pull data directly from your PMS automatically — you still enter numbers into the Sheet (most PMS systems don't have Zapier integrations; this is the intentional manual checkpoint)
• Send reports without your review — every output lands in drafts first
• Replace judgment on unusual weeks — add context in the Notes column and the AI will incorporate it, but you still need to review anything anomalous