Scenarios

1. Scenario 1: Concerts in Your Area
1. 1.1. Data Extensions and Relationships
2. 1.2. AMPscript
3. 1.3. Output
2. Scenario 2: Suggested Car Listings
1. 2.1. Data Extensions and Relationships
2. 2.2. AMPscript
3. Output

This section contains examples that demonstrate using AMPscript and functions.

Scenario 1: Concerts in Your Area

In this example, subscribers can sign up to receive notification of upcoming concerts in their area for their preferred genres of music. You can access text files containing the sample code used in this scenario at the following location:

http://www.memberlandingpages.com/he...InYourArea.zip

Data Extensions and Relationships

The marketer creates a data extension to contain information about upcoming concerts. Each concert has an associated genre, range of ticket prices, date, artist, venue, and city. Later, the marketer will create AMPscript to include this information in an email based on the subscriber's city and genre preferences.

The marketer creates a webpage where subscribers indicate their preferred music genres. The preferences are stored in the Customer_Genre_Preferences data extension.

The Subscriber table contains the following data:

Email Address	Customer ID	First Name	Last Name	City
ettest201@example.com	201	John201	Galt	Indianapolis
ettest202@example.com	202	Dominque202	Francon	Chicago

The Customer_Genre_Preferences data extension contains the following data:

Customer ID	Genre
201	Rock
201	Alt Country
201	Indie
202	Pop
202	Folk
202	Rock

The Upcoming_Shows data extension contains the following data:

Concert ID	Genre	Low Price	High Price	Show Date	Artist	Venue	City
101	Rock	35	45	10/27/2007	Grace Potter and the Nocturnals	Alabama Theater	Indianapolis
106	Indie	15	37.5	10/6/2007	Steel Train	Chain Reaction	New York
107	Indie	25	40	11/15/2007	The Avett Brothers	Joshua Tree Roots Music Festival	Chicago
108	Alt Country	25	47.5	10/27/2007	Dan Bern	Monterey Fairgrounds	Indianapolis
109	Indie	35	50	10/30/2007	Lucero	Brick by Brick	New York
111	Alt Country	15	30	10/19/2007	Garaj Mahal	Moe's Alley	Chicago
112	Indie	35	57.5	11/2/2007	The Pnuma Trio	Aggie Theatre	Indianapolis
113	Folk	25	35	11/6/2007	Rodrigo Y Gabriela	9:30 Club	Chicago
114	Alt Country	15	30	11/15/2007	Film School	Rock & Roll Hotel	Indianapolis
115	Indie	25	35	10/27/2007	...and You Will Know Us by the Trail of Dead	Black Cat	Indianapolis
116	Alt Country	15	30	10/30/2007	Cat Power	State Theater	New York
117	Rock	15	37.5	10/21/2007	The Hives	St. Pete Times Forum - Ice Palace	Chicago
118	Indie	15	30	10/19/2007	Salvador Santana Band	Skipper's Smokehouse	Indianapolis
119	Indie	25	40	11/2/2007	State Radio	The Hub	New York
120	Alt Country	25	40	11/6/2007	Public Property	Mahoney's	Indianapolis
121	Indie	35	57/5	11/15/2007	Bright Eyes	Foellinger Auditorium	Chicago
122	Indie	15	30	10/27/2007	Astral Project	The Iron Post	Chicago
123	Rock	25	35	10/30/2007	Widespread Panic	The Murat Theatre	Indianapolis
124	Indie	35	57.5	10/21/2007	Keller Williams	Unit	Chicago
125	Alt Country	25	47.5	10/19/2007	Nickel Creek	Singletary Center	Chicago
126	Alt Country	35	57.5	11/2/2007	Tim Reynolds	Row-A-Palooza	Indianapolis
127	Indie	25	35	11/6/2007	The Raveonettes	7th Street Entry	Chicago
128	Indie	15	30	11/15/2007	Alice in Chains	Uptown Theater	Chicago
129	Rock	35	57.5	10/27/2007	Papa Mali	The Shed	New York
130	Rock	25	40	10/30/2007	Lotus	The Other Side	Chicago
131	Indie	25	47.5	10/21/2007	The Killers	Auditorio Metropolitano	New York
132	Pop	35	57.5	10/19/2007	Chicago Afrobeat Project	Goofy Foot Lodge	Chicago
133	Folk	25	35	11/2/2007	Rogue Wave	The Waiting room	New York
138	Indie	35	50	10/21/2007	Explosions in the Sky	Tower Theater	Chicago
139	Indie	15	37.5	10/29/2007	The Hold Steady	Lupo's Heartbreak Hotel	Indianapolis
140	Alt Country	25	40	11/2/2007	Robert Randolph & The Family Band	Providence Performing Arts Center	Chicago
141	Pop	25	35	11/6/2007	J. Wail	Stubb's BBQ	Indianapolis
142	Rock	25	40	11/15/2007	Bob Schneider	Threadgill's South "Threadgill's World Headquarters"	New York
143	Rock	25	40	10/27/2007	Gregg Allman	Meridian	Indianapolis
144	Rock	15	30	10/30/2007	Willie Nelson	Nacogdoches County Expo Center	Chicago
145	Alt Country	25	40	10/21/2007	Lyrics Born	Nightlight	Chicago
146	Rock	35	50	10/19/2007	Josh Ritter	The Showbox	Chicago
148	Rock	35	50	11/6/2007	Yonder Mountain String Band	West Virginia University	Indianapolis

AMPscript

The following AMPscript appears in the body of the email.
<html>
<body style="font-size: 12px;color: #000099; font-family: verdana;">

%%[ /* This is an AMPscript Comment */ ]%%

<p>Hi %%First Name%%,<br><br>
We want to tell you about upcoming shows in your area based on your favorite music genres. We think you are as stoked as we are to see great live music and wanted to pass the word on. Enjoy!<br><br>
</p>

