SMS marketing is a powerful way to engage customers, but Oracle Responsys presents a major roadblock: when Automatic Failover for Transactional Messaging (AFTM) is enabled, every contact in the PRIMARY_LIST must have an email address.
For businesses running SMS campaigns through web forms or keyword sign-ups, this requirement creates a serious challenge. Without an email, contacts fail to process, opt-ins get lost, and customer engagement suffers. Worse, it can lead to compliance risks and data inconsistencies that disrupt marketing effectiveness.
So, how do you successfully onboard SMS-only subscribers without breaking Responsys’ email requirement?
The solution: a two-list approach that captures SMS contacts in a SECONDARY_LIST, generates a placeholder email, and seamlessly migrates them into the PRIMARY_LIST—all while ensuring compliance and preventing opt-in failures.
In this article, we’ll break down how this method works, the technical setup, and why it ensures a seamless, compliant, and scalable SMS opt-in process.
Overcoming Mandatory Email Requirements with a Secondary List
In Oracle Responsys, SMS programs often require managing contacts without email addresses. However, if Automatic Failover for Transactional Messaging (AFTM) is enabled in your PRIMARY_LIST, an email address becomes mandatory for all records. This creates a challenge for handling SMS-only contacts, particularly those signing up via web forms or SMS keywords.
To work around this limitation, we use a two-list approach, where:
- SECONDARY_LIST temporarily holds SMS sign-ups that do not provide an email.
- A scheduled Connect Job moves records from SECONDARY_LIST to PRIMARY_LIST while generating a “fake” email address for SMS-only contacts.
By default, Connect Jobs in Responsys typically run on a daily schedule. However, if approved by Oracle (via your account manager), they can be configured to run hourly by requesting this change through Oracle Support.
Why Two Lists?
PRIMARY_LIST (Main List with AFTM Enabled)
- Used for all email and SMS communications.
- Requires an email address for every contact due to AFTM enforcement.
- Only records with valid emails (real or generated) can exist here.
SECONDARY_LIST (Temporary Holding List for SMS Sign-Ups)
- Stores SMS-only contacts who sign up via web forms or text keywords.
- Does not require an email address.
- Serves as an intermediate step before migrating contacts into PRIMARY_LIST.
SMS Opt-In Flow with Two Lists
- User signs up for SMS marketing via:
- A web form (without an email field).
- A JOIN keyword sent to the SMS short code.
- The sign-up creates a record in SECONDARY_LIST, capturing:
- MOBILE_NUMBER_
- MOBILE_PERMISSION_STATUS_ (opt-in status)
- MOBILE_PERMISSION_DATE_ (timestamp of opt-in)
- MOBILE_COUNTRY_
- A Connect Job runs (typically daily, or hourly if Oracle Support approves it) to export all records from SECONDARY_LIST to a flat file stored on the Responsys FTP server.
- The flat file includes a generated “fake” email address for SMS-only contacts.
- Example format: [email protected]
- Another Connect Job imports the processed file back into PRIMARY_LIST, with the following updates:
- EMAIL_ADDRESS_ (fake email added if missing).
- EMAIL_PERMISSION_STATUS_ = ‘O’ (to mark SMS-only contacts as email opt-out).
- MOBILE_PERMISSION_STATUS_ (maintains SMS opt-in).
Flat File Exchange via Responsys FTP
Both Connect Jobs use flat files to transfer data between the two lists. The process works as follows:
- Export Job:
- Runs on a daily or hourly schedule.
- Extracts records from SECONDARY_LIST.
- Saves the file in Responsys FTP under a designated folder.
- Import Job:
- Picks up the flat file from Responsys FTP. Processes and imports the records into PRIMARY_LIST. Updates mobile opt-in records while ensuring compliance with AFTM requirements.
SQL Query for Connect Job Export (Moving SMS Contacts to PRIMARY_LIST)
SELECT a.MOBILE_NUMBER_, a.MOBILE_COUNTRY_, a.MOBILE_PERMISSION_STATUS_, a.MOBILE_PERMISSION_DATE_, a.MOBILE_PERMISSION_REASON_, a.MOBILE_NUMBER_ || ‘@fakesmsdomain.com’ AS EMAIL_ADDRESS_, ‘O’ AS EMAIL_PERMISSION_STATUS_, a.FIRST_NAME, a.LAST_NAME, a.MODIFIED_DATE_ FROM $A$ a;
How This Works:
- MOBILE_NUMBER_ || ‘@fakesmsdomain.com’ creates the fake email dynamically.
- ‘O’ AS EMAIL_PERMISSION_STATUS_ ensures no marketing emails are sent to fake email addresses.
- Only necessary fields are exported to avoid unnecessary data load.
Import Process into PRIMARY_LIST
Data Mapping During Import:
| Field in Import File | Mapped Field in PRIMARY_LIST | Transformation |
| MOBILE_NUMBER_ | MOBILE_NUMBER_ | No changes |
| EMAIL_ADDRESS_ | EMAIL_ADDRESS_ | Uses fake email if missing |
| EMAIL_PERMISSION_STATUS_ | EMAIL_PERMISSION_STATUS_ | Always set to ‘O’ (opted-out) |
| MOBILE_PERMISSION_STATUS_ | MOBILE_PERMISSION_STATUS_ | Retains original opt-in status |
| MOBILE_PERMISSION_DATE_ | MOBILE_PERMISSION_DATE_ | Retains timestamp |
| MOBILE_COUNTRY_ | MOBILE_COUNTRY_ | No changes |
Key Benefits of This Approach
✅ Ensures SMS Sign-Ups Are Captured Even Without Emails
Using a temporary list avoids Responsys’ email requirement issue.
✅ Maintains AFTM Compliance in PRIMARY_LIST
Every record has a valid email, preventing system failures.
✅ Prevents Email Marketing to SMS-Only Users
The email permission status is set to “O”, ensuring no emails are sent to fake email addresses.
✅ Flat File Exchange via Responsys FTP
Data is transferred securely using export and import jobs via the Responsys FTP server.
✅ Automated Data Synchronization
The hourly/daily Connect Job ensures seamless movement of contacts from SECONDARY_LIST to PRIMARY_LIST.
Conclusion
By using a two-list approach, we successfully handle SMS opt-ins while working around Responsys’ mandatory email requirement in PRIMARY_LIST due to AFTM. Automating the migration of SMS-only contacts from SECONDARY_LIST ensures compliance, data integrity, and seamless customer communication, all without manual intervention.
Additionally, since Connect Jobs typically run daily, you can request Oracle Support approval to run them hourly for more frequent updates, reducing potential delays in moving SMS opt-ins.
This method provides a scalable, future proof solution for managing SMS subscriptions efficiently within Responsys.