Campaign Planning and ROI Tracking with Airtable

The Business Situation

Valecrest Equipment Services is a fictional 42-person B2B equipment maintenance provider. Its four-person marketing team runs email, paid social, webinar, partner, and sales-support campaigns. A revenue operations analyst maintains HubSpot, while a finance manager reviews higher-value campaign budgets.

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 marketing department launches approximately six campaigns per month. A typical month also produces about 800 audience, lead, lifecycle, and campaign-result events that need to be associated with the correct campaign. A typical email send reaches approximately 3,000 subscribed contacts, although the audience varies by campaign.

The company already uses HubSpot for contacts and deals, Mailchimp for marketing email, Google Workspace for documents and spreadsheets, and shared folders for campaign assets. Campaign briefs, budgets, launch dates, UTM parameters, and result exports are maintained in separate files.

The process is workable at low volume, but it has become difficult to answer basic operational questions:

• Which campaigns are approved and ready to launch?
• Who owns the next action?
• Are tracking links using the correct UTM values?
• Which Mailchimp audience tag belongs to a campaign?
• How much has been spent against the approved budget?
• How many leads, opportunities, and closed-won deals were attributed to the campaign?
• Which result figures were imported, validated, or corrected?
• Which campaigns still need a post-campaign review?

The business needed a campaign operations system without replacing its CRM, email platform, or Google Workspace environment. Airtable was selected as the campaign system of record, with Zapier coordinating data movement among Airtable, HubSpot, Mailchimp, Google Sheets, and Google Drive.

The Existing Process

The original workflow followed these steps:

1. A campaign manager copied a Google Docs brief and entered the campaign objective, proposed channels, dates, and budget.
2. The campaign manager emailed the brief to the marketing director for review.
3. Budget approval was requested separately when finance involvement was required.
4. Assets were stored in a shared Google Drive folder, but file names and folder structures varied.
5. UTM parameters were created manually in a spreadsheet or copied from a previous campaign.
6. A contact list was selected in HubSpot, exported when necessary, and prepared for Mailchimp.
7. After launch, Mailchimp results, paid-channel spend, HubSpot leads, and deal revenue were copied into a reporting spreadsheet.
8. The revenue operations analyst reconciled campaign names across systems and prepared a monthly management summary.

The main problem was not the absence of data. The problem was that each tool held a different part of the process, and there was no common campaign identifier connecting those parts.

What the New System Needed to Do

The team defined the operating requirements before selecting the final configuration.

Implementation Approaches Considered

Improved spreadsheets

A better workbook could standardize campaign names, budgets, and results. It would be inexpensive to start, but row-level ownership, linked assets, approvals, contact-event deduplication, and reliable cross-tool synchronization would remain difficult.

HubSpot-centered implementation

HubSpot remains the correct authority for contacts, lifecycle stages, and deals. However, Valecrest wanted to retain Mailchimp and needed a flexible operational workspace for briefs, assets, approvals, channel plans, spend entries, and post-campaign reviews. The suitability of a HubSpot-only design would also depend on the exact campaign and workflow features available in the company’s subscription.

Airtable with native automations

Airtable can manage forms, linked records, internal notifications, and status changes. Native automations could handle much of the internal process, but Zapier offered a more practical orchestration layer for the existing HubSpot, Mailchimp, and Google Workspace connections.

Custom application

A custom application would provide stronger transaction controls, database constraints, and highly tailored reporting. At six campaigns and approximately 800 related events per month, the additional build and operational burden was not justified.

The Airtable and Zapier approach offered the most appropriate balance of implementation effort, control, integration coverage, and maintainability.

The Selected Solution

The selected implementation uses Airtable as the operational campaign system of record. It does not attempt to make Airtable authoritative for every kind of business data.

Airtable was selected because the campaign process contains related records rather than one flat list. One campaign can have several channel plans, assets, spend entries, audience synchronization records, and outcome events.

Zapier was selected because it could coordinate the existing tools without introducing a custom application runtime. Google Sheets was retained because finance and executive users were comfortable reviewing a protected tabular report and because exported Mailchimp or paid-channel results could be validated before entering the campaign record.

The new design removed manual campaign-ID reconciliation, most audience-copying work, repeated lifecycle-event entry, and repeated dashboard preparation. It retained human decisions for budget approval, audience definition, consent governance, send authorization, primary revenue attribution, and the final post-campaign assessment.

System Architecture and Data Flow

1. Campaign intake: A marketing employee submits an internal Airtable form. Airtable validates required fields, creates the record, and generates a Campaign ID. Incomplete or potentially duplicate submissions enter a review view instead of advancing.
2. Brief approval: Zapier detects a campaign awaiting approval and emails the marketing director. The director approves, rejects, or requests more information in Airtable. The last-modified user and time are retained as evidence.
3. Budget approval: If planned budget exceeds $10,000, Zapier routes the record to finance after marketing approval. Campaigns at or below the threshold bypass this gate.
4. Campaign provisioning: Once approvals and readiness checks pass, Zapier creates a Google Drive folder, stores its returned folder ID in Airtable, and sets the campaign to Approved. A failed folder operation sets Automation Status to Error.
5. Channel preparation: Campaign channel records use controlled source and medium values. Airtable generates UTM campaign names and tracking URLs. Invalid slugs or missing destination URLs block launch readiness.
6. Audience synchronization: A HubSpot contact whose campaign-tag property changes is evaluated by Zapier. Zapier confirms that the Campaign ID exists and is approved, verifies the synchronization eligibility fields, finds or creates the Mailchimp subscriber, and applies the Campaign ID as a tag. Returned subscriber information is logged in Airtable.
7. Lead processing: A HubSpot form submission containing the Campaign ID triggers Zapier. The Zap validates the campaign, records a lead event, stores the latest UTM values on the contact, and advances the lifecycle stage only when the defined form and current stage meet the rule.
8. Spend and campaign results: Mailchimp result exports and channel spend files are entered in Google Sheets. Only rows marked Validated and passing formula checks are imported into Airtable. The source row is stamped with its import time and destination record ID.
9. Revenue event: A HubSpot deal moving to Closed Won is eligible for campaign revenue only when its primary campaign property is populated and attribution is confirmed. Zapier upserts one revenue event using the deal ID as part of the idempotency key.
10. Reporting and review: Airtable rolls up spend, leads, opportunities, and attributed revenue. Zapier publishes a flattened campaign summary to Google Sheets. When the reporting window closes and data is validated, the campaign enters Review Required.

