It’s very likely that at some point in your career you will inherit or take over a Salesforce Marketing Cloud instance that has been set up and used by someone else in the past. In case it hasn’t been well documented, it can be a struggle to understand all the data related processes and clean up all the unused Data Extensions.
Gregory Gifford has written a very useful blog post, where he explains how to create a Data Extension Inventory for your SFMC Business Unit. Using the code snippets he provided, you will be able to populate a complete list of all the Data Extensions in your Business Unit, along with their properties:
But where does the data in those Data Extensions come from? Of course, it won’t be possible to track everything – things like manual data imports, data uploaded through scripts or coming in through API won’t leave any trace in the system. Fortunately, we can track data-related activities that are scheduled in Automation Studio: Imports and Queries. We will use two SOAP API objects to obtain all the information we need: ImportDefinition and QueryDefinition.
But before we do that, let’s first create a Data Extension where we will populate the results of our calls. In the script, we are going to retrieve the following data:
- Data Extension’s Name,
- Data Extension’s External Key,
- the name of the activity in Automation Studio which populates the data,
- the type of the activity (Import/Query),
- the description of the activity if one has been set up.
Here’s the Data Extension structure you will need:
Retrieve the ImportDefinition data
We will use WSProxy to retrieve information about Imports that have been set up and used in Automation Studio and to get information about Data Extensions they populate with data. The properties we will use in our script are the following:
Name– name of the Import Definition,
Description– description of the Import Definition,
ObjectID– ID of the Import Definition,
DestinationObject.ObjectID– the ID of the destination. In this context, this could be either a Data Extension or a List, so we will need to filter out Lists later on.
As you can see above, we can get only as much as the Data Extension’s
ObjectID from the
ImportDefinition object, so we will add another call to our script. We will call the DataExtension object to check the
Name and the
CustomerKey of that Data Extension using the
ObjectID value to match it correctly.
Let’s combine all of the above together and add pagination, so that we are not limited by the fact that most API objects have a predefined batch size of 2500. Here’s the full function: [see code snippet]
Retrieve the QueryDefinition data
QueryDefinition object is a bit easier to work with because SQL queries in Salesforce Marketing Cloud only have one type of destination: a Data Extension. That’s why the
QueryDefinition object will be able to return everything we need in one call. The properties we will use in our script are the following:
Name– name of the Query Definition,
Description– description of the Query Definition,
DataExtensionTarget.Name– name of the target Data Extension,
DataExtensionTarget.CustomerKey– external key of the target Data Extension.
Again, we will add pagination to make sure we retrieve everything. Here’s the full function: [see code snippet]
The full script
Now let’s combine everything together and run our script – you can either run it on a CloudPage, or in Automation Studio. Note, that depending on the volume of activities in your account, this script can take even several minutes to resolve. All you will need to do with below script is to provide the External Key of the Data Extension you created earlier, which will hold the results of our calls: [see code snippet]
Leave a comment below or email me at firstname.lastname@example.org.