Data Views

Data Views
Home Page

Data views in Salesforce Marketing Cloud (SFMC) are a crucial component that allows marketers to access and analyze data from various sources within the platform. These views provide a structured, SQL-like interface to query and extract data related to your email campaigns, subscriber information, and engagement metrics.

NAME DATA TYPE NULLABLE
AccountID int
OYBAccountID int X
JobID int
ListID int
BatchID int
SubscriberID int
SubscriberKey nvarchar(254)
EventDate datetime
Domain varchar(128)
TriggererSendDefinitionObjectID varchar(36) X
TriggeredSendCustomerKey varchar(36) X
In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

  //===============================================================================================================//
  // Query this data view in Automation Studio to find the subscribers sent emails from your Marketing Cloud account.
  // View subscribers who were sent emails from your account by querying the _Sent data view. 
  // Records dating back six months from the day the query runs are available.
  // Dates and times are stored in Central Standard Time. Daylight Savings Time is not observed.
  //===============================================================================================================//
  function CreateSentDataViews(CategoryID)
  {
       var deObj = {
        "CustomerKey" : "Dataview_Sent",
        "Name" : "Dataview_Sent",
        "CategoryID":CategoryID, //Add CategoryID only if you want to create under a specific folder
        "Fields" : [
          { "Name" : "AccountID", "FieldType" : "Number" },
          { "Name" : "OYBAccountID", "FieldType" : "Number"},
          { "Name" : "JobID", "FieldType" : "Number" },
          { "Name" : "ListID", "FieldType" : "Number" },
          { "Name" : "BatchID", "FieldType" : "Number" },
          { "Name" : "SubscriberID", "FieldType" : "Number" },
          { "Name" : "SubscriberKey", "FieldType" : "Text", "MaxLength" : 254 },
          { "Name" : "EventDate", "FieldType" : "Date"},
          { "Name" : "Domain", "FieldType" : "Text","MaxLength" : 128},
          { "Name" : "TriggererSendDefinitionObjectID", "FieldType" : "Text","MaxLength" : 36},
          { "Name" : "TriggeredSendCustomerKey", "FieldType" : "Text","MaxLength" : 36}
        ]
    };

    var myDE = DataExtension.Add(deObj);
    return myDE;
  }
Write a SQL query that retrieves data for the last 24 hours from the _Sent Data View 

SELECT
  SubscriberKey,
  JobID,
  EventDate,
  EmailName,
  SubscriberID,
  AccountID
FROM _Sent
WHERE EventDate >= DATEADD(HOUR, -24, GETDATE())
In Marketing Cloud, you can use AMPScript to retrive data from sent data view 

    %%[
    var @SubscriberId, @SentData, @RowCount
    set @SubscriberId = 100002  /* Replace with the desired SubscriberKey */
    
    set @SentData = LookupOrderedRows("_sent", 1, "EventDate DESC", "SubscriberID", @SubscriberId)
    set @RowCount = RowCount(@SentData)
    
    if @RowCount > 0 then
      var @EventDate, @JobID, @EmailName, @SubscriberID, @AccountID
      var @row
    
      /* Retrieve data from the first row (most recent send) */
      set @row = Row(@SentData, 1)
      set @EventDate = Field(@row, "EventDate")
      set @JobID = Field(@row, "JobID")
      set @SubscriberID = Field(@row, "SubscriberID")
      set @AccountID = Field(@row, "AccountID")
      
      set @outMessage =concat("You had received message on :", @EventDate)
    else
      set @outMessage ="SubscriberKey not found in Sent Data View"
    endif
    ]%%
    
    %%=v(@outMessage)=%%
In Marketing Cloud, you can use SSJS to retrive data from sent data view 

    <script runat="server">
    Platform.Load("core", "1.1.1");
    var subscriberId = 19899;// Replace with the desired SubscriberId
    
    var sentDataView = DataExtension.Init("_sent");// Replace with the name of your Data Extension
    
    var data = sentDataView.Rows.Retrieve({
      Property: "SubscriberID",
      SimpleOperator: "equals",
      Value: subscriberId
    }
                                         );
    if (data.length > 0) {
      var mostRecentSend = data[0];
      var eventDate = mostRecentSend.EventDate;
      var jobID = mostRecentSend.JobID;
      var subscriberID = mostRecentSend.SubscriberID;
      var accountID = mostRecentSend.AccountID;
      // Output the retrieved data
      Write("EventDate: " + eventDate);
      Write("JobID: " + jobID);
      Write("SubscriberID: " + subscriberID);
      Write("AccountID: " + accountID);
    }
    else {
      Write("SubscriberId not found in Sent Data View");
    }
  </script>

NAME DATATYPE NULLABLE
AccountID int
OYBAccountID int X
JobID int
ListID int
BatchID int
SubscriberID int
SubscriberKey nvarchar(254)
EventDate datetime
Domain varchar(128)
IsUnique bool X
TriggererSendDefinitionObjectID varchar(36) X
TriggeredSendCustomerKey varchar(36) X
In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

  //===============================================================================================================//
  // Query this data view in Automation Studio to find the subscribers open emails from your Marketing Cloud account.
  // View email opens for your account by querying the _Open data view. 
  // Records dating back six months from the day the query runs are available.
  // Dates and times are stored in Central Standard Time. Daylight Savings Time is not observed.
  // To view time-related data in your time zone, set time zone user preferences.
  //===============================================================================================================//
  function CreateOpenDataViews(CategoryID)
  {
       var deObj = {
        "CustomerKey" : "Dataview_Open",
        "Name" : "Dataview_Open",
        "CategoryID":CategoryID,//Add CategoryID only if you want to create under a specific folder
        "Fields" : [
          { "Name" : "AccountID", "FieldType" : "Number" },
          { "Name" : "OYBAccountID", "FieldType" : "Number"},
          { "Name" : "JobID", "FieldType" : "Number" },
          { "Name" : "ListID", "FieldType" : "Number" },
          { "Name" : "BatchID", "FieldType" : "Number" },
          { "Name" : "SubscriberID", "FieldType" : "Number" },
          { "Name" : "SubscriberKey", "FieldType" : "Text", "MaxLength" : 254 },
          { "Name" : "EventDate", "FieldType" : "Date"},
          { "Name" : "Domain", "FieldType" : "Text","MaxLength" : 128},
          { "Name" : "TriggererSendDefinitionObjectID", "FieldType" : "Text","MaxLength" : 36},
          { "Name" : "TriggeredSendCustomerKey", "FieldType" : "Text","MaxLength" : 36},
          { "Name" : "IsUnique", "FieldType" : "Boolean"}
        ]
    };

    var myDE = DataExtension.Add(deObj);
    return myDE;
  }
Write a SQL query that retrieves data for the last 24 hours from the _Open Data View 

SELECT
  JobID,
  SubscriberKey,
  EventDate,
  ListID,
  BatchID,
  TriggeredSendDefinitionObjectID
FROM
  _Open
WHERE
  EventDate >= DATEADD(HOUR, -24, GETDATE())
In Marketing Cloud, you can use AMPScript to retrive data from open data view 

%%[
var @SubscriberId, @OpenData, @RowCount
set @SubscriberId = 100002  /* Replace with the desired SubscriberKey */

set @OpenData = LookupOrderedRows("_open", 1, "EventDate DESC", "SubscriberID", @SubscriberId)
set @RowCount = RowCount(@OpenData)

if @RowCount > 0 then
var @EventDate, @JobID, @EmailName, @SubscriberID, @AccountID
var @row

/* Retrieve data from the first row (most recent send) */
set @row = Row(@OpenData, 1)
set @EventDate = Field(@row, "EventDate")
set @JobID = Field(@row, "JobID")
set @SubscriberID = Field(@row, "SubscriberID")
set @AccountID = Field(@row, "AccountID")

set @outMessage =concat("You had opened message on :", @EventDate)
else
set @outMessage ="SubscriberKey not found in Open Data View"
endif
]%%

%%=v(@outMessage)=%%

In Marketing Cloud, you can use SSJS to retrive data from open data view 

<script runat="server">
Platform.Load("core", "1.1.1");
var subscriberId = 19899;// Replace with the desired SubscriberId

var openDataView = DataExtension.Init("_open");// Replace with the name of your  Data Extension

var data = openDataView.Rows.Retrieve({
Property: "SubscriberID",
SimpleOperator: "equals",
Value: subscriberId
}
         );
if (data.length > 0) {
var mostRecentOpen = data[0];
var eventDate = mostRecentOpen.EventDate;
var jobID = mostRecentOpen.JobID;
var subscriberID = mostRecentOpen.SubscriberID;
var accountID = mostRecentOpen.AccountID;
// Output the retrieved data
Write("EventDate: " + eventDate);
Write("JobID: " + jobID);
Write("SubscriberID: " + subscriberID);
Write("AccountID: " + accountID);
}
else {
Write("SubscriberId not found in Open Data View");
}
</script>