• Intake: Internal Airtable campaign request form.
• System of record: Airtable for campaign operations, with HubSpot remaining authoritative for CRM records.
• Automation layer: Zapier multi-step workflows with filters, paths, lookups, delays, and error handling.
• Document storage: Google Drive campaign folders linked from Airtable.
• Notifications: Role-based email notifications sent through a controlled Google Workspace connection.
• Reporting: Airtable operational views and a protected Google Sheets executive dashboard.
• AI layer: Optional generation of a draft campaign summary from validated aggregate data.

Data Structure

The Airtable base contains related tables rather than one campaign spreadsheet.

Campaign fields

Event and integration fields

Airtable does not provide the same database-level unique constraint as a relational database. Duplicate prevention is therefore implemented by searching for the idempotency key before creation, logging the source identifier, and maintaining a duplicate-review view.

Workflow Statuses and Ownership

A rejected campaign does not automatically return to approval. The campaign owner must correct the record, clear the rejection reason only when appropriate, and deliberately resubmit it. A campaign enters manual review when identifiers conflict, consent is uncertain, the attribution property is missing, or an imported result fails validation.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Create an Airtable workspace or dedicated base for campaign operations. Select an account level that supports the required record volume, form controls, collaborator permissions, automation history, and interfaces.
2. Confirm that HubSpot supports the custom contact and deal properties required by the design. Confirm that authorized users can maintain lists and update contact and deal records.
3. Confirm that the Mailchimp account supports the required audience, tags, user permissions, and campaign reporting process.
4. Use a Zapier account that supports the required number of multi-step workflows, filters, paths, task volume, error handling, and scheduling features.
5. Create a Google Shared drive for marketing when the company’s Google Workspace configuration supports it. Otherwise, use a folder owned by a controlled role account rather than an employee’s personal drive.
6. Create or designate a role mailbox such as YOUR_EMAIL_ADDRESS for automation alerts and operational ownership.
7. Create dedicated integration connections in Zapier. Authenticate with OAuth where the connector provides it. Avoid personal accounts that may be disabled when an employee leaves.
8. Limit the integration account to the Mailchimp audience, Airtable base, HubSpot records, and Google folders required by the process.
9. Create a development Airtable base, a test Mailchimp audience, test HubSpot contacts and deals, and a test Google Drive root folder. Do not test audience synchronization against live subscribers.
10. Document connection owners, renewal dates, recovery contacts, and the process for revoking access.

No direct API keys are required for the core implementation because Zapier’s managed application connections use each platform’s supported authentication flow. If a connector later requires an API credential, store it in the automation platform’s credential store and use placeholders such as YOUR_API_KEY in documentation.

Recommended roles

• Marketing contributors: Create and update campaign records, channels, and assets.
• Marketing director: Approve briefs and final reviews.
• Finance approver: View budgets and record approval decisions.
• Revenue operations: Administer integrations, attribution, exceptions, and reporting.
• Executive viewers: Read-only access to dashboards and approved summaries.
• Automation identity: Update integration fields without broad workspace administration.

Step 2: Build the Intake

Create an internal Airtable form connected to the Campaigns table. Restrict it to authorized employees where the account configuration supports authenticated access. Because this is an internal request form, restricting access is the primary spam-prevention control.

Configure conditional field visibility where the form supports it. For example, show Budget Justification when the amount is above the threshold and show Destination URL when a digital channel is selected. Readiness formulas must still enforce these requirements because form visibility alone is not a transaction control.

After submission, display a confirmation explaining that a Campaign ID will be assigned and that approval notification will follow. Do not include confidential campaign details in the confirmation page.

Create a Duplicate Key formula using normalized campaign name and launch date. A Zap searches open Campaigns for the same key. A suspected duplicate is marked Manual Review rather than automatically deleted.

Step 3: Create the System of Record

1. Create the eight Airtable tables described in the data model.
2. Add the Campaigns fields, including the Autonumber Sequence and Created Date fields.
3. Create the Campaign ID formula. Do not use the campaign name as the identifier because names can change.
4. Create controlled single-select fields for status, approval, priority, source system, metric type, channel, and automation state.
5. Link Campaign Channels, Assets, Spend Entries, Outcome Events, Audience Sync, Activity Log, and Sync Exceptions back to Campaigns.
6. Create numeric helper fields in Outcome Events, such as Lead Units, Opportunity Units, and Revenue Value. Each helper returns the metric value only when the event type matches.
7. Roll those helper values up to Campaigns with SUM(values).
8. Create rollups for Actual Spend, asset counts, missing required assets, and open exceptions.
9. Create the ROI, cost-per-lead, budget variance, and launch-readiness formulas.
10. Add a Last Updated field that observes operational and integration fields.
11. Create filtered views for each workflow queue and automation trigger.
12. Enable record history according to the selected Airtable account capabilities, but retain the Activity Log because history availability and retention can vary.

Naming conventions

• Campaign ID: CMP-2026-0042
• UTM campaign: cmp-2026-0042-q3-preventive-maintenance
• Campaign folder: CMP-2026-0042_Q3-Preventive-Maintenance
• Asset file: CMP-2026-0042_Email_Header_v01_2026-09-18.png
• Mailchimp tag: CMP-2026-0042
• Spend source reference: invoice-4821-line-3

Operational views

• New Intake Review
• Possible Duplicates
• Awaiting Brief Approval
• Awaiting Finance Approval
• Launch Readiness Blocked
• Campaigns Launching in Five Days
• Live Campaigns
• Awaiting Results
• Review Required
• Open Automation Errors
• Unmatched Imported Rows
• Closed This Month

