Marketing Smart with Einstein Analytics – Part 2

Getting marketing smart with Einstein Analytics includes getting details on how your marketing campaigns are performing. What emails were open and which ones had the best click rate? In the first part of this blog series I mentioned, we couldn’t access the tracking data through the connector. In fact, we can’t even see them in Marketing Cloud since they are hidden data extensions. But as I also mentioned in the blog, there is a workaround to accessing the data anyway, which is what this blog will explore. Note that this solution only really works if you have small amounts of tracking data.

Hidden Data Extensions

If you have worked with Marketing Cloud before I am sure you are already aware of the hidden data extensions or data views that contains tracking data such as sends, opens, and clicks. There are a lot more and you can find all the details about them in the Salesforce Help Pages.

As I already mentioned these data extensions contain details we want in Einstein Analytics but the connector cannot access. However, we can replicate the information in Marketing Cloud in new data extensions that the connector can access.

Making the Hidden Unhidden

In order to replicate the hidden data extensions in Marketing Cloud we first need to identify which data extensions we are interested in and then create a complete match of that data extension.

For us to create new data extensions we need to go to the email studio (yes, you can also do this in the contact builder if you rather do that). In Marketing Cloud hover over ‘Email Studio’ and choose ‘Email’.

Now navigate to data extensions by choosing ‘Data Extension’ in the Subscriber tab.

Before we move on and create the data extensions we need please make sure you have the Help Section open with the data extension you want to use. In this case, I am looking at recreating the Open data extension. Now we have an idea of how our new data extension should look. Click on the blue ‘Create’ button, choose the ‘Standard Data Extension’ and click ‘Ok’.

Give the new data extension a name and click ‘Next’.

In the next section, we are going to just click ‘Next’ since we don’t care about retention.

Now the interesting part comes, you need to create every single field from the hidden Open Data Extension in this new data extension. Make sure that the name, data type, length and nullable is an exact match from the data view. Once you are done you can click ‘Create’.

We now have the new Open data extension but we don’t have any data in it. In order for us to populate the data extension, we need to create a query. In the ‘Interactions’ tab choose ‘Query’.

Next, we need to create the query to populate our new data extension. So hit the ‘Create’ button.

Give the query a name and add the following SQL into the ‘Query’ section.

SELECT *
FROM _Open

All we are doing is saying select all the fields from the hidden Open data extension. I know that this data extension is called ‘_Open’ from the data view documentation.

Next, we need to choose the data extension we want to use as a target. So select the data extension you just created and make sure you still have update type as ‘Overwrite’.

Once that is done hit ‘Save’ at the top of the page. You can now select your query and click ‘Start’ to populate data from the hidden data extension to the newly created data extension.

Automate the Whole Thing

We now have all the components we need in order to make the hidden unhidden. However, it’s a manual process. We now want to automate this process, so we do not have to think about getting data from A to B.

In the top right corner where it says ‘Email’ hover over it to show the main menu, hover over ‘Journey Builder’ and select ‘Automation Studio’.

We want to create a new automation so click the blue ‘New Automation’ button. The very first thing we need to do is create a name.

Next drag the green ‘Schedule’ to the Starting Source section and click ‘Configure’. In the popup window set up the schedule as you see fit and click ‘Done’ when you are satisfied.

In the activity section, find the ‘SQL Query’ and drag it onto the canvas.

Click ‘Choose’ and select the query we just created and then click ‘Done’.

You can now click ‘Save’ in the top right corner. After that, you can hit ‘Run Once’ to populate the data (if you didn’t already do that after you created the query).

If you are satisfied with the automation you can activate the automation by hitting the ‘Activate’ button.

And there you go. Your hidden Open data extension is now visible and accessible for the Marketing Cloud connector in Einstein Analytics. In order to replicate this data in Einstein Analytics, please follow the steps from Part 1 of this blog series.

Get All the Data

In this blog we just looked at getting the open data but as you saw there are many different hidden data views. We can surface all of them if you need them. You just need to follow the same process to create the data extensions, then create and add multiple queries to your automation.

Written by

Rikke Hovgaard

I have worked with CRM Analytics since 2015 (back then Wave). I started this blog to help make the journey to master CRM Analytics easy by documenting tips, tricks & cool solutions.

See author's posts