Building a Controlled E-commerce Returns and Refund Workflow

The Business Situation

Elmstead Home Goods is a fictional 42-person e-commerce retailer selling home organization and household products. It operates one warehouse and processes approximately 220 return or replacement requests each month.

Note: This case study is provided as a representative example of the types of AI integration and digital transformation solutions Intelligex designs and delivers. Actual engagements are tailored to each client’s goals, constraints, existing systems, timeline, and available resources, so the approach, tools, and outcomes may vary.

The return process crosses several departments. Five customer care representatives receive customer requests, an operations manager handles policy exceptions, eight warehouse employees receive and inspect returned goods, three finance employees complete refunds, and two merchandising and supplier-quality employees manage supplier claims and product-quality reporting.

Shopify remains the commerce and payment system. Gmail is used for customer and internal communication. Before implementation, staff recorded some requests in a shared Google Sheet, but approval decisions, warehouse updates, supplier correspondence, and refund confirmations were primarily stored in email threads.

The process handled several distinct activities:

• Receiving the customer’s request and identifying the order and item.
• Checking the return window, quantity, product restrictions, and requested remedy.
• Authorizing or rejecting the return.
• Receiving tracking information and monitoring the inbound shipment.
• Inspecting the returned item.
• Deciding whether to restock, refurbish, quarantine, scrap, or return the item to a supplier.
• Completing a refund or creating a replacement order in Shopify.
• Following up on supplier credits.
• Reporting why items were returned and which team or external party should investigate.

For this representative implementation, the return policy uses a 30-day window from fulfillment, excludes final-sale items, and requires manager review for requests over $150. Amounts above $500 or requests involving a policy exception require sequential customer-care and finance review. These are scenario assumptions, not recommendations for another business’s return policy.

The company needed to change because email did not provide reliable ownership, due dates, status reporting, or structured root-cause data. Customers sometimes received repeated questions, warehouse work could not be matched quickly to the correct request, and finance had to search email before confirming whether a refund had been approved.

The Existing Process

The original process followed this sequence:

1. A customer emailed the general customer-care inbox with an order number and a description of the problem.
2. A customer-care representative looked up the order in Shopify and replied if the order number, SKU, quantity, or reason was missing.
3. The representative added a row to a shared Google Sheet when time allowed.
4. If approval was needed, the representative forwarded the email to a manager. The approval remained in the email thread.
5. The customer received shipping instructions by email. Tracking numbers were sometimes added to the spreadsheet and sometimes remained in Gmail.
6. The warehouse received the parcel and searched the sheet or asked customer care to identify the return.
7. Inspection results were typed into the sheet, emailed to customer care, or both.
8. Customer care asked finance to issue a refund or manually created a replacement order in Shopify.
9. Finance completed the transaction and replied to the email thread.
10. Supplier claims were managed in separate email threads. Their relationship to the original return was not consistently recorded.
11. At month end, an operations analyst manually grouped free-text reasons into broad categories.

The existing spreadsheet was not itself the main problem. The larger issue was that no workflow enforced when a row had to be created, who owned it, which transitions were valid, or what evidence was required before a refund or replacement could proceed.

What the New System Needed to Do

The team also decided that Shopify refunds, replacement creation, policy exceptions, and final root-cause confirmation would remain human-controlled. The automation would prepare the work, validate prerequisites, and record evidence without independently releasing money or making final policy decisions.

Implementation Approaches Considered

Improving the existing manual process

Adding required columns, protected ranges, and email templates would have reduced some inconsistency. It would not reliably create events, prevent invalid transitions, enforce owner changes, or isolate sensitive fields. This was considered an interim control rather than the target design.

Using Google Workspace alone

Google Forms, Sheets, Drive, Gmail, and Google Apps Script could implement the workflow. This approach would have kept the system inside the existing productivity suite. It was viable, but the company would have needed custom scripting for record locking, linked events, retries, operational views, and permissions. That maintenance burden was not preferred for the first implementation.

Using Airtable and Make

Airtable provided linked records, controlled views, formulas, interfaces, and field-level workflow structure. Make provided polling triggers, routers, filters, Gmail notifications, Drive actions, retry paths, and updates between forms and Airtable. This combination supported the tailored warehouse, finance, and supplier process without building a complete application.

Using Shopify as the primary workflow

Shopify remained authoritative for orders, refunds, and replacement orders. However, the company’s process also required warehouse inspection evidence, supplier-credit follow-up, root-cause ownership, and operational escalation. Available Shopify capabilities and compatible applications should be evaluated against the current subscription and store configuration, but the team did not want the first phase to write financial transactions automatically.

Purchasing a dedicated returns platform

A specialist returns platform could provide customer portals, labels, carrier tracking, exchanges, and storefront integration. It would be appropriate if self-service, international returns, complex exchange rules, or much higher volume became priorities. At the representative volume, the tailored Airtable workflow provided sufficient control while retaining existing tools.

The Selected Solution

The selected implementation connected Google Forms, Airtable, Make, Gmail, and Google Drive. Shopify remained the commerce system, but order verification, refund execution, and replacement creation were intentionally completed by authorized employees in Shopify during phase one.

The solution removed repeated spreadsheet updates, email-based approval storage, manual reminder lists, warehouse identification searches, and month-end recategorization of every free-text reason.

Human employees retained control over Shopify verification, approval exceptions, refund release, replacement creation, physical inspection, inventory disposition, supplier liability, and final root-cause classification.

System Architecture and Data Flow

• Intake: Public customer Return Request Form and restricted internal Google Forms.
• System of record: Airtable Returns, Events, Users, Reason Taxonomy, and Automation Runs tables.
• Automation layer: Make scenarios using polling triggers, routers, filters, and scheduled processors.
• Document storage: Restricted Google Drive folders organized by year and RMA ID.
• Notifications: Gmail messages sent from a dedicated returns mailbox.
• Reporting: Airtable views and interfaces based on live workflow records.
• AI layer: Optional classification service called by Make after inspection, with human confirmation.