Step 4: Connect the Tools

Connector event and action labels can change. Configure the underlying trigger, filter, lookup, and destination behavior described below rather than relying only on a specific interface label.

HubSpot configuration

Create organization-defined contact properties for the integration. Example internal names are:

• latest_marketing_campaign_id
• latest_utm_source
• latest_utm_medium
• latest_utm_campaign
• mailchimp_campaign_tag
• mailchimp_sync_status

Create deal properties for primary_marketing_campaign_id and campaign_attribution_confirmed. These are custom properties defined by the business, not assumed standard fields.

Create one permanent HubSpot list for Mailchimp-eligible contacts. Its criteria must reflect the organization’s approved consent and suppression policy. Do not treat a generic contact property as a substitute for legal review of email subscription requirements.

Mailchimp configuration

Use one primary audience unless a documented business or regulatory reason requires more. Create a merge field such as HSCID for the HubSpot Contact ID and use the Airtable Campaign ID as a Mailchimp tag.

The automation must not automatically change an existing unsubscribed or cleaned contact to subscribed. New subscription status may be set only when the source record contains the organization’s accepted evidence of permission.

Google Sheets configuration

Create one workbook with these protected tabs:

• Results_Staging: Validated Mailchimp, spend, and other aggregate result rows.
• Campaign_ROI: One published row per Airtable campaign.
• Lists: Controlled source-system, metric-type, channel, and currency values.
• Exceptions: Optional read-only export of open Airtable failures.

Protect formula columns and restrict the Validated field to designated reviewers.

Step 5: Build the Core Automation

Automation 1: Validate a new campaign

• Trigger: A new Airtable campaign record is created.
• Conditions: Campaign ID exists, owner is assigned, dates are valid, and the duplicate key is not already used by another open campaign.
• Actions: Search for a duplicate, set the initial status, write the validation result, and create an Activity Log record.
• Fields updated: Status, Automation Status, Last Automation Run, Error Message.
• Notification: Notify the owner if required data is missing or a duplicate is suspected.
• Exception: Send the record to Manual Review rather than deleting it.

Automation 2: Provision an approved campaign

• Trigger: The campaign enters the Approved Provisioning view.
• Conditions: Brief Approval is Approved, Finance Approval is Approved or Not Required, Drive Folder ID is blank, and readiness validation is not in error.
• Actions: Set Automation Status to Running, create the Google Drive folder, capture the returned folder ID and URL, create missing channel records, and mark provisioning Complete.
• Fields updated: Drive Folder ID, Document Link, Automation Status, Last Automation Run.
• Notification: Email the owner with the folder link and launch-preparation checklist.
• Exception: If folder creation fails, set Automation Status to Error and create a Sync Exception.

The folder ID check makes this Zap idempotent. Replaying the Zap does not create another folder when an ID is already stored.

Automation 3: Synchronize an eligible contact to Mailchimp

• Trigger: A HubSpot contact is created or the campaign-tag property changes.
• Conditions: Email is present, the contact meets the approved eligibility criteria, Mailchimp synchronization status is not Unsubscribed or Cleaned, and the Campaign ID exists in Airtable with an Approved, Scheduled, or Live status.
• Actions: Find the Airtable campaign, build an idempotency key, find the Mailchimp subscriber, add or update allowed merge fields, apply the Campaign ID tag, and upsert an Audience Sync record.
• Fields updated: HubSpot mailchimp_sync_status, Airtable Sync Status, External System ID, Last Automation Run.
• Notification: No message for normal records; send a batch exception summary to revenue operations.
• Exception: Invalid consent state, invalid email, unknown campaign, or existing suppression becomes Skipped or Manual Review.

For larger audience batches, add a queued delay so records are processed at a controlled rate. The exact interval should be adjusted to observed connector responses and current platform limits.

Automation 4: Record a HubSpot lead event

• Trigger: A new HubSpot form submission contains a Campaign ID.
• Conditions: Campaign exists, form is approved for marketing attribution, and the contact-event idempotency key does not already exist.
• Actions: Find the campaign, find or create the Outcome Event, store the latest UTM properties on the HubSpot contact, evaluate the lifecycle rule, and update the contact only if a forward movement is allowed.
• Fields updated: Latest Campaign ID, latest UTM values, lifecycle stage when allowed, event source fields, campaign rollups.
• Notification: Notify the assigned sales owner only for defined high-intent forms.
• Exception: Unknown Campaign IDs and conflicting UTM data enter the manual-review queue.

A marketing-content download does not automatically become an MQL. A demo request may move an eligible Subscriber to Lead if that is the company’s approved rule. Later qualification stages remain controlled by sales and revenue operations.

Automation 5: Record opportunity and revenue events

• Trigger: A HubSpot deal’s stage, amount, close date, or attribution fields change.
• Conditions: Primary Marketing Campaign ID is valid. Revenue requires Closed Won and Campaign Attribution Confirmed equals Yes.
• Actions: Build a deal-based idempotency key, find the existing event, create or update its current value, and refresh the campaign rollups.
• Fields updated: Deal ID, Metric Type, Metric Value, Event Time, Validation Status, Last Automation Run.
• Notification: Notify revenue operations when a Closed Won deal lacks confirmed campaign attribution.
• Exception: A reverted stage or corrected amount updates the existing event rather than creating another revenue row.

Automation 6: Import validated spend and performance rows

• Trigger: A Google Sheets staging row changes and Imported At is blank.
• Conditions: Validated equals Yes, Validation Status equals READY, Campaign ID exists, value is non-negative, and source reference is present.
• Actions: Find the campaign, build the row key, find an existing Spend Entry or Outcome Event, create or update it, and write the Airtable record ID back to the sheet.
• Fields updated: Imported At, Airtable Record ID, Error, campaign rollups.
• Notification: Send one daily summary of rows that could not be imported.
• Exception: Invalid rows stay unimported with an actionable error message.

Stamping Imported At causes the row-change trigger to run again, but the filter stops the second execution. This prevents a loop.