NAME DATATYPE NULLABLE
AccountID int
OYBAccountID int X
JobID bigint
ListID int
BatchID bigint
SubscriberID int
SubscriberKey nvarchar(254)
EventDate datetime
Domain varchar(128)
URL varchar(900) X
LinkName varchar(1024) X
LinkContent varchar(max) X
IsUnique bool X
TriggererSendDefinitionObjectID uniqueidentifier X
TriggeredSendCustomerKey varchar(36) X
In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

 //===============================================================================================================//
  // Query this data view in Automation Studio to find the subscribers click emails from your Marketing Cloud account.
  // View clicks for links in emails sent from your account by querying the _Click data view
  // Records dating back six months from the day the query runs are available.
  // Dates and times are stored in Central Standard Time. Daylight Savings Time is not observed.
  // To view time-related data in your time zone, set time zone user preferences.
  //===============================================================================================================//
  function CreateClickDataViews(CategoryID)
  {
       var deObj = {
        "CustomerKey" : "Dataview_Click",
        "Name" : "Dataview_Click",
        "CategoryID":CategoryID,//Add CategoryID only if you want to create under a specific folder
        "Fields" : [
          { "Name" : "AccountID", "FieldType" : "Number" },
          { "Name" : "OYBAccountID", "FieldType" : "Number"},
          { "Name" : "JobID", "FieldType" : "Number" },
          { "Name" : "ListID", "FieldType" : "Number" },
          { "Name" : "BatchID", "FieldType" : "Number" },
          { "Name" : "SubscriberID", "FieldType" : "Number" },
          { "Name" : "SubscriberKey", "FieldType" : "Text", "MaxLength" : 254 },
          { "Name" : "EventDate", "FieldType" : "Date"},
          { "Name" : "Domain", "FieldType" : "Text","MaxLength" : 128},
          { "Name" : "TriggererSendDefinitionObjectID", "FieldType" : "Text","MaxLength" : 36},
          { "Name" : "TriggeredSendCustomerKey", "FieldType" : "Text","MaxLength" : 36},
          { "Name" : "IsUnique", "FieldType" : "Boolean"},
          { "Name" : "URL", "FieldType" : "Text","MaxLength" : 900},
          { "Name" : "LinkName", "FieldType" : "Text","MaxLength" : 1024},
          { "Name" : "LinkContent", "FieldType" : "Text"}
        ]
    };

    var myDE = DataExtension.Add(deObj);
    return myDE;
  }
Write a SQL query that retrieves data for the last 24 hours from the _Click Data View 

SELECT
  JobID,
  SubscriberKey,
  EventDate,
  ListID,
  BatchID,
  TriggeredSendDefinitionObjectID
FROM
  _Click
WHERE
  EventDate >= DATEADD(HOUR, -24, GETDATE())
In Marketing Cloud, you can use AMPScript to retrive data from click data view 

%%[
var @SubscriberId, @clickData, @RowCount
set @SubscriberId = 100002  /* Replace with the desired SubscriberKey */

set @clickData = LookupOrderedRows("_click", 1, "EventDate DESC", "SubscriberID", @SubscriberId)
set @RowCount = RowCount(@clickData)

if @RowCount > 0 then
var @EventDate, @JobID, @EmailName, @SubscriberID, @AccountID
var @row

/* Retrieve data from the first row (most recent send) */
set @row = Row(@clickData, 1)
set @EventDate = Field(@row, "EventDate")
set @JobID = Field(@row, "JobID")
set @SubscriberID = Field(@row, "SubscriberID")
set @AccountID = Field(@row, "AccountID")

set @outMessage =concat("You had clicked message on :", @EventDate)
else
set @outMessage ="SubscriberKey not found in click Data View"
endif
]%%

%%=v(@outMessage)=%%

In Marketing Cloud, you can use SSJS to retrive data from click data view 

<script runat="server">
Platform.Load("core", "1.1.1");
var subscriberId = 19899;// Replace with the desired SubscriberId

var clickDataView = DataExtension.Init("_click");// Replace with the name of your  Data Extension

var data = clickDataView.Rows.Retrieve({
Property: "SubscriberID",
SimpleOperator: "equals",
Value: subscriberId
}
         );
if (data.length > 0) {
var mostRecentClick = data[0];
var eventDate = mostRecentClick.EventDate;
var jobID = mostRecentClick.JobID;
var subscriberID = mostRecentClick.SubscriberID;
var accountID = mostRecentClick.AccountID;
// Output the retrieved data
Write("EventDate: " + eventDate);
Write("JobID: " + jobID);
Write("SubscriberID: " + subscriberID);
Write("AccountID: " + accountID);
}
else {
Write("SubscriberId not found in Click Data View");
}
</script>

NAME DATATYPE NULLABLE
AccountID int
OYBAccountID int X
JobID bigint
ListID int
BatchID bigint
SubscriberID int
SubscriberKey nvarchar(254)
EventDate datetime
IsUnique bit
Domain varchar(128)
BounceCategoryID smallint
BounceCategory nvarchar(50) X
BounceSubcategoryID smallint X
BounceSubcategory nvarchar(50) X
BounceTypeID smallint
BounceType nvarchar(50) X
SMTPBounceReason nvarchar(max) X
SMTPMessage nvarchar(max) X
SMTPCode smallint X
In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

 //===============================================================================================================//
  // To view bounce data for emails from your Marketing Cloud account, query the _bounce data view in Automation Studio.
  // View bounce data for emails from your account by querying the _bounce data view
  // Records dating back six months from the day the query runs are available.
  // Dates and times are stored in Central Standard Time. Daylight Savings Time is not observed.
  // To view time-related data in your time zone, set time zone user preferences.
  //===============================================================================================================//
  function CreateBounceDataViews(CategoryID)
  {
       var deObj = {
        "CustomerKey" : "Dataview_Bounce",
        "Name" : "Dataview_Bounce",
        "CategoryID":CategoryID,//Add CategoryID only if you want to create under a specific folder
        "Fields" : [
          { "Name" : "AccountID", "FieldType" : "Number" },
          { "Name" : "OYBAccountID", "FieldType" : "Number"},
          { "Name" : "JobID", "FieldType" : "Number" },
          { "Name" : "ListID", "FieldType" : "Number" },
          { "Name" : "BatchID", "FieldType" : "Number" },
          { "Name" : "SubscriberID", "FieldType" : "Number" },
          { "Name" : "SubscriberKey", "FieldType" : "Text", "MaxLength" : 254 },
          { "Name" : "EventDate", "FieldType" : "Date"},
          { "Name" : "Domain", "FieldType" : "Text","MaxLength" : 128},
          { "Name" : "TriggererSendDefinitionObjectID", "FieldType" : "Text","MaxLength" : 36},
          { "Name" : "TriggeredSendCustomerKey", "FieldType" : "Text","MaxLength" : 36},
          { "Name" : "IsUnique", "FieldType" : "Boolean"},
          { "Name" : "BounceCategoryID", "FieldType" : "Number" },
          { "Name" : "BounceCategory", "FieldType" : "Text","MaxLength" : 50},
          { "Name" : "BounceSubcategoryID", "FieldType" : "Number" },
          { "Name" : "BounceSubcategory", "FieldType" : "Text","MaxLength" : 50},
          { "Name" : "BounceTypeID", "FieldType" : "Number" },
          { "Name" : "BounceType", "FieldType" : "Text","MaxLength" : 50},
          { "Name" : "SMTPBounceReason", "FieldType" : "Text"},
          { "Name" : "SMTPMessage", "FieldType" : "Text"},
          { "Name" : "SMTPCode", "FieldType" : "Number" },
          { "Name" : "IsFalseBounce", "FieldType" : "Boolean"}
        ]
    };

    var myDE = DataExtension.Add(deObj);
    return myDE;
  }
Write a SQL query that retrieves data for the last 24 hours from the _Bounce Data View 

SELECT
  JobID,
  SubscriberKey,
  EventDate,
  ListID,
  BatchID,
  TriggeredSendDefinitionObjectID
FROM
  _Bounce
WHERE
  EventDate >= DATEADD(HOUR, -24, GETDATE())
In Marketing Cloud, you can use AMPScript to retrive data from bounce data view 

