Salesforce: Lead merge with MCAE Prospect

Salesforce merge() SOAP API, allow to combine up to three records of the same type into one record. You explicitly tell Salesforce which record is the master/survivor and which record(s) are merged into it. Salesforce does not automatically decide that the newer or older record survives.

Let say you have Lead A, B, and C; you decide keep lead A as the master record (survivor) and merge lead B and C (victims) into lead A.

• Keeps Lead A as the master record.
• Merges Lead B/C into Lead A.
• Deletes B/C as the "victim" records.
• Reparents related records from the victims to Lead A where applicable
• Sets MasterRecordId on the deleted victim records so you can identify what they were merged into.

Field Values

Salesforce doesn't automatically replace the master's fields with all the values from the victim. If the caller wants values from the non-master record to supersede values on the master, those field names and values must be supplied on the masterRecord in the MergeRequest.

Once the API run, you can do SOQL using queryAll() to retrieve records deleted by a merge, and MasterRecordId identifies merged records.

A few facts in the survived Lead:

1. Lead history of victim leads will not be carried to the survived lead, so the lead history stay as per original
2. Lead history have a new line of "Lead Merged" with original value of the victim lead name and company name
3. Created By and Created Date is updated with the oldest victim lead
4. All the related records (Task, Campaign Member, Messaging Session, etc.) from victim leads will be reparent to the survived lead

SOQL with queryAll():

What happened in the MCAE Prospect?

Assume both lead are in the prospect:

• The prospect will be merged too and link to the survived lead
• In the prospect audit, notice a line "Started matching Marketing Data Sharing criteria"
• Created date in the survive prospect will stay as per original date, however, the conversion date and other date such as Recent Interaction will be copy from oldest victim
• Prospect audit will have data from victim leads, including the old prospect activities

Salesforce: Campaign Influence Models

This blog refers to customizable campaign influence and continues from the previous blog: Campaign Influence with Primary Campaign Source Model. 

Record Preference in the Campaign Influence Model settings has 2 options: "All Records" and "Records with >0% Attribution". What does this mean?

1. All Records: the campaign influenced records will be created no matter what the influenced % is. 
2. Records with >0% Attribution: the campaign influenced records will only be created when the influenced is more than 0%. 

For custom model, the option is only "All Records".

1. Primary Campaign Source 

Primary Campaign Source field on Opportunity, plus the Opportunity Contact Role that matches the Auto-Assignment rules and Time Frame days.

• Campaign Influence from the Primary Campaign Source field will get 100% influence
• Campaign Influence from the Opportunity Contact Roles will get 0% influence

2. First Touch

This model is only available if you have MCAE implemented. This comes from the First Campaign Member Created Date. Influence of 100% is given to one campaign.

3. Last Touch

This model is only available if you have MCAE implemented. This comes from the Latest Campaign Member Last Modified Date. Influence of 100% is given to one campaign. 

Any edit on the campaign member (sync, job, status change) will move the influence, so it may reflect the system activity rather than campaign member engagement.

4. Even Distribution 

This model is only available if you have MCAE implemented. All eligible campaigns will get an equal split of influence (100% ÷ number of campaigns).

Treats an email click the same as an event; credit dilutes as touches grow. 

Salesforce: Campaign Statistics fields

There are 16 fields related to the Campaign Statistics:

1. Responses in Campaign: number of Campaign Members with status HasResponded in the Campaign Member Status
2. Leads in Campaign: number of Campaign Members that are Leads
3. Converted Leads in Campaign: number of Campaign Members that are Leads and have been converted
4. Contacts in Campaign: number of Campaign Members that are Contacts
5. Responses in Hierarchy: similar to (1), but include all child campaigns
6. Leads in Hierarchy: similar to (2), but include all child campaigns
7. Converted Leads in Hierarchy: similar to (3), but include all child campaigns
8. Contacts in Hierarchy: similar to (4), but include all child campaigns
9. Opportunities in Campaign: number of Opportunities with Primary Campaign Source points to that Campaign
10. Won Opportunities in Campaign: number of Won Opportunities with Primary Campaign Source points to that Campaign
11. Value Opportunities in Campaign: total amount of Opportunities with Primary Campaign Source points to that Campaign
12. Value Won Opportunities in Campaign: total amount of Won Opportunities with Primary Campaign Source points to that Campaign
13. Opportunities in Hierarchy: similar to (9), but include all child campaigns
14. Won Opportunities in Hierarchy: similar to (10), but include all child campaigns
15. Value Opportunities in Hierarchy: similar to (11), but include all child campaigns
16. Value Won Opportunities in Hierarchy: similar to (12), but include all child campaigns

What happens with the related list when Campaign Influenced is enabled?

• Influenced Opportunities related list available in the Campaign page layout
• Opportunities related list removed from the Campaign page layout
• Campaign Influence related list available in the Opportunity page layout
• Campaign Influence related list available in the Account page layout

Note:
The Campaign related list in the Account page layout is auto-added when a Contact is added as a Campaign Member; it has no relation to the Primary Campaign Source in the Account Opportunity.

What happens to the Campaign Statistics when Campaign Influenced is enabled?

As per the Campaign Influence implementation guide, "Opportunities added by the primary source campaign field appear in Campaign Statistics only when the Primary Source Campaign model is set as the default. If you’re using another model, the value shows opportunities added by auto-association only"

So, if the default model is First Touch, Last Touch, or Even Distribution, Campaign Statistics will use all influenced opportunities (with > 0% influence) of the selected default model.

The sample below uses "All Records" in the Record Preference setting; querying for a campaign with ID 701Hr000001ZpZ1IAK:

 

SELECT Model.MasterLabel, COUNT (Id) FROM CampaignInfluence WHERE CampaignId = '701Hr000001ZpZ1IAK' GROUP BY Model.MasterLabel ORDER BY Model.MasterLabel

SELECT Model.MasterLabel, COUNT (Id) FROM CampaignInfluence WHERE CampaignId = '701Hr000001ZpZ1IAK' AND Influence > 0 GROUP BY Model.MasterLabel ORDER BY Model.MasterLabel

The second query will be used to show the campaign statistics of a campaign; for example, the "Opportunities in Campaign" field:

