Onboarding feedback system¶

Overview¶

An automated email campaign that collects customer feedback after onboarding and encourages satisfied clients to leave Google Reviews. The system sends up to 3 emails based on customer response, with different paths for positive responders, negative responders, and non-responders.

Key Features:

Automated star-rating feedback collection
Google Review solicitation for satisfied clients
Automated Google Review detection and matching
Support ticket creation for negative feedback
Fillout form with auto-reply for detailed negative feedback

Summary of system¶

After a client's cleaning service goes live, they automatically receive an email asking them to rate their onboarding experience (1-5 stars).

Happy clients (4-5 stars) are redirected to leave a Google Review, with follow-up reminders if they don't
Unhappy clients (1-3 stars) are redirected to a feedback form and a support ticket is automatically created
Non-responders get up to two reminder emails

The system automatically detects when someone posts a Google Review and stops sending them reminders. This prevents the spammy experience of asking for a review from someone who already left one.

Email Flow¶

                              ┌──────────────┐
                              │   Email 1    │
                              │ Initial ask  │
                              └──────┬───────┘
                                     │
              ┌──────────────────────┼──────────────────────┐
              │                      │                      │
              ▼                      ▼                      ▼
      ┌──────────────┐      ┌──────────────┐       ┌──────────────┐
      │   POSITIVE   │      │  NO RESPONSE │       │   NEGATIVE   │
      │  (4-5 stars) │      │              │       │  (1-3 stars) │
      └──────┬───────┘      └──────┬───────┘       └──────┬───────┘
             │                     │                      │
             │ 1 hour              │ 3 days               │
             ▼                     ▼                      ▼
      ┌──────────────┐      ┌──────────────┐       ┌──────────────┐
      │   Email 2a   │      │   Email 2b   │       │   Campaign   │
      │ Google nudge │      │   Reminder   │       │    Ends      │
      └──────┬───────┘      └──────┬───────┘       │              │
             │                     │               │ → Fillout    │
             │ 3 days              │               │ → Auto-reply │
             │              ┌──────┴──────┐        │ → Support    │
             │              │             │        │   ticket     │
             │              ▼             ▼        └──────────────┘
             │       ┌──────────┐  ┌────────────┐
             │       │ POSITIVE │  │ NO RESPONSE│
             │       │(4-5 star)│  │            │
             │       └────┬─────┘  └─────┬──────┘
             │            │              │
             │            │ 1 hour       │ 3 days
             │            ▼              ▼
             │     ┌──────────────┐ ┌──────────────┐
             │     │   Email 3b   │ │   Email 3c   │
             │     │ Google nudge │ │ Last chance  │
             │     └──────────────┘ └──────────────┘
             │
             ▼
      ┌──────────────┐
      │   Email 3a   │
      │ Final nudge  │
      └──────────────┘

EXIT CONDITIONS:
• Negative response → Campaign ends (Fillout auto-reply + support ticket created)
• Google review posted → Campaign ends (detected automatically)
• Client no longer has active services → Removed from views

System Components¶

Component	Purpose	Location
Airtable Script	Processes views, sends emails, updates statuses	15-min automation 3
Cloudflare Worker	Star clicks, Google review link tracking, email proxy	feedback-redirect.jordan-845.workers.dev
Google Apps Script (Email)	Sends emails via Gmail API	feedback-email-sender
Google Apps Script (Reviews)	Detects Google reviews, matches to contacts	Review Detection
Fillout Form	Captures negative feedback with auto-reply	Onboarding Feedback
Support Ticket Script	Creates tickets from negative feedback	15-min automation 3

How It Works¶

Email Sending Flow¶

Every 15 minutes, the Airtable automation runs the feedback campaign processor script
The script queries each view in sequence and processes eligible contacts
Emails are sent via Cloudflare Worker → Google Apps Script → Gmail API

Star Rating Click Flow¶

Client clicks a star rating in email
Request hits Cloudflare Worker
Worker records rating, response type, and timestamp in Airtable
Client is redirected to:
4-5 stars: Google Reviews page
1-3 stars: Fillout feedback form

Google Review Link Click Flow¶

