Nightly PMS summary ingest

Mews emails each property a nightly business-intelligence spreadsheet. That is a stack of attachments a night, each with roughly seventeen sheets, none of it queryable. The flash reports downstream need clean numbers, not an inbox.

How it works

A Python job fetches the emails over IMAP, read-only and idempotent with a backfill on first run, and parses each workbook into per-hotel occupancy, ADR, and RevPAR, written to a parquet store the flash reports consume.

• Handles the export's UTC-anchored timing, where 'yesterday' in the file is the night still in progress.
• Knows the month widgets are forward-inclusive, so day-over-day deltas are booking pickup, not rooms slept.
• Resolves each property from a cell inside the file, not the unreliable filename.

Live. The subtle work was semantic, not plumbing: figuring out which field in a UTC-stamped export is a realized night versus an on-the-books forecast. Get that wrong and the downstream report inverts its weekday pattern, which it did once before I caught it.

This is the unglamorous data layer under everything else. An owner cannot reconcile ten spreadsheets a night, but an ingest agent normalizes them by evening and the reports just work. Reliable plumbing is what lets the exception rate fall.