Automating Partner Referrals, Attribution, and Fee Tracking

The Business Situation

Cedar Vale Advisory is a fictional 68-person operations and technology consultancy. The firm receives prospective-client introductions from accounting practices, marketing agencies, independent advisers, and technology partners.

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 referral program involves 24 active partners. It receives approximately 45 submissions per month, of which about 28 are accepted for sales follow-up and 14 progress to an opportunity. Approximately five referred opportunities reach Closed Won in an average month.

The people involved are:

• A partner development manager who owns partner relationships.
• A revenue operations manager who checks attribution and maintains Salesforce.
• Six sales consultants who qualify and progress opportunities.
• A finance coordinator who validates fee calculations and records payments.
• A finance manager who approves higher-value referral fees.
• A marketing operations analyst who prepares channel and partner reporting.

The consultancy already uses Salesforce as its CRM, Google Workspace for forms and spreadsheets, Slack for internal communication, and Make for selected integrations. However, the tools were not operating as one referral system.

Partners sent introductions through email, informal messages, and an inconsistently used Google Form. Revenue operations copied submissions into Google Sheets and asked sales representatives to update Salesforce. The partner manager maintained a separate fee spreadsheet and regularly contacted sellers to determine whether a referral had become an opportunity.

This process created disputes about when a lead was received, which partner deserved attribution, whether a deal was still active, and which fee rule applied. The business needed a controlled process before expanding the partner program because a larger volume would have increased reconciliation work and commercial risk.

The Existing Process

The original workflow usually followed this sequence:

1. A partner sent a referral by email or submitted the Google Form.
2. The partner manager checked whether the prospect appeared familiar.
3. Referral details were copied into a shared spreadsheet.
4. A Slack message or email asked a salesperson to follow up.
5. The salesperson manually created a Salesforce Lead, Contact, or Opportunity.
6. The partner manager periodically asked for a status update.
7. If the opportunity closed, finance searched Salesforce and the spreadsheet for the agreed fee rate.
8. The fee was calculated in a separate spreadsheet and approved through email.
9. The partner manager manually updated the partner after payment.

The spreadsheet was useful as a working list, but it had no enforced relationships between partners, referrals, opportunities, and fee records. A single row could contain several opportunity identifiers, free-text statuses, and manually typed percentages.

Salesforce contained the authoritative sales stages and opportunity values, but referral attributes were often stored in notes. Google Sheets contained the referral history, but its opportunity stages became stale. Neither system showed which records had failed during an attempted update.

There was also no consistent ownership rule. A referral might be assigned to the partner manager, a salesperson, or nobody. When a salesperson was unavailable, reminders continued to be sent to that person without escalation.

What the New System Needed to Do

The team defined the business rules before selecting the final implementation. The requirements distinguished deterministic workflow rules from decisions that required commercial judgment.

The team specifically excluded automatic acceptance, automatic rejection, automatic payment release, and unsupervised partner communications. Those actions could materially affect a partner relationship or financial obligation.

Implementation Approaches Considered

Google Forms and Sheets

Google Forms was appropriate for external intake because partners already understood it and did not need Salesforce access. Google Sheets remained useful for an operational tracker and ad hoc analysis. It was not selected as the authoritative database because partner, referral, opportunity, and fee relationships require stronger controls than a shared spreadsheet normally provides.

Airtable

Airtable could have modeled partners, referrals, deals, and fees effectively. The disadvantage was the creation of a second operational database beside Salesforce. The team would still have needed bidirectional synchronization, permissions, duplicate controls, and a process for deciding which system won a conflict.

Salesforce

Salesforce was the strongest system of record because sales staff already managed Leads and Opportunities there. Custom objects could preserve referral terms, relationships, approval evidence, and fee records. The selected approach therefore extended Salesforce rather than replacing it.

Make, Zapier, and n8n

Make was selected because the company already used it and the workflow required branching, record searches, updates across several systems, and centralized error routes. Zapier would also have been suitable for a simpler linear workflow. n8n would have provided greater control over custom API requests and self-hosting, but it would have required more operational ownership for hosting, upgrades, credentials, and monitoring.

The selection did not imply that Make was universally preferable. An organization already operating Zapier or n8n successfully should include migration effort, support skills, security requirements, and existing automation inventory in its decision.

The Selected Solution

The selected implementation used Google Forms for partner intake, Salesforce as the system of record, Make as the integration layer, Google Sheets as a controlled working tracker, and Slack for internal notifications. Salesforce Files stored partner agreements and supporting documents.

This combination retained the company’s existing tools. It removed manual copying of accepted referrals, manual opportunity-stage updates in the tracker, repeated fee-rate lookups, and most routine reminder messages.

Humans continued to decide whether a referral was valid, whether two submissions represented the same prospect, which partner received disputed attribution, whether revenue was fee-eligible, whether a fee should be approved, and what information could be communicated to a partner.

System Architecture and Data Flow

• Intake: Google Forms with responses written to a protected Google Sheets tab.
• System of record: Salesforce custom Partner Profile, Referral, and Referral Fee objects, plus standard Leads, Opportunities, Tasks, and Events.
• Automation layer: Make scenarios using secured OAuth connections.
• Document storage: Salesforce Files related to the relevant Partner Profile or Referral.
• Notifications: Slack messages containing limited data and a Salesforce record link.
• Reporting: Salesforce reports for authoritative metrics and Google Sheets for the working tracker and reconciliation.
• AI layer: An optional structured-output classification step invoked by Make after the core workflow succeeds.