Automation 7: Publish the executive campaign row

• Trigger: Airtable campaign reporting fields change or a daily reconciliation schedule runs.
• Conditions: Campaign ID exists and the campaign is not a test record.
• Actions: Find the Campaign_ROI row by Campaign ID, create it if absent, update current metrics, and stamp Last Refreshed.
• Fields updated: Status, owner, dates, budget, spend, leads, opportunities, revenue, ROI, and refresh timestamp.
• Notification: None for normal updates; dashboard-write failures go to revenue operations.
• Exception: Duplicate Campaign IDs in the report are flagged and not automatically consolidated.

Automation 8: Capture integration errors

• Trigger: Zapier reports a failed workflow, or a reconciliation check finds a source record without a destination record.
• Conditions: The failure is not already open under the same workflow and source event key.
• Actions: Create or update a Sync Exception with workflow name, source ID, campaign, error message, time, and retry count.
• Fields updated: Exception Status, Retry Count, Last Failure Time, Error Message.
• Notification: Immediate alert for launch-blocking or consent-related errors; daily digest for lower-priority errors.
• Exception: If Airtable itself is unavailable, send the alert to the role mailbox and retain the failed-run identifier in Zapier.

Step 6: Add Approvals, Reminders, and Escalations

The approval process is sequential:

1. The campaign owner submits the validated brief.
2. The marketing director receives the approval request.
3. If the director approves and budget is at or below $10,000, Finance Approval becomes Not Required.
4. If the director approves and budget exceeds $10,000, the campaign moves to Awaiting Budget Approval.
5. The finance manager approves or rejects the budget.
6. Only campaigns with all required approvals can enter Approved.

Each approval field has a corresponding Last Modified By and Last Modified Time field. The automation also creates an Activity Log record containing the campaign, decision, approver, time, and any decision notes.

A rejection requires a reason. A request for more information moves the record backward to the campaign owner without clearing previous approval evidence. If material fields such as budget, objective, audience, or launch date change after approval, a formula marks Reapproval Required and the campaign returns to the appropriate gate.

A scheduled Zap runs daily, retrieves records in reminder views, and processes each result through a loop:

• Send an initial approval reminder after 48 hours.
• Escalate an unanswered approval after 96 hours.
• Alert the owner five days before launch when readiness is blocked.
• Escalate two days before launch when a critical requirement is still missing.
• Remind revenue operations five days after campaign end when results are incomplete.
• Escalate incomplete results ten days after campaign end.

If the primary approver is unavailable, an authorized administrator assigns the Delegate Approver field. The automation sends future notifications to the delegate while preserving the original approver. It does not infer availability from calendars.

Step 7: Add Documents and File Management

Create one controlled Google Drive root folder for marketing campaigns. Each approved campaign receives this structure:

Campaigns/
  2026/
    CMP-2026-0042_Q3-Preventive-Maintenance/
      01_Brief/
      02_Working-Assets/
      03_Approved-Assets/
      04_Reports/
      05_Post-Campaign-Review/

The root folder is shared with the marketing team and automation identity. Finance receives access only where budget documents require it. Executive viewers receive read-only access to approved reports, not working assets.

When an Asset record contains an Airtable attachment, a Zap can upload the file to the campaign’s Working-Assets folder immediately and store the returned Drive file ID and URL. Because temporary attachment URLs may expire, the upload must occur promptly. For files exceeding connector limits, users upload directly to Drive and paste the canonical link into the Asset record.

Use a version number in the file name. When an approved binary asset is replaced, create a new Asset record or mark the previous record Superseded. Do not rely on identical file names to indicate the current version.

• Canonical location: Google Drive, not email attachments.
• Required link: Every approved asset record must contain a Drive file ID or URL.
• Duplicate control: Campaign ID, asset type, channel, and version form the asset key.
• Missing-document handling: Required assets without an approved file block launch readiness.
• Failed upload handling: Preserve the Airtable attachment, set Asset Status to Upload Error, and create a Sync Exception.
• Retention: Apply the company’s documented marketing, contract, and privacy retention policy.
• Archiving: Closed-campaign folders become read-only for most contributors after the review period.

Step 8: Add Reporting and Operational Views

Airtable provides operational views for people doing the work. Google Sheets provides the flattened management report.

The Campaign_ROI sheet contains one row per Campaign ID. Protected formulas calculate budget variance, cost per lead, and ROI from the values published by Airtable. Pivot tables summarize volume and performance by month, campaign type, owner, and channel.

The dashboard is refreshed by event-driven updates and a daily reconciliation. Revenue operations owns the report. Suggested alert thresholds include:

• Actual spend above 110 percent of approved budget.
• Launch within two days while readiness remains blocked.
• Any unresolved high-priority automation failure.
• Results still incomplete ten days after campaign end.
• Closed Won deal with an unconfirmed primary campaign.

These thresholds are business rules, not AI decisions, and should be adjusted to the company’s operating policy.

Step 9: Add Security and Governance Controls

• Least privilege: Give contributors access only to the base, tables, folders, and CRM actions they need.
• Role-based views: Finance sees budget fields but does not need Mailchimp administration. Executive users receive read-only reporting access.
• Sensitive fields: Do not copy contact names, email addresses, deal notes, or customer details into reporting sheets when identifiers and aggregate metrics are sufficient.
• Shared links: Restrict Google Drive links to approved domains or named users. Disable public links.
• Credentials: Store OAuth connections in Zapier. Do not place tokens in Airtable, Sheets, prompts, or documentation.
• Connection ownership: Use controlled integration identities where platform policy permits.
• Activity logs: Retain approval changes, source IDs, imported timestamps, failed-run identifiers, and recovery actions.
• Former employees: Remove access from Airtable, HubSpot, Mailchimp, Zapier, and Google Workspace through the offboarding process.
• Backups: Export campaign and event tables on a documented schedule and test restoration procedures.
• Privacy: Keep campaign audience descriptions aggregate. Contact-level marketing permissions remain in the approved source systems.
• Consent: Never use the integration to bypass an unsubscribe, suppression, or legal-basis requirement.
• AI restrictions: Send only validated aggregate campaign data to an approved AI service.
• Human approval: Keep budget, send authorization, attribution, and final reporting conclusions under named human owners.

