Salesforce: Campaign Statistics fields

There are 16 fields related to the Campaign Statistics:

1. Responses in Campaign: number of Campaign Members with status HasResponded in the Campaign Member Status
2. Leads in Campaign: number of Campaign Members that are Leads
3. Converted Leads in Campaign: number of Campaign Members that are Leads and have been converted
4. Contacts in Campaign: number of Campaign Members that are Contacts
5. Responses in Hierarchy: similar to (1), but include all child campaigns
6. Leads in Hierarchy: similar to (2), but include all child campaigns
7. Converted Leads in Hierarchy: similar to (3), but include all child campaigns
8. Contacts in Hierarchy: similar to (4), but include all child campaigns
9. Opportunities in Campaign: number of Opportunities with Primary Campaign Source points to that Campaign
10. Won Opportunities in Campaign: number of Won Opportunities with Primary Campaign Source points to that Campaign
11. Value Opportunities in Campaign: total amount of Opportunities with Primary Campaign Source points to that Campaign
12. Value Won Opportunities in Campaign: total amount of Won Opportunities with Primary Campaign Source points to that Campaign
13. Opportunities in Hierarchy: similar to (9), but include all child campaigns
14. Won Opportunities in Hierarchy: similar to (10), but include all child campaigns
15. Value Opportunities in Hierarchy: similar to (11), but include all child campaigns
16. Value Won Opportunities in Hierarchy: similar to (12), but include all child campaigns

What happened when Campaign Influenced is enabled?

• Influenced Opportunities related list available in the Campaign page layout
• Opportunities related list removed from the Campaign page layout
• Campaign Influence related list available in the Opportunity page layout
• Campaign Influence related list available in the Account page layout

Note:

The Campaign related list in the Account page layout is auto-added when a Contact is added as a Campaign Member; it has no relation to the Primary Campaign Source in the Account Opportunity.

CRM Analytics: Write Dataset to CSV file

CRM Analytics allows exporting a dataset into a CSV file and creating a lens as a values table. Ideally, the limit is 2,000 rows; you can extend more rows by change the lens to query mode, such as to 10,000 rows. But let's say you have 500,000 rows and many columns; it will not work.

You can use a recipe to write the dataset into a CSV file. In the output node, select Write To CSV, and  I prefer to select "Use fully qualified names in CSV header".

How to download the CSV file? Follow the following articles: Recipe Output to CSV and Export your Einstein Analytics datasets.

1. Query DatasetExport object

SELECT Id, PublisherInfo FROM DatasetExport

2. Query DatasetExportPart object

Sample query:

SELECT Id, PartNumber, DataFile FROM DatasetExportPart WHERE DatasetExportId = '0PxB0000000......' ORDER BY PartNumber  

Change 0Px.... to the DatasetExport ID from the 1st query. This also means the CSV files are split into multiple parts. Depending on the number of columns and characters, each part can be 10000 or 20000 rows.

3. REST Explorer

 Use Workbench or Salesforce Inspector; get the DataFile value from each row from the 2nd query and perform the REST query with a GET request, then copy the response into a text editor.

Reference:

• Recipe Output to CSV 
• Export your Einstein Analytics datasets.

Data 360: Objects Query

Use SOQL to query the following:

DLO >> DataLakeObjectInstance

• Id - prefix "1dl"
• Name - this is "Field Label" e.g. "Account_Home", "Base Events_Main" 
• ExternalName - this is "Field API Name", e.g. Account_Home__dll, base_events_Main__dll
• DataLakeObjectStatus: ACTIVE
• Category: Profile, Engagement, Other
• DataLakeObjectRef - prefix "0GO"
• Billable: Yes or blank
• TotalNumberOfFields
• TotalRecords

DataStream >> DataStream

• Id  - prefix "1ds"
• Name - e.g. "Account_Home"
• LastRefreshedDate  - date/time
• LastNumberOfRowsAddedCount - rows added in last refresh
• TotalRowsProcessed
• TotalNumberOfRowsAdded - all rows for the object
• RefreshFrequency: BATCH, NONE, MINUTES_5, HOURLY
• RefreshMode: UPSERT, INCREMENTAL
• StreamType
• DataStreamStatus: ACTIVE
• ImportRunStatus: SUCCESS

MCAE: Custom Redirect data in Data 360

Use case: to track users who access a PDF file on a website that is linked to MCAE.

MCAE has the WebPageActivity data stream ingested into Data 360; however, for the above scenario, accessing a PDF file will not generate web page activity.

Solution: Instead of promoting (such as in an email CTA) the PDF file directly, create a custom redirect in MCAE to track that PDF file URL, then use the "Tracked URL" generated in the custom redirect for the email CTA.

With this, MCAE will ingest the CustomURLActivity DLO that can be mapped to the Website Engagement DMO, then filter the DMO with the Data Source Object contains CustomURLActivity.

Important fields of CustomURLActivity DLO that can be mapped to Website Engagement DMO:

• DataSourceObject__c: CustomURLActivity_OUvxxxxx
• externalCustomUrlId__c: this is Pardot's Custom Redirect ID
• polymorphicCustomUrlId__c: this is Custom Redirect ID in Salesforce called MarketingLink object - prefix 0S0
• externalProspectId__c: this is Pardot's Prospect ID
• polymorphicIndividualId__c: this is Salesforce Lead ID or Contact ID - prefix 00Q/003

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