Accessing _MobileAddress and _MobileSubscription Data Extensions

Question

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.

Answer 1

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

Answer 2

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.