marketingclouded’s gists

marketingclouded / Updates Performance_Subscriber w _Subscriber and Sent Count

Last active November 25, 2020 04:06

Tracking SFMC Subscriber Behavior: First SQL Query to Update Performance_Subscriber Data Extension with _Subscriber info and Sent Count

marketingclouded / Updates Performance_Subscriber w Open & Click Counts

Last active November 25, 2020 04:06

Tracking SFMC Subscriber Behavior: Second SQL Query to Update Performance_Subscriber Data Extension with Opens and Clicks

	SELECT
	e.SubscriberID,
	g.AllClicks,
	r.AllOpens,
	a.UniqueOpens,
	y.UniqueClicks
	FROM _Subscribers e
	JOIN (
	SELECT SubscriberID, COUNT(SubscriberID) AS 'AllClicks' FROM _Click GROUP BY SubscriberID
	) g

marketingclouded / Calculates CTR and Open Rate for Performance_Subscriber

Last active December 29, 2020 23:24

Tracking SFMC Subscriber Behavior: Third SQL Query to Calculate Open and Click Rates for Performance_Subscriber Data Extension

	SELECT
	SubscriberID,
	CAST(UniqueOpens AS DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'UniqueOpenRate',
	CAST(UniqueClicks AS DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'UniqueCTR',
	CAST(AllOpens AS DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'AllOpenRate',
	CAST(AllClicks AS DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'AllCTR'
	FROM Performance_Subscriber
	WHERE Sent > 0

marketingclouded / Create Performance_Subscriber Data Extension with SSJS

Created September 23, 2020 16:01

Tracking SFMC Subscriber Behavior: Creating the Data Extensions Performance_Subscriber using 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 / Appends Account_Health_Daily

Created September 23, 2020 19:51

Tracking SFMC Account Health: Appends Account_Health_Daily

	SELECT
	FORMAT(DATEADD(d,-1,GETDATE()),'MMM dd yyyy') AS 'Date',
	DATEADD(d,-1,GETDATE()) AS 'DateStamp',
	FORMAT(DATEADD(d,-1,GETDATE()),'MMMM, yyyy') AS 'Month',
	(SELECT COUNT(SubscriberID) FROM _Subscribers WHERE DateJoined >= DATEADD(d,DATEDIFF(day, 1, GETDATE()),0) AND DateJoined < DATEADD(d,DATEDIFF(day, 0, GETDATE()),0)) AS 'NewSubscribers',
	(SELECT COUNT(SubscriberID) FROM _Subscribers WHERE DateUnsubscribed >= DATEADD(d,DATEDIFF(day, 1, GETDATE()),0) AND DateUnsubscribed < DATEADD(d,DATEDIFF(day, 0, GETDATE()),0)) AS 'Unsubscribes',
	(SELECT COUNT(SubscriberID) FROM _Subscribers WHERE Status = 'active' AND DateJoined < DATEADD(d,DATEDIFF(day, 1, GETDATE()),0)) AS 'ActiveSubscribers',
	(SELECT COUNT(SubscriberID) FROM _Sent WHERE EventDate >= DATEADD(d,DATEDIFF(day, 1, GETDATE()),0) AND EventDate < DATEADD(d,DATEDIFF(day, 0, GETDATE()),0)) AS 'Sent',
	(SELECT COUNT(SubscriberID) FROM _Open WHERE EventDate >= DATEADD(d,DATEDIFF(day, 1, GETDATE()),0) AND EventDate < DATEADD(d,DATEDIFF(day, 0,

marketingclouded / Overwrites Account_Health_Daily_Sorted

Created September 23, 2020 19:53

Tracking SFMC Account Health: Overwrites Account_Health_Daily_Sorted

	SELECT TOP 9223372036854775807
	NewSubscribers, Date, Unsubscribes, Sent, Opens, Clicks, Bounces, DateStamp, Month, ActiveSubscribers
	FROM Account_Health_Daily
	ORDER BY DateStamp DESC

marketingclouded / Overwrites Account_Health_Monthly w Account_Health_Daily

Created September 23, 2020 19:54

Tracking SFMC Account Health: Overwrites Account_Health_Monthly w Account_Health_Daily

	SELECT
	Month,
	SUM(SentCount) AS 'Sent',
	SUM(NewSubscribers) AS 'NewSubscribers',
	SUM(Unsubscribes) AS 'Unsubscribes',
	SUM(BounceCount) AS 'Bounces',
	SUM(NewSubscribers) - SUM(Unsubscribes) AS 'NetGrowth',
	MIN(DateStamp) AS 'MonthStamp'
	FROM Account_Health_Daily
	GROUP BY Month

marketingclouded / Updates Account_Health_Monthly w ActiveSubscribers

Created September 23, 2020 19:55

Tracking SFMC Account Health: Updates Account_Health_Monthly w ActiveSubscribers

	SELECT e.ActiveSubscribers, d.Month
	FROM Account_Health_Daily e
	INNER JOIN Account_Health_Monthly d
	ON d.MonthStamp = e.DateStamp

Created September 23, 2020 19:57

Tracking SFMC Account Health: Calculates CTR, Open Rate & List Growth in Account_Health_Monthly

	SELECT
	e.Month,
	CAST(SUM(d.Opens) as DECIMAL(18,2)) / CAST(SUM(d.Sent) AS DECIMAL(18,2)) * 100 AS 'UniqueOpenRate',
	CAST(sum(d.Clicks) AS DECIMAL(18,2)) / CAST(SUM(d.Sent) AS DECIMAL(18,2)) * 100 AS 'UniqueCTR',
	CAST(e.NetGrowth AS DECIMAL(18,2)) / CAST(e.ActiveSubscribers AS Decimal(18,2)) * 100 AS 'PercentGrowth'
	FROM Account_Health_Monthly e
	JOIN Account_Health_Daily d
	ON e.Month = d.Month
	WHERE d.SentCount > 0
	GROUP BY e.Month, e.NetGrowth, e.ActiveSubscribers

marketingclouded / Backfills Account_Health_Daily

Created September 23, 2020 19:58

Tracking SFMC Account Health: Backfills Account_Health_Daily

	SELECT Top 1
	FORMAT(DATEADD(d,-1,e.DateStamp),'MMM dd yyyy') AS 'Date',
	DATEADD(d,-1,e.DateStamp) AS 'DateStamp',
	FORMAT(DATEADD(d,-1,e.DateStamp),'MMMM, yyyy') AS 'Month',
	(SELECT COUNT(SubscriberID) FROM _Subscribers WHERE DateJoined >= DATEADD(d,DATEDIFF(day, 1, e.DateStamp),0) AND DateJoined < DATEADD(d,DATEDIFF(day, 0, e.DateStamp),0)) AS 'NewSubscribers',
	(SELECT COUNT(SubscriberID) FROM _Subscribers WHERE DateUnsubscribed >= DATEADD(d,DATEDIFF(day, 1, e.DateStamp),0) AND DateUnsubscribed < DATEADD(d,DATEDIFF(day, 0, e.DateStamp),0)) AS 'Unsubscribes',
	(SELECT COUNT(SubscriberID) FROM _Subscribers WHERE Status = 'active' AND DateJoined < DATEADD(d,DATEDIFF(day, 0, e.DateStamp),0)) AS ActiveSubscribers,
	(SELECT COUNT(SubscriberID) FROM _Sent WHERE EventDate >= DATEADD(d,DATEDIFF(day, 1, e.DateStamp),0) AND EventDate < DATEADD(d,DATEDIFF(day, 0, e.DateStamp),0)) AS 'Sent',
	(SELECT COUNT(SubscriberID) FROM _Open WHERE EventDate >= DATEADD(d,DATEDIFF(day, 1, e.DateStamp),0) AND EventDate < D

Ed Gray marketingclouded