B2B Marketing Analytics: Account Engagement Email Datasets

To continue from the previous blog, if you get an error when creating the B2B Marketing Analytics app, once the app is created successfully, you will see a new app for the B2B Marketing Analytics app; the app name is based on what you entered when creating the app. 

Within the app, you will get many datasets available, starting with "Account Engagement". The datasets are updated daily by Salesforce from MCAE to CRM Analytics. You can monitor it from Jobs Monitor, look for jobs that start with "pd".

You will see the dataset in the B2B Marketing Analytics app, including when the data is refreshed.  

** To get the Prospect and Activity dataset, you need to enable "Get Prospect and Activity Data" in the B2B Marketing Analytics setup menu, which is under Optional Features for B2B Marketing Analytics, then select "Include Prospect and Activity dataset?" when creating the app.

The app comes with multiple dashboards, such as "Engagement," which shows MCAE data, such as:

• List Email Engagement
• Email Template Engagement
• Forms Engagement
• Landing Page Engagement

These include different metrics for each type of engagement.

In this blog, I want to discuss two of the datasets: the Account Engagement Emails dataset and the Account Engagement Email templates dataset.

Account Engagement Emails dataset 

As per this article, B2B Marketing Analytics Datasets, the dataset API Name is pdEmail. Each row represents 1 day of statistics for an individual list email. You can see the field description in the article above. Let's see some samples:

For Email ID = 2012421957, emails are sent on 2025-09-10, so you see the same Send On Date applied to rows 1 and 2. Some recipients open and click on the same day, and a few open on 2025-09-11. The same applies for Email ID = 2011677609; emails are sent on 2025-09-09 for rows 3, 4, and 5; some people open and click on the same day and the following days. So, this is aggregate data per day per email.

Stats ID is the unique key in the dataset.

Available metrics for this dataset:

• Click To Open Ratio
• Delivery Rate
• Opt Out Rate
• Spam Complaint Rate
• Total Bounced
• Total CTR
• Total Clicks
• Total Delivered
• Total HTML Opens
• Total Hard Bounced
• Total Opt Outs
• Total Queued
• Total Sent
• Total Soft Bounced
• Total Spam Complaint
• Unique CTR
• Unique Clicks
• Unique HTML Open Rate
• Unique HTML Opens

Account Engagement Email Templates dataset 

From the same article above, the dataset API Name is pdEmailTemplate. Each row represents 1 day of statistics for an individual email template. Email metrics are based on the emails built on selected email templates. 

The email sent here are not from the list email, but automated email, such as the Autoresponder email in Completion Action or Engagement program emails. The email here are not sent at the same time, but only when the user submits a form or receives an auto email from the engagement program.

The Send on Date field is not present on this dataset. This is because templates are reusable email designs that are not tied to specific send events.

We can see the same pattern here: each row represents an Email ID for a day, the total emails sent in a day, email opens, and clicks. 

Similar to the Email dataset, Stats ID is the unique key in the dataset, and almost all metrics are also available in this dataset:

• Delivery Rate
• Opt Out Rate
• Spam Complaint Rate
• Total CTR
• Total Clicks
• Total Delivered
• Total HTML Opens
• Total Hard Bounced
• Total Opt Outs
• Total Queued
• Total Sent
• Total Soft Bounced
• Total Spam Complaint
• Unique CTR
• Unique Clicks
• Unique HTML Opens

Note: There is an issue with the "Unique HTML Opens" field in this dataset. Here is the KI and idea.

Reference:

• B2B Marketing Analytics Datasets

CRM Analytics: Enhancing Dashboard Filter with Custom Range Value

Requirement: Create a filter in the dashboard that bucket sums the Amount of customers into categories, such as low (< $500), medium ($500 - $1000), and high (> $1000). The result should be updated dynamically as filtered when other widgets are selected. The range of the Sum of Amount should be fixed, not depend on the Sum of Amount values in the data.

Solution: As each user can select different filters, such as Region, Product, etc., we can't stamp the sum of Amount in the dataflow/recipe. Binding is here to help.

Step-by-step:

1. Create Custom Query

• We need "All" as the 1st value. This is when the dashboard loads; it will load all data. Remember to select the list widget selection type as "Single selection (required)".
• The max value for All and the high (last) row can be set as high as possible.

2. Add Table Widget

• Change the table to Compare Table
• Group By: Customer or Account name
• Change Column to Sum of Amount
• Add a Filter to the Sum of Amount column, use the medium range, which is between 500 and 1000

3. Add Binding to the Filter Range

• Select the table widget added.
• Click Advanced Editor >> Query tab
• Look for "aggregateFilters" with values 500 and 1000
• Change 500, 1000 to the following