Regulatory requirements depend on industry, geography, data type, and business activity. The company should have its privacy and legal advisers validate retention, consent, cross-border processing, and AI-service terms before processing personal or regulated information.

Step 10: Deploy and Test

1. Build the Airtable base, Google workbook, Drive structure, HubSpot properties, Mailchimp test audience, and Zaps in test environments.
2. Create at least 20 sample campaigns covering low and high budgets, missing assets, duplicate names, approval rejection, future launches, and completed campaigns.
3. Create test HubSpot contacts using non-production addresses under a controlled test domain.
4. Create test deals covering open, lost, Closed Won, corrected amount, missing attribution, and confirmed attribution cases.
5. Run each Zap one action at a time and inspect every mapped value before enabling automatic execution.
6. Perform user acceptance testing with the campaign manager, marketing director, finance manager, and revenue operations analyst.
7. Pilot the process with two campaigns while maintaining the previous report as a temporary comparison.
8. Reconcile contact counts, spend, leads, revenue, and dashboard rows between source and destination systems.
9. Document defects, correct the configuration, and rerun the complete testing checklist.
10. Activate integrations in phases: campaign intake first, approvals second, audience synchronization third, event logging fourth, and reporting last.
11. Assign revenue operations as launch support owner and the marketing coordinator as backup.
12. Publish a short operating guide covering intake, approvals, errors, and manual recovery.
13. Retain exports of the old campaign spreadsheets before making them read-only.
14. Define rollback as pausing the affected Zap, preserving source events, correcting the error, and replaying only after reconciliation.

Code and Configuration

The core implementation does not require a custom application or server runtime. Airtable formulas, Google Sheets formulas, and native Zapier triggers and actions provide the required automation. This reduces code maintenance, but the formulas and workflow configuration still require version control, testing, and documentation.

Airtable Campaign ID formula

Place this formula in the Campaign ID field of the Campaigns table. It requires an Autonumber field named Sequence and a Created Time field named Created Date.

"CMP-" &
DATETIME_FORMAT({Created Date}, "YYYY") &
"-" &
RIGHT("0000" & {Sequence}, 4)

A new record should produce a value such as CMP-2026-0042. If the formula returns blank, confirm the field names and types. Gaps caused by deleted records are acceptable because IDs must not be reused.

Campaign slug validation

Place this formula in a field named Slug Validation. It returns Valid only when the slug contains lowercase letters, numbers, and single hyphens.

IF(
  REGEX_MATCH(
    {Campaign Slug},
    "^[a-z0-9]+(-[a-z0-9]+)*$"
  ),
  "Valid",
  "Invalid"
)

Test values such as q3-preventive-maintenance, Q3 Campaign, and q3--campaign. Only the first should pass.

UTM tracking URL formula

Place this formula in the Campaign Channels table. The UTM inputs must already be controlled slugs, so additional URL encoding is not required for those values.

IF(
  AND(
    {Destination URL},
    {UTM Source},
    {UTM Medium},
    {Campaign ID},
    {Campaign Slug}
  ),
  {Destination URL} &
  IF(FIND("?", {Destination URL}) > 0, "&", "?") &
  "utm_source=" & {UTM Source} &
  "&utm_medium=" & {UTM Medium} &
  "&utm_campaign=" & LOWER({Campaign ID} & "-" & {Campaign Slug}) &
  IF({UTM Content}, "&utm_content=" & {UTM Content}, "") &
  IF({UTM Term}, "&utm_term=" & {UTM Term}, ""),
  BLANK()
)

Test one destination URL without a query string and one with an existing query string. Confirm that the formula adds ? to the first and & to the second.

Launch readiness formula

Place this formula in the Campaigns table after creating the referenced approval, rollup, date, and validation fields.

IF(
  AND(
    {Brief Approval} = "Approved",
    IF(
      {Planned Budget} > 10000,
      {Finance Approval} = "Approved",
      TRUE()
    ),
    {Slug Validation} = "Valid",
    {Channel Count} > 0,
    {Missing Required Assets} = 0,
    {Open High-Priority Exceptions} = 0,
    {Planned Launch Date}
  ),
  "Ready",
  "Blocked"
)

Test budgets immediately below, equal to, and above the threshold. A budget of exactly $10,000 does not require finance approval under this rule. Change the comparison if company policy differs.

ROI and cost-per-lead formulas

Place these formulas in the Campaigns table and format the first as a percentage and the second as currency.

IF(
  {Actual Spend} > 0,
  ({Attributed Revenue} - {Actual Spend}) / {Actual Spend},
  BLANK()
)

IF(
  {Leads} > 0,
  {Actual Spend} / {Leads},
  BLANK()
)

The ROI formula uses attributed revenue minus recorded spend, divided by recorded spend. It does not prove that the campaign caused the revenue. Attribution depends on the documented campaign-assignment rule.

Google Sheets staging configuration

Create the Results_Staging columns in this exact order:

A: Row Key
B: Campaign ID
C: Metric Date
D: Source System
E: Metric Type
F: Channel
G: Metric Value
H: Currency
I: External ID
J: Validated
K: Validation Status
L: Imported At
M: Airtable Record ID
N: Error

Place this row-key formula in A2 and fill it down:

=LOWER(B2&"|"&D2&"|"&E2&"|"&I2)

Place this validation formula in K2 and fill it down:

=IF(
  AND(
    REGEXMATCH(B2,"^CMP-[0-9]{4}-[0-9]{4}$"),
    ISNUMBER(C2),
    D2<>"",
    E2<>"",
    ISNUMBER(G2),
    G2>=0,
    I2<>""
  ),
  "READY",
  "ERROR"
)

Protect columns A, K, L, M, and N. Use data validation for Source System, Metric Type, Channel, Currency, and Validated. The Zap triggers only when J equals Yes, K equals READY, and L is blank.