1. Partner submission: A partner submits Google Forms data. Google Sheets receives the timestamp and answers. If the write fails, Google Forms retains the response for administrator review.
2. Submission trigger: Make detects a new protected response row. It creates a deterministic submission key from the UTC timestamp and response row number.
3. Validation: Make validates the partner-code pattern, email format, consent confirmation, prospect name, and prospect website or domain. Invalid submissions are routed to manual review.
4. Partner lookup: Make searches Salesforce for one active Partner Profile with the submitted partner code. No match or multiple matches create an exception.
5. Duplicate checks: Make first searches for the exact submission key. It then searches for an open or recently received referral with the same normalized prospect domain and partner or attribution window.
6. Referral creation: If the event is new, Make creates a Salesforce Referral. Salesforce returns the record ID and auto-number. Make writes both values to the source response row.
7. Review and assignment: The partner development manager or revenue operations manager reviews the referral. On acceptance, Make creates a Salesforce Lead if one does not already exist and assigns it using the Partner Profile’s default owner.
8. Opportunity attribution: When a Lead is converted or an Opportunity is otherwise linked, Make finds the Referral by its unique referral key, writes the Referral lookup to the Opportunity, and updates the working tracker.
9. Activity synchronization: New or changed Salesforce Tasks and Events update the Referral’s last-activity and next-action fields. Missing follow-up creates an exception task and Slack reminder.
10. Deal-stage synchronization: Opportunity changes update the Referral status, Google Sheets working tracker, and selected Slack notifications. The Salesforce Opportunity ID remains the returned external identifier.
11. Fee creation: Closed Won creates or updates one draft Referral Fee per eligible Opportunity. The applicable rate is copied from the Partner Profile as a snapshot. Missing basis data creates a finance exception instead of estimating a payment.
12. Approval and payment tracking: Revenue operations confirms attribution, finance confirms eligible revenue, and an authorized approver approves the fee. Payment execution remains outside this automation. Finance records the payment reference and date after payment.
13. Reporting and reconciliation: Scheduled synchronization updates Google Sheets. A daily reconciliation compares Salesforce records with tracker rows and adds unresolved differences to the automation error register.

Make processes records using idempotent keys. Reprocessing the same form row or Opportunity event therefore updates the existing record instead of creating another one.

Data Structure

Partner Profile

Referral

Opportunity and activity additions

The standard Opportunity receives a Referral lookup, a unique Referral Key text field, Eligible Referral Revenue, Fee Eligibility, and Attribution Review Status. Lead has a Referral Key field mapped to Opportunity during Lead conversion. Make uses that key to populate the Opportunity lookup if conversion does not populate the relationship directly.

Tasks and Events use activity fields for Automation Generated, Exception Key, Partner Update Activity, and Referral ID. Automated exception tasks are marked so the activity synchronization scenario does not treat them as genuine sales activity.

Referral Fee

The relationships are one Partner Profile to many Referrals, one Referral to zero or many Opportunities, and one eligible Opportunity to one Referral Fee. The unique Fee Key prevents the same Opportunity event from creating multiple fee records.

Google Sheets structure

The workbook contains protected tabs named Form_Responses, Referral_Tracker, Opportunity_Events, Fee_Tracker, Automation_Errors, and Control. Only Make and designated revenue operations administrators can edit system columns.

Referral_Tracker uses one row per Referral. Opportunity_Events uses one row per Opportunity. Fee_Tracker uses one row per Referral Fee. This avoids placing several opportunity or fee identifiers in one cell.

Workflow Statuses and Ownership

A record can move backward from Accepted or Attributed to Needs Review when a conflict appears. Rejection requires a controlled reason and reviewer identity. Closure requires a terminal sales outcome or completed fee process.

Automation can move a record into Needs Review, Attributed, Open Opportunity, or Closed Won, Fee Review. Automation cannot approve attribution, approve a fee, mark a fee Paid, or issue a final partner communication.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Confirm that the Salesforce organization supports the custom objects, fields, API access, OAuth connections, reports, and history features required by the design. Availability depends on the organization’s Salesforce configuration and licensing.
2. Create a dedicated Salesforce integration user or another approved non-personal integration identity. Do not connect Make using a salesperson’s account.
3. Grant the integration identity read and write access only to Partner Profile, Referral, Lead, Opportunity referral fields, Referral Fee, Task, Event, and the fields used by the scenarios.
4. Allow the integration identity to create Leads, Referral Fees, and Tasks. Do not grant permission to approve fees or mark them Paid.
5. Create a Google Workspace shared drive or controlled folder for the form, response workbook, implementation documentation, and non-sensitive operational exports.
6. Use a Google integration account or approved shared automation connection with access only to the selected form and workbook.
7. Create a restricted Slack channel such as referral-operations. Add revenue operations, the partner manager, and the finance coordinator. Sales owners can receive direct messages or channel mentions according to internal policy.
8. Create a Make organization or team space with separate development and production folders. Restrict scenario editing and connection management to automation administrators.
9. Connect Salesforce to Make through OAuth. Connect Google Sheets and Slack through their approved OAuth connections. Store all credentials in Make connections rather than variables, Sheets, or scenario notes.
10. Create test users for revenue operations, sales, partner management, and finance. Test with non-production names and domains.
11. Where a Salesforce sandbox is available, configure and test the CRM elements there. Create a separate test form and spreadsheet rather than pointing development automation at the production response sheet.

Permission boundaries are important. Sales users can update qualification and activity fields. Revenue operations can resolve attribution. Finance can edit fee basis and payment evidence. Only designated approvers can populate approval fields. The Make integration user can propose and synchronize records but cannot perform final approvals.

Step 2: Build the Intake

Create a Google Form titled Partner Referral Submission. Configure it to write responses to the protected Form_Responses tab.

Do not enable public file uploads for this implementation. Google Forms file-upload behavior can require account authentication, and unrestricted attachments introduce malware, access, and retention concerns. Supporting documents can be requested after the referral is accepted and stored in Salesforce Files.

The form confirmation message should state that submission does not guarantee acceptance or a referral fee, that a representative will review the information, and that sensitive personal, financial, health, authentication, or confidential client data must not be entered.

