Salesforce Marketing Cloud Data Views: _SMSSubscriptionLog (MobileConnect)

Posted on by admin

The _SMSSubscriptionLog data view in Salesforce Marketing Cloud (SFMC) provides historical insight into SMS keyword subscriptions, opt-ins, and opt-outs for MobileConnect. Unlike many other data views, this one maintains data beyond 6 months, making it ideal for long-term tracking and compliance reporting.

This blog details each field within the _SMSSubscriptionLog data view, outlines common use cases, and provides sample SQL queries to help you track subscriber lifecycle events and keyword performance.

What is the _SMSSubscriptionLog Data View?

The _SMSSubscriptionLog data view captures logs of every subscription or unsubscription event tied to a short code or long code. Whether you’re using a private or shared code, this data view helps trace keyword engagement across time.

Key Benefits of _SMSSubscriptionLog Data View

  • Historical Opt-In/Opt-Out Tracking: Go beyond 6 months of data retention.
  • Compliance Auditing: Prove explicit opt-in or opt-out timestamps.
  • Keyword-Level Insights: Track which keywords gained or lost subscribers.
  • Subscription Journey Monitoring: Analyze how and when users joined or exited your SMS program.

Fields in _SMSSubscriptionLog Data View & Their Uses

FieldTypeDescription & Use Case
MobileNumberPhoneThe mobile number of the subscriber.
SubscriberKeyTextExternal ID for the subscriber. Used for joining with other views.
LogDateDateTimestamp when the log entry was created. Supports full subscription journey tracing.
MobileSubscriptionIDNumberUnique ID of the subscription record.
SubscriptionDefinitionIDTextKeyword ID used for subscription. Helps identify which campaign the user joined.
OptOutStatusIDNumberIndicates opt-out status (see system reference table).
OptOutMethodIDNumberHow the user opted out (e.g., Reply STOP, UI, API). Reference the opt-out method key.
OptOutDateDateDate when the subscriber opted out.
OptInStatusIDNumberIndicates opt-in status (see key values).
OptInMethodIDNumberHow the subscriber opted in (e.g., keyword, web form, API).
OptInDateDateTimestamp of the opt-in event.
SourceNumberIndicates where the subscription originated.
CreatedDateDateWhen the subscription record was created.
ModifiedDateDateLast modification timestamp for the record.

Note: OptIn/OptOut status and method IDs can be mapped using Salesforce documentation to interpret user behavior.

When and Why to Use _SMSSubscriptionLog Data View?

When to Use?

  • To audit SMS opt-in and opt-out events for compliance.
  • When building keyword performance dashboards.
  • To determine subscription duration for retention analysis.
  • To analyze how users opt in (e.g., via text, form, or API).

Why Use It?

  • Historical Accuracy: Tracks subscription behavior long-term.
  • Legal Safeguards: Confirm consent or unsubscribes for compliance.
  • Behavior Analysis: Understand subscription sources and user intent.
  • Campaign Optimization: Determine which opt-in methods yield better results.

10 Example Scenarios and Queries

1. List Subscribers Who Opted In in the Last 30 Days

SELECT MobileNumber, SubscriberKey, OptInDate, SubscriptionDefinitionID 
FROM _SMSSubscriptionLog 
WHERE OptInDate >= DATEADD(day, -30, GETDATE())

Use Case: Target new SMS subscribers.

2. Retrieve All Opt-Out Events with Method ID

SELECT MobileNumber, OptOutDate, OptOutMethodID 
FROM _SMSSubscriptionLog 
WHERE OptOutDate IS NOT NULL

Use Case: Understand why and how users opted out.

3. Find Active Subscribers to a Specific Keyword

SELECT MobileNumber, OptInDate 
FROM _SMSSubscriptionLog 
WHERE SubscriptionDefinitionID = 'KEYWORD2024' 
AND OptOutDate IS NULL

Use Case: Audit keyword audience size.

4. Compare Opt-In vs. Opt-Out Counts

SELECT 'OptedIn' AS Type, COUNT(*) AS Total 
FROM _SMSSubscriptionLog 
WHERE OptInDate IS NOT NULL 
UNION ALL 
SELECT 'OptedOut' AS Type, COUNT(*) 
FROM _SMSSubscriptionLog 
WHERE OptOutDate IS NOT NULL

Use Case: Report SMS program growth or decline.

5. Daily Subscription Volume by Keyword

SELECT CAST(OptInDate AS DATE) AS Day, SubscriptionDefinitionID, COUNT(*) AS OptIns 
FROM _SMSSubscriptionLog 
WHERE OptInDate IS NOT NULL 
GROUP BY CAST(OptInDate AS DATE), SubscriptionDefinitionID

Use Case: Identify peak subscription days.

6. Join with _SMSMessageTracking for Journey Attribution

SELECT s.SubscriberKey, s.OptInDate, t.MessageText 
FROM _SMSSubscriptionLog s 
JOIN _SMSMessageTracking t 
ON s.SubscriberKey = t.SubscriberKey

Use Case: Attribute SMS opt-in to specific message content.

7. Opt-Out Rate per Keyword

SELECT SubscriptionDefinitionID, COUNT(*) AS OptOuts 
FROM _SMSSubscriptionLog 
WHERE OptOutDate IS NOT NULL 
GROUP BY SubscriptionDefinitionID

Use Case: Find keywords with high opt-out rates.

8. Retrieve All Opted-In Subscribers by Source

SELECT Source, COUNT(*) AS TotalOptIns 
FROM _SMSSubscriptionLog 
WHERE OptInDate IS NOT NULL 
GROUP BY Source

Use Case: Discover which source channels perform best.

9. Opted-In Subscribers Modified Recently

SELECT MobileNumber, ModifiedDate 
FROM _SMSSubscriptionLog 
WHERE OptInDate IS NOT NULL 
AND ModifiedDate >= DATEADD(day, -14, GETDATE())

Use Case: Track recent changes in opt-in statuses.

10. Subscribers Who Re-Subscribed

SELECT MobileNumber, COUNT(*) AS Subscriptions 
FROM _SMSSubscriptionLog 
GROUP BY MobileNumber 
HAVING COUNT(*) > 1

Use Case: Identify repeat SMS subscribers.

Conclusion

The _SMSSubscriptionLog data view is one of the most valuable tools for SMS lifecycle management in Marketing Cloud. It gives long-term visibility into opt-in and opt-out patterns, keyword performance, and user subscription history—crucial for compliance, retention, and marketing optimization.

Pair it with _SMSMessageTracking, _Subscribers, and campaign-level reports for end-to-end MobileConnect analytics.

Related Posts