How to Perform a Cohort Analysis Using a Salesforce Opportunity Report‍

‍

Cohort analysis is a key technique in sales analytics, providing a clear view of customer behavior over time. By grouping customers into cohorts based on their first purchase dates, we can track how these groups change and evolve. This approach reveals crucial insights into customer retention, purchase frequency, and shifts in spending patterns, which are essential for strategic decision-making in sales.

‍

Cohort: A group of customers who have made their first purchase or interaction during the same timeframe.

Cohort Analysis: Analyzing the behavior and performance of groups over time to identify trends, patterns, and outcomes specific to these segmented groups.

‍

A Real-World Exercise

In this guide, we'll demonstrate how to perform a cohort analysis using a simple opportunity report from Salesforce.

‍

Analysis Objective: Understand how deal sizes and lifetime value has changed over time.‍

Technology Stack

While we use the Analyst Intelligence Platform, that leverages Google BigQuery and Metabase, this guide is transferable for those with different tech stacks.

• Analyst Intelligence Platform (the AIP)
• Google BigQuery (via the AIP)
• Metabase Business Intelligence (via the AIP)

‍

Why not Excel or other tools?

Aside from being much slower, Excel is not case-sensitive, which can lead to non-unique Account Identifiers and distorted analysis. The Analyst Intelligence Platform, built on Google BigQuery, is case sensitive so it handles Salesforce reporting out of the box. If visualizing with an Excel heatmap or table, the underlying details are unavailable and the visualization efforts are quite high.

‍

{{cta-component}}

‍

‍

Start With a Basic Opportunity Report from Salesforce

Effective cohort analysis begins with the right dataset. In our case, we're extracting data from Salesforce, focusing on key fields that are crucial for our analysis.

‍

‍

Generate Your Opportunity Report With These Essential Fields

• Opportunity Name
• Account ID
• Account Name
• Stage
• Close Date
• Amount

These fields provide the foundational data for analyzing sales performance and customer behavior.

‍

Additional (optional) Fields for Slicing & Dicing Your Data

Depending on your analytical goals, consider adding extra columns for more nuanced analysis, enabling you to slice the data in various meaningful ways. Some common fields are below:

• Industry or Vertical
• Lead Source
• Team
• Product
• Opportunity Age

‍

Here's a Step-by-Step Breakdown to Calculate the Cohort Analysis Columns

This section provides the essential SQL queries needed to set up your cohort analysis. These queries are designed to be used in BigQuery via the Analyst Intelligence Platform, but the template can be used anywhere.

‍

Note: We're using SQL so you can copy the code, but if you're using AIP you can use the no-code interface.

‍

Step 1 - Add your Customer Cohort Columns

‍

‍

The SQL code below clears the cohort columns:

-- Add the "Customer Cohort Date" column
ALTER TABLE `Opp Report Example`
ADD COLUMN IF NOT EXISTS `Customer Cohort Date` DATE;

-- Add the "Customer Cohort Year" column
ALTER TABLE `Opp Report Example`
ADD COLUMN IF NOT EXISTS `Customer Cohort Year` STRING;

-- Add the "Customer Cohort Yr Qtr" column
ALTER TABLE `Opp Report Example`
ADD COLUMN IF NOT EXISTS `Customer Cohort Yr Qtr` STRING;

-- Add the "Months Since First Deal" column
ALTER TABLE `Opp Report Example`
ADD COLUMN IF NOT EXISTS `Months Since First Deal` FLOAT64;

-- Add the "Quarters Since First Deal" column
ALTER TABLE `Opp Report Example`
ADD COLUMN IF NOT EXISTS `Quarters Since First Deal` FLOAT64;

-- Add the "Years Since First Deal" column
ALTER TABLE `Opp Report Example`
ADD COLUMN IF NOT EXISTS `Years Since First Deal` FLOAT64;

‍

Step 2 - Clear the Cohort Columns in Case You Are Rerunning

Optional step: I like to clear out the analytical columns at the beginning of my process as a precautionary step.

‍

‍

Below is the SQL code for clearing the cohort columns:

UPDATE `Opp Report Example`
SET
`Customer Cohort Date` = NULL,
`Months Since First Deal` = NULL,
`Quarters Since First Deal` = NULL,
`Years Since First Deal` = NULL
WHERE TRUE; -- Where true is for BigQuery

‍

Step 3 - Update the Cohort Date

The following query calculates the cohort date based on the first opportunity that is Closed Won in the system.

‍

‍

Below is the SQL code for updating the cohort date column:

-- Populate the "Customer Cohort Date" column with the date of the first 'Closed Won' opportunity for each account.
UPDATE `Opp Report Example` AS main
SET main.`Customer Cohort Date` = cohort.first_won_date
FROM (
 SELECT `Account ID`, MIN(`Close Date`) as first_won_date
 FROM `Opp Report Example`
 WHERE `Stage` = 'Closed Won'
 GROUP BY `Account ID`
) AS cohort
WHERE main.`Account ID` = cohort.`Account ID`;

‍

Step 4 - Update the Cohort Year and Quarter for Business Intelligence

The following query takes the cohort date, which is the date the first opportunity was won for each customer, and translates it into the quarter and year for use in visualizing the data later. These are the year and quarter cohorts.