The partner code is an identifier, not a password. Make validates it against the active Salesforce Partner Profile and checks whether the submitting email matches an authorized domain or contact. A mismatch creates manual review rather than disclosing whether another partner exists.

Google Forms handles basic required-field validation. Make performs business validation. Incomplete or suspicious submissions are preserved but are not automatically assigned to sales.

Step 3: Create the System of Record

1. Create the Partner Profile custom object. Use a text Name field for the partner name and make Partner Code a unique field.
2. Create the Referral custom object with an auto-number Name such as REF-{000000}. Enable activities so Tasks and Events can be related to a Referral.
3. Create the Referral Fee custom object with an auto-number Name such as FEE-{000000}.
4. Add a Partner Profile lookup to Referral and lookups from Referral Fee to Partner Profile, Referral, and Opportunity.
5. Add a Referral lookup and Referral Key text field to Opportunity.
6. Add Referral Key to Lead and configure Salesforce Lead field mapping so it moves to the corresponding Opportunity field during conversion.
7. Create Fee Key and Submission Key as unique external ID text fields. These enforce idempotency even if Make receives the same event twice.
8. Add the controlled status, exception, approval, and automation fields described in the data model.
9. Enable field history tracking for Referral Status, Partner, Owner, Approval Status, Exception Type, Opportunity Referral lookup, Fee Status, Fee Basis Amount, Fee Rate Snapshot, Calculated Fee, approvers, and payment evidence.
10. Create filtered Salesforce list views for new submissions, review exceptions, referrals without activity, Closed Won fee review, approved unpaid fees, and automation failures.

Use clear API naming conventions such as Partner_Profile__c, Referral__c, Referral_Fee__c, Partner_Code__c, Submission_Key__c, Referral_Key__c, Fee_Key__c, Automation_Status__c, and Last_Automation_Run__c.

In Google Sheets, create headers before connecting Make. Protect the Form_Responses answer columns and allow Make to write only processing fields such as Submission Key, Salesforce Referral ID, Referral Number, Processing Status, Processed At, and Error Summary.

Create the following Control tab columns: Scenario Name, Last Successful Timestamp, Last Run ID, Last Record ID, Status, and Last Error. This tab supports reconciliation and recovery. It is not a substitute for Make execution history.

Step 4: Connect the Tools

Make interface labels can vary as connectors are updated. The required operations are a new-row watch, Salesforce record search, create, retrieve, and update operations, Google Sheets row search and update operations, routing filters, variables, error handlers, and Slack message creation.

For every Salesforce create operation, capture the returned Salesforce record ID immediately. Write it to the source Referral or sheet row before sending a non-critical Slack notification. This order prevents a failed notification from leaving the core record unidentifiable.

Step 5: Build the Core Automation

Automation 1: Intake and Referral Creation

• Trigger: A new row appears in Form_Responses.
• Conditions: The response has no Salesforce Referral ID and has not been marked permanently rejected.
• Actions: Create the submission key, normalize values, validate fields, find the partner, check duplicates, create or identify the Referral, update the response row, and notify the reviewer.
• Fields updated: Submission Key, Referral ID, Referral Number, Processing Status, Processed At, Automation Status, and any exception fields.
• Notification: Slack sends a limited new-referral or review-needed message.
• Exception: Invalid partner, possible duplicate, malformed domain, or Salesforce failure enters Automation_Errors and creates a Salesforce review task when a Referral exists.

Configure the modules in this order:

1. Watch new rows in Form_Responses.
2. Read the row number and form timestamp.
3. Build a key such as FORM-20260715T142455Z-R000237.
4. Normalize the partner code by trimming spaces and converting it to uppercase.
5. Normalize the domain by converting it to lowercase, removing the protocol, removing a leading www, removing a path, and removing a trailing slash.
6. Reject unsafe or malformed partner-code input before using it in a Salesforce query.
7. Search Salesforce Referral by exact Submission Key. If found, write the existing Referral ID to the sheet and stop that route.
8. Search Partner Profile for one active record with the exact Partner Code.
9. Search recent Referral records for the normalized prospect domain. Use the partner’s attribution window as a review rule, not an automatic rejection rule.
10. Create a Referral with Submitted status when validation succeeds. Create it with Needs Review when the partner is unmatched or a possible duplicate requires a human decision.
11. Capture the returned Salesforce ID and Referral Number.
12. Update the form-response row.
13. Upsert the Referral_Tracker row using Referral ID as the key.
14. Send the Slack notification last.

Automation 2: Accepted Referral to Salesforce Lead

• Trigger: A Referral changes to Accepted.
• Conditions: Approval Status is Approved, Partner is present, Lead ID is blank, and Automation Status is not Processing.
• Actions: Search for an existing Lead or Contact using exact business email and normalized domain, create or link the Lead, assign the owner, and store the Lead ID.
• Fields updated: Lead ID, Status, Last Automation Run, Automation Status, and Error Message.
• Notification: The assigned salesperson receives a Slack message with the Referral link and due date.
• Exception: A possible existing Lead or Contact is routed to manual review instead of being overwritten.

Set Automation Status to Processing before creating the Lead. Search by Referral Key before any create operation. If the Lead already exists, store its ID and mark the operation complete. This makes recovery safe after a timeout.

Automation 3: Opportunity Attribution and Tracker Synchronization

• Trigger: An Opportunity with a Referral Key changes.
• Conditions: The change affects Stage, Amount, Close Date, Owner, Fee Eligibility, Eligible Referral Revenue, or Referral Key.
• Actions: Find the Referral, populate the Opportunity Referral lookup, update Referral status, and upsert Opportunity_Events and Referral_Tracker.
• Fields updated: Opportunity ID, stage, amount, close date, Referral status, Last Automation Run, and tracker timestamps.
• Notification: Slack notifies owners for material events such as first attribution, Closed Won, Closed Lost, or an attribution exception.
• Exception: Missing, invalid, or conflicting Referral Keys create a Salesforce Task assigned to revenue operations.

