Ed Gray marketingclouded

Salesforce Marketing Cloud consultant with LeraningCurv in Austin, TX.

marketingclouded / Creates 3 Account Health Data Extensions

Created September 23, 2020 20:00

Tracking SFMC Account Health: Creates 3 Account Health Data Extensions

	<script runat="server">
	Platform.Load("core", "1.1.1");

	var api = new Script.Util.WSProxy();

	try {

	var FolderName = "Reporting";
	var FolderAttributes = Folder.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: FolderName });
	var FolderID = FolderAttributes[0].ID;

marketingclouded / Create Email_Performance_Sched Data Extensions

Created September 23, 2020 20:28

Tracking Email Performance in SFMC: Create Email_Performance_Sched Data Extensions

	<script runat="server">
	Platform.Load("core", "1.1.1");

	var api = new Script.Util.WSProxy();

	try {

	var FolderName = "Reporting";
	var FolderAttributes = Folder.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: FolderName });
	var FolderID = FolderAttributes[0].ID;

marketingclouded / Overwrites Email_Performance_Sched w _Sent and _Job Info

Created September 23, 2020 20:29

Tracking Email Performance in SFMC: Overwrites Email_Performance_Sched w _Sent and _Job Info

	SELECT TOP 9223372036854775807
	e.EmailName,
	e.SchedTime AS 'SentTime',
	e.JobID,
	COUNT(d.JobID) AS 'Sent'
	FROM _Job e
	JOIN _Sent d
	ON d.JobId = e.JobId
	WHERE e.TriggererSendDefinitionObjectID IS NULL
	GROUP BY e.EmailName, e.SchedTime, e.JobId

Created September 23, 2020 20:31

Tracking Email Performance in SFMC: Updates Email_Performance_Sched w Opens, Clicks, Bounces, Unsubscribes

	SELECT
	e.JobID,
	g.Clicks,
	r.Opens,
	a.Bounces,
	y.Unsubscribes
	FROM Email_Performance_Sched e
	JOIN (
	SELECT JobID, COUNT(JobID) AS Clicks FROM _Click WHERE IsUnique = 1 GROUP BY JobID
	) g

marketingclouded / Updates Rates in Email_Performance_Sched

Created September 23, 2020 20:32

Tracking Email Performance in SFMC: Updates Rates in Email_Performance_Sched

	SELECT
	JobID,
	CAST(Unsubscribes as DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'UnsubRate',
	CAST(Opens as DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'OpenRate',
	CAST(Clicks as DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'CTR',
	CAST(Bounces as DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'BounceRate'
	FROM Email_Performance_Sched

marketingclouded / Overwrites Email_Perf_Sched_Last_28Days

Created September 23, 2020 20:33

Tracking Email Performance in SFMC: Overwrites Email_Perf_Sched_Last_28Days

	SELECT *
	FROM Email_Performance_Sched
	WHERE SentTime > DATEADD(d,DATEDIFF(day,30,GetDate()),0) AND SentTime < DATEADD(d,DATEDIFF(day,2,GetDate()),0)

Created September 23, 2020 20:41

Tracking Inactive Subscribers in SFMC: Create Subscriber_Last_Open and Inactive_Subscriber Data Extensions with SSJS

	<script runat="server">
	Platform.Load("core", "1.1.1");

	var api = new Script.Util.WSProxy();

	try {

	var FolderName = "Reporting";
	var FolderAttributes = Folder.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: FolderName });
	var FolderID = FolderAttributes[0].ID;

marketingclouded / Updates Subscriber_Last_Open with _Subscribers and Last Sent

Created September 23, 2020 20:42

Tracking Inactive Subscribers in SFMC: Updates Subscriber_Last_Open with _Subscribers and Last Sent

	SELECT
	e.EmailAddress,
	e.SubscriberID,
	e.Status,
	e.DateJoined,
	e.DateUnsubscribed,
	MAX(d.EventDate) AS 'LastSentDate',
	COUNT(d.SubscriberID) AS Sent
	FROM _Subscribers e
	JOIN _Sent d

marketingclouded / Updates Subscriber_Last_Open w _Open and _Job Info

Last active September 23, 2020 20:43

Tracking Inactive Subscribers in SFMC: Updates Subscriber_Last_Open w _Open and _Job Info

	SELECT
	g.SubscriberID,
	MAX(a.EventDate) AS 'LastOpenDate',
	MAX(a.JobID) AS 'JobID',
	y.EmailName
	FROM Subscriber_Last_Open g
	JOIN _Open a
	ON g.SubscriberID = a.SubscriberID
	JOIN _Job y
	ON a.JobID = y.JobID

marketingclouded / Calculates Subscriber_Last_Open with DateDiff

Created September 23, 2020 20:44

Tracking Inactive Subscribers in SFMC: Calculates Subscriber_Last_Open with DateDiff

	Select SubscriberID,
	DateDiff(day,LastOpenDate,LastSentDate) AS 'DateDiff'
	FROM Subscriber_Last_Open