Evaluate Your Contacts
Learning Objectives
After completing this unit, you’ll be able to:
- Identify duplicate records.
- Determine whether contacts are assigned to a channel.
- Pinpoint contacts with technical issues.
- Identify contacts who don’t engage with you.
Removing the Right Contacts
After you get an idea of how many contacts are in your account, you can decide whether these contacts need to be there at all. We discuss what goes into that decision in Unit 3. And again, we encourage you to retain contact information to ensure that you don’t accidentally send messages to unsubscribed contacts. But sometimes you import more than you need, and contact information that’s never used is a waste of space. This unit explores the types of contacts you might want to remove and ways to tidy up your account.
Contacts Without Channel Addresses
You can’t send to contacts that don’t contain any channel addresses, so they are a good candidate for removal. Here’s how you can create a list of these contacts.
- In Marketing Cloud Engagement, navigate to Automation Studio.
- Click the Activities tab, then click Create Activity.
- Select Data Extract and click Next.
- Add a name, external key value, and a file naming pattern. The file naming pattern value doesn’t actually affect anything, because all results go into a Contacts Without Channels data extension in the root folder of the account.
- From the dropdown, select Extract Type Contacts Without Channel Addresses.
This creates a data extension named Contacts Without Channel Addresses and populates it with contacts not associated with any messaging channel (email, SMS, push, or OTT).
Duplicate Contacts
You can also determine how many contacts in your Marketing Cloud Engagement tenant are duplicates. Remember, always perform these reports and automations at your top-level account. First, create two nonsendable data extensions with these parameters.
Duplicate Emails (no primary key, not nullable)
Field | Data Type | Character Length |
---|---|---|
EmailAddress |
Text |
254 |
Duplicate Subs (no primary key, not nullable)
Field | Data Type | Character Length |
---|---|---|
SubscriberKey |
Text |
254 |
EmailAddress |
EmailAddress |
254 |
Next, it’s time to create a new automation!
- In Marketing Cloud Engagement, navigate to Automation Studio and create a new automation.
- Create a scheduled entry source.
- Drag an SQL Query activity into both Step 1 and Step 2 on the automation canvas.
- Click Choose.
- Click Create New Query Activity and add a name and description.
- Add this SQL to the first SQL Query activity.
- Click Next.
- Select the Duplicate Emails data extension you created for the Targeted Data Extension and choose Overwrite for the query.
- Click Next, then click Finish.
- Repeat steps 6 through 9 for the second SQL Query activity, instead using the Duplicate Subs data extension and this SQL.
If you’re using Marketing Cloud Connect and created the data extension Non-003-005-00Q Users earlier, include a third SQL Query activity in your automation addressing that data extension and using this SQL.
Now, run the automation to populate the data extensions. The Duplicate Subs data extension shows you all the contacts with the same email address, so you can determine which contact records can stay and which ones can go. Only you can really determine what constitutes a duplicate contact. Some records may share the same email address (such as a family with multiple cars registered to the same oil change location), where you want to keep those duplicate records. Establish your criteria to evaluate the data to make sure you keep what you need.
Bounces, Unsubscribes, and Unengaged Subscribers
Finally, you can take a look at the contacts that chronically bounce messages, have unsubscribed, or remain unengaged with your marketing efforts. Just like the previous examples, you need to create new nonsendable data extensions—three, to be exact. Here are the parameters.
All Hard Bounces
Field | Data Type | Character Length | Nullable? | Primary Key? |
---|---|---|---|---|
SubscriberKey |
Text |
254 |
x |
|
EmailAddress |
EmailAddress |
254 |
x |
|
Status |
Text |
50 |
||
BounceCategory |
Text |
50 |
You can add an optional status field for the All Subscribers list, with values for Active, Bounced, Held, or Unsubscribed.
Unsubscribed Subscribers
Field | Data Type | Character Length | Nullable? | Primary Key? |
---|---|---|---|---|
SubscriberKey |
Text |
254 |
x |
|
EmailAddress |
EmailAddress |
254 |
x |
|
SubscriberID |
Text |
254 |
||
DateUndeliverable |
Date |
|||
DateUnsubscribed |
Date |
|||
DateJoined |
Date |
|||
BounceCount |
Number |
You can add an optional status field for the All Subscribers list, with values for Active, Bounced, Held, or Unsubscribed.
Unengaged 6 Months
Field | Data Type | Character Length | Nullable? | Primary Key? |
---|---|---|---|---|
SubscriberKey |
Text |
254 |
x |
|
EmailAddress |
EmailAddress |
254 |
x |
Now, navigate to Automation Studio to create your automation for the SQL Query activities.
- Navigate to Automation Studio and create a new automation.
- Create a scheduled entry source.
- Drag an SQL Query activity into Step 1, Step 2, and Step 3 on the automation canvas. (This example shows how to use all three steps in one automation, but you can split them out into different automations if you want.)
- Click Choose.
- Click Create New Query Activity and add a name and description.
- Add this SQL to the first SQL Query activity. Note that this includes a line for status. If you don’t want it, just delete the line.
- Click Next.
- Select the All Hard Bounces data extension you created for the Targeted Data Extension and choose Overwrite for the query.
- Click Next, then click Finish.
- Repeat steps 6 through 9 for the second SQL Query activity, instead using the Unsubscribed Subscribers data extension and this SQL.
- Repeat steps 6 through 9 for the third SQL Query activity, instead using the Unengaged 6 Months data extension and this SQL.
Now you can run the automation to populate the data extensions. Hang on to this information—we look at how to handle these contacts in the next unit.
Hidden List Contacts
Some older versions of Marketing Cloud Connect and other processes may have created contacts in your account that don’t show up in the usual places. Your mission is to seek out contacts not on the standard subscriber lists and find out if they still need to be there. Think of tracking down these contacts like your very own spy mission. First in your mission, create a sendable data extension with precisely one field: SubscriberID. You also need to manage an additional data extension setting—relate SubscriberID to Subscribers on SubscriberID for the sendable relationship.
Next, put together another automation with an SQL query that locates subscribers by subscribertype and places the related SubscriberID values in your new data extension. This SQL query finds all contacts not created through Contact Builder.
So why did this step relate SubscriberID to Subscribers on SubscriberID? Because using the SubscriberKey value to delete these records could also cause the deletion of other, more viable contact records. This setting makes sure that you only delete extraneous records not associated with a Contact Builder contact. We’re just looking out for you.
If you’re looking for subscribers on the Triggered Send Managed list that you aren’t using, make sure your data extension includes these fields:
- EmailAddress
- SubscriberKey
- Status
Then use this SQL in your query.
Now you have a full list of contact information created through external triggered systems.
Find Contacts Deleted in Email Studio, But Not Contact Builder
And finally, you can use this information to find contacts who were deleted in Email Studio but not in Contact Builder.
Create a sendable data extension with just the SubscriberKey field. Then use this SQL in your query.
Set the action to Overwrite, then use the list to evaluate if you need to delete those contacts—but only in Contact Builder.
What to do with all of this knowledge? These steps helped you identify potentially unnecessary contact information. Join us in the next unit to determine how you interact with these contacts.