In pharmaceutical marketing, email engagement data is not just a marketing metric — it’s a sales signal. When a Healthcare Professional (HCP) consistently ignores your emails, it likely means one of two things: they’re not interested in the content, or they’ve mentally disengaged from the relationship. Either way, the right response is not to send another email. It’s to send a person.

A healthcare client came to us with a specific requirement: they needed their medical representatives to know which HCPs had stopped engaging with email communications — not just with low engagement in general, but with three consecutive emails specifically — so reps could prioritise in-person visits and understand the disconnect.

The solution we built tracks email sequences per HCP, applies a 7-day engagement window per send, detects consecutive non-opens, and automatically alerts the assigned medical rep in Sales Cloud — entirely automated, no manual reporting required.

In pharma, knowing which HCPs have disengaged digitally is as commercially important as knowing who is actively engaging.

The Business Rules — Where the Complexity Lives

The technical challenge of this solution is almost entirely in correctly implementing the business rules. The rules sound simple in plain English; they are surprisingly intricate in SQL.

The rules were:

1. Track email sends per HCP across a defined journey (up to 6 emails in sequence).
2. An email only counts as “opened” if the open event occurs within 7 days of the send date. Opens after day 7 are counted as non-opens.
3. If an HCP fails to open 3 consecutive emails, set a checkbox on their Sales Cloud Contact record to alert their assigned rep.
4. If the HCP opens any email within a sequence — even after two consecutive non-opens — the counter resets to zero.
5. The checkbox is automatically disabled after the rep notification fires, allowing the system to detect the next consecutive non-engagement window independently.

The Architecture: Marketing Cloud Does the Heavy Lifting

The solution is split across two platforms, but the intelligence lives almost entirely in Marketing Cloud.

Marketing Cloud: SQL Sequence Tracking

A scheduled SQL automation runs regularly and evaluates the email open history per HCP contact. It pulls from Marketing Cloud’s _Sent and _Open Data Views, assigns sequence numbers per contact, applies the 7-day open window filter, and checks for consecutive non-open triplets.

-- Step 1: Assign sequence numbers and evaluate opens within 7 days
SELECT
    s.SubscriberKey,
    s.JobID,
    s.EventDate                                          AS SendDate,
    ROW_NUMBER() OVER (
        PARTITION BY s.SubscriberKey
        ORDER BY s.EventDate
    )                                                    AS EmailSeq,
    CASE
        WHEN o.SubscriberKey IS NOT NULL
         AND DATEDIFF(day, s.EventDate, o.EventDate) <= 7
        THEN 1
        ELSE 0
    END                                                  AS OpenedInWindow
FROM _Sent s
LEFT JOIN _Open o
    ON  s.SubscriberKey = o.SubscriberKey
    AND s.JobID         = o.JobID
WHERE s.EventDate >= DATEADD(day, -90, GETDATE())

-- Step 2: Flag contacts with 3 consecutive non-opens
SELECT SubscriberKey
FROM (
    SELECT
        SubscriberKey,
        EmailSeq,
        OpenedInWindow,
        LAG(OpenedInWindow, 1) OVER (
            PARTITION BY SubscriberKey ORDER BY EmailSeq
        ) AS PrevOpen1,
        LAG(OpenedInWindow, 2) OVER (
            PARTITION BY SubscriberKey ORDER BY EmailSeq
        ) AS PrevOpen2
    FROM EmailSequenceDE
) x
WHERE OpenedInWindow = 0
  AND PrevOpen1      = 0
  AND PrevOpen2      = 0

When the query identifies contacts with 3 consecutive non-opens, it updates a Contact record in Salesforce via the Marketing Cloud-Salesforce connector — setting the NonEngagementFlag__c checkbox to true.

Sales Cloud: Rep Notification and Auto-Reset

In Salesforce, a Flow triggers on the checkbox update. It sends an internal email notification to the specific medical rep assigned to that HCP contact — not a broadcast to all reps, only the rep responsible for that relationship. After the notification fires, the Flow automatically resets the checkbox to false, enabling the system to detect the next consecutive non-engagement window independently.

The alert reaches only the rep assigned to that specific HCP — actionable, targeted, and contextualised for the right person.

Frequently Asked Questions