Salesforce Marketing Cloud Project: How to Build an Airline Passenger Booking History Email (Airline Domain Project)

If you’re learning Salesforce Marketing Cloud (SFMC), there’s no better way to solidify your skills than by building a domain-specific, end-to-end project. In this post, we’ll walk through a complete airline domain mini project that covers everything from designing a data model to writing AMPscript that dynamically renders a passenger’s past flight bookings inside an email.

This is the exact project covered in the live implementation below — highly recommended to follow along:

▶ Watch Part 1: https://youtu.be/m3QsRJsjvsM
▶ Watch Part 2: https://youtu.be/BApWBMlJF98

---

---

What You’ll Learn

By the end of this project, you’ll be able to:

• Design a real-world relational data model in SFMC
• Create Sendable and Non-Sendable Data Extensions
• Build Contact Builder Attribute Groups with proper relationships
• Import sample data using CSV upload or Automation Studio
• Write AMPscript using LookupRows(), RowCount(), FOR loop, Row(), and Field() to display a dynamic table in an email

---

Salesforce Marketing Cloud Project : The Business Scenario

An airline company wants to send each passenger a personalized email showing their past travel booking history, including:

• Flight Number
• Source → Destination
• Travel Date
• Airline Name
• Booking Status (Completed / Cancelled)

This is a classic one-to-many use case — one passenger, multiple bookings — which makes it perfect for practicing AMPscript’s row-retrieval functions.

---

Salesforce Marketing Cloud Project Task 1: Designing the Data Model

Before touching SFMC, you need to think relationally. The project uses four Data Extensions that mirror a normalized database schema.

1. Passenger_DE (Sendable DE)

This is the root/sendable Data Extension. It represents your contacts.

Field Name	Data Type	Notes
PassengerID	Text (PK)	Unique passenger identifier
SubscriberKey	Text	Used for the send relationship
EmailAddress	EmailAddress	Customer email
FirstName	Text	First name
LastName	Text	Last name
MobileNumber	Phone	Optional
CreatedDate	Date	Profile created date

Sendable Relationship: Subscriber Key → SubscriberKey

This is the DE you’ll target in your email send. Every other DE is non-sendable.

---

2. Airline_DE (Non-Sendable)

Stores airline master data — think of this as a lookup/reference table.

Field Name	Data Type	Notes
AirlineID	Text (PK)	Unique airline ID
AirlineName	Text	e.g., IndiGo, Air India, SpiceJet
Country	Text	Country of origin

---

3. Flight_DE (Non-Sendable)

Stores individual flight records, linked to an airline.

Field Name	Data Type	Notes
FlightID	Text (PK)	Unique flight ID
FlightNumber	Text	e.g., 6E-204
AirlineID	Text (FK)	Foreign key to Airline_DE
Source	Text	Departure city
Destination	Text	Arrival city
Duration	Number	Flight duration in minutes

---

4. Booking_DE (Non-Sendable)

The central transaction table. Each row represents one booking by one passenger on one flight.

Field Name	Data Type	Notes
BookingID	Text (PK)	Booking reference
PassengerID	Text (FK)	Foreign key to Passenger_DE
FlightID	Text (FK)	Foreign key to Flight_DE
BookingDate	Date	Date the booking was made
TravelDate	Date	Actual travel date
BookingStatus	Text	“Completed” or “Cancelled”

Key Design Note: A passenger can have multiple bookings. This is a one-to-many relationship between Passenger_DE and Booking_DE. This is what makes LookupRows() (not Lookup()) the right function — you need to retrieve all matching rows, not just one.

---

Salesforce Marketing Cloud Project Task 2: Building Attribute Groups in Contact Builder

Contact Builder is where you wire these Data Extensions together so SFMC understands the relationships between them.

Step-by-Step Setup

1. Go to Audience Builder → Contact Builder → Data Designer
2. Create a new Attribute Group called “Passenger Profile”
3. Set the Root DE as Passenger_DE, with Contact Key linked to SubscriberKey

Add the Related Attribute Groups

Booking Details

• DE: Booking_DE
• Relationship: PassengerID → PassengerID (1:N — one passenger, many bookings)

Flight Details

• DE: Flight_DE
• Relationship: FlightID → FlightID (1:1 — one booking references one flight)

Airline Details

• DE: Airline_DE
• Relationship: AirlineID → AirlineID (1:1 — one flight belongs to one airline)

After linking all DEs, validate the relationships in Data Designer and publish the data model. This step is critical — publishing makes the model live and usable in sends.

---

Salesforce Marketing Cloud Project Task 3: Importing Sample Data

For this project, you need:

