Customer Health Monitoring Across Billing, Support, and Usage

The Business Situation

Cedarline Software is a fictional 58-person B2B software provider with approximately 240 active customer accounts. The customer engagement function includes a department head, three account managers, a revenue operations analyst, and a marketing manager. Finance, support, and product operations supply information but do not own the customer health process.

The company uses separate billing, help desk, product usage, Google Workspace, and account-management processes. Billing status is reviewed in finance reports. Support activity remains in the help desk. Product adoption is exported from the usage system. Relationship notes are maintained by account managers in meeting notes, email, and a shared spreadsheet.

The team conducts approximately 240 structured account reviews each month. Before implementation, each review required an account manager or revenue operations analyst to locate information in several places, reconcile identifiers, judge whether the account was healthy, and decide whether follow-up was required.

Risk was often discovered during renewal preparation, after an escalation, or when a customer stopped responding. The company needed an earlier and more consistent process without hiding the reasoning behind a proprietary score.

The proposed implementation uses weekly billing, support, and usage exports covering all active accounts. At 240 accounts and three source files per week, Make processes approximately 2,880 source rows per four-week month. Those rows are consolidated into one health snapshot per account per week.

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 Existing Process

The original account-review process followed this sequence:

1. Finance exported an aged receivables report and placed it in a shared folder.
2. Support managers emailed a separate report covering open tickets and critical incidents.
3. Product operations exported active-user and adoption data from the usage system.
4. Account managers updated a spreadsheet with recent meetings, known champions, and renewal concerns.
5. The revenue operations analyst matched billing customer IDs, support organization IDs, and usage tenant IDs to account names.
6. Each account manager opened several reports and manually formed an opinion about account health.
7. Potential risks were discussed by email or during a weekly meeting.
8. If outreach was required, marketing assets and email drafts were stored in inconsistent folders.
9. Managers followed up manually when campaign drafts or recovery plans had not been reviewed.

The spreadsheet did not preserve a dependable history of how a score had been calculated. Users could overwrite values, change formulas, or apply an informal override without recording a reason or expiration date.

Content and campaign work created a second operational gap. When an account required an adoption campaign, executive outreach, renewal education, or service-recovery communication, the owner had to create folders, request reviews, send reminders, and coordinate the publishing handoff manually. The health review and the resulting engagement work were not linked.

What the New System Needed to Do

The team documented the requirements before selecting a tool. The health score had to be understandable to account owners and managers, not just to analysts.

The first model version used four weighted components:

• Usage: 35 percent
• Billing: 25 percent
• Support: 20 percent
• Relationship: 20 percent

The score ranges from 0 to 100. Scores from 80 through 100 are Healthy, 60 through 79 are Watch, and scores below 60 are At Risk. Critical rules can force an account into At Risk even when the weighted score is higher.

The critical rules are more than 30 invoice-overdue days, at least 14 consecutive zero-usage days for an active paid account, or a priority-one support issue open longer than 72 hours. These are deterministic rules, not AI judgments.

Implementation Approaches Considered

Why a Spreadsheet Was Not Selected

A spreadsheet could calculate a health score, but it would not adequately separate account records, weekly snapshots, reviews, campaigns, tasks, approvals, and automation logs. Protecting formulas would reduce some risk but would not provide a complete operational history.

Why the CRM Was Not Selected

The representative company’s CRM was designed around sales opportunities rather than post-sale operations. Adding the required objects, snapshot history, and workflow controls would have involved more administration than the initial use case justified.

Why BI Was Deferred

A BI platform would improve historical analysis, but it would not replace owner queues, approvals, reminders, manual overrides, or campaign handoffs. BI remains a possible reporting upgrade after the workflow produces stable data.

Why Dedicated Customer-Success Software Was Deferred

A specialized platform could become appropriate later. At 240 accounts, the immediate need was a transparent health model and disciplined review process, not a broad customer-success platform migration.

The Selected Solution

Cedarline Software selected Airtable as the operational system of record, Make as the automation layer, Google Drive as document storage, and Gmail as the controlled intake and notification channel.

The implementation retained the existing billing, support, usage, and Google Workspace tools. It removed manual file matching, score calculations, folder creation, routine review-task creation, reminder emails, and publishing-handoff emails.

Account owners still interpret context, decide what action to take, write or approve customer communication, propose overrides, and close recovery plans. Managers retain control over overrides and higher-risk communications.

The content and campaign automation is connected to customer health rather than operating as an unrelated marketing workflow. A Health Review can create an Engagement Campaign record. That record triggers task creation, a campaign asset folder, approval reminders, and a controlled publishing handoff.

System Architecture and Data Flow

The architecture uses scheduled files rather than direct APIs for the first release. This limits integration complexity while still automating validation, consolidation, calculation, ownership, and follow-up.

1. Source export: Billing, support, and usage systems produce CSV files using approved headers and send them to the controlled Gmail mailbox. A source-system timestamp and report date are included.
2. Email trigger: Make detects a new message from an allowlisted sender with a recognized subject prefix and CSV attachment.
3. Event validation: Make validates sender, file type, filename, attachment size, report date, and required headers. Invalid events are logged and routed to the failed-import queue.
4. Raw-file storage: Make uploads the unchanged attachment to a dated Google Drive archive. Drive returns the file ID and URL.
5. Batch creation: Make creates an Import Batch in Airtable containing the Gmail message ID, Drive file ID, source type, report date, row count, and processing status.
6. Account matching: Each row is matched to an Airtable Account using the source-specific external ID. Account names are descriptive only and are not used as the primary match key.
7. Snapshot upsert: Make constructs a Snapshot Key from Account ID and report date. It creates the snapshot if it does not exist or updates the relevant source fields if it does.
8. Health calculation: When billing, support, usage, and relationship inputs are complete, Airtable formulas calculate component scores, weighted health, critical rules, and the calculated band.
9. Account update: Make copies the latest calculated score, band, component values, model version, and calculation timestamp to the Account record.
10. Review creation: At Risk, Watch, incomplete, and certain downgraded accounts create or update a Health Review. The owner and due date are assigned according to the band and exception type.
11. Notification: Gmail sends the owner a concise notice containing the Account ID, score, key deterministic signals, review due date, and Airtable queue link.
12. Campaign branch: If the owner decides that coordinated engagement is required, an Airtable Campaign record is linked to the Health Review.
13. Asset and task creation: Make creates the campaign folder and subfolders in Google Drive, creates review tasks in Airtable, and returns the Drive folder IDs and links to the campaign.
14. Approval and handoff: Review requests and reminders are sent through Gmail. When required approvals are recorded, Make creates the publishing task and sends the handoff email. Publication remains a human action.
15. Failure path: Failed modules write an Automation Event, increment the retry count, mark the affected record, and notify the operations owner after automated retries are exhausted.

• Intake: Scheduled CSV attachments received through a restricted Gmail mailbox, plus internal Airtable campaign and review forms.
• System of record: Airtable.
• Automation layer: Make.
• Document storage: Google Drive.
• Notifications: Gmail.
• Reporting: Airtable views and interfaces, with future BI export available if required.
• AI layer: Optional account-context summarization after deterministic scoring succeeds.

Data Structure

The Airtable base contains related tables rather than one wide spreadsheet. An Account has many Signal Snapshots, Health Reviews, Campaigns, Tasks, and Approval Events.

Accounts

Signal Snapshots

Operational Records

Workflow Statuses and Ownership

A rejection sends the review back to Recovery Plan Pending or Awaiting Owner with required comments. A new critical signal can move a Monitoring review back to In Review. Automation does not close an open recovery plan merely because the latest score improves.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create a dedicated Airtable workspace and base for customer health operations. Use a subscription level that supports the required record volume, interfaces, automation access, and permission controls. Verify current vendor features before purchase.
2. Create the tables described in the data model. Restrict schema changes to the revenue operations owner and one backup administrator.
3. Create a Google Workspace shared mailbox such as YOUR_EMAIL_ADDRESS. Configure delegation for the revenue operations owner and backup owner.
4. Create a Google Drive shared drive or controlled shared folder. Do not use a folder owned by an individual employee.
5. Create an automation administrator identity for Make. Connect Airtable, Gmail, and Google Drive using approved OAuth connections or supported restricted credentials.
6. If the Make Airtable connection uses a personal access token, grant only the scopes and bases required for reading and writing this solution. Store the token in the Make connection, not in an Airtable field or scenario variable.
7. Allow the Make Google connection to read the intake mailbox, send operational messages, and create files and folders in the designated Drive location. Do not grant access to unrelated drives or mailboxes.
8. Create the Gmail labels Health/Billing, Health/Support, Health/Usage, Health/Processed, and Health/Failed.
9. Configure sender allowlists for each source export. Messages from other senders must not enter the processing route.
10. Create test users representing an account manager, manager, marketing approver, revenue operations owner, publishing owner, and unauthorized employee.
11. Create a separate test Airtable base and Drive folder. Duplicate the production schema without copying real customer data.
12. Document the company time zone, operating calendar, reminder schedule, source owners, and backup approvers.

Connector interface labels can vary as Make and the connected platforms update their interfaces. The required behavior is a scheduled or event-based Gmail trigger, Drive file creation, Airtable search and record actions, routers, filters, and Gmail send actions.

Step 2: Build the Intake

The first intake channel is the restricted Gmail mailbox. Each source system sends one CSV attachment for the report date.

Apply these validation rules before processing rows:

• The sender must match the allowlist for the source type.
• The subject must contain one recognized prefix.
• The attachment must use a .csv extension and a CSV-compatible content type.
• The planning design rejects attachments over 10 MB and routes them to manual review. This is an internal safety threshold, not a claim about platform limits.
• Every required header must exist exactly once.
• Every row must contain a report date and source identifier.
• Dates must parse in the agreed YYYY-MM-DD format.
• Counts and overdue days must be nonnegative numbers.
• Active Licensed Ratio must be between 0 and 1.
• Duplicate source identifiers within the same file must cause the batch to fail rather than silently selecting one row.

The mailbox is not a public form, which reduces spam risk. Sender validation remains necessary because message labels and subject lines alone are not security controls.

Make sends a batch confirmation to the revenue operations mailbox after a file is processed. The confirmation contains the source type, report date, row count, successful rows, exceptions, Airtable Batch ID, and Drive archive link.

The second intake channel is an internal Airtable Campaign form or interface. It contains these required fields:

• Linked Account
• Linked Health Review
• Campaign Type: Adoption, Renewal Education, Service Recovery, Executive Outreach, or Other
• Objective
• Audience
• Channel
• Owner
• Approver
• Publishing Owner
• Requested Publish Date
• Commercial or Incident Content flag
• Notes and source evidence links

The form confirmation states that folder and task creation may take several minutes and that no customer communication is authorized until approvals are complete. Access is restricted to authenticated internal users.

Step 3: Create the System of Record

Build the Airtable base with the tables listed earlier. Use stable naming conventions and avoid renaming fields after Make mappings are activated.

1. Use Account ID as the primary display field in Accounts. Import and validate the account master before any signal files are processed.
2. Use Snapshot Key as the primary field in Signal Snapshots. Format it as ACCOUNT_ID|YYYY-MM-DD.
3. Use formula-generated display identifiers for records that originate in Airtable. A Health Review can use HR- plus the Airtable record ID, a Campaign can use CAM-, and a Task can use TSK-.
4. Create single-select fields before mapping Make actions so that allowed values are controlled.
5. Create linked-record fields between Accounts, Signal Snapshots, Health Reviews, Campaigns, Tasks, and Approval Events.
6. Create Created Time and Last Modified Time fields on all operational tables.
7. Create the formula fields for component scores, completeness, critical rules, calculated band, effective band, and overdue state.
8. Create an Open Review Key formula combining Account ID, Snapshot Key, and open status. Make searches this field before creating a review.
9. Create views for automation inputs. Use filters instead of asking Make to retrieve every table row.

Recommended automation views include:

• Snapshots – Ready to Finalize: Data Complete is true and Last Finalized At is blank or older than Last Updated.
• Snapshots – Incomplete: Snapshot Date is at least 24 hours old and Data Complete is false.
• Reviews – Reminder Due: Status is open, Due At is present, and the next reminder time has passed.
• Campaigns – Setup Required: Folder ID is blank and Status is Draft.
• Campaigns – Review Notification Due: Status is Review Requested and the current review request has not been emailed.
• Campaigns – Handoff Due: Status is Approved and Handoff At is blank.
• Automation – Retry Pending: Status is Retry Pending and Next Retry At is due.

Airtable does not provide a conventional relational database unique constraint for these fields. Make must therefore search for Account IDs, Snapshot Keys, Event Keys, and open review keys before creating records. Duplicate detection is part of every create route.

Step 4: Connect the Tools

All source-record updates occur only after the destination action returns successfully. For example, Make writes the Drive File ID to the Import Batch only after Drive confirms file creation. This prevents the source record from claiming that a file exists when creation failed.

Step 5: Build the Core Automation

Automation 1: Import Signal Files

• Trigger: Gmail detects a new message that matches the intake search and contains an attachment.
• Conditions: Sender is allowlisted, subject prefix is recognized, extension is CSV, file is within the internal threshold, and required headers exist.
• Actions: Build Event Key, check Automation Events, archive the file in Drive, create Import Batch, parse rows, validate values, match Account, upsert Snapshot, and complete the batch.
• Fields updated: Source metrics, source received timestamp, Source Batch Links, Last Updated, batch counts, and batch status.
• Notification: Send a processing summary to revenue operations.
• Exception: Unmatched accounts and invalid rows create Exception records. The batch is marked Completed With Exceptions or Failed.

The exact action order is important:

1. Read the Gmail message ID and attachment metadata.
2. Construct an Event Key from message ID, attachment name, and report date.
3. Search Automation Events for the key.
4. If a completed event exists, apply the Processed label and stop.
5. If no event exists, create an event with Processing status.
6. Validate sender, subject, and file.
7. Upload the unchanged file to Google Drive.
8. Create the Import Batch and store the returned Airtable record ID.
9. Parse the CSV with the first row treated as headers.
10. Validate all required headers before iterating data rows.
11. For each row, find exactly one Account by external source ID.
12. Construct the Snapshot Key.
13. Search for the Snapshot Key.
14. Create the snapshot when absent or update only the fields owned by that source when present.
15. Record row exceptions without substituting account names.
16. Update batch totals and status.
17. Mark the event Complete only after every row has been handled.
18. Apply the Gmail Processed label and send the batch summary.

Automation 2: Finalize Health Snapshots

• Trigger: Scheduled Make scenario searches the Ready to Finalize Airtable view every 30 minutes during operating hours.
• Conditions: Data Complete is true, Account is Active, and the snapshot is newer than the Account’s last calculation.
• Actions: Read formula results, validate score range, update Account, compare prior and new bands, create or update a Health Review, and mark the snapshot finalized.
• Fields updated: Current Health Score, component scores, Calculated Band, Critical Rule, Model Version, Last Automation Run, and Last Finalized At.
• Notification: Send owner notification for At Risk, Watch, critical-rule, or material downgrade events.
• Exception: Scores outside 0 through 100, missing owners, or invalid select values create an Exception review.

Review duplicate prevention uses the Account, Snapshot, and Trigger Type. Make searches for an open review with the same combination before creating a record. If one exists, it updates that review rather than creating another.

Automation 3: Campaign Setup

• Trigger: Scheduled search of Campaigns – Setup Required.
• Conditions: Campaign has Account, owner, approver, publishing owner, objective, channel, and requested date.
• Actions: Create the Drive root folder, create four subfolders, create standard tasks, update the campaign, and email the owner.
• Fields updated: Folder ID, Folder URL, Automation Status, Setup Completed At, and linked Task records.
• Notification: Send the owner a setup confirmation with links.
• Exception: If any required field is absent, mark the campaign Incomplete and create a correction task.

The standard tasks are Brief Completion, Asset Draft, Internal Review, Approval, Publishing Handoff, and Response Follow-up. Tasks that are not yet actionable remain Blocked until the preceding status is complete.

Automation 4: Account Synchronization

• Trigger: Nightly scheduled search of active Accounts changed during the previous day.
• Conditions: External identifiers, owner, and owner email are present.
• Actions: Validate identifier uniqueness, refresh relationship recency fields, and flag accounts with missing mappings.
• Fields updated: Automation Status, Error Message, Last Automation Run, and relationship validation status.
• Notification: Daily exception digest to revenue operations.
• Exception: Duplicate external identifiers block all related source rows until corrected.

Step 6: Add Approvals, Reminders, and Escalations

Health overrides and campaign publications use separate approval rules.

Health Override Approval

1. The account owner proposes a Manual Override Band.
2. The owner must provide a reason and an expiration date no more than 30 days in the future.
3. Make changes Override Approval Status to Pending and creates an Approval Event.
4. The manager receives a Gmail message with the calculated score, calculated band, proposed band, reason, expiration date, and current signals.
5. The manager records Approved, Rejected, or More Information Required in Airtable.
6. Approved overrides affect Effective Health Band only until the expiration date.
7. Rejected overrides leave the calculated band unchanged.
8. Expired overrides stop affecting the effective band automatically, while the historical Approval Event remains.

Campaign Approval

Standard adoption and renewal-education campaigns require marketing review. Commercial-term or service-incident communications require sequential marketing and sales-leadership approval.

• Trigger: Campaign status changes to Review Requested.
• Conditions: Required draft exists in Drive, approver is available, and the campaign brief is complete.
• Actions: Create Approval Event, set Approval Requested At, send Gmail review request, and set the next reminder time.
• Fields updated: Approval stage, approval status, request timestamp, reminder count, and approval due date.
• Notification: First reminder at 24 hours; escalation at 48 hours.
• Exception: If the approver is unavailable, use the approved delegate from the owner directory. If no delegate exists, route to the department head.