Canonical event payload

The following JSON represents the normalized data mapped inside Zapier before an Airtable Outcome Event is created or updated. It is a mapping contract, not a public webhook endpoint.

{
  "idempotency_key": "hs:1048821:CMP-2026-0042:lead",
  "campaign_id": "CMP-2026-0042",
  "source_system": "HubSpot",
  "external_system_id": "1048821",
  "metric_type": "Lead",
  "metric_value": 1,
  "event_time": "2026-09-18T14:30:00Z",
  "contact_id": "1048821",
  "deal_id": null,
  "utm_source": "mailchimp",
  "utm_medium": "email",
  "utm_campaign": "cmp-2026-0042-q3-preventive-maintenance",
  "validated": true
}

Each Zap should produce these fields before the Airtable lookup. Required fields are idempotency_key, campaign_id, source_system, external_system_id, metric_type, metric_value, event_time, and validated.

Native Zap configuration pattern

1. Configure the source event and test it with a sample record.
2. Add a formatting step to trim IDs, normalize email case, and convert dates to one documented time zone.
3. Add a filter for required fields and eligible statuses.
4. Find the Airtable Campaign by exact Campaign ID.
5. Stop and create an exception when zero or multiple campaigns are found.
6. Build the idempotency key from immutable source identifiers.
7. Search the destination table for that key.
8. Use paths to create when absent and update when present.
9. Store returned destination IDs in the source or integration-log record.
10. Set the success timestamp only after all required destination actions complete.
11. Configure failure notification or a separate failed-run workflow.
12. Test create, update, duplicate, invalid, and replay scenarios before activation.

Likely errors include expired OAuth connections, renamed Airtable fields, deleted Google Sheets columns, invalid campaign IDs, inaccessible Drive folders, and suppressed Mailchimp contacts. Inspect Zapier task history, Airtable Sync Exceptions, and the source record before replaying a failed event.

Failure Handling and Operational Reliability

Every external event needs an idempotency key. A retry must first search the destination using that key. This protects against duplicate events caused by webhook delivery, source edits, timeouts, user replay, or a successful destination action followed by a failed source update.

Zapier may retry some transient connector failures depending on the response and account features. The implementation must not rely solely on automatic retry. The daily reconciliation compares:

• Validated Google Sheets rows against Airtable destination IDs.
• Approved campaigns against Drive Folder IDs.
• Audience Sync records against Mailchimp result status.
• HubSpot Closed Won deals with confirmed attribution against Airtable revenue events.
• Airtable campaigns against Campaign_ROI sheet rows.

Open Sync Exceptions act as the dead-letter queue. Each exception includes the failed workflow, source ID, campaign, error, retry count, owner, and resolution notes. After correction, the operator replays the source event or runs the designated recovery workflow, verifies the returned ID, and marks the exception Resolved.

A Complete Example

The following walkthrough uses sample data for Valecrest Equipment Services. It is an implementation example, not a verified business result.

1. The campaign manager submits a brief named Q3 Preventive Maintenance Webinar with a planned launch date of September 18, 2026, a planned budget of $12,500, and email, paid social, and webinar channels.
2. Airtable creates record recEXAMPLE0042 and generates Campaign ID CMP-2026-0042.
3. The entered slug is q3-preventive-maintenance. Slug Validation returns Valid.
4. The marketing director approves the brief. Because the budget exceeds $10,000, the campaign moves to Awaiting Budget Approval.
5. The finance manager approves the budget. The Activity Log records the decision, user, and time.
6. Zapier creates the Drive folder CMP-2026-0042_Q3-Preventive-Maintenance and stores its returned folder ID in Airtable.
7. The email channel record uses source mailchimp, medium email, and destination https://www.company-domain.example/webinar.
8. Airtable generates https://www.company-domain.example/webinar?utm_source=mailchimp&utm_medium=email&utm_campaign=cmp-2026-0042-q3-preventive-maintenance.
9. A HubSpot contact with example ID 1048821 becomes eligible and receives CMP-2026-0042 in the approved campaign-tag property.
10. Zapier confirms the Airtable campaign is Approved, finds the subscriber in Mailchimp, applies the tag, and writes an Audience Sync record. The idempotency key is aud:1048821:CMP-2026-0042.
11. The contact submits a demo-request form containing the Campaign ID and UTM values. Zapier creates one Lead event with key hs:1048821:CMP-2026-0042:lead.
12. The contact’s current lifecycle value is eligible to advance under the approved high-intent rule, so HubSpot moves it forward to the organization’s Lead stage. The automation does not assign MQL or SQL status.
13. After the campaign, the coordinator imports a Mailchimp result row with external campaign ID mc-example-8842. The sample validated metrics include 2,800 delivered messages and 148 clicks.
14. Paid-channel and vendor spend rows are imported through Google Sheets. One duplicate invoice-line reference is detected, so the existing Spend Entry is updated rather than duplicated.
15. The validated sample actual spend totals $9,200. Airtable rolls up 42 unique lead events.
16. A HubSpot deal with example ID 781245 later moves to Closed Won with an amount of $18,000. Revenue operations confirms CMP-2026-0042 as the primary campaign.
17. Zapier creates or updates the revenue event using hsdeal:781245:CMP-2026-0042:closedwon.
18. Airtable calculates sample ROI as ($18,000 - $9,200) ÷ $9,200 = 95.7%.
19. Zapier updates the Campaign_ROI sheet. The campaign enters Review Required after metrics are validated.
20. The campaign owner writes the post-campaign assessment, the director approves it, and the record moves to Closed.

If the deal amount is later corrected, the existing deal event is updated. If attribution is removed or the deal is no longer Closed Won, the event is marked invalid or its value is corrected according to the reconciliation rule, preventing stale revenue from remaining in the campaign total.

Implementation Cost

All amounts below are representative planning assumptions, not vendor quotes or verified client costs. Software pricing, plan features, taxes, currency, usage, and implementation rates must be confirmed directly for the selected accounts.