• At least 50 passengers in Passenger_DE
• Each passenger should have 2–4 past bookings in Booking_DE
• Multiple airlines in Airline_DE and multiple flights in Flight_DE
• All TravelDate values must be in the past

Import Order Matters

Always import in this order to avoid foreign key violations:

1. Airline_DE first (no dependencies)
2. Flight_DE second (depends on Airline_DE)
3. Passenger_DE third (independent of flights/airlines)
4. Booking_DE last (depends on both Passenger_DE and Flight_DE)

Data Validation Checklist

Before moving to email creation, verify:

• [ ] PassengerIDs in Booking_DE match records in Passenger_DE
• [ ] FlightIDs in Booking_DE match records in Flight_DE
• [ ] AirlineIDs in Flight_DE match records in Airline_DE
• [ ] SubscriberKey is populated for all rows in Passenger_DE
• [ ] All TravelDate values are in the past

You can do imports via Automation Studio → Import Activity or a simple manual CSV upload into each DE.

---

Salesforce Marketing Cloud Project Task 4: Writing the AMPscript Email

This is the most technically interesting part. The goal is to send each passenger an email containing a dynamically generated table of their past flights.

Required AMPscript Functions

Function	Purpose
LookupRows()	Retrieve all bookings for a given PassengerID
RowCount()	Count how many booking records exist
FOR loop	Iterate over each booking row
Row()	Access a specific row from the result set
Field()	Extract a field value from a row
Lookup()	Fetch a single value (e.g., AirlineName, FlightNumber)

The AMPscript Logic — Step by Step

Step 1: Get the passenger’s PassengerID from Passenger_DE using their SubscriberKey.

Step 2: Use LookupRows() to retrieve all matching rows from Booking_DE.

Step 3: Check RowCount() — if zero, show a fallback message. If greater than zero, proceed.

Step 4: Loop through each booking using a FOR loop.

Step 5: Inside the loop, use Lookup() to fetch FlightNumber, Source, Destination, TravelDate from Flight_DE, and AirlineName from Airline_DE.

Step 6: Render each row as an HTML table row.

---

Full AMPscript Code

%%[

/* Step 1 — Get PassengerID from Passenger_DE using SubscriberKey */
SET @subKey = _subscriberkey
SET @PassengerID = Lookup("Passenger_DE", "PassengerID", "SubscriberKey", @subKey)
SET @FirstName   = Lookup("Passenger_DE", "FirstName",   "SubscriberKey", @subKey)

/* Step 2 — Retrieve all bookings for this passenger */
SET @BookingRows = LookupRows("Booking_DE", "PassengerID", @PassengerID)
SET @BookingCount = RowCount(@BookingRows)

]%%

<p style="font-size:16px;">Hi %%=v(@FirstName)=%%,</p>
<p>Here is your past travel history with us:</p>

%%[ IF @BookingCount > 0 THEN ]%%

<table width="100%" cellpadding="8" cellspacing="0" border="1" style="border-collapse:collapse; font-size:14px;">
  <thead style="background-color:#003580; color:#ffffff;">
    <tr>
      <th>Flight No</th>
      <th>Airline</th>
      <th>From</th>
      <th>To</th>
      <th>Travel Date</th>
      <th>Status</th>
    </tr>
  </thead>
  <tbody>

%%[

FOR @i = 1 TO @BookingCount DO

  SET @row          = Row(@BookingRows, @i)
  SET @FlightID     = Field(@row, "FlightID")
  SET @TravelDate   = Field(@row, "TravelDate")
  SET @Status       = Field(@row, "BookingStatus")

  /* Bonus: only show past bookings */
  IF @TravelDate < NOW() THEN

    /* Lookup flight details */
    SET @FlightNumber = Lookup("Flight_DE", "FlightNumber", "FlightID", @FlightID)
    SET @Source       = Lookup("Flight_DE", "Source",       "FlightID", @FlightID)
    SET @Destination  = Lookup("Flight_DE", "Destination",  "FlightID", @FlightID)
    SET @AirlineID    = Lookup("Flight_DE", "AirlineID",    "FlightID", @FlightID)

    /* Lookup airline name */
    SET @AirlineName  = Lookup("Airline_DE", "AirlineName", "AirlineID", @AirlineID)

    /* Set row background color based on status */
    IF @Status == "Completed" THEN
      SET @rowBg = "#f0fff4"
    ELSE
      SET @rowBg = "#fff5f5"
    ENDIF

]%%

    <tr style="background-color:%%=v(@rowBg)=%%;">
      <td>%%=v(@FlightNumber)=%%</td>
      <td>%%=v(@AirlineName)=%%</td>
      <td>%%=v(@Source)=%%</td>
      <td>%%=v(@Destination)=%%</td>
      <td>%%=v(@TravelDate)=%%</td>
      <td>%%=v(@Status)=%%</td>
    </tr>