Do not send a Slack message for every minor field edit. Filter for material stage or attribution changes to avoid notification fatigue.

Automation 4: Activity Event Synchronization

• Trigger: A qualifying Salesforce Task or Event is created or changed.
• Conditions: It relates to a Referral or attributed Opportunity and Automation Generated is false.
• Actions: Resolve the related Referral, update Last Activity At, identify the earliest open next action, and update the tracker.
• Fields updated: Last Activity At, Next Action Date, Last Partner Update where applicable, and Automation Status.
• Notification: No message for routine activity. A reminder is generated only if the next action is missing or overdue.
• Exception: An activity with an invalid relationship is logged without modifying another Referral.

Completed Tasks and past Events can update Last Activity At. Open Tasks provide candidate Next Action Dates. If several open Tasks exist, Make writes the earliest due date. A Task marked Partner Update Activity updates Last Partner Update only after the user records that the communication occurred.

Automation 5: Closed Won and Fee Draft Creation

• Trigger: An attributed Opportunity changes to Closed Won.
• Conditions: Fee Eligibility is not No, a Partner and Referral exist, and no Referral Fee exists for the Fee Key.
• Actions: Build the Fee Key, copy the partner’s agreement terms, create a draft fee, update Referral status, and assign finance review.
• Fields updated: Fee basis type snapshot, fee rate snapshot, currency, status, approval fields, automation fields, and tracker row.
• Notification: Finance receives a Slack notification with the Salesforce Fee link.
• Exception: Missing agreement, rate, currency, or eligible revenue creates an exception Task and places the fee in Awaiting Basis or Disputed.

The Fee Key uses the Referral ID and Opportunity ID. Make searches this exact key before creating a Referral Fee. Changing the Partner Profile’s current rate does not alter an existing fee because the fee stores a snapshot.

Automation 6: Fee Status Synchronization

• Trigger: A Referral Fee status or approval field changes.
• Conditions: The record has a valid Fee Key.
• Actions: Update Fee_Tracker, update the parent Referral where appropriate, and notify the next owner.
• Fields updated: Fee status, approval dates, paid date, payment reference, and synchronization timestamp.
• Notification: Attribution reviewer, finance approver, or partner manager is notified according to status.
• Exception: Paid without evidence, Approved without required approvals, or inconsistent currency is blocked by Salesforce validation and logged by Make.

Step 6: Add Approvals, Reminders, and Escalations

Referral acceptance and fee approval use different controls.

Referral acceptance

1. Revenue operations validates the partner, submission, duplicate search, and consent.
2. The partner development manager resolves any commercial attribution conflict.
3. An authorized reviewer sets Approval Status to Approved and Referral Status to Accepted.
4. Make creates or links the Lead.

Validation and commercial review can occur in parallel, but the Referral cannot enter Accepted until both are complete. A rejection requires a controlled reason. Return for More Information changes the record to Needs Review and assigns the partner manager a communication task.

Fee approval

1. Revenue operations confirms that the Opportunity is correctly attributed.
2. Finance confirms the fee basis amount, basis type, currency, and agreement status.
3. The finance coordinator approves fees at or below the representative internal threshold of $2,500.
4. A finance manager provides an additional approval above $2,500.
5. After payment is executed through the company’s normal financial controls, finance records the payment reference and paid date.

The $2,500 threshold is a representative assumption, not a recommended universal policy. Each business should use its approved financial delegation rules.

Make searches each business day for items awaiting action:

• Submitted for one business day: remind revenue operations.
• Needs Review for three business days: escalate to the partner manager.
• Accepted without a Lead ID for 15 minutes: create an integration exception.
• Open Opportunity without a next action: notify the sales owner.
• Next Action Date overdue by one day: remind the owner.
• Overdue by three business days: notify the owner’s manager or designated backup.
• Fee awaiting finance review for two business days: remind finance.
• Fee awaiting finance review for four business days: escalate to the finance manager.

An unavailable approver should have a named delegate recorded in an approved delegation field or internal schedule. Make sends the task to the delegate only during the approved delegation period. It does not infer absence from Slack status.

Before creating an exception Task, Make searches for an open Task with the same Referral and Exception Key. If one exists, it updates the due date or reminder count rather than creating another task.

Step 7: Add Documents and File Management

Partner agreements are stored as Salesforce Files related to the Partner Profile. Referral-specific supporting material is related to the Referral. Documents should not be kept solely as public links in the Google Form response.

• Folder and relationship model: Partner agreement files belong to the Partner Profile. Deal-specific evidence belongs to the Referral or Opportunity.
• Naming convention: PartnerCode_DocumentType_EffectiveDate_Version, such as ACCT-014_ReferralAgreement_2026-01-01_v2.pdf.
• Access: Partner program administrators and authorized finance users can access agreements. Sales users receive only the access required for their role.
• Version control: Upload a new version rather than replacing evidence without history.
• Retention: Retain agreements, approvals, and payment evidence according to contractual, financial, privacy, and legal policies.
• Archiving: Mark expired agreements and remove them from active operating views without deleting required evidence.
• Missing documents: Agreement Status cannot become Active until required evidence is present and reviewed.
• Duplicate documents: Check partner code, document type, effective date, and version before upload.
• File size: Keep files within Salesforce and organizational limits. Large source files should use an approved document repository with controlled access.
• Failed upload: Record the failure on the Referral or Partner Profile and create a document exception Task.

Make does not need to copy every document into Google Drive. Avoiding duplicate storage reduces access and retention conflicts.

Step 8: Add Reporting and Operational Views

Create authoritative Salesforce reports for:

