6

In trying to develop a process to deal with MobileConnect Contacts, I have come to understand that the old SMS DE _SubscriberSMS has been deprecated and that the new DEs that will contain this information are: _MobileAddress and _MobileSubscription. I presume that these DEs contain information about the Contact (with a primary key of mobile number or SubscriberId) and the contacts' subscriptions, respectively.

The information that was given to me (by ET) about _MobileAddress was that it contained the following fields:

City  
ContactID  
CountryCode  
CreatedBy  
CreatedDate  
FirstName  
LastName  
MobileNumber  
ModifiedBy  
ModifiedDate  
State  
Status  
ZipCode 

However, when I try to set up a query like SELECT MobileNumber FROM _MobileAddress, I am told that that field does not exist. If I modify the DE to something like _MobileAddressX it tells me that DE doesn't exist, so presumably _MobileAddress exists and has an unknown set of fields.

Can anyone tell me how to get more information about _MobileAddress and _MobileSubscription?

What fields do they have?

Can I access it via API or only through Queries?

Since MobileConnect does not seem to support a series of functions for maintaining Mobile Contacts, it would be infinitely useful to be able to access those contacts and their subscriptions at the data level.

8

So, the field names are correct (though not a complete list). They contain an underscore before each of them in the Data Extension. This makes things difficult when pulling the fields out using SELECT * because fields in [user created] Data Extensions cannot have a leading underscore.

So if you want to pull all the records from _MobileAddress you will need the following query:

SELECT _CarrierID AS CarrierID,
_Channel AS Channel,
_City AS City,
_ContactID AS ContactID,
_CountryCode AS CountryCode,
_CreatedBy AS CreatedBy,
_CreatedDate AS CreatedDate,
_FirstName AS FirstName,
_IsHonorDST AS IsHonorDST,
_LastName AS LastName,
_MobileNumber AS MobileNumber,
_ModifiedBy AS ModifiedBy,
_ModifiedDate AS ModifiedDate,
_Priority AS Priority,
_Source AS Source,
_SourceObjectID AS SourceObjectID,
_State AS State,
_Status AS Status,
_UTCOffset AS UTCOffset,
_ZipCode AS ZipCode
FROM _MobileAddress

And all the records from _MobileSubscription can be pulled with the following query:

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
  • By any chance do you know what each value of the IDs mean e.g. OptInStatusID o OptInMethodID ? the results are returning a numberic value, but I'd like to know what the actual value meaning/name is. Cheers – Gravitai Jun 8 '17 at 23:42
  • @Macca _MobileAddress._Status 1 = active -1 = unspecified 2 = bounced 3 = held 4 = unsubscribed _MobileSubscription._OptOutStatusID 0 = opted in 1 = opted out _MobileSubscription._OptInStatusID 0 = opted out 1 = Opt In Pending 2 = opted in – sforce Nov 9 '17 at 2:08
  • 1
    Do you know if this view can provide ContactKey? – Unais Komath Feb 1 '18 at 14:23
4

In case you're wondering about the data types and such:

_MobileAddress

| Field Name      | Data Type (Length) | Required (Y/N) | Data Default |
| :-              | :-                 | :-:            | :-           |
| _ContactID      | Text               | Y              |              |
| _MobileNumber   | Text(15)           | Y              |              |
| _Status         | Text               | N              |              |
| _Source         | Text               | N              |              |
| _SourceObjectId | Text(200)          | N              |              |
| _Priority       | Text               | N              | 1            |
| _Channel        | Text(20)           | N              |              |
| _CarrierID      | Text               | Y              | 0            |
| _CountryCode    | Text(2)            | Y              |              |
| _CreatedDate    | Date               | Y              | GETDATE()    |
| _CreatedBy      | Text               | N              |              |
| _ModifiedDate   | Date               | Y              | GETDATE()    |
| _ModifiedBy     | Text               | N              |              |
| _City           | Text(200)          | N              |              |
| _State          | Text(200)          | N              |              |
| _ZipCode        | Text(20)           | N              |              |
| _FirstName      | Text(100)          | N              |              |
| _LastName       | Text(100)          | N              |              |
| _UTCOffset      | Decimal(4,2)       | N              | 0            |
| _IsHonorDST     | Boolean            | N              | false        |

_MobileSubscription

| Field Name                | Data Type (Length) | Required (Y/N) | Data Default |
| :-                        | :-                 | :-:            | :-           |
| _SubscriptionDefinitionID | Text(200)          | Y              |              |
| _MobileNumber             | Text(15)           | Y              |              |
| _OptOutStatusID           | Text               | N              |              |
| _OptOutMethodID           | Text               | N              |              |
| _OptOutDate               | Date               | N              |              |
| _OptInStatusID            | Text               | Y              |              |
| _OptInMethodID            | Text               | N              |              |
| _OptInDate                | Date               | N              |              |
| _Source                   | Text               | N              |              |
| _CreatedDate              | Date               | Y              | GETDATE()    |
| _CreatedBy                | Text               | N              |              |
| _ModifiedDate             | Date               | Y              | GETDATE()    |
| _ModifiedBy               | Text               | N              |              |

I did an SOAP API retrieve on the DataExtensionField Object to get this info.

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.