Airtable: Creating a CRM (Part 4) – Convert the Web Lead

This is part of a multi-part series on creating a CRM in Airtable.

In our previous posts in this series, we started our base with a data model of Organizations, Contacts, and Interactions, then followed that up by adding a Web Leads table and a web form for capturing those leads on our website, and then we created an Opportunities table to track our pipeline, wins & losses. In this post, we are going to build a process for taking the web lead data and bringing it into the main data model.

The concept of having Leads in a separate table and then “converting” those leads to other tables (one qualified) is one that comes from my time using Salesforce.

New / Changed Fields

Web Leads

To accommodate this script, we are going to add some fields to the Web Leads table.

Field NameData TypeDescription
Converted OrganizationLink to OrganizationsUpdate this field to it only allows for 1 record to be linked.
Converted ContactLink to ContactsUpdate this field to it only allows for 1 record to be linked.
Converted OpportunityLink to OpportunitiesUpdate this field to it only allows for 1 record to be linked.
Converted DateDate with Time enabled
Convert LeadButtonSee below for how this field should be configured.
InteractionsLink to InteractionsKeep this field as allowing multiple linked records

Other Changes

  • Add a value of “Web Lead” as an Interaction Type

Button Field Configuration

To invoke the process, we are going to add a button field to the Web Leads table. By choosing the Run script action, Airtable will want us to have an instance of the Scripting app in the system. Apps are only available on the Pro plan, so you will need that (or a trial of it) in order to complete the work in this post. I named my Scripting App “Convert Web Lead”

Script Flow

The Convert Lead button field is on the Web Lead record now. Clicking that button will invoke the script in the scripting app. As you can see, I created 2 new views: Open Leads and Converted Leads. When the script is successful, it will mark the Web Lead with a status of Converted. This will cause that lead to fall off the Open Leads view.

1) Match a Contact

When the script runs, it is going to determine whether or not we already have a contact in the system for this lead. We don’t want to create a duplicate. It will do this by:

  • Matching the lead email with all contact email fields
  • Match the lead name with an existing contact name

If it finds some Contacts, it will present the user with what you see below. The user will use the picker to pick a record.

If it cannot find a match, the script just continues without asking the user, assuming it will be creating a new Contact record.

2) Match the Organization

If the user matched a Contact, the system will note that Contact’s organization. The system will also:

  • Match the lead organization name with an existing organization name

If it finds some Organizations, it will present the user with what you see below. The user will use the picker to pick a record.

If it cannot find a match, the script just continues without asking the user, assuming it will be creating a new Organization record.

3) Ask about Opportunity Creation

The user is presented with a prompt whether they’d like an Opportunity created?

4) Ask about Interaction Creation

The user is presented with a prompt whether they’d like an Interaction created? It would be recommended to create one.

5) Confirm actions to be taken

Before the script actually interacts with the system’s data, it will confirm what will happen next.

The Results

The Web Lead was updated with a status of Converted and with links to all of the records involved in the process.

In this example, the script was able to find the Organization and Contact in the system, so no new records were created, but it did create a new Opportunity and Interaction record.

The Opportunity represents the potential sale.

The Interaction documents that the web lead actually took place and is viewable from all records involved.

In this CRM series, we have not yet gotten to To Dos (i.e. Tasks). Once we do, I’d want to update this script to have a “Respond to Web Lead” To Do assigned to the user converting the lead.

The Script

This script should work as long as you created all of the fields in your base as I have them labelled in this blog post series.

// Define primary table and select a record from it 
let table = base.getTable("Web Leads");
let record = await input.recordAsync('Select a record to convert...', table); // A button field will automatically bypass this UI step and select the record where the button was clicked

// Get other tables into memory
let conTable = base.getTable("Contacts");
let orgTable = base.getTable("Organizations");
let oppTable = base.getTable("Opportunities");
let intTable = base.getTable("Interactions");