Parallel approval was not selected for the first release because higher-risk content requires a clear sequence. Marketing confirms clarity and brand standards before sales leadership reviews commercial or relationship risk. If future reviews become independent, parallel branches can be added, but the campaign must not advance until all required approvals are recorded.

A rejection moves the campaign to Changes Requested and requires comments. Reassignment is allowed only to active internal users. Make records the previous owner, new owner, reason, actor, and timestamp.

The final publishing confirmation includes Campaign ID, Account ID, approved asset link, required channel, intended audience, requested date, and approval evidence. The publishing owner must record the actual publish or send date and an evidence link before changing the status to Published.

Step 7: Add Documents and File Management

Create this controlled Google Drive structure:

Customer Health/
  00 Source Archive/
    YYYY/
      MM/
        DD/
  01 Review Evidence/
    ACCOUNT_ID/
      REVIEW_ID/
  02 Campaign Assets/
    YYYY/
      CAMPAIGN_ID - ACCOUNT_ID/
        01 Brief/
        02 Drafts/
        03 Approved/
        04 Publish Evidence/
  98 Failed Imports/
  99 Archived Records/

Make creates folders using IDs rather than searching by folder name. The folder name contains stable identifiers, which avoids problems caused by account-name changes or unsupported characters.

• Root permissions are inherited from the shared drive or controlled parent folder.
• Public and anonymous links are disabled.
• Account managers receive access only to the folders required for their work.
• Raw source files are not edited after upload.
• Draft files remain in 02 Drafts.
• The approved asset is copied or moved to 03 Approved only after approval evidence exists.
• Publication screenshots, delivery reports, or final links are stored in 04 Publish Evidence.
• Airtable stores Drive file IDs and URLs, not duplicate file attachments.

Use file names such as CAMPAIGN_ID_Channel_AssetName_v01_YYYYMMDD. Google Drive version history may be used for edits to the same file, but an explicitly approved copy should remain identifiable.

If a required document is missing, the campaign cannot enter Review Requested. Duplicate documents are not deleted automatically. The owner receives a task to identify the authoritative version.

Large files should be linked rather than copied into Airtable. Failed uploads remain in Retry Pending, and the campaign does not receive a completed setup status until Drive confirms each required folder.

Retention periods must match contractual, privacy, accounting, and support obligations. The representative design archives source files after the operational retention period rather than deleting them without review.

Step 8: Add Reporting and Operational Views

Airtable views and interfaces provide operational reporting for the first release.

Dashboard metrics include account count by effective band, average score by owner, band changes, reviews overdue, data completeness, critical-rule count, overrides pending, campaign cycle time, publishing backlog, failed automation count, and median review completion time.

Airtable formulas calculate processing time from Created At to Closed At. Make writes completion timestamps rather than relying on a status label alone.

The revenue operations analyst owns data accuracy and refresh monitoring. The customer engagement head owns business interpretation. An alert is sent when any expected source batch is missing, more than 2 percent of rows are exceptions, or any failed automation remains unresolved after one business day. These are internal operating thresholds and should be adjusted after observing normal variation.

Step 9: Add Security and Governance Controls

• Grant Airtable schema access only to administrators.
• Provide account managers with the minimum interface and record access needed to review their accounts.
• Restrict override approval fields to managers.
• Restrict approval-event editing after a decision is recorded.
• Keep source credentials in managed Make connections, not in records, formulas, emails, or code blocks.
• Disable public Google Drive links and review inherited permissions.
• Do not store payment card information, authentication secrets, full support transcripts, or unnecessary personal information in the health base.
• Store aggregate support signals rather than full ticket content unless there is an approved operational need.
• Use a shared automation identity so workflows do not fail when an individual employee leaves.
• Review Airtable, Make, Gmail, and Drive activity logs according to available platform features.
• Remove former employee access promptly from all four systems.
• Document retention and deletion rules for exports, campaign assets, and review evidence.
• Export periodic backups of critical Airtable tables to the restricted archive.
• Require human approval before overrides, high-risk outreach, commercial commitments, and publication.
• Limit optional AI inputs to approved fields and sanitized summaries.

Regulatory requirements depend on the data, customers, and jurisdictions involved. The company should review contractual confidentiality, privacy, records-retention, and sector-specific obligations before processing production data.

Step 10: Deploy and Test

1. Build the Airtable base, Make scenarios, Gmail labels, and Drive structure in the test environment.
2. Create at least 20 synthetic accounts covering every health band, missing identifiers, duplicate identifiers, unavailable approvers, and campaign paths.
3. Produce synthetic billing, support, and usage files using the production header contracts.
4. Run each source import separately before testing a complete health snapshot.
5. Compare calculated scores against an independently prepared calculation sheet.
6. Test duplicate messages and repeated files to confirm idempotency.
7. Test error handlers by revoking a test connection, using invalid CSV values, and temporarily removing folder access.
8. Conduct user acceptance testing with one account manager, one manager, revenue operations, and marketing.
9. Run a two-week pilot with a limited set of non-sensitive test or selected production accounts after approval.
10. Compare the automated health output with the existing monthly review without replacing the old process during the pilot.
11. Resolve discrepancies and approve model version CHM-1.0.
12. Activate scenarios in phases: source imports, health finalization, reminders, campaign setup, then publishing handoffs.
13. Monitor every run during the first week and review daily failure digests.
14. Keep a rollback export of the account master and disable Make scenarios if systemic mapping errors appear.
15. Publish operating instructions covering review ownership, overrides, campaigns, errors, and support contacts.

Code and Configuration

The core implementation does not require a custom application or standalone script. Airtable formulas and native Make, Gmail, and Google Drive actions provide the required behavior. This reduces code maintenance, but it does not remove the need for controlled configuration, testing, logging, and ownership.

Configuration Values

Store these values in Make scenario configuration or secured connections. Replace every placeholder before deployment.

