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

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 Name	Data Type	Description
Converted Organization	Link to Organizations	Update this field to it only allows for 1 record to be linked.
Converted Contact	Link to Contacts	Update this field to it only allows for 1 record to be linked.
Converted Opportunity	Link to Opportunities	Update this field to it only allows for 1 record to be linked.
Converted Date	Date with Time enabled
Convert Lead	Button	See below for how this field should be configured.
Interactions	Link to Interactions	Keep 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.**`);
         }
     }
     
}