if (!record) { output.markdown('**A record was not selected. Please try again.**'); // handle exception - no record selected
} else { // a record was selected
    if(record.getCellValueAsString("Status") == "Converted"){ output.markdown(`**This record has already been convertred and cannot be converted again**`); // handle exception - already converted
    } else { // MAIN PROCESSING CODE AFTER PASSING A COUPLE CHECKS
        
        // Pull values from the lead record to use for comparing to other records
        let leadName = record.name;
        let leadEmail = record.getCellValueAsString("Email");
        let leadOrg = record.getCellValueAsString("Organization Name");
        let leadPhone = record.getCellValueAsString("Phone");
        let leadNotes = record.getCellValueAsString("Notes");
        let leadCreated = record.getCellValue("Created"); // important to not get the value as a string. Use getCellValue() instead
        
        // Arrays to hold matches
        let potentialOrgMatches = [];
        let potentialConMatches = [];
        let orgIds = []; // list of organization ids from the matched contacts. We use this to help determine the list of Organizations

        // QUERY CONTACTS AND FIND POTENTIAL MATCHES
        let conQuery = await conTable.selectRecordsAsync({
            fields: ["Name", "Email", "Alt Email", "Organizations"]
        });

        // Loop through Contacts
        for (let theContact of conQuery.records) {
            
            // Get Contact field values
            let conEmail = theContact.getCellValueAsString("Email");
            let contAltEmail = theContact.getCellValueAsString("Alt Email");
            let conOrgs = theContact.getCellValue("Organizations");

            // Compare contact email with lead email
            if( conEmail != ""){
                if (conEmail == leadEmail){
                    potentialConMatches.push(theContact); // add contact as a potential match
                    if(conOrgs != null){
                        for (let theConOrg of conOrgs){
                            orgIds.push(theConOrg.id); // add contact's org as a potential org to link to
                        }
                    }
                }
            }

            // Compare contact alt email with lead email 
            if(contAltEmail != ""){
                if (contAltEmail == leadEmail){
                    potentialConMatches.push(theContact);  // add contact as a potential match
                    for (let theConOrg of conOrgs){
                        orgIds.push(theConOrg.id); // add contact's org as a potential org to link to
                    }
                }
            }

            // Compare Contact name with Lead name
            if(theContact.name != ""){
                if (theContact.name == leadName){
                    potentialConMatches.push(theContact);  // add contact as a potential match
                    for (let theConOrg of conOrgs){
                        orgIds.push(theConOrg.id); // add contact's org as a potential org to link to
                    }
                }
            }
            
        }

        // QUERY ORGANIZATIONS AND FIND POTENTIAL MATCHES
        let orgQuery = await orgTable.selectRecordsAsync({
            fields: ["Name"]
        })

        // Loop through Organizations
        for (let theOrg of orgQuery.records) {

            // Compare name values with each other
            if (theOrg.name == leadOrg){
                potentialOrgMatches.push(theOrg); // add org as a potential match
            }

            // Compare Id values with the Org IDs from the matching Contacts
            if(orgIds.includes(theOrg.id)){
                potentialOrgMatches.push(theOrg); // add org as a potential match
            }
        }


        // HANDLE MATCH SCENARIOS AND BUILD UI PROCESS ACCORDINGLY
        let selectedOrg = null; // assume no record is selected and we create a new organization
        let selectedCon = null; // assume no record is selected and we create a new contact

        // Proceed through the potential Contact matches
        if(potentialConMatches.length > 0){
            
            // Have user select a Contact. If they don't select one, the selectedCon will stay null
            selectedCon = await input.recordAsync(`Potential Contact matches were found. Use the picker below to choose a matching Contact If you would like to create a new Contact, please open the picker and then cancel out of it.`, potentialConMatches);
            
            // Redetermine the potential orgs if a contact was selected
            if(selectedCon != null){
                
                // Get the Org IDs for this Contact into an array
                orgIds = []; // clear orgIds array
                if(selectedCon.getCellValue("Organizations") != null){
                    for (let theConOrg of selectedCon.getCellValue("Organizations")){
                        orgIds.push(theConOrg.id);
                    }
                }

                // Pare down potentialOrgMatches[] to only the orgs from the selected Contact
                let selectedConOrgMatches = []; 
                for (let theOrg of potentialOrgMatches){
                    if(orgIds.includes(theOrg.id)){
                        selectedConOrgMatches.push(theOrg);
                    }
                }
                if(selectedConOrgMatches.length == 1){ // Selected contact only has 1 org
                    selectedOrg = selectedConOrgMatches[0];
                } else if (selectedConOrgMatches.length > 1){ // Selected Contact has many Organizations
                    potentialOrgMatches = []; // clear the main matches array
                    potentialOrgMatches = selectedConOrgMatches;
                }
            }

        }

        
        if(
            (selectedCon == null && potentialOrgMatches.length > 0) ||  // No Contact selected and there are potential Org matches to choose from
            (selectedCon != null && selectedOrg == null && potentialOrgMatches.length > 0) // A Contact was selected, but an org hasn't been selected yet
        ){ 
            selectedOrg = await input.recordAsync(`Potential Organization matches were found. Use the picker below to either choose a matching Organization or cancel out of the picker to indicate a new Organization should be created.`, potentialOrgMatches);
        }

        // Ask if we need to create an interaction and/or opportunity
        let bOpportunity = await input.buttonsAsync('Would you like a new Opportunity record to be created?', ['Yes', 'No']);
        let bInteraction = await input.buttonsAsync('Would you like a new Interaction record to be created to document the Web Lead that took place?', ['Yes', 'No']);

        // Final IDs
        let finalOrg = null;
        let finalOrgName = '';
        let finalCon = null;
        let finalOpp = null;
        let finalInt = null;
        
        // Based upon selections, confirm that the user would like to proceed
        output.markdown(`**Based upon this information, the following will happen next:**`);
        if(selectedOrg == null){ 
            output.markdown(`* A new Organization named **` + leadOrg + `** will be created.`); 
        }
        if(selectedCon == null){
            output.markdown(`* A new Contact named **` + leadName + `** will be created.`); 
            if(selectedOrg == null){ 
                output.markdown(`* This new Contact will be linked to the new **` + leadOrg + `** Organization being created.`); 
            } else {
                output.markdown(`* This new Contact will be linked to the existing **` + selectedOrg.name + `** Organization record.`); 
                finalOrg = selectedOrg.id;
                finalOrgName = selectedOrg.name;
            }
        } else {
            output.markdown(`* The existing Contact **` + selectedCon.name + `** will be used. A new Contact will **not** be created.`); 
            finalCon = selectedCon.id;
            if(selectedOrg != null){ 
                output.markdown(`* The existing Organization **` + selectedOrg.name + `** will be used. A new Organization will **not** be created.`); 
                finalOrg = selectedOrg.id;
                finalOrgName = selectedOrg.name;
            }
        }

        output.markdown(`* The ` + leadName + ` Web Lead will be marked as **Converted** and linked to the Organization and Contact.`);

        if(bOpportunity == "Yes"){
            output.markdown(`* A new Opportunity will be created.`);
        } else if (bOpportunity == "No"){
            output.markdown(`* A new Opportunity will **not** be created.`);
        }

        if(bInteraction == "Yes"){
            output.markdown(`* A new Interaction will be created.`);
        } else if (bInteraction == "No"){
            output.markdown(`* A new Interaction will **not** be created.`);
        }

        let bContinue = await input.buttonsAsync('Would you like to continue converting this lead? If you choose Yes, your system\'s data will be affected.', ['Yes', 'No']);
        if(bContinue == "Yes"){
            
            // CREATE & MODIFY DATA AS NEEDED

            // Create Org
            if(finalOrg == null){
                finalOrg = await orgTable.createRecordAsync({
                    "Name": leadOrg
                });
                finalOrgName = leadOrg;
                output.markdown(`The new account can be found [here](https://airtable.com/` + base.id + `/` + orgTable.id + `/` + finalOrg + `)`);
            }
            
            // Create Contact
            if(finalCon == null){
                finalCon = await conTable.createRecordAsync({
                    "Organizations": [{id: finalOrg}],
                    "Name": leadName,
                    "Email": leadEmail,
                    "Work Phone": leadPhone
                });
                output.markdown(`The new contact can be found [here](https://airtable.com/` + base.id + `/` + conTable.id + `/` + finalCon + `)`);
            }

            if(bOpportunity == "Yes"){
                let currentDate = new Date();
                finalOpp = await oppTable.createRecordAsync({
                    "Organization": [{id: finalOrg}],
                    "Related Contacts": [{id: finalCon}],
                    "Owner": {id: session.currentUser.id},
                    "Name": finalOrgName + "- Opportunity from Web Lead",
                    "Type": {name: "New"},
                    "Stage": {name: "Prospect"},
                    "Lead Source": {name: "Web Lead"},
                    "Close Date": new Date(currentDate.getFullYear(), currentDate.getMonth(), currentDate.getDate() + 30)
                });
                output.markdown(`The new opportunity can be found [here](https://airtable.com/` + base.id + `/` + oppTable.id + `/` + finalOpp + `)`);
            }
            
            if(bInteraction == "Yes"){
                let finalIntObject = {
                    "Organizations": [{id: finalOrg}],
                    "Contacts": [{id: finalCon}],
                    "Web Leads": [{id: record.id}],
                    "Type": {name: "Web Lead"},
                    "Subject": "Web Lead Submitted",
                    "Notes": leadNotes,
                    "Date & Time": leadCreated
                };
                if(bOpportunity == "Yes" && finalOpp != null) {
                    finalIntObject["Opportunities"] = [{id: finalOpp}];
                }
                finalInt = await intTable.createRecordAsync(finalIntObject);
                output.markdown(`The new interaction record can be found [here](https://airtable.com/` + base.id + `/` + intTable.id + `/` + finalInt + `)`);
            }

            let updateObject = {};
            updateObject["Status"] = {name: "Converted"};
            updateObject["Converted Organization"] = [{id: finalOrg}];
            updateObject["Converted Contact"] = [{id: finalCon}];
            updateObject["Converted Date"] = new Date();
            if(bOpportunity == "Yes"){
                updateObject["Converted Opportunity"] = [{id: finalOpp}];
            }
            table.updateRecordAsync(record.id, updateObject);


        } else if (bContinue == "No"){
            output.markdown(`**The operation was cancelled. No system changes were made.**`);
        }
    }
    
}

One thought on “Airtable: Creating a CRM (Part 4) – Convert the Web Lead

  1. Pingback: Airtable: Creating a CRM (Part 5) – Task Management | Scott Hemmeter

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s