• New referrals by partner and month.
• Submissions awaiting review.
• Accepted referrals without Leads.
• Referrals by owner and status.
• Open Opportunities with partner attribution.
• Opportunity pipeline and Closed Won amount by partner.
• Referral conversion from submission to accepted, Opportunity, and Closed Won.
• Fees awaiting basis, approval, or payment.
• Paid fees by partner and period.
• Referrals without recent activity.
• Attribution conflicts and other exceptions.
• Automation records in Exception or Retry Scheduled status.

Google Sheets provides an operational view for users who need a cross-system working tracker but do not maintain Salesforce reports. Referral_Tracker filters include New, Awaiting Action, Overdue, Incomplete, Exceptions, Declined, By Owner, Upcoming Deadlines, Recently Completed, and Manual Review.

Opportunity_Events supports pivot tables for volume and value by stage, partner, owner, and month. Fee_Tracker supports counts and amounts by fee status. Automation_Errors reports failures by scenario, system, error category, retry count, and age.

Make updates event-driven rows throughout the day and runs a full reconciliation once daily. The marketing operations analyst owns report definitions. Revenue operations owns source-data quality. Finance owns fee-status accuracy.

Alert thresholds should be operationally meaningful. For this scenario, alerts are raised when more than three records remain in Automation Exception for over one business day, when an active referral has no owner, or when a Closed Won opportunity lacks a fee eligibility decision.

Step 9: Add Security and Governance Controls

• Use least-privilege Salesforce permission sets rather than broad administrator access.
• Restrict Google Sheets to revenue operations, selected marketing operations users, and the integration account.
• Protect system columns and calculated ranges from manual editing.
• Keep personal contact details and commercial amounts out of broad Slack channels. Slack messages should link to Salesforce rather than reproduce sensitive notes.
• Store OAuth tokens and API keys only in approved connection or secret-management facilities.
• Review Salesforce field-level security for fee rates, payment references, partner agreements, and prospect contact data.
• Disable public sharing for operational Sheets and documents.
• Remove former employees from Salesforce, Google Workspace, Slack, and Make promptly.
• Review Make scenario history and Salesforce field history as audit evidence, while recognizing that integration logs do not replace formal financial records.
• Apply the organization’s privacy notice, retention policy, and lawful-use rules to prospect data supplied by partners.
• Do not send regulated, privileged, authentication, payment-card, health, or unnecessary personal data to an optional AI service.
• Require human approval for attribution conflicts, referral rejection, fee approval, payment status, and external partner updates.

The design should be reviewed for applicable contractual, privacy, tax, accounting, anti-bribery, and industry requirements. The automation records process evidence but does not determine legal or tax treatment.

Step 10: Deploy and Test

1. Build Salesforce objects and fields in a sandbox or controlled development environment.
2. Create separate development Google Form and Sheets files.
3. Clone Make scenarios into a development folder and connect only test systems.
4. Prepare sample partners, referrals, Leads, Opportunities, Tasks, Events, and Fee records.
5. Run unit tests for each scenario route, including success, duplicate, validation, and connector failure cases.
6. Run end-to-end tests using test users from sales, partner management, revenue operations, and finance.
7. Complete user acceptance testing with at least one sample for each fee basis and approval threshold.
8. Pilot with three internal partner managers or a limited set of partner submissions for two weeks.
9. Reconcile every pilot Referral against Salesforce, Sheets, and Make execution history.
10. Document field definitions, ownership, recovery procedures, and approval responsibilities.
11. Schedule production activation outside a high-volume period.
12. Pause the old form or label it clearly so partners cannot submit to both forms.
13. Enable production scenarios in sequence: intake, Referral to Lead, Opportunity sync, activity sync, fee sync, reminders, and reconciliation.
14. Monitor the first production runs and compare record counts after each stage.
15. Maintain a rollback plan that pauses Make scenarios, preserves form responses, and returns staff to Salesforce-only manual processing while errors are corrected.

Launch communication should tell staff which system owns each field, how to report an error, what not to edit in Google Sheets, and which decisions still require human approval.

Code and Configuration

No custom script is required for the core implementation. Google Forms, Salesforce, Make, Google Sheets, and Slack provide the necessary native triggers, record operations, routing, and notifications. Avoiding custom code reduces the number of deployment components, but the native configuration still requires testing and maintenance.

Salesforce formulas and validation

Create Calculated Fee as a Salesforce Currency formula field. This version applies when Fee Rate Snapshot is a Percent field and Fee Basis Amount is Currency:

ROUND(Fee_Basis_Amount__c * Fee_Rate_Snapshot__c, 2)

The formula is placed on Referral_Fee__c. It requires no credentials and runs whenever Salesforce evaluates the record. Test it with rates such as 7.5 percent and known basis amounts. If the result is 100 times too large or small, inspect how the percentage field was defined and entered.

Create a validation rule that prevents Approved status without basis and approval evidence:

AND(
  ISPICKVAL(Status__c, "Approved"),
  OR(
    ISBLANK(Fee_Basis_Amount__c),
    ISBLANK(Fee_Rate_Snapshot__c),
    ISBLANK(Finance_Approved_Date__c),
    ISBLANK(Finance_Approver__c)
  )
)

Create another validation rule that prevents Paid status without payment evidence:

AND(
  ISPICKVAL(Status__c, "Paid"),
  OR(
    ISBLANK(Paid_Date__c),
    ISBLANK(Payment_Reference__c)
  )
)

These rules are placed in the Referral Fee object’s validation-rule configuration. They run for user and integration updates. Test valid and invalid status changes using users with the intended permissions. A failed Make update should be captured as a business validation error, not repeatedly retried as a transient outage.

Create Days Since Partner Update as a Number formula on Referral:

IF(
  ISBLANK(Last_Partner_Update__c),
  TODAY() - DATEVALUE(CreatedDate),
  TODAY() - Last_Partner_Update__c
)

