I would like to understand better the STOP keyword functionality in the Mobile Connect Feature when I subscriber sends an SMS with this keyword inside. What field and system table will be updated (e.g. Mobile Connect Demographics, Mobile Connect Subscriptions) ? Could I export the unsubscribed contacts for each keyword in a csv file?

1 Answer 1


Here is the query we are using to retrieve most recent unsubscribes from our MobileConnect

a._MobileNumber AS MobileNumber, 
a._MobileSubscriptionID AS MobileSubscriptionID, 
a._OptOutDate AS OptOutDate,
b.Contact_Key AS ContactKey
FROM _MobileSubscription a
JOIN Contacts b ON a._MobileNumber = b.PhoneNumber
JOIN Permissions c ON b.Contact_Key = c.Contact_Key
WHERE _OptOutStatusID = 1
AND c.PermissionSMS = 'TRUE'
AND DateDiff(day,PermissionSMSJoinDate,_OptOutDate) > 0

I hope this will lead you in the right direction.

All the fields in _MobileSubscription can be seen here:

_CreatedBy AS CreatedBy,
_OptOutMethodID AS OptOutMethodID,
_MobileNumber AS MobileNumber,
_OptInDate AS OptInDate,
_Source AS Source,
_OptOutStatusID AS OptOutStatusID,
_OptOutDate AS OptOutDate,
_ModifiedBy AS ModifiedBy,
_SourceObjectId AS SourceObjectId,
_SubscriptionDefinitionID AS SubscriptionDefinitionID,
_CreatedDate AS CreatedDate,
_OptInStatusID AS OptInStatusID,
_OptInMethodID AS OptInMethodID,
_ModifiedDate AS ModifiedDate
FROM _MobileSubscription
  • Hi @Lukas L, i followed your suggestion and i retrieved many records into the data extension, but i would like to extract other information (e.g. keyword, OptOutMethod). Do you have the correct field name used in the _MobileSubscription system table? And have you created another query using Mobile Demographic because the system gave back the error message "_MobileDemographic is not a known data extension or system data view." ?
    – cmbob
    Nov 30, 2016 at 15:46
  • Hi robertoc - I have added the full list of fields to my answer. OptOutMethodID is there too Nov 30, 2016 at 16:04
  • Thank you very much @Lukas L. do you have also the fields of Mobile Demographics?
    – cmbob
    Nov 30, 2016 at 16:19
  • No, sorry @robertoc - this I haven't Dec 1, 2016 at 9:59
  • Thanks for this useful insight - it has really helped me. Does anyone know what each of the possible values mean for OptOutMethodID OptOutStatusID The results of my query return an numeric value - it would be really useful to have some documentation as to what these values mean.
    – Gravitai
    Jun 8, 2017 at 23:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.