In our previous posts in this series, we started our base with a data model of Organizations, Contacts, and Interactions. We followed that up by adding a Web Leads table and a web form for capturing those leads on our website. Today, we are going to focus on tracking our sales opportunities.
Opportunities
Opportunities represent the potential sales for our organization. This may be a sale of products, services, a tracking of donations, or any other potential (financial) transaction. When it’s all said and done, our Opportunities table will include records that are Open (our pipeline), Won (yay!), and Lost (boo!). Having everything in one place allows us to track the success of our sales & marketing efforts.
Let’s get started with a data model for this table. When thinking about tracking an opportunity, it’s important to document fundamental things like who the opportunity is with, what kind of opportunity is it, when it will occur, and sales process and transaction information. It is also important to think about how an opportunity relates to a typical sale. Do you sell one product at a time or multiple products of different quantities? Does one sales person work a deal or does a team of people? These answers will drive how complex a data model you will have.
Who
Who is involved in the opportunity? A sale typically involves several parties so let’s have a field for each distinct group. In this example, we are a business selling to other businesses and interacting with the people at those businesses so we will track the following fields.
| Field Name | Data Type | Description |
|---|---|---|
| Organization | Link to Organizations | This defines the Organization that this Opportunity is related to and who we will be billing if this deal closes. Let’s turn off the “Allow linking to multiple records” setting so that only 1 organization is linked. |
| Related Contacts | Link to Contacts | This associates the Contacts involved in the opportunity. This will include some of the Contacts related to the Organization, but can also include any other Contact (like an attorney). Let’s turn on the “Allow linking to multiple records” setting so we can link multiple people to the Opportunity. |
| Owner | Collaborator | This will define the internal user at our company that is responsible for the Opportunity. This would typically be the sales executive running the deal. Let’s turn off the “Allow linking to multiple records” setting and turn off “Notify collaborators when they’re added”. |
| Created By | Created By | Who created this Opportunity. |
| Last Modified By | Last Modified By | Who last edited this opportunity. |
What
What kind of Opportunity is this? We want to categorize the opportunity so that we can report on subsets of opportunities over time.
| Field Name | Data Type | Description |
|---|---|---|
| Name | Single Line Text | Use the default “Name” field. This represents the Opportunities name (or title). Many companies will use a naming convention for this. In this example, I am setting it up as a Single Line Text, but you could change that to a formula or use an Automation to force a naming convention. |
| Type | Single Select | This defines the Type (or Category) for the Opportunity. It’s meant to be a field used in reporting to help group like opportunities together. You can take any approach to the Type of opportunity. For this post, we will use the values “New”, “Upsell”, and “Renewal” as if we are selling a SaaS app. |
| Product | Single Select | This defines the product or service we are selling in this Opportunity. In this post, we are keeping it simple by not having opportunity line items. Thus, an Opportunity will only be for 1 product or service and we can accomplish this with a Single Select field. |
For simplicity in this post, we are going to setup Products as a single select field. However, Product is probably a field that warrants having its own table so that you can track different attributes across different products you sell and you can reuse the product records across other tables you create down the road such as Assets, Subscriptions, Support Cases, and more. In a future post, I plan to talk about this topic and to decide whether a field belongs as a simple single-select or whether a new table is warranted.
When
When is this Opportunity occurring?
| Field Name | Data Type | Description |
|---|---|---|
| Close Date | Date | When the Opportunity is expected to Close as either a win or loss. Once won or lost, this field documents when that win or loss occurred. |
| Age | Formula | The number of days this Opportunity has been open or, if closed, how long it took to close it.MAX(0,SWITCH({Stage Category}, "Open", DATETIME_DIFF(TODAY(),Created,'days') + 1, DATETIME_DIFF({Close Date},Created,'days') + 1))* The formula is measuring Age as Today minus Created Date for open opportunities or Close Date minus Created Date for Closed opportunities. * The reason we add + 1 to the DATETIME_DIFF result is that our Close Date is just a Date and not a datetime. Date fields assume a midnight time on that day, so we add a day to the result. * The Stage Category field in the formula is defined later in the post and results in either “Open”, “Won” or “Lost” depending on the Stage. * The MAX() function is in the formula so that we don’t have negative numbers. |
| Created | Created Time | When was this Opportunity created. |
| Last Modified | Last Modified Time | When was this Opportunity last modified. If you like, Airtable lets you remove certain fields from the logic so you can know when it was last modified in only specific fields (e.g. only track Close Date, Amount, Stage, Owner). If you do this, I’d align the Last Modified By field above with this one so that the two correspond with each other. |
Sales Process & Transaction Details
We want to track our progress through the sales process with a few fields.
| Field Name | Data Type | Description |
|---|---|---|
| Stage | Single Select | The Sales Stage for the Opportunity. Any good sales organization will define a sales methodology. This field tracks the steps in that methodology. The ones I defined in this example are: * Prospect * Connect * Discovery * Quote Submitted * Negotiation / Review * Closed Won * Closed Lost |
| Stage Category | Formula | I always find it helpful to have a way to classify opportunities into 3 main status buckets: open, won, lost. Below is a formula to do this.IF(FIND("Closed Won",Stage)>0,"Won",IF(FIND("Closed Lost",Stage)>0,"Lost","Open"))NOTE: Some companies may like to have additional Closed Won and Closed Lost sales stages to support reasons such as “Closed Lost – Competition”, “Closed Lost – Budget”, “Closed Lost – Bad Timing”, etc. If this is done, this formula is unaffected because we are using the FIND() function in our formula. |
| Lead Source | Single Select | Documents the source of the opportunity to help determine the success of marketing efforts. The placeholder values I put in are: * Web Lead * Networking * Event: ABC Conference * Event: BCD Conference * Event: CDE Conference * Client Request |
| Quantity | Number (Integer) | The number of units of the product that will be sold. |
| Unit Price | Currency | The price per unit of the product that will be sold |
| Amount | Currency | The total amount of the opportunity. You have some options here. I opted to make it a field that needs to be typed into. This is especially handy during the early stages when you may not know the quantities or prices of your product being sold. Another option is to make it a formula of Quantity * Unit Price. |
| Attachments | Attachment | It is always handy to have the ability to add file attachments to a record. |
Views
Airtable makes creating Views of your data super easy. We have not covered this yet in this series, so let’s start now since Opportunities provide a good use case for Views . Views are managed along the left side of Airtable and consist of Fields, Groups, Filters, and Sorts. They can also have different styles such as Grids, Kanban, Calendar,
Below are the views I setup for my CRM system. The All Opportunities view is a simple grid with no filters. The only thing I did, which is not really that important is use the Color option to show a color on the left edge of the record to give me a hint as to the Product being sold.
The Pipeline view has a filter for Stage Category = “Open” and then groups the Opportunities by Stage.
The Pipeline Calendar view uses Close Date for the calendar date and has a filter for Stage Category = “Open”. The Win Calendar is the same except the filter is for “Won”.
The Wins and Losses views are a copy of the All Opportunities view with an added filter for Stage Category = “Won” or “Lost”, respectively.
Review
Up to this point, we have done the following in our CRM:
- Created a base set of objects for Organizations, Contacts, and Interactions (the first post)
- Created a new table to house leads that we capture from our website (the previous post)
- Created an Airtable Form to add to our website that will take in the lead information (the previous post)
- Created an Automation to clean up the lead record to set the Status to New (the previous post)
- Created an Opportunities table to track our sales and marketing success (this post)
Our updated schema looks like this.
Next we will add some automation into the system to help us process a Web Lead that comes in and bring it to our primary CRM tables.
Comments