This formula has no external dependencies. It supports reports and reminder searches. Confirm that date interpretation matches the Salesforce organization’s timezone and business process.

Google Sheets calculated columns

In Referral_Tracker, assume column A is Referral ID, B is Created Date, H is Referral Status, M is Next Action Date, P is Closed Date, W is Aging Days, and X is Follow-Up Flag.

Place this formula in W2:

=ARRAYFORMULA(IF(A2:A="","",IF(P2:P<>"",P2:P-B2:B,TODAY()-B2:B)))

Place this formula in X2:

=ARRAYFORMULA(
  IF(
    A2:A="",
    "",
    IF(
      (M2:M<>"")*
      (M2:M<TODAY())*
      (H2:H<>"Closed Lost")*
      (H2:H<>"Declined")*
      (H2:H<>"Fee Paid"),
      "OVERDUE",
      ""
    )
  )
)

These formulas run in Google Sheets and require no API credentials. Make must write actual date values or consistently parseable ISO dates. Protect the formula columns. Test with blank dates, future dates, terminal statuses, and dates in the past. Text-formatted dates are the most likely cause of incorrect results.

Make filters and transformations

The Slack message uses mapped values rather than code. Configure it in the Slack message action and keep it concise:

Referral {{Referral_Number}} requires review.
Status: {{Referral_Status}}
Owner: {{Owner_Name}}
Exception: {{Exception_Type}}
Due date: {{Due_Date}}
Salesforce record: {{Referral_URL}}

The message runs after the Salesforce record and tracker have been updated. It requires the secured Slack connection and permission to post to the selected channel. Test it in a private test channel. If a user mention fails, fall back to the owner’s displayed name and record the notification failure without reversing the CRM update.

Native deployment configuration

1. Configure each scenario connection using OAuth and a non-personal integration identity.
2. Set production scenarios to process records sequentially where concurrent writes could create duplicates.
3. Enable incomplete execution storage where appropriate and permitted.
4. Add an error route to every module that writes to Salesforce, Google Sheets, or Slack.
5. Write sanitized errors to Automation_Errors with Scenario, Source Record Key, Destination, HTTP or Connector Category, Retry Count, Next Retry At, and Resolution Status.
6. Set a maximum of three automated recovery attempts for transient failures. Use representative delays of one, five, and fifteen minutes, subject to platform limits and organizational policy.
7. Do not retry validation failures until a user corrects the record.
8. Record the last successful cursor in Control only after all critical writes for the event have succeeded.
9. Activate scenarios in dependency order and inspect the first executions in Make history.

Failure Handling and Operational Reliability

Automation_Errors acts as a controlled dead-letter register for events that cannot complete automatically. It stores no credentials and should contain only the minimum data needed for recovery.

Each error row includes the idempotency key, source record, failed destination, sanitized message, retry count, next retry time, owner, and resolution status. A recovery scenario scans only rows marked Retry Requested or Retry Scheduled.

Idempotency is enforced at several levels:

• Submission Key is unique on Referral.
• Referral Key is preserved from Lead to Opportunity.
• Fee Key is unique per Referral and Opportunity.
• Exception Key is searched before a new Task is created.
• Tracker rows are updated by Salesforce ID, not by row position.

A daily reconciliation compares accepted Referrals without Lead IDs, Opportunities with Referral Keys but no Referral lookup, Closed Won Opportunities without a fee eligibility decision, Referral Fees missing from Fee_Tracker, and unresolved automation errors. Staff can therefore identify silent partial failures even when no user reports a problem.

A Complete Example

An authorized contact at an accounting partner submits a referral for a regional packaging distributor.

Partner code

ACCT-014

Prospect organization

Regional Packaging Distributor

Prospect domain

regional-packaging.example

Service interest

Technology Enablement

Expected timing

Within 30 days

Submission time

2026-07-15 14:24:55 UTC

Google Forms writes the answers to row 237 of Form_Responses. Make creates Submission Key FORM-20260715T142455Z-R000237.

Make validates ACCT-014 and finds one active Salesforce Partner Profile. The profile has a 90-day attribution window, a fee basis of Eligible Collected Revenue, a 7.5 percent rate, and a default sales owner.

The domain is normalized to regional-packaging.example. No exact Submission Key exists. A search finds no open or recent Referral for that domain, so Make creates Salesforce Referral REF-000418 and receives its Salesforce record ID.

Make writes the Salesforce ID and REF-000418 to the form-response row, creates the Referral_Tracker row, and posts a limited Slack notification to the referral operations channel.

Revenue operations reviews the consent, prospect details, and duplicate result. The partner development manager confirms the referral terms. The Referral becomes Accepted.

Make searches for an existing Lead with the Referral Key. None exists, so it creates a Lead assigned to the partner profile’s default sales owner and stores the returned Lead ID on REF-000418.

The salesperson qualifies and converts the Lead. Salesforce maps the Referral Key to the new Opportunity. The Opportunity-change scenario finds REF-000418, populates the Referral lookup on the Opportunity, changes the Referral to Open Opportunity, and updates Opportunity_Events.

The salesperson logs a completed discovery Task and creates a follow-up Task due in five days. The activity scenario updates Last Activity At and Next Action Date. No exception Task is created.

After the proposal is accepted, the Opportunity becomes Closed Won with a contract value of $48,000. Eligible Collected Revenue is initially blank. Make creates Fee record FEE-000087 with the fee basis and rate snapshots, but places it in Awaiting Basis. It also creates an exception Task for finance.

Finance confirms that $40,000 is eligible collected revenue. The Salesforce formula calculates a proposed fee of $3,000:

Proposed fee: $40,000 × 7.5 percent = $3,000

Revenue operations confirms attribution. Because the fee exceeds the representative $2,500 threshold, both the finance coordinator and finance manager approve it sequentially. Make updates Fee_Tracker after each status change.

