Excel: Copy Formula

One of the most powerful features of Excel is the formula and the ability to copy the formula easily across rows. But in a scenario where rows are filtered in other columns, the shortcut will not work -- this applies to both Excel desktop and Excel online. 

Rows filter >> double click bottom left of formula cell

• will copy the value to all the rows below if all rows below are blank
• will stop copying when it reaches a row (filtered or unfiltered) that is not blank

Rows filter >> manual copy and paste the formula

• works, the filtered rows will be ignored, and the value will not be overwritten from the paste

Salesforce: ListEmail

In addition to the B2B Marketing Analytics - Account Engagement Emails and Account Engagement Email Templates Datasets discussed here. We can also retrieve email statistics sent from MCAE/Pardot using SOQL into the ListEmail object in Salesforce. 

The ListEmail object represents a list email sent from Salesforce or from Account Engagement, and the email sent result is synced to Salesforce. When the list email is sent, recipients are generated by combining the recipients from ListEmailIndividualRecipients and ListEmailRecipientSource. Duplicate and other invalid recipients are removed. The result is recipient sent any given list email. ListEmail has a one-to-many relationship with ListEmailRecipientSource and ListEmailIndividualRecipient. This object is available in API version 41.0 and later.

To differentiate emails sent from Salesforce or Pardot, check the SentVia field. 

SELECT SentVia, count(Id) FROM ListEmail WHERE CreatedDate = this_year GROUP BY SentVia

Each row represents an email sent; no recipients are on this object because the email can be sent to many people. Check ListEmailIndividualRecipient for email recipients, which is a child of ListEmail.

Reference:

• B2B Marketing Analytics: Account Engagement Email Datasets
• Salesforce Field Reference Guide - ListEmail
• Object Reference for the Salesforce Platform - ListEmail

Salesforce: Campaign Member Deletion

Contact or Lead Deletion

When a Contact or Lead used in a Campaign Member is deleted, all Campaign Members for that lead or contact will also be deleted.

Undeleting the Contact or Lead from the Recycle Bin will restore all Campaign Members for the Contact or Lead, including the Status. However, the Last Modified Date and By of the Campaign Member record will be updated with the timestamp of when the Contact or Lead is undeleted, along with the name of the user who performed the undeletion. The Campaign Member ID will be restored to the original Campaign Member ID. 

Campaign Deletion

When a Campaign is deleted, all Campaign Members associated with that Campaign will also be deleted.

Undeleting the Contact from the Recycle Bin will restore all Campaign Members for the Campaign, including the Status. However, the Last Modified Date and By of the Campaign Member record will be updated with the timestamp of when the Campaign is undeleted, along with the name of the user who performed the undeletion. The Campaign Member ID will be restored to the original Campaign Member ID. 

CampaignMember Deletion

When a Campaign Member record is deleted, it is not sent to the Recycle Bin, so it cannot be restored. Vote for this idea.

Bonus: In the CampaignMember object, there are LeadId, ContactId, and LeadOrContactId fields. For a Lead in Campaign Member, when the Lead converted to a Contact:

• The LeadOrContactId will be updated from LeadId to ContactId
• The LeadId will not be changed
• The ContactId will be populated with the Contact ID

Excel: Count of Duplicate

We have recently encountered duplicate data entries being created in Salesforce by automation, resulting in multiple entries with the same lookup value. 

A spreadsheet is still one of the most widely use tools shared among business users. In this blog, I'll have 2 scenarios:

1. Mark how many duplicate roles based on a key field.

I know this can be achieved easily with a pivot table, but it would be great to see the number in the table itself.

sample: Excel formula for row 2, and the unique key is in column E

=COUNTIF(E:E,E2)

2. Mark rows to be kept and to be deleted

From the duplicate row, we need to keep 1 row and delete the rest.

Excel formula for row 2 =IF(COUNTIF($E$2:E2,E2)=1, "Keep", "Delete")

Excel formula for row 3 =IF(COUNTIF($E$2:E3,E3)=1, "Keep", "Delete")

Salesforce: Upsert

For upsert action in Salesforce, if the key field is not an External ID, you may encounter:

"UPSERT requires the external ID field to be specified"

This error occurs because Salesforce's upsert operation relies on an External ID to determine whether to insert a new record or update an existing one. Without an External ID, Salesforce cannot perform this match.

Additional Notes:

• If the field is not unique, you may encounter a DUPLICATE_EXTERNAL_ID error if multiple records match the same value during an upsert.
• Auto-number fields cannot be used as External IDs

Once you select Action = Upsert in Salesforce Inspector, you need to enter the External ID

Similarly, if you are using Workbench, you can select the External Id field from the selected object, as shown in the screenshot below; the Id and Name fields can be used as the External Id field by default.

MCAE: Segmentation Lists

When you click Segmentation Lists, the first view is "All Current Lists," but "All Current Lists" does not mean to include ALL the lists in MCAE.

All Current Lists: view shows all active lists, excluding:

• Archived Lists
• Deleted Lists
• Inactive Dynamic Lists
• Unused Dynamic Lists

Dynamic Lists:

• This includes all dynamic lists currently in your database
• Archived dynamic lists are not displayed in this view - they're moved to the recycle bin
• Deleted and inactive lists are excluded from this view

Inactive Dynamic Lists:

• No prospects added or removed in the last 30 days
• They're no longer actively used in ongoing campaigns or processes
• While they still exist in your system, they're not processing new prospects or updating automatically

Unused Dynamic Lists:

• These are dynamic lists that aren't currently referenced in any active marketing assets (emails, automation rules, etc.)
• Often created for one-time campaigns (like a single email blast), but no longer serve a purpose
• Recommendation: These should be reviewed regularly and either:
• Converted to static lists (if you need to preserve the prospect data)
• Deleted (to free up system resources and improve performance)