The $0 incremental entries do not mean those platforms are free. They indicate that the representative business already pays for them and that the implementation assumes no subscription change. The full cost should be included if a business is acquiring those tools specifically for this system.

Estimated Time and Cost Savings

The estimate separates campaign administration from lead and outcome-event reconciliation.

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

Campaign administration: 6 × 240 ÷ 60 = 24 hours

Event reconciliation: 800 × 1.5 ÷ 60 = 20 hours

Total current monthly labour hours: 24 + 20 = 44 hours

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

New campaign administration: 6 × 75 ÷ 60 = 7.5 hours

Routine event spot checks: 800 × 0.1 ÷ 60 = 1.33 hours

Exception handling: 800 × 4% × 5 ÷ 60 = 2.67 hours

Maintenance: 4 hours

Total new monthly labour hours: 7.5 + 1.33 + 2.67 + 4 = 15.5 hours

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

Monthly hours recovered: 44 – 15.5 = 28.5 hours

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

Estimated monthly labour value: 28.5 × $45 = $1,282.50

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

Net estimated monthly value: $1,282.50 – $225 = $1,057.50

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

Estimated payback period: $10,710 ÷ $1,057.50 = approximately 10.1 months

The maintenance labour is included in the new monthly labour calculation, so it is not subtracted again as a separate cash cost in the net-value formula.

Recovered time does not automatically reduce payroll. It may instead provide additional campaign capacity, quicker launches, reduced overtime, fewer administrative tasks, more time for analysis, and less dependency on one employee.

Non-financial benefits include:

• Clearer campaign ownership.
• Consistent UTM values and campaign identifiers.
• Fewer email approval follow-ups.
• Better visibility into incomplete work.
• More consistent audience synchronization.
• Improved consent and suppression controls.
• Stronger attribution evidence.
• Faster management reporting.
• Traceable source IDs and import history.
• A defined process for recovering failed records.

Readers should replace campaign volume, event volume, handling time, exception rate, loaded labour cost, subscription cost, implementation labour, and maintenance requirements with their own measured figures.

Adding AI to the Automation

AI should be added only after campaign identifiers, validation, attribution, spend imports, and reporting are reliable. It should not be used to compensate for missing required fields or inconsistent source data.

Useful AI applications in this process include:

• Drafting a post-campaign performance summary from validated aggregate metrics.
• Summarizing approved campaign briefs for internal handoff.
• Classifying unstructured campaign requests into suggested campaign types.
• Identifying likely missing context in long-form briefs.
• Comparing approved messaging with final asset text for human review.
• Supporting semantic search across approved post-campaign reviews.

Required fields, UTM construction, budget thresholds, exact duplicate matching, consent rules, ROI formulas, campaign status, and approval routing should remain deterministic. Normal automation provides the central records, ownership, synchronization, rollups, and reporting. AI adds value only when it works with unstructured language, such as preparing a draft narrative.

The Recommended AI Enhancement

The recommended enhancement generates a draft performance summary when a campaign enters Review Required and all required metrics are validated.

• Trigger: Airtable campaign status becomes Review Required, Metrics Validated equals Yes, and AI Summary Status is blank.
• AI input: Campaign objective, approved audience description, dates, channels, budget, spend, leads, opportunities, attributed revenue, ROI, email metrics, data-quality notes, and prior approved benchmarks when available.
• System instruction: Use only supplied values, identify missing data, avoid unsupported causal claims, and return structured JSON.
• Expected output: A concise headline, summary, performance observations, caveats, follow-up questions, and missing-field list.
• Validation: Confirm valid JSON, exact Campaign ID, permitted keys, expected arrays, and maximum field lengths.
• Record update: Write the output to AI Draft fields, not the approved Post-Campaign Summary field.
• Human review: The campaign owner edits the draft and the marketing director approves the final summary.
• Low-confidence handling: Missing or conflicting data changes AI Summary Status to Manual Review.
• Prohibited data: Contact names, email addresses, individual-level behavior, customer notes, contract terms, and sensitive deal details.
• Logging: Record generation time, provider, model identifier when exposed, input campaign version, validation result, and reviewer.
• Cost monitoring: Track monthly request count and provider-reported usage where available.
• Failure behavior: Leave the approved summary blank and assign the campaign owner a manual drafting task.

Reusable system instruction

You are a marketing performance analyst preparing a draft campaign review.

Use only the validated campaign data supplied by the user. Do not invent metrics, causes, benchmarks, customer reactions, or business outcomes. Do not claim that correlation proves causation. If a required value is missing or conflicting, identify it explicitly.

Treat attributed revenue as an accounting and reporting convention, not proof that the campaign caused the sale. Do not make budget, personnel, customer, or legal decisions.

Return valid JSON matching the supplied schema. Do not include text outside the JSON.

Reusable user prompt

Prepare a draft performance summary for the campaign data below.

Requirements:
1. Keep the headline under 120 characters.
2. Keep the summary under 700 characters.
3. Provide no more than five performance points.
4. Provide no more than five caveats.
5. Provide no more than four recommended follow-up questions.
6. Use only supplied numbers.
7. Set requires_human_review to true.
8. Set data_quality to complete, incomplete, or conflict.
9. Do not expose or infer personal information.

Validated campaign data:
{{CAMPAIGN_JSON}}

Return JSON using the required schema.

Required structured output

{
  "campaign_id": "CMP-2026-0042",
  "data_quality": "complete",
  "headline": "Webinar campaign generated qualified interest within budget",
  "summary": "The campaign recorded validated spend, lead, and attributed revenue data. Results should be interpreted under the confirmed primary-campaign attribution rule.",
  "performance_points": [
    "Actual spend remained below the approved budget.",
    "The campaign recorded 42 validated lead events."
  ],
  "caveats": [
    "Attributed revenue does not establish that the campaign caused the sale."
  ],
  "recommended_follow_ups": [
    "Review lead-to-opportunity conversion with sales."
  ],
  "missing_fields": [],
  "requires_human_review": true
}

Zapier configuration