‍

‍

Below is the SQL code for translating the date into the quarter and year format for business intelligence:

-- Update the "Customer Cohort Yr Qtr" with the formatted "Customer Cohort Date"
UPDATE `Opp Report Example`
SET `Customer Cohort Yr Qtr` =
 FORMAT_DATE('%y-Q', `Customer Cohort Date`) ||
 CAST(EXTRACT(QUARTER FROM `Customer Cohort Date`) AS STRING)
WHERE `Customer Cohort Date` IS NOT NULL;

-- Update the "Customer Cohort Year" with the year from "Customer Cohort Date"
UPDATE `Opp Report Example`
SET `Customer Cohort Year` = CAST(EXTRACT(YEAR FROM `Customer Cohort Date`) AS STRING)
WHERE `Customer Cohort Date` IS NOT NULL;

‍

Step 5 - Update the Time Passed Since First Deal Was Closed Won

The purpose of this task is to determine the time elapsed since the first deal, in months, quarters, and years.

‍

‍

Below is the SQL code for calculating the time passed since the first deal closed:

-- Update the "Months Since First Deal" with the number of months between "Close Date" and "Customer Cohort Date"
UPDATE `Opp Report Example`
SET `Months Since First Deal` = DATE_DIFF(DATE(`Close Date`), DATE(`Customer Cohort Date`), MONTH)
WHERE `Customer Cohort Date` IS NOT NULL AND `Close Date` IS NOT NULL;

-- Update the "Quarters Since First Deal" with the number of quarters between "Close Date" and "Customer Cohort Date"
UPDATE `Opp Report Example`
SET `Quarters Since First Deal` = DATE_DIFF(DATE(`Close Date`), DATE(`Customer Cohort Date`), QUARTER)
WHERE `Customer Cohort Date` IS NOT NULL AND `Close Date` IS NOT NULL;

-- Update the "Years Since First Deal" with the number of years between "Close Date" and "Customer Cohort Date"
UPDATE `Opp Report Example`
SET `Years Since First Deal` = DATE_DIFF(DATE(`Close Date`), DATE(`Customer Cohort Date`), YEAR)
WHERE `Customer Cohort Date` IS NOT NULL AND `Close Date` IS NOT NULL;

‍

These queries lay the groundwork for you to derive meaningful insights about your sales over time and set us up for visualization.

‍

Now the Fun Part: Visualization

Open up your favorite visualization tool - ours is Metabase, followed by Google's Looker Data Studio. If you use the Analyst Intelligence Platform then your data is instantly available.

‍

‍Analyzing Bookings by Cohort Year and Quarter

This section delves into the analysis of bookings over time, segmented by cohort year and quarter. By setting the stage to 'Closed Won,' we focus on successful deals and track the cumulative sum of amounts against the time elapsed since the first deal, visualized by cohort year.

‍

One thing I love about Metabase is their no-code interface for creating awesome visualizations. Regardless of the tool, configure that axis like this:

‍

‍

Configuring the Chart:

• The X-axis represents quarters passed since the first deal, offering a timeline of customer engagement.
• The Y-axis shows the cumulative booking amount, indicating the revenue growth from each cohort.
• Lines on the chart represent each Customer Cohort Year, allowing for comparison between different cohort years within the same timeframe.

‍

‍

Benefits of this Analysis:

• Identify trends in customer spending over time
• Evaluate the effectiveness of sales strategies and customer acquisition efforts.
• Understanding the lifetime value of customers
• Analyze how pricing and packaging impacts lifetime value
• Understand how moving up or down markets impact Lifetime value

‍

Sample Insights From This Customer Cohort by Year Chart:

Imagine you were tasked by the CFO and CRO to evaluate whether or not the company's objective to increase average deal size is paying off.

‍

The above visualization indicates that while initial deals are becoming larger, suggesting more substantial upfront revenue, there is no significant increase in the bookings from customers within the first year. Although this meets the C-Suite's goal to 'increase our average deal size,' it does not significantly boost bookings over time.

The above chart also backs up the CFO's intuition that the large discounts being provided to close larger deals is hurting revenue growth.

‍

Pairing the two charts, we can see that the sales cycle got substantially longer - backing up the CRO's claims that pushing for larger deals is hurting their sales cycle times. This also helps explain the increase in Customer Acquisition Cost the CFO has been discussing.

‍

The C-Suite loves it when you can deliver insights like this, paired with visuals that build trust, within an hour.

‍

‍

Closing Remarks

Assuming you have the right toolkit, a cohort analyses take minutes to perform, yet it informs strategies ranging from pricing, to customer engagement, to sales quotas, to retention, to customer lifetime value, to completely revamping the go-to-market approach.

Pro Tip: Cohort analysis is here to stay - executives will only want to see the data sliced and diced in different ways. Make sure that the process you put in requires almost no ongoing effort to maintain, and the foundation can be expanded as the inevitable requests come your way.

‍

A Cohort Analysis is one small piece of the analytical puzzle.

For B2B companies, make sure you pair the cohort analysis with conversion rates for early stage deals, and win-loss for later stage funnel.

‍

{{cta-component}}