Post-Click Attribution Using Emarsys Open Data

For marketers relying on CRM and email campaigns, proving ROI often comes down to attribution. The most common method—last click attribution—offers a simple way to assign conversions, but it rarely tells the full story, especially for email-driven journeys. This is where Emarsys Open Data and a bit of SQL can unlock a more realistic view of how customers behave between click and purchase.

💡 Why Post-Click Attribution Matters

Let’s say a user clicks on an email, browses your site, but doesn’t convert. Hours later—or even the next day—they return via direct traffic or a search ad and place an order. Last-click attribution would give all the credit to that final touchpoint, ignoring the email’s role in initiating the journey.

To get a clearer picture, I use a method I call “post-click attribution”, which attributes a purchase to an email campaign if the user:

Clicked an email
Made a purchase within a certain time window (e.g., 48 hours)

This allows for a more balanced and behaviorally accurate attribution model. And Emarsys Open Data makes it possible.

🛠️ What You Need from Emarsys Open Data

When connected to BigQuery, Emarsys Open Data exposes highly detailed tables like:

email_clicks: captures user-level email engagement
session_purchases: logs all completed orders with user ID and timestamps
sessions: includes browsing sessions with rich metadata (tags, URLs, sources)

Using these, we can join events at the contact_id level and measure time intervals between:

Click and purchase
Session and purchase

📊 The Query: Connecting Clicks, Sessions, and Purchases

Here’s the SQL I used in a recent analysis at the last company I worked at. (Project and dataset names have been anonymized for confidentiality.)


WITH Clicks AS (
  SELECT 
    contact_id, 
    campaign_id, 
    event_time AS click_time,
    TIMESTAMP_TRUNC(event_time, DAY) AS click_date
  FROM 
    `sap-od-clientnameod.emarsys_clientnameod_818339686.email_clicks_818339686`
),
Purchases AS (
  SELECT 
    contact_id, 
    order_id, 
    event_time AS purchase_time,
    TIMESTAMP_TRUNC(event_time, DAY) AS purchase_date
  FROM 
    `sap-od-clientnameod.emarsys_clientnameod_818339686.session_purchases_818339686`
  WHERE
    event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
),
OlderSessions AS (
  SELECT 
    contact_id,
    TIMESTAMP_TRUNC(start_time, DAY) AS session_date,
    start_time AS session_start_time,
    tag,
    attributes.name AS attribute_name,
    attributes.string_value AS attribute_value
  FROM 
    `sap-od-clientnameod.emarsys_clientnameod_818339686.sessions_818339686`,
    UNNEST(tags) AS tags,
    UNNEST(tags.attributes) AS attributes
)
SELECT 
  p.contact_id, 
  p.order_id, 
  c.campaign_id,
  c.click_time,
  p.purchase_time,
  TIMESTAMP_DIFF(p.purchase_time, c.click_time, MINUTE) AS minutes_between_click_and_purchase,
  s.session_date,
  s.session_start_time,
  s.attribute_name,
  s.attribute_value,
  TIMESTAMP_DIFF(p.purchase_time, s.session_start_time, MINUTE) AS minutes_between_session_and_purchase
FROM 
  Purchases p
JOIN 
  Clicks c
ON 
  p.contact_id = c.contact_id
AND 
  p.purchase_date = c.click_date
AND 
  c.click_time < p.purchase_time
LEFT JOIN 
  OlderSessions s
ON 
  p.contact_id = s.contact_id
AND 
  s.session_start_time < p.purchase_time
AND 
  s.session_start_time > c.click_time
ORDER BY 
  minutes_between_session_and_purchase DESC

🔍 What This Query Tells Us

This query uncovers the exact chain of actions between email engagement and order placement:

How long it takes between click and purchase
What sessions happened in the meantime
Which URLs or campaign parameters appeared in those sessions

It’s especially useful when paired with a time-window filter (e.g., limit results to purchases within 48 hours of the click) to simulate a “2-day post-click attribution model”.

📈 Real Value for CRM and Marketing Teams

While last-click attribution may favor paid ads or brand search, this approach helps CRM teams defend their true contribution by showing how email initiates user journeys—even if the final click comes from another channel.

You can also use this model to:

Calculate assisted conversions by email campaigns
Optimize re-engagement campaigns with better timing
Demonstrate the role of email in omnichannel journeys

✅ Conclusion

With Emarsys Open Data and a bit of SQL, you can go far beyond the oversimplified last click attribution for email. By aligning your attribution with real user behavior—across sessions, channels, and time—you get insights that are more trustworthy and much more useful for strategic decisions.

If you’d like a ready-made Power BI dashboard or a visual flowchart of this model, feel free to reach out here.

🧠 Want to understand why last click attribution is holding your email marketing back? Don’t miss my article on why last click attribution for email is misleading and how smarter models can reshape your CRM strategy.

Post-Click Attribution with Emarsys Open Data: Going Beyond Last Click