1. Customer submission: A customer submits the Return Request Form. Google Forms validates required fields and records a response identifier and timestamp. Make polls for new responses every five minutes.

2. Idempotency check: Make constructs an event key from the configured form identifier and Google response identifier. It searches the Automation Runs table before processing. If the key already exists, the scenario stops without creating a second return.

3. Normalization and duplicate check: Make trims whitespace, normalizes email and SKU casing, validates quantity, and searches Airtable for an open return with the same order number, email, and SKU.

4. Return creation: If the request is new, Make creates a Returns record with Automation Status set to Pending. Airtable generates the RMA ID from its record ID.

5. Document folder and confirmation: A scheduled processor creates the Google Drive folder, assigns the customer-care queue, creates a Submitted event, and sends a Gmail confirmation containing the RMA ID. Returned identifiers include the Airtable record ID, RMA ID, Drive folder ID, and Gmail message reference when available.

6. Eligibility verification: A customer-care employee checks Shopify and submits the restricted Eligibility Review Form. Make validates the submitter against the Users table, records the event, and updates order, value, fulfillment, and eligibility fields.

7. Authorization routing: Standard eligible requests at or below $150 are authorized after human order verification. Higher-value or exceptional requests enter Pending Approval. Authorized requests receive return instructions and a ship-by date.

8. Shipment and warehouse events: Shipment, receipt, inspection, and restock submissions create immutable Events records. The event processor validates each status transition, updates the parent return, assigns the next owner, and sends the relevant notification.

9. Resolution: Finance issues an approved refund in Shopify, or customer care creates a replacement order. The employee submits the Finance or Resolution Completion Form with the external Shopify identifier. Make records the evidence and marks the customer resolution complete.

10. Disposition and supplier work: Warehouse disposition events can create restock, quarantine, scrap, refurbishment, or supplier tasks. Supplier events record claim references, credits, return shipments, and final closure.

11. Completion: A return closes only when the customer resolution is complete and the inventory disposition is complete or not required. Make sends a final confirmation and stores the completion timestamp.

12. Monitoring: Scheduled Make scenarios send reminders, escalate overdue work, retry failed records, and populate Airtable status and root-cause views. Permanent failures are moved to a dead-letter view for manual recovery.

If any destination action fails, the source return or event remains available in Airtable with its processing status, retry count, next retry time, and error message. This prevents a Gmail, Drive, or downstream update failure from making the original request disappear.

Data Structure

The first version uses one returnable SKU per RMA. If a customer returns two different SKUs from one order, the form creates two RMAs. This simplifies quantity validation, disposition, supplier responsibility, and reason reporting. A future version can add a related Return Items table without changing the Events design.

Returns table

Related tables

Events

Contains one immutable row for each submission, decision, shipment, receipt, inspection, refund, replacement, supplier update, reassignment, override, reminder, and closure. Each event links to one Return.

Users

Contains name, work email, active status, role, team, escalation manager, backup approver, and whether the user may approve exceptions.

Reason Taxonomy

Contains code, customer-facing label, internal label, default root-cause owner, active status, definition, and reporting group.

Automation Runs

Contains event key, scenario name, source response ID, related return or event, started time, completed time, result, retry count, and sanitized error details.

The relationship is one Return to many Events. Users can own many Returns and submit many Events. Each completed Return links to one final Reason Taxonomy record.

Workflow Statuses and Ownership

A record can move backward when an approver requests more information, a warehouse event identifies a quantity mismatch, or finance finds that the approved amount does not match the requested transaction. Rejection requires an authorized eligibility or approval event. Manual review is required for policy exceptions, values above the configured threshold, mismatched order information, invalid status transitions, and automation records that exhaust their retries.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create separate development and production Airtable bases. Use the same table and field names in both. Populate development with fictional orders, customers, products, and users only.

2. Create a Make team or workspace controlled by at least two administrators. Store production scenarios in a dedicated folder and restrict editing to the automation owner and backup owner.

3. Confirm that the selected Airtable and Make subscription levels provide the required record volume, automation operations, interface access, permissions, and execution history. Product packaging can change, so verify current vendor documentation rather than relying on historical plan names.

4. Create a dedicated Google Workspace mailbox such as returns@YOUR_DOMAIN. Use a licensed or otherwise supported mailbox that can authorize the Gmail connection. Do not use an address that cannot authenticate to Make.

5. Create a shared Google Drive folder called Returns Operations. Grant edit access to the warehouse lead, customer-care manager, automation account, and backup administrator. Give finance access only if refund evidence stored there requires it.

6. Create the Google Forms under an operational account rather than an employee’s personal ownership. Give the backup administrator editor access.

7. Authorize Make connections for Google Forms, Gmail, Google Drive, and Airtable. Use named connections such as PROD - Airtable Returns and PROD - Gmail Returns so test and production credentials cannot be confused.

8. In Shopify, create roles that allow customer care to view orders and create approved replacements, while finance can issue refunds. The automation does not need Shopify write credentials in phase one.

9. Create test users representing customer care, warehouse, finance, supplier quality, an approver, an unauthorized employee, and a backup approver.

10. Document permission boundaries. Public form respondents can submit requests but cannot read Airtable or Drive. Internal form respondents must authenticate with approved organizational accounts. Dashboard viewers receive only the fields necessary for their work.

Step 2: Build the Intake

Create five Google Forms. Interface labels can change, but the underlying fields, validation, ownership, and response identifiers should remain as described.

Customer Return Request Form

Do not request card numbers, passwords, identity documents, or unnecessary address information. The order is verified in Shopify by an authorized employee.

Google Forms file upload requires the respondent to sign in and places files in the form owner’s Drive. To avoid forcing public customers into that authentication path, the public form does not accept attachments. If photographs are necessary, customer care requests them by email and stores them manually in the RMA folder. The restricted warehouse form handles internal photographic evidence.

Set the confirmation message to explain that submission is not approval and that an RMA email will follow. Include the expected response interval and a warning not to send payment information by email.

