Build an Airtable Help Desk: Intake, SLA Clocks, Escalation, and CSAT

Every team that supports anybody eventually builds a ticket tracker. In Airtable it usually starts as a table called Requests with a status single select and a person field, and it works for about three months. Then nobody can answer the two questions that matter — how long are people waiting? and what is about to breach? — because nothing in the base measures time.

This tutorial builds an Airtable service desk that does measure it: a clean intake, SLA clocks that pause when the ball is in the requester's court, escalation that fires before the deadline rather than after, and a satisfaction loop that tells you whether any of it is working. It is a different animal from an approval workflow, where the question is who signed off. Here the question is always how long.

The data model

Four tables. Resist the urge to add more until the first version is running.

Tickets — one record per request. Fields:

• Ticket ID — autonumber, plus a formula Ref of "TKT-" & ticket_id_padded if you want something people can quote in email.
• Subject, Description (long text).
• Requester — link to People, not a free-text email. You will want ticket history per person.
• Team — link to Teams (IT, Ops, Data, Facilities). This drives routing and SLA targets.
• Priority — single select: Urgent, High, Normal, Low.
• Status — single select: New, Triaged, In progress, Waiting on requester, Resolved, Closed, Cancelled.
• Assignee — collaborator field.
• Created at — created-time field (do not trust a manually entered date).
• First response at, Resolved at — date-time fields written by automations.
• Clock paused minutes — number, accumulated while waiting on the requester.

Teams — the routing and target table: Team name, Default assignee, Escalation contact, and four duration-ish numbers: Response target hours and Resolution target hours for each priority (easiest as eight number fields, or a child SLA targets table with Team, Priority, Response hours, Resolution hours if you prefer it normalised — see our data modelling guide).

People — requesters, with Email, Department, and a rollup of their open ticket count.

Ticket events — an append-only log: Ticket, Timestamp, Event type (Status changed, Assigned, Comment, Paused, Resumed, Escalated), Detail, Actor. Airtable's revision history is per-field, is not queryable, and ages out of the interface; if you ever need to prove when a ticket was escalated, you need your own log.

Intake that does not create work

Use an Airtable form, or an interface form if requesters are licensed users. Three rules:

1. Ask for the minimum. Subject, description, team, and one "how is this affecting you" question that maps to priority. Every extra field is a reason to send an email instead.
2. Prefill what you know. If requests come from an intranet link, pass the user's email into a hidden prefilled field so Requester resolves automatically. The mechanics are in our forms tutorial.
3. Do not let requesters set Urgent. Let them describe impact; let triage set priority. Otherwise everything is urgent by Thursday.

For email-originated tickets, forward a support alias into a parsing automation so a message becomes a record with the thread ID stored, which keeps replies attached to the right ticket. That plumbing is covered in Airtable and email.

SLA clocks: the formula work

Two targets per ticket: time to first response, and time to resolution. Pull them from the ticket's team and priority with a lookup or a SWITCH:

Response target hrs:
SWITCH({Priority},
  "Urgent", {Team response urgent},
  "High",   {Team response high},
  "Normal", {Team response normal},
  {Team response low}
)

Then the elapsed clock. The naive version is DATETIME_DIFF(NOW(), {Created at}, 'minutes'), and it is wrong in two ways that will cost you credibility in the first SLA review.

Wrong thing one: it counts the weekend. A ticket raised at 17:00 on Friday is not four hours late by Saturday breakfast. Full business-hours arithmetic in a formula is painful; the pragmatic approach is WORKDAY_DIFF() for whole days plus a same-day hours term, and accepting that it rounds. If your SLAs are contractual, do the maths in a scheduled script instead and write the result to a number field — scripts can hold a proper business-calendar function and a holiday list. See the scripting guide for the automation-script pattern.

Wrong thing two: NOW() is not live. Airtable recalculates NOW() periodically, not continuously, and formula fields containing it cannot be filtered on reliably in automations. For anything that triggers an alert, do not depend on a NOW() formula flipping a flag. Use a scheduled automation that runs hourly, reads Created at and the target, and acts.

Pausing the clock. When Status becomes Waiting on requester, write the timestamp to Paused at. When it leaves that status, add DATETIME_DIFF(NOW(), {Paused at}, 'minutes') into Clock paused minutes and clear Paused at. Your effective elapsed time is then elapsed − Clock paused minutes. This single mechanic is the difference between an SLA report your team believes and one they argue with.

Finally, a SLA state formula for the views:

IF({Status} = "Resolved", "Met/Missed (final)",
IF({Effective elapsed hrs} > {Resolution target hrs}, "Breached",
IF({Effective elapsed hrs} > {Resolution target hrs} * 0.75, "At risk", "On track")))

Escalation that fires early

One hourly scheduled automation does most of the work. Trigger: At scheduled time, hourly. Step one: find records in a view filtered to open tickets. Step two: a script, or conditional actions, that for each record decides:

• Over 75% of the resolution target and not yet resolved → set Escalation level to 1, notify the assignee in Slack or by email, log an event.
• Over 100% → Escalation level 2, notify the team's Escalation contact, log an event.
• No First response at and past the response target → notify the team channel regardless of level.

Two safeguards. First, idempotency: check Escalation level before notifying so the hourly run does not page someone twenty-three times. Writing the level and only acting when it changes is enough. Second, a run log: automations fail quietly, and an escalation engine that has been dead for a fortnight is worse than none. Build the monitoring described in automations that fail silently before you rely on this.

Also set First response at automatically: an automation on "when record updated" where Status changes from New, or when the first outbound comment is logged, writes NOW() once — guard it with IF first response is empty so a reopened ticket does not overwrite the original.

The queue interface

Three interface pages, each for one audience. The role-based interface patterns apply directly:

• Agent queue — a list filtered to "assigned to me, not resolved", sorted by SLA state then priority, with a record detail panel on the right for editing status and logging comments. Agents should never need to open the underlying base.
• Team lead — unassigned tickets, breached and at-risk counts by team, oldest open ticket, and a bar chart of volume by category. Add a filter for Escalation level ≥ 1.
• Requester portal — their own tickets only, using a current-user filter, with the comment field editable and everything else read-only. If requesters are unlicensed, publish this as an external interface instead; the trade-offs are in client portals.

Closing the loop: CSAT

When Status moves to Resolved, send the requester a one-click rating. The simplest version that works: an automation emails three prefilled form links (good / okay / bad), each prefilling the ticket's record ID and the score into a Feedback table. No login, no survey tool, one click. Roll the score up to the ticket and to the agent, and report monthly on three numbers only: median time to first response, percentage of tickets resolved inside target, and average CSAT. Add a fourth number — reopen rate — once you have a quarter of data, because a team under SLA pressure learns to close tickets early.

Housekeeping and scale

Resolved tickets should auto-close after seven quiet days, and closed tickets older than your retention window should move to an archive base on a schedule. A support desk accumulates personal data and free-text descriptions fast, so set the retention clock deliberately rather than keeping everything forever — see Airtable and GDPR. Keeping the hot table under a few tens of thousands of records also keeps the interfaces quick; the performance tuning guide covers what slows down first.

What to build in week one

Do not build all of this at once. Ship the Tickets table, the form, the agent queue, and First response at. Add SLA targets and the SLA state formula in week two, once you know what real response times look like. Escalation and CSAT come third. A service desk that measures two honest numbers beats one that models nine and is trusted for none.

BaseBrainers builds operational systems like this on Airtable every week, including the clock arithmetic and the escalation scripts most teams would rather not own. If you want help scoping a service desk or fixing one that has stalled, see our business operations work or get in touch.