Unlocking the Full Lead Journey

From “Just a Click” to Revenue

I love diving deep into data and connecting the dots to paint a picture far beyond what any built-in report can give you. When you work with out-of-the-box reports in Marketing Cloud Account Engagement (formerly Pardot) or standard Salesforce dashboards, you often hit a wall. You see the marketing activity in one silo and the sales outcome in another.

But what if you want to see the entire story in a single row?

That’s where CRM Analytics (CRMA) and a little bit of “Data Prep” magic come in. I recently built a recipe that maps a prospect’s journey from their very first anonymous interaction all the way to a closed-won opportunity.

The “Spaghetti” vs. The Strategy

If you look at the raw schema, it can look like a mess of disconnected objects. But if you map it out logically, as shown in the recipe below, it becomes a clear pipeline.

In this flow, we aren’t just looking at a list of emails sent. We are actively joining:

Prospect and Activity: The heartbeat of marketing engagement.
Core Salesforce Objects: User (Owner), Contact, Account, Lead, and Opportunity.

By joining Prospect and Activity to Opportunity (via Roles and Contacts), we bridge the gap. We can finally answer: “Which specific whitepaper download in 2023 contributed to this $50k deal in 2025?”

The Rosetta Stone: Decoding the Data

One of the trickiest parts of working with raw marketing data is that it often speaks in codes, not English. If you’ve ever looked at the backend tables, you know that an AssetActivity of “1 2” or “2 1” is meaningless to a Sales Director.

That’s why the Transformation node is the unsung hero of this build.

In the recipe, I used a CASE statement (essentially a giant “If/Then” formula) to act as a translator.

Raw Data: '1 2' → Clean Data: 'Form View'
Raw Data: '2 1' → Clean Data: 'List Email Click'

This turns cryptic database integers into a human-readable narrative that anyone (from the CMO to a new SDR) can understand immediately.

Why This Matters (and Why Sales Will Love You)

The end result isn’t just a fancy chart; it’s a unified dataset that aligns your teams.

True Multi-Touch Attribution: You can see every touchpoint before a deal closes, not just the last one.
Sales Context: When a rep looks at a Lead, they don’t just see “Score: 100.” They can see the exact journey.
Spreadsheet Friendly: Let’s be honest, some sales leaders still live in spreadsheets. Because this recipe flattens complex relationships into a single table, it is incredibly easy to export to CSV. You can hand them a file where Column A is the “Landing Page Name” and Column Z is “Deal Revenue.”

Stop relying on disjointed reports. Build the map, translate the code, and show the full value of your marketing efforts.

If you need help with SAQL queries, allow me to throw you a bone here.

Query: AssetActivity

CASE
WHEN "AssetActivity" = '1 2' THEN 'Form View'
WHEN "AssetActivity" = '1 4' THEN 'Form Success'
WHEN "AssetActivity" = '2 1' THEN 'List Email Click'
WHEN "AssetActivity" = '2 11' THEN 'List Email Open'
WHEN "AssetActivity" = '3 2' THEN 'File View'
WHEN "AssetActivity" = '4 21' THEN 'Custom Redirect Click'
WHEN "AssetActivity" = '5 2' THEN 'Landing Page View'
WHEN "AssetActivity" = '5 4' THEN 'Landing Page Success'
WHEN "AssetActivity" = '6 4' THEN 'Form Handler Success'
WHEN "AssetActivity" = '7 1' THEN 'Automated Email Click'
WHEN "AssetActivity" = '7 11' THEN 'Automated Email Open'
WHEN "AssetActivity" = '8 20' THEN 'Website Visit'
WHEN "AssetActivity" = '9 2' THEN 'Priority Page View'
ELSE "AssetActivity"
END

Query: AssetType

CASE 
  WHEN "AssetType" IN ('1', 1) THEN 'Form'
  WHEN "AssetType" IN ('2', 2) THEN 'List Email'
  WHEN "AssetType" IN ('3', 3) THEN 'File'
  WHEN "AssetType" IN ('4', 4) THEN 'Custom Redirect'
  WHEN "AssetType" IN ('5', 5) THEN 'Landing Page'
  WHEN "AssetType" IN ('6', 6) THEN 'Form Handler'
  WHEN "AssetType" IN ('7', 7) THEN 'Automated Email'
  WHEN "AssetType" IN ('8', 8) THEN 'Website Visit'
  WHEN "AssetType" IN ('9', 9) THEN 'Priority Page View'
  ELSE "AssetType"
END

Query: ActivityType

CASE
WHEN “ActivityType” IN (‘1’, 1) THEN ‘Click’
WHEN “ActivityType” IN (‘2’, 2) THEN ‘View’
WHEN “ActivityType” IN (‘3’, 3) THEN ‘Error’
WHEN “ActivityType” IN (‘4’, 4) THEN ‘Success’
WHEN “ActivityType” IN (’11’, 11) THEN ‘Open’
WHEN “ActivityType” IN (’20’, 20) THEN ‘Visit’
WHEN “ActivityType” IN (’21’, 21) THEN ‘Custom Redirect Click’
ELSE “ActivityType”
END