<h3>Upcoming Shows in %%City%%</h3>
<br><br>

%%[
/* First we want to determine if the customer has any favorite genres stored, not, encourage them to create online */
/* Otherwise, we will loop through every genre we find */

var @rsgenre, @rowgenre, @rsshow, @rowshow, @countergenre, @countershow, @currentgenre

SET @rsgenre = LOOKUPROWS("Customer_Genre_Preferences", "Customer ID", [Customer ID])
]%%

%%[
if ROWCOUNT(@rsgenre) == 0 then
]%%

<h4>We see that you don't have any genres stored, please add them online to get the most relevant information sent to you next time!</h4>
<a href="http://music.com">Add My Genres</a>

%%[
else

/* Now we are going to loop through each customer's genre, and then use that and City to look up upcoming shows to display in a table */
/* if we find a genre, but no corresponding shows, we are not going to display anything for that genre */
]%%

%%[
for @countergenre = 1 to ROWCOUNT(@rsgenre) do

SET @currentgenre = FIELD(ROW(@rsgenre, @countergenre), "Genre Preference")
SET @rsshow = LOOKUPROWS("Upcoming_Shows", "City", City, "Genre", @currentgenre)

    if ROWCOUNT(@rsshow) != 0 then ]%%
      <h4>%%=CONCAT("Upcoming ", @currentgenre, " Shows")=%%</h4><br>
        <table width="700px" style="border: 1 solid black; font-size: 10px;color: #000099; font-family: verdana;" cellpadding="2" cellspacing="0">
          <tr style="font-size: 10px;color: #999999; font-family: verdana; font-weight: bold;">
            <td>Date</td>
            <td>Artist</td>
            <td>Venue</td>
            <td>Tickets</td>
            <td>City</td>
          </tr>
        %%[
        for @countershow = 1 to ROWCOUNT(@rsshow) do
          SET @rowshow = ROW(@rsshow, @countershow)

          /* Here we are setting alternating style for the row */

          if MOD(@countershow, 2)== 0 and @countershow != 1 then
        ]%%

          <tr style="background-color: #dcdcdc;">

        %%[else]%%

          <tr>

        %%[endif]%%

            <td>%%=FORMAT(FIELD(@rowshow, "Show Date"), "ddd, MMM d")=%%</td>
            <td>%%=FIELD(@rowshow, "Artist")=%%</td>
            <td>%%=FIELD(@rowshow, "Venue")=%%</td>
            <td><a href="http://music.com?ConcertID=%%=FIELD(@rowshow, 'Concert ID')=%%">%%=CONCAT("From", FORMAT(FIELD(@rowshow, "Low Price"), "C2")," to ", FORMAT(FIELD(@rowshow, "High Price"), "C2"))=%%</a></td>
            <td>%%=FIELD(@rowshow, "City")=%%</td>
          <tr>

        %%[
        next @countershow
        ]%%

        </table>
        <br><br>
    %%[
    endif
    ]%%

%%[
next @countergenre
]%%
%%[
endif
]%%

<custom name="opencounter" type="tracking"><table cellpadding="2" cellspacing="0" width="600" ID="Table5" Border=0><tr><td><font face="verdana" size="1" color="#444444">This email was sent to: %%emailaddr%% <br><br><b>Email Sent By:</b> %%Member_Busname%%<br>%%Member_Addr%% %%Member_City%%, %%Member_State%%, %%Member_PostalCode%%, %%Member_Country%%<br><br></font></td></tr></table><a href="%%profile_center_url%%" alias="Update Profile">Update Profile</a>

</body>
</html>

Output

When the marketer sends the email, the application processes the code:

For John201 Gault:

RockShowOutput1 (1).png

For Dominique202:

Scenario 2: Suggested Car Listings

In this example, a car broker wants to send subscriber an email that includes a list of makes and models of cars that the subscriber might like based on the subscriber's stated preference as to type of car.

The car broker creates an attribute for the subscriber to contain the subscriber's preference in car type. The broker populates the attribute with a survey.

Data Extensions and Relationships

The car broker also creates a data extension that contains multiple makes and models of cars for each of the short list of types that the subscribers could choose in the survey. The car broker creates a data relationship to associate the car_preference attribute on the subscriber to the type column in the data extension.

The subscriber table contains the following data:

email_address	first_name	last_name	region	car_preference
hrearden@example.com	Hank	Rearden	north	sedan
jgalt@example.com	John	Galt	south	convertible
hroark@northerntrailoutfitter.com	Howard	Roark	north	SUV

The cars data extension contains the following data:

type	make	model
Sedan	BMW	530i
Sedan	Ford	Fusion
Sedan	Cadillac	CTS
Convertible	Chevrolet	Corvette
Convertible	Chrysler	Sebring
SUV	Toyota	4runner
SUV	Chevrolet	Trailblazer
SUV	Porsche	Cayenne
SUV	Nissan	Pathfinder

AMPscript

The car broker includes the following AMPscript block in the body of the email:

<html> ... <table style= "border: 1px solid black"> %%[ /* Starting AMPscript */ /* Declaring variables */ var @rs, @row, @cntr /* Looking up related cars by subscriber */ /* Using lookup rows function to return a rowset of related cars to use */ SET @rs = LookupRows("Cars", "Type", CarPreference) /* Looping through each record in the recordset */ for @cntr = 1 to RowCount(@rs) do /* If there is a value in the row, output to HTML */ SET @row = Row(@rs, @cntr) /*Ending script block here */ ]%% %%=Field(@row, "Make")=%%       %%=Field(@row, "Model")=%% %%[ /* resuming script block here */ next @cntr /*Ending script block */]%% </table> </html>