0

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?

4

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
  • 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 '16 at 15:46
  • Hi robertoc - I have added the full list of fields to my answer. OptOutMethodID is there too – Lukas Lunow Nov 30 '16 at 16:04
  • Thank you very much @Lukas L. do you have also the fields of Mobile Demographics? – cmbob Nov 30 '16 at 16:19
  • No, sorry @robertoc - this I haven't – Lukas Lunow Dec 1 '16 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 '17 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.