How to Access _MobileAddress and _MobileSubscription?

Question

I am working on managing MobileConnect Contacts and have discovered that the old _SubscriberSMS Data Extension has been deprecated. It appears that the relevant Data Extensions for this data are now _MobileAddress and _MobileSubscription.

From what I understand, _MobileAddress contains contact details, possibly using MobileNumber or SubscriberId as the primary key, while _MobileSubscription stores information about contact subscriptions. I was informed that _MobileAddress includes fields such as City, ContactID, CountryCode, CreatedBy, CreatedDate, FirstName, LastName, MobileNumber, ModifiedBy, ModifiedDate, State, Status, and ZipCode.
However, when I try to run a query like:

SELECT MobileNumber FROM _MobileAddress

I receive an error stating that the field does not exist. If I modify the Data Extension name to _MobileAddressX, I get an error indicating that the Data Extension itself does not exist, suggesting that _MobileAddress does exist but contains an unexpected set of fields.

I would like to know:

1. How can I retrieve a list of available fields for _MobileAddress and _MobileSubscription?
2. Can these Data Extensions be accessed via API, or are they only available through SQL queries?
3. What is the best approach for managing MobileConnect contacts and their subscriptions at the data level, considering the limitations of MobileConnect’s built-in functions?

Answer

The _MobileAddress and _MobileSubscription Data Extensions do exist, but all of their field names begin with an underscore (_). This causes issues when attempting to retrieve them using SELECT *, because user-created Data Extensions cannot have leading underscores in their field names.

To retrieve records from _MobileAddress, you need to explicitly alias each field in your query, as shown below:

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

Similarly, to retrieve records from _MobileSubscription, use this 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

These queries ensure that the system correctly recognizes the field names despite the leading underscores.

In response to the second question, _MobileAddress and _MobileSubscription can be accessed via SQL queries within Automation Studio and Query Activities. Additionally, they may be accessible via API, but this depends on your Marketing Cloud setup and permissions. Checking the available endpoints in the API documentation or using a tool like Postman to make API requests to retrieve metadata for these Data Extensions can confirm this.

For managing MobileConnect contacts and subscriptions efficiently, SQL queries provide a way to extract and manipulate data at the data level. However, for real-time operations, API access (if available) would be more practical. Since MobileConnect lacks built-in tools for extensive contact management, using a combination of Automation Studio, SQL queries, and possibly API calls would be the best approach.

Enroll for Salesforce Training Designed for Career Building Success

Our Salesforce Course is thoughtfully structured to provide a comprehensive grasp of the Salesforce platform, equipping you with essential skills to thrive in the CRM industry. The curriculum covers key modules such as Salesforce Admin, Developer, and AI, seamlessly integrating conceptual learning with practical application. Through hands-on projects and real-world case studies, you’ll build the expertise needed to tackle complex business challenges using Salesforce solutions. Our seasoned trainers ensure you gain both technical proficiency and industry-relevant knowledge to excel in the Salesforce ecosystem.

Beyond technical expertise, our Salesforce Training in Singapore offers personalized mentorship, certification exam support, and interview preparation to enhance your career prospects. You’ll have access to extensive study materials, real-world project exposure, and dedicated guidance throughout your learning path. By the completion of the course, you’ll be fully prepared for certification exams and possess the hands-on skills that employers highly value. Begin your Salesforce journey with us today and unlock a world of exciting career possibilities. Register for a Free Demo now!