Mobile Connect STOP keyword

Question

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?

Answer 1

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

SELECT 
a._MobileNumber AS MobileNumber, 
a._MobileSubscriptionID AS MobileSubscriptionID, 
a._OptOutDate AS OptOutDate,
b.EmailAddress,
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:

SELECT 
_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