Do you need a quick snapshot of your Sales pipeline in Airtable? Here’s how to do it.
Tracking your Sales funnel can make a positive impact on growth. You always know about open opportunities and any projects that were won or lost.
With the Scripting App in Airtable, I’ll show you how to create a custom dashboard displaying your Sales Funnel and Top Sales Reps.
You can stay on top of relationships, interactions with clients, the number of days it takes to close a sale, and which reps are knocking it out of the park with consistent sales.
You can even use automations or forms in your Sales CRM to capture new leads directly to your base or automatically email your leads and clients.
Table of Contents
What can the Scripting app do?
About the Sales Rep Leaderboard and Sales Funnel scripts
How to add script to your Airtable base
Add the variables and utility functions
Add and view the Sales Rep Leaderboard script
Add and view the Sales Funnel script
In this article
We’ll set up a Sales Dashboard in your Airtable base showing the number of leads and opportunities in each status, current sales rep leaders, and the total potential revenue for the leads. After you’ve added the scripts, you can connect a form to capture custom data in your base.
The dashboard will be created using the Scripting App in Airtable with 2 scripts you can add to your base.
About scripts in Airtable
Scripts in Airtable are a way for you to add custom features to your bases without having to use another tool.
You don’t have to be a software developer and know JavaScript to use scripts. The Airtable community and Airtable have shared a variety of scripts for you to add to your base and start using immediately. You typically only need to add the name of your tables and fields to the script.
Adding scripts in your bases can take your business data to a whole new level with custom insights and special features your company needs.
What can the scripting app in Airtable do?
In Airtable, you may have multiple bases helping run your company. You have a base for Sales, Marketing, Content, Finances, Production, and IT info. With scripts, you can customize any of your bases. If you have multiple scripts to manage in several of your bases, On2Air: Scripts is a powerful script manager.
With scripts, you can
- Create custom reports
- Create special features for your data that Airtable doesn’t have
- Clean up your data with custom utilities
- Create custom processes
- Connect to external software
- Validate your data to make sure it matches specific criteria
- Create unlimited possibilities for your bases!
Sales Rep Leaderboard and Sales Funnel scripts
Let’s create a couple of Scripts to use with our Sales base in Airtable.
You can copy this Sales base into your Airtable workspace, add the code to your base and match your field names to the code, or you can make some small changes in your own base that I’ll show you later in the article.
While a basic understanding of JavaScript and Airtable-specific classes can help you understand certain functions, it’s not necessary to know to implement these scripts. You will need to change some terms if you use your own base.
The first script will display the Top Sales Reps in a Leaderboard with the dollar amount and the number of sales totaling the amount.
The second script will display a snapshot of the Sales Funnel stages, the number of leads in each stage, and the total dollar amount for each stage.
Before creating this script, you need:
At least 2 tables in your base – Sales Reps and Leads
In the Sales Rep table, you need the following fields:
- Name (text field type)
- Relation to the Leads table
In the Leads table, you need the following fields:
- Name (text field type)
- Amount (currency field type)
- Relation to the Sales Reps table
- Status (single select field type with an option of ‘Won’)
How to add Scripts to your Airtable base
Now, we’re going to add the scripts to the base. If you haven’t ever used the Scripting app in Airtable, here’s how you add scripts to your base.
- Open your base
- Click the APPS button in the top right side of your base
- Click Install an app
- Click Scripting
- Select the blue Install button
- Click outside of the popup box
- Write or Paste your code into the text box on the left
- Click the blue Run button
1. Add the Variables
The variables are used to determine the 2 tables and the Status types that will be used in the script.
- Copy code for variables
- Click Edit Code in Scripting app
- Paste code into the text editor
Code
const tableLeads = base.getTable('Leads')
const tableReps = base.getTable('Sales Reps')
const statuses = ["Pitched","In progress","Won","Lost"]
2. Add Utility functions
There are 4 utility functions in this section. They do tasks such as grab single records from a table, determine days between dates, get single fields from a record, and get a filtered subset of records from a table.
- Copy code for utility functions
- Paste code into text editor below the variables code
Code
const getSingleRecord = async (table, id) => {
let records = await table.selectRecordsAsync( )
return records.getRecord(id)
}
const daysBetween = (date1,date2) => {
date1 = new Date(date1)
date2 = new Date(date2)
date1 = Date.UTC(date1.getFullYear(), date1.getMonth(), date1.getDate());
date2 = Date.UTC(date2.getFullYear(), date2.getMonth(), date2.getDate());
var ms = Math.abs(date1-date2);
return Math.floor(ms/1000/60/60/24)
}
const get = (record, field, raw = false, ifNullValue = "-") => record ? (raw ? record.getCellValue(field) : record.getCellValueAsString(field)) : ifNullValue
const getRecords = async (table, filter = null, sorts = []) => {
let records = await table.selectRecordsAsync( {sorts} )
if(filter){
let filtered = []
for(let record of records.records){
if(filter(record)){
filtered.push(record)
}
}
return filtered
}
return records
}
3. Add the Sales Rep Leaderboard script
This script filters all ‘Won’ statuses in the Leads table.
Then, it calculates the lead amounts according to the rep who has the highest dollar amount of leads.
It then determines the total number of ‘Won’ leads per rep, then sorts and displays the leaderboard.
- Copy code for Sales Rep Leaderboard script
- Paste code into text editor a line or two below the Utilities code
Code
const salesLeaders = async () => {
//get won deals
let records = await getRecords( tableLeads, record => {
return get(record,'Status') == 'Won'
})
//group won deals by reps
let rankings = {}
for(let record of records){
let rep = record.getCellValueAsString('Sales Rep')
//if first time, create entry
if(!rankings[rep]){
rankings[rep] = {"Sales Rep":rep,Total:0,"Sales Count":0}
}
rankings[rep].Total += record.getCellValue('Amount')
rankings[rep]["Sales Count"]++
}
//sort winners and format $ amount
let winners = Object.values(rankings)
.sort( (a,b) => a.Total > b.Total ? -1 : 1)
.map( entry => {
entry.Total = "$" + entry.Total.toLocaleString()
return entry
})
output.markdown('## Top Sales Leaders')
output.table(winners)
}
await salesLeaders()
4. View the Sales Rep Leaderboard
- Click Run
You should see a table containing your Sales Leaderboard!
5. Add the Sales Funnel script
This script grabs all the Lead Statuses, counts them, and calculates the total amount for each Status
- Click Edit code to get back to the text editor
- Copy code for Sales Funnel script
- Paste code into text editor a line or two below the Sales Leaderboard code
Code
const salesFunnel = async () => {
let leads = await getRecords(tableLeads, record => true)
let funnel = {}
for(let lead of leads){
let status = get(lead,"Status")
if(!funnel[status]){
funnel[status] = {
Status:status,
Count:0,
Amount:0
}
}
funnel[status].Count++
funnel[status].Amount += get(lead,"Amount",true)
}
funnel = Object.values(funnel)
.sort( (a,b) => statuses.indexOf(a.Status) > statuses.indexOf(b.Status) ? 1 : -1)
.map( entry => {
entry.Count = entry.Count.toLocaleString()
entry.Amount = "$" + entry.Amount.toLocaleString()
return entry
})
output.markdown('## Sales Funnel')
output.table(funnel)
}
await salesFunnel()
6. View the Sales Funnel table
- Click Run
You should see a table containing your Sales Funnel (and the Sales Leaderboard)!
Adapt to your base
You can adapt the scripts to fit your Sales base. You need to change the tables, fields, and field values.
For the variables, you need to make the following changes
For the Sales Funnel script, you need to make the following changes
For the Sales Leaderboard script, you need to make the following changes
Growing your Sales
That’s it!
You now have a Sales Dashboard containing your Sales Rep Leaderboard and a Sales Funnel status with totals. This will allow you to quickly glance all the important sales info for your team or business.
As you’re using Airtable to keep your business sales growing, start a trial with On2Air, our suite of apps for Airtable, to make it even better. On2Air allows you to create advanced forms, automatically backup your bases, and connect your bases to other bases or any other software.
On2Air: Scripts - Script Management App
Do you use scripts in more than 1 base? Do any of your scripts use some of the same code?
Need more control and organization for your scripts?
Scripts in Airtable allows you to add JavaScript code to interact with the data in your base. If you have multiple scripts or you use the same script in multiple bases, it can get messy trying to maintain each one.
With On2Air: Scripts, you can edit, organize, and maintain multiple scripts for multiple bases from a single codebase.
Start a free trial of On2Air: Scripts and all On2Air apps today!