%%[

  ENDIF

NEXT @i

]%%

  </tbody>
</table>

%%[ ELSE ]%%

<p style="color:#888888; font-style:italic;">Looks like you haven't traveled with us yet. We hope to see you on board soon!</p>

%%[ ENDIF ]%%

---

Code Walkthrough

_subscriberkey is a built-in AMPscript system variable that holds the subscriber key of the recipient at send time — you don’t need to pass it in manually.

LookupRows("Booking_DE", "PassengerID", @PassengerID) returns a rowset — a collection of multiple rows — where the PassengerID field matches the current passenger. This is the heart of the one-to-many retrieval.

RowCount(@BookingRows) tells you how many rows came back. Always check this before looping — iterating over an empty rowset causes rendering errors.

Row(@BookingRows, @i) retrieves one specific row from the rowset by its index position (1-based, not 0-based).

Field(@row, "FieldName") extracts the value of a named field from that row.

The inner Lookup() calls (for flight and airline data) are single-value lookups — they return one field from one matching row. That’s the key difference between Lookup() (one value) and LookupRows() (multiple rows).

---

Bonus Enhancements

The code above already includes these bonus tasks from the assignment:

Filter for past bookings only — The IF @TravelDate < NOW() THEN condition ensures only flights that have already happened are shown, filtering out future or test bookings.

Personalized greeting — Hi %%=v(@FirstName)=%% uses the passenger’s first name pulled directly from Passenger_DE.

Graceful empty state — The ELSE branch handles passengers with no past bookings with a friendly message instead of a blank table.

Status-based row coloring — Completed bookings get a light green background, cancelled ones get a light red — a nice UX touch.

---

Key Concepts Summary

Concept	Where It’s Used
Sendable vs Non-Sendable DE	Passenger_DE is sendable; others are lookup/reference DEs
Primary Key / Foreign Key	AirlineID, FlightID, PassengerID link DEs together
1:N Relationship	One passenger → many bookings
Contact Builder	Wires DEs together for SFMC’s audience model
LookupRows()	Retrieves multiple rows — essential for history-style emails
FOR loop with Row() and Field()	Iterates over the rowset to render each row dynamically

---

Common Mistakes to Avoid

1. Using Lookup() instead of LookupRows() for multi-row retrieval
Lookup() only returns the first match. If a passenger has 4 bookings, you’ll only see one. Always use LookupRows() when you expect multiple records.

2. Not checking RowCount() before the FOR loop
If there are zero rows and you try to loop, the AMPscript will throw a rendering error. Always wrap your loop in an IF @count > 0 check.

3. Wrong import order
Importing Booking_DE before Flight_DE or Passenger_DE means your foreign key values reference IDs that don’t exist yet. Import parent tables first.

4. Forgetting to publish the data model in Contact Builder
Validating relationships without publishing means the model isn’t live. Always click Publish after validation.

5. TravelDate in the future
The assignment specifies that all travel dates must be in the past. If you’re generating test data, double-check the dates — a common slip when copy-pasting CSV templates.

---

Watch the Live Implementation

This entire project is demonstrated live across two videos. Part 1 covers the data model design and Contact Builder setup, and Part 2 covers the AMPscript email creation.

▶ Part 1 — Data Model & Contact Builder: https://youtu.be/m3QsRJsjvsM
▶ Part 2 — AMPscript Email: https://youtu.be/BApWBMlJF98

Watching a real build is often the fastest way to understand how these pieces fit together, especially the Contact Builder relationship setup and the AMPscript debugging process.

---

Final Thoughts

This project is a fantastic benchmark for SFMC learners because it touches nearly every foundational skill in one go — data modeling, Contact Builder, data import, and AMPscript. The airline domain is intuitive (everyone understands booking history), which lets you focus on the technical implementation rather than the domain logic.

Once you’ve completed this, you’ll have a strong mental model for tackling any scenario that involves dynamic, multi-row content in SFMC emails — loyalty programs, order histories, event registrations, subscription plans, and more.

If you found this helpful, share it with someone preparing for their SFMC certification. And drop any questions in the comments below — we’re happy to help debug your AMPscript!

---

Published on rizexlabs.com | Salesforce Marketing Cloud Tutorials

Tags: Salesforce Marketing Cloud, AMPscript, SFMC Tutorial, Data Extensions, Contact Builder, LookupRows, Email Personalization, Airline Domain, Marketing Automation, SFMC Certification