%%[
var @SubscriberId, @bounceData, @RowCount
set @SubscriberId = 100002  /* Replace with the desired SubscriberKey */

set @bounceData = LookupOrderedRows("_bounce", 1, "EventDate DESC", "SubscriberID", @SubscriberId)
set @RowCount = RowCount(@bounceData)

if @RowCount > 0 then
var @EventDate, @JobID, @EmailName, @SubscriberID, @AccountID
var @row

/* Retrieve data from the first row (most recent send) */
set @row = Row(@bounceData, 1)
set @EventDate = Field(@row, "EventDate")
set @JobID = Field(@row, "JobID")
set @SubscriberID = Field(@row, "SubscriberID")
set @AccountID = Field(@row, "AccountID")

set @outMessage =concat("You had bounced message on :", @EventDate)
else
set @outMessage ="SubscriberKey not found in bounce Data View"
endif
]%%

%%=v(@outMessage)=%%

In Marketing Cloud, you can use SSJS to retrive data from bounce data view 

<script runat="server">
Platform.Load("core", "1.1.1");
var subscriberId = 19899;// Replace with the desired SubscriberId

var bounceDataView = DataExtension.Init("_bounce");// Replace with the name of your  Data Extension

var data = bounceDataView.Rows.Retrieve({
Property: "SubscriberID",
SimpleOperator: "equals",
Value: subscriberId
}
         );
if (data.length > 0) {
var mostRecentBounce = data[0];
var eventDate = mostRecentBounce.EventDate;
var jobID = mostRecentBounce.JobID;
var subscriberID = mostRecentBounce.SubscriberID;
var accountID = mostRecentBounce.AccountID;
// Output the retrieved data
Write("EventDate: " + eventDate);
Write("JobID: " + jobID);
Write("SubscriberID: " + subscriberID);
Write("AccountID: " + accountID);
}
else {
Write("SubscriberId not found in Bounce Data View");
}
</script>

NAME DATATYPE NULLABLE
AccountID int
OYBAccountID int X
JobID bigint
ListID int
BatchID bigint
SubscriberID int
SubscriberKey nvarchar(254)
EventDate datetime
IsUnique bit
Domain varchar(128)
In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

  //===============================================================================================================//
  // Query this data view in Automation Studio to find unsubscribes from email lists in your Marketing Cloud account..
  // Records dating back six months from the day the query runs are available.
  // Dates and times are stored in Central Standard Time. Daylight Savings Time is not observed.
  // To view time-related data in your time zone, set time zone user preferences.
  //===============================================================================================================//
  function CreateUnsubscribeDataViews(CategoryID)
  {
       var deObj = {
        "CustomerKey" : "Dataview_Unsubscribe",
        "Name" : "Dataview_Unsubscribe",
        "CategoryID":CategoryID,//Add CategoryID only if you want to create under a specific folder
        "Fields" : [
          { "Name" : "AccountID", "FieldType" : "Number" },
          { "Name" : "OYBAccountID", "FieldType" : "Number"},
          { "Name" : "JobID", "FieldType" : "Number" },
          { "Name" : "ListID", "FieldType" : "Number" },
          { "Name" : "BatchID", "FieldType" : "Number" },
          { "Name" : "SubscriberID", "FieldType" : "Number" },
          { "Name" : "SubscriberKey", "FieldType" : "Text", "MaxLength" : 254 },
          { "Name" : "EventDate", "FieldType" : "Date"},
          { "Name" : "Domain", "FieldType" : "Text","MaxLength" : 128},
          { "Name" : "IsUnique", "FieldType" : "Boolean"}
        ]
    };

    var myDE = DataExtension.Add(deObj);
    return myDE;
  }
Write a SQL query that retrieves data for the last 24 hours from the _Unsubscribe Data View 

SELECT
  JobID,
  SubscriberKey,
  EventDate,
  ListID,
  BatchID
FROM
_Unsubscribe 
WHERE
  EventDate >= DATEADD(HOUR, -24, GETDATE())
In Marketing Cloud, you can use AMPScript to retrive data from Unsubscribe data view 

%%[
var @SubscriberId, @unsubscribeData, @RowCount
set @SubscriberId = 100002  /* Replace with the desired SubscriberKey */

set @unsubscribeData = LookupOrderedRows("_Unsubscribe", 1, "EventDate DESC", "SubscriberID", @SubscriberId)
set @RowCount = RowCount(@unsubscribeData)

if @RowCount > 0 then
var @EventDate, @JobID, @EmailName, @SubscriberID, @AccountID
var @row

/* Retrieve data from the first row (most recent send) */
set @row = Row(@unsubscribeData, 1)
set @EventDate = Field(@row, "EventDate")
set @JobID = Field(@row, "JobID")
set @SubscriberID = Field(@row, "SubscriberID")
set @AccountID = Field(@row, "AccountID")

set @outMessage =concat("You had unsubscribed message on :", @EventDate)
else
set @outMessage ="SubscriberKey not found in unsubscribe Data View"
endif
]%%

%%=v(@outMessage)=%%

In Marketing Cloud, you can use SSJS to retrive data from unsubscribe data view 

<script runat="server">
Platform.Load("core", "1.1.1");
var subscriberId = 19899;// Replace with the desired SubscriberId

var unsubscribeDataView = DataExtension.Init("_unsubscribe");// Replace with the name of your  Data Extension

var data = unsubscribeDataView.Rows.Retrieve({
Property: "SubscriberID",
SimpleOperator: "equals",
Value: subscriberId
}
         );
if (data.length > 0) {
var mostRecentunsubscribe = data[0];
var eventDate = mostRecentunsubscribe.EventDate;
var jobID = mostRecentunsubscribe.JobID;
var subscriberID = mostRecentunsubscribe.SubscriberID;
var accountID = mostRecentunsubscribe.AccountID;
// Output the retrieved data
Write("EventDate: " + eventDate);
Write("JobID: " + jobID);
Write("SubscriberID: " + subscriberID);
Write("AccountID: " + accountID);
}
else {
Write("SubscriberId not found in unsubscribe Data View");
}
</script>

NAME DATATYPE NULLABLE
AccountID int
OYBAccountID int X
JobID bigint
ListID int
BatchID bigint
SubscriberID int
SubscriberKey nvarchar(254)
EventDate datetime
IsUnique bit
Domain varchar(128)
In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

  //===============================================================================================================//
  // Query this data view in Automation Studio to view complaints data related to emails from your Marketing Cloud account.
  // Dates and times are stored in Central Standard Time. Daylight Savings Time is not observed.
  // To view time-related data in your time zone, set time zone user preferences.
  //===============================================================================================================//
  function CreateComplaintDataViews(CategoryID)
  {
       var deObj = {
        "CustomerKey" : "Dataview_UComplaint",
        "Name" : "Dataview_Complaint",
        "CategoryID":CategoryID,//Add CategoryID only if you want to create under a specific folder
        "Fields" : [
          { "Name" : "AccountID", "FieldType" : "Number" },
          { "Name" : "OYBAccountID", "FieldType" : "Number"},
          { "Name" : "JobID", "FieldType" : "Number" },
          { "Name" : "ListID", "FieldType" : "Number" },
          { "Name" : "BatchID", "FieldType" : "Number" },
          { "Name" : "SubscriberID", "FieldType" : "Number" },
          { "Name" : "SubscriberKey", "FieldType" : "Text", "MaxLength" : 254 },
          { "Name" : "EventDate", "FieldType" : "Date"},
          { "Name" : "Domain", "FieldType" : "Text","MaxLength" : 128},
          { "Name" : "IsUnique", "FieldType" : "Boolean"}
        ]
    };

    var myDE = DataExtension.Add(deObj);
    return myDE;
  }
Write a SQL query that retrieves data for the last 24 hours from the _Complaint Data View 

SELECT
  JobID,
  SubscriberKey,
  EventDate,
  ListID,
  BatchID
FROM
Complaint 
WHERE
  EventDate >= DATEADD(HOUR, -24, GETDATE())
In Marketing Cloud, you can use AMPScript to retrive data from Complaint data view 

%%[
var @SubscriberId, @ComplaintData, @RowCount
set @SubscriberId = 100002  /* Replace with the desired SubscriberKey */

set @Complaint = LookupOrderedRows("_Complaint ", 1, "EventDate DESC", "SubscriberID", @SubscriberId)
set @RowCount = RowCount(@Complaint)

if @RowCount > 0 then
var @EventDate, @JobID, @EmailName, @SubscriberID, @AccountID
var @row

/* Retrieve data from the first row (most recent send) */
set @row = Row(@Complaint, 1)
set @EventDate = Field(@row, "EventDate")
set @JobID = Field(@row, "JobID")
set @SubscriberID = Field(@row, "SubscriberID")
set @AccountID = Field(@row, "AccountID")

set @outMessage =concat("You had Complaint message on :", @EventDate)
else
set @outMessage ="SubscriberKey not found in Complaint Data View"
endif
]%%

%%=v(@outMessage)=%%

In Marketing Cloud, you can use SSJS to retrive data from Complaint data view 