Google Forms does not provide a complete public anti-abuse layer for every storefront scenario. Keep the form URL out of searchable public pages where practical, validate response volume in Make, and route suspicious repeated submissions to review. If abuse becomes material, replace the public form with an authenticated storefront or returns portal.

Eligibility Review Form

Restrict this form to organizational users and collect the submitter’s verified email. Include RMA ID, Shopify order ID, order match, email match, SKU match, fulfilled quantity, already-refunded quantity, fulfilled date, final-sale status, item value, eligibility result, and reviewer notes.

Approval Decision Form

Restrict this form to approved managers and finance approvers. Include RMA ID, decision, approved remedy, approved maximum amount, reason, and delegation note. Decisions are Approve, Reject, or Return for Information.

Warehouse Event Form

Restrict this form to the warehouse group. Use section branching based on event type:

• Received: RMA ID, received quantity, received timestamp, package condition, notes.
• Inspection Completed: RMA ID, inspected quantity, inspection grade, item condition, packaging condition, disposition, root-cause recommendation, notes, and photographs.
• Restock Completed: RMA ID, quantity restocked, inventory location, completion timestamp.
• Supplier Return Shipped: RMA ID, carrier, tracking number, shipped quantity, timestamp.

Finance and Supplier Event Forms

The Finance Completion Form captures RMA ID, transaction type, amount, Shopify refund ID or replacement order ID, completion timestamp, and notes. The Supplier Event Form captures claim submitted, supplier response, credit confirmed, supplier return authorization, supplier return shipped, or claim closed.

All internal forms display a submission confirmation stating that Make will validate the event. A form confirmation alone does not mean that the parent return was updated.

Step 3: Create the System of Record

Create the five Airtable tables described earlier. Use stable field names because Make mappings depend on them. Avoid renaming production fields without first updating and testing every scenario.

In the Returns table, configure these default values:

• Status: Submitted
• Customer Resolution Status: Not Started
• Disposition Status: Not Started
• Approval Status: Not Required
• Automation Status: Pending
• Retry Count: 0
• Reminder Count: 0

Create the RMA ID formula:

"RMA-" & DATETIME_FORMAT(CREATED_TIME(), "YYMM") & "-" & UPPER(RECORD_ID())

Create the normalized duplicate key formula:

LOWER(TRIM({Order Number})) & "|" & LOWER(TRIM({Customer Email})) & "|" & UPPER(TRIM({SKU}))

Create the request-age formula:

IF(
  {Fulfilled At},
  DATETIME_DIFF({Request Date}, {Fulfilled At}, "days")
)

Create an overdue flag:

IF(
  AND(
    {Next Action Due},
    {Status} != "Completed",
    {Status} != "Rejected",
    {Status} != "Cancelled",
    {Next Action Due} < NOW()
  ),
  1,
  0
)

Create a cycle-time formula:

IF(
  {Completed At},
  DATETIME_DIFF({Completed At}, {Created Time}, "hours"),
  DATETIME_DIFF(NOW(), {Created Time}, "hours")
)

Airtable does not provide a relational database unique constraint on the duplicate key. Make therefore searches before creation and processes form responses sequentially. The source response ID provides a second idempotency control.

Create these operational views:

• New Intake
• Eligibility Due
• Pending Approval
• Awaiting Customer Return
• Warehouse Receiving
• Inspection Due
• Refund Queue
• Replacement Queue
• Restock Queue
• Supplier Follow-up
• Needs Information
• Overdue
• Automation Failures
• Dead Letter
• Recently Completed
• AI Review, if the optional enhancement is activated

In the Events table, add Processing Status, Retry Count, Error Message, Source Response ID, Submitted By Email, Notification Sent At, and Processed At. Make creates Events as pending queue records before applying them to Returns.

Step 4: Connect the Tools

For every Google connection, authenticate through Make’s managed connection. For Airtable, use an OAuth connection or supported personal access token with the minimum scopes and base access needed. Store credentials in Make’s connection manager, not in Airtable fields or form descriptions.

Configure each Google Forms ingestion scenario to watch one specific form. Set the scenario schedule to every five minutes and enable sequential processing where available. Use a static form prefix plus the returned response ID for the event key, for example:

RETURN_REQUEST:{GOOGLE_RESPONSE_ID}
WAREHOUSE_EVENT:{GOOGLE_RESPONSE_ID}
FINANCE_EVENT:{GOOGLE_RESPONSE_ID}

When mapping a form, use each question’s stable response output rather than relying only on its visible position. If a question is replaced rather than renamed, remap the Make module and run regression tests.

Step 5: Build the Core Automation

Automation 1: Ingest a return request

• Trigger: Google Forms returns a new response during the scheduled Make poll.
• Conditions: Response ID has not been processed; required values are present; quantity is an integer from 1 through 5; email is syntactically valid.
• Actions: Create an Automation Runs row, normalize values, search open Returns by duplicate key, create a new Return when no match exists, and mark it Pending.
• Fields updated: Source Response ID, customer fields, request fields, Status, Automation Status, and Request Date.
• Notification: No message is sent until the processor has created the folder and retrieved the RMA ID.
• Exception: A duplicate request creates a Duplicate Submission event against the existing RMA and sends the existing reference instead of creating a second RMA.

The exact action order is:

1. Read the Google response.
2. Construct the event key.
3. Search Automation Runs for the event key.
4. Stop if the event key already exists with Succeeded or Processing status.
5. Create the Automation Runs row with Processing status.
6. Trim customer name, order number, SKU, and narrative.
7. Convert email to lowercase and SKU to uppercase.
8. Validate quantity and allowed select values.
9. Search active Returns for the normalized order, email, and SKU combination.
10. If a duplicate exists, create a duplicate event, send the existing RMA ID, mark the run Succeeded, and stop.
11. If no duplicate exists, create the Return with Automation Status Pending.
12. Update the Automation Runs row with the Airtable record link and Succeeded status.

Automation 2: Process a pending return

