Python Automation

Enterprise Ticketing & Revenue Data Pipeline

Turning daily ticketing PDFs and emails from multiple vendors into one automated revenue dashboard for a music festival organizer.

Section: Python Automation

Overview

Palm Tree Crew, a live music festival organizer, needed accurate daily revenue and attendance numbers across several cities. We built a custom Python pipeline on Google Cloud that reads vendor audit emails, applies the client’s business rules, stores everything in BigQuery and renders an executive dashboard in Google Sheets with no manual steps.

The business problem

Every day the operations team downloaded static PDF audits from Ticketmaster, Front Gate Tickets and Posh. They split passes by hand (a 2-Day Pass counts toward both Friday and Saturday) and typed the numbers into a master Google Sheet. The work took hours and invited mistakes. VIP Table and Corporate Sponsorship revenue lived in separate spreadsheets, so leadership never saw total gross and remaining capacity in one place.

The solution

A cloud-hosted ETL pipeline runs on a schedule. It intercepts vendor emails through the Gmail API, parses PDF and HTML reports with custom Python code, calculates the client’s metrics, loads the results into Google BigQuery and rebuilds a styled Google Sheets dashboard. Offline revenue from other spreadsheets is linked in through a manual configuration area.

Project snapshot

How the System Works

The pipeline runs once per reporting cycle with no human steps, from vendor email to finished dashboard.

Architecture

Image: Vendor emails enter through Gmail, the Python service on Cloud Run parses them and applies the client’s rules, BigQuery keeps the history, and the Google Sheets dashboard shows the result.

Offline VIP and sponsorship sheets are linked into the dashboard with IMPORTRANGE.

Core Pipeline Components

The pipeline is custom Python code split into four parts, each with one job.

Automated ingestion and parsing

The Gmail API with secure OAuth intercepts the daily audit emails from each vendor. Bespoke parsing engines handle the unstructured formats:

• Ticketmaster: a custom PDF scraper that bypasses AWS API Gateway redirects, reads complex tabular data and adjusts to unannounced layout changes by the vendor.
• Front Gate Tickets: an HTML parser that extracts inline tables directly from the email body.

Business logic

The code applies the client’s own rules instead of only moving data:

• The 2-Day Rule: splits a multi-day pass into the correct daily attendance buckets, for example one 2-Day Pass counts toward both Friday and Saturday.
• Wrap rates: computes 24-hour sales velocity.
• Dynamic capacities: detects and adjusts venue capacity limits from live vendor reports, so no hardcoded limit goes out of date.

Data warehouse

All parsed data goes into Google BigQuery to keep full fidelity and prevent double-counting. Three tables hold the history of every ticket sold:

Dynamic dashboard rendering

The final step renders an executive-ready Google Sheets dashboard. The code formats the layout, freezes rows, color-codes stale data feeds and calculates multi-day averages.

A #MANUAL configuration area lets non-technical team members link external spreadsheets with =IMPORTRANGE() for offline VIP Table and Sponsorship revenue. The dashboard syncs this data automatically, so total top-line revenue appears in one view.

Product Experience

The executive team sees one Google Sheets dashboard that is rebuilt automatically from the BigQuery data. It formats itself, freezes the header rows, color-codes feeds that have not updated and shows multi-day averages, daily wrap rates and remaining capacity per show.

Results & Impact

The daily reporting cycle went from hours of manual transcription to no manual steps. The report gives no counts of events, hours saved or revenue, so none are stated here.

Technical Challenges & Solutions

Unstructured vendor formats

Challenge

Ticketmaster sends static PDFs behind AWS API Gateway redirects, and Front Gate sends tables inside email bodies.

Solution

A custom PDF scraper that follows the redirects and adapts to layout changes, plus an HTML parser for inline tables.

Outcome

Both vendors feed the pipeline without manual downloads.

Business rules that were done in the operator’s head

Challenge

A 2-Day Pass had to be split across Friday and Saturday by hand.

