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. 

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 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:

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.

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)

Some values of:

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 sent email from Salesforce; that record is 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 would be "List Email" and SentVia = "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. 

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

Field Name	Field Label	Type	Digits	Length	Precision	Scale
ActionCadenceStepId	Cadence Step ID	reference		18
CampaignId	Campaign ID	reference		18
CcAddress	CC Address	textarea		320
CreatedById	Created By ID	reference		18
CreatedDate	Created Date	datetime
CurrencyIsoCode	Currency ISO Code	picklist		3
DeliveryRate	Delivery Rate	percent			5	2
FromAddress	From Address	textarea		320
FromName	From Name	string		121
HasAttachment	Has Attachment	boolean
HtmlBody	Html Body	textarea		32000
Id	List Email ID	id		18
IsDeleted	Deleted	boolean
IsEmailArchiveRequired	Is Email Archive Required	boolean
IsSyntheticCcEnabled	Synthetic CC Enabled	boolean
IsTracked	Is Tracked	boolean
LastModifiedById	Last Modified By ID	reference		18
LastModifiedDate	Last Modified Date	datetime
LastReferencedDate	Last Referenced Date	datetime
LastViewedDate	Last Viewed Date	datetime
Name	Name	string		255
OpenRate	Open Rate	percent			5	2
OwnerId	Owner ID	reference		18
RelatedToId	RelatedTo ID	reference		18
ReplyToName	Reply To Name	string		121
ScheduledDate	Scheduled Date	datetime
Status	Status	picklist		255
Subject	Subject	textarea		3000
SystemModstamp	System Modstamp	datetime
TextBody	Text Body	textarea		32000
TotalDelivered	Total Delivered	int	9
TotalHardBounced	Total Hard Bounces	int	9
TotalOpens	Total Opens	int	9
TotalOutOfOffice	Total Out Of Office	int	9
TotalReplies	Total Replies	int	9
TotalSent	Total Sent	int	9
TotalSoftBounced	Total Soft Bounces	int	9
TotalTrackedLinkClicks	Total Clicks	int	9
UniqueOpens	Unique Opens	int	9
UniqueReplies	Unique Replies	int	9
UniqueTrackedLinkClicks	Unique Clicks	int	9

Reference:

• ListEmail
• ListEmail
• EmailMessage

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