• Trigger: Scheduled Make scenario searches for Returns with Pending status or a due retry.
• Conditions: Retry Count is below 3 and no other processor has recently set the row to Processing.
• Actions: Mark Processing, retrieve the formula-generated RMA ID, create the Drive folder if absent, create a Submitted event, assign the customer-care queue, send confirmation, and mark Succeeded.
• Fields updated: RMA-related links, Owner, Status, Next Action Due, Confirmation Sent At, Last Automation Run, and Automation Status.
• Notification: Customer confirmation and customer-care assignment.
• Exception: On failure, increment Retry Count, store a sanitized error, calculate Next Retry At, and preserve any successful identifiers.

Each action is guarded. If Document Folder ID already exists, the processor does not create another folder. If Confirmation Sent At already exists, it does not send another confirmation. This makes the processor safe to run again after partial completion.

Automation 3: Ingest internal events

• Trigger: New response from an internal eligibility, approval, warehouse, finance, or supplier form.
• Conditions: Verified submitter is active, the RMA exists, the user has the required role, and the response ID is new.
• Actions: Create an Events record with Pending processing status and preserve all submitted values.
• Fields updated: Event Type, Return link, submitter, source response, event payload, and Created At.
• Notification: None until the transition is accepted.
• Exception: Unknown RMAs and unauthorized submissions create an exception log and notify the automation owner.

Automation 4: Process an event

• Trigger: Scheduled search for Events with Pending status or a due retry.
• Conditions: Event is allowed from the current Return status and required event fields are complete.
• Actions: Mark the Event Processing, update the Return, create the next assignment, send any required notification, and mark the Event Succeeded.
• Fields updated: Status, owner, due date, resolution status, disposition status, external IDs, timestamps, and evidence fields.
• Notification: Determined by event type, such as approval request, warehouse assignment, finance assignment, or customer completion.
• Exception: Invalid transitions do not overwrite the Return. The Event enters Failed or Dead Letter and the Return enters Exception when operational review is required.

Step 6: Add Approvals, Reminders, and Escalations

Configure authorization using deterministic rules after a customer-care employee has verified the order in Shopify.

• Eligible, non-final-sale requests at or below $150 can be authorized automatically after verified order review.
• Eligible requests above $150 and at or below $500 require customer-care manager approval.
• Requests above $500 require sequential customer-care manager approval followed by finance approval.
• Any policy exception requires operations-manager review regardless of value.
• A refund amount cannot exceed the approved maximum without a new approval event.
• A rejected request requires a reason selected from a controlled list plus notes.

Sequential approval is used instead of parallel approval for high-value returns because finance should see the customer-care decision before reviewing the financial remedy. Parallel informational notifications may be sent to warehouse or supplier quality, but they do not count as approval evidence.

The approval email includes the RMA ID, order number, SKU, item value, policy result, requested resolution, customer narrative, Airtable record link, and restricted Approval Decision Form link. It does not include payment details.

Make validates the captured submitter email against the Return’s assigned Approver and the active Users table. A prefilled RMA value is a convenience only. The event processor still verifies that the user is authorized for that RMA.

The reminder scenario runs each weekday morning and searches for active records whose Next Action Due has passed. It checks Last Reminder At and Reminder Count before sending. Only after Gmail succeeds does it update the reminder fields. Escalation creates an Event so that the audit history shows when and why ownership changed.

If an approver is inactive or marked unavailable, Make follows the Users table’s Backup Approver link. If neither user is available, the request enters Exception and is assigned to the operations manager. Delegation is recorded as an event rather than silently changing the approver.

Step 7: Add Documents and File Management

Create this Drive structure:

Returns Operations/
  Production/
    2026/
      RMA-2607-RECXXXXXXXXXXXXXX/
        01 Customer Evidence/
        02 Warehouse Inspection/
        03 Finance Evidence/
        04 Supplier Evidence/
  Archive/

The processor creates the year and RMA folders only when their IDs are absent. Folder names use the generated RMA ID, not the customer’s name or email. This avoids placing personal data in folder paths.

Warehouse file uploads initially arrive in the Google Forms upload folder. Make retrieves each file identifier and moves the file into 02 Warehouse Inspection. It records the final Drive URL on the Event. If the organization prefers not to move form-owned files, copy them to the RMA folder, verify the copy, and then apply the documented retention rule to the source.

Use a file naming convention such as:

{RMA_ID}_{EVENT_DATE}_{EVIDENCE_TYPE}_{SEQUENCE}.{EXTENSION}

Example:
RMA-2607-REC7K3M9P2Q4V6ABC_2026-07-18_INSPECTION_01.jpg

• Restrict shared links to authenticated organizational users.
• Use Drive version history for replaced documents rather than overwriting without evidence.
• Record file replacements as Events.
• Set file-size guidance in the form and test the actual Google Forms and Drive limits for the organization’s account.
• Require at least one inspection image only for configured conditions such as damaged, defective, wrong item, or missing parts.
• If an upload fails, leave the warehouse Event in Failed status and do not advance to refund or replacement when evidence is mandatory.
• Archive completed RMA folders according to the company’s retention policy rather than an arbitrary automation schedule.

Step 8: Add Reporting and Operational Views

Create Airtable Interfaces for customer care, warehouse, finance, supplier quality, and management. Each interface should expose only the records and fields needed by that role.

Management reporting should include:

• Monthly request volume and completed volume.
• Median and average completion time, with exceptions shown separately.
• Open work by status and owner.
• Overdue percentage by department.
• Return volume and value by SKU and product.
• Final reason code and root-cause owner.
• Disposition volume and estimated recoverable inventory value.
• Supplier claim count, claimed amount, confirmed credit, and aging.
• Refund versus replacement mix.
• Automation failures and retry success.

Airtable formula and linked-record changes are reflected in its interfaces as records update. Dashboard owners should review filter definitions monthly and after any workflow status change. A practical alert threshold is any Dead Letter record or more than five automation failures in a day, but each business should set thresholds using its own baseline.

Step 9: Add Security and Governance Controls