<script runat="server">
Platform.Load("core", "1.1.1");
var subscriberId = 19899;// Replace with the desired SubscriberId

var unsubscribeDataView = DataExtension.Init("_Complaint");// Replace with the name of your  Data Extension

var data = unsubscribeDataView.Rows.Retrieve({
Property: "SubscriberID",
SimpleOperator: "equals",
Value: subscriberId
}
         );
if (data.length > 0) {
var mostRecentComplaint = data[0];
var eventDate = mostRecentComplaint.EventDate;
var jobID = mostRecentComplaint.JobID;
var subscriberID = mostRecentComplaint.SubscriberID;
var accountID = mostRecentComplaint.AccountID;
// Output the retrieved data
Write("EventDate: " + eventDate);
Write("JobID: " + jobID);
Write("SubscriberID: " + subscriberID);
Write("AccountID: " + accountID);
}
else {
Write("SubscriberId not found in Complaint Data View");
}
</script>

NAME DATATYPE NULLABLE
AccountID int
OYBAccountID int X
JobID bigint
ListID int
BatchID bigint
SubscriberID int
SubscriberKey nvarchar(254)
TransactionTime datetime
Domain varchar(128)
IsUnique bool X
TriggererSendDefinitionObjectID uniqueidentifier X
TriggeredSendCustomerKey varchar(36) X
In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

 //===============================================================================================================//
  // Query this data view in Automation Studio to view behavioral information related to email messages from your Marketing Cloud account that were forwarded to friends.
  // Records dating back six months from the day the query runs are available.
  // Dates and times are stored in Central Standard Time. Daylight Savings Time is not observed.
  // To view time-related data in your time zone, set time zone user preferences.
  //===============================================================================================================//
  function CreateFTATDataViews(CategoryID)
  {
       var deObj = {
        "CustomerKey" : "Dataview_FTAT",
        "Name" : "Dataview_FTAT",
        "CategoryID":CategoryID,//Add CategoryID only if you want to create under a specific folder
        "Fields" : [
          { "Name" : "AccountID", "FieldType" : "Number" },
          { "Name" : "OYBAccountID", "FieldType" : "Number"},
          { "Name" : "JobID", "FieldType" : "Number" },
          { "Name" : "ListID", "FieldType" : "Number" },
          { "Name" : "BatchID", "FieldType" : "Number" },
          { "Name" : "SubscriberID", "FieldType" : "Number" },
          { "Name" : "SubscriberKey", "FieldType" : "Text", "MaxLength" : 254 },
          { "Name" : "TransactionTime", "FieldType" : "Date"},
          { "Name" : "Domain", "FieldType" : "Text","MaxLength" : 128},
          { "Name" : "TriggererSendDefinitionObjectID", "FieldType" : "Text","MaxLength" : 36},
          { "Name" : "TriggeredSendCustomerKey", "FieldType" : "Text","MaxLength" : 36},
          { "Name" : "IsUnique", "FieldType" : "Boolean"}
        ]
    };

    var myDE = DataExtension.Add(deObj);
    return myDE;
  }
Write a SQL query that retrieves data for the last 24 hours from the _FTAT Data View 

SELECT
  JobID,
  SubscriberKey,
  TransactionTime,
  ListID,
  BatchID,
  TriggeredSendDefinitionObjectID
FROM
 _FTAT
WHERE
 TransactionTime >= DATEADD(HOUR, -24, GETDATE())
In Marketing Cloud, you can use AMPScript to retrive data from _FTAT data view 

%%[
var @SubscriberId, @FTATData, @RowCount
set @SubscriberId = 100002  /* Replace with the desired SubscriberKey */

set @FTATData = LookupOrderedRows("_FTAT", 1, "TransactionTime DESC", "SubscriberID", @SubscriberId)
set @RowCount = RowCount(@FTATData)

if @RowCount > 0 then
var @EventDate, @JobID, @EmailName, @SubscriberID, @AccountID
var @row

/* Retrieve data from the first row (most recent send) */
set @row = Row(@FTATData, 1)
set @EventDate = Field(@row, "TransactionTime")
set @JobID = Field(@row, "JobID")
set @SubscriberID = Field(@row, "SubscriberID")
set @AccountID = Field(@row, "AccountID")

set @outMessage =concat("You had forwarded message on :", @EventDate)
else
set @outMessage ="SubscriberKey not found in _FTAT Data View"
endif
]%%

%%=v(@outMessage)=%%

In Marketing Cloud, you can use SSJS to retrive data from _FTAT data view 

<script runat="server">
Platform.Load("core", "1.1.1");
var subscriberId = 19899;// Replace with the desired SubscriberId

var FTATDataView = DataExtension.Init("_FTAT");// Replace with the name of your  Data Extension

var data = FTATDataView.Rows.Retrieve({
Property: "SubscriberID",
SimpleOperator: "equals",
Value: subscriberId
}
         );
if (data.length > 0) {
var mostRecentFTAT = data[0];
var eventDate = mostRecentFTAT.TransactionTime;
var jobID = mostRecentFTAT.JobID;
var subscriberID = mostRecentFTAT.SubscriberID;
var accountID = mostRecentFTAT.AccountID;
// Output the retrieved data
Write("EventDate: " + eventDate);
Write("JobID: " + jobID);
Write("SubscriberID: " + subscriberID);
Write("AccountID: " + accountID);
}
else {
Write("SubscriberId not found in FTAT Data View");
}
</script>

NAME DATATYPE NULLABLE
BusinessUnitID bigint
SubscriberID bigint
SubscriberKey nvarchar(254)
UnsubDateUTC datetime
UnsubReason varchar(100)
In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

 //===============================================================================================================//
  // Query this data view in Automation Studio to find subscribers in your account and their child business unit unsubscribe data
  // Dates and times are stored in Central Standard Time. Daylight Savings Time is not observed.
  // To view time-related data in your time zone, set time zone user preferences.
  //===============================================================================================================//
  function Create_BusinessUnitUnsubscribesDataViews(CategoryID)
  {
       var deObj = {
        "CustomerKey" : "Dataview_BusinessUnitUnsubscribes",
        "Name" : "Dataview_BusinessUnitUnsubscribes",
        "CategoryID":CategoryID,//Add CategoryID only if you want to create under a specific folder
        "Fields" : [
          { "Name" : "BusinessUnitID", "FieldType" : "Number" },
          { "Name" : "SubscriberID", "FieldType" : "Number" },
          { "Name" : "SubscriberKey", "FieldType" : "Text", "MaxLength" : 254 },
          { "Name" : "UnsubDateUTC", "FieldType" : "Date"},
          { "Name" : "UnsubReason", "FieldType" : "Text","MaxLength" : 100}
        ]
    };

    var myDE = DataExtension.Add(deObj);
    return myDE;
  }
Write a SQL query that retrieves data for the last 24 hours from the _BusinessUnitUnsubscribes Data View 

SELECT
  BusinessUnitID,
  SubscriberID,
  SubscriberKey,
  UnsubDateUTC,
  UnsubReason
FROM
_BusinessUnitUnsubscribes
WHERE
UnsubDateUTC >= DATEADD(HOUR, -24, GETDATE())
In Marketing Cloud, you can use AMPScript to retrive data from _BusinessUnitUnsubscribes data view 

%%[
var @SubscriberId, @businessUnitUnsubscribesData, @RowCount
set @SubscriberId = 100002  /* Replace with the desired SubscriberKey */

set @businessUnitUnsubscribesData = LookupOrderedRows("_BusinessUnitUnsubscribes", 1, "UnsubDateUTC DESC", "SubscriberID", @SubscriberId)
set @RowCount = RowCount(@businessUnitUnsubscribesData)

if @RowCount > 0 then
var @BusinessUnitID, @SubscriberID, @SubscriberKey, @UnsubDateUTC, @UnsubReason
var @row

/* Retrieve data from the first row (most recent send) */
set @row = Row(@businessUnitUnsubscribesData, 1)
set @BusinessUnitID = Field(@row, "BusinessUnitID")
set @SubscriberID = Field(@row, "SubscriberID")
set @SubscriberKey = Field(@row, "SubscriberKey")
set @UnsubReason = Field(@row, "UnsubReason")

set @outMessage =concat("You had unsubscribed from on :", @BusinessUnitID)
else
set @outMessage ="SubscriberKey not found in _BusinessUnitUnsubscribes Data View"
endif
]%%

%%=v(@outMessage)=%%

In Marketing Cloud, you can use SSJS to retrive data from _BusinessUnitUnsubscribes data view 

<script runat="server">
Platform.Load("core", "1.1.1");
var subscriberId = 19899;// Replace with the desired SubscriberId

