Pages

Friday, October 30, 2026

Salesforce: Campaign Influence Models

This blog refers to customizable campaign influence and continues from the previous blog: Campaign Influence with Primary Campaign Source Model. 

Record Preference in the Campaign Influence Model settings has 2 options: "All Records" and "Records with >0% Attribution". What does this mean?

  1. All Records: the campaign influenced records will be created no matter what the influenced % is. 
  2. Records with >0% Attribution: the campaign influenced records will only be created when the influenced is more than 0%. 

For custom model, the option is only "All Records".


1. Primary Campaign Source 

Primary Campaign Source field on Opportunity, plus the Opportunity Contact Role that matches the Auto-Assignment rules and Time Frame days.

  • Campaign Influence from the Primary Campaign Source field will get 100% influence
  • Campaign Influence from the Opportunity Contact Roles will get 0% influence

2. First Touch
This model is only available if you have MCAE implemented. This comes from the First Campaign Member Created Date. Influence of 100% is given to one campaign.


3. Last Touch
This model is only available if you have MCAE implemented. This comes from the Latest Campaign Member Last Modified Date. Influence of 100% is given to one campaign. 
Any edit on the campaign member (sync, job, status change) will move the influence, so it may reflect the system activity rather than campaign member engagement.


4. Even Distribution 
This model is only available if you have MCAE implemented. All eligible campaigns will get an equal split of influence (100% ÷ number of campaigns).
Treats an email click the same as an event; credit dilutes as touches grow. 




Thursday, October 29, 2026

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 happens with the related list 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.


What happens to the Campaign Statistics when Campaign Influenced is enabled?

As per the Campaign Influence implementation guide, "Opportunities added by the primary source campaign field appear in Campaign Statistics only when the Primary Source Campaign model is set as the default. If you’re using another model, the value shows opportunities added by auto-association only"







So, if the default model is First Touch, Last Touch, or Even Distribution, Campaign Statistics will use all influenced opportunities (with > 0% influence) of the selected default model.




The sample below uses "All Records" in the Record Preference setting; querying for a campaign with ID 701Hr000001ZpZ1IAK:

 


SELECT Model.MasterLabel, COUNT (Id) FROM CampaignInfluence WHERE CampaignId = '701Hr000001ZpZ1IAK' GROUP BY Model.MasterLabel ORDER BY Model.MasterLabel






SELECT Model.MasterLabel, COUNT (Id) FROM CampaignInfluence WHERE CampaignId = '701Hr000001ZpZ1IAK' AND Influence > 0 GROUP BY Model.MasterLabel ORDER BY Model.MasterLabel



