Profile picture for user Andrew Nguyen

Doing a cohort analysis is a great reporting tactic to better understand the influence of marketing. A cohort analysis is a broad term that refers to selecting a group of people and understanding what happened after an intervention, or after a designated period of time. In this case, the intervention is marketing, and we are studying whether marketing campaigns influence a group of leads. 

The question our customers often ask is: How many opportunities were generated from a cohort of leads? In other words, we've generated leads, we've done some marketing, now what were the results?

In this post we’ll answer this type of question by using cohort analysis with Bizible.

What Is A Cohort Analysis In B2B Marketing?

B2B cohort analysis is the B2C equivalent to studying a group of consumers who are exposed to an advertisement or given an offer and tracking that group over time. We’ll call this the intervention group.

Compared to a group that did not see the ad or receive an offer, how much in sales did the intervention group generate? Did they convert faster? Were conversion rates higher?

This makes perfect sense in B2C, but in B2B marketing there are added difficulties.

In B2B, people receive offers and get included in campaigns, but accounts and opportunities generate revenue. Pipeline measurement, too, is based on account and opportunity values. This makes it difficult to study cohorts, because the revenue and pipeline information is not attached to people, it’s attached to opportunities.

For example, in Salesforce reporting, leads get converted into contacts inside of an account, and opportunities live within accounts. So the process of mapping all this out for the purpose of cohort analyses is nearly impossible.

Luckily, we export our data to a data warehouse so that we can query our data how we want, and build tables in ways that helps us easily do cohort analysis. It’s a lot like a magic wand that turns marketing data from Salesforce into the exact spreadsheet we have in mind. 

The Questions To Answer With Cohort Analysis

As B2B marketers we want to better understand how our campaigns, promotional efforts, messaging, and content affects the bottom line. From a cohort of leads we want to learn the following:

  • The number of opportunities generated

  • Pipeline value

  • Closed-won revenue

  • Velocity 

  • Conversion rate (By opportunity stage)

To get there with a cohort analysis using Bizible Data Warehouse use the following steps:

  1. Define your cohort by date crated, campaign inclusion, channel, or a combination. 

  2. Find the leads table and filter by your cohort definitions. Generate a second table with only these leads.

  3. Delete duplicate leads, this might happen if a person in your cohort downloaded multiple pieces of content with the same email address. 

  4. Create a merged table, using the leads table and the opportunities table using a Left Join. Merge using the Account ID column. NOTE: You will have repeated opportunity ID’s because multiple leads are part of the same opportunity You now have a table that looks like this: 

  5. Generate your metrics and visualizations. 

  6. Repeat this process for a comparison cohort, then compare the results.

Your dashboard now has the performance metrics for your cohort, as well as a comparison cohort to help you draw conclusions. You can see whether the metrics differ, showing potential that your intervention (i.e. marketing) created different results compared to the baseline cohort.

Next let’s dive deeper with a tutorial on how to do the above steps.

Cohort Analysis In PowerBI Tutorial

Below is a brief set of steps for comparing a cohort of leads generated in January 2017 to a cohort of leads generated in February 2017. You can define your cohorts any way you choose, for example, by inclusion in a marketing campaign, lead source, etc.

To keep the tutorial simple, we’ll just compare January leads and February leads. Marketers often want to know how many leads were created in 

Step 1: Connect to your database. Import the Leads Table (called biz_leads) and the Opportunities Table (called biz_opportunities). These tables house your, you guessed it, leads data and sales opportunities data. 

Step 2: Go to Edit Query, select the Leads Table, duplicate it, and filter rows, selecting for only leads that were created in January.  We'll call this the January Leads Table. 

Filter rows PowerBI edit query

Step 3: Remove duplicates leads. 

remove duplicates PowerBI

Step 4: Merge the Leads Table with the Opportunities Table, using a Left Outer Join on the account_id column. You now have a new table that has both the leads generated in January and the associated opportunity information. Call it something like the January Merged Table. 

merge queries PowerBI

Step 5: Repeat the above steps to create a new table for February leads. We'll call it the Merged February Table. You now have two tables, one for January leads and on for February leads. They both have opportunity data merged with them.

Step 6: It's now time to create a dashboard that compares the number of opportunities generated, the amount of pipeline, and the amount of revenue between the January leads cohort, and the February cohort. 

In the fields menu, select the January Leads Table you just created, select the Opportunity ID and drag it to the Fields section in the visualization menu, right click and select Count (Distinct). This adds up all the opportunities in this cohort. You may define "opportunity" differently, for example, the stage: Qualified Discovery is not considered an opportunity. Only when there's a product demo do we call it an "opportunity" so we filter those out in the Visual Level Filter. 

Count distinct PowerBI

Step 7: To measure pipeline generated, drag the Amount field into the Fields section in the Visualization menu. Then drag the Opportunity Stage field into the Visual Level Filters section of the visualization menu. Then filter for the stages that denote opportunity. For us at Bizible we open an opportunity after a demo has been scheduled. 

visual level filtering

Step 8: For revenue, simply check the Closed Won box to select opportunities that were closed-won. You now have three metrics about your January cohort: The number of opportunities, the amount of pipeline and the amount of revenue. 

Step 9: You can create different views too. You can create a pie chart showing the current status of opportunities from your cohort. Or you can create a bar chart comparing revenue or pipeline across cohorts. Like below:

example cohort analysis chart

Step 10: Repeat this process for the February Leads Table.

You now have a dashboard that can look something like below, comparing your two cohorts. In this example, we can compared the kind of marketing we did, and the kinds of leads we generated, in January and February. We can just as easily compare leads from different campaigns, segments, channels, etc. 

 cohort analysis b2b marketing dashboard example


Cohort analysis can be a powerful reporting tool. There are many more ways to group your leads and opportunities. With Bizible Data Warehouse and your favorite BI tool, you're much less likely to be constrained by the data and reporting architecture that comes with your CRM or MAP.