Payment occurs through the consultancy’s normal financial process. Finance records a sample payment reference and paid date in Salesforce. Validation confirms that the evidence is present before allowing Paid status. REF-000418 changes to Fee Paid, the tracker is updated, and the partner manager receives a Slack task to send a reviewed partner update.

If the Closed Won event is delivered again, Make finds Fee Key REF-000418 plus the Opportunity ID and updates FEE-000087 rather than creating a second fee.

Implementation Cost

All amounts below are representative planning assumptions, not verified client prices or results. Actual software contracts, internal rates, implementation scope, taxes, currencies, and support needs will vary.

The optional professional implementation range should not be added to the internal labour estimate if an organization is comparing alternative delivery models. A blended project may use part of each.

Estimated Time and Cost Savings

The following model uses conservative representative assumptions:

• 45 referral submissions per month.
• 32 minutes of current handling time per referral.
• 10 minutes of routine handling after core automation.
• 12 percent exception rate.
• 12 minutes of manual handling per exception.
• Four hours of monthly maintenance.
• $48 loaded hourly labour cost.
• $90 monthly recurring automation cost.
• $6,760 one-time internal implementation cost.

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

45 × 32 ÷ 60 = 24.00 hours

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

Routine work: 45 × 10 ÷ 60 = 7.50 hours

Exception handling: 45 × 12 percent × 12 ÷ 60 = 1.08 hours

Maintenance: 4.00 hours

Total new monthly labour: 7.50 + 1.08 + 4.00 = 12.58 hours

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

24.00 – 12.58 = 11.42 hours

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

11.42 × $48 = $548.16

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

$548.16 – $90 = $458.16

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

$6,760 ÷ $458.16 = approximately 14.8 months

Recovered time does not automatically reduce payroll. It may create additional capacity, shorten response times, reduce overtime, remove repetitive administration, support higher referral volume, and reduce dependency on one employee.

Non-financial benefits include clearer ownership, more consistent partner communication, better attribution evidence, fewer incomplete records, improved reporting, a controlled fee history, and faster identification of integration failures.

Readers should replace the referral volume, current time, new time, exception rate, review time, maintenance time, labour rate, software costs, and implementation cost with their own measured values.

Adding AI to the Automation

AI should be added only after the intake, validation, attribution, synchronization, fee calculation, and exception workflow operate reliably.

Most controls in this system do not require AI. Partner-code validation, required fields, exact Submission Key matching, fee formulas, approval thresholds, status transitions, permission checks, and payment evidence should remain rule-based.

Potential AI uses include summarizing referral context, suggesting a service line from unstructured notes, identifying likely missing context, comparing free-text descriptions for possible duplication, drafting an internal briefing, and preparing a partner-update draft for human review.

The core automation creates the record, moves data between systems, sends reminders, and calculates proposed fees. AI adds value only where staff must interpret unstructured language.

The Recommended AI Enhancement

The recommended enhancement is an AI-generated internal referral summary with a suggested service-line classification and missing-information list. It does not accept, reject, prioritize, attribute, or price the referral.

Place the following system instruction in the AI module’s system-instruction field:

You assist a consultancy's revenue operations team with referral intake.

Summarize only the information supplied. Do not invent facts, contacts, needs, budgets, timing, relationships, or commercial terms.

You may suggest a service-line category and identify missing information. You must not accept or reject the referral, determine final attribution, assign final priority, approve a fee, provide legal conclusions, or draft an external commitment.

Return only JSON that matches the provided schema. Use concise business language. If information is uncertain, state that it is unknown and lower the confidence score.

Place the following reusable user prompt in the user-message field and map the placeholders from Salesforce:

Review this referral submission.

Referral ID: {{Referral_Number}}
Selected service interest: {{Service_Interest}}
Prospect industry description: {{Prospect_Industry_Description}}
Expected timing: {{Expected_Timing}}
Referral context: {{Sanitized_Referral_Context}}

Tasks:
1. Write an internal summary of no more than 80 words.
2. Suggest one allowed service-line category.
3. List information that appears necessary but missing.
4. List possible duplicate signals stated in the input, without deciding that a duplicate exists.
5. Return a confidence score from 0 to 1.
6. Set review_required to true in all cases.

Configure structured output against this JSON schema. The AI connector must support schema-constrained output, or Make must parse and validate the returned JSON before any Salesforce update:

{
  "type": "object",
  "additionalProperties": false,
  "properties": {
    "summary": {
      "type": "string",
      "maxLength": 600
    },
    "suggested_service_line": {
      "type": "string",
      "enum": [
        "Operations",
        "Data and Analytics",
        "Technology Enablement",
        "Other"
      ]
    },
    "missing_information": {
      "type": "array",
      "items": {
        "type": "string",
        "maxLength": 160
      },
      "maxItems": 8
    },
    "possible_duplicate_signals": {
      "type": "array",
      "items": {
        "type": "string",
        "maxLength": 160
      },
      "maxItems": 5
    },
    "confidence": {
      "type": "number",
      "minimum": 0,
      "maximum": 1
    },
    "review_required": {
      "type": "boolean",
      "const": true
    }
  },
  "required": [
    "summary",
    "suggested_service_line",
    "missing_information",
    "possible_duplicate_signals",
    "confidence",
    "review_required"
  ]
}

The prompt and schema are placed in the approved AI module in Make. The module requires a secured provider connection, permission to read the selected sanitized Salesforce fields, and write access only to separate AI suggestion fields.

Test with fictional referrals containing complete details, sparse context, conflicting language, prompt-injection text, and prohibited-data markers. If JSON parsing fails, an enum is invalid, or output exceeds a field limit, set AI Status to Invalid Output and leave the human workflow unchanged.

Do not send payment data, agreement documents, confidential proposal content, authentication data, regulated personal information, or sensitive client records to the model. Log the model identifier, request time, Referral ID, validation result, approximate usage, reviewer, and final disposition. Do not store hidden reasoning or request chain-of-thought output.

