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?

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 and time frame defined. The Influence % will be 100%.

Changing Primary Campaign Source will set the previous campaign to 0% and the new Campaign to 100% influence. The same applies if you delete 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 Opportunity Contact Role must fit both Auto-Association rules and 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 link 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 rule; all Campaigns will be added as Campaign Influence. They can be from the same or different contacts in the Opportunity Contact Role.

Reference:

• Salesforce Platform: Implement Custom Campaign Influence Models with Apex Triggers

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

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