cohort analysis

Sales Velocity from Display Advertising Demystified

“How can we close this deal faster?”

Virtually every B2B marketer has been confronted with this question. The complex nature of the B2B sales cycle and the myriad factors that influence can make any marketer’s head spin.

But combing through data from past transactions opens a world of possibilities for savvy marketers. Follow along as we show you how to manipulate your CMS and CRM to measure the impact of display advertising on your sales velocity.  

Sales velocity is measured in “dollars per day” as:

Number of Leads x Win Rate (%) x Average Deal Size / Average Sales Cycle

According to the sales gurus at TAS Group, the majority of sellers focus on increasing the number of opportunities within their sales funnel. But experts have revealed that improving average deal value, win rate, and sales cycle can lead to highly beneficial results. In fact, increasing your deal value, win rate, and sales cycle efficiency by just 10% generally produces a 34% sales velocity improvement.

Increase deal size, win rate, and sales velocity to improve your pipeline value

So, why is sales velocity such an important metric?

Imagine you spearhead marketing efforts for a SaaS company and are presented the following metric data:

100 Marketing Qualified Leads (MQL) x 5% (Win Rate) x $2,000 monthly recurring revenue (Deal Size) / 60 days (Sales Cycle)

In this situation, you could expect a sales velocity of $166.67 per day. Given the compounding nature of SaaS revenue, improving sales velocity 34% will dramatically impact the top line.  The graph below illustrates an annual revenue curve with a sales velocity of $166 per day as well as a revenue curve featuring a sales velocity that is 34% faster and, therefore results in $224 per day. The net difference is $507,960 over a 24 month period.

Graph illustrates impact of sales velocity on SaaS revenue

While it’s not rocket science, gathering data for these variables can be challenging. Salesforce has pre-made reports you can use, but you should consider developing your own report to enable customized segmentation. For instance, you may want to explore what impact display advertising has on your sales velocity. Take a look at the following example:

Initial Setup

Start by opening your marketing automation or CMS vendor and add a hidden field to your forms, such as “visitor_id.”  This hidden field will be the key that merges the advertising impression data and your CRM database.

Add a unique ID for each visitor into your CMS and CRM to track the impact of display ads

Next, you’ll need to add JavaScript to your website to create a unique token for each visitor on your site. (You may want to ask your development team to help you with this.)  The JavaScript snippet should search for the name of your hidden field and populate its value with the visitor_id token. Once complete, a form submit should inject the visitor_id into your contact database.

Use JavaScript to inject the unique ID as a hidden field in a form

Let The Campaigns Run

After your advertising campaigns have run for 1-2 sales cycles, you will export the advertising data from your retargeting vendor. Ideally, you’ll want to see this data for each visitor, including the visitor_id, the number of impressions served, which ads they received, and so on.

CSV file showing display ad impressions per unique ID

Next, you’ll need to associate the visitor_id from the retargeting vendor’s export to your contacts in the CRM, such as Salesforce. This works best when your marketing automation or CMS system is integrated with Salesforce. Although a bit more technical, the best way to obtain this data is using the Apex Data Loader application (it’s a free download within your account; navigate to the Setup menu – under the Administer heading, click Data Management).

Salesforce provides a free app to download CSV data in bulk

Pick a data range that coincides with the length of your ad campaigns and export columns for each of the objects Lead, Contact, Accounts, and Opportunities. For this analysis, you may want the name, email, company, title, status, ID, CreatedDate, and LastModifiedDate for each contact.

CSV file shows sample data from Salesforce CRM to be merged advertising data

Time to Grab Excel or Google Spreadsheets

Combine the CSVs from the retargeting vendor and the Apex Data Loader into one file.  Then use your preferred spreadsheet program to perform a simple JOIN that combines fields from two tables (or more) using values common to each.  Start by grabbing the Salesforce Contact ID with a formula like this:

=IF(ISERROR(VLOOKUP(Visitor_id_For_This_Row, SFDC_Contact!A:CC, Nth_Column, FALSE)), “”,VLOOKUP(Visitor_id_For_This_Row, SFDC_Contact!A:CC, Nth_Column, FALSE))

Visitor_id_For_This_Row is the row being calculated (e.g., C2), and SFDC_Contact!A:CC is the sheet that contains the exported data from the Apex Data Loader.  Here, the VLOOKUP formula is taking the visitor_id generated from your website, searching for that value within your Salesforce data, and returning the Nth_Column from your Salesforce data if a match exists.  In the retargeting CSV file, add the columns “Contact_Name” and “Contact_Status” and repeat the formula above to populate the appropriate data for each column.  Repeat the same process for the Lead object in Salesforce.

CSV file shows display ad data merged with Salesforce CRM data

JOINing the Account and Opportunity objects requires a similar set of actions, although it might possibly be a bit trickier since a visitor_id is not associated to with those objects.  Instead, you’ll perform a VLOOKUP on the Contact_ID to find the appropriate Account_ID and Account_Name. Then you’ll perform another VLOOKUP to find opportunities associated to the Account_ID. For each Opportunity_ID you retrieve, you’ll grab data like Opportunity_Status, Opportunity_Amount, Opportunity_CreatedAt, and Opportunity_Closed_Date. Remember, the goal of these JOINs is to combine data about the number of impressions served to a given contact and their lead status or opportunity stage.

CSV representation of advertising impressions correlated to CRM lead stage

Add columns “Lead Cycle Duration” and “Opportunity Cycle Duration” to calculate funnel and sales velocity. Opportunity Cycle Duration is measured as the difference (in days) between the time the opportunity was created and the time the opportunity closed,

=IF(ISERROR(‘Opportunity Closed Date’‘Opportunity Created Date’), “”, ‘Opportunity Closed Date’‘Opportunity Created Date’)

A similar formula applies for the Lead Cycle Duration calculation.

Sales Funnel Impact of Your Display Advertising

In the same file, create a new sheet called Analysis. Here, you will calculate the quantifiable effects of the data you have JOINed together on your conversion rate. Your goal is to create output that looks similar to this:

Full Funnel Impact of Display Ads between two cohorts

and this:

Cohort Analysis for Sales Velocity between two cohorts

Create a table with four columns, SFDC Status, Mapping, Cohort A, and Cohort B.  In column SFDC Status, copy-paste all unique statuses available from your Salesforce database, such as “Open”, “Closed”, and so on.  For each of the SFDC Statuses, identify whether they represent a contact that is at the “Top of Funnel” (TOFU), “Middle of Funnel” (MOFU), “Bottom of Funnel” (BOFU), or Won in the Mapping column.  This step will help organize your company’s diverse lead statuses into a manageable set for this analysis.  In Column Cohort A, input a formula to count the number of leads featuring a given lead status who were exposed to display ads:

=COUNTIFS(JOINed_Data!L:L, Analyses!C64, JOINed_Data!P:P, “>0”)

Where JOINed_Data!L:L is the column containing the lead statuses retrieved from your JOIN formulas, Analyses!C64 is the cell containing the SFDC Status to be checked, and JOINed_Data!P:P is the column containing the number of impressions per visitor. Additionally, “>0” indicates that a given visitor saw more than 0 impressions.

In Column Cohort B, implement a formula to count the number of leads with a given lead status if they were not exposed to display ads.

=COUNTIFS(JOINed_Data!L:L, Analyses!C64, JOINed_Data!P:P, “0”)

The net result should look something like this:

Mapping multiple lead statuses to different sales funnel stages

In three columns of the Analysis sheet, input the funnel stages in the first column, Cohort A in the second column, and Cohort B in the third column.  For each cell in the second column, Cohort A, calculate the number of visitors with a SFDC Status representing a TOFU, MOFU, BOFU, and Win.  This can be accomplished using a formula such as:

Cohort A, Wins =SUMIF(Mapping_Column, “Win”, Cohort_A_from_Mapping_Table)