Benefits of the AI Enhancement

• Reduces the time spent reading long referral narratives.
• Produces a consistent internal summary structure.
• Suggests a service line when the partner used broad language.
• Highlights information that may need clarification.
• Surfaces duplicate indicators contained in unstructured text.
• Creates fields that can support later theme and service-interest reporting.

These are AI-specific benefits. Record creation, duplicate event prevention, Salesforce attribution, opportunity synchronization, fee calculation, reminders, and audit fields already exist in the core automation without AI.

What Remains Rule-Based or Human-Controlled

• Required fields: Form and Salesforce validation provide reliable deterministic checks.
• Exact duplicate events: Unique Submission Keys are more dependable than AI.
• Partner validity: The active Partner Profile determines eligibility.
• Final duplicate decision: A person reviews dates, agreements, relationships, and account history.
• Referral acceptance or rejection: This can affect a partner relationship and must remain human-controlled.
• Final attribution: Commercial agreements and evidence require accountable review.
• Opportunity stage and value: The sales owner remains responsible for CRM accuracy.
• Fee basis and rate: Approved agreement terms and finance evidence determine the calculation.
• Fee approval and payment release: Financial controls require authorized human approval.
• Partner communications: A person confirms that the update is accurate and does not disclose restricted information.
• Legal, tax, or policy exceptions: AI output cannot replace qualified review.

Estimating the Additional Value of AI

The representative comparison uses 45 referrals per month:

Additional time recovered: (10.00 – 8.59) × 45 ÷ 60 = 1.06 hours per month

Additional labour value: 1.06 × $48 = $50.88 per month

Less representative AI usage cost: $50.88 – $18 = $32.88 net additional monthly value

The direct financial value is modest at this volume. The enhancement may still be useful for consistent summaries and reporting, but it should not be adopted on the assumption that AI will eliminate review or errors. Its value becomes more material if narrative volume or average reading time increases.

Testing Checklist

Use fictional sample data in the test environment before processing real partner or prospect information.

Ongoing Maintenance

The revenue operations manager is the primary business owner. The marketing operations analyst is the backup operational owner. An automation administrator owns Make connections and technical recovery. Finance owns fee-control definitions.

Scenario changes should use version notes, peer review, test evidence, and a rollback copy. Documentation must be updated whenever fields, statuses, partner rules, approval thresholds, connectors, or error recovery steps change.

Monitor Salesforce API consumption, Make operations, Google Sheets size, Slack connection health, execution duration, retry volume, AI usage cost, and average exception age. Rising exception rates often indicate a data or process problem rather than a need for more retries.

When to Move to Dedicated Software

This implementation can remain appropriate while the program has manageable volume, straightforward fee rules, internal operating users, and limited partner self-service needs.

Consider dedicated partner relationship management, deal-registration, commission-management, or custom portal software when several of the following appear:

• Referral volume grows beyond the practical capacity of scheduled synchronization and manual exception review.
• Hundreds or thousands of partners require authenticated self-service.
• Partners need to see approved deal status, documents, training, or payment history directly.
• Multiple business units, regions, or currencies introduce complex attribution rules.
• One referral can create many products, renewals, tiers, or split fee arrangements.
• Regulatory or contractual requirements demand more formal approval, segregation of duties, or audit evidence.
• Google Sheets performance or permissions become difficult to manage.
• Integration maintenance consumes more time than the process saves.
• Exception rates remain high despite process improvements.
• Mobile, offline, or customer-facing portal requirements become important.
• Advanced partner scoring, enablement, incentive management, tax documentation, or payment integration is required.
• The organization needs formal vendor support and service commitments for the complete referral process.
• Security risk increases because too many systems contain duplicated sensitive information.

A dedicated platform is not automatically necessary at a particular record count. The decision should consider process complexity, exception rates, control requirements, integration reliability, administration effort, and partner experience.

Implementation Checklist

• Confirm referral, attribution, update, and fee-tracking requirements.
• Document which decisions remain human-controlled.
• Select the intake, system of record, automation, notification, storage, and reporting tools.
• Confirm Salesforce, Google Workspace, Make, and Slack account capabilities.
• Create non-personal integration connections and least-privilege permissions.
• Define Partner Profile, Referral, Opportunity, Activity, and Referral Fee fields.
• Create unique Submission Key, Referral Key, and Fee Key controls.
• Build and validate the Google Form intake.
• Protect the response sheet and working-tracker columns.
• Create Salesforce objects, relationships, statuses, formulas, validation, and history tracking.
• Configure field mapping between Forms, Sheets, Salesforce, and Slack.
• Build intake, Lead creation, Opportunity, activity, fee, reminder, and reconciliation scenarios.
• Configure duplicate checks before every create action.
• Add ownership, reassignment, and fallback rules.
• Configure approval thresholds, sequential approvals, reminders, and escalations.
• Define unavailable-approver and delegation handling.
• Configure document storage, naming, permissions, versioning, retention, and failed-upload handling.
• Create Salesforce reports and Google Sheets operational views.
• Add Automation_Errors and controlled manual-recovery procedures.
• Configure transient retries and permanent-error routing.
• Restrict sensitive fields, shared links, credentials, and Slack content.
• Test normal, duplicate, failure, approval, escalation, document, reporting, and audit paths.
• Run a pilot with fictional or controlled sample data.
• Document deployment, monitoring, rollback, and user-support procedures.
• Replace representative implementation and savings assumptions with measured figures.
• Add AI only after the core automation is reliable.
• Validate AI prompts, schema, data restrictions, human review, fallback, and cost monitoring.
• Name a primary maintenance owner and backup owner.
• Schedule permission, credential, integration, report, and exception reviews.
• Define the volume, complexity, security, and portal criteria that would justify dedicated software.