Support-in-a-Box

Streamlined CX Ops automation using Forms, Sheets & Apps Script

Portfolio 2025

Support-in-a-Box (CX Ops Demo)

  • CX Operations
  • Process Design
  • AI / LLM
  • Google Apps Script
  • Google Sheets
  • Google Forms
  • Slack
  • Gmail
  • Notion
  • Automation
  • Metrics
Open Intake Form View Live Tickets Sheet KB Home (Notion)

Project Overview

Support-in-a-Box is a compact CX operations stack built with free tools to demonstrate how to design, document, and implement scalable support processes, automate first-response workflows with AI, and monitor the key health metrics (FRT, TTR, FCR, CSAT, NPS). It uses Google Forms for intake and CSAT, Google Sheets for the working queue + dashboard, Apps Script for logic and integrations, Slack for alerts, Gmail for messaging, and Notion for a lightweight runbook/KB.

  • Goal: Show end-to-end CX ops capability under tight scope—hands-on process design, automation, and reporting.
  • Role: Solo build—requirements, UX flows, implementation, QA, and documentation.
  • Outcome: A working demo with AI acknowledgments, Slack alerts, idempotent ticket creation, CSAT write-backs, and 30-day metrics.

How the Demo Works

  1. Submit Issue: User fills the Intake Form.
    Intake Form
  2. Slack Alert: Apps Script posts a formatted ticket summary to a Slack channel.
    Slack Notification
  3. AI Acknowledgment: An email is sent via Gmail with a friendly AI-drafted ack + relevant KB guide links.
    AI Acknowledgment Email
  4. Ticket Ingestion: A normalized row is appended to the Tickets sheet. A TicketID is written back to the intake row in the Tickets tab.
    Tickets Sheet
  5. First Response / Resolution: From the Sheet menu (Support ★), “Stamp First Response” and “Stamp Resolved (send CSAT)” set timestamps and email the CSAT link.
    First Response Example
  6. CSAT → Tickets: The CSAT form writes into a CSAT tab; the script maps TicketID, pushes CSAT (1–5) and NPS (0–10) back onto the matching ticket row. CSAT Survey Form.
    CSAT Survey
  7. Metrics: FRT_hours, Resolution_hours, FCR, CSAT, NPS roll into a 30-day dashboard, plus a Drivers view by Issue Type.
    Metrics Dashboard

Architecture & Components

  • Google Forms (2): Intake and CSAT forms (CSAT link is prefilled with TicketID on resolve).
  • Google Sheets:
    • Tickets (working table) with computed columns:
      • FRT_hours = hours from Created → FirstResponseAt
      • Resolution_hours = hours from Created → ResolvedAt
      • InLast30d = Created within 30 days
    • Metrics & Drivers: last-30-day rollups and IssueType-level cuts.
    • CSAT: response sink; script writes CSAT/NPS back to Tickets by TicketID.
  • Apps Script: single installable onFormSubmit trigger handles both Forms, with:
    • Idempotency & locking: Script lock + “Processed” flag per response row prevents duplicates.
    • Header-flexible mapping: robust field extraction even if columns move/rename.
    • Menu tools: “Stamp First Response”, “Stamp Resolved (send CSAT)”, “Mark FCR”, diagnostics, and CSAT backfill.
  • Integrations: Slack (webhook alerts), Gmail (ack + CSAT outreach), AI (ack copy), Notion KB (runbooks and self-serve fixes).

Process, Safeguards, and Ops Notes

  • Process design: Standard intake fields + tagging; explicit first-response and resolution steps; FCR captured as Y/N.
  • Runbook/KB: Notion guides for common drivers (Sign-in & Scopes, Common auth errors, Refresh schedule & limits, Field mapping, Data freshness checks).
  • Quality gates: CSAT/NPS tied to TicketID; position-aware + header-aware CSAT mapping; unlocks per-driver quality analysis.
  • Resilience: Single trigger, lock, and “Processed” guard eliminate duplicate ticket creation and re-posts.

Metrics Tracked (Last 30 Days)

  • FRT (First Response Time): median + 90th percentile of FRT_hours.
  • Resolution Time (TTR): median of Resolution_hours.
  • FCR %: share of tickets with FCR = Y.
  • CSAT: average of 1–5 ratings.
  • NPS: average 0–10 likelihood to recommend (demo-scale).

Note: This is a demo; “industry benchmarks” vary by product and segment. The build emphasizes instrumenting reliable metrics and showing how they connect to process changes.

Skills Demonstrated

  • CX Ops & Process: intake design, triage flow, KB/runbook, SLA-aligned stamps, FCR capture.
  • Automation: Apps Script trigger pipeline, Slack alerts, AI-drafted acks, CSAT write-back.
  • Systems Thinking: idempotency/locking, header-robust mapping, minimal-surface integrations.
  • Analytics: computed fields, 30-day filters, driver cuts, and definitions users can trust.
  • Documentation: Notion guides and in-sheet notes to support self-serve fixes.

What I’d Improve Next

  • Assignment & SLAs: add assignees/queues; auto-escalate on aging FRT/TTR; on-call paging.
  • Knowledge routing: auto-link KB by driver + product area; add search over runbooks.
  • Integrations: optional Zendesk/Intercom sync; Notion API for live article titles/IDs.
  • QA & Insights: transcript sampling; reason codes; weekly deltas on FRT/TTR/FCR/CSAT.

Privacy & Scope

Demo only. Uses Forms/Sheets email fields for notifications and prefilled CSAT links. No production customer data. Slack and Gmail are used solely for demo alerts and acknowledgments.