如何用SQL查询每个销售机会对应的最后触达营销活动

Got it, let's break down how to solve this problem. We need to pull the most recent (last touch) Campaign that any Contact under an Opportunity's Account responded to—before the Opportunity was created. Here's a step-by-step approach with SQL:

Approach #

1. Link all related tables: Start from Opportunity, join to its associated Account, then all Contacts under that Account, their CampaignMember records, and finally the linked Campaign details.
2. Filter for valid responses: Only keep CampaignMember records where ResponseDate is earlier than the Opportunity's CreatedDate.
3. Rank responses by recency: Use a window function to rank records per Opportunity, sorting by ResponseDate in descending order (so the newest response gets rank 1).
4. Select the top-ranked record: Pick only the rank 1 record for each Opportunity to get the last touch Campaign.

SQL Query #

WITH OpportunityLastTouchCTE AS (
    SELECT
        o.Id AS OpportunityId,
        o.Name AS OpportunityName,
        o.CreatedDate AS OpportunityCreatedDate,
        a.Id AS AccountId,
        a.Name AS AccountName,
        c.Id AS ContactId,
        c.Name AS ContactName,
        cm.ResponseDate,
        cam.Id AS CampaignId,
        cam.Name AS CampaignName,
        -- Assign rank: 1 = most recent response per Opportunity
        ROW_NUMBER() OVER (
            PARTITION BY o.Id
            ORDER BY cm.ResponseDate DESC
        ) AS ResponseRank
    FROM Opportunity o
    -- Join to the Account linked to the Opportunity
    JOIN Account a ON o.AccountId = a.Id
    -- Join all Contacts under that Account
    JOIN Contact c ON a.Id = c.AccountId
    -- Join CampaignMembers for those Contacts
    JOIN CampaignMember cm ON c.Id = cm.ContactId
    -- Join the associated Campaign details
    JOIN Campaign cam ON cm.CampaignId = cam.Id
    -- Only include responses that happened before the Opportunity was created
    WHERE cm.ResponseDate < o.CreatedDate
)
-- Grab only the top-ranked (most recent) record per Opportunity
SELECT
    OpportunityId,
    OpportunityName,
    OpportunityCreatedDate,
    AccountId,
    AccountName,
    ContactId,
    ContactName,
    ResponseDate AS LastTouchResponseDate,
    CampaignId AS LastTouchCampaignId,
    CampaignName AS LastTouchCampaignName
FROM OpportunityLastTouchCTE
WHERE ResponseRank = 1;

Notes & Adjustments #

• Include Opportunities with no Last Touch: If you want to retain Opportunities that have no matching CampaignMember records (no prior responses), replace all JOIN clauses with LEFT JOIN and adjust the final WHERE clause to WHERE ResponseRank = 1 OR ResponseRank IS NULL. This will return NULL values for all Campaign/Contact fields for those Opportunities.
• Handle ties for latest response: If multiple Contacts have a CampaignMember with the exact same latest ResponseDate for an Opportunity, ROW_NUMBER() will randomly pick one. To keep all tied records, use RANK() instead of ROW_NUMBER()—this will assign the same rank to all records with the max ResponseDate.

内容的提问来源于stack exchange，提问作者Joe F.