Client clicks "Leave a Google Review" link
Request hits Cloudflare Worker
Worker records click timestamp in Airtable ({Clicked Google review link})
Client is redirected to Google Reviews

Google Review Detection Flow¶

Every 15 minutes, the Review Detection script runs
Fetches recent reviews from Google Business Profile API
Fetches contacts who clicked the review link in the last 14 days
Matches reviews to contacts by name (exact or fuzzy)
Updates {Google review posted} and {Google review match confidence}
Sends email alert for fuzzy matches requiring manual review

Negative Feedback Flow¶

Client clicks 1-3 stars → redirected to Fillout form
Fillout sends automatic thank-you email
Every 15 minutes, Airtable script creates support ticket from negative feedback
Ticket includes feedback comments, contact details, and link to contact record

Email Content¶

Email 1 — Initial Ask¶

Subject: How was your onboarding, {First name}?

Asks the client to rate their experience by clicking 1-5 stars.

Email 2a — Positive Follow-up¶

Subject: Glad you're happy, {First name}!

Thanks them for positive feedback and asks for a Google review. Sent 1 hour after clicking 4-5 stars.

Email 2b — Reminder¶

Subject: Quick reminder, {First name}

Reminds non-responders to rate their experience. Sent 3 days after Email 1.

Email 3a — Final Google Nudge¶

Subject: One last thing, {First name}

Final request for a Google review. Sent 3 days after Email 2a.

Email 3b — Positive Follow-up (from 2b path)¶

Subject: Thanks for the feedback, {First name}!

Thanks them for positive feedback and asks for a Google review. Sent 1 hour after clicking 4-5 stars in Email 2b.

Email 3c — Last Chance¶

Subject: Last chance to share feedback, {First name}

Final request for any feedback. Sent 3 days after Email 2b to non-responders.

Airtable Configuration¶

Views¶

View	Purpose	Key Filters
Ready for feedback campaign	Contacts ready for Email 1	Eligibility criteria, no campaign started
Ready for Email 2a	Positive responders ready for Google nudge	Status = "Email 1 sent", Response = Positive, 1 hour passed
Ready for Email 2b	Non-responders ready for reminder	Status = "Email 1 sent", Response = empty, 3 days passed
Ready for Email 3a	Ready for final Google nudge	Status = "Email 2a sent", 3 days passed
Ready for Email 3b	Positive responders from 2b path	Status = "Email 2b sent", Response = Positive, 1 hour passed
Ready for Email 3c	Non-responders ready for last chance	Status = "Email 2b sent", Response = empty, 3 days passed
Ready for Complete	Ready to mark complete	Response = Positive, Google review posted
Google review flagged for manual review	Fuzzy matches needing verification	Match confidence = "Flagged for review"
Create support ticket from negative feedback	Negative responders needing ticket	Response = Negative, Support ticket created = unchecked

Common filters on all campaign views:

{Google review posted} is empty (except Complete view)
{Feedback response} is not "Negative"
Active services filter (Active, Renegotiating, or Done one-off)

Fields¶

Field	Type	Purpose
Feedback campaign status	Single select	Tracks current stage
Feedback campaign started	Date	When campaign began
Last feedback email sent	Date	When last email was sent
Feedback rating	Number	Star rating clicked (1-5)
Feedback response	Single select	Positive, Negative, or None
Feedback response date	Date	When they clicked a star
Clicked Google review link	Date	When they clicked the Google review link
Google review posted	Date	When review was detected
Google review match confidence	Single select	Auto-confirmed or Flagged for review
Rejected reviewer names	Long text	Comma-separated names to skip when matching
Review matching was correct	Checkbox	Confirms fuzzy match was correct
Support ticket created	Checkbox	Prevents duplicate ticket creation
Ready for Email 2a	Formula	Eligibility formula for 1-hour delay
Ready for Email 3a	Formula	Eligibility formula for 3-day delay
Ready for Email 3b	Formula	Eligibility formula for 1-hour delay

Status Values¶

Status	Meaning
(empty)	Not yet in campaign
Email 1 sent	Initial email sent, awaiting response
Email 2a sent	Positive follow-up sent
Email 2b sent	Reminder sent to non-responder
Email 3a sent	Final Google nudge sent
Email 3b sent	Positive follow-up (from 2b path) sent
Email 3c sent	Last chance email sent
Complete	Campaign finished

