
Vindhya Rani
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.