• Apply least-privilege access to Airtable, Google Drive, Make, Gmail, and Shopify.
• Restrict public form respondents to submission only. They must not receive record or folder links.
• Hide customer email, name, notes, and financial fields from general product-quality dashboards.
• Store credentials only in supported connection managers. Never place access tokens in Airtable, form fields, or email templates.
• Require multi-factor authentication where supported by the organization’s identity provider.
• Use an operational mailbox and operational form ownership so employee departures do not break connections.
• Review Make execution logs, Airtable Events, Google account activity, and Shopify transaction evidence as complementary audit sources.
• Remove former employees from Google groups, Airtable roles, Make teams, Drive folders, and Shopify roles through the offboarding process.
• Document retention periods for customer requests, photographs, financial evidence, and supplier claims.
• Back up the Airtable base or export key tables on a scheduled basis appropriate to business risk.
• Do not send personal data to an AI provider unless the provider, contract, region, retention settings, and company policy have been approved.
• Keep final refunds, policy exceptions, supplier liability, and final reason confirmation under human control.

Regulatory requirements vary by jurisdiction, product type, and customer location. The implementation owner should involve legal, privacy, finance, and information-security reviewers where consumer rights, payment records, product safety, or regulated goods are involved.

Step 10: Deploy and Test

1. Build the full workflow in the development Airtable base with duplicate Google Forms and a test Gmail mailbox.
2. Use at least 30 sample records covering normal, rejected, duplicate, high-value, damaged, supplier, refund, replacement, and failed-upload cases.
3. Run each Make scenario manually while inspecting every mapped input and output.
4. Confirm that development connections cannot access production customer data.
5. Ask representative users from customer care, warehouse, finance, supplier quality, and management to complete user acceptance testing.
6. Record defects with expected result, actual result, scenario execution ID, RMA ID, and corrective action.
7. Pilot the workflow with a small customer-care group and one warehouse shift for two weeks.
8. During the pilot, run the old log as a read-only reconciliation aid rather than allowing two active sources of truth.
9. Review failures daily and adjust validation, permissions, and instructions before expanding.
10. Copy the approved Airtable structure and Make scenarios to production, remap production connections, and verify every table and field identifier.
11. Activate ingestion first, then event processing, then reminders, and finally optional AI.
12. Publish a launch guide explaining the new RMA ID, ownership rules, forms, status definitions, and support contacts.
13. Maintain a rollback plan that pauses Make scenarios, preserves form responses, and temporarily routes new requests to a controlled intake queue without deleting production records.
14. Assign a named primary support owner and backup owner for the first month.

Code and Configuration

No custom code is required for the core implementation. Google Forms, Airtable, Make, Gmail, and Google Drive provide the required triggers and actions through native connectors. The implementation still requires precise formulas, filters, mappings, and error routes.

Configuration values

ENVIRONMENT=PRODUCTION
AIRTABLE_BASE_ID=YOUR_AIRTABLE_BASE_ID
RETURNS_TABLE=Returns
EVENTS_TABLE=Events
USERS_TABLE=Users
REASONS_TABLE=Reason Taxonomy
AUTOMATION_RUNS_TABLE=Automation Runs

RETURN_REQUEST_FORM_ID=YOUR_RETURN_REQUEST_FORM_ID
ELIGIBILITY_FORM_ID=YOUR_ELIGIBILITY_FORM_ID
APPROVAL_FORM_ID=YOUR_APPROVAL_FORM_ID
WAREHOUSE_FORM_ID=YOUR_WAREHOUSE_FORM_ID
FINANCE_FORM_ID=YOUR_FINANCE_FORM_ID
SUPPLIER_FORM_ID=YOUR_SUPPLIER_FORM_ID

RETURNS_DRIVE_ROOT_FOLDER_ID=YOUR_FOLDER_ID
RETURNS_MAILBOX=YOUR_EMAIL_ADDRESS
OPERATIONS_MANAGER_EMAIL=YOUR_EMAIL_ADDRESS

STANDARD_AUTO_AUTHORIZATION_LIMIT=150.00
SECOND_APPROVAL_LIMIT=500.00
MAX_RETRY_COUNT=3

These values belong in Make scenario variables, securely managed connections, or documented environment configuration. Credentials must not appear in the variables shown above.

Native Make scenario configuration

Return Request Ingest filters

Accept when:
- Response ID is present
- Customer email contains one @ symbol and a valid domain structure
- Order number is not blank
- SKU is not blank
- Quantity is an integer from 1 through 5
- Requested resolution is in the configured list
- Submitted reason is in the configured list

Duplicate when:
- An open Return has the same normalized Order Number, Customer Email, and SKU
- OR Automation Runs already contains the same form prefix and Response ID

Reject to manual review when:
- Field values cannot be normalized
- Submission rate exceeds the configured abuse threshold
- An existing completed RMA has conflicting refund or replacement evidence

Eligibility router configuration

Route 1: Needs Information
Condition:
- Order Match is false
- OR Email Match is false
- OR SKU Match is false
- OR required verification data is blank

Route 2: Rejected
Condition:
- Eligibility Result is Ineligible
- AND reviewer is authorized
- AND rejection reason is present

Route 3: Standard Authorization
Condition:
- Eligibility Result is Eligible
- AND Final Sale is false
- AND Item Value is less than or equal to 150.00
- AND Policy Exception is false

Route 4: Manager Approval
Condition:
- Eligibility Result is Eligible
- AND Item Value is greater than 150.00
- AND Item Value is less than or equal to 500.00
- AND Policy Exception is false

Route 5: Sequential Approval
Condition:
- Item Value is greater than 500.00
- OR Policy Exception is true

Gmail authorization template

Subject: Return authorized: {{RMA_ID}}

Hello {{CUSTOMER_NAME}},

Your return request has been authorized.

RMA reference: {{RMA_ID}}
Order: {{ORDER_NUMBER}}
Item: {{SKU}}
Approved resolution: {{APPROVED_RESOLUTION}}
Ship-by date: {{SHIP_BY_DATE}}