Google Review Detection¶

How Matching Works¶

Script fetches contacts who clicked the Google review link in the last 14 days
Script fetches recent reviews from Google Business Profile API
For each review, attempts to match by name:
Exact match → Auto-confirmed
Fuzzy match (Levenshtein distance ≤2 on last name) → Flagged for review
Single name match (e.g., "Adam" matches "Adam Smith") → Flagged for review
Review must be posted AFTER the contact clicked the review link

Handling Flagged Matches¶

When a fuzzy match occurs, you receive an email with:

Reviewer name (from Google)
Contact name (from Airtable)
Review posted timestamp
Link clicked timestamp
Link to the flagged matches view

If the match is CORRECT: Tick the {Review matching was correct} checkbox

If the match is INCORRECT:

Clear {Google review posted} and {Google review match confidence}
Add the reviewer name to {Rejected reviewer names}

The email¶

This is how the email looks:

Script Properties (Google Apps Script)¶

Property	Purpose
CLIENT_ID	Google OAuth client ID
CLIENT_SECRET	Google OAuth client secret
AIRTABLE_PAT	Airtable Personal Access Token
AIRTABLE_BASE_ID	Base ID (app50ZU9jqsaWnu07)
AIRTABLE_CONTACTS_TABLE_ID	Contacts table ID
FIELD_GOOGLE_REVIEW_POSTED	Field ID for review posted date
FIELD_CLICKED_GOOGLE_REVIEW_LINK	Field ID for click timestamp
FIELD_CONTACT_NAME	Field ID for contact name
FIELD_GOOGLE_REVIEW_MATCH_CONFIDENCE	Field ID for match confidence
FIELD_REJECTED_REVIEWER_NAMES	Field ID for rejected names
MATCH_WINDOW_DAYS	Days to look back for clicks (default: 14)

Support Ticket Creation¶

When a client submits negative feedback through Fillout, the system automatically creates a support ticket.

Ticket Contains¶

Comments prefixed with: "Client sent negative feedback when asked to review their onboarding experience."
Client's feedback in quotes
Whether client wants a callback
Link to the site
Contact details (name, email, phone)
Link back to contact record

Script Properties (Airtable)¶

Key input variables:

tableContactsViewId → 'Create support ticket from negative feedback' view
commentsPrefix → "Client sent negative feedback when asked to review their onboarding experience."
roleValue → "Client"

Cloudflare Worker Configuration¶

Environment Variables¶

Variable	Purpose
AIRTABLE_PAT	Personal Access Token for Airtable API
APPS_SCRIPT_URL	Google Apps Script deployment URL

Troubleshooting¶

Emails not sending¶

Check the Automation errors table for logged errors
Verify the automation is enabled and running
Check Cloudflare Worker logs (Observability tab)

Star clicks not recording¶

Check Cloudflare Worker AIRTABLE_PAT is valid
Verify field IDs are correct in worker code
Check Worker logs for Airtable API errors

Contact not appearing in expected view¶

Verify all filter conditions are met
Check formula fields are calculating correctly
Ensure contact has active services

Google reviews not being detected¶

Check Apps Script execution logs for errors
Verify OAuth is still valid (run getAuthorizationUrl() if needed)
Confirm the review appears in Google Business Profile
Note: Owner/manager reviews are filtered out by Google's API

OAuth token expired¶

If you see auth errors in the Review Detection script logs:

Open the Apps Script project
Run clearAuth()
Run getAuthorizationUrl()
Open the URL and re-authorize

Fuzzy match incorrectly matching¶

Clear {Google review posted} and {Google review match confidence}
Add the incorrect reviewer name to {Rejected reviewer names}
The script will skip that name for this contact on future runs

Files & Versions¶

File	Version	Location
feedback-campaign-processor	v2.9	Airtable automation script
feedback-worker	v3.2	Cloudflare Workers
feedback-email-sender	v1.0	Google Apps Script
review-detection	v2.9	Google Apps Script
create-support-tickets-from-negative-feedback	v1.2	Airtable automation script

Last updated: December 2025