AIRTABLE_BASE_ID=YOUR_AIRTABLE_BASE_ID
AIRTABLE_ACCOUNTS_TABLE=Accounts
AIRTABLE_SNAPSHOTS_TABLE=Signal Snapshots
AIRTABLE_REVIEWS_TABLE=Health Reviews
AIRTABLE_CAMPAIGNS_TABLE=Campaigns
AIRTABLE_TASKS_TABLE=Tasks
AIRTABLE_EVENTS_TABLE=Automation Events
AIRTABLE_BATCHES_TABLE=Import Batches

HEALTH_DATA_MAILBOX=YOUR_EMAIL_ADDRESS
OPERATIONS_NOTIFICATION_EMAIL=YOUR_EMAIL_ADDRESS
DEFAULT_MANAGER_EMAIL=YOUR_EMAIL_ADDRESS

SOURCE_ARCHIVE_FOLDER_ID=YOUR_FOLDER_ID
REVIEW_EVIDENCE_FOLDER_ID=YOUR_FOLDER_ID
CAMPAIGN_ASSET_FOLDER_ID=YOUR_FOLDER_ID
FAILED_IMPORT_FOLDER_ID=YOUR_FOLDER_ID

COMPANY_TIME_ZONE=YOUR_COMPANY_TIME_ZONE
MODEL_VERSION=CHM-1.0
MAX_ATTACHMENT_SIZE_MB=10
AUTOMATED_RETRY_LIMIT=3

Airtable Identifier Formulas

Place each formula in the primary display field of the corresponding table. Airtable creates its internal record ID automatically.

Health Review ID:
"HR-" & RECORD_ID()

Campaign ID:
"CAM-" & RECORD_ID()

Task ID:
"TSK-" & RECORD_ID()

Approval Event ID:
"APR-" & RECORD_ID()

Billing Score

Create this as the Billing Score formula field in Signal Snapshots. Blank input produces a blank score so missing data is not mistaken for good health.

IF(
  {Invoice Overdue Days}=BLANK(),
  BLANK(),
  IF(
    {Invoice Overdue Days}=0,
    100,
    IF(
      {Invoice Overdue Days}<=14,
      70,
      IF(
        {Invoice Overdue Days}<=30,
        35,
        0
      )
    )
  )
)

Usage Score

The active-license ratio contributes 60 percent of the usage component. The 30-day usage trend contributes 40 percent.

IF(
  OR(
    {Active Licensed Ratio}=BLANK(),
    {Usage Trend 30d}=BLANK()
  ),
  BLANK(),
  ROUND(
    (
      IF(
        {Active Licensed Ratio}>=0.70,
        100,
        IF(
          {Active Licensed Ratio}>=0.50,
          75,
          IF(
            {Active Licensed Ratio}>=0.30,
            45,
            15
          )
        )
      ) * 0.60
    )
    +
    (
      IF(
        {Usage Trend 30d}>=0.05,
        100,
        IF(
          {Usage Trend 30d}>=-0.10,
          75,
          IF(
            {Usage Trend 30d}>=-0.25,
            45,
            15
          )
        )
      ) * 0.40
    ),
    0
  )
)

Support Score

The support score starts at 100 and subtracts controlled penalties. The formula cannot produce a result below zero.

IF(
  OR(
    {Open P1 Count}=BLANK(),
    {Open Noncritical Count}=BLANK(),
    {Oldest Open Ticket Days}=BLANK()
  ),
  BLANK(),
  MAX(
    0,
    100
    - MIN({Open P1 Count} * 35, 70)
    - MIN({Open Noncritical Count} * 5, 25)
    - IF({Oldest Open Ticket Days}>7, 20, 0)
  )
)

Relationship Score

The relationship component uses recency and champion coverage. A missing relationship review prevents finalization through the Data Complete formula.

MAX(
  0,
  IF(
    {Days Since Meaningful Contact}<=14,
    100,
    IF(
      {Days Since Meaningful Contact}<=30,
      75,
      IF(
        {Days Since Meaningful Contact}<=45,
        50,
        20
      )
    )
  )
  - IF({Champion Confirmed}, 0, 15)
)

Completeness, Critical Rule, and Health Score

Data Complete:

IF(
  AND(
    {Billing Received At},
    {Support Received At},
    {Usage Received At},
    {Relationship Reviewed At},
    {Days Since Meaningful Contact}!=BLANK()
  ),
  1,
  0
)


Critical Rule:

IF(
  OR(
    {Invoice Overdue Days}>30,
    {Zero Usage Days}>=14,
    {Oldest P1 Hours}>72
  ),
  1,
  0
)


Health Score:

IF(
  {Data Complete}=0,
  BLANK(),
  ROUND(
    ({Usage Score} * 0.35)
    + ({Billing Score} * 0.25)
    + ({Support Score} * 0.20)
    + ({Relationship Score} * 0.20),
    0
  )
)


Calculated Band:

IF(
  {Data Complete}=0,
  "Incomplete",
  IF(
    {Critical Rule}=1,
    "At Risk",
    IF(
      {Health Score}>=80,
      "Healthy",
      IF(
        {Health Score}>=60,
        "Watch",
        "At Risk"
      )
    )
  )
)

Effective Band With Approved Override

Place this formula in Accounts. The field uses the approved override only while all required conditions remain true.

IF(
  AND(
    {Override Approval Status}="Approved",
    {Manual Override Band}!="",
    {Override Reason}!="",
    {Override Expires}>=TODAY()
  ),
  {Manual Override Band},
  {Current Calculated Band}
)

Make Scenario Configuration

Configure five production scenarios. Use an error-handler route on each write action.

CHM-01 Import Signal Attachments
1. Gmail: watch matching messages.
2. Filter: sender, subject, attachment, and file rules.
3. Airtable: search Automation Events by Event Key.
4. Filter: continue only if no completed event exists.
5. Airtable: create Processing event.
6. Google Drive: upload raw attachment.
7. Airtable: create Import Batch.
8. CSV parser: parse attachment with headers.
9. Router: Billing, Support, or Usage route.
10. Iterator: process each row.
11. Airtable: search Account by source-specific external ID.
12. Filter: exactly one Account must match.
13. Airtable: search Signal Snapshot by Snapshot Key.
14. Router: create missing snapshot or update existing snapshot.
15. Airtable: update batch totals.
16. Airtable: mark event Complete.
17. Gmail: apply Processed label.
18. Gmail: send batch summary.

