Track Email Status logs using Data views:

How do we capture Bounce,Blocked,Unsubscribed.. etc? and what is the LIMIT of its storage?

As we know in SFDC there are few inbuilt tables where capture the batch class information etc.. In the same way SFMC has feature called Data Views.

Data views have some individual standard tables for each of the actions like Bounce,Click,Complaint,Forward,Open,Sent,Subscribes and few more.

These tables have many columns to store the JobId,Email, FirstName etc..

It is good that it stores this information but It will purge it after six months. So in order to safeguard this information. We can write automation to transfer all the day to day tracking logs to one Data extension.

From that Data extension we can move it anywhere based on our need. Data extension will have a heavy storage base(Nowhere its size limit is mentioned, Need to check with Salesforce).

Example of an aggregation on the recipient which campaign and which email or push notification he or she got, opened, clicked, unsubscribed from or forwarded.

It will stored into Data Extension in regular basis with Automation.

Solution :

1. Create an Automation to schedule on Daily,Weekly or monthly:

2. Write a query in query editor fetch all the necessary logs from Data views

SELECT j.EmailName AS Campaign, j.JobID, s.EmailAddress AS Recipient_email, s.SubscriberKey AS Peid, j.FromEmail AS Sender_email, o.Opencount AS Opens, C.ClickCount AS Clicks, U.UnsubscribeCount AS Unsubscribed, F.FTATCount AS Forwarded

FROM _Job j

INNER JOIN _Sent se

on j.JobID = se.JobID

INNER JOIN _Subscribers s

ON se.SubscriberKey = s.SubscriberKey

LEFT JOIN (SELECT

count(SubscriberKey) as OpenCount, s.JobID

FROM _open o

INNER JOIN _Job s

ON o.JobID = s.JobID

GROUP BY s.JobID) O

ON O.JobID = j.JobID

LEFT JOIN(

SELECT

count(SubscriberKey) as ClickCount, s.JobID

FROM _Click o

INNER JOIN _Job s

ON o.JobID = s.JobID

GROUP BY s.JobID) C

ON C.JobID = j.JobID

LEFT JOIN(

SELECT

count(SubscriberKey) as UnsubscribeCount, s.JobID

FROM _Unsubscribe o

INNER JOIN _Job s

ON o.JobID = s.JobID

GROUP BY s.JobID

) U

ON U.JobID = j.JobID

LEFT JOIN (

SELECT

count(SubscriberKey) as FTATCount, s.JobID

FROM _FTAF o

INNER JOIN _Job s

ON o.JobID = s.JobID

GROUP BY s.JobID) F

ON F.JobID = j.JobID.

3. Save the query and map the fields according to the Target Data Extension. Then activate the Automation

Note: Overwriting the DE will be the correct approach

4. Once the Automation is fine The data stored in DE can be exported into CSV file or using AMPScript we can push to other systems.