var BusinessUnitUnsubscribesDataView = DataExtension.Init("_BusinessUnitUnsubscribes");// Replace with the name of your  Data Extension

var data = BusinessUnitUnsubscribesDataView.Rows.Retrieve({
Property: "SubscriberID",
SimpleOperator: "equals",
Value: subscriberId
}
         );
if (data.length > 0) {
var mostRecentUnsub = data[0];
var UnsubDateUTC = mostRecentUnsub.UnsubDateUTC;
var UnsubReason = mostRecentUnsub.UnsubReason;
var subscriberID = mostRecentUnsub.SubscriberID;
var BusinessUnitID = mostRecentUnsub.BusinessUnitID;
// Output the retrieved data
Write("UnsubDateUTC: " + UnsubDateUTC);
Write("BusinessUnitID: " + BusinessUnitID);
Write("SubscriberID: " + subscriberID);
Write("UnsubReason: " + UnsubReason);
}
else {
Write("SubscriberId not found in _BusinessUnitUnsubscribes Data View");
}
</script>

NAME DATATYPE NULLABLE
SubscriberID bigint
SubscriberKey nvarchar(254)
DateUndeliverable datetime X
DateJoined datetime X
DateUnsubscribed datetime X
Domain nvarchar(254) X
EmailAddress varchar(254)
BounceCount smallint
SubscriberType varchar(100)
Status varchar(12) X
Locale int X
In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

 //===============================================================================================================//
  // Query this data view in Automation Studio to find subscribers in your Marketing Cloud account and their statuses.
  // Dates and times are stored in Central Standard Time. Daylight Savings Time is not observed.
  // To view time-related data in your time zone, set time zone user preferences.
  //===============================================================================================================//
  function CreateSubscribersDataViews(CategoryID)
  {
       var deObj = {
        "CustomerKey" : "Dataview_Subscribers",
        "Name" : "Dataview_Subscribers",
        "CategoryID":CategoryID,//Add CategoryID only if you want to create under a specific folder
        "Fields" : [
          { "Name" : "SubscriberID", "FieldType" : "Number" },
          { "Name" : "SubscriberKey", "FieldType" : "Text", "MaxLength" : 254 },
          { "Name" : "DateUndeliverable", "FieldType" : "Date"},
          { "Name" : "DateJoined", "FieldType" : "Date"},
          { "Name" : "DateUnsubscribed", "FieldType" : "Date"},
          { "Name" : "Domain", "FieldType" : "Text","MaxLength" : 254},
          { "Name" : "EmailAddress", "FieldType" : "EmailAddress"},
          { "Name" : "BounceCount", "FieldType" : "Number" },
          { "Name" : "SubscriberType", "FieldType" : "Text","MaxLength" : 100},
          { "Name" : "Status", "FieldType" : "Text","MaxLength" : 12},
          { "Name" : "Locale", "FieldType" : "Locale"}
        ]
    };

    var myDE = DataExtension.Add(deObj);
    return myDE;
  }
Write a SQL query that retrieves data for the last 24 hours from the _Subscribers Data View 

SELECT
  SubscriberID,
  SubscriberKey,
  DateUndeliverable,
  DateUnsubscribed,
  Domain
FROM
_Subscribers
WHERE
DateUnsubscribed >= DATEADD(HOUR, -24, GETDATE())
In Marketing Cloud, you can use AMPScript to retrive data from _Subscribers data view 

%%[
var @SubscriberId, @DateUnsubscribedData, @RowCount
set @SubscriberId = 100002  /* Replace with the desired SubscriberKey */

set @subscribersData = LookupOrderedRows("_Subscribers", 1, "DateUnsubscribed DESC", "SubscriberID", @SubscriberId)
set @RowCount = RowCount(@subscribersData)

if @RowCount > 0 then
var @SubscriberKey, @DateUndeliverable, @DateUnsubscribed, @Domain
var @row

/* Retrieve data from the first row (most recent send) */
set @row = Row(@subscribersData, 1)
set @SubscriberKey = Field(@row, "SubscriberKey")
set @DateUndeliverable = Field(@row, "DateUndeliverable")
set @DateUnsubscribed = Field(@row, "DateUnsubscribed")
set @Domain = Field(@row, "Domain")

set @outMessage =concat("You had unsubscribed on :", @DateUnsubscribed)
else
set @outMessage ="SubscriberKey not found in _Subscribers Data View"
endif
]%%

%%=v(@outMessage)=%%

In Marketing Cloud, you can use SSJS to retrive data from _Subscribers data view 

<script runat="server">
Platform.Load("core", "1.1.1");
var subscriberId = 19899;// Replace with the desired SubscriberId

var subscribersDataView = DataExtension.Init("_Subscribers");// Replace with the name of your  Data Extension

var data = subscribersDataView.Rows.Retrieve({
Property: "SubscriberID",
SimpleOperator: "equals",
Value: subscriberId
}
         );
if (data.length > 0) {
var mostRecentUnsub = data[0];
var DateUndeliverable = mostRecentUnsub.DateUndeliverable;
var DateJoined = mostRecentUnsub.DateJoined;
var DateUnsubscribed = mostRecentUnsub.DateUnsubscribed;
var Domain = mostRecentUnsub.Domain;
// Output the retrieved data
Write("DateUndeliverable: " + DateUndeliverable);
Write("DateJoined: " + DateJoined);
Write("DateUnsubscribed: " + DateUnsubscribed);
Write("Domain: " + Domain);
}
else {
Write("SubscriberId not found in _Subscribers Data View");
}
</script>

In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension.

This list contains information on send jobs and messages that didn’t send. Only the most recent two months of data is available.


<script runat="server">
    // Load necessary libraries
    Platform.Load("core", "1");

    try {

       var dataExtensionName="NotSent_DataViews"
       var folderName="Data Views"
       // Initialize the campaign Data Extension
       var result = createDataExtension(dataExtensionName,folderName);
       var objectId = RetrieveDataExtension(dataExtensionName);
       Write(objectId);
    } catch (ex) {
        // Catch and log any errors that occur
        Write(Stringify(ex));
    }

    // Function to retrieve the ID of a folder named "12. Non_FSC_Campaigns"
    function RetrieveFolderID(folderName) {
        // Define filter to retrieve folder by name
        var filter = {
            Property: "Name",
            SimpleOperator: "equals",
            Value: folderName
        };
        // Retrieve folder based on filter
        var results = Folder.Retrieve(filter);
        // Return the ID of the first folder found
        return results[0].ID;
    }

    // Function to create a Data Extension
    function createDataExtension(dataExtensionName,folderName) {
        // Initialize WSProxy API
        var api = new Script.Util.WSProxy();
        
        // Set the client ID
        api.setClientId({ "ID": Platform.Function.AuthenticatedMemberID() });

        // Define Data Extension configuration
        var config = {
            "CustomerKey": dataExtensionName,
            "Name": dataExtensionName,
            "CategoryID": RetrieveFolderID(folderName), // Retrieve folder ID
             "Fields" : [
              { "Name" : "ClientID", "FieldType" : "Number" },
              { "Name" : "SendID", "FieldType" : "Number"  },
              { "Name" : "ListID", "FieldType" : "Number" },
              { "Name" : "BatchID", "FieldType" : "Number" },
              { "Name" : "SubscriberID", "FieldType" : "Number"},
              { "Name" : "SubscriberKey", "FieldType" : "Text", "MaxLength" : 254 },
              { "Name" : "EmailAddress", "FieldType" : "Text","MaxLength" : 500},
              { "Name" : "EventDate", "FieldType" : "Date"},
              { "Name" : "EventType", "FieldType" : "Text","MaxLength" : 128},
              { "Name" : "TriggeredSendExternalKey", "FieldType" : "Text","MaxLength" : 36},
              { "Name" : "Reason", "FieldType" : "Text","MaxLength" : 100}
            ]
        };

        // Create the Data Extension and return the result
        var result = api.createItem("DataExtension", config);
        return (Stringify(result));
    };

    function RetrieveDataExtension(externalKey) {
        var api = new Script.Util.WSProxy();
        var req = api.retrieve("DataExtension", ["ObjectID"], {
            Property: "DataExtension.CustomerKey",
            SimpleOperator: "equals",
            Value: externalKey
        });
        return req.Results[0].ObjectID;
    }
</script>

In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

