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

B2B Marketing Analytics app: sfdc_internal__B2BMA

B2B Marketing Analytics, a CRM Analytics app within Salesforce designed to analyze marketing and sales data. It leverages datasets from Account Engagement (formerly Pardot) and Salesforce to provide insights into campaign performance, prospect behavior, and overall marketing effectiveness. 

You can create the B2B Marketing Analytics app from Analytics Studio, select B2B Marketing Analytics, and follow the wizard.

You need to enter your Pardot Account ID, then select optional features, such as Account-Based Marketing, Multi-Touch Attribution, Prospect and Activity dataset, etc. If you stopped with the following error:

Unable to create app based on template: [sfdc_internal__B2BMA].

* Your org does not currently meet minimum data requirements to proceed. Please fix the following issues before creating a 'B2B Marketing Analytics' Application:

In the 'sfdcDigest_Contact_CWA' node, the 'pi__grade__c' field doesn't exist, is deprecated, or isn't accessible to the Integration User. In the 'sfdcDigest_Contact_CWA' node, the 'pi__score__c' field doesn't exist, is deprecated, or isn't accessible to the Integration User.

You need to adjust the fields in both Lead and Contact, the Account Engagement Score (pi__score__c) and the Account Engagement Grade (pi__grade__c) fields, to make them visible (as read-only is sufficient) to the Analytics Cloud Integration User profile. 

From the object manager, open each field and update the field-level security settings to grant visibility to the Analytics Cloud Integration User profile. 

Re-create the B2B Marketing Analytics app from Analytics Studio, and you should be able to pass the error.

Once the app is created, it can also be seen in the "Auto-Installed Apps" in the setup menu.

Salesforce Field history tracking on Record creation

In Salesforce, field history tracking can be setup for both standard and custom fields. 

On Record Creation
No, field history tracking does not log the initial values when a record is created.

On Record Update

Yes, it tracks changes to the specified fields when they are updated. It logs the old value, new value, user who made the change, and timestamp.

As noted in the last screenshot, the Mailing Country should ideally be auto-updated (from Flow, Trigger, etc.) after the record is created; however, the order can be misleading, as they have the same timestamp. If we use SOQL, will we see the difference? No, they may have the same exact milliseconds

Reference:

• Field History Tracking on Record Creation

Salesforce: Sales Territories (I)

Sales Territory

As per the Summer '24 release, Enterprise Territory Management is now called Sales Territories. 

Out-of-the-box sharing mechanism to share record access to users based on the territory setup:

• Account (View OR View and edit OR View, edit, transfer, and delete) 
• Contact (No access OR View all OR View and edit all contacts associated with accounts)
• Opportunity (No access OR View all OR View and edit all opportunities associated with accounts)
• Case (No access OR View all OR View and edit all cases associated with accounts)
• Leads (View, edit, and transfer OR View, edit, transfer, and delete)

* You can set access for the Contact and Lead if you use Private as the default internal access for contacts or cases.

Default access levels determine the default selection when creating new territory.

Territory Model

• Defines the structure and assignment of sales territories and their associated accounts and users.
• It's a hierarchical structure that organizes sales teams and their responsibilities.
• Allow for flexible assignment of accounts and users to territories based on various criteria.
• You can create and preview multiple models (2 for EE, 4 for UE/PE), but only one can be active at a time.
• Once a model has been created, you can open it to Clone, Activate/Archive, or view more info, such as the activation date, etc.

Territory Type

• It can help you group territories based on geography (e.g., North America, Europe), industry (e.g., Financial Services, Retail), or other factors.
• To categorize territories at a more abstract level, not within the hierarchy itself.
• Can be used in reports to analyze sales performance by type (e.g., compare sales across geographical regions). 
• Every territory you create must have a territory type.
• Territory types are used for organizing and creating territories only: they do not appear on territory model hierarchies.
• Each territory type has a priority (it must be a number). For example, "1" indicates the highest or lowest priority.

Create New Territory

Once the Territory Type and Territory Model are created (Territory Model does not need to be activated), you can add the Territory from the "View Hierarchy" in a Territory Model. When creating a new territory, you need to select:

• Territory Type 
• Parent Territory, this is populated by default
• Description (optional)
• Access level for: Account, Contact, Opportunity, and Case

After a territory is created, you can:

• Edit the Territory to set the Forecast Manager (for forecasting purposes)
• Assign users
• Manually add accounts
• Create assignment rules

- An assignment rule can be assigned to multiple territories
  - You need to run the rule when the account or assignment criteria are changed

• Create a child territory

- Inherited Assignment Rules from the parent territory

• Click the 'View Accounts' button to see all Accounts that fit into the rule. 

Account

Add the Assigned Territories related list to the Account page layout, and you will see all Territories related to the Account

Reference:

• Enterprise Territory Management Is Now Sales Territories
• Salesforce Sharing Using Enterprise Territory Management
• What's new in Enterprise Territory Management
• Managing Users and Roles Within Territories