• Default model = Even Distribution Model >> 51 (each campaign has equal influence)
• Default model = First Touch Model >> 49 (2 of them have Influence 0%)
• Default model = Last Touch Model >> 6 (1 of them has Influence 0%)
• Default model = Primary Campaign Source Model >> 0 (because no Campaign 701Hr000001ZpZ1IAK is stamped as the Primary Campaign Source field in Opportunity 

The same applies to the custom model; only campaigns with influence > 0% for the Opportunity will be counted for the Campaign statistic field.

CRM Analytics: Write Dataset to CSV file

CRM Analytics allows exporting a dataset into a CSV file and creating a lens as a values table. Ideally, the limit is 2,000 rows; you can extend more rows by change the lens to query mode, such as to 10,000 rows. But let's say you have 500,000 rows and many columns; it will not work.

You can use a recipe to write the dataset into a CSV file. In the output node, select Write To CSV, and  I prefer to select "Use fully qualified names in CSV header".

How to download the CSV file? Follow the following articles: Recipe Output to CSV and Export your Einstein Analytics datasets.

1. Query DatasetExport object

SELECT Id, PublisherInfo FROM DatasetExport

2. Query DatasetExportPart object

Sample query:

SELECT Id, PartNumber, DataFile FROM DatasetExportPart WHERE DatasetExportId = '0PxB0000000......' ORDER BY PartNumber  

Change 0Px.... to the DatasetExport ID from the 1st query. This also means the CSV files are split into multiple parts. Depending on the number of columns and characters, each part can be 10000 or 20000 rows.

3. REST Explorer

 Use Workbench or Salesforce Inspector; get the DataFile value from each row from the 2nd query and perform the REST query with a GET request, then copy the response into a text editor.

Reference:

• Recipe Output to CSV 
• Export your Einstein Analytics datasets.

Data 360: Objects Query

Use SOQL to query the following:

DLO >> DataLakeObjectInstance

• Id - prefix "1dl"
• Name - this is "Field Label" e.g. "Account_Home", "Base Events_Main" 
• ExternalName - this is "Field API Name", e.g. Account_Home__dll, base_events_Main__dll
• DataLakeObjectStatus: ACTIVE
• Category: Profile, Engagement, Other
• DataLakeObjectRef - prefix "0GO"
• Billable: Yes or blank
• TotalNumberOfFields
• TotalRecords

DataStream >> DataStream

• Id  - prefix "1ds"
• Name - e.g. "Account_Home"
• LastRefreshedDate  - date/time
• LastNumberOfRowsAddedCount - rows added in last refresh
• TotalRowsProcessed
• TotalNumberOfRowsAdded - all rows for the object
• RefreshFrequency: BATCH, NONE, MINUTES_5, HOURLY
• RefreshMode: UPSERT, INCREMENTAL
• StreamType
• DataStreamStatus: ACTIVE
• ImportRunStatus: SUCCESS

MCAE: Custom Redirect data in Data 360

Use case: to track users who access a PDF file on a website that is linked to MCAE.

MCAE has the WebPageActivity data stream ingested into Data 360; however, for the above scenario, accessing a PDF file will not generate web page activity.

Solution: Instead of promoting (such as in an email CTA) the PDF file directly, create a custom redirect in MCAE to track that PDF file URL, then use the "Tracked URL" generated in the custom redirect for the email CTA.

With this, MCAE will ingest the CustomURLActivity DLO that can be mapped to the Website Engagement DMO, then filter the DMO with the Data Source Object contains CustomURLActivity.

Important fields of CustomURLActivity DLO that can be mapped to Website Engagement DMO:

• DataSourceObject__c: CustomURLActivity_OUvxxxxx
• externalCustomUrlId__c: this is Pardot's Custom Redirect ID
• polymorphicCustomUrlId__c: this is Custom Redirect ID in Salesforce called MarketingLink object - prefix 0S0
• externalProspectId__c: this is Pardot's Prospect ID
• polymorphicIndividualId__c: this is Salesforce Lead ID or Contact ID - prefix 00Q/003

Data 360: Email Engagement DMO and Bulk Email Message DMO

For MCAE users, the Email Engagement DMO is mapped to the Email Activity DLO. Important fields in this DMO:

• ssot__EngagementDateTm__c: activity date time
• ssot__BulkEmailMessageId__c: reference to Bulk Email Message DMO: 0XB
• ssot__IndividualId__c: individual field, can refer to Lead ID or Contact ID: 00Q/003
• ssot__EngagementChannelActionId__c: stores information: SEND_STATUS, OPEN, CLICK, BOUNCE, UNSUBSCRIBE, and SUBSCRIBE

Because Email Activity DLO comes from the MCAE Email Activity data stream, this DMO does not have information about emails sent to each individual from Salesforce, but only emails sent from MCAE / Pardot for both List Email and Automated Email.

Bulk Email Message DMO is mapped from ListEmail DLO, which is streamed from the Salesforce ListEmail object. Important fields in this DMO:

• ssot__Id__c: 0XB
• ssot__ScheduledDate__c: the email sent date 
• ssot__CampaignId__c: the campaign ID
• Sent_Via__c: store information email sent from Pardot or Salesforce
• ssot__SendMethodType__c: store information email sent is List Email or Automated Email

Each row of Email Engagement DMO stores recipient ID, email status, etc., while Bulk Email Message DMO stores aggregate information, such as Total Sent, Total Delivered,  UniqueOpens, etc.; there is no individual-level information in this DMO.

But this DMO stores aggregated email information sent from both Salesforce (List Email) and Pardot (List and Automated Email).

Data 360: Calculated Insight using SQL for Segment

For Calculated Insights created using SQL (not Visual Builder), there is no "Show in Builder" option under the dropdown menu. 

If you edit the SQL and save the Calculated Insight, you must republish the Calculated Insight to get the new result. Also, you can't edit the SQL if there is a structural change, such as changing or removing an output field; the recommended solution is to create a new CI.

What is the SQL requirement in a Calculated Insight?

Syntax

SELECT <Attributes>, <Aggregation[_Measures_]>.

FROM <Data Model Object>

JOIN [Inner | Left | Right | Full] <Data Model Object> [Optional]

WHERE <predicate on rows> [Optional]

GROUP BY <columns[_Dimensions_]>

1. Must include a Profile Key

The output must contain a key that can be related back to the segment population, depending on whether your segment is built on Unified Individual or Individual.

2. Must include at least one aggregated measure

Calculated Insights require aggregation such as: COUNT(), SUM(), MAX(), MIN(), AVG().

3. Must have a GROUP BY

Sample SQL:

SELECT 

UnifiedIndividual__dlm.ssot__Id__c AS unifiedindividual_id__c, 

UnifiedIndividual__dlm.ssot__FirstName__c AS first_name__c, 

UnifiedIndividual__dlm.ssot__LastName__c AS last_name__c, 

ssot__EmailEngagement__dlm.ssot__EngagementChannelActionId__c AS channel__c,

COUNT(ssot__EmailEngagement__dlm.ssot__EmailMessageId__c) AS count_of_email_engagement__c, 

MIN(ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c) AS first_date__c,

MAX(ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c) AS last_date__c  

FROM ssot__EmailEngagement__dlm 

JOIN ssot__Individual__dlm ON (ssot__EmailEngagement__dlm.ssot__IndividualId__c = ssot__Individual__dlm.ssot__Id__c) 

JOIN IndividualIdentityLink__dlm ON (ssot__Individual__dlm.ssot__Id__c = IndividualIdentityLink__dlm.SourceRecordId__c) 

JOIN UnifiedIndividual__dlm ON (IndividualIdentityLink__dlm.UnifiedRecordId__c = UnifiedIndividual__dlm.ssot__Id__c) 

WHERE (ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c >= date_sub(current_date(), 7)) 

GROUP BY unifiedindividual_id__c, first_name__c, last_name__c, channel__c

Sample data from the above query to show how the JOIN works:

ssot__EmailEngagement__dlm.ssot__IndividualId__c = 003xxx or 00Qxxx

ssot__Individual__dlm.ssot__Id__c = 003xxx or 00Qxxx

IndividualIdentityLink__dlm.SourceRecordId__c = 003xxx or 00Qxxx

IndividualIdentityLink__dlm.UnifiedRecordId__c = c434453739ba80ff0cee6df928b19cde 

UnifiedIndividual__dlm.ssot__Id__c = c434453739ba80ff0cee6df928b19cde

In the Segment

Create a segment, type the CI name under the Search Attributes box, and use the aggregated "measure" attribute in the Include tab.

Can I run SQL from Calculated Insight in Query Editor?

Calculated Insight SQL and Query Editor SQL are similar, but there are some differences;

1.  When filtering by date field

In Query Editor: 

WHERE (ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c >= DATE_ADD('day', -7, CURRENT_DATE)) 

OR 

WHERE (ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c >= CURRENT_DATE - INTERVAL '7' DAY) 

In Calculated Insight: 

WHERE (ssot__EmailEngagement__dlm.ssot__EngagementDateTm__c >= date_sub(current_date(), 7)) 

2. Attribute name must end with __c in CI

In Query Editor

SELECT UnifiedIndividual__dlm.ssot__FirstName__c AS first_name

In Calculated Insight: 

SELECT UnifiedIndividual__dlm.ssot__FirstName__c AS first_name__c

3. The ORDER BY clause is not supported in CI

Data 360: Query Syntax

1. Object or Field Name
Use " " or nothing.

SELECT * FROM EmailActivity_0Uvxxxx__dll WHERE "eventType__c" <> 'OPEN' LIMIT 10

2. String match - Text field

Use ' ' and case-sensitive

SELECT * FROM EmailActivity_0Uvxxxx__dll WHERE "eventType__c" <> 'OPEN' LIMIT 10

3. Filter on Boolean field

SELECT * FROM ssot__Individual__dlm  WHERE "Is_FieldName__c" IS NOT TRUE LIMIT 10

OR

SELECT * FROM ssot__Individual__dlm  WHERE "Is_FieldName__c" IS TRUE LIMIT 10

4. Filter on Text field

SELECT * FROM "ssot__Account__dlm" WHERE "Customer__c" <> '' LIMIT 10

OR

SELECT * FROM "ssot__Account__dlm" WHERE "Customer__c" = '' LIMIT 10

5. Filter on DateTime field

SELECT

    l."Status__c" AS Lead_Status,

    COUNT(DISTINCT l."Id__c") AS Total_Leads,

    COUNT(

        DISTINCT CASE

            WHEN e."createdAt__c" >= TIMESTAMP '2026-05-01 00:00:00'

             AND e."createdAt__c" <= TIMESTAMP '2026-05-31 23:59:59'

             AND e."sendStatus__c" = 'SENT'

            THEN l."Id__c"

        END

    ) AS Contacted_May_2026

FROM "Lead_Home__dll" l

LEFT JOIN "EmailActivity_0Uvxxxx__dll" e

    ON l."Id__c" = e."prospectSalesforceId__c"

GROUP BY Lead_Status

ORDER BY Lead_Status;

Referral:

• Data 360 Query Guide

Salesforce: Campaign Influence with Primary Campaign Source Model

Primary Campaign Source Model (or Salesforce Model) is the only OOTB available model in the customizable campaign influence if you do not have MCAE/Pardot, but you can create your own model as explained here.

How does the system add a campaign as Campaign Influence?

There are 2 sources of data that create Campaign Influence records for the Primary Campaign Source Model:

1. Primary Campaign Source

When the Primary Campaign Source field is populated in the Opportunity, it will be auto-added as Campaign Influence, even if it does not fit the Auto-Association rules. The Influence % will always be 100%.

Changing the Primary Campaign Source will set the previous campaign to 0% and the new Campaign to 100% influence. The same applies if you remove the Primary Campaign Source from the Opportunity; it will be set to 0%, but make sure the Record Preference is set to "All Records" in the model setting.

2. Opportunity Contact Role

The Contact added to the Opportunity Contact Role must fit both Auto-Association rules and the Campaign Influence Time Frame. The Record Preference on the Campaign Influence Model Setup must be set to "All Records"; the Influence % will be 0%. 

If you set Record Preference to "Records with >0% Attribution", the Campaign Influence record will not be created, as it is 0% influence.

So, if the Opportunity has Primary Campaign Source and Opportunity Contact Role that are linked to different Campaigns, all Campaigns will be added as Campaign Influence. The same applies if the Contact in Opportunity Contact Role links to multiple campaigns that fit the auto-association rules and time frame; all Campaigns will be added as Campaign Influence. They can be from the same or different contacts in the Opportunity Contact Role.

If the Campaign in the Primary Campaign Source is the same Campaign from the Opportunity Contact Role, only 1 Campaign Influence record will be created with 100% influence.

Time Frame Days

As per Salesforce's article, the time frame days for auto-association are based on 

(Opportunity created date - Campaign Member created date (First Associated Date)) should be less than the Time Frame.

Example: We set the time frame to 365 days.

• Opportunity created date (September 15, 2024) - Campaign Member created date (December 10, 2024) =  -86. -86 is less than 365 (time frame days), so the Campaign is considered influential to the Opportunity. 
• Opportunity created date (September 15, 2024) - Campaign Member created date (December 10, 2022) =  645. 645 is more than 365 (time frame days), so the Campaign is not considered influential to the Opportunity.

Based on the above definition, any contact added to the Opportunity Contact Role and also added to a Campaign after the Opportunity is created will be considered for Campaign Influence (assuming it fits the Auto-Association rules).

Summary:

• The count of Campaign Influence with this model is usually higher than the count of Opportunities with Primary Campaign Source; the Campaign Influence records may be created from the Opportunity Contact Role. 

My testing results in a Dev Edition org:

• Campaign Active status does not affect Campaign Influence.
• Opportunity Open/Closed status does not affect Campaign Influence.

However, in the Campaign Influence implementation guide, it mentions:

• "Influence models scan active campaigns to identify members who are also assigned a contact role on an open opportunity"
• "After an opportunity’s stage is closed (won or lost), influence records are no longer created"

So not sure why my dev org behaves differently from the implementation guide:

Reference:

• Understanding the Salesforce Campaign Influence Time Frame
• Salesforce Platform: Implement Custom Campaign Influence Models with Apex Triggers
• Campaign Influence implementation guide

Excel: Copy Value/Formula

As a Salesforce admin, at a point in time, we may need to use Microsoft Excel (unless you use Google Spreadsheet). Here is the shortcut: assume you have an Excel spreadsheet with hundreds of thousands of rows, and you need to copy a value or formula.

Filtering the rows and double-clicking the fill handle will stop copying when it reaches a row that is not blank.

Sample data:

If we filter column A = Z2, type "y" in B3, then double-clicking the fill handle of cell B3 will stop at row 4, as row 5 is not blank.

So here are the correct steps:

1. Filter column A = Z2

2. In the Name Box (upper left), type B3:B1000 (assume you have 1000 rows), then press Enter

3. Enter Alt + ; to select visible cell only

4. Type "y" in the Formula Bar and hit Ctrl + Enter  << replace "y" with any formula, such as: =A3

5. Done; all visible rows from B3 - B1000 will be filled with "y"

blue arrow is the Name Box, and the green arrow is the Formula Bar

If you need to fill a formula instead of a static value, change the "y" in step (4) to a formula, such as: =A3&"y"

Reference:

• Excel: Copy Formula

Salesforce Marketing Cloud Personalization: Using to Data Cloud segment

Continue from previous blog Using to Data Cloud Segment in MCAE

Create Segment in Data 360

1. Add the segment attributes
2. Schedule and publish the segment
3. Activate the segment to Marketing Cloud Personalization (you may see the platform name as Salesforce Interaction Studio)

Segment in Marketing Cloud Personalization

1. Monitor the segment is published for the activation created
2. The segment will be automatically created in Marketing Cloud Personalization as Manual Segment (notice M orange icon)
3. If you change the segment name in Data 360, the segment name in Marketing Cloud Personalization will stay as the original segment name, however, the it still connected to Data 360, so segment members change in Data 360 will update segment members in Marketing Cloud Personalization

Salesforce MCAE: Using to Data Cloud segment

How to use Data Cloud segments in MCAE?

Create Segment in Data 360

1. Make sure the Account Engagement Integration ID is mapped in the object for the segment
2. Use the default data space
3. Add the segment attributes
4. Schedule and publish the segment

** Interestingly, we do not need activation to allow the segment to be used in MCAE.

Create Dynamic List in MCAE:

1. Best practice: create the dynamic list name to include the segment name of Data 360, so at a glance you know which segment is used; for example: Dyn_D360_Automotive West; Dyn = dynamic, D360 = use Data 360 segment, and Automotive West = the segment name in Data 360.
2. Up to 100 dynamic lists can be built in MCAE using Data Cloud Segments
3. Up to 1 million prospects per list
4. Each segment can be connected to one dynamic list; this is why the naming convention in dynamic lists is important.
5. A new dynamic list can take up to 3 hours to populate for the first time. Allow up to 1 hour for a dynamic list to update after a Data 360 segment is refreshed.
6. Record counts can vary between MCAE dynamic lists and Data 360 segments. For example, prospects in the Recycle Bin or those without a matching Data 360 Account Engagement Integration ID and Salesforce ID aren't counted. Segments in Data 360 can be built on Individuals, Unified Individuals, Leads, or Contacts. The object that you choose affects the value in the Account Engagement Integration ID field.
7. You can use a calculated insight to define and calculate multidimensional metrics in Data 360 based on prospect data or email engagement data from MCAE, then use the calculated insights to define segment criteria.
8. If we change the segment name in Data 360, the "Segment Name" information in the dynamic list created will be updated too.

Sample dynamic list from the screenshot: red boxes are the dynamic list information, and blue boxes are segment information.

Reference: 

• Build Segments and Analytics with Data 360

Salesforce: Add to Campaign

Background: A user can add a Lead or Contact to a Campaign via the "Campaign History" related list. 

But the user does not see the "Add to Campaign" button in the list view or report. What is the cause?

Finding: The user needs the following permissions:

• Enable "Marketing User" on User Records
• Campaigns: Read and Edit permissions.

Additionally, the user does not need to have create or edit permission on the Campaign object.

Salesforce: Contact in Opportunity

Continue from the previous blog: Primary Campaign Source, Opportunity Contact Role, and Campaign Influence. You may be aware that there is a field called Contact ID in the Opportunity:

• API Name = ContactId, Label = Contact ID
• This is a standard Salesforce field, not a custom field
• The field type is a lookup to Contact
• The field is not visible in the Object Manager
• You can query the field with SOQL

This field value is auto-populated when a contact is added and marked as Primary in the Opportunity Contact Role. Note that we can only have one Primary contact for one Opportunity.

Dreamforce 26 Tips

If this is your first time attending Dreamforce, here are a few tips:

Shoes - wear comfortable shoes. Dreamforce campus is huge and will require us to walk around, so please wear comfortable shoes; also, not brand-new shoes.

Bag/backpack - security is always strict. Bags or backpacks exceeding 12 by 18 by 6 inches will not be allowed into Dreamforce. Bring only essentials, as Dreamforce is a long day.

Clothes - Wear layers and comfortable, sneakers; jeans, plain shirts, vests, hoodie are just fine.

Lunch - a meal will be provided in a lunchbox from 11.30 am to 1.30 pm with multiple choices. Morning and afternoon beverages are available throughout Howard Street, Moscone North, and Moscone West. Don't skip meals because you're "too busy."

Hydrate - bring your water bottle; there will be many water stations across the Dreamforce campus for refills.

Power bank - as we may use our phones a lot throughout the day, the battery will drain quickly. Bring a portable charger to avoid the panic of finding a power port.

Explore - there will be 100+ booths from Salesforce and partners; it's good to explore the ones you're interested in, see their product live demo, and ask questions.

Break and recharge - there are hundreds of sessions, and we will not be able to attend all. Build your agenda with breaks in between for a rest; it is much more time-saving than falling sick. Also, grab some protein bars to bring with you just in case you need food

Agenda - build your Dreamforce agenda from https://reg.salesforce.com/flow/plus/df26/myagenda/page/myagenda  

Additionally, you can install the mobile app called "Salesforce Events" from the App Store or Google Play. You can also sync the agenda to your Outlook/Gmail

Session - some sessions need to be reserved in advance (from agenda builder) and need to be queued before the session starts, so come early when possible, be flexible, and many sessions can be watched later online at https://www.salesforce.com/plus. So, don't assume you can just walk in at every session.

Wi-Fi - there will be free Wi-Fi across Dreamforce, although sometimes the connection can be patchy when there are too many people

• Wi-Fi Network: #DF26
• Password: Dreamforce2026

Quiet zone - in case you need to have a quiet zone for calls, rest, etc.

• Moscone North, Level 1, Room 113
• Marriott Marquis, Level 4, Pacific F
• InterContinental, Level 3, Yerba Buena
• Yerba Buena Center, Mindfulness Meadow

Official FAQ: https://www.salesforce.com/dreamforce/faq/ 

Dreamforce Campus Map (screenshot from the Salesforce event mobile app)

MCAE and Salesforce Email in Data 360

In this blog, we discussed Email Activity DLO, or Email Engagement DMO. This blog will continue with how the MCAE and Salesforce email information is stored in Data 360.

We can get information from 3 objects related here; ideally, this should be done in DMO.

1. Email Engagement DMO / Email Activity DLO

This is the base object that stores all emails sent from MCAE. This object can be related to the Bulk Email Message DMO with key: Email Engagement object (field Bulk Email Message) related to Bulk Email Message object (field Bulk Email Message Id). The relationship needs to be set up from the child object, which is the Email Engagement DMO.

A few important fields from this object:

• Bulk Email Message Id starts with the prefix 0XB
• Engagement Date Time: when the record is created
• Individual information starts with the prefix 003 (contact) or 00Q (lead)
• Engagement Channel Action: SEND_STATUS, CLICK, and OPEN
• Email Recipient Send Status: SENT or blank

Again, you can check all other fields in this object from this blog.

2. Bulk Email Message DMO / List Email DLO

We can treat this as the parent object, Email Engagement; the data is coming from both emails sent from Salesforce List Email and from MCAE, which is synced from data stored in the ListEmail object in Salesforce.

We can create a Salesforce report using the "Bulk Email Message with Email Engagement" report type; however, because of the "with", this means only Bulk Email Message has child records of Email Engagement DMO, which also means the results are ListEmail sent from MCAE only.

Because the Campaign ID field is in this object, we can relate it to the Campaign DMO. The relationship needs to be created from this object to Campaign. 

The 1st relationship is the Bulk Email Message object with key field = Campaign, related to the parent object Campaign with key field = Campaign ID.

The 2nd relationship is the same as mentioned in the Email Engagement DMO relationship above.

A few important fields from this object:

• Send Method Type: List Email or Automated Email
• Sent Via: Salesforce or Pardot
• Email stat fields such as: Total Sent, Total Delivered, Total Hard/Soft Bounces, Unique Opens, and Unique Clicks
• Subject
• From Name and From Address
• Name, which is the List Email name

Creating a Salesforce report using the Bulk Email Message report type will return all records for emails sent from Salesforce and MCAE, similar to a query to Salesforce's ListEmail object.

3. Campaign

As the objects have been linked, we can use Query Editor to run SQL to get the results as needed. So, instead of showing Campaign ID, we can get the campaign name, owner, start date, end date, etc. 

Salesforce: Sales Engagement email

A few out-of-the-box custom report types are created when you enable Sales Engagement (pka High Velocity Sales) in your Salesforce org.

Cadences & Steps

Cadences & Steps with Monthly Metrics

Cadences & Trackers

Each report type produces different results; for example, Cadences & Steps uses Cadences as the primary object, with or without related records from Cadence Steps. This report will produce the effectiveness of cadences and cadence steps, all the metrics about the Cadence and its Steps—one cadence usually has many steps. The Step Cadence object gives all information related to the step; for the email step cadence, it will contain: 

• All Email Sent Count
• All Email Delivered Count
• All Email Bounced Count
• All Email Opened Count 
• All Email Link Clicked Count

and many more. Check this article: Asset Engagement Reports for Sales Engagement. To see all out-of -the-box Sales Engagement reports, look for the report folder 'Sample Sales Reports' or 'High Velocity Sales Dashboard Reports'.

Sample report: As you can see from the screenshot, the Step Name is auto-generated and doesn't really tell anything, but the Step Title and Type from Cadence Step are useful.

If we group the report by Type, here are a few values available:

• Root
• Make A Call
• Send An Email
• AutoSendAnEmail
• Create Task
• LinkedInMail
• LinkedInConnection

For email, there are Type = Send An Email and Auto Send An Email. As per the name, AutoSendAnEmail will let the system automatically send the email, while Send An Email is for the user to review and send the personalized email. Both are still available in the Cadence Builder Classic and Cadence Builder 2.0.

Dive deeper if you need to understand how the data/metric is stored; they are stored in 2 different objects: ListEmail or ActionCdncStpMonthlyMetric. Check the Type field on the ActionCadenceStep object:

• Step Type = AutoSendAnEmail >> ListEmail 
• Step Type = SendAnEmail >> ActionCdncStpMonthlyMetric 

ListEmail (0XB)

Sample SOQL: 

SELECT Id, Name, CreatedDate, TotalSent, TotalDelivered, ActionCadenceStepId, ActionCadenceStep.ActionCadenceId, ActionCadenceStep.Type, ActionCadenceStep.TypeDetail, ActionCadenceStep.MessageType FROM ListEmail WHERE CreatedDate = last_month AND ActionCadenceStepId <> '' ORDER BY CreatedDate 

Items to note from the above query:

• ActionCadenceStepId filter with NOT BLANK; this is to get only list emails sent from Sales Cadence, and not from MCAE or Contact list view, Lead list view, or Campaign Members list view.
• Even if ActionCadenceStepId is not blank, ActionCadenceStep.ActionCadenceId can be blank. To overcome this, you can look into the ActionCadenceStepTracker object.
• For the same list email Name, the ActionCadenceStepId can be duplicated with different create dates.
• Total email metrics can be grouped by ActionCadenceStepId

ActionCadenceStep (8C8)

Sample SOQL:

SELECT Id, CreatedDate, StepName, StepTitle, Type, TypeDetail, ActionCadenceId, AllEmailsSentCount, AllEmailsDeliveredCount FROM ActionCadenceStep WHERE CreatedDate = last_month ORDER BY CreatedDate 

Check this and this article for reference.

Items to note from the above query:

• TypeDetail: If the step is a cadence step flow, this field contains the flow name. Otherwise, this field contains the same value as the Type field. 
• StepName will contain the step Name, such as 2a92c03f-c1f9-4b2c-9548-42c0c564ec34, bacb7132-e45f-42cf-94f8-85ccef2515a6; unless Type = Root, this name will be 'RootNode'.
• Step Title usually has values such as: Day 1: Email, Day 9: Make a Call, Day 6: Send Connection Request, but the cadence creator can type in anything as their preferred, but a good practice to enter a meaningful step name; this will help any user understand the step more easily.
• This object is a child of ActionCadence, so you will have multiple records with the same ActionCadenceId.
• The email metrics starting with AllEmails* are available for SendAnEmail and AutoSendAnEmail.

ActionCadence (77C)

Sample SOQL:

SELECT Id, CreatedDate, Name, Type, TotalSteps, TotalTargets, ActiveTargets, ActivatedDate, FolderName FROM ActionCadence WHERE CreatedDate = last_month ORDER BY CreatedDate 

Items to note from the above query:

• Type: this may contain Standard, Quick, or SDR -- using cadence builder will be Standard
• FolderName: where the cadence is stored
• ActiveTargets: the total number of active targets that are currently assigned to this cadence; you will see this number when you open the Cadence Targets in the user interface.
• TotalTargets: the total number of targets that have been assigned to this cadence.
• ActivatedDate is usually the same as CreatedDate, but with only date information

Check out this article for reference.

ActionCadenceStepTracker (8HF)

Represents a step in an active cadence for a specific cadence target. An ActionCadenceStepTracker record is created when a target moves to a new step in a cadence. Use ActionCadenceStepTracker to find information such as the step's current state, the reason it completed, and its type.

Sample SOQL:

SELECT Id, CreatedDate, ActionCadenceId, ActionCadenceName, ActionCadenceStepId, StepTitle, StepType, TargetId FROM ActionCadenceStepTracker WHERE ActionCadenceStepId = '8C8xxxx' ORDER BY CreatedDate 

Items to note from the above query:

• TargetId: this is a Lead or Contact ID
•  You can get the Cadence Name, Step Title, and Step Type without needing to get it from their object

One interesting thing about querying this object: you will get ActionCadenceId and ActionCadenceName, even though they are not/no longer visible when querying the ActionCadenceStep object.

Check out this article for reference.

ActionCdncStpMonthlyMetric (0gk)

Sample SOQL:

SELECT Id, CreatedDate, ActionCadenceStepId, ActionCadenceStep.ActionCadenceId,  AllEmailsSentCount, AllEmailsDeliveredCount, AllEmailsHardBouncedCount, AllEmailsSoftBouncedCount, AllEmailsOpenedCount, AllEmailsLinkClickedCount FROM ActionCdncStpMonthlyMetric WHERE CreatedDate = this_month

If the ActionCadenceStepId is recorded in this object, it will not be available in the ListEmail object. As mentioned earlier, record in this object only for SendAnEmail, not from AutoSendAnEmail.  

Unfortunately, some of these objects, such as ActionCdncStpMonthlyMetric, are not available in the CRMA dataflow/recipe. But Salesforce provides 3 out-of-the-box CRMA dashboards; check this article.

Salesforce Opportunity: Primary Campaign Source, Opportunity Contact Role and Campaign Influence

Opportunity Created from Lead Conversion

Salesforce out-of-the-box behavior during Lead Conversion (when creating a new Opportunity):

Primary Campaign Source
If the Lead is a member of one or more campaigns, Salesforce populates the Opportunity's Primary Campaign Source with the campaign most recently associated with the Lead, no matter what the Campaign detail or Campaign Member status.

Opportunity Contact Role (OCR)

Salesforce automatically creates the converted Contact and associates it to the new Opportunity as an Opportunity Contact Role (marked as Primary and without a role).

Campaign Influence

Campaign Influence relies on both Campaign Membership and Opportunity Contact Roles. When the converted Contact has campaign memberships and is related to the Opportunity through an OCR, Salesforce creates Campaign Influence records according to auto-association rules and model settings. But it only shows in the Campaign Influence related list for the Default Model.

Opportunity Created from Opportunity tab or from Account record

Primary Campaign Source

Blank by default. Salesforce does not automatically populate it because there is no Lead conversion context.

Opportunity Contact Role (OCR)

No OCR is created automatically. The user must manually add one or more Contact Roles.

Campaign Influence

• No Campaign Influence records are created initially.
• Campaign Influence can only be generated after:
• A Contact is added as an OCR, and
• That Contact is a member of one or more Campaigns, and
• The Campaign Influence model criteria are met.

Opportunity Created from a Contact Record

Primary Campaign Source

Salesforce automatically populates the Primary Campaign Source with the most recently associated Campaign of that Contact. Users can see and can change the value before creating the Opportunity.

 

Opportunity Contact Role (OCR)

Salesforce automatically adds that Contact to the Opportunity as an OCR.

Campaign Influence

If the Contact is a Campaign Member, Campaign Influence records will be created automatically according to the enabled influence model(s).

Enabling Customizable Campaign Influence

When enabling Customizable Campaign Influence, the Auto-Association Rules are enabled with the Time Frame of X days. Salesforce will evaluate existing opportunities and create Campaign Influence records for historical opportunities, including closed opportunities, as long as they meet the criteria:

• The Opportunity must have at least one Opportunity Contact Role (OCR).
• The Contact in the OCR must be a Campaign Member.
• The Campaign Member engagement date must fall within x days before the Opportunity Created Date.

After enabling Campaign Influence, Salesforce does not necessarily create all historical influence records immediately. Background processing evaluates opportunities and campaign memberships and creates influence records for qualifying opportunities.

Also note that Primary Campaign Source is not backfilled by Customizable Campaign Influence; the existing opportunities may receive Campaign Influence records, but the Opportunity's Primary Campaign Source field remains whatever value it already has (or blank if never populated).

Salesforce: Member Status Updated

What is Member Status Updated in the Campaign History of a Contact or Lead?

As per the Salesforce article, Member Status Update Date Behaves as Last Modified Date on Campaign Members.

Example

Event	Member Status	Member Status Updated Date
Added to Campaign	Sent	1-Jul-2026 10:00
Status changed to Opened	Opened	5-Jul-2026 09:00
Custom Campaign Member field updated	Opened	6-Jul-2026 14:00

In the above example, the field would show 6-Jul-2026 14:00, even though the status itself remained Opened

Salesforce: SOQL Knowledge

Before we start, let us put a few notes related to Salesforce knowledge:

• You can create a multi-level category: parent, child, grandchild, etc.
• You can assign multiple categories in an article, including ones that are not from the same hierarchy
• You can assign the article to the parent only (not including the child)

There are 2 special keywords to query the knowledge object, but you can't get the data category in the query result. 

1. WITH DATA CATEGORY
2. AT, BELOW, ABOVE, ABOVE_OR_BELOW

Sample: SELECT Id, Title FROM Knowledge__kav WITH DATA CATEGORY Product__c AT Laptop__c

You must use the API Name in the query such as: All_Product__c, not the label: All Product

1. AT (Exact match only)

2. BELOW (Category + all children)

3. ABOVE (Category + all parents)

4. ABOVE_OR_BELOW (Everything around it)

Tips:

You can use the AND logical operator, but not the OR logical operator. Example: you have article with two Category Groups, sample: SELECT Id, Title FROM Knowledge__kav where PublishStatus = 'Online' WITH DATA CATEGORY Region__c AT Singapore__c AND Product__c AT Mobile__c

To query with more than one Category Name, use parentheses and a comma, sample: SELECT Id, Title FROM Knowledge__kav where PublishStatus = 'Online' WITH DATA CATEGORY Region__c AT (Singapore__c, Malaysia__c)

Reference: 

• WITH DATA CATEGORY

CRM Analytics: Multi Currencies

When we pull data from Salesforce with multi-currency enabled, what will the value be in the CRM Analytics dataset? We usually find this in the Opportunity Amount.

Answer: It would be in the Corporate currency.

Sample of an Opportunity in EUR:

Result CRM Analytics:

How about enabling the Dated Exchange Rate?

Answer: It would be in the Corporate currency and follow the exchange rate as per the date range.

Sample of Opportunities in EUR:

Result in CRM Analytics:

Architecture Email Sent from Salesforce and Account Engagement

1. Account Engagement Emails & Account Engagement Email Templates datasets

In this blog, we discussed Account Engagement Emails (API Name = pdEmail) and the Account Engagement Email Templates (API Name = pdEmailTemplate) datasets from the B2B Marketing Analytics app in CRM Analytics. Each row represents one day of statistics for an individual list email or an individual email template

AE Email + AE Email Template Dataset Fields

Type	Account Engagement Emails	Account Engagement Email Templates
Measures	Click To Open Ratio
	Delivery Rate	Delivery Rate
	Opt Out Rate	Opt Out Rate
	Spam Complaint Rate	Spam Complaint Rate
	Total Bounced
	Total Clicks	Total Clicks
	Total CTR	Total CTR
	Total Delivered	Total Delivered
	Total Hard Bounced	Total Hard Bounced
	Total HTML Opens	Total HTML Opens
	Total Opt Outs	Total Opt Outs
	Total Queued	Total Queued
	Total Sent	Total Sent
	Total Soft Bounced	Total Soft Bounced
	Total Spam Complaint	Total Spam Complaint
	Unique Clicks	Unique Clicks
	Unique CTR	Unique CTR
	Unique HTML Open Rate
	Unique HTML Opens	Unique HTML Opens*
Dates	Created At Date	Created At Date
	Sent On Date
	Stats Date	Stats Date
	Updated At Date	Updated At Date
Dimensions	Campaign ID (CRM)	Campaign ID (CRM)
	Campaign ID (external)	Campaign ID (external)
	Campaign	Campaign
	Created By	Created By - User ID (CRM)
	Email ID	Email Template ID
	Name	Name
	Sender	Sent To
	Stats ID	Stats ID
	Subject	Subject
	Suppressed From
	Tags	Tags
	Type
	Updated By	Updated By - User ID (CRM)

Look at the Salesforce article B2B Marketing Analytics Datasets; there is a slight difference, as some fields are not available, such as bounce rate. Even between the email and email template datasets, there are slight differences in the field availability; see the blank values in the Account Engagement Emails and Account Engagement Email Templates columns.

There are 2 major challenges/issues with the email template dataset:

1. There is no Sent on Date, although we can use Stats Date and Total Sent
2. *Unique HTML Opens always show 0; this is a bug and has been captured as a known issue.

As you read above, there is no detail on who the email is sent to in the Account Engagement Emails and the Account Engagement Email Templates datasets.

2. Prospect and Activity dataset 

The Prospect and Activity (API Name = pdProspectActivity) dataset is another dataset provided by the B2B Marketing Analytics app. Each row represents a unique engagement activity and data related to the prospect who performed it, so emails sent to the prospect does not count and are not available in this dataset. 

If a prospect performs multiple activities, such as list email click, list email open, landing page view, etc., they will appear as multiple rows with different activity dates (including timestamps). 

Here is the Salesforce article: Prospect and Activity Dataset.

The screenshot below filters the prospect and activity by ID (using Lead ID)

Here are the fields and the types available in this dataset:

Prospect and Activity Dataset

Measures	Score
Dates	Activity Date
	Prospect Assigned Date
	Prospect Created At Date
	Prospect CRM Last Activity Date
	Prospect Last Activity Date
	Prospect Updated At Date
Dimensions	Account
	Account ID
	Account Name
	Activity
	Activity Campaign ID
	Activity Campaign Name
	Address One
	Address Two
	Annual Revenue
	Archived
	Asset Activity
	Asset ID
	Asset Name
	Asset Type
	Assigned User First Name
	Assigned User Full Name
	Assigned User Last Name
	CRM Contact FID
	CRM Lead FID
	CRM Owner FID
	City
	Comments
	Company
	Country
	Created By First Name
	Created By Full Name
	Created By Last Name
	Department
	Employees
	Email
	External ID
	Fax
	First Name
	Full Name
	ID
	Grade
	Industry
	Is Do Not Call
	Is Do Not Email
	Job Title
	Last Name
	Month
	Name
	Opted Out
	Pardot Account ID (external)
	Pardot Activity ID
	Pardot Asset ID
	Phone
	Prospect Archived
	Prospect ID (external)
	Prospect Job Title
	Prospect Pardot Campaign Id
	Prospect Type
	Salutation
	Source
	Source Campaign
	State
	Tags
	Territory
	Updated By First Name
	Updated By Last Name
	Website
	Years In Business
	Zip

We can relate the Pardot Asset ID in this dataset to the Email ID in the Account Engagement Emails and Account Engagement Email Templates datasets.

ID (API Name: ActorID) in this dataset is the Salesforce Lead ID or Contact ID.

Prospect Type will contain 'Lead' or 'Contact' only.

If you are working with the email performance report/dashboard, you can't use this dataset as it only contains email click and open information, not email sent.

The following screenshot filters the asset type = List Email and Automated Email.

3. Pardot Engagement History dataset

This is another CRMA dataset with API Name = Pardot_Activity, but provided by the B2B Marketing Engagement History app, not the B2B Marketing Analytics app, where the datasets mentioned above are.

✔ 1 row = 1 activity event (per prospect per interaction); email sent to the prospect is not recorded in this dataset.

I see this dataset is almost similar to the Prospect and Activity dataset, but lighter. It contains many similar fields, such as: ID (API Name: ActorID, which is Lead ID or Contact ID), Activity, Asset Activity, Asset ID, Asset Name, Asset Type, Prospect ID, Prospect Type, Pardot Activity ID, and Pardot Asset ID. But does not contain detailed information about the Prospect, such as Job Title, Address, Annual Revenue, Company, etc. -- this information can be retrieved from the Account Engagement Prospects dataset in the B2B Marketing Analytics app.

Pardot Engagement History Dataset

Dates	Activity Date
Dimensions	Account
	Account ID
	Activity
	Activity Campaign ID
	Activity Campaign Name
	Activity Date (Month Name)
	Asset Activity: Priority Page View, Landing Page View
	Asset ID
	Asset Name
	Asset Type:
	External ID
	ID
	Name
	Pardot Account ID
	Pardot Activity ID
	Pardot Asset ID
	Pardot Prospect Archived
	Pardot Prospect Job Title
	Prospect Pardot Campaign Id
	Prospect Type (Lead or Contact)

Sample values of the Activity and Asset fields:

Asset Activity, in short, is Asset Type + Activity.

As mentioned in the Prospect and Activity dataset, if you are working with the email performance report/dashboard, you can't use this dataset, as it only contains email click and open information, not email sent.

The following screenshot filters the asset type = List Email and Automated Email.

4. ListEmail object

This is not a CRM Analytics dataset; it is a Salesforce object. Each record in the ListEmail object represents a list email sent from Salesforce or sent from Account Engagement. However, this object does not include emails sent manually from Salesforce; those records are stored in the EmailMessage object.

The ListEmail record prefix is 0XB, while the EmailMessage record prefix is 02s.

The email sent via Sales Cadence will have the ActionCadenceStepId set to the prefix 8C8; the Type will be "List Email" and SentVia will be "Salesforce".

The ListEmailIndividualRecipient object links the List Email to recipients, represented by the Lead or Contact ID. Check the ListEmailId and RecipientId fields. The documentation does not mention whether the data includes emails sent from Account Engagement -- Salesforce confirms that email from MCAE is not added to this object.

The ListEmailRecipientSource object connects the List Email with the source, such as Campaign or List View. SourceListId will be either the List View ID or the Campaign ID. The documentation does not mention whether the data includes emails sent from Account Engagement.

The ListEmailSentResult is another object related to ListEmail; check the ListEmailId and RecipientId fields. As per the documentation, this object represents the results of a list email sent from Salesforce and MCAE; however, I do not see emails from MCAE here. Salesforce support confirmed that the documentation stating that ListEmailSentResult captures data for list emails sent from MCAE and synced to Salesforce is inaccurate; the data from MCAE list emails doesn't reliably populate this object the way the docs suggest, and a documentation bug has already been filed to get that corrected. 

Back to the ListEmail object, for emails sent from MCAE, the record creator will be the user who connects Salesforce with MCAE, such as B2BMA Integration and SentVia = Pardot, while the list email sent from Salesforce (including Sales Cadence) will have SentVia = Salesforce. For Pardot Email, Type = List Email for AE Email and Type = Automated Email for AE Email Template.

There is no Sent Date in this object, but look into the ScheduledDate. 

Salesforce ListEmail Fields

API Name	Type	Refers To
ActionCadenceStepId	Lookup	ActionCadenceStep
CampaignId	Lookup	Campaign
ClickThroughRate	Percent
ClickToOpenRatio	Percent
DeliveryRate	Percent
EmailContentId	Lookup
FromAddress	Textarea
FromName	String
HasAttachment	Boolean
HtmlBody	Textarea
IsTracked	Boolean
LastReferencedDate	DateTime
LastViewedDate	DateTime
Name	String
OpenRate	Percent
OptOutRate	Percent
OwnerId	Lookup	Group, User
ProgramName	String
ScheduledDate	DateTime
SentVia	Picklist
SpamComplaintRate	Percent
Status	Picklist
Subject	Textarea
TextBody	Textarea
TotalDelivered	Int
TotalHardBounced	Int
TotalOpens	Int
TotalOutOfOffice	Int
TotalReplies	Int
TotalSent	Int
TotalSoftBounced	Int
TotalSpamComplaints	Int
TotalTrackedLinkClicks	Int
Type	Picklist
UniqueOpens	Int
UniqueOptOuts	Int
UniqueReplies	Int
UniqueTrackedLinkClicks	Int

5. Engagement Metrics on List Emails

Check out this article: Five recommended custom report types for Account Engagement's Engagement History, and create the "Engagement Metrics on List Emails" report type. There is a known issue related to this report.

This report will show each email as a single record in the aggregate data. Compared to the (1) Account Engagement Emails & Account Engagement Email Templates datasets, the datasets use the Stats Date, which shows email stats for each day, such as how many emails were sent, delivered, opened, etc., on 1-Jun, 2-Jun, etc. While this report includes Created Date and Scheduled Date, in the email template, the Scheduled Date is the creation date, so this does not reflect the daily activity details as offered by the Account Engagement Email Templates datasets.

6. Email Activity DLO or Email Engagement DMO

If you have Data 360 ingested data from MCAE, look for the out-of-the-box Email Activity DLO.  There are 5 data streams from this data: Email Engagement Data, Form Engagement Data, Web Page Engagement Data, Custom URL Engagement Data, and Form Handler Engagement Data. 

For email, select Email Engagement Data. The data stream and DLO will be named EmailActivity_0Uvxxxxx, with API Name EmailActivity_0Uvxxxxx_dll. You can add formula fields in the data stream if needed.

Check out this article for email activity: Marketing Cloud Account Engagement: Email Activity Mapping.

A few standard fields that are provided in the DLO:

• id*: primary key
• externalId: ID of the MCAE Visitor Activity record
• externalSourceId: "pi"
• externalListEmailId: ID of the MCAE List Email record
• externalProspectId: ID of the MCAE Prospect record
• prospectId: <pi-pr-account_id-externalProspectId>
• prospectSalesforceId: ID of the Salesforce record synced with the MCAE Prospect (00Q/003)
• prospectEmail: Email address of the recipient at the time of data ingestion from Account Engagement to Data 360
• polymorphicListEmailId: ID of the Salesforce List Email record (0XB)
• redirectUrl: The URL that the prospect is redirected to when the link is clicked. Only populated when the eventType is CLICK
• createdAt = value of the visitor activity record in Account Engagement
• eventType: type of Email Activity: SEND_STATUS, OPEN, CLICK, BOUNCE, UNSUBSCRIBE, COMPLAINT, SUBSCRIBE
• sendStatus: The default is SENT. Only populated when the eventType is SEND_STATUS
• emailBounceType: SOFT or HARD. Only populated when the eventType is BOUNCE
• emailBounceReason: Only populated when the eventType is BOUNCE, such as: Soft bounce - Other [5.4.0 5.4.0 Error: too many hops]

You can query in Data 360 to retrieve the Email Activity with List Email information, such as Type and Name, for example:

SELECT

    ea."externalId__c", ea."prospectSalesforceId__c", ea."polymorphicListEmailId__c", ea."eventType__c",

    le."Type__c", le."Name__c"

FROM "EmailActivity_0Uvxxxxxxxxx__dll" ea

LEFT JOIN "ListEmail_Home__dll" le

    ON ea."polymorphicListEmailId__c" = le."Id__c"

Reference: 

• ListEmail
• Five recommended custom report types for Account Engagement's Engagement History