Skip to main content

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.

  1. In Marketing Cloud Engagement, navigate to Automation Studio.
  2. Click the Activities tab, then click Create Activity.
  3. Select Data Extract and click Next.
  4. 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.
  5. 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).

Note

The Contacts Without Channel Addresses report takes several hours to run and can only run once every 24 hours.

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

Note

If you use Marketing Cloud Connect, you can create a copy of the Duplicate Subs data extension and name it Non-003-005-00Q Contacts to hold contacts that don’t sync from Sales Cloud.

Next, it’s time to create a new automation!

  1. In Marketing Cloud Engagement, navigate to Automation Studio and create a new automation.
  2. Create a scheduled entry source.
  3. Drag an SQL Query activity into both Step 1 and Step 2 on the automation canvas.
  4. Click Choose.
  5. Click Create New Query Activity and add a name and description.
  6. Add this SQL to the first SQL Query activity.
    select EmailAddress from _Subscribers
    group by EmailAddress
    having count(EmailAddress) > 1
  7. Click Next.
  8. Select the Duplicate Emails data extension you created for the Targeted Data Extension and choose Overwrite for the query.
  9. Click Next, then click Finish.
  10. Repeat steps 6 through 9 for the second SQL Query activity, instead using the Duplicate Subs data extension and this SQL.
    select top 500000 s.SubscriberKey, s.EmailAddress
    from _Subscribers s join [Duplicate Emails] de on de.EmailAddress = s.EmailAddress
    order by s.EmailAddress

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.

select SubscriberKey, EmailAddress from [Duplicate Subs]
where SubscriberKey not like '003%'
and SubscriberKey not like '00Q%'
and SubscriberKey not like '005%'

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.

  1. Navigate to Automation Studio and create a new automation.
  2. Create a scheduled entry source.
  3. 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.)
  4. Click Choose.
  5. Click Create New Query Activity and add a name and description.
  6. 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.
    SELECT DISTINCT UPPER(s.EmailAddress) as Email,
    s.SubscriberKey,
    s.Status,
    b.BounceCategory
    FROM _Subscribers as s
    INNER JOIN _Bounce as b
    ON b.SubscriberKey = s.SubscriberKey
    WHERE b.BounceCategory = 'Hard bounce'
  7. Click Next.
  8. Select the All Hard Bounces data extension you created for the Targeted Data Extension and choose Overwrite for the query.
  9. Click Next, then click Finish.
  10. Repeat steps 6 through 9 for the second SQL Query activity, instead using the Unsubscribed Subscribers data extension and this SQL.
    SELECT SubscriberID,
    EmailAddress,
    DateUndeliverable,
    DateUnsubscribed,
    DateJoined,
    BounceCount,
    SubscriberKey
    FROM _Subscribers with (nolock)
    WHERE DateUndeliverable is not null
    OR DateUnsubscribed is not null
  11. Repeat steps 6 through 9 for the third SQL Query activity, instead using the Unengaged 6 Months data extension and this SQL.
    Select distinct
    s.SubscriberKey,
    su.EmailAddress,
    su.DateJoined
    from [_sent] s
    join _subscribers su on su.SubscriberID = s.SubscriberID
    left join [_open] o
    on s.SubscriberID = o.SubscriberID
    left join [_click] c
    on s.SubscriberID = c.SubscriberID
    where (o.SubscriberID is NULL and c.SubscriberID is NULL)
    and su.DateJoined < dateadd(dd,-180,getdate())

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.

Note

If this relationship-setting option is not visible, enable Contact ID Mapping in Contact Builder. 

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.

select subscriberid from _subscribers
where subscribertype !='ExactTarget'

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.

SELECT EmailAddress,SubscriberKey,[Status] FROM _Subscribers
WHERE SubscriberType = 'Unknown External System'

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.

Note

The following process just helps you clean up any previous mistakes, as you shouldn't delete contacts in Email Studio. Only use Contact Delete in Contact Builder when deleting contacts.

Create a sendable data extension with just the SubscriberKey field. Then use this SQL in your query.

Select Subscriberkey from AllEmailAddresses
EXCEPT
Select Subscriberkey from _subscribers

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.

Resources

Keep learning for
free!
Sign up for an account to continue.
What’s in it for you?
  • Get personalized recommendations for your career goals
  • Practice your skills with hands-on challenges and quizzes
  • Track and share your progress with employers
  • Connect to mentorship and career opportunities