SMS Weather Report Solution Guide

About This Guide

This document provides conceptual, scenario, and procedural information about how to use ExactTarget features to create a series of SMS messages in response to MT (mobile-terminated) requests. Based on the information sent via SMS message, an ExactTarget client can set up triggered responses with individual content sections. The steps in this document demonstrate how to use features in ExactTarget to provide a weather report to subscribers on the day of an outdoor event they are scheduled to attend. These subscribers are not joined to your main mailing list as part of this scenario.

Audience

This document is intended for ExactTarget users responsible for creating SMS messages for their subscribers. Users must have an ExactTarget account with SMS enabled to use this solution guide.

Scenario

Northern Trails is working with an area radio station to sponsor a series of picnics designed to show off their new line of rugged outdoor athletic gear. Knowing that the turn-out will be low if there's inclement weather, Northern Trails wants to alert those who have accepted invitations to come to the picnic if the festivities are on or off. They invite those attending to send in SMS messages to the Northern Trails short code with the keyword WEATHER and the date they plan on attending as DD/MM/YY.

This scenario involves sending SMS messages to recipients who have opted only to receive information regarding the weather report. These recipients have not agreed to receive SMS messages through the double-opt-in procedure recommended by ExactTarget and the Mobile Marketing Association. Therefore, these recipients should receive only those messages related to their initial request.

How the SMS Weather Report Solution Works

First, Northern Trails creates a data extension to hold the incoming information from the MO (mobile-oriented) SMS messages. They enter the following information for the data extension:

• Name: WeatherAlertSubscribers
• External Key: None
• Description: Subscribers for Picnic Weather Alerts
• Phone Number Fields
  • Name: Phone
  • Data: Phone
  • Length: 15
• Date Fields
  • Name: Date
  • Type: Text
  • Length: 50

Northern Trails also creates a similar data extension called TodaysList with the same attributes as WeatherAlertSubscribers.

Next, Northern Trails creates an SMS voting action with the following information:

• Name: WeatherAlert
• Verb: WEATHER
• Type: Vote
• Action Settings: Change the dropdown menu to the Weather Alert Subscribers data extension.
• Generic Error Message: We didn't understand your message. Please try again.
• Invalid Category Message: That was an invalid date. Please try again.
• Mobile Phone Column: Phone
• Success Message: Thanks for your message! We'll send you a weather confirmation on the date of the picnic.
• Valid Categories: Blank
• Vote Category Column: Date

From here, the data extension stores all messages with the keyword WEATHER in the data extension, capturing the invitee's mobile number and their chosen data. Each day of the picnic, Northern Trails looks out the window, checks the weather report, and decides whether to hold the picnic. To filter out the invitees who want information on today's picnic, they log into their ExactTarget account and follow these steps:

SELECT
    CASE
        WHEN s.SubscriberKey is null then
            '1-' + w._CustomObjectKey
        ELSE
            s.SubscriberKey
    END AS SubscriberKey, w.ShowDate, w.MobileNumber
FROM WeatherAlertSubscribers w
LEFT OUTER JOIN Subscribers s
ON w.MobileNumber = s.MobileNumber
WHERE w.ShowDate = GETDATE()
ORDER BY SubscriberKey ASC

1. Click on the Interactions tab.
2. Click on the Activities tab.
3. Click on the Queries tab.
4. Click Create.
5. Enter the the name and description for the query.
6. Enter the following query in the Query field. This query retrieves existing subscriber keys and assigns new keys to those who have not opted in to the mailing list previously.
7. Select TodaysList in the Select Data Extension to Populate field.
8. Select Update in the Update Type dropdown menu.
9. Click Save.
10. Select the query and click Start. This runs the query and populates the data extension with the subscribers for that day.

Once the TodaysList data extension has been populated, Northern Trails creates a triggered send that sends the verdict on that day's picnic to the interested invitees. Every time the query is run, it refreshes the TodaysList data extension with the current invitees.