Excel Formula to map CRM lead statuses to sales funnel stages

As you calculate the number of BOFU, MOFU, and TOFU, be sure to incorporate the down-funnel values in each stage.  For instance:

Cohort A, BOFU = SUMIF(Mapping_Column, “BOFU”, Cohort_A_from_Mapping_Table) + Cohort_A_Wins

In other words, BOFU is the addition of visitors with a BOFU label and Wins. Essentially, if a lead was a Win, then it was also a BOFU at some point.

Repeat the same analysis for Cohort B, encompassing visitors who were not exposed to your display ads.

After all three columns are populated, calculate the conversion rate lift between Cohort A and Cohort B.  Lift is measured as the percentage change between the conversion rate for the cohorts:

Cohort A, Visitor to Win Ratio = (Cohort A, Wins) / (Cohort A, Visitors)

Cohort B, Visitor to Win Ratio = (Cohort B, Wins) / (Cohort B, Visitors)

Visitor to Win Ratio Lift = (Cohort A, Visitor to Win Ratio Cohort B, Visitor to Win Ratio) / (Cohort B, Visitor to Win Ratio)

Formulas showing how to calculate conversion rate and impact boost

Sales Velocity Impact of Your Display Advertising

Recall that sales velocity is calculated using the following formula:

Number of Leads x Win Rate (%) x Average Deal Size / Average Sales Cycle

The analysis performed above will provide the Win Rate (%) and the Number of Leads.  All that’s left is for these parameters, you will need to calculate the average deal size and average sales cycle for Cohorts A and B.

Let’s begin with the average deal size. In principle, you must calculate the average of the deal sizes if that opportunity was exposed to display ads for Cohort A.  Fortunately, your spreadsheet software makes this easy with an AverageIF function:

=AVERAGEIF(JOINed_Data!Impressions, “>0”, JOINed_Data!Opp_Amount)

Use a similar calculation for Cohort B in order to address opportunities not exposed to ads:

=AVERAGEIF(JOINed_Data!Impressions, “0”, JOINed_Data!Opp_Amount)

Next, calculate the average sales cycle for Cohorts A and B using the identical AverageIF function. In this calculation, make sure you modify the [average_range] criterion so that it points to the Opportunity Cycle Duration column you configured above.  For instance, Average Opportunity Cycle Duration for Cohort A is:

=AVERAGEIF(JOINed_Data!Impressions, “>0”, JOINed_Data!Opp_Cycle_Duration)

Use a similar function for Cohort B. You should now have all of the elements necessary to calculate sales velocity between cohorts which have been exposed to display ads as well as those which have not.

Formulas showing how to calculate sales velocity improvement between cohorts

Calculate the sales velocity for Cohorts A and B, respectively, using the formula:

=(Number_of_Leads * Visitor_to_Win_Ratio * Avg_Deal_Size) / (Avg_Sales_Cycle)

Lastly, measure the lift in sales velocity between Cohorts A and B by calculating the percentage change between the sales velocities:

Sales Velocity Lift = (Cohort A, Sales VelocityCohort B, Sales Velocity) / (Cohort B, Sales Velocity)

Congratulations – you did it!  Your spreadsheet should look something like this:

CSV showing final results after implementing Excel formulas on two cohorts

And Now Some Words of Caution

“Don’t try this at home,” crossed my mind multiple times as I wrote this.  Unless you have extensive experience in SQL or Excel, you’ll want to enlist an IT professional to assist you with this process.

Should you embark on this journey, you will probably identify many issues along the way which you will need to resolve for your organization.  For instance, does your sales organization use the “Opportunity Close Date” to forecast deals? If so, calculating Opportunity Cycle Duration as described here may be lead to some odd numbers. In this case, you may need a different Salesforce attribute to calculate the Opportunity Cycle Duration. You can also include additional operators in the formulas described above to clean edge cases.

I host a monthly webinar on this subject with live examples. Join me and we can work through this together.

Join a Webinar