Please include the RMA reference with the parcel and follow the shipping instructions below.

{{RETURN_INSTRUCTIONS}}

Authorization does not confirm that a refund or replacement has been completed. The item may require receipt and inspection under the return policy.

For assistance, reply to this message without including payment-card information.

Elmstead Home Goods Returns Team

Test each template with long product names, missing optional values, and non-ASCII customer names. The message should never expose Airtable or Drive links to a customer.

Error route configuration

Add an error handler to every module that writes to Airtable, sends Gmail, or moves Drive files.

1. Capture the module name, scenario execution identifier, error type, and sanitized error message.
2. If the parent Return or Event exists, set Processing Status to Failed.
3. Increment Retry Count and set the next retry to 5, 15, or 45 minutes based on the current count.
4. Use Make’s incomplete execution capability where available so the original bundle is retained.
5. After the third failed attempt, set status to Dead Letter and notify the automation owner.
6. Do not include customer narratives, email addresses, or file contents in failure notifications unless operationally necessary and approved.

To test the configuration, use a development form response, inspect the Make execution details, confirm the Airtable record and Event, verify the Drive folder and Gmail message, and then resubmit the same response identifier through the scenario’s replay mechanism. The second run must not create another RMA, folder, or customer email.

Failure Handling and Operational Reliability

Idempotency operates at three levels. The form response ID prevents duplicate ingestion, the Return duplicate key prevents repeated open RMAs for the same item, and action-completion fields prevent repeated folders, messages, refunds, or replacement evidence.

The automation owner reviews Make execution history against Airtable Automation Runs. A daily reconciliation checks for form responses without a run, runs without a linked record, pending Events older than 30 minutes, completed refunds without external IDs, and active Returns without owners.

Manual recovery must preserve the audit trail. Staff should not delete failed Events or directly change a completed transaction identifier. They should add a correction or override Event explaining who changed the record, what changed, and why.

A Complete Example

A customer submits a request for order EHG-10482, SKU BIN-24-GR, quantity one, with a requested replacement. The selected reason is Defective, and the narrative says that the lid hinge separated during first use.

1. Google Forms assigns a response identifier. Make creates the event key RETURN_REQUEST:RESPONSE_ID_EXAMPLE.
2. Make finds no prior Automation Runs row and no open return with the same normalized order, email, and SKU.
3. Airtable creates the Return. Its formula produces representative RMA ID RMA-2607-REC7K3M9P2Q4V6ABC.
4. The Return Processor creates the Drive folder, assigns the customer-care queue, records a Submitted event, and sends the customer confirmation.
5. A customer-care representative checks Shopify. The order email and SKU match, one unit was fulfilled 12 days earlier, the product is not final sale, and the item value is $86.
6. The representative submits the Eligibility Review Form with the Shopify order ID and verified values. Since the item is eligible, under $150, and not exceptional, the rule authorizes the RMA.
7. Gmail sends return instructions and a ship-by date. Status changes from Authorized to Awaiting Return.
8. Customer care records the customer’s tracking number. Make creates a Shipment Recorded event, assigns the warehouse receiving queue, and changes the status to In Transit.
9. The warehouse receives the parcel and submits a Received event. The return changes to Inspection with a 24-hour due time.
10. The inspector records one defective unit, uploads two photographs, recommends quarantine, and identifies a possible supplier defect. Make moves the files into the RMA folder.
11. The customer-resolution branch changes to Replacement Pending. The disposition branch changes to Supplier Pending because the item must remain quarantined while a claim is reviewed.
12. Customer care creates replacement order EHG-10531 in Shopify and submits the completion form. Make records the replacement ID and sets Customer Resolution Status to Complete.
13. The supplier-quality coordinator submits a claim and records supplier reference SUP-7842. Seven days later, the supplier confirms credit and instructs the warehouse to include the unit in the next supplier return.
14. The warehouse records Supplier Return Shipped. Supplier quality then records Claim Closed.
15. A human reviewer confirms final reason code DEFECT and root-cause owner Supplier.
16. Because customer resolution and inventory disposition are complete, Make changes the overall status to Completed, records Completed At, and sends the final customer confirmation.

If the warehouse had submitted the wrong RMA or a refund amount above the approval, the Event Processor would have rejected the transition and left the parent Return unchanged. The event would appear in the Exception view for correction.

Implementation Cost

All amounts below are representative planning assumptions for this scenario. They are not vendor quotes or verified client costs. Current subscription terms, operation limits, taxes, exchange rates, implementation scope, and internal labor rates must be checked before approval.

An existing subscription is not costless. The calculation uses incremental cost because Google Workspace and Shopify were already required for normal operations. A business evaluating total cost of ownership should allocate an appropriate share of those subscriptions.

Estimated Time and Cost Savings

The calculation covers administrative coordination, status checks, duplicate entry, approval follow-up, reason categorization, and reconciliation. It excludes the physical time required to receive, inspect, move, and restock products because automation does not remove that warehouse work.

Current monthly labour hours: Monthly volume × current minutes per record ÷ 60

New monthly labour hours: Monthly volume × new minutes per record ÷ 60, plus exception handling and maintenance

Monthly hours recovered: Current monthly labour hours minus new monthly labour hours

Estimated monthly labour value: Monthly hours recovered × loaded hourly labour cost

Net estimated monthly value: Monthly labour value minus recurring tool costs

Estimated payback period: One-time implementation cost ÷ net estimated monthly value

Calculation

Current monthly labour hours: 220 × 32 ÷ 60 = 117.33 hours

Routine new labour hours: 220 × 10 ÷ 60 = 36.67 hours

Exception labour: 220 × 18% × 12 ÷ 60 = 7.92 hours

New monthly labour hours: 36.67 + 7.92 + 5 maintenance hours = 49.59 hours

Monthly hours recovered: 117.33 − 49.59 = 67.74 hours

Estimated monthly labour value: 67.74 × $42 = $2,845.08

Net estimated monthly value: $2,845.08 − $247 = $2,598.08