Solution

The 2-Day Rule, wrap rates and capacity detection are coded into the pipeline.

Outcome

Consistent daily buckets with no mental math.

Outdated hardcoded limits

Challenge

Venue capacities change, and fixed numbers in code go stale.

Solution

Capacities are read from live vendor reports.

Outcome

The dashboard shows current remaining capacity.

Double-counting and audit trail

Challenge

Daily reports overlap, so repeated loads could count a ticket twice.

Solution

A three-table BigQuery design (raw_reports, report_lines, daily_snapshots) keeps the original data and the calculated snapshots separate.

Outcome

A historical record of every ticket sold.

Offline revenue in separate sheets

Challenge

VIP Tables and Sponsorships were tracked outside the main sheet and maintained by non-technical staff.

Solution

A #MANUAL configuration area where staff link external sheets with =IMPORTRANGE().

Outcome

Offline revenue appears in the dashboard without code changes.

Key Takeaways & Future Development

Key takeaways

• End-to-end automation: email ingestion, parsing, calculation, storage and reporting run without human input.
• Custom code where vendors give no API: parsing PDFs and email tables made the data reachable.
• Built for scale: a decoupled design and an onboarding template let the client add shows and platforms quickly.

Future development

• Add parsers for further ticketing platforms as the client adds vendors.
• Add alerts when a vendor email is missing or a report layout changes.
• Extend the dashboard with ad-spend data to compare against sales velocity.

Page · Plain text

Event-Driven Quote Triage for a Freight Brokerage

An event-driven Python service (FastAPI, Celery, Playwright and Claude) that answers load quote requests from a shared inbox with three ranked carriers. Median first reply: 48 seconds, down from 7 minutes 40.

Section: Python Automation

• Client: regional 3PL, 11 dispatchers
• Stack: Python 3.12 · FastAPI · Celery + Redis · Postgres · Browserbase + Playwright · Airtable · Anthropic API (Claude Sonnet 4.6) · Slack

The problem

Load quote requests landed in a shared quotes@ inbox, around 40 a day. A dispatcher would read the request, log into the carrier portal (no public API for what they needed), check which trucks were available on that lane, cross-reference an internal rate sheet, and reply with three carriers and a price. Six to nine minutes each, every time.

Their first automation attempt was a Python script on a five-minute cron driving a hosted agent platform. It worked about 70% of the time, cost roughly $0.42 per request, and failed silently. They usually found out from a customer, not a log.

Three root causes, and all three shaped the rebuild:

• Polling. A 0–5 minute delay before anything started, and the cron had no idea what the previous run had done.
• Re-planning on every run. The agent asked an LLM to navigate the portal from scratch each time, so any UI jitter became a fresh failure mode, and every run paid full navigation token cost.
• No durable state. Nothing recorded which requests had been handled, so retries either duplicated replies or never happened.

The architecture

Everything runs as a small Python service on a single Fly.io machine, a FastAPI app that receives Gmail push notifications, and a Celery worker pool that does the actual work.

Image: Event-driven quote triage pipeline: a FastAPI webhook verifies and acknowledges the push, the request is de-duplicated and queued (Postgres key, Redis queue), Browserbase and Playwright check portal availability while an Airtable rate sheet supplies lanes, margins and exclusions, Claude ranks the top 3 carriers, and a threaded reply goes out on Gmail and Slack.

Key engineering decisions

Real-time trigger, not a cron. Gmail's users.watch publishes to a Google Pub/Sub topic, which pushes to a FastAPI endpoint. The handler does three things and nothing else: verify the JWT on the push request, write the message ID to Postgres, and return 204. Pub/Sub redelivers anything not acked within 10 seconds, so all real work is handed to Celery and the endpoint stays under 50 ms.