Let’s pretend that you have a state field and you have a mix of records that have abbreviated values and full state values. Here’s the SQL query for that:

CASE
  WHEN CHAR_LENGTH("Prospect.state") = 2 THEN UPPER("Prospect.state")
  WHEN "Prospect.state" = 'Alabama' THEN 'AL'
  WHEN "Prospect.state" = 'Alaska' THEN 'AK'
  WHEN "Prospect.state" = 'Arizona' THEN 'AZ'
  WHEN "Prospect.state" = 'Arkansas' THEN 'AR'
  WHEN "Prospect.state" = 'California' THEN 'CA'
  WHEN "Prospect.state" = 'Colorado' THEN 'CO'
  WHEN "Prospect.state" = 'Connecticut' THEN 'CT'
  WHEN "Prospect.state" = 'Delaware' THEN 'DE'
  WHEN "Prospect.state" = 'District of Columbia' THEN 'DC'
  WHEN "Prospect.state" = 'Florida' THEN 'FL'
  WHEN "Prospect.state" = 'Georgia' THEN 'GA'
  WHEN "Prospect.state" = 'Hawaii' THEN 'HI'
  WHEN "Prospect.state" = 'Idaho' THEN 'ID'
  WHEN "Prospect.state" = 'Illinois' THEN 'IL'
  WHEN "Prospect.state" = 'Indiana' THEN 'IN'
  WHEN "Prospect.state" = 'Iowa' THEN 'IA'
  WHEN "Prospect.state" = 'Kansas' THEN 'KS'
  WHEN "Prospect.state" = 'Kentucky' THEN 'KY'
  WHEN "Prospect.state" = 'Louisiana' THEN 'LA'
  WHEN "Prospect.state" = 'Maine' THEN 'ME'
  WHEN "Prospect.state" = 'Maryland' THEN 'MD'
  WHEN "Prospect.state" = 'Massachusetts' THEN 'MA'
  WHEN "Prospect.state" = 'Michigan' THEN 'MI'
  WHEN "Prospect.state" = 'Minnesota' THEN 'MN'
  WHEN "Prospect.state" = 'Mississippi' THEN 'MS'
  WHEN "Prospect.state" = 'Missouri' THEN 'MO'
  WHEN "Prospect.state" = 'Montana' THEN 'MT'
  WHEN "Prospect.state" = 'Nebraska' THEN 'NE'
  WHEN "Prospect.state" = 'Nevada' THEN 'NV'
  WHEN "Prospect.state" = 'New Hampshire' THEN 'NH'
  WHEN "Prospect.state" = 'New Jersey' THEN 'NJ'
  WHEN "Prospect.state" = 'New Mexico' THEN 'NM'
  WHEN "Prospect.state" = 'New York' THEN 'NY'
  WHEN "Prospect.state" = 'North Carolina' THEN 'NC'
  WHEN "Prospect.state" = 'North Dakota' THEN 'ND'
  WHEN "Prospect.state" = 'Ohio' THEN 'OH'
  WHEN "Prospect.state" = 'Oklahoma' THEN 'OK'
  WHEN "Prospect.state" = 'Oregon' THEN 'OR'
  WHEN "Prospect.state" = 'Pennsylvania' THEN 'PA'
  WHEN "Prospect.state" = 'Rhode Island' THEN 'RI'
  WHEN "Prospect.state" = 'South Carolina' THEN 'SC'
  WHEN "Prospect.state" = 'South Dakota' THEN 'SD'
  WHEN "Prospect.state" = 'Tennessee' THEN 'TN'
  WHEN "Prospect.state" = 'Texas' THEN 'TX'
  WHEN "Prospect.state" = 'Utah' THEN 'UT'
  WHEN "Prospect.state" = 'Vermont' THEN 'VT'
  WHEN "Prospect.state" = 'Virginia' THEN 'VA'
  WHEN "Prospect.state" = 'Washington' THEN 'WA'
  WHEN "Prospect.state" = 'West Virginia' THEN 'WV'
  WHEN "Prospect.state" = 'Wisconsin' THEN 'WI'
  WHEN "Prospect.state" = 'Wyoming' THEN 'WY'
  WHEN "Prospect.state" = 'American Samoa' THEN 'AS'
  WHEN "Prospect.state" = 'Guam' THEN 'GU'
  WHEN "Prospect.state" = 'Northern Mariana Islands' THEN 'MP'
  WHEN "Prospect.state" = 'Puerto Rico' THEN 'PR'
  WHEN "Prospect.state" = 'U.S. Virgin Islands' THEN 'VI'
  ELSE "Prospect.state"
END