Much of the magic of Marketing Cloud relies on invisible data extensions called Data Views. These are data extensions that house the data of sends, opens, clicks, subscribers, journeys, etc. However, they are not immediately visible to the user. There are ways to extract this data using reports or data extracts. You can also leverage this data by using the build-in Einstein features, like Einstein Send Time Optimization and Einstein Engagement splits in Journey Builder.
However, there are other times that you might want to pull from this data without generating reports or writing one-off SQL queries. For example, you might want to use a suppression data extension in a Salesforce Send where you exclude anyone who has already opened an email on the day of the send. Or you might want to quickly skim through the bounces for a particular day if you suspect that perhaps Hotmail has blocked your domain. These are both ways in which I recently used replicated versions of Data Views.
I especially recommend creating these Data View data extensions if you have someone working in your Marketing Cloud org who does not feel comfortable writing SQL queries. This way they can easily create filtered data extensions from the data extensions you are going to create.
I despise making Data Extensions in Marketing Cloud. I think it is tedious. It can also be quirky. If a Data Extension is in a Data Designer relationship, you cannot make updates in Email Studio. Sometimes updates in Contact Builder do not stick. Or, recently I was trying to add a boolean field to a data extension in Contact Builder and it would only let me select field type “text.” It was infuriating. So, I added that field via API, which sort of felt like hitting a needle with a hammer, but it worked.
Creating Data Extensions that replicate Data Views is particularly painful. Some of them have a lot of fields. While the Data Views are not visible, Salesforce does document all the Data Views and their fields so you can replicate them yourself.
Since I found myself adding Data View replicas to multiple Marketing Cloud environments, I decided I needed a way to automate this process. So, here is a GitHub repository with the SOAP API for almost every Data View. As of this writing, I still have a few to build. They are not ones I have yet to use, so I have not gotten to them yet (but I will).
And here is the easiest way to create Data Extensions on the fly:
First, if you do not have Postman, download it now.
Second, create an API package in Marketing Cloud.
In Marketing Cloud, Navigate to Setup → Platform Tools → Apps → Installed Packages
Create a new package. The name you choose for this package will show up as the “Created By” name on the Data Extensions, so keep that in mind when you choose a name.
After the Package is created, select Add Component. Choose Server-to-Server as the API type and authorize the component for at least Read and Write under Data Extensions.
Once this Component is added, you will have the Client Id, Client Secret, Authentication Base URI, REST Base URI, and SOAP Base URI.
Open up Postman and in the upper right corner, click on the gear icon.
Select "Add" under Manage Environments and name the environment. Add the following variables: AuthenticationBaseURI, ClientId, ClientSecret, RESTBaseURI, SOAPBaseURI, and accessToken. You can create an Environment for each org you are using in and toggle between them as needed.
Populate the initial value column in each of these, except for the accessToken variable, with the information in your Marketing Cloud API Component. After you click the orange Add button, closeout of the Environment settings, and you will be back to the Welcome setup. Make sure to select the Environment you just created from the dropdown menu next to the eye and gear icons.
Click Create a request or just open a new tab.
The first thing we are going to do is get the access token from Marketing Cloud. We are doing this with the Authentication Base URI and the Client Id + Client Secret. The first thing you need to do is set the request method to POST. Then populate the request URL with the following:
{{AuthenticationBaseURI}}/v2/tokenUnder Headers, you will add just the following
Content-Type: application/jsonCombined with the pre-existing heads, you will see this:
Now, switch to the Body tab, select the “raw” radio button, and paste the following:
{
"grant_type": "client_credentials",
"client_id": "{{ClientId}}",
"client_secret": "{{ClientSecret}}"
}If you click the Code button in the right corner of the screen, you will get a pop-up of the fully variable-populated request.
At this point, if you click Send you will get the Access Token back. This token will last for 20 minutes.
You can copy and paste this response into what you are going to do next, or you can store it in that accessToken Environment variable that we created earlier. In order to do that, navigate to the Tests tab and paste in the following:
var data = JSON.parse(responseBody);
postman.setEnvironmentVariable("accessToken", data.access_token);This little bit of Javascript will grab the access_token response and store it in the accessToken variable. Run the request again and you will now see the Access Token in the Environment variables. In order to view this, click the eye next to the Environment dropdown.
Now, for the create data extension request. Open a new tab in Postman by clicking the + button.
We will be using SOAP requests for all of our Data Extension needs. Start by changing the request method to POST again. This time you will populate the request URL with your SOAP endpoint:
{{SOAPBaseURI}}/Service.asmxAdd the following Headers:
Authorization: Bearer {{accessToken}}
SoapAction: Create
Content-Type: text/xml This is what the full Header layout will look like. Notice that the pre-existing Content-Type is crossed out in favor of what we added. You can uncheck the box next to the header you do not need so that you are not sending any conflicting information in your request:
Under the Body tab, choose “raw” again and then you will just drop in exactly what I have written in the GitHub files. For example, This is what beginning of the Bounce Data View will look like:
If you click on “Code” again you will see the full request with the Access Token populated. The files on GitHub will exactly replicate the Marketing Cloud Data Views. However, there are several things you might want to adjust before creating. You can change the name of the Data Extension. You can add or delete fields to accommodate your needs. For example, I have a client where I built these with the ContactId added as an additional field so that I could append the Salesforce ID to these Data Extensions, as they are using email address as SubscriberKey, rather than Salesforce Id.
Now, just hit Send and you will receive a response that says Data Extension Created.
You may get an error back but these should be easy to resolve. For example, if the token times out then you just toggle back to the first tab and run that request again to refresh the token, which will update the variable.
Once you have these built, you want to populate them with SQL queries. For example, you can simply populate the data extension we just created with this:
SELECT *
FROM _bounceYou can limit the date range that you are querying with a simple modification. The following will give you the last 7 days of data:
SELECT *
FROM _bounce
WHERE EventDate BETWEEN GETDATE()-7 AND GETDATE() You may want more specifications on these data extensions and here is your full resource. For example, I do not have data retention set for these tables. You can do that manually in Contact Builder or you can add retention specifications to the SOAP request.
There are also no primary keys on these data extensions. If you want to be able to run an Update, rather than an overwrite, you will need to figure out how and where you can add primary keys.
Once these Data Extensions are populated, you can create filtered data extensions to use as sending lists, suppression lists, or audiences in Ad Studio, just to name a few ideas.