@app.post("/gmail/push")
async def gmail_push(req: Request, claims=Depends(verify_pubsub_jwt)):
    envelope = await req.json()
    history_id = decode_message(envelope["message"])["historyId"]
    for msg in gmail.list_since(history_id):
        if requests_repo.claim(msg.thread_id):   # INSERT ... ON CONFLICT DO NOTHING
            process_quote.delay(msg.thread_id)
    return Response(status_code=204)

Idempotency lives in the database, not in memory. claim() is an INSERT ... ON CONFLICT DO NOTHING on the Gmail thread ID. If Pub/Sub redelivers, or the safety net and a live webhook race each other, exactly one worker wins the row. This is the piece that eliminated duplicate replies outright.

Cached navigation instead of per-run LLM planning. Playwright drives a Browserbase session with stored credentials and a persistent context. The first time the portal layout is seen, an LLM produces a navigation plan, a sequence of selectors and actions, which is hashed against the page structure and cached in Postgres. Every subsequent run replays the cached plan with plain Playwright calls: no LLM, no tokens, sub-second steps. Only when a replay step fails does the code fall back to re-planning, write the new plan, and post a Slack notice that the portal changed. That's the same idea as Skyvern's Route Memorization, built explicitly so the failure path is visible rather than silent.

Prompt caching on the ranking call. The system prompt, the scoring rubric, and the serialised carrier roster are static across requests and sit in a cached block; only the parsed request and the fresh availability data go in the user turn. Roughly 11k stable input tokens moved to cache reads.

system=[{
    "type": "text",
    "text": RUBRIC + roster_block,
    "cache_control": {"type": "ephemeral"},
}]

Concurrency capped deliberately. The Celery pool runs three workers. The portal rate-limits aggressively and had previously locked the shared account twice, so throughput was traded for account safety.

State machine and the safety net

Every request row moves through an explicit status column. The hourly sweep is the belt-and-braces layer for the rare case where a Pub/Sub push is lost or a worker dies mid-run.

Image: Request states and recovery: a request is processing (row claimed, timestamp set), then done (reply sent, thread id) on success or failed (alert posted to Slack) on error. An hourly sweep re-runs rows stuck for more than 15 minutes.

The sweep is a Celery Beat task, about 30 lines: select rows in processing with claimed_at < now() - interval '15 minutes', reset the attempt counter, re-enqueue. After three attempts it moves to failed and posts a Slack alert with the thread link and the last exception, so a dispatcher can handle it manually and an engineer can see exactly where it broke.

Errors are handled in three tiers rather than one generic retry: transient network and portal timeouts retry with exponential backoff inside the task; a failed selector replay triggers re-planning and a "portal layout changed" notice; anything else fails the row loudly.

Results

The cost drop came from two places roughly equally: cached navigation removed the per-run planning tokens, and prompt caching cut input cost on the ranking call.

Delivered

Dockerised FastAPI + Celery service with a one-command deploy; Postgres schema and migrations; the Browserbase automation module with cached-plan replay and fallback; the Claude ranking module with the rubric prompt; Slack alerting; a runbook covering the four common failure modes and how to re-run a request by thread ID; and a 45-minute handoff call testing a live request end to end.

Page · Plain text

Ingredient Quoting Across 22 Supplier Portals

A Python pipeline (Prefect, Skyvern and GPT-4.1) that quotes all 22 approved suppliers for every ingredient reorder and delivers a ranked comparison in about four hours instead of 3.5 days.

Section: Python Automation

At a glance

Mid-size generic drug manufacturer, ~40 SKUs in production. Five weeks. Python 3.12, Prefect, Skyvern Cloud, OpenAI GPT-4.1, Postgres, S3, Slack. Went from quoting a fifth of their supplier base per order to quoting all of it.

The 6% nobody could see

Production planning flags a reorder for an active ingredient. A procurement officer opens a portal, searches the CAS number, pulls a price, downloads the spec sheet, pastes it into Excel. Then the next portal. Then the next.