<script runat="server">
    // Load necessary libraries
    Platform.Load("core", "1");

    try {
        var dataExtensionName = "JourneyActivity_DataView";
        var folderName = "Data Views";

        // Create the Journey Activity Data Extension
        var result = createDataExtension(dataExtensionName, folderName);
        var objectId = RetrieveDataExtension(dataExtensionName);
        Write(objectId);
    } catch (ex) {
        // Catch and log any errors that occur
        Write(Stringify(ex));
    }

    // Function to retrieve the ID of a folder
    function RetrieveFolderID(folderName) {
        // Define filter to retrieve folder by name
        var filter = {
            Property: "Name",
            SimpleOperator: "equals",
            Value: folderName
        };
        // Retrieve folder based on filter
        var results = Folder.Retrieve(filter);
        // Return the ID of the first folder found
        return results[0].ID;
    }

    // Function to create a Data Extension
    function createDataExtension(dataExtensionName, folderName) {
        // Initialize WSProxy API
        var api = new Script.Util.WSProxy();

        // Set the client ID
        api.setClientId({ "ID": Platform.Function.AuthenticatedMemberID() });

        // Define Data Extension configuration
        var config = {
            "CustomerKey": dataExtensionName,
            "Name": dataExtensionName,
            "CategoryID": RetrieveFolderID(folderName), // Retrieve folder ID
            "Fields": [
                { "Name": "VersionID", "FieldType": "Text", "MaxLength": 36 },
                { "Name": "ActivityID", "FieldType": "Text", "MaxLength": 36 },
                { "Name": "ActivityName", "FieldType": "Text", "MaxLength": 200 },
                { "Name": "ActivityExternalKey", "FieldType": "Text", "MaxLength": 200 },
                { "Name": "JourneyActivityObjectID", "FieldType": "Text", "MaxLength": 36 },
                { "Name": "ActivityType", "FieldType": "Text", "MaxLength": 512 }
            ]
        };

        // Create the Data Extension and return the result
        var result = api.createItem("DataExtension", config);
        return Stringify(result);
    };

    // Function to retrieve Data Extension ObjectID
    function RetrieveDataExtension(externalKey) {
        var api = new Script.Util.WSProxy();
        var req = api.retrieve("DataExtension", ["ObjectID"], {
            Property: "DataExtension.CustomerKey",
            SimpleOperator: "equals",
            Value: externalKey
        });
        return req.Results[0].ObjectID;
    }
</script>

In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

<script runat="server">
    // Load necessary libraries
    Platform.Load("core", "1");

    try {
        var dataExtensionName = "Journey_DataView";
        var folderName = "Data Views";

        // Create the Journey Activity Data Extension
        var result = createDataExtension(dataExtensionName, folderName);
        var objectId = RetrieveDataExtension(dataExtensionName);
        Write(objectId);
    } catch (ex) {
        // Catch and log any errors that occur
        Write(Stringify(ex));
    }

    // Function to retrieve the ID of a folder
    function RetrieveFolderID(folderName) {
        // Define filter to retrieve folder by name
        var filter = {
            Property: "Name",
            SimpleOperator: "equals",
            Value: folderName
        };
        // Retrieve folder based on filter
        var results = Folder.Retrieve(filter);
        // Return the ID of the first folder found
        return results[0].ID;
    }

    // Function to create a Data Extension
    function createDataExtension(dataExtensionName, folderName) {
        // Initialize WSProxy API
        var api = new Script.Util.WSProxy();

        // Set the client ID
        api.setClientId({ "ID": Platform.Function.AuthenticatedMemberID() });

        // Define Data Extension configuration
        var config = {
            "CustomerKey": dataExtensionName,
            "Name": dataExtensionName,
            "CategoryID": RetrieveFolderID(folderName), // Retrieve folder ID
            "Fields": [
                { "Name": "VersionID", "FieldType": "Text", "MaxLength": 36 },
                { "Name": "JourneyID", "FieldType": "Text", "MaxLength": 36 },
                { "Name": "JourneyName", "FieldType": "Text", "MaxLength": 200 },
                { "Name": "VersionNumber", "FieldType" : "Number" },
                { "Name": "CreatedDate", "FieldType" : "Date"},
                { "Name": "LastPublishedDate", "FieldType" : "Date" },
                { "Name": "ModifiedDate", "FieldType" : "Date" },
                { "Name": "JourneyStatus", "FieldType": "Text", "MaxLength": 100 }
            ]
        };

        // Create the Data Extension and return the result
        var result = api.createItem("DataExtension", config);
        return Stringify(result);
    };

    // Function to retrieve Data Extension ObjectID
    function RetrieveDataExtension(externalKey) {
        var api = new Script.Util.WSProxy();
        var req = api.retrieve("DataExtension", ["ObjectID"], {
            Property: "DataExtension.CustomerKey",
            SimpleOperator: "equals",
            Value: externalKey
        });
        return req.Results[0].ObjectID;
    }
</script>

In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

<script runat="server">
    // Load necessary libraries
    Platform.Load("core", "1");

    try {

       var dataExtensionName="TriggeredSendDefinition"
       var folderName="Data Views"
        // Initialize the campaign Data Extension
       var result = createDataExtension(dataExtensionName,folderName);
       var objectId = RetrieveDataExtension(dataExtensionName);
       Write(objectId);
    } catch (ex) {
        // Catch and log any errors that occur
        Write(Stringify(ex));
    }

    // Function to retrieve the ID of a folder named "12. Non_FSC_Campaigns"
    function RetrieveFolderID(folderName) {
        // Define filter to retrieve folder by name
        var filter = {
            Property: "Name",
            SimpleOperator: "equals",
            Value: folderName
        };
        // Retrieve folder based on filter
        var results = Folder.Retrieve(filter);
        // Return the ID of the first folder found
        return results[0].ID;
    }

    // Function to create a Data Extension
    function createDataExtension(dataExtensionName,folderName) {
        // Initialize WSProxy API
        var api = new Script.Util.WSProxy();
        
        // Set the client ID
        api.setClientId({ "ID": Platform.Function.AuthenticatedMemberID() });

        // Define Data Extension configuration
        var config = {
            "CustomerKey": dataExtensionName,
            "Name": dataExtensionName,
            "CategoryID": RetrieveFolderID(folderName), // Retrieve folder ID
             "Fields" : [
              { "Name" : "CustomerKey", "FieldType" : "Text", "MaxLength" : 50,"IsPrimaryKey" : true, "IsRequired" : true },
              { "Name" : "CreatedDate", "FieldType" : "Date"},
              { "Name" : "ModifiedDate", "FieldType" : "Date"},
              { "Name" : "Name", "FieldType" : "Text", "MaxLength" : 200 },
              { "Name" : "Description", "FieldType" : "Text", "MaxLength" : 200 },
              { "Name" : "EmailSubject", "FieldType" : "Text","MaxLength" : 200},
              { "Name" : "ObjectID", "FieldType" : "Text","MaxLength" : 36}
            ]
        };

        // Create the Data Extension and return the result
        var result = api.createItem("DataExtension", config);
        return (Stringify(result));
    };

    function RetrieveDataExtension(externalKey) {
        var api = new Script.Util.WSProxy();
        var req = api.retrieve("DataExtension", ["ObjectID"], {
            Property: "DataExtension.CustomerKey",
            SimpleOperator: "equals",
            Value: externalKey
        });
        return req.Results[0].ObjectID;
    }
</script>

In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

<script runat="server">
    // Load necessary libraries
    Platform.Load("core", "1");

    try {

       var dataExtensionName="NotSent_Summary"
       var folderName="Data Views"

        // Initialize the campaign Data Extension
       var result = createDataExtension(dataExtensionName,folderName);
       var objectId = RetrieveDataExtension(dataExtensionName);
       Write(objectId);
    } catch (ex) {
        // Catch and log any errors that occur
        Write(Stringify(ex));
    }

    // Function to retrieve the ID of a folder named "12. Non_FSC_Campaigns"
    function RetrieveFolderID(folderName) {
        // Define filter to retrieve folder by name
        var filter = {
            Property: "Name",
            SimpleOperator: "equals",
            Value: folderName
        };
        // Retrieve folder based on filter
        var results = Folder.Retrieve(filter);
        // Return the ID of the first folder found
        return results[0].ID;
    }

    // Function to create a Data Extension
    function createDataExtension(dataExtensionName) {
        // Initialize WSProxy API
        var api = new Script.Util.WSProxy();
        
        // Set the client ID
        api.setClientId({ "ID": Platform.Function.AuthenticatedMemberID() });

        // Define Data Extension configuration
        var config = {
            "CustomerKey": dataExtensionName,
            "Name": dataExtensionName,
            "CategoryID": RetrieveFolderID(folderName), // Retrieve folder ID
             "Fields" : [
              { "Name" : "SubscriberKey", "FieldType" : "Text", "MaxLength" : 254 },
              { "Name" : "EmailAddress", "FieldType" : "Text","MaxLength" : 500},
              { "Name" : "EventDate", "FieldType" : "Date"},
              { "Name" : "TriggeredSendExternalKey", "FieldType" : "Text","MaxLength" : 36},
              { "Name" : "Reason", "FieldType" : "Text","MaxLength" : 100},
              { "Name": "ActivityName", "FieldType": "Text", "MaxLength": 200 },
              { "Name": "JourneyName", "FieldType": "Text", "MaxLength": 200 }
            ]
        };

        // Create the Data Extension and return the result
        var result = api.createItem("DataExtension", config);
        return (Stringify(result));
    };

    function RetrieveDataExtension(externalKey) {
        var api = new Script.Util.WSProxy();
        var req = api.retrieve("DataExtension", ["ObjectID"], {
            Property: "DataExtension.CustomerKey",
            SimpleOperator: "equals",
            Value: externalKey
        });
        return req.Results[0].ObjectID;
    }
