Queries for Influenced By Drift & Sourced by Drift

How does Drift calculate its Salesforce metrics?

In your revenue reports, you can see the revenue impact of Drift. In short, influenced by Drift represents all the opportunities that have had conversations with you using Drift chat before they closed. Sourced by Drift is a subset of influenced where the opportunity was created up to 90 days after the chat occurred, only if the chat was with the primary (or sole) contact on the opportunity.

Here's a breakdown of the Salesforce queries we use to calculate these numbers so you can run them yourself:

<FROM_DATE> is the start of the query date range
<TO_DATE> is the end of the query date range

INFLUENCED OPPORTUNITIES (count and amount)

First query to get contact ids for contacts who had chats and opportunities in a given timeframe:

SELECT WhoId, MIN(CreatedDate) CreatedDate
FROM TASK t
WHERE Subject = 'Conversation in Drift'
  AND DAY_ONLY(CreatedDate) >= <FROM_DATE>
    AND DAY_ONLY(CreatedDate) <= <TO_DATE>
    AND WhoId IN(
SELECT ContactId
     FROM OpportunityContactRole
     WHERE DAY_ONLY(Opportunity.CreatedDate) >= <FROM_DATE>
        AND DAY_ONLY(Opportunity.CreatedDate) <= <TO_DATE>
        AND ContactId != NULL 
 	  )
GROUP BY WhoId

Second query to get influenced opportunities:

SELECT OpportunityId,
       IsPrimary,
       ContactId,
       Opportunity.Id,
       Opportunity.Name,
       Opportunity.Amount,
       Opportunity.CloseDate,
       Opportunity.CreatedDate,
       Opportunity.IsWon,
       Opportunity.Account.Id,
       Opportunity.Account.Name,
       Contact.Email,
       Contact.CreatedDate,
       Contact.Id
FROM OpportunityContactRole
WHERE ContactId IN (<CONTACT_IDS>)
  AND Contact.Email != NULL
  AND DAY_ONLY(Opportunity.CreatedDate) >= <FROM_DATE>
  AND DAY_ONLY(Opportunity.CreatedDate) <= <TO_DATE>
  AND Opportunity.Amount >= 0
  AND Opportunity.Account.Name != NULL

The results of the second query are further filtered to exclude opportunities which closed prior to the conversation happening.

SOURCED OPPORTUNITIES (count and amount)

INFLUENCED opportunities filtered using the following logic (pseudo query):

MIN(Contact.Task.CreatedDate) >= Opportunity.CreatedDate - 90
Contact.IsPrimary = true OR COUNT(OpportunityContactRole.ContactId) = 1

CLOSED WON OPPORTUNITIES (count and amount)

INFLUENCED or SOURCED opportunities filtered using the following logic (pseudo query):
Opportunity.IsWon = true