Salesforce Marketing Cloud Data Views: SMSSubscriptionLog

Posted on by admin

The SMSSubscriptionLog is one of the most valuable system data views in Salesforce Marketing Cloud (SFMC) for MobileConnect. It captures a full historical log of SMS subscriber opt-in and opt-out activity, making it critical for compliance, audience management, and tracking SMS program effectiveness.

This blog explores the field-level definitions, use cases, and SQL examples to help marketers and developers make full use of the SMSSubscriptionLog view.

What is the SMSSubscriptionLog Data View?

The SMSSubscriptionLog tracks SMS subscription events, including when a subscriber opts in or out, how they performed that action, and the method and source of the subscription. Unlike some other data views, this one maintains longer historical data beyond six months and is ideal for auditing and analytics.

✅ Tip: Use this view to comply with regional SMS laws and carrier regulations by maintaining a clear opt-in audit trail.

Key Benefits

  • Historical Audit Log: Captures opt-in and opt-out timestamps.
  • Compliance Assurance: Provides opt-in/out method IDs for transparency.
  • Behavior Analysis: Understand how users interact with SMS opt-in campaigns.
  • Campaign Optimization: Evaluate the effectiveness of opt-in sources.

Fields in SMSSubscriptionLog

FieldTypeDescription
LogDateDateThe timestamp when the subscription activity was logged.
MobileSubscriptionIDNumberUnique ID for the mobile subscription event.
SubscriptionDefinitionIDTextIdentifier for the SMS keyword or subscription campaign.
MobileNumberTextSubscriber’s mobile phone number.
OptOutStatusIDNumberID indicating if the user has opted out.
OptOutMethodIDNumberID representing the opt-out method used (e.g., STOP reply, API).
OptOutDateDateDate when the user opted out.
OptInStatusIDNumberID indicating if the user opted in.
OptInMethodIDNumberID representing the opt-in method used (e.g., keyword, web form, API).
OptInDateDateDate when the user opted in.
SourceNumberIdentifier for the source system (API, UI, import, etc.).
CreatedDateDateRecord creation timestamp.
ModifiedDateDateLast modification timestamp for the subscription event.

When and Why to Use SMSSubscriptionLog

When to Use?

  • When analyzing opt-in growth or opt-out trends.
  • To support compliance with data privacy and carrier rules.
  • When building re-engagement campaigns for lapsed subscribers.
  • To evaluate source performance (e.g., keyword vs API vs landing page).

Why Use It?

  • Detailed Audit History: Includes both opt-in and opt-out paths.
  • Segmentation Support: Build segments based on subscription activity.
  • Conversion Attribution: Identify which method or source drove sign-ups.

Sample SQL Queries

1. List All Subscribers Opted In

SELECT MobileNumber, OptInDate, SubscriptionDefinitionID 
FROM SMSSubscriptionLog 
WHERE OptInStatusID = 1 AND OptOutStatusID IS NULL

2. Find Subscribers Who Opted Out in Last 30 Days

SELECT MobileNumber, OptOutDate, OptOutMethodID 
FROM SMSSubscriptionLog 
WHERE OptOutDate >= DATEADD(day, -30, GETDATE())

3. Count Opt-ins and Opt-outs by SubscriptionDefinitionID

SELECT SubscriptionDefinitionID, 
       COUNT(CASE WHEN OptInStatusID = 1 THEN 1 END) AS TotalOptIns, 
       COUNT(CASE WHEN OptOutStatusID = 1 THEN 1 END) AS TotalOptOuts 
FROM SMSSubscriptionLog 
GROUP BY SubscriptionDefinitionID

4. Find Subscribers Opted In via a Specific Source

SELECT MobileNumber, Source, OptInDate 
FROM SMSSubscriptionLog 
WHERE Source = 3 AND OptInStatusID = 1

Assumes Source ‘3’ corresponds to a specific opt-in origin such as Web Form.

5. Recently Created Subscription Records

SELECT MobileNumber, CreatedDate, SubscriptionDefinitionID 
FROM SMSSubscriptionLog 
WHERE CreatedDate >= DATEADD(day, -15, GETDATE())

Conclusion

The SMSSubscriptionLog is a cornerstone for MobileConnect subscription analytics, allowing marketing and compliance teams to view every opt-in and opt-out action across all campaigns. Its rich historical data and detailed logging make it ideal for performance tracking, regulatory audits, and strategy refinement.

Use this in combination with _SMSMessageTracking, _UndeliverableSms, and SMSSendLog for a complete SMS channel analytics framework.

Related Posts