</script>

In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

<script runat="server">
    // Load necessary libraries
    Platform.Load("core", "1");

    try {
        // Define Data Extension and folder names
        var dataExtensionName = "ImportDefinition";
        var folderName = "Monitoring";

        // Create Data Extension and retrieve result
        var result = createDataExtension(dataExtensionName, folderName);
        // Write result to console
        Write(result);
    } catch (ex) {
        // Catch and log any errors that occur
        Write(Stringify(ex));
    }

    // Function to retrieve the ID of a folder by name
    function RetrieveFolderID(folderName) {
        var folderID = null;
        // Define filter to retrieve folder by name
        var filter = {
            Property: "Name",
            SimpleOperator: "equals",
            Value: folderName
        };
        // Retrieve folder based on filter
        var folders = Folder.Retrieve(filter);
        // If folder is found, set folderID
        if (folders && folders.length > 0) {
            folderID = folders[0].ID;
        }
        return folderID;
    }

    // Function to create a Data Extension
    function createDataExtension(dataExtensionName, folderName) {
        // Initialize WSProxy API
        var api = new Script.Util.WSProxy();

        // Set the client ID for API request
        api.setClientId({ "ID": Platform.Function.AuthenticatedMemberID() });

        // Retrieve folder ID using provided folderName
        var folderID = RetrieveFolderID(folderName);
        // If folder not found, throw error
        if (!folderID) {
            throw new Error("Folder not found: " + folderName);
        }

        // Define Data Extension configuration
        var config = {
            "CustomerKey": dataExtensionName,
            "Name": dataExtensionName,
            "CategoryID": folderID, // Assign folderID to Data Extension
            "Fields": [
                // Define fields for Data Extension
                { "Name": "ObjectID", "FieldType": "Text", "MaxLength": 36, "IsPrimaryKey": true, "IsRequired": true },
                { "Name": "CustomerKey", "FieldType": "Text", "MaxLength": 36 },
                { "Name": "Description", "FieldType": "Text", "MaxLength": 4000 },
                { "Name": "Name", "FieldType": "Text", "MaxLength": 400 },
                { "Name": "LocaleCode", "FieldType": "Text", "MaxLength": 10 },
                { "Name": "UpdateType", "FieldType": "Text", "MaxLength": 50 },
                { "Name": "SubscriberImportType", "FieldType": "Text", "MaxLength": 50 },
                { "Name": "FileTransferLocationID", "FieldType": "Text", "MaxLength": 50 },
                { "Name": "FileSpec", "FieldType": "Text", "MaxLength": 400 },
                { "Name": "FieldMappingType", "FieldType": "Text", "MaxLength": 50 },
                { "Name": "DestinationObjectID", "FieldType": "Text", "MaxLength": 50 },
                { "Name": "AllowErrors", "FieldType": "Boolean" }
            ]
        };

        // Create the Data Extension using WSProxy API and return the result
        var result = api.createItem("DataExtension", config);
        return Stringify(result);
    }

</script>

In Marketing Cloud, you can use SSJS (Server-Side JavaScript) to create a Data Extension. 

<script runat="server">
    // Load necessary libraries
    Platform.Load("core", "1");

    try {
        // Define Data Extension and folder names
        var dataExtensionName = "ImportDefinitionResults";
        var folderName = "Monitoring";

        // Create Data Extension and retrieve result
        var result = createDataExtension(dataExtensionName, folderName);
        // Write result to console
        Write(result);
    } catch (ex) {
        // Catch and log any errors that occur
        Write(Stringify(ex));
    }

    // Function to retrieve the ID of a folder by name
    function RetrieveFolderID(folderName) {
        var folderID = null;
        // Define filter to retrieve folder by name
        var filter = {
            Property: "Name",
            SimpleOperator: "equals",
            Value: folderName
        };
        // Retrieve folder based on filter
        var folders = Folder.Retrieve(filter);
        // If folder is found, set folderID
        if (folders && folders.length > 0) {
            folderID = folders[0].ID;
        }
        return folderID;
    }

    // Function to create a Data Extension
    function createDataExtension(dataExtensionName, folderName) {
        // Initialize WSProxy API
        var api = new Script.Util.WSProxy();

        // Set the client ID for API request
        api.setClientId({ "ID": Platform.Function.AuthenticatedMemberID() });

        // Retrieve folder ID using provided folderName
        var folderID = RetrieveFolderID(folderName);
        // If folder not found, throw error
        if (!folderID) {
            throw new Error("Folder not found: " + folderName);
        }

        // Define Data Extension configuration
        var config = {
            "CustomerKey": dataExtensionName,
            "Name": dataExtensionName,
            "CategoryID": folderID, // Assign folderID to Data Extension
            "Fields": [
                // Define fields for Data Extension
                { "Name": "ObjectID", "FieldType": "Text", "MaxLength": 36, "IsPrimaryKey": true, "IsRequired": true },
                { "Name": "TaskResultID", "FieldType": "Number"},
                { "Name": "ImportStatus", "FieldType": "Text", "MaxLength": 50 },
                { "Name": "ImportType", "FieldType": "Text", "MaxLength": 200 },
                { "Name": "TotalRows", "FieldType": "Number" },
                { "Name": "NumberErrors", "FieldType": "Number" },
                { "Name": "NumberRestricted", "FieldType": "Number" },
                { "Name": "NumberDuplicated", "FieldType": "Number" },
                { "Name": "NumberSuccessful", "FieldType": "Number" },
                { "Name": "DestinationID", "FieldType": "Text", "MaxLength": 50 },
                { "Name": "StartDate", "FieldType": "Date"},
                { "Name": "EndDate", "FieldType": "Date"},
                { "Name": "ImportDefinitionCustomerKey", "FieldType": "Text", "MaxLength": 50 },
                { "Name": "FileSpec", "FieldType": "Text", "MaxLength": 400 }
            ]
        };

        // Create the Data Extension using WSProxy API and return the result
        var result = api.createItem("DataExtension", config);
        return Stringify(result);
    }

</script>

TOP


Comments

Post a Comment


Knowledge Article

Most Viewed

Image

CLOUD PAGE ENABLEMENT - PART 1

 CLOUD PAGE ENABLEMENT - PART 1 CREATE A CLOUD PAGE IN MARKETING CLOUD  USE CASE : Create a landing page in marketing cloud to collect consent from subscribers. Step 1: Create a landing page and publish it [no coding required] Step 2: Build a form control using bootstrap  <form class = "row g-3"       action = "https://tenantEndpoint/ConsentCollection"       method = "Post" > </form> Update your form "action" attribute value with your cloud page URL. Method attribute "Post" Add form controls  To collect email, add the below form control <div class = "col-md-6" >       <label for = "inputEmail4" class = "form-label" > Email </label>       <input type = "email" class = "form-control"               id = "inputEmail4" name = "inputEmail4"               value = "%%=v(@inputEmail4)=%%" required %% = v(@disabled) = %% &
Image

EMAIL NOT SENT IN JOURNEY BUILDER

TROUBLESHOOTING JOURNEY SENDS First step towards troubleshooting in SFMC is to install Query Studio from AppExchange. https://appexchange.salesforce.com/appxListingDetail?listingId=a0N3A00000FP3yFUAT Once you have access to Query studio you can write SQL queries and troubleshoot the issues quickly. If you are building a complex queries for a use case, you can write and execute the queries to see if it returns the desired output. Once, you are satisfied with your query you can save and add it to query activity. You can also save the output into a data extension if required, by default query studio stores the output into a data extension under the folder QueryStudioResults. If you have to execute another SQL with the output of the previous result, you can reference the Data Extensions and apply it in your FROM statement. FAQ : 1. How to find out who all did not receive email during journey send ? Best practice is to enable send logging for your account. This will help a-lot during troubl
Image