CHM-02 Finalize Health
1. Scheduler: every 30 minutes during operating hours.
2. Airtable: search Ready to Finalize view.
3. Iterator: process each snapshot.
4. Filter: score is between 0 and 100.
5. Airtable: read linked Account.
6. Airtable: update current account health fields.
7. Router: Healthy, Watch, At Risk, Incomplete, or Critical Rule.
8. Airtable: search for existing open review.
9. Router: update existing review or create new review.
10. Gmail: notify owner when required.
11. Airtable: mark snapshot finalized.
12. Airtable: complete Automation Event.

CHM-03 Review and Approval Reminders
1. Scheduler: hourly during operating hours.
2. Airtable: search Reminder Due views.
3. Iterator: process each review, task, override, and campaign.
4. Filter: no reminder sent for the current reminder stage.
5. Gmail: send owner or approver reminder.
6. Airtable: update Last Reminder At and Reminder Count.
7. Router: if overdue, notify manager or approved delegate.
8. Airtable: record escalation event.

CHM-04 Campaign Setup and Handoff
1. Scheduler: every 15 minutes.
2. Airtable: search Campaigns - Setup Required.
3. Validate required campaign fields.
4. Google Drive: create campaign root folder.
5. Google Drive: create four subfolders.
6. Airtable: create standard Tasks.
7. Airtable: update Campaign with returned folder IDs.
8. Gmail: send setup confirmation.
9. Airtable: search Review Notification Due.
10. Gmail: send approval request.
11. Airtable: search Handoff Due.
12. Airtable: create publishing task.
13. Gmail: send publishing handoff.
14. Airtable: update Handoff At.

CHM-05 Failure Digest and Reconciliation
1. Scheduler: every weekday morning.
2. Airtable: search Failed and Retry Pending events.
3. Airtable: search expected Import Batches for the current cycle.
4. Compare expected sources with received sources.
5. Gmail: send consolidated failure and missing-source digest.
6. Airtable: create reconciliation tasks where required.

For temporary connector failures, configure up to three automatic retries with increasing delays, subject to the connected service’s supported retry behavior. Validation failures should not be retried because the same invalid input will fail again.

Test each scenario with a synthetic message, inspect the Make execution history, confirm returned Airtable and Drive IDs, and verify that the Automation Event reaches Complete. If the run fails, inspect the module input and output bundles, connection state, filter result, Airtable field type, and Drive permissions.

Failure Handling and Operational Reliability

The Automation Events table functions as a lightweight dead-letter queue. Events that cannot be completed after automated retries remain in Failed or Retry Pending with the scenario, record ID, error category, error text, attempt count, and next action.

Idempotency is applied at two levels. The Event Key prevents the same attachment event from being completed twice. The Snapshot Key prevents duplicate account snapshots if a partially completed batch is replayed.

A daily reconciliation compares expected source batches with completed batches. It also compares active Account count with the number of complete snapshots for the report date. Staff recover a failed record by correcting the cause, changing its event status to Retry Pending, and allowing the recovery scenario to process it. Manual corrections require notes and the correcting user’s identity.

A Complete Example

On July 14, a weekly usage export arrives at the health-data mailbox with the subject prefix [USAGE-HEALTH]. The message comes from the approved usage-export sender and contains a valid CSV file.

Make constructs an Event Key from the Gmail message ID, filename, and report date. No completed event exists, so Make creates a Processing event and uploads the original file to the source archive in Google Drive.

One row contains Usage Tenant ID TEN-147, Active Licensed Ratio 0.38, Usage Trend 30d -0.32, and Zero Usage Days 2. Make matches TEN-147 to Cedarline Software’s Account ID ACC-00147.

The Snapshot Key is ACC-00147|2026-07-14. A snapshot already exists because billing and support files were processed earlier, so Make updates the usage-owned fields rather than creating another record.

The complete snapshot contains:

• Invoice Overdue Days: 0
• Active Licensed Ratio: 0.38
• Usage Trend 30d: -0.32
• Zero Usage Days: 2
• Open P1 Count: 0
• Open Noncritical Count: 5
• Oldest Open Ticket Days: 11
• Days Since Meaningful Contact: 38
• Champion Confirmed: No

The billing score is 100. The active-license component is 45 and the usage-trend component is 15, producing a rounded usage score of 33. The support score is 55 because five noncritical tickets and an old open ticket reduce the starting score. The relationship score is 35 because meaningful contact is 38 days old and no current champion is confirmed.

The weighted score is:

(100 × 0.25) + (33 × 0.35) + (55 × 0.20) + (35 × 0.20)
= 25 + 11.55 + 11 + 7
= 54.55
Rounded Health Score = 55

No critical rule is true, but the weighted score is below 60. The calculated and effective bands are At Risk.

Make updates Account ACC-00147, creates Health Review HR-rec7Q2M8V4P1K6D3Z, assigns the account manager, and sets the initial review deadline to 24 hours. Gmail sends the owner the score, component results, signal date, and queue link.

The account manager confirms that adoption has fallen following a change in the customer’s internal team. The owner records a recovery plan and creates an Adoption campaign linked to the Health Review.

Airtable generates Campaign ID CAM-rec9R5N2T7W4K8P1X. Make creates the campaign Drive folder and four subfolders, writes the returned folder IDs to Airtable, and creates the standard review tasks.

The owner uploads an adoption guide and email draft, then changes the campaign to Review Requested. Gmail notifies the marketing manager. The manager requests one wording change, which returns the campaign to Changes Requested. After revision, the manager approves the campaign.

Make creates the publishing task, records the approval event, and sends the publishing-handoff email. The account manager reviews the final message before it is sent. The publishing owner records the delivery evidence and changes the campaign to Published.

The Health Review moves to Monitoring. It does not close automatically. At the next snapshot, the owner evaluates whether usage and relationship signals have improved and decides whether the recovery plan can be closed.

