marketingclouded

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

marketingclouded

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

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

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

marketingclouded

<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

<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

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

marketingclouded

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

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

marketingclouded

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