Twenty-two suppliers were on the approved list. In practice the officer got through five or six before the purchase order had to go out, and the same five or six every time, the ones with the least annoying logins.

The waste wasn't the officer's afternoon. It was the sixteen vendors nobody had time to ask. When the full list was finally quoted, the cheapest compliant supplier turned out not to be one of the usual five surprisingly often.

Why this wasn't a scraping job

Three things ruled out the obvious approach, and a hand-written Playwright suite had already been abandoned for exactly these reasons.

Vendors don't agree on what anything is called. Metformin HCl USP, metformin hydrochloride BP/EP, or a bare product code, same molecule, three listings, no shared key except the CAS number buried somewhere on the page.

Quotes don't arrive in one shape. Some portals render a price table. Some hand back a PDF. Some won't tell you anything until you've submitted an RFQ form and waited for an email.

And it's a regulated purchase. Pharmacopoeia grade, GMP status, and whether the supplier holds a DMF or CEP are pass/fail gates, not preferences. A cheap quote from a vendor without the right filing isn't a cheap quote. It's not a quote at all. Every choice has to survive an audit two years later.

Following one RFQ through

Image: Multi-vendor ingredient quoting pipeline: RFQ intake (CAS number, grade, quantity), a Skyvern browser handles login, 2FA and CAPTCHA, prices come from an in-page table or from a downloaded and parsed PDF or emailed quote, GPT-4.1 normalises and validates them, and the ranked comparison goes to a Slack thread with an audit log.

A reorder request arrives as a CAS number, a grade, and a quantity. Before anything opens a browser, that gets resolved against an internal ingredient register, deterministically, on the CAS number, never on the product name.

Prefect then fans the request out to twenty-two isolated subflows, one per vendor. Each launches its own Skyvern cloud browser and logs in. Nine of these portals want 2FA and two sit behind CAPTCHAs; that's handled inside the login step rather than being a per-vendor engineering problem.

From there the paths diverge by portal. Where the price sits in the page, it's read straight off the page against a schema. Where it only exists as a PDF or an emailed quote sheet, the file is downloaded and parsed separately.

Everything then funnels back into a single normalisation pass, one model call, one strict schema, twenty-two vendors reduced to comparable rows: unit price, currency, unit of measure, MOQ, lead time, incoterm, grade, GMP status, DMF reference.

The ranked comparison lands in Slack about four hours later, with every row linking to the evidence behind it.

Four forks in the road

Skyvern rather than raw Playwright

The rejected option was writing and maintaining twenty-two scripts. That's what had already failed. At this many portals somebody is always redesigning something, and the maintenance load quietly exceeded the hours the automation was saving. Buying the resilience rather than building it was the entire reason the project was viable a second time.

1Password rather than a second vault

Skyvern can read credentials from several providers, and the client was already on 1Password. The alternative, standing up a dedicated secrets store, would have meant twenty-two sets of credentials living in two places, which is worse security dressed up as better engineering.

Selectors first, model second

Every stable element on a page gets a selector and a prompt. The selector runs, costs nothing, and usually works; the model only engages when it misses. Going model-first on everything would have been simpler to write and roughly ten times the running cost.

The model never decides what an ingredient is

It was tempting to let a model reconcile vendor product names. It isn't allowed to. Where a listing has no CAS field, the model may suggest a match, and that suggestion goes to a human. Misidentifying an ingredient in this industry isn't a data quality issue, and no accuracy figure would have made that trade acceptable.

When it breaks

Image: Route Memorization in practice: a memorized route replays deterministically. If the replay works the quote is captured with its evidence hash; if the route breaks it is regenerated by AI with a diff alert; if it keeps failing it goes to a review queue with the session recording attached.

Skyvern's Route Memorization compiles a successful run into a deterministic script and replays it. When a portal changes and the replay fails, it re-plans with AI, finds the new path, and regenerates the script. That mechanism does the heavy lifting here.

What it doesn't do is announce itself. A regenerated route is a silent event, and silent events don't belong in a regulated purchasing process.