Estimated payback period: $11,840 ÷ $2,598.08 = approximately 4.56 months

Recovered time does not automatically reduce payroll. It may provide additional capacity, faster response, less overtime, fewer administrative tasks, lower dependency on individual employees, or the ability to handle more return volume without adding equivalent coordination work.

Non-financial benefits include clearer ownership, fewer follow-up emails, more consistent approval evidence, quicker identification of incomplete records, better warehouse handoffs, more reliable supplier reporting, and a more consistent customer experience.

Readers should replace the volume, handling time, exception rate, labor cost, subscription allowance, implementation hours, and maintenance time with measured figures from their own process.

Adding AI to the Automation

AI should be added only after the rule-based workflow, statuses, validation, event processing, retries, and reporting are reliable.

Normal automation already provides structured intake, duplicate checks, assignments, reminders, status changes, audit events, document links, and deterministic threshold routing. AI is not needed for these tasks.

Potential AI uses include:

• Suggesting a final reason code from the customer narrative and inspection notes.
• Summarizing a long customer description for the warehouse queue.
• Identifying missing inspection details in unstructured notes.
• Suggesting potential duplicate narratives when exact identifiers differ.
• Grouping recurring defect themes for product-quality review.
• Comparing supplier responses with the original claim summary.

Required fields, exact order matching, monetary thresholds, status transitions, permissions, and approval rules should remain deterministic. AI would add unnecessary uncertainty to those tasks.

The Recommended AI Enhancement

The recommended enhancement suggests a structured return-reason code and root-cause owner after warehouse inspection. It does not authorize the return, approve money, assign supplier liability, or finalize the classification.

• Trigger: A Return has a completed inspection, non-empty customer narrative, non-empty inspection notes, and AI Review Status set to Not Run.
• AI input: Submitted reason, product category, sanitized product description, customer narrative, inspection grade, packaging condition, item condition, and inspection notes.
• Record update: Suggested Reason Code, Suggested Root-Cause Owner, AI Summary, Confidence, Missing Information, AI Run ID, and AI Review Status.
• Human review: A product-quality employee accepts, changes, or rejects the suggestion before Final Reason Code is populated.
• Low confidence: Confidence below 0.85 enters the Manual Review view.
• Prohibited data: Customer name, email, address, order number, payment data, access credentials, and unrelated attachments.
• Failure behavior: Leave final fields unchanged, set AI Review Status to Failed, and route the record to manual categorization.

Reusable system instruction

You categorize e-commerce return evidence for operational reporting.

Use only the supplied information. Do not infer customer intent, supplier liability, fraud, product safety, or policy eligibility beyond the evidence.

Select exactly one reason_code:
DAMAGED_TRANSIT
DEFECT
WRONG_ITEM_SENT
MISSING_PARTS
NOT_AS_DESCRIBED
CHANGED_MIND
ORDERING_ERROR
LATE_DELIVERY
OTHER
UNKNOWN

Select exactly one root_cause_owner:
SUPPLIER
WAREHOUSE
CARRIER
MERCHANDISING
CUSTOMER_PREFERENCE
UNKNOWN

If the evidence is incomplete or conflicting, use UNKNOWN where appropriate and list the missing information.

Return only an object matching the required JSON schema. Keep the summary factual and no longer than 240 characters.

Reusable user prompt

Classify this inspected return.

Submitted reason: {{SUBMITTED_REASON}}
Product category: {{PRODUCT_CATEGORY}}
Product description: {{SANITIZED_PRODUCT_DESCRIPTION}}
Customer narrative: {{REASON_NARRATIVE}}
Inspection grade: {{INSPECTION_GRADE}}
Packaging condition: {{PACKAGING_CONDITION}}
Item condition: {{ITEM_CONDITION}}
Inspection notes: {{INSPECTION_NOTES}}

Structured output schema

{
  "type": "object",
  "additionalProperties": false,
  "properties": {
    "reason_code": {
      "type": "string",
      "enum": [
        "DAMAGED_TRANSIT",
        "DEFECT",
        "WRONG_ITEM_SENT",
        "MISSING_PARTS",
        "NOT_AS_DESCRIBED",
        "CHANGED_MIND",
        "ORDERING_ERROR",
        "LATE_DELIVERY",
        "OTHER",
        "UNKNOWN"
      ]
    },
    "root_cause_owner": {
      "type": "string",
      "enum": [
        "SUPPLIER",
        "WAREHOUSE",
        "CARRIER",
        "MERCHANDISING",
        "CUSTOMER_PREFERENCE",
        "UNKNOWN"
      ]
    },
    "summary": {
      "type": "string",
      "maxLength": 240
    },
    "missing_information": {
      "type": "boolean"
    },
    "missing_fields": {
      "type": "array",
      "items": {
        "type": "string",
        "enum": [
          "customer_description",
          "inspection_notes",
          "packaging_condition",
          "item_condition",
          "product_description"
        ]
      },
      "maxItems": 5
    },
    "evidence": {
      "type": "array",
      "items": {
        "type": "string",
        "maxLength": 120
      },
      "maxItems": 3
    },
    "confidence": {
      "type": "number",
      "minimum": 0,
      "maximum": 1
    }
  },
  "required": [
    "reason_code",
    "root_cause_owner",
    "summary",
    "missing_information",
    "missing_fields",
    "evidence",
    "confidence"
  ]
}

Expected response

{
  "reason_code": "DEFECT",
  "root_cause_owner": "SUPPLIER",
  "summary": "Inspection found a separated lid hinge with no visible transit damage.",
  "missing_information": false,
  "missing_fields": [],
  "evidence": [
    "Customer reported hinge separation during first use",
    "Inspector confirmed separated hinge",
    "Packaging showed no transit damage"
  ],
  "confidence": 0.92
}

Make configuration

