SMS Reporting with _SMSMessageTracking

SMS tracking basics

_SMSMessageTracking stores MobileConnect MT/MO message events. Unlike email tracking views, there is no six-month purge — but unbounded queries still time out.

Always anchor on CreateDateTime or ActionDateTime. Join JBDefinitionID to _Journey.VersionID for Journey SMS attribution.

Delivery rate by keyword

SELECT
  SharedKeyword,
  COUNT(*) AS Messages,
  SUM(CASE WHEN Delivered = 1 THEN 1 ELSE 0 END) AS Delivered,
  SUM(CASE WHEN Undelivered = 1 THEN 1 ELSE 0 END) AS Undelivered
FROM _SMSMessageTracking
WHERE CreateDateTime >= DATEADD(day, -30, GETDATE())
  AND Outbound = 1
  AND IsTest = 0
GROUP BY SharedKeyword
ORDER BY Messages DESC

Practitioner tips

• Mobile is stored without a + prefix — normalize E.164 numbers before joining to external lists.
• Pair _UndeliverableSMS when diagnosing repeated failures on the same Mobile number.
• SubscriptionDefinitionID on _SMSSubscriptionLog joins to KeywordID for opt-in history.