Implementation Cost

All amounts below are representative planning assumptions, not vendor quotes or verified client costs. Current subscription requirements and pricing must be confirmed directly with each vendor.

The maintenance labour is included as time in the savings calculation and is not subtracted again as a tool expense. A fully internal build could reduce external implementation cost but would still require staff time, testing, documentation, and ongoing ownership.

Estimated Time and Cost Savings

The following estimates are planning assumptions. They do not represent verified client results.

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

240 × 18 ÷ 60 = 72 hours

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

Routine review:
240 × 5 ÷ 60 = 20 hours

Exception handling:
19.2 × 12 ÷ 60 = 3.84 hours

Maintenance:
4 hours

Total new monthly labour:
20 + 3.84 + 4 = 27.84 hours

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

72 - 27.84 = 44.16 hours

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

44.16 × $55 = $2,428.80

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

$2,428.80 - $258 = $2,170.80

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

$9,420 ÷ $2,170.80 = approximately 4.34 months

Recovered time does not automatically reduce payroll. It may create additional capacity for customer conversations, faster follow-up, more consistent renewal preparation, reduced overtime, or the ability to manage more accounts without adding equivalent administrative work.

Non-financial benefits include clearer ownership, fewer manual follow-ups, more consistent health classifications, better audit evidence, earlier visibility into incomplete data, controlled overrides, linked engagement activity, and more reliable operational reporting.

Readers should replace the account volume, review time, exception rate, labour rate, subscription cost, maintenance time, and implementation effort with their own measured figures.

Adding AI to the Automation

AI is optional and should be added only after identity matching, validation, scoring, reviews, reminders, and failure handling work reliably.

Most of this implementation does not require AI. Required fields identify missing values. Exact identifiers match accounts. Formulas calculate health. Thresholds identify critical conditions. Workflow rules assign owners and due dates. Permissions control approvals.

Potential AI uses include summarizing account context, grouping support themes, extracting non-sensitive topics from approved notes, and drafting internal review questions. AI should not set the health score, approve an override, decide whether an account should be retained, or send customer communication without review.

The Recommended AI Enhancement

The recommended enhancement is a concise account-context summary for Watch and At Risk reviews. It helps the owner read structured signals and approved internal notes, but it does not change the deterministic model.

• Trigger: A Health Review reaches Awaiting Owner with AI Summary Status set to Queued.
• AI input: Account ID, current and previous scores, component scores, critical-rule results, approved relationship notes, aggregate support metrics, campaign history, and missing-data indicators.
• System instruction: Summarize facts only, cite supplied evidence, identify missing information, and avoid decisions or customer-facing language.
• Expected output: Valid JSON matching the defined schema.
• Validation: Make parses JSON, checks required keys, allowed signal names, length limits, and array limits.
• Record update: Valid output is written to AI Summary, AI Key Factors, AI Questions, and AI Summary Status.
• Human review: The account owner marks Accepted, Edited, or Rejected before using it.
• Low-confidence handling: Missing or conflicting evidence produces a low-confidence value and a manual-review flag.
• Failure behavior: The review remains actionable without AI. The deterministic score and band are unchanged.

Reusable System Prompt

You summarize internal account context for a human account manager.

Use only the information provided in the user message. Do not infer facts that are not supported by the input. Do not change, recalculate, endorse, or challenge the supplied health score or health band. Do not recommend account closure, contract action, pricing, credits, legal conclusions, or customer-facing statements.

Produce concise factual observations. Separate evidence from missing information. If two fields conflict, state the conflict. Treat account names and identifiers as labels, not evidence.

Return only valid JSON matching the requested schema.

Reusable User Prompt

Summarize this account context for human review.

Account ID: {{Account ID}}
Calculated health score: {{Current Health Score}}
Calculated band: {{Current Calculated Band}}
Effective band: {{Effective Health Band}}
Model version: {{Model Version}}

Billing score: {{Billing Score}}
Invoice overdue days: {{Invoice Overdue Days}}

Usage score: {{Usage Score}}
Active licensed ratio: {{Active Licensed Ratio}}
Usage trend 30d: {{Usage Trend 30d}}
Zero usage days: {{Zero Usage Days}}

Support score: {{Support Score}}
Open P1 count: {{Open P1 Count}}
Oldest P1 hours: {{Oldest P1 Hours}}
Open noncritical count: {{Open Noncritical Count}}
Oldest open ticket days: {{Oldest Open Ticket Days}}

Relationship score: {{Relationship Score}}
Days since meaningful contact: {{Days Since Meaningful Contact}}
Champion confirmed: {{Champion Confirmed}}

Previous health score: {{Previous Health Score}}
Previous calculated band: {{Previous Calculated Band}}
Approved internal notes: {{Approved Internal Notes}}
Missing data indicators: {{Missing Data Indicators}}

Return:
1. A factual summary of no more than 120 words.
2. Up to four key factors with evidence.
3. Missing information.
4. Up to four questions for the human reviewer.
5. Confidence of high, medium, or low based only on data completeness and consistency.

Required JSON Output

{
  "account_context_summary": "Usage declined while billing remained current. Support workload and relationship recency also contributed to the supplied At Risk classification.",
  "key_factors": [
    {
      "signal": "usage",
      "observation": "Thirty-day usage declined.",
      "evidence": "Usage Trend 30d was -0.32."
    },
    {
      "signal": "relationship",
      "observation": "Recent relationship coverage is limited.",
      "evidence": "Meaningful contact was 38 days ago and no champion was confirmed."
    }
  ],
  "missing_information": [
    "No approved note explains the recent usage decline."
  ],
  "review_questions": [
    "Did the customer experience a staffing or rollout change?",
    "Who can be confirmed as the current operational champion?"
  ],
  "confidence": "medium"
}

Make must reject the response if it contains invalid JSON, an unsupported signal value, more than four factors or questions, or a summary over the approved length. It must not extract a health score from the AI output.

Prohibited AI inputs include credentials, payment card information, unnecessary personal data, complete private support transcripts, legal advice, protected employee information, and content restricted by customer contract.

