Salesforce Marketing Cloud Data Views: _UndeliverableSms (MobileConnect)

Posted on by admin

The _UndeliverableSms data view in Salesforce Marketing Cloud (SFMC) offers insight into SMS message delivery failures. It helps identify mobile numbers that are no longer reachable due to repeated bounces or being placed on hold by MobileConnect.

In this blog, we’ll break down the fields in _UndeliverableSms, explain its use cases, and provide SQL query examples to help you improve SMS deliverability and list hygiene.

What is the _UndeliverableSms Data View?

The _UndeliverableSms data view logs mobile numbers that failed to receive SMS messages due to repeated delivery issues. When a number reaches a bounce threshold, MobileConnect places it on hold, and this data view captures that state.

Key Benefits of _UndeliverableSms Data View

  • Improve SMS Deliverability: Monitor numbers that consistently fail.
  • List Hygiene: Suppress unreachable numbers to maintain campaign health.
  • Compliance & Cost Savings: Avoid sending to non-functional numbers, reducing costs and potential spam complaints.

Fields in _UndeliverableSms Data View & Their Uses

FieldTypeDescription & Use Case
MobileNumberPhoneThe mobile number of the subscriber. Used for filtering and suppression.
UndeliverableBooleanTrue (1) if the number is held and will no longer receive SMS messages.
BounceCountNumberThe total number of bounced SMS messages. Use to set bounce thresholds.
FirstBounceDateDateThe first time a bounce occurred. Helps in understanding deliverability trends.
HoldDateDateWhen the subscriber was officially marked undeliverable. Key for list exclusions.

When and Why to Use _UndeliverableSms Data View?

When to Use?

  • When managing SMS list hygiene.
  • To exclude held numbers from future SMS sends.
  • To track bounce rates over time.
  • When diagnosing low SMS engagement or deliverability issues.

Why Use It?

  • Avoid Sending to Held Numbers: Improve send success rates.
  • Protect Sender Reputation: Prevent repeated failed attempts.
  • Data-Driven Segmentation: Segment audiences based on bounce behavior.
  • Optimize Spend: Reduce unnecessary charges from failed sends.

10 Example Scenarios and Queries

1. List All Currently Undeliverable Numbers

SELECT MobileNumber, HoldDate 
FROM _UndeliverableSms 
WHERE Undeliverable = 1

Use Case: Suppress these numbers from your next SMS campaign.

2. Count of All Undeliverable Numbers

SELECT COUNT(*) AS TotalUndeliverable 
FROM _UndeliverableSms 
WHERE Undeliverable = 1

Use Case: Monitor overall SMS list health.

3. Mobile Numbers with More Than 3 Bounces

SELECT MobileNumber, BounceCount 
FROM _UndeliverableSms 
WHERE BounceCount > 3

Use Case: Identify highly problematic numbers.

4. Bounce Trends Over Time

SELECT CAST(FirstBounceDate AS DATE) AS BounceDate, COUNT(*) AS BounceCount 
FROM _UndeliverableSms 
GROUP BY CAST(FirstBounceDate AS DATE)

Use Case: Spot trends that may be linked to message content or carrier issues.

5. Recently Marked Undeliverable (Last 7 Days)

SELECT MobileNumber, HoldDate 
FROM _UndeliverableSms 
WHERE HoldDate >= DATEADD(day, -7, GETDATE())

Use Case: Analyze recent delivery failures.

6. Join With _SMSMessageTracking to Trace Bounce Sources

SELECT u.MobileNumber, t.MessageText, t.ActionDateTime 
FROM _UndeliverableSms u 
JOIN _SMSMessageTracking t 
ON u.MobileNumber = t.Mobile 
WHERE u.Undeliverable = 1

Use Case: Understand what messages triggered bounces.

7. Subscribers Who Became Undeliverable in a Campaign

SELECT MobileNumber, HoldDate 
FROM _UndeliverableSms 
WHERE HoldDate IS NOT NULL

Use Case: Audit bounce impact from a specific SMS campaign.

8. Daily Undeliverables Summary

SELECT CAST(HoldDate AS DATE) AS HoldDay, COUNT(*) AS Count 
FROM _UndeliverableSms 
GROUP BY CAST(HoldDate AS DATE)

Use Case: Track bounce volume per day.

9. Filter Undeliverables by Bounce Count Range

SELECT MobileNumber, BounceCount 
FROM _UndeliverableSms 
WHERE BounceCount BETWEEN 2 AND 5

Use Case: Segment for re-validation before suppression.

10. Cleanup Suggestions for Marketing Team

SELECT MobileNumber, BounceCount, FirstBounceDate, HoldDate 
FROM _UndeliverableSms 
WHERE Undeliverable = 1 
ORDER BY HoldDate DESC

Use Case: Provide marketing team a suppression list for cleanup.

Conclusion

The _UndeliverableSms data view in SFMC MobileConnect is essential for maintaining a clean and efficient SMS subscriber list. By leveraging bounce and hold data, marketers can make informed decisions about subscriber targeting, re-engagement, and compliance.

Pair it with _SMSSubscriptionLog and _SMSMessageTracking to build a complete view of your SMS deliverability landscape.

Related Posts