So the wrapper watches for it. Each subflow records its action count and wall-clock duration; a memorised replay sits in a tight, predictable band, and an AI re-plan blows straight through it. That signal tags the run as a regeneration, diffs the new route against the old one, and drops a summary into Slack so someone can see what the portal changed before the next cycle.

If a regenerated route produces a quote that can't be evidence-checked, that vendor goes to a review queue with its session recording attached instead of into the comparison. About 4% of quotes land there, mostly from two portals that render prices as images.

The result is that failure is local. One portal breaking means twenty-one quotes still land and the RFQ is still useful.

Impact

Page · Plain text

Python MCP Server Connecting Claude to a Field-Service CRM

A hosted, OAuth-protected MCP server in Python that lets a home-services business ask Claude about customers, jobs, invoices and leads, with write actions hidden unless allowed.

Section: Python Automation

The Problem

A home-services business wanted to ask Claude questions about customers, jobs, invoices and leads, and to take safe actions, without giving it raw database access.

The Solution

A Model Context Protocol server that exposes the full REST API of the business’s field-service software (Housecall Pro) as about 20 domain modules: customers, jobs, estimates, invoices, leads, employees, scheduling, webhooks and more. They are all mounted under one FastMCP app. It is deployed on Railway and connects straight into claude.ai.

Notable details

• OAuth login sits in front of the server (GitHub provider, redirecting back to claude.ai), so no shared API keys are handed to users.
• A read-only enforcer middleware hides every tool whose name contains create, update, delete, send, void or dispatch from the tool list, unless it is explicitly allowed. This is per-agent permission control at the protocol level.
• Tools are namespaced (customers_*, jobs_*, invoices_*), so the model cannot confuse one domain with another.

Image: The server entry point: GitHub OAuth in front, one FastMCP app, and each domain module (customers, jobs, estimates, invoices, leads, employees) mounted under its own namespace.

Image: The repository: one Python module for each area of the field-service API, plus a Procfile for deployment on Railway. The commit messages are blurred.

Built with

Python, FastMCP, OAuth 2.0, REST and Railway.

Page · Plain text

Daily Ticket-Count Automation: Gmail PDFs to BigQuery and Google Sheets

A Python job that reads daily Ticketmaster audit PDFs from email, stores every figure in BigQuery and publishes a Google Sheet that mirrors the team’s existing workbook.

Section: Python Automation

The Problem

An events company received daily Ticketmaster audit PDFs by email and re-typed the figures into a workbook by hand.

The Solution

A daily job reads the report emails through the Gmail API (with an OAuth refresh token), downloads and parses the audit PDFs, stores every figure in BigQuery, and publishes a Google Sheet that mirrors the team’s existing workbook. It was built spec first: a design spec, then an implementation plan, then tests with fixture PDFs, then the pipeline.

Image: The project repository, with the ticket-count source code, tests, scripts and design documents.

Image: The Google Sheet the job publishes: tickets sold per day against capacity, split into GA and VIP, with the percentage sold. Event names and some tab names are hidden.

Image: The Run Log tab, with one row for each run of the job and its time.

Built with

Python, the Gmail API (OAuth), PDF parsing, BigQuery, the Google Sheets API and pytest.

Page · Plain text

Production Linux Server Audit and Repair for a Data Subscription Site

An audit of the single Ubuntu server behind a financial-data subscription site, a fix for its silently failed social-posting pipeline, and a documented setup so it can be maintained safely.

Section: Python Automation

The Problem

A financial-data subscription site ran a Python backend, scheduled jobs, blog and email automation and a social-posting pipeline, all on one Ubuntu VPS. The social pipeline had silently died. The client needed an audit, a fix and a roadmap.

What Was Done