Log the AI provider, model identifier, request timestamp, response status, token or usage measurement when available, validation result, human disposition, and estimated cost. Do not log secret credentials or unnecessary full prompts containing sensitive data.

Benefits of the AI Enhancement

• Reduces the time required to read several structured signal sections.
• Presents signal evidence in a consistent format.
• Highlights missing or conflicting context for human investigation.
• Produces reusable review questions without changing the score.
• Supports faster internal preparation for account-review meetings.
• Improves handling of approved unstructured relationship notes.

These are AI-specific benefits. Identity matching, score consistency, owner queues, reminders, approvals, Drive folders, and publishing handoffs come from normal automation and remain available if the AI service is disabled.

What Remains Rule-Based or Human-Controlled

• Health score and band: Airtable formulas and approved thresholds remain deterministic and reviewable.
• Critical-risk rules: Billing age, zero usage, and P1 duration remain exact comparisons.
• Manual overrides: A manager must approve the proposed band, reason, and expiration.
• Recovery plans: Account owners decide what action is appropriate based on business context.
• Commercial commitments: Discounts, credits, pricing changes, and contract terms require authorized human approval.
• Customer communication: A human reviews and approves every external message.
• Publication: The publishing owner confirms the channel, audience, approved asset, and evidence.
• Review closure: An account owner closes the review after evaluating results.
• Final account decisions: Renewal strategy, termination, legal action, and risk acceptance remain with authorized leaders.

These controls are necessary because the AI summary can omit context, misunderstand notes, or produce unsupported wording. It is an aid for reading, not a decision authority.

Estimating the Additional Value of AI

Assume AI summaries are generated only for approximately 60 Watch and At Risk reviews per month.

Base additional capacity:
60 × 3 ÷ 60 = 3.00 hours

Correction time:
60 × 15% × 2 ÷ 60 = 0.30 hours

Failure fallback time:
60 × 5% × 3 ÷ 60 = 0.15 hours

Net additional capacity:
3.00 - 0.30 - 0.15 = 2.55 hours

Additional labour value:
2.55 × $55 = $140.25

Net additional value after AI allowance:
$140.25 - $25 = $115.25 per month

The AI layer has modest expected value in this scenario because the core automation has already organized the information. Its primary benefit is faster reading and more consistent summaries, not replacement of human review.

Testing Checklist

Use synthetic or approved sample data before processing real customer information.

Ongoing Maintenance

The revenue operations analyst is the primary system owner. A trained operations manager is the backup owner. Business-rule changes require approval from the Head of Customer Engagement.

Do not change a source header, Airtable field type, select value, Drive parent folder, or Gmail sender without testing dependent scenarios. Maintain a change log showing the request, approver, implementation date, affected scenarios, test evidence, and rollback method.

When to Move to Dedicated Software

The selected implementation should not be replaced merely because a specialized product exists. It should be reviewed when operational requirements exceed the design.

• Daily or event-level signal volume approaches Airtable or automation performance limits.
• The company needs near-real-time streaming instead of scheduled files.
• Multiple business units require complex field-level and account-level permissions.
• Formal regulatory or audit requirements demand stronger immutable records.
• Source systems require many direct APIs, webhooks, and bidirectional updates.
• Customer journeys require many coordinated playbooks, channels, and lifecycle stages.
• Exception rates rise because the file-based integrations are no longer reliable.
• Maintenance consumes more time than the process saves.
• Advanced cohort, predictive, or product telemetry analysis requires a data warehouse.
• Account managers require a specialized mobile or offline application.
• Customers require a secure self-service portal.
• Multiple regions or locations require separate data residency and governance controls.
• Vendor support and contractual service commitments become mandatory.
• Security risk increases because too many operational controls depend on low-code configuration.

Possible next steps include adding a data warehouse and BI layer, moving to a dedicated customer-success platform, or creating a lightweight custom application. Airtable can remain the operational layer if it continues to meet volume, permission, audit, and maintenance requirements.

Implementation Checklist

• Confirm business goals, account population, review volume, owners, and due dates.
• Approve the billing, usage, support, and relationship signal definitions.
• Approve score weights, thresholds, critical rules, and model version.
• Select Airtable, Make, Google Drive, and Gmail based on verified current features.
• Create shared accounts, connections, folders, mailbox labels, and backup ownership.
• Define role-based permissions and remove unnecessary access.
• Build Accounts, Signal Snapshots, Health Reviews, Campaigns, Tasks, Approval Events, Import Batches, and Automation Events.
• Validate Account IDs and all external source identifiers.
• Define exact CSV headers, date formats, numeric ranges, and sender allowlists.
• Build Gmail intake and Google Drive source archiving.
• Configure Snapshot Key and Event Key duplicate prevention.
• Create component-score, health-score, critical-rule, calculated-band, and effective-band formulas.
• Build source imports and field mappings in Make.
• Build snapshot finalization and Account updates.
• Create Watch, At Risk, incomplete, exception, and failure queues.
• Configure owner assignment, due dates, reminders, and escalations.
• Configure manual override proposals, expiration, approval, and evidence.
• Build campaign intake, task creation, and Drive folder creation.
• Configure campaign review, rejection, reassignment, approval, and publishing handoff.
• Create operational views for health, ownership, overdue work, failures, overrides, and campaigns.
• Implement least-privilege access, shared-link restrictions, credential controls, retention, and backups.
• Configure retries, dead-letter records, reconciliation, and manual recovery.
• Test normal, duplicate, incomplete, unauthorized, failed, rejected, overdue, and recovered scenarios.
• Run user acceptance testing and a controlled pilot with sample data.
• Document activation, monitoring, support, rollback, and change-management procedures.
• Replace representative cost, volume, labour, exception, and savings assumptions with measured figures.
• Add optional AI summarization only after the core process is reliable.
• Validate AI JSON, restrict input data, require human review, and monitor cost and accuracy.
• Assign a primary maintenance owner and trained backup owner.
• Define the volume, security, integration, reporting, and maintenance thresholds that would justify an upgrade.