Data Views

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>

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


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

    try {
        // Define Data Extension and folder names
        var dataExtensionName = "Audit_Trail_Log";
        var folderName = "1. Logging";

        // 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
                { "CustomerKey":"CreatedDate","Name": "CreatedDate", "FieldType": "Date", "IsPrimaryKey":false,"IsRequired":false },
                { "CustomerKey":"EnterpriseId","Name":"EID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
                { "CustomerKey":"MemberId","Name":"MID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"UserID","Name":"UserID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"EmployeeID","Name":"EmployeeID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"EmployeeName","Name":"EmployeeName","FieldType":"Text","MaxLength":100,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"ObjectTypeID","Name":"ObjectTypeID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"ObjectTypeName","Name":"ObjectTypeName","FieldType":"Text","MaxLength":250,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"OperationID","Name":"OperationID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"OperationName","Name":"OperationName","FieldType":"Text","MaxLength":100,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"ObjectID","Name":"ObjectID","FieldType":"Text","MaxLength":36,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"ObjectName","Name":"ObjectName","FieldType":"Text","MaxLength":250,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"TransactionID","Name":"TransactionID","FieldType":"Text","MaxLength":36,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"InsertedDate","Name":"InsertedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"getdate()"}
            ]
        };

        // 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 Audit Trail Access Log Data Extension.


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

    try {
        // Define Data Extension and folder names
        var dataExtensionName = "Audit_Trail_Access_Log";
        var folderName = "1. Logging";

        // 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
                {"CustomerKey":"User","Name":"User","FieldType":"Text","MaxLength":100,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"UserName","Name":"UserName","FieldType":"Text","MaxLength":100,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"AccessDate","Name":"AccessDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"FromIP","Name":"FromIP","FieldType":"Text","MaxLength":15,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"SecurityEventTypeID","Name":"SecurityEventTypeID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"SecurityEventType","Name":"SecurityEventType","FieldType":"Text","MaxLength":100,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"LoginStatusID","Name":"LoginStatusID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"LoginStatusName","Name":"LoginStatusName","FieldType":"Text","MaxLength":100,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"UserAgent","Name":"UserAgent","FieldType":"Text","MaxLength":250,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"Event Source","Name":"Event Source","FieldType":"Text","MaxLength":100,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"InsertedDate","Name":"InsertedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"getdate()"}
            ]
        };

        // 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 Security Events Data Extension.


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

    try {
        // Define Data Extension and folder names
        var dataExtensionName = "Security_Events";
        var folderName = "1. Logging";

        // 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
                {"CustomerKey":"SecurityEventTypeID","Name":"SecurityEventTypeID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"SecurityEventType","Name":"SecurityEventType","FieldType":"Text","MaxLength":200,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"Description","Name":"Description","FieldType":"Text","MaxLength":4000,"IsPrimaryKey":false,"IsRequired":false}
            ]
        };

        // 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 Login Status Data Extension.


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

    try {
        // Define Data Extension and folder names
        var dataExtensionName = "Login Status";
        var folderName = "1. Logging";

        // 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
                {"CustomerKey":"LoginStatusID","Name":"LoginStatusID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"LoginStatusName","Name":"LoginStatusName","FieldType":"Text","MaxLength":200,"IsPrimaryKey":false,"IsRequired":false},
                {"CustomerKey":"Description","Name":"Description","FieldType":"Text","MaxLength":4000,"IsPrimaryKey":false,"IsRequired":false}
            ]
        };

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

</script>

Comments

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.

Search This Blog

Data Views

Comments

Post a Comment

Most Viewed

CLOUD PAGE ENABLEMENT - PART 1

EMAIL NOT SENT IN JOURNEY BUILDER

CONSIDERATIONS FOR JOURNEY BUILDER

Understanding Transactional Messaging

Preference Center Demystified

Popular Posts

CLOUD PAGE ENABLEMENT - PART 1

EMAIL NOT SENT IN JOURNEY BUILDER

CONSIDERATIONS FOR JOURNEY BUILDER

Understanding Transactional Messaging

Preference Center Demystified

Journey Builder REST API Documentation

Read more >>

Share with Friends

Data Views

Sent #1

Open #2

Click #3

Bounce #4

Unsubscribe #5

Complaint #6

FTAT #7

BusinessUnitUnsubscribes #8

Subscribers #9

Not Sent #10

Journey Activity #11

Journey #12

TriggeredSendDefinition #13

Not Sent Summary #14

Import Definition #15

Import Definition Results #16

Audit Trail Log #17

Audit Trail Access Log #18

Security Events #19

Login Status #20

Comments

Post a Comment

Most Viewed

Popular Posts

Read more >>

Share with Friends