How to track mobile numbers who revert with START keyword to re-optin

Question

can someone tell me how to track the 'Mobile-Optin' Keyword using dataview in marketing cloud? When a person receives the SMS, he will revert with 'STOP' to optout (which I'm able to track using _smsmessagetracking dataview.) Similarly I have set up a Mobile optin message for 'START' keyword to re-optin for the subscribers who have reverted with 'STOP'. How do I track these phone numbers who has reverted with 'START' using dataview. When I run the below query, it's not showing any data.

Select *
from ENT._smsmessagetracking 
where  
MessageText in ('START','STOP') 

Answer 1

Assuming you are using 'START' as the opt-in keyword and 'STOP' as the opt-out keyword, and the customers did receive and send text with those keywords like you mentioned above.

I would leverage the _MobileSubscription data view to know which keyword status for each mobile number.

Note: "This Data View is officially unsupported by Salesforce. Marketing Cloud still fills it with data, but there is no guarantee of how long it will persist or whether it will be correct." Reference

But first, you need to get the "SubscriptionDefinitionID" for each keyword, I would use _SMSMessageTracking data view since the customers did receive and send text with those keywords.

SELECT DISTINCT
KeywordID,
SharedKeyword
FROM
_SMSMessageTracking
WHERE
SharedKeyword = 'START' OR
SharedKeyword = 'STOP'

The "KeywordID" field in this data view equals with "SubscriptionDefinitionID" in _MobileSubscription so you can use value of this field to map with the corresponding "_SubscriptionDefinitionID" field below

SELECT
CASE
    WHEN mobileSub._SubscriptionDefinitionID = 'KeywordID for START keyword' THEN 'JCC'
    WHEN mobileSub._SubscriptionDefinitionID = 'KeywordID for STOP keyword' THEN 'JCCSTOP'
END AS Keyword
    , mobileSub._MobileNumber             AS MobileNumber
    , mobileSub._OptOutStatusID           AS OptOutStatusID
    , mobileSub._OptOutMethodID           AS OptOutMethodID
    , mobileSub._OptOutDate               AS OptOutDate
    , mobileSub._OptInStatusID            AS OptInStatusID
    , mobileSub._OptInMethodID            AS OptInMethodID
    , mobileSub._OptInDate                AS OptInDate
    , mobileSub._Source                   AS Source
    , mobileSub._CreatedDate              AS CreatedDate
    , mobileSub._CreatedBy                AS CreatedBy
    , mobileSub._ModifiedDate             AS ModifiedDate
    , mobileSub._ModifiedBy               AS ModifiedBy
FROM _MobileSubscription AS mobileSub
WHERE mobileSub._SubscriptionDefinitionID IN ('KeywordID for START keyword', 'KeywordID for STOP keyword')

You can track many useful information in the result, like each mobile number opt-in and opt-out date

Note 2: Subscription Status here can be understood by numeric values

***Opt-in***
OptInStatusID: 2
OptOutStatusID: 0

***Opt-out***
OptInStatusID: 0
OptOutStatusID: 1

Note 3: I don't think the "stop" keyword will show up in the results, normally when a person sends a message with a stop keyword, it will opt-out other opt-in keywords. But you should give a try and figure out by yourself.