Marketing Smart with Einstein Analytics – Part 2

5
(1)

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.

How useful was this post?

Click on a star to rate useful the post is!

Written by


8 thoughts on “Marketing Smart with Einstein Analytics – Part 2”

  • 1
    Kenneth on July 27, 2018 Reply

    Love it 🙂 the One thing to remember is that the Data Views only contain data for the past 6 months, so, depending on how far back you want to go, you need to account for this in the DE and Query design 🙂 i.e. not overwriting the records in the target DE etc.

  • 2
    Claudio Pontirolli on May 23, 2019 Reply

    Hi Rikke,
    we used your advice to improve our dashboard…
    now we can collect properly “sent, opens, bounces, etc..” from marketing cloud hidden dataviews.

    But SMS are making us mad.. How can we identify the “sms campaign ID”?

    thanks in advance

  • 3
    Francois Pomport on November 5, 2019 Reply

    Hi Rikke,
    Thanks a lot for well done tutorials. I am trying to replicate with my Marketing Cloud Admin but we failed in the setting up the queries. Although he is admin and should access to everything, when he clicks on the “Interactions” menu in Email Studio, he does not see the menu “Query”. Do you know why? What does he have to do to access to Query?
    By the way, we met last year at Dreamforce
    Francois

    • 4
      Rikke on November 11, 2019 Reply

      I think that’s a provisioning issue. If you raise a case they should be able to help 🙂

  • 5
    Duncan Chrystal on December 3, 2019 Reply

    Hi Rikke,

    Thank you for the very informative post! Wanted to ask what qualifies as a “small” amount of tracking data? Could synching via MC Connect into SF and then into Einstein (though less direct) handle more?

    Many thanks,

    Duncan

    P.s. we met at a workshop at Salesforce Tower London in September where you helped a colleague and create a POC that we are now implementing for a client so thanks again!

    • 6
      Rikke on December 6, 2019 Reply

      Remember there are limits to your SQL, so you may be better off doing data export/import. Also remember that the connector itself has it’s limits, so it may be worth prepping data on the MC side.

      Pleased that the POC is going ahead 🙂

  • 7
    AB on March 25, 2020 Reply

    Hi Rikke,

    Is there a way to send data from Einstein Analytics dashboard saql to Marketing cloud via API’s with around a million rows.

    Thanks.

  • 8
    vInnovate Technologies on March 7, 2022 Reply

    Thank you for your this piece of work! This is a really useful article. Excellent work.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.