• Audited the whole server (systemd units, cron, logs, nginx, gunicorn, the SQLite database and the backup job) and wrote a plain-English client report with a health score and prioritised fixes.
• Fixed the dead social pipeline with two targeted changes and stabilised the scheduling.
• Set up maintenance through a git mirror of /opt with a CLAUDE.md that describes the architecture, so Claude Code can work on it safely.

How the system runs

A single source of truth: one SQLite database plus JSON outputs.

20:00 ET  systemd timer  → nightly_pipeline.sh
                            ingest OHLC → run scanners → build JSON → DB backup to Drive (rclone)
                            → build newsletter + blog post (publish via WP REST API)
                            → send daily email (Brevo)
15:45 ET  systemd timer  → live_pipeline.py (real-time signals + options pricing)
always    systemd service → gunicorn Flask API :5500 behind nginx + Cloudflare Tunnel
cron      08:15 / 08:45 / every 15 min market hours / 21:00 → content engine renders PNG/MP4 cards and posts to IG / X / TikTok

Built with

Python, Flask, SQLite, systemd, cron, bash, nginx, gunicorn, Cloudflare Tunnel, rclone, JWT auth, Stripe, the WordPress REST API, Brevo and the Post for Me API.

Page · Plain text

Cold Email Copy Generation with Automatic Quality Checks

An AI tool that writes five cold email sequences in spintax format in minutes and checks each one against eight quality rules, rewriting any that fail.

Section: Python Automation

Overview

Cold email copy generation and quality control. Instead of spending hours writing email variations by hand in spintax format and checking each line against banned phrases and formatting rules, teams now get five production-ready email sequences in minutes.

The Problem

Creating cold email variations meant hours of manual work. Writers had to draft email sequences by hand, then apply spintax format line by line, a tedious process where every variation had to be written and formatted separately. Quality control was completely manual: each line had to be checked against banned phrases and banned words, word counts verified, spintax coverage confirmed, and offer rotation rules checked. With no automated checklist, mistakes slipped through before emails went into the sending tool. Creating five email sequences took 5 to 10 hours of work, was error-prone, and depended entirely on someone being available to do it correctly.

What the Automation Does

Image: The team interface: a chat assistant on the left, a button to generate the week’s strategy, and Copy Variations and History at the top.

Step 1: Email sequences are generated

The automation takes the email parameters and requirements, then uses AI to generate five complete cold email sequences in full spintax format, with multiple variations per email step (A/B variants) so testing options are built in.

Step 2: Automatic quality checks run

Every generated copy is automatically checked against eight quality rules: word count, spintax coverage on every line, banned phrases, banned words, offer accuracy, hook uniqueness, offer rotation compliance, and more. If a copy passes all checks, it is flagged as production-ready. If it fails, the system automatically rewrites it (up to three retries) to meet the requirements.

Step 3: Ready-to-send copies are delivered

The team receives five email sequences with a pass or fail verdict on each. Every copy is either production-ready or clearly flagged as needing manual revision, so there are no surprises or quality issues after the copy goes into the sending tool.

Tools and Integrations Used

• Custom code automation (AI-powered email generation and quality control)
• Anthropic Claude (copywriting and analysis)
• Python and FastAPI (backend automation)
• TypeScript, React and Next.js (team interface)

The Result

Teams no longer spend 5 to 10 hours writing and quality-checking email sequences by hand. Five production-ready sequences now generate automatically in minutes. Quality control shifted from a manual checklist to an automated pipeline that catches banned phrases, spintax gaps, word count violations and formatting mistakes instantly. Every copy follows the same proven structure with consistent formatting. What used to take hours of manual writing and QA now happens automatically, freeing the team to focus on strategy and testing instead of production work.

Summary

The tool automates the full cycle from email copy generation to quality-checked delivery. Teams provide their email requirements, the automation generates five complete sequences in spintax format, automatic quality checks verify everything, and production-ready emails are delivered in minutes instead of hours. The team stays in control, since they review and approve the output, while the repetitive writing and quality-checking work is handled automatically.

Page · Plain text