1. Create an approved AI-provider connection in Make using YOUR_AI_API_KEY. Store the credential in the connection manager.
2. Search Airtable for inspected Returns where AI Review Status is Not Run.
3. Set AI Review Status to Processing before the request.
4. Map only the approved sanitized fields into the reusable prompt.
5. Call the provider’s response or chat action using YOUR_APPROVED_MODEL_ID, the system instruction, user prompt, and structured JSON schema. Module labels can vary by connector version.
6. Parse the returned JSON.
7. Confirm that the reason code exists and is active in Reason Taxonomy.
8. Confirm that root-cause owner is allowed, confidence is between 0 and 1, and summary length does not exceed 240 characters.
9. Write values only to AI suggestion fields.
10. Set AI Review Status to Ready for Review when validation passes.
11. For malformed output or provider failure, set AI Review Status to Failed and preserve final human-controlled fields.
12. Record provider request ID, model ID, timestamp, estimated usage, and outcome without storing unnecessary prompt content in general logs.

Benefits of the AI Enhancement

• Less time reading and condensing long customer and inspection narratives.
• More consistent suggestions against the approved reason taxonomy.
• Faster identification of incomplete inspection evidence.
• Improved handling of unstructured descriptions that do not match customer-facing dropdown labels.
• Quicker preparation of product, carrier, warehouse, and supplier trend reports.
• Better prioritization of records that need careful human review.

These benefits are specifically attributable to AI-assisted text interpretation. Queue ownership, reminders, audit history, duplicate prevention, status reporting, and financial controls come from the core rule-based automation.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The following assumptions are illustrative and should be validated during a pilot:

• 220 inspected returns per month.
• Manual categorization and summarization takes 2 minutes per return without AI.
• AI-assisted review takes 0.6 minutes per return.
• Fifteen percent require an additional 1.5-minute correction.
• Two percent experience a provider or parsing failure and require the original 2-minute manual task.
• AI usage costs $18 per month.
• Loaded labor cost remains $42 per hour.

Manual categorization time: 220 × 2 ÷ 60 = 7.33 hours

AI review time: 220 × 0.6 ÷ 60 = 2.20 hours

Correction time: 220 × 15% × 1.5 ÷ 60 = 0.83 hours

Failure fallback time: 220 × 2% × 2 ÷ 60 = 0.15 hours

AI-assisted categorization total: 2.20 + 0.83 + 0.15 = 3.18 hours

Additional capacity: 7.33 − 3.18 = 4.15 hours per month

Additional labor value: 4.15 × $42 = $174.30 per month

Net additional estimated value: $174.30 − $18 AI usage = $156.30 per month

The value depends on narrative length, taxonomy quality, correction rate, model cost, and review discipline. AI does not eliminate errors or human involvement.

Testing Checklist

Use fictional sample data before processing real customer, financial, or supplier information.

Ongoing Maintenance

The operations systems manager owns the workflow. A trained customer-care operations lead acts as backup owner. Neither ownership role should depend solely on the original implementer.

Any field rename, status addition, form-question replacement, or connection change requires regression testing. Documentation should include scenario names, field mappings, owner contacts, recovery procedures, current thresholds, and the date of the last successful end-to-end test.

When to Move to Dedicated Software

The Airtable and Make implementation does not need to be replaced simply because it is lightweight. It should be reassessed when operating requirements materially exceed its design.

• Return volume grows enough that polling, record limits, or automation operations create cost or performance concerns.
• Customers require an authenticated self-service portal with live status.
• Automatic carrier labels, tracking events, exchanges, and international routing become essential.
• Multiple warehouses require location-aware inventory and disposition rules.
• Advanced role permissions cannot be expressed safely in the current interfaces.
• Formal regulatory, product-safety, or audit requirements demand stronger controls and immutable records.
• Shopify, warehouse management, accounting, carrier, and supplier integrations become too numerous for maintainable low-code scenarios.
• Exception rates increase because the workflow has accumulated too many branches.
• Maintenance requires frequent intervention from specialist staff.
• Mobile scanning, barcode workflows, offline operation, or customer-facing document upload becomes necessary.
• Management requires a dedicated analytics warehouse or more advanced product-quality modeling.
• Vendor support, contractual uptime, or formal service commitments become mandatory.

At that stage, the company could evaluate dedicated returns-management platforms, warehouse-management extensions, service-management platforms, or a custom application backed by a relational database. The existing taxonomy, statuses, Events table, approval rules, and reporting definitions would provide useful migration requirements.

Implementation Checklist

• Confirm return policy, approval thresholds, owners, and exception rules.
• Confirm Google Forms, Airtable, Make, Gmail, Drive, and Shopify responsibilities.
• Create development and production accounts and environments.
• Assign primary and backup system owners.
• Configure role-based permissions and operational accounts.
• Build Returns, Events, Users, Reason Taxonomy, and Automation Runs tables.
• Create stable field names, formulas, statuses, defaults, and views.
• Build the public Return Request Form.
• Build restricted eligibility, approval, warehouse, finance, and supplier forms.
• Configure response validation, privacy notices, and conditional sections.
• Create the Drive folder structure and naming convention.
• Connect Forms, Airtable, Gmail, and Drive through Make.
• Document every source-to-destination field mapping.
• Build idempotent intake and processing scenarios.
• Configure duplicate checks and source response event keys.
• Configure ownership, approvals, sequential review, and delegation.
• Configure reminders, escalations, and unavailable-owner handling.
• Configure refund, replacement, restock, disposition, and supplier events.
• Build operational views, dashboards, and automation-failure queues.
• Add least-privilege access, credential controls, retention, and backup procedures.
• Configure retry counts, incomplete executions, dead-letter handling, and reconciliation.
• Test normal, duplicate, rejected, failed, unauthorized, and recovery paths.
• Run user acceptance testing with every participating department.
• Pilot the workflow before full production activation.
• Validate representative software, labor, implementation, and maintenance assumptions.
• Replace savings assumptions with measured internal figures.
• Add optional AI only after the core workflow is stable.
• Keep AI suggestions separate from final human-controlled fields.
• Schedule ongoing permission, credential, cost, quality, and integration reviews.
• Define the transaction, security, portal, reporting, and maintenance criteria that would justify dedicated software.