The second query will be used to show the campaign statistics of a campaign; for example, the "Opportunities in Campaign" field:
  • Default model = Even Distribution Model >> 51 (each campaign has equal influence)
  • Default model = First Touch Model >> 49 (2 of them have Influence 0%)
  • Default model = Last Touch Model >> 6 (1 of them has Influence 0%)
  • Default model = Primary Campaign Source Model >> 0 (because no Campaign 701Hr000001ZpZ1IAK is stamped as the Primary Campaign Source field in Opportunity 

The same applies to the custom model; only campaigns with influence > 0% for the Opportunity will be counted for the Campaign statistic field.




    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:




    Tuesday, October 27, 2026

    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

    Tuesday, October 20, 2026

    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


    Monday, October 19, 2026

    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).







    Saturday, October 17, 2026

    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


    Wednesday, October 14, 2026

    Data 360: Query Syntax

    1. Object or Field Name
    Use " " or nothing.
    SELECT * FROM EmailActivity_0Uvxxxx__dll WHERE "eventType__c" <> 'OPEN' LIMIT 10

    2. String match - Text field
    Use ' ' and case-sensitive
    SELECT * FROM EmailActivity_0Uvxxxx__dll WHERE "eventType__c" <> 'OPEN' LIMIT 10

    3. Filter on Boolean field
    SELECT * FROM ssot__Individual__dlm  WHERE "Is_FieldName__c" IS NOT TRUE LIMIT 10
    OR
    SELECT * FROM ssot__Individual__dlm  WHERE "Is_FieldName__c" IS TRUE LIMIT 10

    4. Filter on Text field
    SELECT * FROM "ssot__Account__dlm" WHERE "Customer__c" <> '' LIMIT 10
    OR
    SELECT * FROM "ssot__Account__dlm" WHERE "Customer__c" = '' LIMIT 10

    5. Filter on DateTime field
    SELECT
        l."Status__c" AS Lead_Status,
        COUNT(DISTINCT l."Id__c") AS Total_Leads,
        COUNT(
            DISTINCT CASE
                WHEN e."createdAt__c" >= TIMESTAMP '2026-05-01 00:00:00'
                 AND e."createdAt__c" <= TIMESTAMP '2026-05-31 23:59:59'
                 AND e."sendStatus__c" = 'SENT'
                THEN l."Id__c"
            END
        ) AS Contacted_May_2026
    FROM "Lead_Home__dll" l
    LEFT JOIN "EmailActivity_0Uvxxxx__dll" e
        ON l."Id__c" = e."prospectSalesforceId__c"
    GROUP BY Lead_Status
    ORDER BY Lead_Status;




    Referral:

    Monday, October 12, 2026

    Salesforce: Campaign Influence with Primary Campaign Source Model

    Primary Campaign Source Model (or Salesforce Model) is the only OOTB available model in the customizable campaign influence if you do not have MCAE/Pardot, but you can create your own model as explained here.

    How does the system add a campaign as Campaign Influence?

    There are 2 sources of data that create Campaign Influence records for the Primary Campaign Source Model:

    1. Primary Campaign Source

    When the Primary Campaign Source field is populated in the Opportunity, it will be auto-added as Campaign Influence, even if it does not fit the Auto-Association rules. The Influence % will always be 100%.

    Changing the Primary Campaign Source will set the previous campaign to 0% and the new Campaign to 100% influence. The same applies if you remove the Primary Campaign Source from the Opportunity; it will be set to 0%, but make sure the Record Preference is set to "All Records" in the model setting.


    2. Opportunity Contact Role

    The Contact added to the Opportunity Contact Role must fit both Auto-Association rules and the Campaign Influence Time Frame. The Record Preference on the Campaign Influence Model Setup must be set to "All Records"; the Influence % will be 0%. 

    If you set Record Preference to "Records with >0% Attribution", the Campaign Influence record will not be created, as it is 0% influence.


    So, if the Opportunity has Primary Campaign Source and Opportunity Contact Role that are linked to different Campaigns, all Campaigns will be added as Campaign Influence. The same applies if the Contact in Opportunity Contact Role links to multiple campaigns that fit the auto-association rules and time frame; all Campaigns will be added as Campaign Influence. They can be from the same or different contacts in the Opportunity Contact Role.

    If the Campaign in the Primary Campaign Source is the same Campaign from the Opportunity Contact Role, only 1 Campaign Influence record will be created with 100% influence.


    Time Frame Days

    As per Salesforce's article, the time frame days for auto-association are based on 

    (Opportunity created date - Campaign Member created date (First Associated Date)) should be less than the Time Frame.

    Example: We set the time frame to 365 days.

    • Opportunity created date (September 15, 2024) - Campaign Member created date (December 10, 2024) =  -86. -86 is less than 365 (time frame days), so the Campaign is considered influential to the Opportunity. 
    • Opportunity created date (September 15, 2024) - Campaign Member created date (December 10, 2022) =  645. 645 is more than 365 (time frame days), so the Campaign is not considered influential to the Opportunity.

    Based on the above definition, any contact added to the Opportunity Contact Role and also added to a Campaign after the Opportunity is created will be considered for Campaign Influence (assuming it fits the Auto-Association rules).


    Summary:

    • The count of Campaign Influence with this model is usually higher than the count of Opportunities with Primary Campaign Source; the Campaign Influence records may be created from the Opportunity Contact Role. 


    My testing results in a Dev Edition org:

    • Campaign Active status does not affect Campaign Influence.
    • Opportunity Open/Closed status does not affect Campaign Influence.

    However, in the Campaign Influence implementation guide, it mentions:

    • "Influence models scan active campaigns to identify members who are also assigned a contact role on an open opportunity"
    • "After an opportunity’s stage is closed (won or lost), influence records are no longer created"

    So not sure why my dev org behaves differently from the implementation guide:


    Reference:





    Excel: Copy Value/Formula

    As a Salesforce admin, at a point in time, we may need to use Microsoft Excel (unless you use Google Spreadsheet). Here is the shortcut: assume you have an Excel spreadsheet with hundreds of thousands of rows, and you need to copy a value or formula.

    Filtering the rows and double-clicking the fill handle will stop copying when it reaches a row that is not blank.

    Sample data:



    If we filter column A = Z2, type "y" in B3, then double-clicking the fill handle of cell B3 will stop at row 4, as row 5 is not blank.

    So here are the correct steps:

    1. Filter column A = Z2

    2. In the Name Box (upper left), type B3:B1000 (assume you have 1000 rows), then press Enter

    3. Enter Alt + ; to select visible cell only

    4. Type "y" in the Formula Bar and hit Ctrl + Enter  << replace "y" with any formula, such as: =A3

    5. Done; all visible rows from B3 - B1000 will be filled with "y"

    blue arrow is the Name Box, and the green arrow is the Formula Bar

    If you need to fill a formula instead of a static value, change the "y" in step (4) to a formula, such as: =A3&"y"


    Reference:





    Monday, October 5, 2026

    Salesforce Marketing Cloud Personalization: Using to Data Cloud segment

    Continue from previous blog Using to Data Cloud Segment in MCAE

    Create Segment in Data 360

    1. Add the segment attributes
    2. Schedule and publish the segment
    3. Activate the segment to Marketing Cloud Personalization (you may see the platform name as Salesforce Interaction Studio)


    Segment in Marketing Cloud Personalization

    1. Monitor the segment is published for the activation created
    2. The segment will be automatically created in Marketing Cloud Personalization as Manual Segment (notice M orange icon)
    3. If you change the segment name in Data 360, the segment name in Marketing Cloud Personalization will stay as the original segment name, however, the it still connected to Data 360, so segment members change in Data 360 will update segment members in Marketing Cloud Personalization

    Page-level ad