P00 — Start Here: The Practical UI Playbook

🎯 Why this exists: your last interviews failed on "where / how would you click", not on theory. This playbook fixes exactly that — every common task is a screen wireframe + click-recipe + the spoken answer. Learn to narrate the clicks, and the practical questions become easy.

🧠 The one rule that fixes the practical gap

When they ask "how would you do X?", answer in this shape:

"I'd go to  <App>  ▸  <menu>  ▸  <button> ,
 set <the key field>, then <validate / test>, and verify in <where>."

If you can name the path and the button, you sound like you've done it 100 times. That's the whole game.

🗺️ "Where does everything live?" — the cheat table (memorize this)

📚 The modules (each = wireframe + click-recipe + spoken answer)

🧪 How to get REAL hands-on (do this — reading isn't enough)

The wireframes here are faithful labeled maps of the screens (not screenshots — I can't fabricate those). To build true muscle memory:

1. Ask your GAP lead for sandbox/QA-BU access and actually click through P02→P05 once each. Nothing beats doing it.
2. Trailhead (free): search the "Marketing Cloud Engagement" trailmixes — Email Studio, Contact Builder, Journey Builder, Automation Studio, Query Data with SQL. Several have hands-on challenges.
3. Narrate while you click: say the breadcrumb out loud each time ("Automation Studio, Activities, SQL Query, Create…"). That's what you'll repeat in the interview.
4. After each module here, close your eyes and re-draw the screen from memory. If you can sketch it, you can describe it.

🛠️ How to use this reader

• Dark by default (🌓 toggles light). One subsection at a time; ← / → or Prev/Next.
• / searches everything. ⏱ paces you (default ~2h). Space pauses. ☆ / M marks a screen for review.
• Bold = key term, green bold (in quotes) = say-this, cyan italic = nuance.

➡️ Next: P01_Navigation_and_App_Map.md

P01 — SFMC Navigation & App Map

🎯 Interview questions this answers: - "Walk me through the SFMC interface — how do you move around the tool?" - "What Studios and Builders have you worked with?" - "Where do you go to build an email? A journey? An automation? To manage contacts?" - "How do you switch between business units?" - "Where is Setup, and what do you do there?" - "Classic nav vs the newer nav — what's the difference?"

You know the theory. This module makes the screen feel familiar so that when they ask "where would you click to do X," you narrate it like someone who logs in every day. Read this with the picture in your head: one horizontal bar across the top, every tool hangs off it.

🧭 Where in the UI

Everything starts at the top navigation bar (the dark horizontal strip across the top of every SFMC page). You hover an app name to drop its menu, then click an item. The literal breadcrumbs you'll be expected to say out loud:

• Email → Top nav > Email Studio > Email (lands in Email Studio Overview; Content Builder, Subscribers, Tracking, Admin live here)
• Content → Top nav > Email Studio > Email > Content Builder (shared content library)
• Journeys → Top nav > Journey Builder > All Journeys
• Automations → Top nav > Journey Builder > Automation Studio > Overview
• Contacts/Data → Top nav > Audience Builder > Contact Builder > Data Extensions
• SMS / Push → Top nav > Mobile Studio > MobileConnect (SMS) or MobilePush
• Landing pages → Top nav > Web Studio > CloudPages
• Reports → Top nav > Analytics Builder > Reports
• Admin → Top-right account name (☰/▾) > Setup
• Switch BU → Top-right account/BU dropdown > pick the business unit

🗝️ Mental model: Studios = channels & content (Email, Mobile, Web). Builders = data & orchestration (Journey, Contact, Analytics, Automation). (Automation Studio sits visually under the Journey Builder menu.)*

🖼️ Screen map

Wireframe (not a screenshot) — layout & labels reflect the live SFMC UI.

👉 Click recipe

1. Log in at your tenant URL (e.g. https://mcXXXXXX.marketingcloudapps.com) → you land on the dashboard with the top nav bar across the top.
2. Open a tool: hover the app name in the top nav → its dropdown appears → click the item. - Build/send email: hover Email Studio → click Email → use the inner tabs Overview / Content Builder / Subscribers / Interactions / Tracking / Admin. - Build a journey: hover Journey Builder → click All Journeys (or Journey Builder to open the canvas). - Build an automation: hover Journey Builder → click Automation Studio. - Manage contacts/DEs: hover Audience Builder → click Contact Builder → tab to Data Extensions / All Contacts / Data Designer. - Send SMS/push: hover Mobile Studio → click MobileConnect or MobilePush. - Landing pages/forms: hover Web Studio → click CloudPages. - Reports: hover Analytics Builder → click Reports (or Discover / Dashboards).
3. Switch business unit: click the account/BU dropdown at the top-right → choose the BU. The whole app reloads scoped to that BU.
4. Reach Setup: click the account name at the top-right → choose Setup. (Newer nav puts it behind a gear/⚙ icon, same place.) Setup is where Users, Roles, Business Units, Sender Authentication, Installed Packages (API), Data Retention live.

🗣️ Say this in the interview

"SFMC is one horizontal top-nav bar — every tool hangs off it, and you hover an app to drop its menu. Day to day at GAP I live in Email Studio — that's Content Builder for building emails, plus Subscribers, Tracking, and Admin. For data I'm in Audience Builder → Contact Builder, working with Data Extensions and the Data Designer. For batch work — imports, SQL query activities, file transfers, scheduled sends — I go to Automation Studio, which sits under the Journey Builder menu. For orchestration I use Journey Builder itself off All Journeys. I've also touched Mobile Studio (MobileConnect for SMS, MobilePush), CloudPages under Web Studio for landing pages and code resources, and Analytics Builder for tracking and reports. Admin and config — users, roles, sender authentication, API packages — is under Setup, which you reach from the account name at the top-right, same dropdown you use to switch business units."

That single paragraph answers "what have you worked with" and "how do you navigate" at once — it's the most efficient thing you can memorize.

⚠️ Gotchas / what they probe

• BU switching loses unsaved work. Changing the BU from the top-right dropdown reloads the whole app scoped to that BU — anything unsaved (a half-built email, an unsaved query) is gone. Always save first. Good senior tell: "I confirm which BU I'm in before I send — content, DEs, and sends are BU-scoped."
• Automation Studio is NOT its own top-level app. It lives under the Journey Builder menu. Interviewers like to see if you know that. (Email Studio also has an "Interactions" area for triggered/automated sends — don't confuse the two.)
• Setup is per-account, role-gated. What you see in Setup depends on your role/permissions and which BU/parent account you're in. Some config (e.g., Sender Authentication Package, account-wide settings) is only at the top/parent BU, not child BUs.
• Classic nav vs new nav. Older tenants show full text labels in the bar; newer ones may collapse some apps behind an app launcher / waffle icon and move Setup behind a ⚙ gear — the paths and tool names are the same, only the chrome moved. Say: "Whether it's the classic text nav or the newer condensed nav, the tools and breadcrumbs are identical."
• Audience Builder vs Contact Builder. Contact Builder (data model, DEs, Data Designer) is the everyday one and isn't going anywhere; the separate Audience Builder segmentation product is being retired — so if asked, lean on Contact Builder + SQL/Data Filters for segmentation, not legacy Audience Builder.
• Content Builder is shared, Email Studio "Email" is the sender. Content Builder is the cross-channel content library; the Email app is where classifications, send definitions, and tracking live. Knowing they're distinct-but-linked reads as hands-on.

➡️ Next: P02_Create_Data_Extension.md

P02 — Create & Load a Data Extension

🎯 Interview questions this answers: - "Walk me through creating a Data Extension in the UI." - "How do you take the data for the email builds?" - "Explain Append and deduplication in a Data Extension." - "How do you PREVENT deduplication of subscribers?" - "What's the difference between Overwrite, Add & Update, and Append on import?"

🧭 Where in the UI

Create a DE: App Switcher (top-left waffle) ▸ Audience Builder ▸ Contact Builder ▸ Data Extensions ▸ Create

You can also create DEs from Email Studio ▸ Email ▸ Subscribers ▸ Data Extensions ▸ Create. Same object, two doors. Contact Builder is the modern path and the one to name in an interview.

On Create, you pick a creation method: - Standard Data Extension — empty DE you define field-by-field. - Filtered Data Extension — a live subset of a source DE driven by filter rules (no SQL). - From a Template — pre-built field sets (e.g. the Salesforce Master / behavioral templates).

Load data into it: - Manual / one-off: open the DE ▸ Records tab (view rows) and the Import button (push a file). - Automated / production: Automation Studio ▸ Activities ▸ Import File Activity (reads a file dropped on the Enhanced FTP / Safehouse, writes to the DE on a schedule).

🖼️ Screen map

👉 Click recipe

A. Create a sendable DE with a Primary Key

1. App switcher ▸ Audience Builder ▸ Contact Builder ▸ Data Extensions.
2. Click Create ▸ choose Standard Data Extension ▸ Next.
3. Properties: type a Name (e.g. Master_Audience); leave External Key blank to auto-generate (or set your own for API/AMPscript); pick a Location (folder); tick Is Sendable, then tick Is Testable. Next.
4. Data Retention Policy: leave Off for a persistent audience, or set a period (e.g. delete records after 6 months). Next.
5. Fields: add rows. For SubscriberKey set Type = Text, Length = 50, tick Primary Key, untick Nullable. Add EmailAddress (Type = EmailAddress) and content fields (e.g. FirstName, Nullable on). Next.
6. Send Relationship: in the dropdown choose the field that maps to Subscriber Key — pick SubscriberKey so each row is keyed to one contact. Click Complete.

B. Import a file with Add & Update

7. Open the DE ▸ Records tab ▸ click Import.
8. Choose source = FTP (file on Enhanced FTP) or From File (manual upload). Pick the file + delimiter.
9. Update Type = Add and Update ▸ map CSV columns to DE fields ▸ Next ▸ Finish. Rows matching the PK update in place; new PKs insert; nothing is deleted.

C. Production version: in Automation Studio, drop the same as an Import File Activity (FTP location + Update Type = Add and Update) inside a scheduled Automation.

🗣️ Say this in the interview

"How do you take the data for the email builds?"

"Three patterns. The clean audience usually lives in a sendable Data Extension keyed on SubscriberKey. If marketing wants a slice of that — say UK customers who opted in — I build a Filtered DE off it, no SQL needed. For anything that needs joins or business logic I write a SQL Query Activity that selects into a target sendable DE, then I send off that DE. The send always points at a sendable DE so every row resolves to a contact."

"Explain append and deduplication."

"On import you pick an Update Type. Add Only / Append just inserts new rows — if you append the same person twice with no key, you get duplicate rows. Add & Update is an upsert: it matches on the Primary Key, updates the existing row, and only inserts genuinely new keys — that's how I dedupe at the row level. Overwrite truncates the DE and reloads it from scratch."

"How do you PREVENT deduplication of subscribers?" (the trick question)

"Two different levers, and the interviewer is testing whether I conflate them. Row-level dedup inside a DE is controlled by the Primary Key plus Add & Update — same PK, one row. But preventing a subscriber from being split or merged incorrectly across sends is about the send relationship: I map a stable, unique SubscriberKey as the relationship field — not EmailAddress. Because SubscriberKey is the contact's identity, the same person stays a single subscriber across every send and isn't duplicated or fragmented. So: PK for row dedup, SubscriberKey as the send relationship for subscriber identity."

(GAP voice: "On GAP brand sends our Master_Audience DE is keyed on the customer ID as SubscriberKey, loaded nightly via an Import File Activity on Add & Update, so the loyalty file refreshes in place without ever duplicating a shopper.")

💻 Code (optional)

A SQL Query Activity that upserts into a sendable DE (set the activity's update type to Update, which behaves as Add & Update on the PK):

SELECT
    c.CustomerID  AS SubscriberKey,
    c.Email       AS EmailAddress,
    c.FirstName,
    c.LoyaltyTier
FROM Customers_Raw  AS c
WHERE c.EmailOptIn = 'True'
  AND c.Email IS NOT NULL

🔍 Line by line: - c.CustomerID AS SubscriberKey — alias the stable customer ID to the DE's PK / send-relationship field, so each shopper is one keyed row. - c.Email AS EmailAddress — aliases must match the target DE field names exactly or the write fails. - FROM Customers_Raw — source DE (a non-sendable staging/audience DE). - WHERE EmailOptIn = 'True' AND Email IS NOT NULL — guards against sending to opted-out or null-email rows (null email would block the send for that row). - Target DE + Update Type Update ⇒ matches on SubscriberKey PK, updates existing rows, inserts new ones — no duplicates.

⚠️ Gotchas / what they probe

• Overwrite wipes the DE first. If a job fails mid-load after the truncate, the DE can be left empty — never use Overwrite on a live send audience without a backup. Add & Update is the safe default.
• No Primary Key = silent duplicate rows. Without a PK, every import inserts; running the same file twice doubles your rows and the send goes to the same person twice. The PK is what makes Add & Update actually dedupe.
• SubscriberKey ≠ EmailAddress. Using EmailAddress as the subscriber key means a person who changes email becomes a new subscriber (and one who shares an email collides). Always key on a stable ID.
• Retention deletes permanently. A Data Retention Policy silently drops rows (or the whole DE) on schedule — great for compliance, dangerous on a master audience. Confirm it's Off for persistent DEs.
• Is Testable depends on Is Sendable. You can't tick Testable until Sendable is on — and a non-sendable DE can't be the target of a send or a test send.
• External Key is the stable handle for API / AMPscript / SQL — once integrations reference it, renaming the DE is fine but changing the key breaks them.

➡️ Next: P03_SQL_Query_Activity.md

P03 — Writing SQL: the Query Activity

🎯 Interview questions this answers: - "Where would you type the SQL query for extracting the Data Views?" - "How do you save historical data of customers?" - "What's the difference between Overwrite, Update, and Append on a Query Activity?" - "Can you SELECT and see results on screen in SFMC?" (No — results must land in a Data Extension.)

🧭 Where in the UI

You do not type SQL into a free console that prints results. In SFMC you type SQL inside a SQL Query Activity, and the output is written into a target Data Extension (DE). There are two places to build that activity.

Primary path — Automation Studio (this is the answer to memorize):

App Switcher (waffle, top-left)
  → Automation Studio
    → Activities  (left-hand tab / Overview > Activities)
      → Create Activity  (the "New Automation Activity" wizard)
        → SQL Query
          → Create
            → ⬛ SQL Query Activity editor  ← YOU TYPE THE SQL HERE

In the editor you fill in: Name → the big SQL text box (the multi-line query editor) → Target Data Extension (chosen from a dropdown; you must have pre-created it) → Data Action = Overwrite / Update / Append → click Validate → Save.

Alternate / legacy path — Email Studio:

Email Studio
  → Email  (top nav)
    → Interactions
      → Query
        → Create
          → ⬛ same Query Activity editor (SQL text box + target DE)

Both editors are functionally the same Query Activity. Modern work lives in Automation Studio because that's where you schedule it; Email Studio > Interactions > Query is the older entry point you'll still see referenced.

🖼️ Screen map

Call-outs: ① the SQL text box is the literal answer to "where do you type the query" — including queries against Data Views. ② Target Data Extension is where results land (you can't print to screen). ③ Data Action decides whether the target is wiped (Overwrite), matched-and-merged (Update), or added-to (Append).

👉 Click recipe

1. Pre-create the target DE. Email Studio → Subscribers → Data Extensions → Create. Add columns whose names and data types match the columns your SELECT returns (an aliased SELECT s.SubscriberKey AS SubscriberKey must have a SubscriberKey column waiting in the DE). Mark a Primary Key if you plan to use Update.
2. Open Automation Studio → Activities tab → Create Activity → SQL Query → Create.
3. Name the activity (e.g., Daily_Openers_Rollup).
4. Paste your SQL into the big SQL text box (call-out ①). Data Views are just table names here — _Sent, _Open, etc. — you don't "import" them.
5. Choose the Target Data Extension from the dropdown (call-out ②).
6. Pick the Data Action (call-out ③): Overwrite, Update, or Append.
7. Click Validate. SFMC parses the SQL and confirms every returned column maps to a column in the target DE. Fix mismatches until it passes.
8. Save the activity.
9. Add it to an Automation: Automation Studio → New Automation → drag a SQL Query step onto the canvas → select your saved activity (or build it inline). Add a Schedule (e.g., nightly) or a File Drop starting source.
10. Run Once to test, then Schedule it. Output appears in the target DE; check the Automation's run-history Activity log for row counts / errors.

💻 Code

Example 1 — Last-30-day openers (join _Sent + _Open)

SELECT
    s.SubscriberKey,
    s.JobID,
    j.EmailName,
    o.EventDate AS OpenDate
FROM _Open o
INNER JOIN _Sent s
    ON o.SubscriberKey = s.SubscriberKey
   AND o.JobID         = s.JobID
INNER JOIN _Job j
    ON s.JobID = j.JobID
WHERE o.EventDate >= DATEADD(DAY, -30, GETDATE())

🔍 Line by line: - SELECT s.SubscriberKey, s.JobID, j.EmailName, o.EventDate AS OpenDate — the columns we keep; every one of these must exist (by name) in the target DE, which is why o.EventDate is aliased to OpenDate. - FROM _Open o — the _Open Data View (system table of open events); o is its alias. Data Views are queried exactly like any DE — no import step. - INNER JOIN _Sent s ON o.SubscriberKey = s.SubscriberKey AND o.JobID = s.JobID — join opens to the corresponding send. SubscriberKey + JobID is the standard pairing that ties an engagement event back to the exact send job; INNER JOIN keeps only subscribers who actually opened. - INNER JOIN _Job j ON s.JobID = j.JobID — _Job holds one row per send job, so this brings in human-readable send metadata like EmailName. - WHERE o.EventDate >= DATEADD(DAY, -30, GETDATE()) — limits to opens in the trailing 30 days. SFMC SQL is T-SQL flavored, so DATEADD/GETDATE are valid.

Example 2 — Nightly rollup of Data Views into a retained Reporting DE

SELECT
    s.SubscriberKey,
    s.JobID,
    s.EventDate AS SentDate,
    CASE WHEN o.SubscriberKey IS NOT NULL THEN 1 ELSE 0 END AS Opened,
    CASE WHEN c.SubscriberKey IS NOT NULL THEN 1 ELSE 0 END AS Clicked,
    CASE WHEN b.SubscriberKey IS NOT NULL THEN 1 ELSE 0 END AS Bounced
FROM _Sent s
LEFT JOIN _Open o
    ON s.SubscriberKey = o.SubscriberKey AND s.JobID = o.JobID
LEFT JOIN _Click c
    ON s.SubscriberKey = c.SubscriberKey AND s.JobID = c.JobID
LEFT JOIN _Bounce b
    ON s.SubscriberKey = b.SubscriberKey AND s.JobID = b.JobID
WHERE s.EventDate >= DATEADD(DAY, -1, GETDATE())

Run this on a nightly schedule with Data Action = Append into a permanent Reporting_EmailEngagement DE.

🔍 Line by line: - SELECT s.SubscriberKey, s.JobID, s.EventDate AS SentDate, ... — one row per send, flattened with engagement flags; these columns define the schema of the retained Reporting DE. - CASE WHEN o.SubscriberKey IS NOT NULL THEN 1 ELSE 0 END AS Opened — turns "did a matching open exist?" into a 1/0 flag. Same pattern for Clicked (_Click) and Bounced (_Bounce). - FROM _Sent s — start from every send, so the row exists even when there was no engagement. - LEFT JOIN _Open o ... / _Click c ... / _Bounce b ... — LEFT JOIN (not INNER) so a send with zero opens still returns a row with Opened = 0. Each join keys on SubscriberKey + JobID. - WHERE s.EventDate >= DATEADD(DAY, -1, GETDATE()) — only yesterday's sends. Because the activity runs nightly with Append, the Reporting DE accumulates history beyond the 6-month Data View window — this is the historical-data answer.

🗣️ Say this in the interview

"In SFMC you don't get a free SQL console that prints rows. You type the query inside a SQL Query Activity — Automation Studio → Activities → Create Activity → SQL Query → Create — and the SQL goes in the big SQL text box in that editor. That's exactly where I'd write a query against the Data Views: _Sent, _Open, _Click, _Bounce, _Job are system tables I just SELECT FROM, even though they never show up in the Data Extension list. I pick a pre-built target Data Extension whose columns match my SELECT, set the Data Action to Overwrite, Update, or Append, hit Validate, save, then drop the activity into an Automation and schedule it. The older entry point is Email Studio → Interactions → Query, but I keep it in Automation Studio because that's where I schedule it.

For saving historical customer data: Data Views only keep about 6 months, so at GAP I'd run a nightly Query Activity that reads the Data Views and Appends the flattened engagement into a permanent Reporting DE. That DE never expires, so we keep multi-year send/open/click history for reporting long after the Data Views have rolled off."

⚠️ Gotchas / what they probe

• Overwrite wipes the target first. Overwrite truncates the entire target DE before writing — great for a "current snapshot," catastrophic if you meant to keep history. For historical rollups use Append (or Update with a Primary Key to upsert).
• Columns must match the target DE. The DE is not auto-created from your SELECT. Returned column names/types must already exist in the target, which is what Validate checks — alias everything (AS …) so names line up.
• Data Views are ~6-month rolling. _Sent, _Open, _Click, _Bounce, _Job, _Unsubscribe, etc. retain roughly 6 months (_Subscribers, _ListSubscribers, _BusinessUnitUnsubscribes are unlimited). If you need history, persist it to a retained DE — the Data View itself silently drops old rows.
• No INSERT / UPDATE / DELETE on the source. Query Activity SQL is SELECT-only. You can't mutate Data Views or any DE in the query body; the only "write" is the Data Action against the target.
• You can't SELECT to screen. There's no result grid in the activity — results must land in a DE. (Query Studio is a separate AppExchange-style tool some orgs add for ad-hoc previews; the native activity always writes to a DE.)
• Runtime cap (~30 min). A Query Activity that runs longer than roughly 30 minutes is cancelled. Probe answer: narrow the date WHERE, index via Primary Keys, avoid full-table Update checks on huge targets, and split heavy jobs into smaller scheduled passes.
• Child BU prefix. From a child Business Unit, prefix shared Data Views with Ent. (e.g., Ent._Open) — except _Job, which stays BU-specific.

➡️ Next: P04_Build_and_Send_Email.md

P04 — Build & Send an Email

🎯 Interview questions this answers: - "Walk me through building an email and sending it end-to-end." - "How do you take / pull the data for your email builds?" (the audience DE) - "In the send flow, where do you pick the audience vs. the exclusions?" - "What is a Send Classification, and when is it Commercial vs. Transactional?" - "What's a Sender Profile vs. a Delivery Profile?" - "Template vs. HTML vs. Text — when do you pick each in Content Builder?"

🧭 Where in the UI

Two distinct things happen here, in two distinct tools:

A) Build the email — Content Builder (inside Email Studio):

App Switcher (waffle, top-left)
  → Email Studio
    → Content  (top nav)  ← this is Content Builder
      → + Create  (top-right button)
        → Email          ← the message type
          → choose creation mode:
              • Template      (drag-and-drop into a layout)
              • HTML          (paste/write your own HTML + AMPscript)
              • Text Only     (plain-text fallback)
              • Existing Email (clone a previous build)
            → ⬛ Email content editor  ← YOU EDIT BLOCKS / CODE HERE

B) Send the email — the Guided Send wizard: From the open email (or from the email's row), click Send. The wizard runs four numbered steps:

1  Define Properties   → subject, preheader, From (Sender Profile),
                          Send Classification (Commercial / Transactional)
2  Select Audience     → Targeted = sendable DE / list / filtered
                          Excluded & Suppressed = exclusions/suppression
3  Configure Delivery  → send now or Schedule, throttling, tracking
4  Review and Send     → preview, fix errors, confirm, Send

Memorize the two-part split: Content Builder builds the asset; the Send wizard delivers it. The interviewer's "where do you pick the audience" lives in Step 2 (Select Audience), and "Commercial vs. Transactional" lives in Step 1 (Define Properties → Send Classification).

🖼️ Screen map

Call-outs: ① the left content tree holds your reusable Content Blocks (Free Form, HTML, Image, Button) and saved Templates — drag these onto ② the canvas, where you edit copy, personalization strings, and AMPscript. ③ the Send button launches the Guided Send wizard. In the wizard, Step 2 Select Audience is where "where do you pick the audience" is answered — Targeted vs. Excluded & Suppressed — and Step 1 Define Properties holds the Send Classification (Commercial vs. Transactional). The dotted bottom band shows the three ways the sendable DE gets populated before you ever open the wizard.

👉 Click recipe

Part A — Build the email

1. Open Content Builder: App Switcher → Email Studio → Content (top nav).
2. Click + Create (top-right) → Email.
3. Pick the creation mode on the "Create Email" screen: - Template — start from a drag-and-drop layout; best for marketers / repeatable brand emails. - HTML — paste or write your own responsive HTML; this is where I drop hand-built markup + AMPscript for full control. - Text Only — plain-text version (also auto-generated as the text fallback of any HTML email). - Existing Email — clone a prior build to save time.
4. Fill Define Email Properties: Name (internal), Subject, Preheader, and the folder to save into. Click Next.
5. Edit content on the canvas: - Drag Content Blocks from the left tree onto the layout (Image, Free Form/Text, Button, HTML). - Double-click a block to edit. For an HTML block, switch to the code view and write markup. - Add personalization: type %%FirstName%% for a profile/DE attribute, or open an AMPscript block: %%[ ... ]%% for logic (lookups, conditionals).
6. Save. Use Preview and Test (covered in P05) before you ever send.

Part B — Send the email (Guided Send wizard)

7. With the email open, click Send. The wizard opens at Step 1.
8. Step 1 — Define Properties: confirm Subject + Preheader, set the From information by choosing a Sender Profile, and choose the Send Classification (which bundles Sender Profile + Delivery Profile and sets Commercial vs Transactional). Click Next.
9. Step 2 — Select Audience: - From the All Audience Types dropdown, find your sendable Data Extension (or list / filtered DE) and drag it into the Targeted area. Watch Total Targeted populate with the row count. - Drag any audiences you want gone into Excluded and Suppressed (and/or apply a suppression list). Add CC/BCC if needed. Click Next.
10. Step 3 — Configure Delivery: choose Send Now or Schedule (date/time). Set Send Throttling if it's a large send, and confirm tracking options (Track Clicks, Retain Send Log Data). Click Next.
11. Step 4 — Review and Send: preview the rendered email, review every setting, fix any flagged errors, tick the confirmation checkbox, and click Send.

🗣️ Say this in the interview

"There are two halves: build in Content Builder, then send through the Guided Send wizard.

To build, I go Email Studio → Content → + Create → Email, and pick the mode: Template for drag-and-drop brand emails, HTML when I want full control — that's my default at GAP because I hand-write responsive HTML and drop in AMPscript for personalization and conditional content — or Text Only for the plain-text fallback. On the canvas I drag content blocks, edit copy, and add personalization strings like %%FirstName%% or AMPscript %%[ ... ]%% lookups. I always Preview and Test before sending.

To send, I hit Send and the wizard walks four steps. Step 1, Define Properties — subject, preheader, the From via a Sender Profile, and the Send Classification, which is the bundle of Sender Profile plus Delivery Profile and decides Commercial vs Transactional. Step 2, Select Audience — this is where I pick the audience: I drag my sendable Data Extension into Targeted and drop anything I want removed into Excluded and Suppressed. Step 3, Configure Delivery — send now or schedule, throttling, tracking. Step 4, Review and Send — preview, fix errors, confirm, send.

On how I take the data for the build: the audience is always a sendable Data Extension, and I build it one of three ways. Most often a SQL Query Activity in Automation Studio that joins or filters my source data into the audience DE — for example, customers who purchased in the last 30 days. Sometimes an import / File Drop of a CSV into a DE for a one-off list. And for simple segments, a Data Filter that produces a filtered DE. Whichever way, it lands in a DE that has a subscriber-key / email field and is marked sendable, so it shows up in Step 2 of the wizard."

⚠️ Gotchas / what they probe

• Audience vs. exclusions are two different boxes in one step. Both live in Step 2 (Select Audience): Targeted = who gets it, Excluded and Suppressed = who's pulled back out. Exclusions are applied on top of the targeted set at send time — saying "I filter them out in SQL" is fine, but they want to hear you also know the wizard's Excluded and Suppressed box and suppression lists.
• Send Classification = Commercial vs. Transactional, and it changes the rules. A Commercial classification enforces the unsubscribe / CAN-SPAM footer and respects the master unsubscribe list. Transactional (order confirmations, password resets) can skip the unsubscribe requirement and isn't subject to the same opt-out — abusing Transactional for marketing is a compliance trap. Classification bundles the Sender Profile (the friendly From name/address) and the Delivery Profile (IP/authentication, footer/header settings).
• Sender Profile ≠ Delivery Profile. Sender Profile = who it's from (display name + reply address). Delivery Profile = how it leaves (sending IP, SAP/authentication, header & footer). The Send Classification stitches them together so you pick one named thing.
• "Sendable" is a DE property, not a guess. A Data Extension only appears in Step 2 if it's marked Is Sendable with a relationship mapping its key (e.g., EmailAddress/SubscriberKey) to the Subscriber relationship. A correct, populated DE that isn't sendable simply won't show up — a common "why can't I select it" trap.
• Always Preview and Test before Send (P05). They probe for discipline: send a test send to a seed/test DE, validate AMPscript renders, check personalization didn't blank out, and confirm links. Test sends don't count against the audience and don't fire on the real list.
• Throttling & timing on big sends. Large sends should be throttled or scheduled off-peak; an unthrottled blast can hurt deliverability and trip the per-hour ceiling.
• Sender Authentication / footer. If the From domain isn't authenticated (SPF/DKIM via a Sender Authentication Package), deliverability tanks — a frequent follow-up to "what could go wrong on send."

➡️ Next: P05_Preview_Test_Validate.md

P05 — Preview, Test & Validate

🎯 Interview questions this answers: - "How do you test an email before you send it?" - "How do you confirm your AMPscript / dynamic content actually renders for a real subscriber?" - "It worked in preview but the live send went out blank / wrong — what happened?" - "How do you check links and tracking before a send?" - "How do you make sure it renders in Outlook and dark mode?" - "How do you verify the audience is right before sending?" - "How do you promote a tested email from QA to production?"

🧭 Where in the UI

There is no separate "test" app. Testing lives on the email itself, inside Content Builder.

App Switcher (waffle, top-left)
  → Content Builder
    → find your email in the folder tree
      → hover the row → click the ▾ down-arrow (or open the email, top-right ▾)
        → Preview and Test          ← THE TESTING SCREEN
            ├─ Subscribers   tab   → Subscriber Preview (render vs a real DE row)
            ├─ Test Send     tab   → Send Preview / Proof (up to 5 inboxes or a test DE)
            └─ Validate            → link / content checks (Content Detective, link validation)
            └─ Litmus icon         → cross-client render previews (if provisioned)

Where "Send Proof" is: it's the Test Send tab inside Preview and Test (older accounts call the button Send Proof; newer UI calls it Send Test → Confirm and Send). You enter up to five email addresses or point it at a test Data Extension, then choose whose data to merge in via Change (selected subscriber / entire list or DE / recipient test DE).

The same Preview and Test screen is reachable from inside a Journey Builder email activity and from Email Studio, but Content Builder is the canonical place to memorize.

🖼️ Screen map

Call-outs: ① Subscriber selector — pick a real row from the sendable Data Extension so personalization/AMPscript resolve against actual data. ② Rendered preview pane — the email as that subscriber sees it, with %%FirstName%%, AMPscript, and dynamic blocks already merged. ③ Render testing (Litmus / Email on Acid) for Outlook, Gmail, Apple Mail, mobile, and dark mode. ④ Validate runs link/subject checks and Content Detective (spam-trigger words). ⑤ Send Preview / Proof to up to five inboxes or a test DE, then Confirm and Send.

👉 Click recipe

Do these in order, before any production send.

1. Open Preview and Test. Content Builder → email row → ▾ → Preview and Test.
2. Subscriber Preview against a real row. On the Subscribers tab, select a subscriber from the sendable DE (call-out ①). Confirm the preview pane shows the resolved values — real first name, real order number, dynamic blocks populated — not the raw %%…%% tags or AMPscript.
3. Preview several edge-case rows. Swap the subscriber: a row with empty optional fields, a long name, a different locale/segment. This is how you catch AMPscript that only works for "happy path" data.
4. Check links and UTMs. Hover/click each link in the preview; confirm destination URLs are correct and that UTM parameters (utm_source, utm_medium, utm_campaign) are present and consistent. Run Validate so SFMC flags broken/empty links and Content Detective surfaces spam-trigger words.
5. Run render tests. Open the Litmus / Email on Acid panel and review Outlook (desktop), Gmail, Apple Mail, mobile, and dark mode. Outlook (Word rendering engine) and dark-mode color inversion are the usual breakers.
6. Verify audience row count. Before sending, check the target audience/DE record count matches the expected segment size (Email Studio → the DE → Records, or the send wizard's audience count). A count of 0 or a count 10x too big = stop.
7. Confirm the View As Web Page (VAWP) link works and that the email has a valid Subject, preheader, From name/address, and unsubscribe / physical-address footer (CAN-SPAM).
8. Send a Proof. Test Send tab → enter up to five addresses (yourself + QA) or a test DE → Change to merge real subscriber data (not generic placeholders) → Send Test → Confirm and Send.
9. Inspect the proof in a live inbox on desktop and phone — this catches things preview never will (image blocking, clipping in Gmail, footer truncation).
10. Only then send / activate. Promote from the QA Business Unit to production (see Gotchas → deployment).

✅ QA checklist

Memorize this as P-R-A-V-D: Preview, Render, Audience, Validate links, Deploy.

• [ ] Subscriber Preview run against a real sendable-DE row — personalization + AMPscript resolve (no raw %%…%%).
• [ ] Previewed edge-case rows (empty field, long value, other segment).
• [ ] Links + UTMs correct; Validate passed (no broken links, Content Detective clean).
• [ ] Render tests pass: Outlook, Gmail, Apple, mobile, dark mode.
• [ ] Audience row count matches expected segment (not 0, not wildly off).
• [ ] Subject, preheader, From, VAWP, unsubscribe + physical address all present.
• [ ] Proof sent to inboxes/test DE and opened on real desktop + mobile.
• [ ] Promoted QA BU → production via Deployment/Package Manager with correct naming + folder.

🗣️ Say this in the interview

"Before any send I open the email in Content Builder, click the ▾ and go to Preview and Test. The first thing I do is Subscriber Preview against a real row from the sendable Data Extension — that's the only way to confirm my AMPscript and dynamic content actually resolve, because the preview merges that subscriber's real data, not a placeholder. I deliberately preview a few edge-case rows too — someone with a blank optional field, a long name — because that's where AMPscript quietly breaks.

Then I Validate to catch broken links and check my UTMs are tagged consistently, and I run render tests in Litmus / Email on Acid across Outlook, Gmail, Apple Mail, mobile and dark mode — Outlook and dark-mode inversion are the usual culprits. I confirm the audience row count matches the segment we expect, then I Send a Proof to myself and QA — pulling real subscriber data, not generic placeholders — and I open it on an actual phone and desktop.

The gotcha I always watch for is 'it worked in preview but the send went out blank.' That almost always means the AMPscript referenced an attribute that exists for the preview row but is empty or missing on other subscribers, or it read from a DE the send context couldn't actually resolve. Preview shows you one row looking perfect; the live send hits every row. So I never trust a single preview — I check multiple rows and always confirm with a real proof.

At GAP, once QA signs off I promote from our QA / staging Business Unit to production using Deployment Manager (or Package Manager) so the exact tested asset moves over — never hand-rebuilt in prod — and everything follows our naming and folder governance so it's auditable."

⚠️ Gotchas / what they probe

• Preview uses ONE real row — the send hits all of them. Subscriber Preview renders against the single subscriber you picked. Looking perfect there proves nothing about the other 2 million rows. Always preview multiple/edge-case rows and send a real proof.
• "It worked in preview but the send went out blank." The classic AMPscript trap. Causes: (1) the attribute is populated for the preview row but NULL/empty for most subscribers; (2) AMPscript Lookup/AttributeValue points at a field or DE that resolves in preview context but not in send context (sendable-DE relationship / subscriber relationship missing); (3) a fallback default was never set (%%[ if empty(@fn) then ... ]%%). Fix: defensive AMPscript with defaults, and validate against rows that are missing data — not just complete ones.
• Proof ≠ real audience data unless you tell it so. On Test Send, if you don't use Change to merge real subscriber data, the proof can render with placeholders and mask exactly the personalization bug you're hunting. Always proof with a real row / test DE.
• Outlook and dark mode are the renderers that break. Outlook uses Word's engine (background images, padding, rounded corners fail); dark mode inverts colors and can hide logos/text. Subscriber Preview alone won't show these — that's what Litmus / Email on Acid is for.
• Audience count is a guardrail. A row count of 0 (broken filter / empty DE) or 10x expected (un-deduped join, wrong DE) is the cheapest catch before a send. Verify it every time.
• VAWP / unsubscribe / physical address. A missing unsubscribe link or physical mailing address is a CAN-SPAM violation; a broken View As Web Page link is an embarrassing but common miss. Validate confirms these.
• There is no true "sandbox" in SFMC. Marketing Cloud has no sandbox the way core Salesforce does — every Business Unit is production. Teams designate a QA / staging BU for testing, then promote to the production BU. Don't say "I deploy to a sandbox"; say "I test in our QA Business Unit and promote to prod."
• Deploy the tested asset — don't rebuild it. Promote QA → prod with Deployment Manager (creates a JSON snapshot of journeys/DEs/automations to import into the target BU) or Package Manager (bundles journeys/automations/campaigns). Rebuilding by hand in prod reintroduces the exact errors QA just caught. Follow naming + folder governance so the promoted asset is traceable and the right version is the one that ships.

➡️ Next: P06_Journey_Builder_UI.md

One-line summary: Validate every email by previewing AMPscript/dynamic content against a real sendable-DE row, checking links/UTMs and Outlook/dark-mode renders, confirming the audience count, sending a proof with real data, then promoting the tested asset from a QA Business Unit to production via Deployment/Package Manager — because "it worked in preview" only proves one row worked.

P06 — Journey Builder (build a journey)

🎯 Interview questions this answers: - "Walk me through building a multi-step journey in the UI — from entry source to activation." - "What's the difference between Journey Data and Contact Data?" (the frozen entry-time snapshot vs the live contact record) - "How do you reference each in a message?" ({{Event."DEkey".Field}} vs {{Contact.Attribute."Set"."Field"}}) - "Can you edit a journey that's already running?" (No — you create a new version.) - "How does a Decision Split decide the path?" (Top-to-bottom on contact attributes; first match wins.)

🧭 Where in the UI

App Switcher (waffle, top-left)
  → Journey Builder
    → Journeys  (list of all journeys)
      → Create New Journey  (top-right button)
        → Multi-Step Journey   ← pick this (not Single Send / Transactional)
          → ⬛ Journey Canvas   ← Entry Source box + drag-and-drop activities

The canvas is the workspace. The Entry Source box sits at the top-left of the flow; the activity palette runs down the left edge; you drag activities onto the canvas to the right of the entry source. Validate and Activate live in the top-right toolbar.

🖼️ Screen map

Wireframe (not a screenshot).

Call-outs: 1 Entry Source feeds contacts in (here a Data Extension). 2 Decision Split branches on a contact attribute, evaluated top-to-bottom. 3 Update Contact writes a value back to the contact record at that step. 4 The activity palette you drag from. 5 Validate, then Activate.

👉 Click recipe — build & activate an advisor-onboarding journey

1. Journey Builder → Journeys → Create New Journey → Multi-Step Journey. Rename it top-left, e.g. Advisor_Onboarding_Welcome.
2. Click the Entry Source box → pick the source: - Data Extension — contacts enter when they land in the chosen DE (batch / scheduled). You set the DE, a filter (e.g. Status = New), and an entry schedule (how often Journey Builder checks the DE for new rows). - API Event — a real-time POST from an external system fires entries (good for event-driven onboarding). You define an event definition key. - Salesforce Data — entries driven by an SFDC object via Marketing Cloud Connect; unlocks related-object data in splits. - CloudPage — a form submission on a CloudPage injects the contact.
3. Set Entry Settings — re-entry mode (No re-entry / Re-entry anytime / Re-entry only after exiting), and a contact evaluation filter so only qualifying contacts proceed.
4. Drag activities from the left palette onto the canvas, in order: - Email — pick the welcome email; map it to use Journey or Contact data (see below). - Wait — e.g. wait 2 days. - Decision Split — branch on AdvisorTier (Yes = Gold → VIP email, No = standard track). Configure the attribute (next section). - Engagement Split — branch on did-they-open/click the prior email. - Join — merge the branches back into one path so downstream steps run once. - Update Contact — stamp a value back (e.g. Onboarded = true).
5. Validate (top-right). Fix any flagged steps (unconfigured email, missing wait duration, dangling branch).
6. (Optional) Test Mode — run a small set of test contacts through the flow without sending live; confirm path logic and personalization before going live.
7. Activate. The journey goes live and contacts start entering per the entry source.

🧠 JourneyData vs ContactData

When Journey Builder injects a contact, it takes a snapshot of the entry Data Extension row — that snapshot is Journey Data and it is frozen for the contact's entire trip through the journey. Contact Data is the live contact record from the Contact Builder data model, re-read at each step.

Reference syntax in a message:

Journey Data (frozen snapshot of the entry DE):
  {{Event."DEAudience-a33c4f76-f98c-1210-8205-df62800d6778"."FirstName"}}

Contact Data (live attribute set from the contact model):
  {{Contact.Attribute."Loyalty"."Tier"}}

Contact Key shortcut:
  {{Contact.Key}}

When to use which: - Journey Data — when you want the value as it was at entry. The advisor's region, the order they placed, the tier that qualified them. Even if it later changes, the message should reflect entry-time state. - Contact Data — when the value can change mid-journey and you want the latest. A Decision Split that re-checks Tier after a Wait, or an email that should show today's points balance. - Decision Splits can read either — pick Contact Data when the branch must reflect changes that happened during the journey (e.g. after an Update Contact step), Journey Data when the branch should lock to entry-time conditions.

🗣️ Say this in the interview

"At GAP I build advisor-onboarding journeys in Journey Builder. I start with Create New Journey → Multi-Step, click the Entry Source and usually pick a Data Extension with an entry schedule — or an API Event when onboarding is event-driven. Then I drag activities onto the canvas: a welcome Email, a Wait, a Decision Split on advisor tier, sometimes an Engagement Split on open/click, a Join to merge branches, and an Update Contact to stamp the contact record. Then Validate, run a quick Test Mode pass, and Activate."

"The one I always call out is Journey Data versus Contact Data. Journey Data is a frozen snapshot of the entry DE row taken when the contact is injected — I reference it as {{Event."DEkey"."Field"}}. Contact Data is the live contact record, re-read at each step, referenced as {{Contact.Attribute."Set"."Field"}}. So if I want the tier that qualified them at entry, I use Journey Data; if I want the current tier after they might have upgraded mid-journey, I use Contact Data."

"And you can't edit a running version — if the journey is live I create a new version, build my changes there, then activate it. The old version moves to Finishing so existing members complete it, then Stopped, and new entrants pick up the new version."

⚠️ Gotchas / what they probe

• You cannot edit a running journey. The only way to change a live journey is to create a new version. Existing members keep flowing through the old version while you build; once you activate the new one, the old goes Finishing → Stopped.
• Re-entry modes matter. No re-entry (a contact can be in once), Re-entry anytime (can re-qualify while still in), Re-entry only after exiting. Pick wrong and contacts either get stuck out or double-messaged. You also can't push current members through newly added steps unless your settings allow re-entry.
• Entry DE schedule. A Data Extension entry source only injects on its schedule — contacts don't enter the instant a row appears unless the schedule runs. If "no one's entering," check the entry schedule and the entry filter first.
• Frozen vs live is a classic trap. If a personalization value looks stale, you probably used Journey Data ({{Event…}}) when you needed Contact Data ({{Contact.Attribute…}}), or vice-versa. Journey Data never updates after entry.
• Decision Split order. Paths evaluate top-to-bottom; first match wins, and a contact who matches multiple criteria takes the first one. Put the most-specific / highest-priority path on top. Up to 20 paths.
• Validate before Activate. Validation catches unconfigured emails, empty waits, and dangling branches — fix these before activation, and use Test Mode to confirm the path logic with sample contacts.

➡️ Next: P07_Automation_Studio_UI.md

P07 — Automation Studio (build an automation)

🎯 Interview questions this answers: - "Walk me through building a nightly automation in Automation Studio." - "What's the difference between a Schedule and a File Drop starting source?" - "How do you know an automation failed? Where do you watch runs?" - "What activities can you chain together, and in what order for a campaign send?" - "How does a Verification activity protect a send?" - "Run Once vs Schedule — when do you use each?" - "Automation Studio vs Journey Builder — when do you reach for which?"

🧭 Where in the UI

App Switcher (waffle, top-left)
  → Automation Studio
    → Overview  (lists all automations + their status)
      → New Automation  (top-right button)
        → ⬛ Automation canvas
             • Starting Source box  (top of canvas)  ← Schedule OR File Drop
             • Steps row            (Step 1, Step 2, … left-to-right)
             • Activities palette   (left rail — drag onto a step)

The canvas has three zones: a Starting Source box at the top (what kicks the run off), a left-to-right row of Steps (each step runs to completion before the next starts), and the Activities palette on the left that you drag activities from onto a step. A step can hold multiple activities, which run in parallel within that step — so put things that must happen in order in separate steps.

🖼️ Screen map

Call-outs: ① the Starting Source box — pick Schedule (time-based) or File Drop (fires when a file lands on Enhanced FTP). ② a Step holds one or more activities; steps run left-to-right in sequence. ③ the Verification activity sits between SQL and Send as a guard — if the audience is empty/too small/too large it halts before the email goes out. ④ Run Once tests the whole chain immediately; Schedule activates the recurring run. ⑤ the Activities palette you drag from. ⑥ the Activity Monitor tab — where you watch live runs and drill into Complete / Error / Skipped status.

👉 Click recipe

Goal: a nightly file-drop → import → SQL → verify → send automation (the classic campaign-prep pattern).

1. Pre-build the pieces you'll reference. A Staging DE (matches the CSV layout), an Audience DE (matches your SQL SELECT), and the email + send definition. Activities reference existing objects — the automation just orchestrates them.
2. Automation Studio → Overview → New Automation. Name it Nightly_Campaign_Prep.
3. Set the Starting Source (call-out ①). Click the box at the top: - Schedule → set Repeats = Daily, Time = 02:00, time zone, start/end dates. Use this when the vendor file lands on a reliable clock. - File Drop → choose the Enhanced FTP location and a filename pattern (e.g. nightly_*.csv). The automation fires the moment a matching file arrives — no clock. Use this when the upstream system's timing is unpredictable.
4. Step 1 — drag Import File from the palette (call-out ⑤). Point it at the dropped file → target Staging DE → action Overwrite (fresh load each night).
5. Step 2 — drag SQL Query. Select your saved query that reads the Staging DE (joins/segments) and writes the Audience DE. New step = runs after import finishes.
6. Step 3 — drag Verification (call-out ③). Set a rule on the Audience DE, e.g. Stop the automation if row count is less than 1 (or greater than your safety ceiling). This guards the send.
7. Step 4 — drag Send Email. Pick the email + the Audience DE as the sendable target. Because it's the last step, it only runs if Verification passed.
8. Turn on notifications. Automation settings → Notifications: add your email for Run Complete, Error, and Skip. (Skip fires when a scheduled run is missed — e.g. the previous run was still going.)
9. Save, then click Run Once (call-out ④) to dry-run the whole chain. Watch it in Activity Monitor.
10. Activate the Schedule once the test run is clean. From now on it runs nightly (or on every file drop).
11. Watch it in the Activity Monitor tab (call-out ⑥): each run shows Running → Complete / Error / Skipped; click a failed run to see which step / activity errored and the message (e.g. import column mismatch, SQL timeout).

🗣️ Say this in the interview

"For a nightly campaign-prep job I build it in Automation Studio → New Automation. First I set the Starting Source. If the vendor drops a file on a dependable clock I use a Schedule — say daily at 2 AM. If their timing is unreliable I use File Drop, which points at an Enhanced FTP folder and fires the instant a matching file lands, so I'm not guessing when to run.

Then I lay out Steps left to right — they run in sequence. Step 1 Import File loads the CSV into a staging DE with Overwrite. Step 2 a SQL Query segments that into my Audience DE. Step 3 — and this is the part I always include — a Verification activity that halts the automation if the audience is empty or unexpectedly huge, so a bad data night can never trigger a bad send. Step 4 is the Send Email, which only runs because Verification passed. I'll add File Transfer up front if I need to decrypt/move the file, or a Data Extract plus Script** at the end if I'm exporting results.

I Run Once to dry-run it, watch it in the Activity Monitor — that's where I see Running, Complete, Error, or Skipped per run and can click into the exact step that failed — and I turn on run / error / skip notifications so I'm emailed the moment anything breaks. Then I activate the schedule.

Automation Studio vs Journey Builder in one line: Automation Studio is batch / data-and-ops orchestration on a schedule (ETL, imports, SQL, scheduled sends); Journey Builder is per-contact, event-triggered, 1:1 lifecycle messaging that waits and branches on individual behavior. At GAP I use Automation Studio to prepare and feed the audience, and Journey Builder to engage each customer once they're in it."

⚠️ Gotchas / what they probe

• File Drop vs Schedule is the classic question. Schedule = time-based (cron-like; runs even if no new data). File Drop = event-based on Enhanced FTP (runs only when a matching file arrives; ignores the clock). File Drop avoids the race where your 2 AM run beats a late file. Mention Enhanced FTP and the filename pattern.
• Steps are sequential; activities inside one step are parallel. Put anything order-dependent (import then query then send) in separate steps. Two activities dropped on the same step do not guarantee order.
• Verification guards a bad send. Without it, an empty or broken import can still flow into a Send. A Verification rule (e.g. "stop if Audience DE rows < 1" or "> ceiling") halts the automation before Step 4 — this is the safety story interviewers want.
• Import Overwrite wipes the DE first. Overwrite truncates the staging DE before loading. If the new file is short or corrupt, you've lost the prior data and loaded garbage — pair it with Verification downstream, or use Append/Update when you need to retain.
• Skipped runs are silent killers. If a scheduled run is still going when the next is due, the next is Skipped (and a Wait activity mid-run causes the overlapping run to skip too). Turn on the Skip notification so you catch overlap instead of finding a missing send the next morning.
• Run Once ≠ Scheduled. Run Once executes the chain immediately for testing but does not activate the schedule. You must explicitly activate the schedule, or it silently never recurs.
• Activity Monitor is the answer to "how do you know it failed." It lists every run with Complete / Error / Skipped, lets you drill into the failing step + activity and read the error, and is where you'd pause, skip the next occurrence, or stop a running automation. Pair it with run / error / skip email notifications — don't rely on logging in to check.
• ~30-min activity / runtime caps still apply. A SQL Query or import that runs too long is cancelled and surfaces as an Error in the monitor — narrow date ranges, index with primary keys, split heavy jobs.

➡️ Next: P08_Contact_Deletion.md

P08 — Contact Deletion (GDPR erasure)

🎯 Interview questions this answers: - "How do you do a Contact Deletion in Marketing Cloud?" - "How do you handle a GDPR 'right to be forgotten' / right-to-erasure request?" - "What's the difference between unsubscribing someone, deleting a row in a Data Extension, and a Contact Delete?" - "What is the suppression period and why does it matter?" - "Does a Contact Delete affect one Business Unit or all of them?" - "What is a Data Retention Policy on a Data Extension?"

🧭 Where in the UI

There are two breadcrumbs — one to turn the feature on (once, by an admin), one to actually run a deletion.

1. Enable it once (admin, at the parent/top Business Unit):

Audience Builder ▸ Contact Builder ▸ Contacts Configuration (top menu)
   └─ "Contact Delete" section ▸ enable ▸ "Manage Settings"
        └─ set the Suppression Period (default = 2 days; set 0 for immediate)

2. Run a deletion (any time, after it's enabled):

Audience Builder ▸ Contact Builder ▸ All Contacts (top menu)
   └─ trash 🗑️ icon ▸ choose the source of contacts to delete ▸ confirm "Delete"

Heads-up: Contacts Configuration is where you flip the toggle and set the delay. All Contacts is where the trash-can deletion flow lives. People mix these up in interviews — enable lives in Configuration, run lives in All Contacts.

🖼️ Screen map

Wireframe (not a screenshot).

Call-outs: 1. Delete Contacts toggle — in Contacts Configuration; an admin turns this ON once before anyone can delete. 2. Suppression Period — the delete delay. Default 2 days; set 0 to make the request eligible immediately. 3. Trash icon — opens the Delete Contacts dialog from the All Contacts screen. 4. Source selector — pick the audience to erase: a List, a Data Extension of contact keys, or a Filtered set. 5. Delete — confirm; the contacts go into the suppress → queue → hard-delete pipeline.

👉 Click recipe

A. Enable once (admin, do this on the parent / top BU):

1. Audience Builder ▸ Contact Builder ▸ Contacts Configuration.
2. Find the Contact Delete section and turn Delete Contacts ON.
3. Click Manage Settings and set the Suppression Period — leave the 2-day default, or set 0 days if you want erasure to start immediately.
4. Save. (This is a one-time setup; you don't repeat it per deletion.)

B. Run a deletion (every time you need to erase someone):

5. Audience Builder ▸ Contact Builder ▸ All Contacts.
6. Click the trash 🗑️ icon without selecting individual rows → choose Delete contacts from list / data extension (or select specific contacts and choose Delete selected contacts).
7. Pick the source: a List, a Data Extension holding the contact keys to delete, or a Filtered set. (A DE of keys is the usual pattern for a batch GDPR request — up to ~1 million per operation.)
8. Optionally choose whether the source DE itself is kept or removed after the run.
9. Click Delete Contacts, then review and confirm by clicking Delete.

C. What happens next (you don't click anything — it's automatic):

10. Suppress — the contacts are immediately hidden and blocked everywhere (Contact Builder, Email Studio, Journey Builder, Mobile Studio, Einstein) for the suppression window.
11. Queue (async) — the request enters a queue that processes one deletion at a time; a run can take several hours.
12. Hard delete — once the window passes and all related data is identified, the contact and its related data are permanently erased account-wide across every Business Unit that shares the account's ClientID.

🗣️ Say this in the interview

"Contact Deletion is the GDPR right-to-erasure tool, and it's a two-part thing.

First, enable it once. As an admin at the parent BU I go to Contact Builder ▸ Contacts Configuration, turn on Delete Contacts, and set the Suppression Period — it defaults to 2 days, and I usually set it to 0 if I want the erasure to start right away.

Then, to run it, I go to Contact Builder ▸ All Contacts, click the trash icon, and choose my source — typically a Data Extension that holds the contact keys I need erased, but it can also be a List or a filtered set. I confirm with Delete.

What happens then is suppress-then-delete: the contacts are immediately suppressed — hidden and blocked across Email, Journey Builder, Mobile, and Einstein — and the actual erase request goes into an asynchronous queue that processes one at a time and can take hours. After the suppression window it hard-deletes the contact and all its related data. And critically it's account-wide — it erases across every Business Unit under the account, not just the one I'm in.

I always contrast the three kinds of 'remove': an unsubscribe only changes a status — the data's still there; deleting a row in a Data Extension removes them from that audience only; and a Contact Delete is true account-wide erasure. For GDPR you need the third one."

⚠️ Gotchas / what they probe

• Irreversible. Once it hard-deletes, the contact and related data are gone permanently — there's no undo. Make sure the key list is correct before confirming.
• Suppression delay is a window, not the deletion. During the suppression period the contact is only hidden/blocked. The default is 2 days; set 0 to make the request immediately eligible. Until the window passes, nothing is actually erased.
• It affects ALL Business Units. In Enterprise 2.0, the delete runs from the parent and applies account-wide to every BU sharing the ClientID. You cannot selectively delete a contact from just one BU with this tool — it's all or nothing across the account.
• Asynchronous / queued. Requests process one at a time and can take several hours; don't expect instant removal. Don't fire dozens of separate requests expecting parallel processing.
• Watch BU-specific leftovers. Triggered sends, synchronized data extensions, and query/filter outputs in child BUs can still hold copies — verify those separately if you need a truly clean account.
• Difference vs unsubscribe / row delete (they love this one):
• Unsubscribe = changes status only (still in the system; suppressed from sends).
• Delete a DE row = removes from that audience/table only; contact record survives elsewhere.
• Contact Delete = account-wide erasure of the contact and related data. Only this one satisfies GDPR right-to-erasure.
• Per-DE Data Retention Policy is a different lever. Set on a Data Extension (often on step 2 when creating it), it auto-cleans data on a schedule: delete individual records older than N days, delete all records (keep the DE shell), or delete the entire Data Extension. Max period is 730 days (2 years), and anything that ages out is permanently deleted — there's no recovery. With no policy set, data stays forever. This is housekeeping/compliance hygiene; it is not a substitute for a Contact Delete, because a DE policy only touches that one table, not the account-wide contact record.

➡️ Next: P09_Reports_and_Broader_Engagement.md

P09 — Reports & Broader Engagement

🎯 Interview questions this answers: - "What are the broader engagement reports in Marketing Cloud and where do you find them?" - "How do you access and run an email engagement report for a date range, then export it?" - "How do you verify that the platform stores 730 days of engagement data — and what do you do when you need more history?"

🧭 Where in the UI

You answer "broader engagement reports" by pointing at three distinct places — name them in order from most-packaged to most-custom:

1. Analytics Builder ▸ Reports — the standard report catalog. Pre-built email engagement reports you run, filter by date range, schedule, and export. This is the literal answer to "broader engagement reports." - Breadcrumb: App Switcher (top-left waffle) ▸ Analytics Builder ▸ Reports
2. Email Studio ▸ Tracking — per-send and aggregate metrics (Sent / Opens / Clicks / Bounces / Unsubs) for an individual job or rolled up across the account. - Breadcrumb: App Switcher ▸ Email Studio ▸ Email ▸ Tracking
3. Analytics Builder ▸ Discover — drag-and-drop custom reports when the standard catalog isn't enough. And below that, Data Views via SQL (in Automation Studio Query Activities) for fully custom engagement history. - Breadcrumb: App Switcher ▸ Analytics Builder ▸ Discover (custom) / Automation Studio ▸ SQL Query Activity (Data Views)

The single most common miss: candidates say "Email Studio Tracking" and stop. "Broader engagement reports" specifically means the Analytics Builder ▸ Reports catalog — reports that span many sends, not one job.

🖼️ Screen map

Wireframe (not a screenshot).

Call-outs: 1. Report catalog (left rail) — pick a standard engagement report; Email Performance Over Time is the workhorse "broader" one. 2. Date-range control — the filter that scopes the report; how far back it lets you go is your live proof of the retention window. 3. Run / Schedule / Export — run on demand, schedule a recurring emailed run, or export to Excel / CSV / PDF. 4. 730-day boundary — the earliest date you can select lands ~2 years ago; data older than that is no longer queryable here.

👉 Click recipe

A. Run a broader engagement report for a date range and export it

1. Top-left App Switcher (waffle) ▸ Analytics Builder ▸ Reports.
2. In the Report Catalog (left), select Email Performance Over Time (or Account Send Summary for account-level totals).
3. Set the Date Range control to your window (e.g., last 90 days, or a custom From/To).
4. Optionally filter by send/email or business unit, then click Run.
5. Click Export and choose a format (Excel / CSV / PDF), or click Schedule to have it emailed on a recurring cadence.

B. Confirm the 730-day retention window (the "verify" move)

6. In any engagement report, open the Date Range picker and drag the From date as far back as it allows.
7. The earliest selectable / returnable date lands at roughly today − 730 days. Anything older returns no rows — that is the retention limit, observable in the UI.
8. Cross-check the same boundary in Email Studio ▸ Tracking (older jobs drop off the same 730-day cliff) and confirm against the Salesforce policy effective June 16, 2025.

🗣️ Say this in the interview

"The broader engagement reports live in Analytics Builder ▸ Reports — that's the standard catalog. The one I reach for is Email Performance Over Time, plus Account Send Summary and Email Performance by Attribute / Domain. I select the report, set a date range, click Run, then Export to Excel/CSV/PDF or Schedule it to email out on a cadence. For a single send I use Email Studio ▸ Tracking for sent/open/click/bounce/unsub, and for anything custom I go to Discover or query the Data Views with SQL in Automation Studio.

On retention: since the June 16, 2025 policy, Tracking and the Analytics Builder engagement reports keep 730 days — two years of subscriber engagement data. Data Views are only ~6 months. I verify the 730-day window by walking the date-range picker back: the earliest date it'll return is about today minus 730 days, and older data just isn't accessible. At GAP, because campaign-level history outlives two years, the senior move is to persist engagement to a Reporting Data Extension — a scheduled SQL Query Activity copying _Open, _Click, _Sent, _Bounce, _Unsubscribe data views into a DE — so we keep history well past the 730-day and 6-month limits and own our long-range reporting."

⚠️ Gotchas / what they probe

• 730 days, not 6 months, not 180 days. The modern correct answer for Tracking + Analytics Builder Reports engagement data is 730 days (2 years), effective June 16, 2025. (The earlier-announced 180-day change was superseded.) Saying "6 months" here is the trap.
• Data Views are the ~6-month figure — that's _Open, _Click, _Sent, _Bounce, _Job, etc. via SQL. Don't conflate the two retention numbers: Reports/Tracking = 730 days, Data Views ≈ 6 months.
• Intelligence Reports for Engagement already retain 2 years and Data Extensions are admin-managed — both are excluded from the June 2025 change. Mention this if probed on scope.
• Opens are unreliable. Apple Mail Privacy Protection (MPP) pre-fetches images, inflating open rates. Lead with clicks / click-to-open as the trustworthy engagement signal.
• Persist for longer history. The expected senior answer: data past 730 days (or 6 months for Data Views) is gone unless you export it out — a scheduled Query Activity into a Reporting DE, or an Automation Studio export to SFTP. Owning your own history is the GAP pattern.
• "Verify" means show, not recite. They want you to demonstrate it in the UI (walk the date picker to the ~730-day floor) and cite the Salesforce policy — not just quote a number.

➡️ Next: P10_CloudPage_Form_to_Email.md

P10 — CloudPage Form → DE → Email

🎯 Interview questions this answers: - "How will you take information from a CloudPage form into an email? Which AMPscript/SSJS function inserts the data into SFMC?" - "InsertDE vs InsertData — what's the difference and when do you use each?"

One-line answer: A CloudPage form self-posts → you read the fields with RequestParameter("field") → you write them to a Data Extension with UpsertData (the CloudPage-context function that returns rows affected) → a later email reads them back with Lookup / LookupRows keyed on SubscriberKey/email. On a CloudPage you use the *Data family; in an email you'd use the *DE family.

🧭 Where in the UI

Breadcrumb: App Switcher (waffle) ▸ Web Studio ▸ CloudPages

Web Studio
 └─ CloudPages
     └─ [+ Create]  →  Collection            ← a "Collection" is just a folder/campaign bucket
          └─ Open the Collection
               └─ [Create] ▸ Landing Page    ← your form page (HTTPS, public URL)
                    └─ Content tab
                         ├─ HTML Block        ← paste your <form> + AMPscript here (full control)
                         └─ Smart Capture Block ← drag-drop form bound to a DE (no-code option)

• The form, the RequestParameter reads, and the UpsertData write all live in ONE Landing Page — an HTML Content Block that posts to itself.
• Two ways to build the form:
• Smart Capture block — drag it on, point it at a target Data Extension, map DE columns → input fields. SFMC writes the rows for you. Fast, but limited logic.
• HTML block + AMPscript — you write the <form>, read fields, and call UpsertData yourself. This is the version interviewers want to see because you control validation, keys, and de-dupe.
• The Data Extension itself is created over in Email Studio ▸ Subscribers ▸ Data Extensions (or Contact Builder). The CloudPage just references it by name/key.
• The email that reads the data back is built in Email Studio / Content Builder — its Lookup runs at send time.

🖼️ Screen map

Wireframe (not a screenshot).

👉 Click recipe

1. Create the DE first. Email Studio ▸ Subscribers ▸ Data Extensions ▸ Create. Name it Form_Submissions. Add columns: EmailAddress (Email, Primary Key), FirstName, Product, SubmittedDate. Set the primary key to EmailAddress so upserts de-dupe on it.
2. Create the page. Web Studio ▸ CloudPages ▸ Create ▸ Collection (e.g. Acquisition) ▸ open it ▸ Create ▸ Landing Page.
3. Set properties. Name it, pick your domain from the URL dropdown, tick HTTPS only.
4. Build the form. Drag an HTML Block onto the canvas and paste the form + AMPscript (code below). (Or drag a Smart Capture Block, point it at Form_Submissions, and map fields — no-code alternative.)
5. Add the AMPscript write-back. At the top of the same block, detect a POST (RequestParameter), validate, and call UpsertData.
6. Publish. Click Publish (top right) — a draft page does NOT serve a live URL. Copy the published URL.
7. Test. Open the URL, submit the form, then check Form_Submissions in Email Studio — the row should appear. Resubmitting the same email should update, not duplicate (proves the key works).
8. Read it back in an email. Content Builder ▸ create/open an email ▸ in an HTML block add the Lookup (code below) keyed on the subscriber's email ▸ preview against a test subscriber whose email exists in the DE ▸ send.

💻 Code

(a) CloudPage — form + RequestParameter + UpsertData write-back

%%[
  /* Run only when the page was POSTed to itself */
  SET @submitted = RequestParameter("submitted")

  IF @submitted == "1" THEN
    /* 1. Read the posted fields */
    SET @email     = RequestParameter("email")
    SET @firstName = RequestParameter("firstName")
    SET @product   = RequestParameter("product")

    /* 2. Validate + encode before trusting anything */
    IF NOT EMPTY(@email) AND IndexOf(@email, "@") > 0 THEN
      SET @firstName = HTMLEncode(@firstName)
      SET @product   = HTMLEncode(@product)

      /* 3. Write to the DE. UpsertData RETURNS the rows affected. */
      SET @rows = UpsertData(
        "Form_Submissions", 1,
          "EmailAddress",  @email,        /* 1 key column → de-dupes on email */
          "FirstName",     @firstName,    /* update/insert columns follow */
          "Product",       @product,
          "SubmittedDate", Now()
      )
      SET @thanks = Concat("Thanks ", @firstName, " — saved (", @rows, " row).")
    ELSE
      SET @error = "Please enter a valid email."
    ENDIF
  ENDIF
]%%

%%[ IF NOT EMPTY(@thanks) THEN ]%%
  <p>%%=v(@thanks)=%%</p>
%%[ ELSE ]%%
  %%[ IF NOT EMPTY(@error) THEN ]%%<p style="color:red">%%=v(@error)=%%</p>%%[ ENDIF ]%%
  <form method="post" action="%%=RequestParameter('PAGEURL')=%%">
    <input type="hidden" name="submitted" value="1">
    <label>Email <input type="email" name="email" required></label>
    <label>First name <input type="text" name="firstName"></label>
    <label>Product <input type="text" name="product"></label>
    <button type="submit">Sign up</button>
  </form>
%%[ ENDIF ]%%

🔍 Line by line: - SET @submitted = RequestParameter("submitted") — reads the hidden field. On first GET it's empty (show the form); after POST it's "1" (process the write). This is how one page handles both display and submit. - RequestParameter("email" / "firstName" / "product") — pulls each posted form field by its name attribute. RequestParameter reads POSTed form fields (and also CloudPagesURL-encrypted params); for plain ?key=value GET strings you'd use QueryParameter. - IF NOT EMPTY(@email) AND IndexOf(@email, "@") > 0 — minimal server-side validation. Never trust the browser; always re-check on the page. - HTMLEncode(@firstName) — output/encoding defense. Stops a submitted <script> from being reflected back into the page (XSS). Encode anything that came from the user before you store or echo it. - UpsertData("Form_Submissions", 1, "EmailAddress", @email, ...) — the write. Arg shape: DE name, then number of key columns (1), then the key column/value pair(s), then the update column/value pairs. If a row with that EmailAddress exists it's updated; otherwise a new row is inserted — that's the "upsert." - SET @rows = UpsertData(...) — the whole point of the *Data family: it returns the number of rows added/updated. You can branch on it, log it, or show a confirmation. (InsertData similarly returns 1 on success.) - Now() — server timestamp for SubmittedDate. - action="%%=RequestParameter('PAGEURL')=%%" — posts the form back to this same page, so the AMPscript above handles it. PAGEURL is the current published page URL. - %%=v(@thanks)=%% — renders the confirmation string. The IF @thanks / ELSE structure shows the thank-you after a successful write, otherwise re-renders the form (with an error if validation failed).

(b) Email — Lookup read-back at send time

%%[
  /* In an email, the recipient's address is in the system var. */
  SET @sk = AttributeValue("_subscriberkey")   /* or emailaddr */

  /* Single value: */
  SET @product = Lookup("Form_Submissions", "Product", "EmailAddress", emailaddr)

  /* Multiple columns / rows: */
  SET @rows = LookupRows("Form_Submissions", "EmailAddress", emailaddr)
  IF RowCount(@rows) > 0 THEN
    SET @row  = Row(@rows, 1)
    SET @fname = Field(@row, "FirstName")
  ENDIF
]%%

<p>Hi %%=v(@fname)=%%, here's more on %%=v(@product)=%% you asked about.</p>

🔍 Line by line: - AttributeValue("_subscriberkey") / emailaddr — system values available in the email send context, identifying the recipient. You key your lookup off the recipient, never off a value the user could tamper with. - Lookup("Form_Submissions", "Product", "EmailAddress", emailaddr) — returns one column value from the first matching row: "give me Product where EmailAddress = this recipient." Perfect for a single field. - LookupRows("Form_Submissions", "EmailAddress", emailaddr) — returns the whole rowset matching the WHERE. Use when you need several columns or multiple rows. - RowCount(@rows) > 0 — guard so you don't read from an empty set (a recipient who never submitted). - Row(@rows, 1) then Field(@row, "FirstName") — pull row #1, then extract a named column from it. - Net effect: the email is personalized from what the subscriber typed on the CloudPage, because both sides share the Form_Submissions DE keyed on email.

*Data (CloudPage) vs *DE (email) — the function families

Lookup/LookupRows work in both contexts; the insert/upsert split is the one that matters.

🗣️ Say this in the interview

"It's a round-trip through a Data Extension. On the CloudPage — built under Web Studio ▸ CloudPages as a Landing Page — I have an HTML form that posts to itself. On submit I read each field with RequestParameter, validate and HTMLEncode them, then write the row with UpsertData — keyed on email so it de-dupes instead of creating duplicates. I use UpsertData specifically because it's the CloudPage-context function and it returns the rows affected, so I can confirm the write and show a thank-you. Then the next email personalizes off that same DE: at send time I use Lookup or LookupRows keyed on the recipient's SubscriberKey or email to pull back exactly what they submitted. So the data the subscriber typed on the page flows form → UpsertData → DE → Lookup → email."

On InsertDE vs InsertData: "Same operation, different context. InsertDE/UpsertDE are the email-context functions — they run at the completion of the send and don't return a usable value. InsertData/UpsertData are the CloudPage / landing-page / SMS functions and they return the number of rows affected, which is why on a CloudPage I always reach for the *Data family — I get a return value I can branch on, and it runs inline when the page is hit rather than deferred to send completion."

(GAP context: I've used exactly this pattern for preference-center and acquisition pages — capture on a CloudPage, write to a DE, then drive the welcome/journey email off Lookup.)

⚠️ Gotchas / what they probe

• *Data returns, *DE doesn't. This is the #1 distinction they're testing. UpsertData/InsertData give you a rows-affected count you can act on; UpsertDE/InsertDE return nothing usable and the insert is processed at send completion (and is skipped if a RaiseError aborts the send). Use *Data on CloudPages, *DE in emails.
• RequestParameter vs QueryParameter. RequestParameter reads POSTed form fields and CloudPagesURL-encrypted params; QueryParameter reads plain GET ?key=value strings. A self-posting form uses POST → RequestParameter.
• IDOR / don't trust a raw key. Never accept a subscriber key or email straight from a query string and use it to write or read someone else's record. Validate, and when you link from an email to a CloudPage pass identifiers through CloudPagesURL so they're delivered as encrypted/signed params (then read with RequestParameter) — not as plaintext the user can edit.
• Always validate + encode. Server-side check required fields and HTMLEncode user input before storing/echoing it to block reflected XSS. Browser-side required is not security.
• Publish vs draft. A draft Landing Page has no live URL — the form 404s until you click Publish. Re-publish after edits.
• Primary key drives upsert. If EmailAddress isn't the DE's primary key, UpsertData can't match and you'll get duplicate rows instead of updates.
• Smart Capture vs HTML+AMPscript. Smart Capture is no-code but limited; for custom validation, de-dupe logic, and a confirmation off the return value, write the HTML form + UpsertData yourself.

➡️ Next: P11_Deliverability_and_IP_Warming.md

P11 — Deliverability & IP Warming

🎯 Interview questions this answers: - "How do you do IP warming?" - "A client just moved to a dedicated IP — what's your plan to protect deliverability?" - "Where do sender, authentication, and delivery settings live in SFMC, and how do they tie into reputation?"

One-line answer: IP warming = you gradually build sender reputation on a cold/new dedicated IP by starting with low volume to your most engaged subscribers, then ramping daily over ~2–4+ weeks on a fixed schedule while watching bounces, complaints, and Postmaster/SNDS — and backing off the moment reputation dips. ISPs distrust a brand-new IP that suddenly blasts millions; a slow, consistent, engagement-first ramp teaches them your mail is wanted.

🧭 Where in the UI

The auth + sender plumbing (one-time setup):

Setup (gear, top-right)
 └─ Settings
     └─ Company Settings
          └─ Sender Authentication Package (SAP)   ← your dedicated IP(s), private/branded domain,
              ├─ Dedicated IP address(es)               SPF, DKIM, Reply Mail Management, link wrapping
              ├─ Authenticated sending domain (DKIM/SPF)
              └─ Reply / bounce domain

Email Studio  ▸  Admin
 ├─ Send Classifications     ← bundles a Sender Profile + Delivery Profile + CAN-SPAM/commercial-vs-transactional
 ├─ Sender Profiles          ← the From name / From address subscribers see
 └─ Delivery Profiles        ← which IP/IP-pool + header/footer the send goes out on

• The Sender Authentication Package (SAP) is the paid add-on that gives you a dedicated IP plus a branded/private domain with SPF + DKIM aligned to your domain (not the shared *.mcsv.net space). This is what you warm. Without a SAP you're on a shared IP — reputation is pooled across many senders and there's nothing for you to warm.
• A Send Classification is the dropdown you pick at send time. It ties together a Sender Profile (the visible From) and a Delivery Profile (the technical "how/where it leaves"), and flags the send as Commercial vs Transactional for CAN-SPAM (transactional skips the unsubscribe/commercial footer).
• The Delivery Profile is where the dedicated IP / IP pool is bound to the send. During warming you route through the IP you're warming; if you have multiple IPs you can split traffic across a pool here.
• Authentication ≠ warming. SPF/DKIM/DMARC + branded domain prove who you are (anti-spoofing). Warming builds trust in your sending pattern. You need both — auth is table stakes; a perfectly-authenticated cold IP that blasts will still get throttled.

🖼️ Screen map

Wireframe (not a screenshot).

📈 The warm-up ramp

A sample schedule — always tune the numbers to your list size, engagement, and how each ISP responds. The shape matters more than the exact figures: small start, steady daily increases, engaged-first, widen the audience window as reputation builds.

Rules of thumb that go with the table: - Roughly double (or +40–100%) per step, but only if yesterday's bounces/complaints stayed clean. - Keep volume consistent and rising — don't do 500 one day then nothing for a week; erratic patterns read as suspicious. - Cap a single IP at ~2–2.5M/day even once warm; add another dedicated IP per additional ~2M/day rather than overloading one. - A typical warm takes ~30 days of desirable sending history before ISPs fully trust the IP; larger/colder lists take longer.

👉 In practice

The step-by-step I'd actually run:

1. Confirm the foundation is live. Setup ▸ Company Settings ▸ Sender Authentication Package provisioned: dedicated IP assigned, branded sending domain with SPF + DKIM validated, DMARC published on the domain, link/reply domains aligned. Verify with a seed send + header check before any warming. (No SAP = shared IP = nothing for you to warm.)
2. Wire up the send config. Create a Sender Profile (From name/address on the branded domain), a Delivery Profile pointing at the dedicated IP/pool you're warming, and a Send Classification that bundles them and marks the stream Commercial.
3. Build the engaged-first segments. Query/segment your most-engaged subscribers — opened or clicked in the last 30 days — as your Day-1 audience. Prepare wider tiers (60-day, 90-day) for later weeks. Suppress hard bounces, unsubscribes, role addresses, and anything stale.
4. Build the ramp calendar. Lock the daily volumes (table above) into a sending calendar. Pick high-engagement, relevant content for the warm-up sends — these need strong open/click rates to signal "wanted mail."
5. Send Day 1 low and watch. Send the smallest volume to the hottest segment. Don't move to Day 2 numbers until you've seen the results.
6. Monitor every single day. Check Google Postmaster Tools (Gmail reputation, spam rate, auth pass), Microsoft SNDS / SNDR (Outlook/Hotmail), Sender Score, plus SFMC's own bounce rate, spam-complaint rate, deferrals/421 errors, and open rate.
7. Ramp only on green. If yesterday looked clean, step up per the calendar. If bounces/complaints/deferrals spike or reputation dips — hold or roll the volume back to the last good level, let it stabilize, then resume more slowly. Throttle per-ISP independently (Gmail might be happy while Outlook defers).
8. Reach steady state, then keep it steady. After ~2–4+ weeks you're at full volume. Maintain a consistent daily cadence — a warmed IP that goes quiet for weeks cools off and effectively needs re-warming.

🗣️ Say this in the interview

"IP warming is how you build sender reputation on a new dedicated IP before you send at full scale. A brand-new IP has no history, so ISPs treat it with suspicion — if you blast millions on day one you get throttled, deferred, or blocked. So I warm it gradually.

First I make sure the foundation's there: in Setup ▸ Company Settings, the Sender Authentication Package gives us the dedicated IP and a branded domain with SPF and DKIM, and we publish DMARC. Then I set up the Sender Profile, Delivery Profile, and Send Classification in Email Studio Admin so the send routes out on that IP.

The warming itself: I start low — a few hundred to a couple thousand a day — to our most-engaged subscribers, people who opened or clicked in the last 30 days, because their opens and low complaints tell the ISP this mail is wanted. Then I ramp the daily volume gradually over about two to four weeks, roughly doubling each step, and I widen the engagement window as I go — 60-day, then 90-day, then the full list. The whole time I'm monitoring bounces, spam complaints, deferrals, and reputation in Google Postmaster Tools and Microsoft SNDS. If reputation dips or complaints rise, I back off — hold or drop volume to the last clean level, let it settle, then resume more slowly. The keys are: engaged-first, gradual and consistent ramp, monitor daily, back off on any dip."

(Financial-services angle): "In financial services this matters even more — the mail is sensitive and time-critical, statements, security and account alerts, so deliverability has to be near-perfect and inbox placement consistent. I'd warm conservatively, separate transactional and promotional streams so a marketing complaint never poisons the reputation that delivers a security alert, and keep DMARC enforced to prevent spoofing of a trusted financial brand."

⚠️ Gotchas / what they probe

• Cold blast = throttling/blocks. The #1 thing they're checking you understand: send big from a fresh IP and ISPs defer (421/4xx), throttle, or hard-block you, tanking the reputation you were trying to build. Warming exists precisely to avoid this.
• Warm by engagement, not by random list slices. Start with your most-engaged subscribers — high opens/clicks and near-zero complaints are the positive signals that build reputation. Warming on a stale or purchased list does the opposite.
• Authentication ≠ reputation. SPF/DKIM/DMARC and a branded domain prove identity and stop spoofing, but they don't earn inbox placement on their own. A fully-authenticated cold IP still has to be warmed.
• Domain reputation matters too — and it's portable. ISPs track reputation at the sending-domain level as well as the IP. If you change IPs but keep the domain, some domain reputation carries; conversely a damaged domain hurts even a warm IP. Warm and protect both.
• Consistency beats bursts. Erratic volume (500 today, nothing for a week, then 50k) reads as suspicious and breaks the warm. Keep a steady, rising daily cadence — and once warm, don't let the IP go idle or it cools.
• Shared vs dedicated. On a shared IP you don't warm anything — reputation is pooled. Warming only applies once you've got a dedicated IP via the SAP; clarify which the client has before promising a plan.
• Watch the right signals, per ISP. Don't rely on open rate alone (Apple MPP inflates it). Use Postmaster Tools (Gmail), SNDS (Microsoft), Sender Score, plus bounce/complaint/deferral rates — and throttle per-ISP, since Gmail and Outlook can react very differently to the same volume.
• Separate your streams (esp. financial services). Keep transactional mail (statements, security/account alerts) on a different IP/classification from promotional blasts, so a marketing-complaint spike can't degrade the reputation that delivers critical, sensitive messages.
• Single-IP ceiling. Even fully warmed, keep a single IP under ~2–2.5M sends/day; add IPs for more volume rather than overloading one.

➡️ Next: P12_Troubleshooting_and_Ops.md

P12 — Troubleshooting & Day-to-Day Ops (the questions you got)

🎯 Interview questions this answers: - "A customer says they didn't receive the email, but I can see the new email address IS in the target Data Extension — why didn't it go to the new address?" - "Lookup vs LookupRows vs LookupOrderedRows — what's the difference and when do you use each?" - "How do you raise an error / stop a send in AMPscript?" - "How do you save historical data of customers in SFMC?" - "Talk about your projects."

One-line answer: Most "they didn't get it" tickets are a subscriber-record problem, not a DE problem — the send email is stored on All Subscribers keyed by SubscriberKey, and for a DE send keyed on SubscriberKey, SFMC sends to the email already on that subscriber record (or a Held/Bounced/Unsub status suppresses them) — the new email in the DE only seeds brand-new subscribers. The rest is knowing your Lookup family (single value / unordered rowset / ordered+limited rowset, 2,000-row cap), RaiseError to guard a send, and a nightly Query Activity to retain Data View data before it ages out (Data Views ~6 months, Tracking 730 days).

🖼️ RCA decision tree

Wireframe (not a screenshot) — layout reflects the live SFMC UI.

Wireframe (not a screenshot) — layout reflects the live SFMC UI.

🔧 Q&A — theory + in practice

Q1. "Customer didn't receive the email, but the new address IS in the target DE — why?"

Theory. This is the single most-asked SFMC troubleshooting question, and the trap is assuming the DE is the source of truth for the email address. It is not. SFMC stores subscribers at the All Subscribers level, keyed by SubscriberKey, and the email address lives on that subscriber record (plus the _Subscribers profile data). For a Data Extension send whose send relationship is SubscriberKey, SFMC matches each DE row to an existing All Subscribers record by key and sends to the email already on that record. The email address in the DE is only used to create a brand-new subscriber the first time that key is seen — it does not overwrite the address on an existing subscriber. So: new address in the DE, old (or wrong) address on the subscriber record → the mail goes to the old address (or nowhere useful), and the ticket says "didn't receive it."

That's the headline cause. Walk the rest of the RCA tree (above) in order:

1. Status suppression. Even with a perfect address, if the All Subscribers status is Held, Bounced, or Unsubscribed, SFMC silently suppresses the send. They were never mailed.
2. Stored email ≠ DE email (the headline cause) — send keyed on SubscriberKey used the record's old address.
3. Wrong / stale audience. The send went out against an older DE or filtered audience that didn't include the corrected row — or the EmailAddress field wasn't mapped as the send's email field, so the address was empty/blank.
4. It actually sent. Check Tracking — if it shows in _Sent and then _Bounce, the address left SFMC and the problem is downstream (mailbox full, hard bounce, spam folder).

…and in practice I'd: - Open Email Studio ▸ Subscribers ▸ All Subscribers, search by the customer's SubscriberKey (not email). On the record I check two things: the Status (Active vs Held/Bounced/Unsub) and the stored Email Address. If the stored email is the old one, that's the smoking gun — the DE's new email never took effect. - If status is Held/Bounced/Unsubscribed, that's the cause — they were suppressed. (For Unsub I cannot just flip it back; the contact must re-opt-in / re-subscribe through a legitimate flow.) - Confirm which audience was sent: open the Job in Tracking (or the email's send definition) and verify it used the corrected DE and that the EmailAddress field is mapped as the send email field. - Check Tracking ▸ the Job ▸ _Sent / _Bounce (or the _Sent, _Bounce data views via a Query): did it send and bounce? Hard vs soft bounce tells me address-dead vs temporary.

The fix (matched to the cause): - Stored email is stale → update the email on the subscriber record before sending (manual edit in All Subscribers, an import that updates the profile, or a sync process — at GAP I built a WSProxy-based tool exactly to reconcile DE values against the subscriber record). Updating the DE alone does not fix it. - Want the DE email to win going forward → send via a Journey (Journey Builder honors the DE email when entering contacts), or send to a list-based / properly configured send where the address is taken from the DE field, or have Salesforce support enable the override that lets a sendable DE's EmailAddress update the record at send time. Be careful: silently changing send-relationship behavior org-wide has side effects. - Held record → clear the Held status (it auto-clears after the held period, or you re-validate); Unsubscribed → legitimate re-opt-in only.

Q2. Lookup vs LookupRows vs LookupOrderedRows

Theory. All three read from a Data Extension, but they differ in what they return and whether you control order:

All three are subject to the 2,000-row cap: LookupRows returns at most 2,000 rows, and LookupOrderedRows returns at most 2,000 when its rowcount argument is < 1 (meaning "all rows, capped at 2,000"). If you pass an explicit number greater than 2,000 you can exceed it, but the practical guidance is: don't lean on AMPscript to page huge sets — that's a SQL Query Activity's job.

%%[
  /* Lookup → ONE value from the first matching row (unordered) */
  SET @firstName = Lookup("Customers", "FirstName", "SubscriberKey", _subscriberkey)

  /* LookupRows → a rowset, unordered */
  SET @orders = LookupRows("Orders", "SubscriberKey", _subscriberkey)
  SET @count  = RowCount(@orders)

  /* LookupOrderedRows → ordered + limited (here: the 1 most-recent order) */
  SET @recent = LookupOrderedRows("Orders", 1, "OrderDate DESC", "SubscriberKey", _subscriberkey)
  IF RowCount(@recent) > 0 THEN
    SET @row       = Row(@recent, 1)
    SET @lastOrder = Field(@row, "OrderNumber")
  ENDIF
]%%

🔍 Line by line: - Lookup("Customers", "FirstName", "SubscriberKey", _subscriberkey) — args are DE name, the column you want back, then key/value pair(s) for the WHERE. Returns the FirstName from the first row whose SubscriberKey matches. One value, no order guarantee — fine when keys are unique. - LookupRows("Orders", "SubscriberKey", _subscriberkey) — args are DE name, then key/value pair(s). Returns the whole rowset that matches, unordered. You then iterate with Row() / Field(). - RowCount(@orders) — always guard a rowset before reading it, so you don't index into an empty set. - LookupOrderedRows("Orders", 1, "OrderDate DESC", "SubscriberKey", _subscriberkey) — args are DE name, max rows (1), the ORDER BY string ("OrderDate DESC"), then key/value pair(s). This gives me the single most-recent order. Passing 0/negative as the row count means "all, capped at 2,000." - Row(@recent, 1) then Field(@row, "OrderNumber") — pull row #1 from the rowset, then read a named column.

…and in practice I'd: reach for Lookup for a simple personalization token (first name, loyalty tier); LookupRows to render a list where order doesn't matter (e.g. all active subscriptions); and LookupOrderedRows whenever the business asks for "latest," "top N," or any sorted slice — passing the limit as the 2nd arg so I never accidentally pull a huge set. If I genuinely need thousands of rows joined/aggregated, I do it upstream in a SQL Query Activity into a purpose-built DE and let the email do a cheap Lookup.

Q3. How do you RaiseError in AMPscript?

Theory. RaiseError stops processing for a send and surfaces a message — it's how you guard a bad send or exclude a subscriber when a precondition isn't met (missing required field, failed validation, no matching record). The signature:

RaiseError("message", stopForThisSubscriberOnly [, smtpErrorCode] [, errorNumber] [, preserveDE])

The boolean (2nd arg) is the one interviewers probe — and it's counterintuitive: - true → skip only the current subscriber and continue the rest of the send. - false (the default if omitted) → stop the entire send job and return the error message.

%%[
  SET @email = AttributeValue("emailaddr")

  /* Guard: no usable email → skip THIS subscriber, keep the job running */
  IF EMPTY(@email) THEN
    RaiseError("Missing email address — skipping subscriber", true)
  ENDIF

  /* Guard: a config/data problem so bad the whole send should abort */
  SET @region = Lookup("Config", "Region", "Key", "ACTIVE")
  IF EMPTY(@region) THEN
    RaiseError("Config DE empty — aborting entire send", false)
  ENDIF
]%%

🔍 Line by line: - RaiseError("Missing email address — skipping subscriber", true) — message first, then true = per-subscriber skip. SFMC drops just this contact from the send and moves to the next one. Use this to quietly exclude bad rows without nuking the campaign. - RaiseError("Config DE empty — aborting entire send", false) — false (also the default) halts the whole job and bubbles the message up. Use this for "something is so wrong I'd rather send nothing." - Optional later args: an smtp error code / custom error number (your own values for logging), and a preserve-DE boolean that lets data already written by InsertDE/UpsertDE persist even though the error fired.

…and in practice I'd: put a RaiseError(..., true) guard at the top of the email's AMPscript for every must-have field (email present, key record found), so dirty rows get skipped, not sent with broken content — and I test it by previewing/sending to a deliberately broken test subscriber and confirming they're excluded while clean ones still send. I reserve false for genuine "abort everything" conditions (a config/feed DE that came back empty), because stopping a live send is a big hammer.

Q4. How do you save historical data of customers?

Theory. SFMC's engagement data ages out: Data Views (the system views like _Sent, _Open, _Click, _Bounce, _Job, _Subscribers) only hold roughly the last 6 months, and the broader Tracking / Email Studio Reports engagement retention is 730 days (2 years). If the business wants reporting beyond those windows, you have to copy the data into your own Data Extension before it expires — a DE you control can retain data indefinitely while the account is active. The standard pattern is a nightly SQL Query Activity in Automation Studio that selects from the Data Views and writes into a retained Reporting DE (append/update, not overwrite), scheduled daily so you always grab the freshest rows before they roll off.

-- Nightly: roll _Open data into a retained reporting DE
SELECT
    o.SubscriberKey,
    o.JobID,
    o.EventDate,
    j.EmailName
FROM _Open o
JOIN _Job j
    ON o.JobID = j.JobID
WHERE o.EventDate >= DATEADD(day, -2, GETDATE())   -- small overlap window

🔍 Line by line: - FROM _Open o JOIN _Job j ON o.JobID = j.JobID — read from the Data Views (_Open, _Job); joining _Job gives human-readable context like EmailName alongside the raw open event. - WHERE o.EventDate >= DATEADD(day, -2, GETDATE()) — only pull the last couple of days each night (a small overlap window so nothing is missed between runs), instead of re-scanning 6 months every time. - The Query Activity's target DE is configured as Update (or Append) so each run adds to history rather than wiping it — the target DE is your long-term store and is set to no/long data retention so it never auto-purges.

…and in practice I'd: in Automation Studio, build a SQL Query Activity per data view I care about (_Sent, _Open, _Click, _Bounce, _Unsubscribe), point each at a Reporting DE with retention off, set the action to Update, drop them into a daily Automation scheduled in the early morning, and verify the row counts grow over time. That way when someone asks for engagement from 18 months ago, it's in my DE even though the Data View only remembers 6 months.

🗣️ "Talk about your projects" — your template

Use a tight 3-part frame for each project — Problem → What I built (with the SFMC primitives named) → Impact — and tie the last line back to this role. Lead with the one most relevant to the JD, keep each to ~30–45 seconds, and name the actual tools so it's clearly hands-on, not theoretical.

Framing line: "At GAP I've owned the SFMC build for [brand/program]. Three pieces I'm proud of —"

1. DE-lookup WSProxy tool. "Support kept asking which DE holds this customer and what's their current value. I built an internal tool using WSProxy (the lightweight SOAP wrapper) that queries across our Data Extensions and the subscriber record by SubscriberKey, so we could reconcile the email on the All Subscribers record vs what's in the DE — which, as you know, is exactly the gap behind most 'they didn't get the email' tickets. It cut that triage from manual hunting to a few seconds."

2. Dynamic barcodes & countdown timers. "For loyalty and promo emails I built AMPscript-driven dynamic content — per-subscriber barcodes and live countdown timers generated at send/open time from DE values — so each email was personalized and time-aware without a separate template per offer."

3. A/B testing framework. "I set up a reusable A/B test framework so the team could split audiences, swap subject lines/content, and read the winner out of Tracking / Data Views consistently, instead of one-off tests — which made our optimization repeatable."

4. VAWP escalations. "I also handled Validation / Account / Workflow Process escalations end to end — diagnosing failed sends, automation errors, and data issues under SLA — which is where I got fluent in the RCA tree: All Subscribers status, stored-email-vs-DE, audience source, and bounce/tracking."

Tie-back: "That mix — building tooling, writing the AMPscript, and owning production troubleshooting — maps directly to what this role needs: someone who can both build the campaign and keep it running when something breaks."

How to deliver it: pick 1–2 projects unless they ask for more; always end each with a number or an outcome; and if they push on "how," drop straight into the UI path or the function (e.g. "I'd open All Subscribers, search by key, check status and stored email") — that's what separates a builder from a bystander.

⚠️ Gotchas / what they probe

• The DE is NOT the source of truth for the send email. For a SubscriberKey-based DE send, SFMC uses the email on the All Subscribers / subscriber record; the DE email only seeds new subscribers. Updating the DE alone won't fix a wrong address. This is the #1 trap in the "didn't receive it" question — say it explicitly.
• Search All Subscribers by SubscriberKey, not email. If you search by email you may miss the record entirely (the old/wrong email won't match the customer you're chasing).
• Held / Bounced / Unsubscribed are silent. No error, no delivery — the contact is suppressed. Always check status first.
• Unsubscribe is one-way. You can't just flip an Unsub back to Active; it needs a legitimate re-opt-in. Saying "I'd re-subscribe them" without that nuance is a red flag.
• RaiseError boolean is backwards from intuition. true = skip just this subscriber; false (default) = kill the whole send. Mixing these up either nukes a campaign or lets bad rows through.
• 2,000-row cap on the Lookup family. Don't try to paginate huge sets in AMPscript — that's a SQL Query Activity's job. Know that LookupOrderedRows with a rowcount < 1 means "all, capped at 2,000."
• Retention windows: Data Views ~6 months, Tracking 730 days. If you can't quote these, you can't justify the nightly-Query-Activity archive pattern. The archive must run before data ages out, and the target DE must have retention off.
• Mapped EmailAddress field. A DE send with no field designated as the email address sends to nothing — easy to overlook when a junior built the send definition.
• "Talk about your projects" tests specificity. Vague ("I worked on emails") loses. Naming WSProxy, AMPscript dynamic content, Data Views, RCA steps proves hands-on depth — always be ready to drill from the story down into the exact click or function.

➡️ Next: P13_Write_It_Live_Drills.md

P13 — Write-It-Live Drills

🎯 Interview questions this answers: - "Here's a join diagram on the whiteboard — write me the SFMC SQL for it." (the D → A → B → C puzzle) - "De-dupe this Data Extension so we keep only the latest row per subscriber. Write it now." - "Build a send audience that excludes hard bounces and unsubscribes. Show me the SQL." - "Loop over a customer's orders in AMPscript and render an HTML table. Go." - "Stop a send to anyone with a bad email — how do you throw it out at send time?" - "On a CloudPage, take this form and write it back to a Data Extension. Code it." - "Find everyone we emailed in the last 90 days who never opened. Write the query." - "Walk me through your thinking while you write it." (narration — they're scoring this too)

This module is a dojo. Each drill is Prompt → solution → line-by-line. Read the prompt, cover the solution, write your own on paper or in a scratch DE, then compare. The win condition is not "I understand it" — it's "I can produce it cold, out loud, in 90 seconds."

🖼️ Join types at a glance

When they draw a diagram, you need to map shape → join in one second. INNER = overlap only. LEFT = all of the left circle + the overlap. FULL OUTER = everything in both, matched where it can be, NULL-padded where it can't.

Wireframe.

🥊 Drills

Conventions used below (retail / financial-services flavored): - Customers — master audience DE. PK SubscriberKey, plus EmailAddress, FirstName, Region. - Orders — transactional DE. SubscriberKey, OrderID, OrderDate, OrderTotal. - Accounts — financial-services DE. SubscriberKey, AccountType, Balance. - System Data Views: _Sent, _Open, _Bounce, _Subscribers — joined on SubscriberKey (and JobID for send-level data).

Drill 1 — The JOIN-diagram question

Prompt: The interviewer draws this on the board and says "write the SFMC SQL":

D  --C.E.-->  A  --C.E.-->  B  --Everything-->  C

where C.E. = Common Elements (INNER JOIN) and "Everything" = FULL OUTER JOIN. No SELECT *. Explain INNER vs FULL OUTER, and mention the alternate reading.

Solution:

SELECT
      a.SubscriberKey   AS SubscriberKey,
      a.EmailAddress    AS EmailAddress,
      d.Region          AS Region,
      b.AccountType     AS AccountType,
      c.OrderID         AS OrderID,
      c.OrderTotal      AS OrderTotal
FROM      TableD AS d
INNER JOIN TableA AS a
      ON  a.SubscriberKey = d.SubscriberKey
INNER JOIN TableB AS b
      ON  b.SubscriberKey = a.SubscriberKey
FULL OUTER JOIN TableC AS c
      ON  c.SubscriberKey = b.SubscriberKey

🔍 Line by line: - SELECT a.SubscriberKey AS SubscriberKey, ... — explicit column list with aliases, never SELECT *. In SFMC the target DE's columns must match what you return, so naming every column is non-negotiable. - FROM TableD AS d — start at D because the diagram starts there; the reading order of the arrows drives the FROM/JOIN order. - INNER JOIN TableA AS a ON a.SubscriberKey = d.SubscriberKey — first arrow D --C.E.--> A. C.E. = Common Elements = INNER JOIN: keep only rows where the key exists in both D and A. Anything unmatched on either side is dropped. - INNER JOIN TableB AS b ON b.SubscriberKey = a.SubscriberKey — second arrow A --C.E.--> B, same logic: only subscribers present in the D∩A result and in B survive. - FULL OUTER JOIN TableC AS c ON c.SubscriberKey = b.SubscriberKey — third arrow B --Everything--> C. "Everything" = FULL OUTER JOIN: every row from the left side (D∩A∩B) and every row from C, matched where keys line up, NULL-padded where they don't. A subscriber in C with no order history on the left still appears; a left-side subscriber with no row in C still appears.

Why INNER vs FULL OUTER (say this out loud): - INNER = intersection. Use it when the relationship is required — "a customer must exist in both the audience and the eligibility list." Smaller, safe result. - FULL OUTER = union of both sides with matching. Use it when you must not lose anyone from either table — reconciliation, "show me all customers and all orders even if one side is missing." Can produce large, NULL-heavy results, so it's the deliberate choice, not the default.

🔁 Alternate reading — if "Everything" means "all of B": If the diagram-drawer meant "keep everything from B (the left so far) and only matching C" rather than literally both sides, that's a LEFT JOIN, not FULL OUTER:

LEFT JOIN TableC AS c
      ON  c.SubscriberKey = b.SubscriberKey

Call this ambiguity out in the room: "'Everything' usually means FULL OUTER — both sides survive. But if you mean 'all of B plus whatever matches in C,' that's a LEFT JOIN. Which did you intend?" Asking that question scores points; guessing silently does not.

Drill 2 — Dedup: keep the latest row per SubscriberKey

Prompt: "Orders has multiple rows per customer. Give me one row per SubscriberKey — the most recent order. Write it."

Solution:

SELECT
      ranked.SubscriberKey  AS SubscriberKey,
      ranked.OrderID        AS OrderID,
      ranked.OrderDate      AS OrderDate,
      ranked.OrderTotal     AS OrderTotal
FROM (
      SELECT
            o.SubscriberKey  AS SubscriberKey,
            o.OrderID        AS OrderID,
            o.OrderDate      AS OrderDate,
            o.OrderTotal     AS OrderTotal,
            ROW_NUMBER() OVER (
                  PARTITION BY o.SubscriberKey
                  ORDER BY     o.OrderDate DESC
            ) AS rn
      FROM  Orders AS o
) AS ranked
WHERE ranked.rn = 1

🔍 Line by line: - Inner SELECT ... FROM Orders AS o — pull the raw order rows; you can't apply ROW_NUMBER() and filter it in the same SELECT, so it lives in a subquery. - ROW_NUMBER() OVER (...) — assigns a sequential number to each row within its window. It produces a value you can then filter on. - PARTITION BY o.SubscriberKey — restart the numbering for each subscriber. Every customer's rows get numbered 1, 2, 3... independently. - ORDER BY o.OrderDate DESC — within each customer, number them newest-first, so the most recent order gets rn = 1. (Add a tiebreaker like , o.OrderID DESC if two orders share a date and you need determinism.) - ) AS ranked — the whole windowed query becomes a derived table named ranked. SFMC requires a subquery alias. - WHERE ranked.rn = 1 — keep only the top row per partition. That's exactly one latest order per SubscriberKey. Done.

💡 To dedup and keep all columns of the latest row, list those columns in the inner SELECT (as above) — don't GROUP BY, because MAX(OrderDate) alone can't tell you which OrderID/OrderTotal belonged to that date.

Drill 3 — Suppression anti-join (audience minus bounces + unsubs)

Prompt: "Build the mailable audience: everyone in Customers, minus anyone who hard-bounced or unsubscribed. Use an anti-join. Write it."

Solution:

SELECT
      c.SubscriberKey  AS SubscriberKey,
      c.EmailAddress   AS EmailAddress,
      c.FirstName      AS FirstName,
      c.Region         AS Region
FROM  Customers AS c
WHERE NOT EXISTS (
      SELECT 1
      FROM  _Bounce AS bnc
      WHERE bnc.SubscriberKey = c.SubscriberKey
        AND bnc.BounceCategory = 'Hard bounce'
)
AND   NOT EXISTS (
      SELECT 1
      FROM  _Subscribers AS sub
      WHERE sub.SubscriberKey = c.SubscriberKey
        AND sub.Status = 'Unsubscribed'
)

🔍 Line by line: - FROM Customers AS c — the base audience; everything starts as "in" and we carve people out. - WHERE NOT EXISTS ( SELECT 1 FROM _Bounce ... ) — the anti-join. NOT EXISTS keeps a customer only when the subquery returns no rows. If a matching bounce exists, the customer is excluded. - SELECT 1 — inside an EXISTS/NOT EXISTS you only care whether a row exists, not its values, so select a constant. It's the idiomatic, fastest form. - WHERE bnc.SubscriberKey = c.SubscriberKey — correlate the subquery to the outer row. This is what makes it a per-customer existence check. - AND bnc.BounceCategory = 'Hard bounce' — only hard bounces suppress; soft bounces are transient and shouldn't permanently drop someone. - AND NOT EXISTS ( ... _Subscribers ... Status = 'Unsubscribed' ) — second anti-join, AND-ed on, removing opt-outs. Both conditions must pass to stay in the audience.

💡 Why NOT EXISTS over LEFT JOIN ... WHERE x IS NULL? Both work in SFMC, but NOT EXISTS reads as intent ("not in the suppression list"), short-circuits on first match, and won't multiply rows if the suppression table has duplicates. Say that if they ask.

Drill 4 — AMPscript LookupOrderedRows loop → HTML table

Prompt: "On an email or CloudPage, render this customer's recent orders as an HTML table, newest first. Handle the case where they have no orders. Write the AMPscript."

Solution:

%%[
  VAR @sk, @rows, @rowCount, @i, @row, @orderId, @orderDate, @orderTotal

  SET @sk = AttributeValue("SubscriberKey")

  /* DE name, max rows (0 = all, cap 2000), sort, then column/value filter */
  SET @rows = LookupOrderedRows("Orders", 10, "OrderDate DESC", "SubscriberKey", @sk)
  SET @rowCount = RowCount(@rows)
]%%

%%[ IF @rowCount > 0 THEN ]%%
  <table border="1" cellpadding="6">
    <tr><th>Order</th><th>Date</th><th>Total</th></tr>
    %%[ FOR @i = 1 TO @rowCount DO ]%%
      %%[
        SET @row        = Row(@rows, @i)
        SET @orderId    = Field(@row, "OrderID")
        SET @orderDate  = Field(@row, "OrderDate")
        SET @orderTotal = Field(@row, "OrderTotal")
      ]%%
      <tr>
        <td>%%=v(@orderId)=%%</td>
        <td>%%=Format(@orderDate, "MMM d, yyyy")=%%</td>
        <td>%%=FormatCurrency(@orderTotal, "en-US")=%%</td>
      </tr>
    %%[ NEXT @i ]%%
  </table>
%%[ ELSE ]%%
  <p>You have no recent orders.</p>
%%[ ENDIF ]%%

🔍 Line by line: - VAR @sk, @rows, ... — declare every variable up front. AMPscript wants declarations before use. - SET @sk = AttributeValue("SubscriberKey") — pull the current subscriber's key from the send context (use RequestParameter instead if this is a CloudPage with a query string). - LookupOrderedRows("Orders", 10, "OrderDate DESC", "SubscriberKey", @sk) — returns a rowset: from Orders, at most 10 rows, sorted newest-first, filtered to SubscriberKey = @sk. The trailing pairs are column/value filters (you can append more pairs to AND extra conditions). - SET @rowCount = RowCount(@rows) — how many rows came back. This is the loop bound and the empty-state test. - IF @rowCount > 0 THEN — the empty fallback guard. Never loop 1 TO 0; branch first so a customer with no orders gets a friendly message, not a broken empty table. - FOR @i = 1 TO @rowCount DO — AMPscript rowsets are 1-indexed. Looping from 1 to the count touches every row exactly once. - SET @row = Row(@rows, @i) — grab the i-th row object out of the rowset. - SET @orderId = Field(@row, "OrderID") — pull a named column off that row. Repeat per column you want to print. - %%=v(@orderId)=%% — v() prints a variable inline in HTML. Format(...) and FormatCurrency(...) make the date and money human-readable. - NEXT @i — advance the loop. After the loop, </table> closes it. - ELSE ... ENDIF — the no-orders branch. Always close every IF with ENDIF and every FOR with NEXT.

Drill 5 — RaiseError to exclude a bad/empty email

Prompt: "At send time, throw this subscriber out of the send if their email is missing or malformed. How?"

Solution:

%%[
  VAR @email
  SET @email = AttributeValue("EmailAddress")

  IF Empty(@email) THEN
    RaiseError("Empty email address — excluding subscriber", true)
  ELSEIF IndexOf(@email, "@") == 0 THEN
    RaiseError("Malformed email (no @) — excluding subscriber", true)
  ENDIF
]%%

🔍 Line by line: - SET @email = AttributeValue("EmailAddress") — read the recipient's email from the send context. - IF Empty(@email) THEN — Empty() is true for both NULL and "". Catches the missing-email case. - RaiseError("...", true) — this is the exclusion mechanism. RaiseError stops processing for the current subscriber and skips the send to them; the rest of the send continues for everyone else. The first arg is the logged message. - The second arg true — skip this subscriber but keep the send running. Passing false (or omitting it) would abort the entire send job, which you almost never want for a per-recipient data problem. Know the difference cold — they love this follow-up. - ELSEIF IndexOf(@email, "@") == 0 THEN — IndexOf is 1-based in AMPscript and returns 0 when the substring isn't found. So == 0 means "no @ anywhere" → malformed → raise and skip. - ENDIF — close the branch. If neither condition fires, the subscriber sails through and gets the send.

💡 If asked "why not just filter in SQL upstream?" — say: "I do filter in the audience query; RaiseError is the last-line guard for bad data that slips through at send time, and it logs the reason so I can audit exclusions."

Drill 6 — CloudPage form write-back (RequestParameter + UpsertData)

Prompt: "A CloudPage has a preference-center form. On submit, write the values back to a Data Extension. Code the AMPscript."

Solution:

%%[
  VAR @submitted, @sk, @firstName, @email, @region

  SET @submitted = RequestParameter("submitted")

  IF @submitted == "true" THEN
    SET @sk        = RequestParameter("SubscriberKey")
    SET @firstName = RequestParameter("FirstName")
    SET @email     = RequestParameter("EmailAddress")
    SET @region    = RequestParameter("Region")

    UpsertData(
      "Customers", 1,
      "SubscriberKey", @sk,
      "FirstName",     @firstName,
      "EmailAddress",  @email,
      "Region",        @region
    )
  ENDIF
]%%

<form method="post" action="%%=RequestParameter('PAGEURL')=%%">
  <input type="hidden" name="submitted" value="true">
  <input type="hidden" name="SubscriberKey" value="%%=v(@sk)=%%">
  <input type="text"   name="FirstName"     placeholder="First name">
  <input type="email"  name="EmailAddress"  placeholder="Email">
  <input type="text"   name="Region"        placeholder="Region">
  <button type="submit">Save preferences</button>
</form>

🔍 Line by line: - SET @submitted = RequestParameter("submitted") — RequestParameter reads a posted/query-string value by its form name. Here it reads a hidden flag so the page knows it's handling a real submission, not the first load. - IF @submitted == "true" THEN — only write on submit. Without this guard the page would try to upsert empty values every time it loads. - SET @sk = RequestParameter("SubscriberKey") (and the rest) — pull each form field by name into a variable. The name attribute on each <input> must match the string here exactly. - UpsertData("Customers", 1, ...) — the write-back. Updates the matching row or inserts a new one if none matches. - "Customers" — the target DE. It must have a Primary Key (here SubscriberKey) for the match to work. - 1 — the number of match column/value pairs that follow before the update pairs begin. We match on one key (SubscriberKey). The pairs after the match pair are the columns to set. - "SubscriberKey", @sk — the match pair: find the row where SubscriberKey = @sk. - "FirstName", @firstName, ... — the update pairs: the columns to write. If the row exists they're updated; if not, a new row is inserted with all these values. - action="%%=RequestParameter('PAGEURL')=%%" — post the form back to this same CloudPage, so the AMPscript at the top processes it. - <input ... name="FirstName"> — the form name is the contract between the HTML and RequestParameter. Mismatch the name and you silently write blanks.

💡 UpsertData is the CloudPage/Microsite/SMS function. Its email-send cousin is UpsertDE. If they ask which to use on a landing page, the answer is UpsertData.

Drill 7 — Sent but not opened (last 90 days)

Prompt: "Find everyone we emailed in the last 90 days who never opened the email. Join the system data views. Write it."

Solution:

SELECT DISTINCT
      s.SubscriberKey  AS SubscriberKey,
      s.EmailAddress   AS EmailAddress
FROM  _Sent AS s
WHERE s.EventDate >= DATEADD(DAY, -90, GETDATE())
  AND NOT EXISTS (
      SELECT 1
      FROM  _Open AS o
      WHERE o.SubscriberKey = s.SubscriberKey
        AND o.JobID         = s.JobID
)

🔍 Line by line: - SELECT DISTINCT s.SubscriberKey, s.EmailAddress — a subscriber can be sent many emails in 90 days; DISTINCT collapses them to one row per person so the resulting suppression/retarget audience has no duplicates. - FROM _Sent AS s — the system Data View of send events. One row per (subscriber, send). - WHERE s.EventDate >= DATEADD(DAY, -90, GETDATE()) — the 90-day window. GETDATE() is now; DATEADD(DAY, -90, ...) rolls back 90 days. Keep only sends in that window. - AND NOT EXISTS ( SELECT 1 FROM _Open AS o ... ) — the anti-join: keep a send only if there is no matching open. - WHERE o.SubscriberKey = s.SubscriberKey AND o.JobID = s.JobID — match on both keys. SubscriberKey alone isn't enough — you must check that this specific send (JobID) wasn't opened. Joining on SubscriberKey only would wrongly clear someone who opened a different email. - Net result: subscribers who received an email in the last 90 days and never registered an open for that exact send. That's your "sent, not opened" re-engagement audience.

💡 Two follow-ups they may toss: (1) "unique opens vs total?" — _Open has a row per open event; DISTINCT plus the JobID match keeps it clean. (2) "why JobID?" — answer above; it scopes the open to the same send. Saying "SubscriberKey + JobID" unprompted signals you actually know the data views.

🗣️ How to narrate while you code

Silence reads as "stuck." Keep a quiet running commentary — it shows your reasoning even when the syntax is still landing.

• State the plan before the first keystroke. "I'll start from the base audience, then anti-join the suppression lists with NOT EXISTS." Now they know where you're headed even if you fumble a comma.
• Name the join as you type it. "INNER here because the relationship is required — both sides must have the key." / "FULL OUTER because we can't lose rows from either table."
• Say why, not just what. Not "I'm adding ROW_NUMBER" but "I need the latest row per customer, so I'll rank within each SubscriberKey by OrderDate descending and keep rank one."
• Flag assumptions out loud. "I'm assuming OrderDate is the recency field — is that right?" / "'Everything' — do you mean FULL OUTER, or all of B? I'll go FULL OUTER unless you mean the latter." Asking beats guessing.
• Call your edge cases. "I'll guard the loop with a row-count check so a customer with no orders gets a message, not an empty table." / "RaiseError with the second arg true so I skip just this subscriber, not the whole send."
• Read it back when you finish. One sentence: "So: base audience, minus hard bounces, minus unsubs — that's the mailable list." Confirms intent and gives them a clean stopping point.
• If you blank on exact syntax, narrate the shape. "It's a windowed function — ROW_NUMBER OVER, PARTITION BY the key, ORDER BY date descending — let me get the parentheses right." Demonstrating you know the structure recovers most of the credit.

➡️ Next: (final — re-run these drills out loud until automatic. You've got this, Akash! 🚀)

P14 — Deloitte Round: CRM writes, Suppression, Lists & i18n

🎯 Interview questions this answers: - "How would you update a CRM object with AMPscript? Which Marketing Cloud Connect functions?" - "UpsertData vs UpsertDE — what's the difference and when do you use each?" - "Where is the Auto-Suppression List and who/how keeps it updated?" - "How do you update a List?" - "Walk me through updating a List via a File Drop Automation — the steps." - "All Subscribers vs All Contacts — what's the difference?" - "How do you carry data from an email into a CloudPage for personalisation?" - "The email supports 10 languages — how do you switch? Is there a switch / three-track function in AMPscript?"

One-line answer: CRM writes go through three Marketing Cloud Connect functions (CreateSalesforceObject, RetrieveSalesforceObjects, UpdateSingleSalesforceObject) — fine on CloudPages, never in bulk sends. *Data returns rows (CloudPage context), *DE doesn't (email context). The Auto-Suppression List lives under Email Studio ▸ Admin ▸ Send Management and you keep it fed (usually via an Automation). Lists update by import — File Drop → File Transfer → Import File. All Subscribers is the email roster (SubscriberKey); All Contacts is the cross-channel superset (ContactKey). Email→CloudPage uses CloudPagesURL with an encrypted param. And there is no switch in AMPscript — the "three-track" function is the IIF() ternary, and 10 languages want a lookup-driven pattern, not a 10-branch IF.

🧭 Where in the UI

Auto-Suppression Configuration (the one they probe):

Email Studio
  → Email  (top nav)
    → Admin
      → Send Management
        → Auto-Suppression Configuration   ← the org-level auto-suppression lists

⚠️ This is NOT the same screen as Email Studio ▸ Subscribers ▸ Suppression Lists. That second one is a per-send/exclusion list you attach at send time. The Auto-Suppression Configuration under Admin ▸ Send Management is applied automatically to every send in the Business Unit. Knowing both paths — and that they're different — is the whole point of the question.

Lists:

Email Studio
  → Subscribers
    → Lists            ← create / open / import into a List
      → [open a List] → Import  (or feed it from an Automation Import File activity)

File Drop Automation (to update a List on file arrival):

Automation Studio
  → New Automation
    → Starting Source = File Drop      ← watches an Enhanced FTP folder
      → File Transfer activity          ← move / decrypt / unzip the dropped file
        → Import File activity          ← destination = the List, with field mapping + update type
          → (optional) Verification activity / SQL / send

🖼️ Screen map

Call-outs: ① it lives under Admin ▸ Send Management, distinct from Subscribers ▸ Suppression Lists. ② Add / Edit manages the org-level lists that auto-apply to every send. ③ SFMC does not keep these fed for you — a scheduled Automation typically extracts do-not-send / hard-bounce keys and imports them back in.

🔧 Q&A — theory + in practice

Q1 — Update a CRM (Salesforce) object with AMPscript

Theory. With Marketing Cloud Connect (the MCC integration that links SFMC to a Salesforce org), AMPscript gets three CRM functions:

The catch is latency: roughly ~1 second PER call because each one is a live API round-trip to Salesforce. That's fine on a CloudPage or preference centre (one visitor at a time, low volume). It is catastrophic in a high-volume send — 1 million recipients × ~1s = you blow the send window. For bulk CRM writes use Journey Builder Salesforce activities (Update Contact / Object) or the Salesforce API / data loads, not AMPscript in the email.

…and in practice (code): retrieve a Contact by email, then flip a field.

%%[
  /* 1. Read the CRM Contact for this visitor */
  SET @rows = RetrieveSalesforceObjects(
    "Contact",
    "Id,Email,Newsletter_OptIn__c",   /* fields to return */
    "Email", "=", @email              /* filter: Email = @email */
  )

  IF RowCount(@rows) > 0 THEN
    SET @row = Row(@rows, 1)
    SET @cid = Field(@row, "Id")       /* the record Id we'll update */

    /* 2. Update that one Contact — returns 1 on success, 0 on fail */
    SET @ok = UpdateSingleSalesforceObject(
      "Contact", @cid,
      "Newsletter_OptIn__c", "true"
    )

    IF @ok == 1 THEN
      SET @msg = "Preferences saved to CRM."
    ELSE
      SET @msg = "Could not update CRM — please retry."
    ENDIF
  ENDIF
]%%
<p>%%=v(@msg)=%%</p>

🔍 Line by line: - RetrieveSalesforceObjects("Contact", "Id,Email,Newsletter_OptIn__c", "Email", "=", @email) — a live read into the connected Salesforce org: object = Contact, the fields to return (comma-separated), then the filter triple field / operator / value. You must return Id because that's what the update needs. This call costs ~1s. - IF RowCount(@rows) > 0 — guard: only proceed if a matching CRM Contact exists. - SET @cid = Field(@row, "Id") — pull the Salesforce record Id out of row #1. UpdateSingleSalesforceObject targets a record by Id, not by a filter. - UpdateSingleSalesforceObject("Contact", @cid, "Newsletter_OptIn__c", "true") — updates that one Contact's custom checkbox field. Arg shape: object, RecordId, then field/value pair(s). Another ~1s round-trip. - IF @ok == 1 — the function returns 1 on success, 0 on failure, so you branch on it to confirm or show a retry. (CreateSalesforceObject instead returns the new Id.) - Because each CRM call is ~1s, this whole block belongs on a CloudPage / preference centre, never inside a send to thousands of subscribers.

Q2 — UpsertData vs UpsertDE (and the whole *Data / *DE split)

Theory. Same operation (update-if-key-matches, else insert) — different execution context and different return behaviour:

• UpsertDE = the email context function. It runs at send completion and does not return a usable value. You can't confirm the row count.
• UpsertData = the CloudPage / landing-page / SMS context function. It runs inline when the page is hit and returns the number of rows affected, so you can branch on it and show a confirmation.

The same split applies to the whole family: InsertData/InsertDE, UpsertData/UpsertDE. Lookup / LookupRows (the read side) work in both contexts.

…and in practice (code):

%%[
  /* CloudPage: UpsertData RETURNS rows affected → confirm the write */
  SET @rows = UpsertData(
    "Preferences", 1,
      "SubscriberKey", @sk,     /* 1 key column → de-dupes on it */
      "Frequency",     @freq,   /* update/insert columns follow */
      "UpdatedDate",   Now()
  )
  SET @msg = Concat("Saved (", @rows, " row).")

  /* Email: UpsertDE does the same write but returns nothing usable */
  UpsertDE("Preferences", 1, "SubscriberKey", @sk, "Frequency", @freq)
]%%

🔍 Line by line: - SET @rows = UpsertData("Preferences", 1, "SubscriberKey", @sk, …) — arg shape is DE name, number of key columns (1), then key col/value, then update col/value pairs. On a CloudPage this returns the rows-affected count into @rows. - SET @msg = Concat("Saved (", @rows, " row).") — because we got a return value, we can show a real confirmation. This is the reason to prefer *Data on CloudPages. - UpsertDE("Preferences", 1, "SubscriberKey", @sk, "Frequency", @freq) — identical intent in an email, but there's no return to capture (nothing to SET), and the write is deferred to send completion. Use it when the data-write is a side-effect of the send (e.g. logging), not when you need to confirm it.

Q3 — Auto-Suppression List: where it lives & who updates it

Theory. The Auto-Suppression Configuration is at Email Studio ▸ Admin ▸ Send Management ▸ Auto-Suppression Configuration. Lists registered here are applied automatically to every send in the Business Unit — you don't attach them per-send. This is different from Email Studio ▸ Subscribers ▸ Suppression Lists, which are exclusion lists you pick when configuring a specific send/user-initiated send.

Who / how maintains it: YOU do. SFMC does not auto-populate it by default. The standard pattern is a scheduled Automation:

1. SQL Query Activity — select do-not-send / hard-bounce / opt-out SubscriberKeys (e.g. from _Bounce where IsUnique + bounce category = hard, plus your compliance DE) into a staging DE.
2. Data Extract activity — write that DE to a file.
3. File Transfer activity — drop the file on Enhanced FTP.
4. Import File activity — import the file back into the auto-suppression list, so the list stays current.

…and in practice (SQL for step 1):

SELECT DISTINCT b.SubscriberKey
FROM _Bounce b
WHERE b.BounceCategory = 'Hard bounce'
  AND b.EventDate >= DATEADD(DAY, -180, GETDATE())
UNION
SELECT d.SubscriberKey
FROM DoNotContact d          /* your compliance / opt-out DE */

🔍 Line by line: - SELECT DISTINCT b.SubscriberKey FROM _Bounce b — pull unique keys from the bounce Data View; DISTINCT avoids re-adding the same key many times. - WHERE b.BounceCategory = 'Hard bounce' — only permanent (hard) bounces belong in suppression; soft bounces can retry. - AND b.EventDate >= DATEADD(DAY, -180, GETDATE()) — bounded to the ~6-month Data View window so the query stays fast. - UNION SELECT d.SubscriberKey FROM DoNotContact d — merges in your maintained opt-out/compliance DE. UNION de-dupes across both sources. - The result DE is then Data-Extracted → File-Transferred to FTP → Imported into the Auto-Suppression list on a schedule — that's the "who maintains it" answer: an Automation you own.

Q4 — Update a List

Theory. A List (Email Studio ▸ Subscribers ▸ Lists) is the classic email-channel grouping. You update its membership four ways: manual add/edit, Import (upload a file through the UI), an Automation Studio Import File activity (scheduled/File-Drop), or the API (SOAP/REST). Imports let you pick an update type (Add & Update / Overwrite / etc.) and map file columns to subscriber attributes.

…and in practice (UI path):

Email Studio ▸ Subscribers ▸ Lists
  → open the target List
    → Import
      → choose file source (FTP / upload) → map columns → pick update type → Import

For anything recurring you promote that same import into an Automation (next question) rather than doing it by hand.

Q5 — Update a List via a File Drop Automation (the steps)

Theory. A File Drop starting source makes Automation Studio watch an Enhanced FTP folder; the moment a matching file lands, the automation fires. That's how you turn "someone SFTP's us a file" into a hands-off list refresh.

…and in practice (the ordered steps):

1. Automation Studio ▸ New Automation.
2. Set Starting Source = File Drop. Point it at the Enhanced FTP folder and a filename pattern (e.g. list_update_*.csv) it should watch.
3. Add a File Transfer activity — moves the dropped file into the safe Import location and, if it arrived encrypted/zipped, decrypts / unzips it. (File Drop watches the drop folder; File Transfer stages it for import.)
4. Add an Import File activity — destination = the List, define the field mapping (file columns → subscriber attributes), and choose the update type (Add & Update / Overwrite / Update only).
5. (Optional) Add a Verification activity (row-count sanity check) and/or a follow-on SQL / send step.
6. Activate the automation. Now every matching file that lands updates the List automatically.

Memory hook: File Drop → File Transfer → Import File (→ Verify). "Watch → stage/decrypt → load → check."

Q6 — All Subscribers vs All Contacts

Theory.

Rule to say out loud: every Subscriber is a Contact, but not every Contact is a Subscriber. All Subscribers is the legacy email view (status lives here — Active/Held/Unsubscribed/Bounced); All Contacts is the modern Contact Builder view that counts toward your contact billing count and spans every channel. Deleting from All Contacts is what actually removes a person org-wide (see P08).

Q7 — Email → CloudPage personalisation (carry data across)

Theory. From an email you link to a CloudPage and pass an identifier through CloudPagesURL(pageId, "sk", _subscriberkey). CloudPagesURL encrypts the appended params, so the identifier travels as ciphertext, not plaintext the user can edit. On the page you read it with RequestParameter("sk"), then Lookup the DE for the details. Never pass raw PII (email, name) in the URL — pass the key, then look the rest up server-side.

…and in practice (code):

%%[ /* --- in the EMAIL --- */ ]%%
<a href="%%=CloudPagesURL(1234, 'sk', _subscriberkey)=%%">Manage preferences</a>

%%[ /* --- on the CLOUDPAGE --- */
  SET @sk  = RequestParameter("sk")                 /* encrypted param, auto-decrypted */
  SET @row = LookupRows("Master", "SubscriberKey", @sk)
  IF RowCount(@row) > 0 THEN
    SET @fname = Field(Row(@row, 1), "FirstName")
    SET @freq  = Field(Row(@row, 1), "Frequency")
  ENDIF
]%%
<p>Hi %%=v(@fname)=%%, your current frequency is %%=v(@freq)=%%.</p>

🔍 Line by line: - CloudPagesURL(1234, 'sk', _subscriberkey) — builds a link to CloudPage id 1234 and appends sk = _subscriberkey as an encrypted query param. _subscriberkey is the email-context system var identifying the recipient. - RequestParameter("sk") — on the landing page this reads that param and auto-decrypts it. (Because it came via CloudPagesURL, it's signed/encrypted — a user can't just swap in someone else's key.) - LookupRows("Master", "SubscriberKey", @sk) — key the lookup off the trusted, decrypted key to pull the person's real details server-side. This is why you pass a key, not PII: the sensitive data never rides in the URL. - Field(Row(@row, 1), "FirstName") / "Frequency" — extract the columns you need from row #1 to personalise the page. - Net: the email hands off only an encrypted key; the CloudPage rehydrates the rest from the DE.

Q8 — 10 languages: switch / the "three-track" function

Theory. There is no native switch statement in AMPscript. (CASE … WHEN exists, but that's SQL — Query Activities — not AMPscript.) The AMPscript "three-track" function interviewers mean is IIF(condition, trueValue, falseValue) — the inline ternary: one condition, one true track, one false track. IIF is great for a 2-way choice.

For 10 languages, do not write a 10-branch IF/ELSEIF. Use a lookup-driven pattern so adding an 11th language is a data row, not a code change:

• Option A — copy DE: a Copy_By_Lang DE keyed on (LangCode, Key); Lookup the localized string by the subscriber's Language attribute, with an English fallback when the row is missing.
• Option B — content blocks: name blocks by language (hero-en, hero-fr, …) and pull with ContentBlockByKey(Concat("hero-", @lang)).

…and in practice (code):

%%[
  /* IIF = the "three-track" ternary: 2-way only */
  SET @lang    = AttributeValue("Language")
  SET @lang    = IIF(EMPTY(@lang), "en", @lang)     /* default to English */
  SET @isRTL   = IIF(@lang == "ar", "rtl", "ltr")   /* one condition, two tracks */

  /* Option A — lookup localized copy with English fallback (scales to 10+) */
  SET @hdr = Lookup("Copy_By_Lang", "Text", "LangCode", @lang, "Key", "hero_headline")
  IF EMPTY(@hdr) THEN
    SET @hdr = Lookup("Copy_By_Lang", "Text", "LangCode", "en", "Key", "hero_headline")
  ENDIF
]%%

<div dir="%%=v(@isRTL)=%%">
  <h1>%%=v(@hdr)=%%</h1>

  %%[ /* Option B — pull a whole localized content block by key */ ]%%
  %%=ContentBlockByKey(Concat("hero-", @lang))=%%
</div>

🔍 Line by line: - SET @lang = AttributeValue("Language") — read the subscriber's language attribute (the switch key). One attribute drives everything — no hard-coded branching. - SET @lang = IIF(EMPTY(@lang), "en", @lang) — the three-track IIF: condition EMPTY(@lang) → true track "en" → false track keep @lang. This is AMPscript's stand-in for a switch/ternary, and it's inherently 2-way. - SET @isRTL = IIF(@lang == "ar", "rtl", "ltr") — another IIF for text direction. IIF shines for binary decisions like this; it does not scale to 10 branches. - Lookup("Copy_By_Lang", "Text", "LangCode", @lang, "Key", "hero_headline") — the scalable pattern: fetch the localized string for this language and content key from a data-driven copy table. Adding a language = adding rows, not editing AMPscript. - IF EMPTY(@hdr) THEN … Lookup(… "LangCode", "en" …) — the English fallback: if a translation row is missing, fall back to English instead of rendering blank. - ContentBlockByKey(Concat("hero-", @lang)) — Option B: build the block key dynamically (hero-fr, hero-de, …) and pull the whole localized block. Same idea — data/naming-driven, not a 10-way IF. - dir="%%=v(@isRTL)=%%" — applies the RTL/LTR direction so Arabic renders correctly. Localisation is copy and layout direction.

⚠️ Gotchas / what they probe

• CRM writes are ~1s each — never in bulk sends. Update/Create/RetrieveSalesforceObjects are live API round-trips. Fine on CloudPages/preference centres (one visitor); in a mass send they blow the send window. Bulk = Journey Builder Salesforce activities or the API. Marketing Cloud Connect must be configured for these to work at all.
• UpdateSingleSalesforceObject returns 1/0, and updates by Id. You need the record Id first (retrieve it), and you should branch on the 1/0 return to confirm. CreateSalesforceObject returns the new Id instead.
• *Data returns, *DE doesn't. The #1 tested distinction: UpsertData/InsertData (CloudPage) give a rows-affected count and run inline; UpsertDE/InsertDE (email) return nothing usable and run at send completion.
• Two different "suppression" screens. Admin ▸ Send Management ▸ Auto-Suppression Configuration (auto-applies to every send) is NOT Subscribers ▸ Suppression Lists (attached per send). Mixing them up is the trap.
• Auto-suppression isn't self-maintaining. You feed it — typically SQL → Data Extract → File Transfer (FTP) → Import back into the list, on a schedule. If nobody built that Automation, the list goes stale.
• File Drop vs File Transfer. File Drop is the starting source that watches the FTP folder; File Transfer is the activity that moves/decrypts the file. You usually need both, in that order, before Import File.
• Import update type matters. On the Import File activity, "Overwrite" wipes the List first; "Add & Update" merges. Pick deliberately — Overwrite on the wrong list drops everyone not in the file.
• All Subscribers ⊂ All Contacts. Subscriber status lives in All Subscribers; billing/cross-channel identity lives in All Contacts. Every subscriber is a contact, not vice-versa — and true org-wide deletion happens in Contact Builder (P08).
• No PII in URLs. Pass the SubscriberKey via CloudPagesURL (encrypted) and Lookup the rest on the page; never put email/name in the querystring. Read encrypted params with RequestParameter, not QueryParameter.
• No switch in AMPscript; CASE is SQL. The ternary is IIF() (two-way). For 10 languages use a lookup/content-block pattern with an English fallback — a 10-branch IF is the wrong answer and they're listening for that.

➡️ Next: (final practical module — pair with the Marketing Cloud Next track)

★ Marked for Review

Sections you flagged with the ☆ Mark button in the bar above (or the M key) while studying. Click any item to jump straight back to it. This list is saved in your browser and updates automatically.