Allowing for multiple formats of mobile numbers

Question

I'm trying to perform a lookup to retrieve a records "MemberNumber" off their mobile number.

The data i'm dealing with has up to three different formats of numbers

either starting with a 0, 61 or no 0

Which means three lookups and the use of Substring and concat.

So to get around this I formatted the below:

    %%[ 

    VAR @KW, @LogDate, @MN, @VerificationCode, @MobileNumber1, @MobileNumber2, @MobileNumber3, @MemberNumber1, @MemberNumber2, @MemberNumber3

    SET @KW=Uppercase( [MSG(0).NOUNS] ) 
    SET @LogDate = Format(SystemDateToLocalDate(NOW()),"yyyy-MM-dd HH:mm:ss")
    SET @MN = MobileNumber
    SET @MobileNumber1 = MobileNumber
    SET @MobileNumber2 = Substring(MobileNumber,2,12)
    SET @MobileNumber3 = Concat('0', Substring(MobileNumber))
    SET @MemberNumber1 = Lookup("Main_SMS_Database","MemberNumber","MobileNumber1", @MN, "CommunicationCode", "SMS0004")
    SET @MemberNumber2 = Lookup("Main_SMS_Database","MemberNumber","MobileNumber2", @MN, "CommunicationCode", "SMS0004")
    SET @MemberNumber3 = Lookup("Main_SMS_Database","MemberNumber","MobileNumber3", @MN, "CommunicationCode", "SMS0004")


    IF @KW == "STOP" THEN InsertData("STOP_SMS_Database","Mobile", @MN, "Keyword", @KW, "EventDate", @LogDate) 
    ]%% 
    Thank you for opting out of SMS.
    %%[ 

    ELSEIF @KW == "YES" AND Not Empty (@MemberNumber1)  THEN InsertData("Reponses_DE","MobileNumber", @MobileNumber, "MemberNumber", @MemberNumber1, "MessageText", @KW, "EventDate", @LogDate) 
    ]%% 
    We're passing on your request
    %%[ 

    ELSEIF @KW == "YES" AND Not Empty (@MemberNumber2)  THEN InsertData("Reponses_DE","MobileNumber", @MobileNumber, "MemberNumber", @MemberNumber2, "MessageText", @KW, "EventDate", @LogDate) 
    ]%% 
    We're passing on your request
    %%[ 

    ELSEIF @KW == "YES" AND Not Empty (@MemberNumber3)  THEN InsertData("Reponses_DE","MobileNumber", @MobileNumber, "MemberNumber", @MemberNumber3, "MessageText", @KW, "EventDate", @LogDate) 
    ]%% 
    We're passing on your request
    %%[ 

    ELSE
    ]%% 
    We didn’t quite understand that. If you’d like to request a call back, reply ‘YES’. 
    %%[ 

    ENDIF
    ]%%

However it's returning a error no matter what I try.

Can anyone tell what I've done wrong in the above?

Any feedback would be very much appreciated.

Thanks,

Luke

Answer 1

Could be that you are using the wrong system personalization string for Mobile Number. The default value is "MOBILE_NUMBER" not "MobileNumber" as you have in your code.

Answer 2

Thanks everyone for your assistance. I now have this working using the below code.

This now enables you to send a SMS responses and insert that respondents record into a data extension.

I've done this using a lookup on each records unique "MemberNumber" and their "MobileNumber"

This is more specific to Australia mobile numbers but it allows for multiple formats of mobile numbers that are fed in.

This includes:

0400112233 400112233 61400112233

%%[ 

VAR @KW, @LogDate, @MN, @VerificationCode, @MobileNumber1, @MobileNumber2, @MobileNumber3, @MemberNumber1, @MemberNumber2, @MemberNumber3

SET @KW=Uppercase( [MSG(0).NOUNS] ) 
SET @LogDate = Format(SystemDateToLocalDate(NOW()),"yyyy-MM-dd HH:mm:ss")
SET @MN = MobileNumber
SET @MobileNumber1 = MobileNumber
SET @MobileNumber2 = Substring(MobileNumber,3,12)
SET @MobileNumber3 = Concat('0', @MobileNumber2)
SET @MemberNumber1 = Lookup("PROD_SMS_Campaign_Records","MemberNumber","MobileNumber", @MobileNumber1, "Campaigncode", "SMS0004")
SET @MemberNumber2 = Lookup("PROD_SMS_Campaign_Records","MemberNumber","MobileNumber", @MobileNumber2, "Campaigncode", "SMS0004")
SET @MemberNumber3 = Lookup("PROD_SMS_Campaign_Records","MemberNumber","MobileNumber", @MobileNumber3, "Campaigncode", "SMS0004")


IF @KW == "STOP" THEN InsertData("STOP_Records_DE","Mobile", @MN, "Keyword", @KW, "EventDate", @LogDate) 
]%% 
Thank you for opting out of SMS.
%%[ 

ELSEIF @KW == "YES" AND Not Empty (@MemberNumber1)  THEN InsertData("Responses_DE","MobileNumber", MobileNumber, "MemberNumber", @MemberNumber1, "MessageText", @KW, "EventDate", @LogDate) 
]%% 
Reponse
%%[ 

ELSEIF @KW == "YES" AND Not Empty (@MemberNumber2)  THEN InsertData("Responses_DE","MobileNumber", MobileNumber, "MemberNumber", @MemberNumber2, "MessageText", @KW, "EventDate", @LogDate) 
]%% 
Reponse
%%[ 

ELSEIF @KW == "YES" AND Not Empty (@MemberNumber3)  THEN InsertData("Responses_DE","MobileNumber", MobileNumber, "MemberNumber", @MemberNumber3, "MessageText", @KW, "EventDate", @LogDate) 
]%% 
Reponse
%%[ 

ELSE
]%% 
We didn’t quite understand that. If you’d like to request a call back, reply ‘YES’. 
%%[ 

ENDIF
]%%

If anyone has any comments or can see room for improvement please let me know!