Create Marketing Cloud Data Views clones

Three scripts (SSJS) that clones data extensions from the data views and create the queries that populate them.

Steps

1. Download all-data-views-de.html and paste it into a Cloud Page or a Script activity in the automation studio
2. Download all-data-views-sql.html and paste it into a Cloud Page or a Script activity in the automation studio to do the initial population of the DEs
3. Download 'all-data-views-sql-daily-delta.html' and paste it into a Cloud Page or a Script activity in the automation studio. This can be run daily to add deltas to the DEs.
4. Under Email > subscribers > data extensions location you will find your data extensions
5. Under Automation Studio > Activities > All Queries location you will find the query activities

Are included

• _Job
• _Sent
• _Click
• _Complaint
• _Open
• _Subscribers
• _SubscriberSMS
• _UndeliverableSms
• _Journey
• _JourneyActivity
• _Bounce
• _Unsubscribe
• _smsmessagetracking
• _SMSSubscriptionLog

Sample of Script

data extension

<script runat="server">
    Platform.Load("Core", "1.1.1");
    try {
        var MC_job_data_view = {
            "CustomerKey": "MC_job_data_view",
            "Name": "MC_job_data_view",
            "Fields": [{
                    "Name": "JobID",
                    "IsRequired": true,
                    "FieldType": "Number"
                },
                {
                    "Name": "EmailID",
                    "FieldType": "Number"
                },
                [...]
                {
                    "Name": "TriggeredSendCustomerKey",
                    "MaxLength": 36,
                    "FieldType": "Text"
                }
            ]

        };
        var myMC_job_data_view = DataExtension.Add(MC_job_data_view);

    } catch (ex) {
        Write("error message: " + ex);
    }
</script>

query

<script runat="server">
    Platform.Load("Core", "1.1.1");
    try {
        var queryDef1 = {
            Name: "MC_job_data_view",
            CustomerKey: "MC_job_data_view",
            TargetUpdateType: "Overwrite",
            TargetType: "DE",
            Target: {
                Name: "MC_job_data_view",
                CustomerKey: "MC_job_data_view"
            },
            QueryText: "select * from _Job"
        };
        var status_queryDef1 = QueryDefinition.Add(queryDef1);
    } catch (ex) {
        Write("error message: " + ex);
    }
</script>

Going further

• Data views
• SSJS