1. Trigger on the Airtable view containing AI-ready campaigns.
2. Find the full Campaign record and linked validated rollups.
3. Construct a JSON input containing only approved aggregate fields.
4. Send the system instruction and user prompt to the organization’s approved AI action or provider connection.
5. Request structured output when the selected action supports it.
6. Parse the returned JSON.
7. Confirm that campaign_id exactly matches the Airtable Campaign ID.
8. Confirm that requires_human_review is true and that all expected keys exist.
9. Reject output containing unapproved fields or excessive length.
10. Write valid output to AI Draft Headline, AI Draft Summary, AI Caveats, and AI Follow-Ups.
11. Set AI Summary Status to Ready for Human Review.
12. For malformed output, create a Sync Exception and set AI Summary Status to Manual Draft Required.

The AI action requires an approved provider connection and any required provider credentials. Credentials remain in the automation platform’s secure connection store. Test the workflow with synthetic aggregate campaign data before enabling it for real campaigns.

Benefits of the AI Enhancement

The core automation already creates consistent IDs, validated data, approvals, synchronization, calculations, dashboards, and audit records. The AI enhancement does not create those benefits.

AI-specific benefits are narrower:

• Less time spent converting metrics into a first narrative draft.
• More consistent inclusion of data-quality caveats.
• Faster identification of missing context for the review meeting.
• A standard summary structure across campaigns.
• Faster preparation of follow-up questions for marketing and sales.
• Improved handling of campaign objectives and review notes that are not naturally represented by formulas.

The draft still requires human review because source data can be technically valid but incomplete, attribution can be disputed, and the business context may not be visible in aggregate metrics.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

The estimate assumes six post-campaign reviews per month.

Without AI: 6 × 30 minutes = 180 minutes, or 3 hours.

Normal AI review: 6 × 10 minutes = 60 minutes.

Expected correction time: 6 × 25% × 5 minutes = 7.5 minutes.

Expected failure fallback: 6 × 3% × 20 minutes = 3.6 minutes.

Total with AI: 60 + 7.5 + 3.6 = 71.1 minutes, or approximately 1.19 hours.

Additional monthly capacity: 3 – 1.19 = approximately 1.81 hours.

Additional labour value: 1.81 × $45 = approximately $81.45.

Net additional monthly value: $81.45 – $8 = approximately $73.45.

This value should not be added to the core estimate if summary-writing time is already included in the measured campaign-handling baseline. AI does not eliminate review, corrections, or service failures.

Testing Checklist

Use synthetic sample data and test accounts before processing real contacts, deals, campaign assets, or financial information.

Ongoing Maintenance

Revenue operations is the primary system owner. The marketing coordinator is the backup owner for campaign operations and first-line user support.

Every production change should record the date, reason, affected tables or Zaps, tester, and rollback procedure. Field renaming should be treated as an integration change because mapped actions may depend on the original field.

When to Move to Dedicated Software

The Airtable-based implementation should not be replaced solely because the company grows. It should be reviewed when operational risk or complexity exceeds what the team can control reliably.

Potential upgrade indicators include:

• Campaign and event volume approaches the practical limits of the selected accounts.
• Audience synchronization requires near-real-time, high-volume processing.
• Multiple brands, business units, or countries need isolated permissions and consent rules.
• Campaign attribution requires multi-touch models across many channels.
• Spreadsheet reporting no longer performs adequately.
• Formal audit requirements demand immutable logs and stronger transaction controls.
• Exception rates or failed-run recovery consume excessive staff time.
• Workflow changes require frequent specialist maintenance.
• Customer-facing campaign portals or approval portals are required.
• Mobile or offline campaign operations become important.
• Assets require formal digital rights, localization, or complex version management.
• Security risk increases because more sensitive contact or financial data is being copied.
• The organization requires contractual vendor support and service commitments.
• Advanced forecasting, experimentation, or cross-channel attribution becomes a core requirement.

Relevant next-step categories may include marketing resource management platforms, customer data platforms, dedicated attribution systems, digital asset management software, a cloud data warehouse with BI reporting, or a custom campaign operations application. The correct choice depends on the limitation being addressed.

Implementation Checklist

• Confirm campaign volume, event volume, users, and business requirements.
• Document which system is authoritative for campaigns, contacts, subscriptions, deals, files, and reports.
• Confirm Airtable, HubSpot, Mailchimp, Zapier, and Google Workspace account features.
• Create test environments, test users, and test data.
• Assign platform administrators, operational owners, approvers, and backup owners.
• Configure least-privilege permissions and controlled integration connections.
• Build the Airtable tables, links, controlled fields, formulas, and views.
• Create the internal campaign intake form.
• Implement Campaign ID and duplicate-detection rules.
• Configure campaign statuses and ownership.
• Create HubSpot contact and deal properties.
• Configure the Mailchimp audience, merge fields, tags, and suppression controls.
• Create the Google Drive root folder and campaign-folder convention.
• Create Results_Staging, Campaign_ROI, Lists, and Exceptions sheets.
• Document every source-to-destination field mapping.
• Build campaign validation and provisioning automation.
• Build audience synchronization and unsubscribe handling.
• Build lead, lifecycle, opportunity, and revenue event workflows.
• Build validated spend and performance imports.
• Build brief and budget approval gates.
• Configure reminders, escalations, reassignment, and delegation.
• Configure notifications and role mailbox ownership.
• Build operational views and executive reporting.
• Create idempotency keys and duplicate-event controls.
• Create the Sync Exceptions queue and manual-recovery process.
• Configure logs, retries, reconciliation, and failed-run alerts.
• Apply file permissions, retention, backup, privacy, and consent controls.
• Test normal, duplicate, invalid, rejected, failed, replayed, and completed records.
• Complete user acceptance testing and phased deployment.
• Validate implementation cost and recurring software assumptions.
• Replace time-saving assumptions with measured internal data.
• Add the AI summary workflow only after core data is reliable.
• Keep AI output in draft status until human approval.
• Assign ongoing maintenance, permission review, and credential-rotation owners.
• Define measurable criteria for moving to dedicated software.