CONSIDERATIONS FOR JOURNEY BUILDER

 CONSIDERATIONS FOR JOURNEY BUILDER   WHAT HAPPENS WHEN YOU SEND AN EMAIL OUTBOUND MAIL MANAGEMENT GETS INVOKED BY MARKETING CLOUD , OMM IS NOT IN OUR CONTROL BUT LETS GO THROUGH BEHIND THE SCENE ACTIONS. 1. OOM PREPARES DATABASE FOR THE JOBS, OOM ANALYZES THE EMAIL, WHETHER IT'S A TEMPLATE BASED EMAIL OR HTML, WHETHER EMAILS CONTAINS PERSONALIZATIONS OR DYANMIC CONTNETS.  2.  OOM STRUCTURES THE EMAIL BASED ON HEADER , BODY AND FOOTER. OOM RETRIEVES THE SUBJECT LINE , BODY CONTENT , AMPSCRIPT IN THE EMAIL AND INTERPRETS THE AMPSCRIPT AND RETIRVES IMAGES. 3. BASED ON THE DATABASE AND STRUCUTRE MAINATAINED BY THE OOM, IT DETERMINES THE CONTENT FOR THE SUBSCRIBERS AND PROCESSES TO MAINTAIN THE PLACEHOLDERS FOR EACH SUBSCRIBERS. 4. OOM HAS PLACED THE PROCESSES IN THE QUEUE, OOM BUNDLES EMAILS IN 500 BATCHES AND SEND TO SUBSCRIBERS INBOX.  NOW WE HAVE THE BACKGROUD , WHAT ARE THE CONSIDERATIONS FOR BUILDING A JOURNEY  JOURNEY PERFORMANCE ARE BASED ON : 1. DATA MODEL 2. FILTERS  3. JOURN
Image

Journey Builder REST API Documentation

Journey Builder REST API Documentation Home Page Journey Builder REST API Documentation Introduction The Journey Builder REST API allows you to programmatically interact with the Journey Builder feature of Salesforce Marketing Cloud. With this API, you can create, update, and manage customer journeys, as well as perform various operations related to contacts and interactions within those journeys. This document provides detailed information on how to use the Journey Builder REST API to integrate and automate your marketing workflows. Base URL The base URL for accessing the Journey Builder REST API is:
Image

Preference Center Demystified

Preference Center Demistifier Home Page Preference Center Demystified Email preference centers can unlock a wealth of subscriber information if executed properly. Learn more about them to create the personalized email experiences that build loyalty and trust.

Popular Posts

Image

CLOUD PAGE ENABLEMENT - PART 1

 CLOUD PAGE ENABLEMENT - PART 1 CREATE A CLOUD PAGE IN MARKETING CLOUD  USE CASE : Create a landing page in marketing cloud to collect consent from subscribers. Step 1: Create a landing page and publish it [no coding required] Step 2: Build a form control using bootstrap  <form class = "row g-3"       action = "https://tenantEndpoint/ConsentCollection"       method = "Post" > </form> Update your form "action" attribute value with your cloud page URL. Method attribute "Post" Add form controls  To collect email, add the below form control <div class = "col-md-6" >       <label for = "inputEmail4" class = "form-label" > Email </label>       <input type = "email" class = "form-control"               id = "inputEmail4" name = "inputEmail4"               value = "%%=v(@inputEmail4)=%%" required %% = v(@disabled) = %% &
Image

EMAIL NOT SENT IN JOURNEY BUILDER

TROUBLESHOOTING JOURNEY SENDS First step towards troubleshooting in SFMC is to install Query Studio from AppExchange. https://appexchange.salesforce.com/appxListingDetail?listingId=a0N3A00000FP3yFUAT Once you have access to Query studio you can write SQL queries and troubleshoot the issues quickly. If you are building a complex queries for a use case, you can write and execute the queries to see if it returns the desired output. Once, you are satisfied with your query you can save and add it to query activity. You can also save the output into a data extension if required, by default query studio stores the output into a data extension under the folder QueryStudioResults. If you have to execute another SQL with the output of the previous result, you can reference the Data Extensions and apply it in your FROM statement. FAQ : 1. How to find out who all did not receive email during journey send ? Best practice is to enable send logging for your account. This will help a-lot during troubl
Image

CONSIDERATIONS FOR JOURNEY BUILDER

 CONSIDERATIONS FOR JOURNEY BUILDER   WHAT HAPPENS WHEN YOU SEND AN EMAIL OUTBOUND MAIL MANAGEMENT GETS INVOKED BY MARKETING CLOUD , OMM IS NOT IN OUR CONTROL BUT LETS GO THROUGH BEHIND THE SCENE ACTIONS. 1. OOM PREPARES DATABASE FOR THE JOBS, OOM ANALYZES THE EMAIL, WHETHER IT'S A TEMPLATE BASED EMAIL OR HTML, WHETHER EMAILS CONTAINS PERSONALIZATIONS OR DYANMIC CONTNETS.  2.  OOM STRUCTURES THE EMAIL BASED ON HEADER , BODY AND FOOTER. OOM RETRIEVES THE SUBJECT LINE , BODY CONTENT , AMPSCRIPT IN THE EMAIL AND INTERPRETS THE AMPSCRIPT AND RETIRVES IMAGES. 3. BASED ON THE DATABASE AND STRUCUTRE MAINATAINED BY THE OOM, IT DETERMINES THE CONTENT FOR THE SUBSCRIBERS AND PROCESSES TO MAINTAIN THE PLACEHOLDERS FOR EACH SUBSCRIBERS. 4. OOM HAS PLACED THE PROCESSES IN THE QUEUE, OOM BUNDLES EMAILS IN 500 BATCHES AND SEND TO SUBSCRIBERS INBOX.  NOW WE HAVE THE BACKGROUD , WHAT ARE THE CONSIDERATIONS FOR BUILDING A JOURNEY  JOURNEY PERFORMANCE ARE BASED ON : 1. DATA MODEL 2. FILTERS  3. JOURN
Image

Journey Builder REST API Documentation

Journey Builder REST API Documentation Home Page Journey Builder REST API Documentation Introduction The Journey Builder REST API allows you to programmatically interact with the Journey Builder feature of Salesforce Marketing Cloud. With this API, you can create, update, and manage customer journeys, as well as perform various operations related to contacts and interactions within those journeys. This document provides detailed information on how to use the Journey Builder REST API to integrate and automate your marketing workflows. Base URL The base URL for accessing the Journey Builder REST API is:
Image

Preference Center Demystified

Preference Center Demistifier Home Page Preference Center Demystified Email preference centers can unlock a wealth of subscriber information if executed properly. Learn more about them to create the personalized email experiences that build loyalty and trust.
Image

SEND LOG EANBLEMENT

SEND LOG EANBLEMENT STEP BY STEP GUIDE HOW TO ENABLE SEND LOGGING IN SALESFORCE MARKETING CLOUD  STEP 1 : CREATE SEND LOG DATA EXTENSION Create a data extension for Send Log utilizing send log template. You can create similar Send log for SMS, Push etc.,             Default attributes for Send Log Add custom attributes to send log, custom attributes will increase you query optimization and troubleshooting If you have Google Analytics enabled you can add utm_campaign and utm_source attributes as well. Best Practice would be to archive send log on a monthly basis and set the retention policy to 6 months.  STEP 2 : CREATE CODE SNIPPET TO ADD CUSTOM ATRRIBUTES TO SEND LOG  Default send log attributes gets added to send log by default as it picks from system personalization string  Email personalization strings can be found in email tool bar: Profile and System Attribute   Create a code snippet  Write an AmpScript block to add custom attributes to Send Log data extension                   %

Share with Friends

Disclaimer:

The information provided on this technical blog is for general informational purposes only. As a SFMC (Salesforce Marketing Cloud) Technical Architect, I strive to offer accurate and up-to-date content related to SFMC and its associated technologies. However, please note that technology is constantly evolving, and the information provided may become outdated or inaccurate over time.

The content published on this blog represents my personal views and experiences as a SFMC Technical Architect and does not necessarily reflect the official views or opinions of any organization or employer I may be affiliated with.

While I make every effort to ensure the accuracy and reliability of the information presented, I cannot guarantee its completeness, suitability, or applicability to your specific circumstances. Therefore, it is essential to verify any information provided and make your own independent assessments or seek professional advice if needed.

Furthermore, any actions taken based on the information provided on this blog are at your own risk. I shall not be held liable for any damages, losses, or inconveniences arising from the use of the information presented here.

Please keep in mind that SFMC and its associated technologies are complex and require technical expertise for proper implementation and management. It is recommended to consult with qualified professionals or official SFMC documentation for comprehensive guidance.

Finally, please note that any product or company names mentioned on this blog are trademarks or registered trademarks of their respective owners. The mention of these trademarks or registered trademarks does not imply any endorsement or affiliation with the blog.

By accessing and using this blog, you agree to the terms of this disclaimer. If you do not agree with any part of this disclaimer, please refrain from using this blog.

Home Page