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 and the Account Engagement Email Templates 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
	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 from 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 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, 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 with an 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 Salesforce Lead ID or Contact ID.

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

3. Pardot Engagement History dataset

This is another CRMA dataset, 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)

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.

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 manually email sent 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 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.

For email 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

Reference: 

• ListEmail

CRM Analytics: Aging between Status

Requirement: to get aging across the record status using object history data. Status is enabled for tracking. In this example, I'll use the following Lead statuses: Unqualified, MAL, MQL, SAL, and SQL. The lead status is not always moving linearly, but can move forward and backward, for example, SAL can move to Unqualified.

In this blog, I am going to get the aging from MQL to SQL only; the same logic can be used to find the aging for other statuses as well.

Solution: using CRM Analytics dataflow. ComputeRelative will play a major role in the solution; however, the ComputeRelative field output is only Numeric or Text, so we will use "sec_epoch" of the created date, which is automatically converted to a number by CRM Analytics.

The idea here is to use ComputeRelative to create multiple helper fields to get the aging for each lead.

Dataflow logic:

1. Get LeadHistory object

2. Filter the data retrieved

'Field' == "Status"

3. ComputeRelative #1: get the MQL_Date_Enter 

case when current(NewValue) == "MQL" then current(CreatedDate_sec_epoch) end

4. ComputeRelative #2: get the Last_MQL_Date

case when current(MQL_Date_Enter) > 0 then current(MQL_Date_Enter) else previous(Last_MQL_Date) end

5.ComputeRelative #3: get the delta between MQL and SQL in seconds

case when current(NewValue) == "SQL" and current(Last_MQL_Date) > 0 and current(CreatedDate_sec_epoch) > current(Last_MQL_Date) then current(CreatedDate_sec_epoch) - current(Last_MQL_Date) end

6. ComputeExpression 

Use this node to convert the result from seconds to days.

If a lead never touches MQL, which means from MAL to SAL, then SQL, it would be excluded from this aging.

Notes:

• You need to use multiple ComputeRelative nodes because if you set the 2nd field to read the 1st field, the system will error because it cannot read the 1st field. Unless the 2nd field does not depend on the 1st field. This is different from computeExpression, where the 2nd field can read the result from the 1st field.
• The _sec_epoch field will be auto-calculated by CRM Analytics; however, if the date field is a result from a computeExpression node, you need to have another computeExpression note to read the date in _sec_epoch value.

CRM Analytics: date_to_epoch

In this blog, we shared in detail to use daysBetween(start date, end date) and date_diff(datepart, start date, end date) functions to compare 2 dates. 

Another function relate to date is date_to_epoch(date).

date_to_epoch(date) converts a date value into Unix epoch seconds—i.e., the number of seconds elapsed since 00:00:00 UTC on January 1, 1970, so the result here is a number in seconds.

 date_to_epoch() must take a toDate() or now() function as its first argument, sample:. 

date_to_epoch(now()) or 

date_to_epoch(toDate(CreatedDate, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")) or date_to_epoch(toDate(CreatedDate_sec_epoch))
-> the last 2 have similar result.

Sample result: 

2024-02-26T14:10:21.000Z → 1,708,956,621, this number represents the number of seconds since 1 January 1970 (UTC). 

2024-01-11T04:48:35.000Z → 1,704,948,515, this number represents the number of seconds since 1 January 1970 (UTC). 

So, if we calculate 1,708,956,621 - 1,704,948,515 = 4,008,106 → 4,008,106 seconds. 4,008,106 / 86,400 → 46.4 days 

CRM Analytics: Simple Binding

Scenario: Instead of creating 2 widgets for Count of Opportunity and Sum of Amount, we need a toggle that lets the user switch between count and sum.

1. Group the widget as per normal, with Count of Rows or Sum of Amount

2. Add Static Query

        "static_1": {

                "broadcastFacet": true,

                "columns": {},

                "selectMode": "singlerequired",

                "start": {

                    "display": [

                        "Amount"

                    ]

                },

                "type": "staticflex",

                "values": [

                    {

                        "display": "Amount",

                        "value": [

                            "sum",

                            "Amount"

                        ]

                    },

                    {

                        "display": "Count",

                        "value": [

                            "count",

                            "*"

                        ]

                    }

                ],

                "numbers": [],

                "strings": [],

                "groups": []

            }

3. Update the Query

From:

           "field": [

                            "count",

                            "*"

                        ]

To: "field": "{{cell(static_1.selection, 0, \"value\").asObject()}}",

Salesforce: Report export and open in Excel

Scenario:  

• Computer locale setting is English (Singapore) → uses DD/MM/YYYY
• Salesforce user account locale is English (United States) → uses MM/DD/YYYY

When opening the CSV file in Excel, the date will be incorrect because Excel expects the date in DD/MM/YYYY format.

Easy and Fast trick option

1. Rename the file extension from .csv to .txt

2. Open Excel

3. Open the .txt file from Excel

   This forces the Text Import Wizard; then:

   - Choose Delimited

   - Select Comma

   - Set the date columns to: MDY

This trick will only work with the date type, but not the date/time field.

Use Power Query

Another option is to use Power Query in Excel; this option works for both date and date/time fields. Here is the quick step:

1. Create a new Excel file (do not open the CSV file directly)

2. Navigate to Data tab >> Get Data >> From File >> From Text/CSV

3. Open the CSV file, click Import

4. Click the "Transform Data" button. This will open Power Query Editor

5. Select the column of the date or date/time columns

6. Right-click on the header

7. Select Change Type >> Using Locale...

8. On the pop-up window, select Data Type = Date (or Date/Time) and Locale = English (United States), click the OK button.

9. Click "Close & Load", the data will be loaded as a table in the Excel file with the correct date or date/time format. You can copy and paste it into a new sheet if you prefer not to be loaded as a table.

Salesforce: CURRENCYRATE()

There are 2 types of currency exchange rates in Salesforce: the default/standard exchange rate and the dated exchange rate, but Dated Exchange Rates is optional; you can enable it if needed.

In this blog, we discussed how to create a formula field using CURRENCYRATE() in this blog; however, the CurrencyRate() function does not support dated exchange rate. So the formula will only use the standard exchange rate. See the report below as an example, dated exchange rate for:

• USD/SGD, standard exchange rate = 1.28
• USD/SGD, 2-May-2026 and before = 1.25
• USD/SGD, 3-May-2026 and after = 1.3

Amount USD is a formula that uses CurrencyRate(), and because the standard exchange rate is 1.28, Amount (converted) uses the dated exchange rate based on the Opportunity Closed Date.

Reference:

• Salesforce: Query Multi-Currency Field
• Salesforce: Dated Exchange Rates versus Conversion Rate