"{{coalesce(cell(static1_1.selection, 0, \"low\"), cell(static1_1.result, 0, \"low\")).asObject()}}",

"{{coalesce(cell(static1_1.selection, 0, \"high\"), cell(static1_1.result, 0, \"high\")).asObject()}}"

• Save the query and done :) 

Result

See the table widget

no filter

6 months ago to today

6 months ago to today, and the Sum of Amount between $500 - $1000

Product X3 and X4, and the Sum of Amount between $500 - $1000

 

Salesforce: Shortcut create Task

Instead of starting with a blank task record when creating a task, we can prepopulate some fields when creating a specific task, and we can create multiple specific tasks.

As shown in the above screenshot, if clicking the New Task green icon opens a new form with blank fields, of course, if the button is in Account, the Related To will be auto-populated with the Account Name.

If we have a specific task where additional fields, such as Type, Subject, etc., should be the same, we can create a Custom Action for the Task. We can even hide the fields from the layout so that users cannot change the pre-populated fields manually when creating a task. The specific tasks will appear under the arrow next to the New Task button in a record page, such as Important-1 and Important-2 in the above screenshot.

Here are the steps to create the task and add it to page layout:

1. Create New Action under Global Actions

Open the action created to set predefined field values, and click Edit Layout to add/remove only relevant fields.

2. Add the action to the object page layout, such as for the Account Page layout

In this sample, I add both Important-1 and Important-2 to the Account Layout (under Salesforce Mobile and Lightning Experience Actions), while only adding Important-2 to the Account (Sales) Layout. 

In other scenarios, if you need the action to be always visible in Salesforce, you can add it to Publisher Layout (under Salesforce Mobile and Lightning Experience Actions), you can create multiple Publisher Layouts, and assign them by profile.

The same approach can be applied to the New Event as well.

CRM Analytics: Join or Augment Master to Child

When you augment the master table with a child table and there are multiple rows for the same master value, which row will the dataflow and recipe select?

Sample Data:

Master

Child

Dataflow

With "Look Up Multiple Values"

With "Look Up Single Values"

Summary:

• Look Up Multiple Values: sum value of all numeric (measure) fields, while dimension (text) fields will retrieve all values, although only 1 value is shown when you browse the data in a lens. Check out this blog. 
• Look Up Single Values: the first matching record for both numeric and text fields.

Recipe

With "Look Up Multiple Values"

Without "Look Up Multiple Values"

Summary:

• With Look Up Multiple Values: sum value of all for numeric (measure) fields, while dimension (text) fields will retrieve all values, "all values" will be shown in the recipe, but in Lens, it will show 1 value only. 
• Without Look Up Single Values: as per this article, Lookup - the lookup returns only the first matching record. However, in my test results, the system will get the row with the lowest value of the numeric field in sort order. If no numeric field, sort the text field alphabetically in ascending order and get the first row.

Reference:

• Using Multi-Value
• Lookup

CRM Analytics: Sort Date field

I recently encountered working with a CSV file containing a date field in dd-MM-yyyy format. CRMA supports this format when manually loading the CSV file. Additionally, you can vote for this idea for more format support.

However, once the field is uploaded to CRMA, even if it is selected as a Date type, it is stored as dimensions, although it automatically generates multiple derived fields (like day, month, year, quarter, day_epoch, etc.).

This causes an issue when you add the field in a table widget and sort it. Because the generated date fields are dimensions, sorting is performed alphanumerically rather than in date order.

Here are a few workarounds:

1. Add the day_epoch field and sort with that field, although it is not a good user experience

2.  Load the CSV file in yyyy-MM-dd format

The system supports using the yyy-MM-dd format when uploading the CSV file

3. Use toDate() function in the dashboard, but this approach will stop your ability to edit the dashboard with clicks

q = load "ddmmyyyy";
q = foreach q generate q.'Id' as 'Id', q.'Amount' as 'Amount', q.'Date' as 'Date',toDate(Date_Year+"/"+Date_Month+"/"+Date_Day,"yyyy/MM/dd") as 'Date_Formatted';
q = order q by 'Date_Formatted' asc;
q = limit q 100;

4. Same as (3) by using toDate() function in a dataflow or recipe.

Check out the samples here and here.

Reference:

• Understanding How Date Information is Uploaded to Analytics
• Handle Date Values

MCAE: Open Prospect user interface with Prospect ID

If you have the Prospect ID, you can open the Prospect detail with the following URL with parameters:

With the Pardot user interface:
https://pi.pardot.com/prospect/read/id/179597888
https://pi.pardot.com/prospect/read?id=179597888

With the Account Engagement user interface:

https://mydomain.lightning.force.com/lightning/page/pardot/prospect?pardot__path=%2Fprospect%2Fread%2Fid%2F179597888