Skip to content

Instantly share code, notes, and snippets.

@marketingclouded

marketingclouded/Appends Account_Health_Daily

Created September 23, 2020 19:51
Show Gist options
  • Save marketingclouded/2d73244c082f6f2d0d78ee1e5ca7650c to your computer and use it in GitHub Desktop.
Save marketingclouded/2d73244c082f6f2d0d78ee1e5ca7650c to your computer and use it in GitHub Desktop.
Download ZIP
Tracking SFMC Account Health: Appends Account_Health_Daily
Raw
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, GETDATE()),0) AND IsUnique = 1) AS 'Opens',
(SELECT COUNT(SubscriberID) FROM _Click WHERE EventDate >= DATEADD(d,DATEDIFF(day, 1, GETDATE()),0) AND EventDate < DATEADD(d,DATEDIFF(day, 0, GETDATE()),0) AND IsUnique = 1) AS 'Clicks',
(SELECT COUNT(SubscriberID) FROM _Bounce WHERE EventDate >= DATEADD(d,DATEDIFF(day, 1, GETDATE()),0) AND EventDate < DATEADD(d,DATEDIFF(day, 0, GETDATE()),0)) AS 'Bounces'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment