Skip to main content

Data Views in Salesforce Marketing Cloud (SFMC) - _Sent

Data Views:


Data Views are pre-built system tables in SFMC that store subscriber engagement and tracking data. These tables automatically update and provide valuable insights into email performance, subscriber behavior, and campaign optimization.


🔹 Key Benefits of Data Views:

✅ Track email sends, opens, clicks, bounces, and unsubscribes
✅ Analyze customer engagement trends over time
✅ Identify inactive subscribers for re-engagement
✅ Use SQL JOINs to combine data for advanced segmentation

🚀 1. Key Data Views in SFMC & Their Use Cases

Each Data View stores tracking data for up to 6 months and contains different fields related to subscriber interactions.

Data View

Purpose

Important Fields

_Subscribers

Stores subscriber details & statuses

SubscriberKey, EmailAddress, Status

_Sent

Tracks sent emails

SubscriberKey, JobID, EventDate

_Open

Tracks email opens

SubscriberKey, JobID, EventDate

_Click

Tracks link clicks

SubscriberKey, JobID, EventDate, URL

_Bounce

Identifies bounced emails

SubscriberKey, BounceType, EventDate

_Unsubscribe

Tracks unsubscribed users

SubscriberKey, EventDate

_Job

Stores email job details

JobID, EmailSubject, EmailName

🚀 2. Querying Data Views in SFMC

Since Data Views are not directly accessible via the UI, you need SQL queries in Automation Studio to extract and analyze data.

✅ Finding Email Engagement (Sends, Opens, Clicks)

This query tracks the customer journey from email send → open → click:

SQL Query:

SELECT s.SubscriberKey, s.EmailAddress, s.EventDate AS SentDate, o.EventDate AS OpenDate, c.EventDate AS ClickDate FROM _Sent s LEFT JOIN _Open o ON s.SubscriberKey = o.SubscriberKey AND s.JobID = o.JobID LEFT JOIN _Click c ON s.SubscriberKey = c.SubscriberKey AND s.JobID = c.JobID WHERE s.EventDate > DATEADD(DAY, -30, GETDATE())

When I try with the first SQL query, I got the error below because the Source DE "_Sent s" does not have email field. After removing the emailaddress from the query, I was able to proceed further:



Target DE:




Comments

Post a Comment

Popular posts from this blog

A/B Testing

Confluence link 1:  https://elearningit24.atlassian.net/wiki/external/MzA3YWUxNjI3YzBkNGZiZmFlZTc1ZGIzYmU1ZmM1MmQ   What is A/B Testing? A/B testing, also known as split testing , is a controlled experiment where two versions of a webpage, email, ad, or any digital asset are tested against each other to determine which one performs better. Why? Higher open rates Better Engagement Increased Sales and Lead conversion Reduced unsubscribed rates Date Driven- No assumption & Proven Results Test Type Subject Line Email Content Area From Name Send Date & Time Pre Header   Email Test Template: Friendly and Personalised Straight to the point & Action Oriented Confluence Link 2:  https://elearningit24.atlassian.net/wiki/external/NDk0Zjg0ODk3YjAwNDI5ZjkxOTVmMTAxNTQ3ZjlhNmQ Subject Line A/B Test: A/B Testing Use Case: Optimizing Email Subject Lines for Higher Open Rates Scenario- Subject Line: A company wants to increase the open rates of its promotional emails in Sa...
Post a Comment
Read more

Email Marketing Campaings

Use Cases of Standard Data Extensions 1. Customer Data Storage Scenario : A retail business wants to store customer information, such as names, email addresses, and purchase history, for email marketing campaigns. Solution : Use a standard data extension to store and manage customer data securely and efficiently. Data Extension Structure Field Name Data Type Length Required Primary Key SubscriberKey Text 50 Yes Yes FirstName Text 50 Yes No LastName Text 50 Yes No EmailAddress EmailAddress N/A Yes No PurchaseHistory Decimal N/A No No Sample Records SubscriberKey FirstName LastName EmailAddress PurchaseHistory 001 John Doe john.doe@example.com 1500.50 002 Jane Smith jane.smith@example.com 200.00 https://mc.s8.exacttarget.com/cloud/#app/Email/C12/Default.aspx?entityType=none&entityID=0&ks=ks%23Subscribers
Post a Comment
Read more

SQl Query

  Workflow: Journey Builder> Automation Studio> New Automation Enter Query: Drag and drop Schedule in  Start with a Starting Source and the SQL Query> Choose Query> Create New Query Activity> Name your file and add your query (Field name thats part of the DE you create separately- Example: FirstName, LastName, Subscriber Key) Create a new DE in Email Studio with the query field> Select The test DE which was created: Select Target DE and Save & Run Workflow: Select All Activities and Run Workflow: Activity Details: Run Completed with updated DE Edit/ add another query: Copy Source DE and create a new DE with same fields that we are running in SQL: Run the updated SQL Query added Activity and DE updated with Records: Completed Status: Received error: When the Query field and the DE created field does not match When an existing Query is being run, if we try to run another query Fixed the error by matching same field Waiting for existing query to complete
Post a Comment
Read more