A00 — SFMC Career Bible: Your Complete Roadmap

This is the master index for your SFMC career study guide. You are an SFMC Email Developer with 4 years at GAP Inc, currently at L1. This bible maps the full path from where you stand today to CTO — every level, every skill gap, every salary band, and every unlock truth. Study this module first. Return to it every time you level up.

How to Use This Bible

This is the map before the territory. Read this page first, then follow the study order that matches your interview timeline. This is not a book you read once — it is a revision engine you return to before every interview and every promotion cycle.

Why this page exists

• Orientation first — you cannot cram 9 career levels at once. This page tells you which module to open next and in what order.
• One sitting per module — every module is built to be read in 20-40 minutes. Do not marathon.
• Revise, do not re-read — the goal is to speak each concept out loud at interview speed, not recognize it on a page.

Module directory — the A-G series

Each module is prefixed with a letter-number code. A00 is this index. Letters group by intent, numbers order within.

• A-series — Career strategy & meta-skills: this file (A00), salary negotiation, interview strategy. Read when planning your next move.
• B-series — Platform fundamentals you must explain to anyone: B01 AMPscript, B02 SSJS/WSProxy, B03 SQL & Data Views, B04 APIs, B05 Email Dev Platform. The Contact model, Send architecture, Journey Builder internals live here.
• C-series — Consultant track: C01 MC Connect, deliverability, analytics, requirements. The L1 to L2 bridge.
• D-series — Architect track: D01 Data Cloud, MuleSoft, enterprise integration, multi-org design. The L3 to L4 bridge.
• E-series — AI, Principal & Leadership: E01 Agentforce, AI governance, cross-cloud strategy. The L4 to L5+ bridge.
• F-series — Scenario bank: F01 real interview scenarios with worked answers and diagrams. The single most valuable interview asset.
• G-series — Certifications & daily plan: G01 MCE-Dev-201, cert path, study cadence.

Navigation logic at a glance

• A = strategy · B = fundamentals · C = consultant · D = architect · E = AI/leadership · F = scenarios · G = certs.
• Move down the letters as you move up the ladder. An L1 lives in B and G. An L4 lives in D and F.

The 9-Level Career Ladder

Each level is a distinct identity shift — not a title bump. The skills, the questions you are asked, and the way you are judged change fundamentally at each rung.

• The first three levels (L1-L3) are graded on execution and judgment — can you build it, and do you know why you built it that way.
• The middle (L4-L6) is graded on design and trade-offs — can you architect across systems and defend it under fire.
• The top (L7-L9) is graded on influence and P&L — can you sell, lead, and own the number.

The three identity shifts on the ladder

• Builder → Owner (L1→L3): you stop needing a ticket and start owning the outcome.
• Owner → Designer (L3→L6): you stop building in one system and start designing across many.
• Designer → Executive (L6→L9): you stop defending designs and start owning revenue, org, and vision.

Levels L1-L2 — Email Developer to Consultant

The first two rungs are where you live today (L1) and where you are heading next (L2). This page gives you, for each level: what interviewers actually ask and the artifacts/proof you must show.

L1 — SFMC Email Developer

• Identity: you execute against a scoped ticket. Someone else owns the why; you own the build.
• Core skills: AMPscript personalization, HTML/CSS email, Journey Builder config, Data Extension management, list segmentation, send operations.
• Cloud footprint: Marketing Cloud only — Email Studio, Content Builder, Journey Builder, Contact Builder.

What interviewers ask at L1

• "Explain the SFMC processing order — what renders first in an email send?"
• "Write AMPscript that greets a subscriber by first name and falls back to 'there' if empty."
• "What is the difference between a sendable and non-sendable Data Extension?"
• "How do you set up a basic welcome Journey? What is the entry source?"
• "How do you test rendering across Outlook, Gmail, and Apple Mail?"

Artifacts / proof you must show at L1

• A portfolio of live templates you coded — responsive, dark-mode-safe, Litmus/Email-on-Acid tested.
• AMPscript snippets for personalization and dynamic content with Lookup/LookupRows.
• A Journey screenshot you configured end to end (entry event → wait → decision → send).
• SFMC Email Specialist certificate (baseline) and MCE-Dev-201 in progress.

L2 — SFMC Consultant

• Identity: you own the spec. You turn a vague business goal into a technical design and get sign-off.
• Core skills: everything L1 plus MC Connect, requirements gathering, SSJS, REST API, data modeling.
• Cloud footprint: Marketing Cloud + Sales Cloud joined by the Marketing Cloud Connect package.

What interviewers ask at L2

• "Walk me through configuring MC Connect from scratch — Connected App, API user, SubscriberKey mapping."
• "A client says 'send an email when a customer buys.' Turn that into a technical design."
• "Write SSJS that calls the SFMC REST API to retrieve a subscriber's status."
• "5 million customers, 3 daily campaigns — how do you structure the data model?"
• "What is the difference between a Salesforce Send journey and an API Send journey?"

Artifacts / proof you must show at L2

• A BRD and an FSD you authored for a real campaign — requirement → design → sign-off.
• A working SSJS CloudPage that authenticates to the REST API and returns data.
• An MC Connect configuration you set up (or a documented troubleshoot of a sync failure).
• MCE-Dev-201 passed plus SFMC Admin and ideally Pardot Specialist.

%%[
  /* L1-grade personalization: greet by name with a safe fallback */
  VAR @first
  SET @first = AttributeValue("FirstName")
  IF Empty(@first) THEN
    SET @first = "there"
  ENDIF
]%%
Hi %%=v(@first)=%%, your order is on its way.

<script runat="server">
  // L2-grade: authenticate to the SFMC REST API and read subscriber status
  Platform.Load("Core", "1.1.1");
  var auth = HTTP.Post(
    "https://YOURSUBDOMAIN.auth.marketingcloudapis.com/v2/token",
    "application/json",
    Stringify({
      grant_type: "client_credentials",
      client_id: "XXXX",
      client_secret: "YYYY"
    })
  );
  var token = Platform.Function.ParseJSON(auth.Response[0]).access_token;
  // token now used as Bearer for /contacts or /messaging REST calls
</script>

Levels L3-L4 — Lead to Solution Architect

These are the "design and judgment" rungs. L3 is where you own an outcome without supervision; L4 is where you design across clouds. For each: what interviewers ask and the artifacts/proof you must show.

L3 — Senior Consultant / Lead Developer

• Identity: you own a full technical workstream end to end — no hand-holding. You are the 11pm escalation.
• Core skills: full platform ownership, complex SQL/automation, Sales Cloud integration, presales.
• Cloud footprint: Marketing Cloud + Sales Cloud + Service Cloud (must be proficient across all three).

What interviewers ask at L3

• "Here is a broken Journey delaying sends by 4 hours. Diagnose it under time pressure."
• "Tell me about a project that failed. What did you do wrong and what did you change?"
• "Write a purchase recency-frequency SQL query across a 3-table schema — no help."
• "A client escalates a live launch-night failure to you. Walk me through the next 30 minutes."
• "How do you scope and estimate a new workstream in presales?"

Artifacts / proof you must show at L3

• A failure debrief story — real, specific: what broke in production, your design error, the fix, the lesson. (GAP's Black Friday email infrastructure is your material.)
• A multi-table SQL query — JOINs, NOT IN suppression, date filters, CASE behavioral scoring.
• One workstream you owned end to end — requirement → FSD → build → UAT → go-live, unsupervised.
• SFMC Consultant cert and ideally Einstein Analytics.

L4 — Solution Architect

• Identity: you stop asking "how do I build this in SFMC" and start asking "how do I design the data layer SFMC, Sales Cloud, and the ERP all share."
• Core skills: Data Cloud, enterprise integration, multi-org design, technical governance.
• Cloud footprint: the above + Data Cloud (Data 360) + MuleSoft for enterprise integration.

What interviewers ask at L4

• "Design a Unified Customer Profile across three source systems. What are your identity resolution rules?"
• "Defend your Data Stream choices to a technical review board."
• "When do you use MuleSoft versus a native Data Cloud connector versus direct API?"
• "Two business units, shared customers, data isolation required — design the org model."
• "How do you govern who can change the shared data model?"

Artifacts / proof you must show at L4

• A Unified Profile design — data streams, matching rules, reconciliation logic documented.
• An integration architecture diagram — systems, connectors, directionality, failure handling.
• A multi-org / multi-BU design with a data-isolation rationale.
• SFMC Architect + Data Cloud Consultant certs.

-- L3-grade: RFM-style behavioral scoring across a 3-table schema,
-- excluding anyone already in the suppression DE.
SELECT
    c.SubscriberKey,
    c.EmailAddress,
    MAX(o.OrderDate)                              AS LastOrder,
    COUNT(o.OrderId)                              AS Frequency,
    SUM(o.OrderTotal)                             AS Monetary,
    CASE
        WHEN MAX(o.OrderDate) >= DATEADD(day, -30,  GETDATE()) THEN 'Active'
        WHEN MAX(o.OrderDate) >= DATEADD(day, -90,  GETDATE()) THEN 'Lapsing'
        ELSE 'Churn Risk'
    END                                           AS RecencyBand
FROM Contacts c
JOIN Orders o          ON o.SubscriberKey = c.SubscriberKey
WHERE c.SubscriberKey NOT IN (SELECT SubscriberKey FROM Suppression_Master)
GROUP BY c.SubscriberKey, c.EmailAddress
HAVING COUNT(o.OrderId) >= 2

Levels L5-L9 — Principal to CTO

The top of the ladder is graded on influence, judgment under fire, and P&L — not on how much you can build. For each level: what interviewers ask and the artifacts/proof you must show.

L5 — Principal Architect

• Identity: you move from "I configure AI features" to "I govern how AI uses enterprise data."
• Core skills: AI/Agentforce, cross-cloud strategy, enterprise data modeling, vendor evaluation.
• Cloud footprint: the full stack + Agentforce + Tableau / CRM Analytics for insight and AI action.

What interviewers ask at L5 — "Write the AI model-selection criteria for an enterprise." · "Design the consent and data-lineage architecture that satisfies Legal." · "When do you build vs buy an AI capability?" · "Explain Agentforce grounding on Data Cloud to both an engineer and the board."

Artifacts / proof at L5 — an enterprise AI governance policy you authored; a model-selection rubric; a vendor evaluation scorecard; CTA track in progress + Agentforce Specialist.

L6 — Enterprise Architect / CTA

• Identity: you defend every trade-off under pressure and remain the person the room defers to.
• Core skills: full enterprise platform strategy, board-level communication, AI governance.
• Cloud footprint: all clouds, defensible to a review board — the CTA gate.

What interviewers ask at L6 — the CTA review board format: present a full solution, then defend it against five domain experts who disagree. · "Where is your design weakest, and why did you accept that?"

Artifacts / proof at L6 — a passed CTA (the gate); a presentation deck defended live; a reference architecture adopted enterprise-wide.

L7 — Field CTO

• Identity: customer-facing executive; your audience is the customer's leadership.
• Core skills: industry verticals, thought leadership, GTM strategy.
• Cloud footprint: platform breadth plus vertical/industry framing.

What interviewers ask at L7 — "Take our platform story to a skeptical customer CIO." · "What is the vertical narrative for retail vs financial services?"

Artifacts / proof at L7 — published thought leadership; keynote/customer-executive references; CTA + demonstrated executive presence.

L8 — VP of Technology

• Identity: you own the number — P&L, org design, vendor ecosystem.
• Core skills: P&L, org design, M&A technical due diligence, multi-vendor ecosystems.

What interviewers ask at L8 — "Walk me through a technical due-diligence you led." · "How did you structure the org to hit the roadmap?"

Artifacts / proof at L8 — P&L ownership evidence; an org design you built; a due-diligence you led; MBA-equivalent executive credibility.

L9 — CTO

• Identity: enterprise vision, board governance, investor relations.
• Core skills: enterprise vision, board governance, AI strategy, investor relations.

What interviewers ask at L9 — "What is your 3-year technology thesis and how do you fund it?" · "How do you govern AI risk at board level?"

Artifacts / proof at L9 — recognized industry authority; a board-adopted strategy; investor-facing narrative.

Skills Matrix — What Every Level Demands

Rating key: ⭐⭐⭐ = must be expert · ⭐⭐ = must be proficient · ⭐ = must be aware · — = not expected.

• Read this matrix down a column to see what one level demands, and across a row to see how a single skill rises and falls over a career.
• The pattern to internalize: hands-on code peaks early and decays; strategy and leadership start at zero and rise to mandatory.

How to read the matrix for your next move

• You are the L2 Consultant column. Your must-be-expert cells: AMPscript, SQL, HTML/CSS. Your must-be-proficient: SSJS, REST/SOAP API, Sales Cloud. Your must-be-aware: Service Cloud, Data Cloud, Solution Design, Stakeholder Mgmt.
• The row that will surprise you: SQL jumps from ⭐⭐ (L1) to ⭐⭐⭐ (L2). That single-star jump is the most common L2 rejection cause for email devs.
• The rows that go to zero: HTML/CSS and AMPscript disappear by L7 — proof the ladder rewards decisions, not keystrokes, at the top.

Where You Are Now and What Unlocks Next

This page is your honest audit and the exact gap list for your next two moves. No vague "learn more" — named categories with the exact interviewer question and why you fail without it.

Your honest starting point

You have 4 years at GAP Inc, SFMC Email Specialist certified, and MCE-Dev-201 in preparation.

• Genuinely strong (defensible under pressure): HTML/CSS email production, template builds, AMPscript for basic personalization, Journey Builder configuration, Data Extension management, list segmentation, send-time operations.
• Touched but not yet defensible: SSJS end-to-end (you may have pasted snippets but cannot write a CloudPage API integration from scratch), REST API calls from within SFMC, SQL beyond basic SELECT/WHERE/JOIN, Synchronized Data Sources and MC Connect setup, Sales Cloud objects and how they relate to Contact Builder.
• The distinction that matters: "have used" is not "can defend." An interviewer's follow-up question is designed to find the seam between the two.

Gaps for L2 Consultant role

The L2 interview probes outside pure email production. The exact categories:

• 1. MC Connect / Salesforce Connect — "Walk me through setting up Marketing Cloud Connect. What objects sync? What is the difference between Synchronized Data Sources and the standard Contact model?" Fail cause: you may have used GAP's MC Connect without configuring it. Consultants set it up and troubleshoot sync failures. Master: Connected App setup, the MC Connect configuration wizard, which standard objects sync (Lead, Contact, Campaign, Campaign Member), Synchronized DEs in Contact Builder, and Salesforce Send vs API Send journeys.
• 2. Requirements gathering & solution writeup — "A client wants emails when a customer buys. Translate that into a technical design." Fail cause: you are used to fully scoped tickets. Consultants discover requirements, write a design doc, get sign-off before building. Master: BRD vs FSD, and the five questions (what data triggers it, who qualifies, re-entry logic, suppressions, success metric).
• 3. SSJS functional literacy — "Write SSJS that calls the SFMC REST API to retrieve subscriber status." Fail cause: you cannot bluff SSJS. Write 10 real scripts. Master: SSJS syntax (not just AMPscript), calling the REST auth endpoint for a token, HTTP.Post to trigger a journey entry, and error handling. Know HTTP.Get, HTTP.Post, Platform.Load, Stringify.
• 4. Data architecture fundamentals — "5 million customers, 3 campaigns daily — structure the data model." Master: sendable vs non-sendable DEs, parent vs child business units for isolation, Data Relationships in Contact Builder, and the Contact Key / Subscriber Key distinction and why it matters.

Gaps for L3 Lead / Senior Consultant role

The L3 interview is a different beast — it evaluates judgment and ownership, not skill. Not "can you do this task" but "you had two options, which did you choose, why was the first wrong, how did you fix it."

• 1. Broken design under time pressure — e.g. "A Journey with a 300ms decision split firing 10,000 contacts at once delays emails 4 hours — what is happening and what do you change?" You must diagnose throttling limits, API call rate limits in decision splits, and the difference between Journey concurrency and Send Throttle.
• 2. Why your last project failed — L3 interviews at Accenture, Deloitte, Wipro, Cognizant always include a failure debrief. Say "it went well" and you fail instantly. Have a real story: what you built, what broke in production, your design error, what you would do differently. GAP's Black Friday email infrastructure is your material.
• 3. Owning a workstream without supervision — take a business requirement, design the solution, write the FSD, get sign-off, build, test, get UAT, go live — without asking your manager what to do next. If you have never done this end-to-end, engineer a project inside GAP where you do, before you interview for L3.
• 4. SQL for non-trivial segmentation — multi-table JOINs, NOT IN suppression, date filters, CASE behavioral scoring. Practice a purchase recency-frequency query on a 3-table schema without help.

-- Data-model self-check: is your suppression logic set-based (L3) or row-based (L1)?
-- L3 answer: exclude the entire suppression population in one NOT IN / anti-join.
SELECT s.SubscriberKey, s.EmailAddress
FROM   Sendable_Master s
WHERE  s.SubscriberKey NOT IN (SELECT SubscriberKey FROM Global_Unsubscribe)
  AND  s.SubscriberKey NOT IN (SELECT SubscriberKey FROM Hard_Bounces)
  AND  s.SubscriberKey NOT IN (SELECT SubscriberKey FROM Frequency_Capped_Today)

The 5 Level-Unlock Truths

Each transition has one single unlock — the thing that actually causes the level change. Everything else is table stakes. Memorize these five; they are the spine of every promotion conversation.

• Dev → Consultant: business translation + MC Connect.
• Consultant → Senior: owning a full technical workstream without supervision.
• Senior → Architect: Data Cloud + enterprise integration patterns.
• Architect → Principal: AI / Agentforce + enterprise data strategy.
• Principal → CTA: defending every trade-off under pressure.

Truth 1 — Dev → Consultant: Business Translation + MC Connect

• The unlock is not more AMPscript. It is sitting with a marketing director, understanding the business goal, and translating it into a technical design the dev team can build.
• Specifically: set up MC Connect from scratch, explain the Contact model to a non-technical stakeholder, write a solution design document.
• The test: if you still need a fully scoped ticket to start work, you are still a developer. Consultants are hired to remove ambiguity.

Truth 2 — Consultant → Senior: Owning a Full Workstream Without Supervision

• The unlock is accountability without hand-holding. A Senior is who the client escalates to at 11pm on launch night.
• You must have been that person once: debugged a live Journey failure, communicated status to a VP-level stakeholder under pressure, found root cause, shipped a fix — all without asking your manager for permission.
• If you have not had that experience, manufacture it inside GAP before you leave.

Truth 3 — Senior → Architect: Data Cloud + Enterprise Integration

• The unlock is crossing the boundary from "how do I build this in SFMC" to "how do I design the data layer that SFMC, Sales Cloud, and the ERP all share."
• Data Cloud is the primary unlock in 2025-2026 because it is where enterprise customer identity lives.
• You must: design a Unified Customer Profile, explain identity resolution rules, and defend your Data Stream choices to a technical review board. Without Data Cloud, you top out at L3.

Truth 4 — Architect → Principal: AI / Agentforce + Enterprise Data Strategy

• The unlock is moving from "I configure AI features" to "I govern how AI uses enterprise data."
• Principal Architects in 2026 must: define AI model-selection criteria, write the enterprise AI governance policy, design the consent and data-lineage architecture that satisfies Legal, and explain all of it to both engineers and the board.
• Agentforce is the vehicle — enterprise data strategy is the actual skill.

Truth 5 — Principal → CTA: Defending Every Trade-off Under Pressure

• The unlock is not a new technical skill. It is walking into a room with five domain experts who disagree, defending every trade-off with quantified rationale, acknowledging your own approach's weaknesses, and still being the person the room defers to.
• The CTA exam simulates this. The real world demands it daily.
• You cannot prepare by studying — you prepare by taking accountability for hard decisions, being wrong in front of people, and recovering.

Salary Landscape India 2026

All figures are total compensation (base + variable). Equity is excluded for non-executive roles. Product/ISV companies typically run 20-40% higher than services/SI firms.

Key observations for your situation

• The L1→L2 transition pays Rs 7-12L more immediately. This is the highest-impact salary jump relative to effort on the entire ladder.
• The L3→L4 transition crosses Rs 40L — the point where lifestyle in Bangalore / Hyderabad / Pune changes materially.
• The CTA certification adds Rs 15-25L to any Architect-band comp regardless of title change.
• GAP Inc's internal bands may cap you at L2-equivalent. Maximum salary growth happens by changing employers at each level, not waiting for internal promotion.

A01 · The Salesforce Ecosystem & CRM Integration Reference

The definitive "how Marketing Cloud depends on and talks to everything else" module. Every other cloud, every connector, every identity key, every integration pattern — mapped end to end. Read this when you need to answer "where does SFMC sit in the Salesforce estate, and how does data get in and out?"

The Salesforce Ecosystem Map

Big idea — one company, many clouds, one customer. Salesforce is not a single product; it is a portfolio of clouds that ideally sit around one shared view of the customer. Marketing Cloud is one spoke on that wheel.

• Customer 360 — Salesforce's umbrella promise: every team (sales, service, marketing, commerce, IT) works off one connected view of each customer.
• Shared Platform — the clouds are meant to interoperate through connectors, APIs, and increasingly Data Cloud (Data 360) as the unifying data layer.
• The customer record — in classic CRM this is the Contact / Lead / Person Account. In Data Cloud it is the Unified Individual. Marketing Cloud's version is the Subscriber (keyed by SubscriberKey).
• Where Marketing Cloud sits — it is the engagement / outbound spoke: email, SMS, push, journeys. It is historically a separate tenant (its own login, its own data model) bolted onto core Salesforce via Marketing Cloud Connect.

The clouds at a glance

• Sales Cloud — the CRM heart: leads, accounts, opportunities, pipeline.
• Service Cloud — support: cases, agents, knowledge, omni-channel.
• Marketing Cloud Engagement — the classic SFMC (email/SMS/journeys).
• Data Cloud (Data 360) — the CDP / unifying data lake.
• Commerce Cloud — B2C & B2B storefronts, carts, orders.
• Experience Cloud — customer portals, communities, microsites.
• Tableau / CRM Analytics — BI and dashboards.
• MuleSoft — the integration bus to non-Salesforce systems.
• Slack — the collaboration / workflow surface.
• Heroku — custom app hosting + Heroku Connect data sync.
• Agentforce — the AI agent layer across all of the above.

Sales Cloud

What it is — Sales Cloud is the CRM core: it tracks prospects, customers, deals, and the accounts they belong to. It is the primary source of truth that Marketing Cloud reads from and writes back to via MC Connect.

The object model (memorize this)

• Lead — an unqualified prospect; not yet linked to an account. A standalone record with its own email/name.
• Contact — a qualified person, always linked to an Account (AccountId).
• Account — the company/organization (B2B) or the household anchor.
• Opportunity — a deal in progress on an account; has StageName, Amount, CloseDate.
• Campaign — a marketing initiative (webinar, email blast); people join as CampaignMember records.
• Activity — Task and Event records: calls, emails logged, meetings.

How SFMC reads/writes Sales Cloud

• Reads — MC Connect creates Synchronized Data Extensions mirroring Contact, Lead, Account, Opportunity, Campaign, CampaignMember. Journey Builder and segmentation query these.
• Writes — email tracking (opens, clicks, bounces) writes back to the CRM as IndividualEmailResult / HTML email status records on the Contact/Lead timeline.
• AMPscript can read/write CRM live: RetrieveSalesforceObjects, UpdateSingleSalesforceObject, CreateSalesforceObject.

%%[
  /* Read a live Opportunity stage from Sales Cloud inside an email */
  SET @rows = RetrieveSalesforceObjects("Opportunity","StageName,Amount","ContactId","=",@contactId)
  IF RowCount(@rows) > 0 THEN
    SET @row = Row(@rows,1)
    SET @stage = Field(@row,"StageName")
  ENDIF
]%%
Your deal is currently in the %%=v(@stage)=%% stage.

Service Cloud

What it is — Service Cloud is the support / customer-care cloud: agents resolve Cases, route work with Omni-Channel, and answer with Knowledge articles. Its data tells marketing how happy or frustrated a customer is — gold for segmentation.

Core objects

• Case — a support request/ticket; has Status, Priority, Origin, Reason, ClosedDate.
• Omni-Channel — the routing engine that pushes the right work item to the right available agent.
• Knowledge (KnowledgeArticleVersion) — searchable help content, reused in emails and self-service.
• Entitlement / Milestone — SLA tracking (must respond within X hours).

How Service Cloud feeds Marketing Cloud

• Case-triggered journeys — a Case reaching Closed fires the Salesforce Data entry source in Journey Builder → sends a CSAT survey or follow-up.
• Transactional / service emails — order confirmations, password resets, "your case is updated." Often sent via the Transactional Messaging API (not subject to marketing opt-out; different sending path).
• Support data enriches segmentation — open-case count, last-case reason, CSAT score become DE fields → suppress promos from angry customers, or trigger a win-back.

{
  "ContactKey": "003XX000004TmiQ",
  "EventDefinitionKey": "APIEvent-case-closed-csat",
  "Data": {
    "CaseNumber": "00012345",
    "CaseReason": "Shipping Delay",
    "ClosedDate": "2026-07-24T10:02:00Z"
  }
}

Experience Cloud

What it is — Experience Cloud (formerly Community Cloud) builds customer-facing portals, communities, help centers, and microsites that sit directly on the CRM data model. Logged-in users read and write real Contact/Case/custom-object records.

Experience Cloud vs SFMC CloudPages

• Experience Cloud — authenticated portal on core Salesforce; users log in (SSO), see their records, submit forms that write straight to CRM objects with sharing rules.
• CloudPages — Marketing Cloud landing pages; great for anonymous campaign pages, preference centers, form capture into Data Extensions; NOT a full authenticated portal, no native CRM record-level security.
• Rule of thumb — secure, personalized, logged-in account area → Experience Cloud; campaign landing / preference page → CloudPages.

Forms writing back

• Experience Cloud forms (or Web-to-Lead/Web-to-Case) write directly to Lead/Case/custom objects on the platform.
• CloudPages forms typically write to a Data Extension (via AMPscript/SSJS), then sync to CRM later.
• SSO — Experience Cloud supports SAML / OpenID; a single login can bridge the portal and (via configuration) personalization.

// SSJS on a CloudPage: upsert a form submission into a Data Extension
var de = DataExtension.Init("PreferenceCenter");
de.Rows.Upsert(
  { SubscriberKey: Request.GetQueryStringParameter("skey") },
  ["EmailOptIn","SMSOptIn","UpdatedDate"],
  [Request.GetFormField("email_opt"), Request.GetFormField("sms_opt"), Now()]
);

Data Cloud (Data 360)

What it is — Data Cloud (rebranded Data 360) is Salesforce's Customer Data Platform (CDP) — a massively scalable lake that ingests data from every cloud and external system, unifies it into one profile per person, and activates segments back out (including into Marketing Cloud).

The pipeline (memorize the arrow chain)

• Ingestion — connectors/streams pull data in: CRM, web/mobile SDK, MC Engagement, SFTP, APIs, external DBs.
• DLO (Data Lake Object) — the raw landing representation of an ingested source. One per data stream.
• DMO (Data Model Object) — the harmonized/canonical model; multiple DLOs map into a standard DMO (e.g., Individual, Contact Point Email, Sales Order).
• Identity Resolution — match & reconcile rules collapse duplicate records into one Unified Individual (Unified Profile).
• Segments — build audiences by filtering DMOs / insights.
• Activation — push a segment to a target. Into SFMC the segment lands as a data extension (an activation-created DE) that a journey or send uses.
• Calculated Insights — SQL-like metrics computed across the whole lake (LTV, engagement score, RFM) reusable in segments and personalization.
• Zero-Copy — query data in place in Snowflake / BigQuery / Databricks / Redshift without copying it into Data Cloud.

-- Calculated Insight: 90-day email engagement score per Unified Individual
SELECT
  ui.ssot__Id__c                                   AS UnifiedId,
  COUNT(eng.ssot__Id__c)                           AS Opens_90d,
  MAX(eng.ssot__EngagementDateTm__c)               AS LastOpen
FROM   UnifiedIndividual__dlm      ui
JOIN   EmailEngagement__dlm        eng
  ON   eng.ssot__IndividualId__c = ui.ssot__Id__c
WHERE  eng.ssot__EngagementType__c = 'Open'
  AND  eng.ssot__EngagementDateTm__c > DATEADD(day, -90, CURRENT_DATE)
GROUP BY ui.ssot__Id__c

Commerce Cloud

What it is — Commerce Cloud runs the storefront — product catalog, cart, checkout, orders — in two flavors: B2C Commerce (consumer retail, ex-Demandware) and B2B Commerce (business buyers, larger orders, negotiated pricing). It is the richest source of behavioral + transactional signals for marketing.

What flows into SFMC

• Cart events — items added, cart value, abandonment timestamp → abandoned-cart journeys.
• Orders — Order, line items, order status → post-purchase / order-confirmation / replenishment journeys.
• Catalog & price — product feed (name, image, price, availability) → Einstein / Content Blocks for dynamic product recommendations in email.
• Browse behavior — product views → Marketing Cloud Personalization.

How it connects

• Marketing Cloud Connect for Commerce / Commerce–Marketing connectors stream cart/order events.
• Data Cloud ingests orders + carts as Sales Order / Cart DMOs → segments (e.g., "bought category X, no repeat in 60 days").
• Catalog feeds land in SFMC as data extensions or Einstein product catalog for recommendations.

{
  "ContactKey": "loyalty-88213",
  "EventDefinitionKey": "APIEvent-abandoned-cart",
  "Data": {
    "CartId": "c_9931",
    "CartValue": 148.00,
    "Items": [
      {"Sku": "SHRT-BLU-M", "Name": "Blue Oxford Shirt", "Qty": 2},
      {"Sku": "BELT-BRN-34", "Name": "Brown Leather Belt", "Qty": 1}
    ],
    "AbandonedAt": "2026-07-24T14:22:00Z"
  }
}

MuleSoft

What it is — MuleSoft (Anypoint Platform) is Salesforce's integration middleware — the bus that connects Salesforce to SAP, Oracle, legacy databases, mainframes, and any REST/SOAP system. It replaces brittle one-off integrations with reusable, layered APIs.

API-Led Connectivity (the three layers — memorize)

• System API — unlocks a source system (SAP, Oracle, mainframe) with a clean, stable API; hides the ugly backend.
• Process API — orchestrates business logic across multiple System APIs (e.g., "get customer 360" combines SAP order + Oracle billing + Salesforce contact).
• Experience API — reshapes data for a specific consumer/channel (mobile app, SFMC, web) without touching the layers below.
• Benefit — reuse: build the SAP System API once; every channel consumes it.

MuleSoft vs point-to-point

• Point-to-point — direct A-to-B integration (SFMC calls SAP directly). Fast to build ONE, but N systems = N² spaghetti, fragile, hard to change.
• MuleSoft — a hub: each system connects once to the bus; changes are isolated behind the API layer.
• Use MuleSoft when — many systems, reuse needed, governance/monitoring/versioning required, non-Salesforce backends (SAP/Oracle).
• Use point-to-point when — one simple, stable connection; low volume; no reuse expected.

// SFMC Script Activity (SSJS): pull enriched profile from a MuleSoft Experience API into a DE
var Platform = Script.Util.HttpRequest;
var req = new Platform("https://api.company.com/exp/customer360/003XX000004TmiQ");
req.method = "GET";
req.setHeader("Authorization", "Bearer " + tokenFromMule);
var resp = req.send();
var profile = Platform.ParseJSON(String(resp.content));
// upsert into SFMC data extension for the journey to use
DataExtension.Init("Customer360").Rows.Upsert(
  { ContactKey: profile.contactKey }, ["LTV","Tier"], [profile.ltv, profile.tier]
);

Tableau & CRM Analytics

What it is — The reporting layer. Four options overlap; know when to reach for each:

• Analytics Builder — SFMC-native reporting (email/journey performance, discover reports). Lives inside Marketing Cloud.
• Marketing Cloud Intelligence (Datorama) — cross-channel marketing analytics; stitches spend + performance across Google/Meta/SFMC/etc.
• CRM Analytics (ex-Einstein/Tableau CRM) — analytics native to the Salesforce core platform; blends CRM + marketing data, embedded in Lightning.
• Tableau — the enterprise BI powerhouse; standalone, connects to anything (warehouses, Data Cloud, CRM), richest visualization.

Which tool, when

• Email/journey KPIs only → Analytics Builder (fast, built-in).
• Cross-channel marketing spend + ROI → Intelligence (Datorama).
• Marketing engagement joined to CRM pipeline/revenue, inside Salesforce → CRM Analytics.
• Executive/enterprise dashboards spanning warehouse + all clouds → Tableau.

The Marketing Cloud Family

What it is — "Marketing Cloud" is an umbrella brand, not one product. Knowing which member does what — and who uses it — is a guaranteed interview question.

The members

• Marketing Cloud Engagement — the classic SFMC (ex-ExactTarget): Email Studio, Journey Builder, Automation Studio, Content Builder, Mobile. B2C batch + journeys. This is "SFMC" in most job descriptions.
• Marketing Cloud Personalization (ex-Interaction Studio, ex-Evergage) — real-time 1:1 web/app personalization + recommendations; decisioning at the moment of interaction.
• Marketing Cloud Intelligence (ex-Datorama) — cross-channel marketing analytics / media-mix.
• Account Engagement (ex-Pardot) — B2B marketing automation: lead nurture, scoring, grading; sits directly on the Sales Cloud platform.
• Marketing Cloud Growth / Advanced — newer core-platform marketing editions (built on Data Cloud + Flow + Einstein); the future direction, especially for SMB/mid-market and now scaling up.

Who uses which

• B2C, high volume, journeys → Engagement.
• B2B, lead nurture + scoring, tied to Sales Cloud → Account Engagement (Pardot).
• Real-time web/app personalization → Personalization.
• Cross-channel reporting → Intelligence.
• New, native-to-platform builds → Growth / Advanced.

Slack & Heroku & Agentforce touchpoints

What it is — Three "edge" surfaces that plug into the marketing workflow: Slack (collaboration/approvals), Heroku (custom apps + data sync), and Agentforce (AI agents). Not core sending tools, but common integration touchpoints.

Slack

• Alerts — journey/automation failures, send milestones, or high-value events posted to a Slack channel (via webhook / Slack API / Flow).
• Approvals — campaign or send approvals routed through Slack (approve/reject buttons) before a send fires.
• Ops — data-quality or deliverability alerts (bounce spike) pushed to on-call.

Heroku

• Custom apps — bespoke microservices/web apps (preference centers, high-scale landing pages, custom APIs) hosted on Heroku when CloudPages/Experience Cloud aren't enough.
• Heroku Connect — bi-directional sync between Heroku Postgres and Salesforce objects; lets a custom app read/write CRM data at scale without hammering the Salesforce API.

Agentforce

• AI agents across clouds — draft/optimize email copy and subject lines, summarize a customer, recommend next best action, auto-triage cases.
• Grounded in Data Cloud — agents reason over the Unified Individual for context; can take actions (update case, send message, post to Slack).

// Automation Studio Script Activity: post a send-failure alert to Slack
var http = new Script.Util.HttpRequest("https://hooks.slack.com/services/T000/B000/XXXX");
http.method = "POST";
http.setHeader("Content-Type", "application/json");
http.postData = Stringify({
  text: ":rotating_light: Journey 'Welcome_v3' entry failed — 412 rows rejected. Check the entry DE."
});
http.send();

Marketing Cloud Connect

What it is — Marketing Cloud Connect (MC Connect) is THE bridge between core Salesforce (Sales/Service) and Marketing Cloud Engagement. Because they are separate tenants, MC Connect is how they share data, identity, and tracking. This is the single most-tested integration on the exam and in interviews.

The setup components

• Managed Package — installed in the Salesforce (CRM) org; adds MC objects, buttons ("Send Email"), and the sync engine.
• API User — a dedicated Salesforce integration user MC Connect authenticates as; controls what CRM data MC can see (via profile/permissions).
• Connected App + OAuth — the trust handshake; MC and Salesforce exchange OAuth tokens so API calls are authorized. No shared password.
• Synchronized Data Extensions (Sync DEs) — MC-side mirrors of CRM objects (Contact, Lead, Account, Opportunity, Campaign, CampaignMember, User, Case) kept in sync on a schedule.
• Sync frequency — Sync DEs refresh on an interval (near-real-time to periodic depending on config/volume); not instantaneous — plan for lag.

The identity mapping (the crux)

• SubscriberKey ↔ ContactId / LeadId — MC Connect maps the SFMC SubscriberKey to the CRM record ID so the SAME person is tracked in both systems.
• Choose the mapping carefully — LeadId breaks on conversion (see Sales Cloud page); many orgs use ContactId or a durable external ID.

Tracking writeback

• Email sends made through MC Connect write tracking (sends, opens, clicks, bounces, unsubscribes) back into the CRM on the Contact/Lead (as IndividualEmailResult / HTML Email Status), visible on the Activity timeline.

AMPscript CRM functions (and their latency warning)

• RetrieveSalesforceObjects(object, fields, criteriaField, operator, value) — read CRM records live at send time.
• UpdateSingleSalesforceObject(object, id, field, value, ...) — write a field back to CRM.
• CreateSalesforceObject(object, numFields, field, value, ...) — create a new CRM record from an email/page.

Journey Builder Salesforce entry source

• Salesforce Data entry source — starts a journey when a CRM record meets criteria (e.g., new Lead, Case closed, Opportunity stage change). It polls the CRM via MC Connect.

%%[
  /* LOW-VOLUME transactional email: safe to read/write CRM live.
     Do NOT do this in a million-row batch send. */
  SET @rows = RetrieveSalesforceObjects("Case","Status,CaseNumber","ContactId","=",@contactId)
  IF RowCount(@rows) > 0 THEN
    SET @status = Field(Row(@rows,1),"Status")
  ENDIF

  /* Write a timestamp back to the Contact */
  SET @res = UpdateSingleSalesforceObject("Contact", @contactId, "Last_Email_Sent__c", Format(Now(),"yyyy-MM-dd"))
]%%
Your case %%=v(@caseNumber)=%% is currently: %%=v(@status)=%%.

CRM Integration Patterns

What it is — The master playbook for wiring SFMC to ANY CRM — Salesforce or not. Every integration answers three questions: (1) how does data move, (2) what is the durable identity key, (3) real-time or batch?

Patterns by CRM

• Salesforce CRM → Marketing Cloud Connect (managed package, native, Sync DEs, tracking writeback). Default and deepest.
• Microsoft Dynamics 365 → no native SFMC connector; use REST/SOAP API (Dynamics Web API), MuleSoft, or SFTP exports into SFMC data extensions. Map contactid (Dynamics GUID) to SubscriberKey.
• SAP (SAP CRM / C4C / S4HANA) → MuleSoft (System API fronting SAP) landing data into DEs, or SFTP batch files. SAP business partner ID becomes the durable key.
• HubSpot → REST API (HubSpot CRM API) or middleware (MuleSoft/Zapier/iPaaS) → DE; HubSpot vid/contact ID maps to SubscriberKey.
• Generic / any external CRM → REST or SOAP API to the SFMC APIs, MuleSoft orchestration, or SFTP file drops into a DE via Automation Studio Import Activity.

How data physically enters SFMC

• REST/SOAP API — external system pushes/pulls DE rows via the SFMC APIs (real-time-ish, per-record or batched).
• SFTP file drop — CSV lands on the SFMC FTP; Automation Studio Import Activity loads it into a DE (batch, robust, high volume).
• MuleSoft — orchestrates any of the above with governance and mapping.
• Data Cloud — ingest the external CRM as a data source, unify, activate into SFMC (modern path).

The identity strategy (the non-negotiable)

• Pick ONE durable ContactKey and carry it across every system — CRM, SFMC, Commerce, Data Cloud.
• Prefer a stable external ID (customer number, loyalty ID) over volatile system IDs (LeadId retires; some CRM GUIDs change on merge).
• All tracking, suppression, and dedup hinge on this one key being consistent everywhere.

{
  "note": "Crosswalk DE keeps one durable ContactKey across CRMs",
  "rows": [
    {"ContactKey": "CUST-100482", "SF_ContactId": "003XX000004TmiQ", "Dynamics_Id": "a1b2-...", "HubSpot_Vid": "778201", "SFMC_SubscriberKey": "CUST-100482"},
    {"ContactKey": "CUST-100483", "SF_ContactId": "003XX000004TmiR", "Dynamics_Id": null, "HubSpot_Vid": "778202", "SFMC_SubscriberKey": "CUST-100483"}
  ]
}

/* SFMC SQL: dedup imported external-CRM rows onto the durable ContactKey before send */
SELECT ContactKey, Email, FirstName, LastName
FROM (
  SELECT c.ContactKey, c.Email, c.FirstName, c.LastName,
         ROW_NUMBER() OVER (PARTITION BY c.ContactKey ORDER BY c.ModifiedDate DESC) AS rn
  FROM   ExternalCRM_Import c
) t
WHERE t.rn = 1

B01 — AMPscript Complete Mastery

Audience: Lead / Architect-level SFMC interview prep. Every section has working code. Reading order: Top to bottom for a first pass. Return to individual sections as reference. Format: Memory-first. Each H2 is one self-contained study card — key terms, layered depth (L1/L2/L3), code, ecosystem, a mind map, a mnemonic, and interview scenarios.

1. Syntax Forms and the Critical Processing Order

AMPscript has three distinct syntax forms. Mixing them up — or not knowing which context supports which — is the classic junior mistake that surfaces in every Lead-level screen.

1.1 The Three Syntax Forms

• Block form %%[ ... ]%% — multi-line logic, loops, conditionals, VAR/SET, function calls that do NOT need to print inline.
• Inline form %%=FunctionName(args)=%% — outputs a value directly into rendered HTML. v() prints a variable's value inline.
• Tag-based form %%[IF ...]%% — one directive per delimiter pair. Functionally identical to block form; common in older docs and in subject/preheader fields where full block syntax is inconsistent.

Block form — declare and compute:

%%[
  VAR @firstName, @lastName, @fullName
  SET @firstName = AttributeValue("FirstName")
  SET @lastName  = AttributeValue("LastName")
  SET @fullName  = Concat(@firstName, " ", @lastName)
]%%

<p>Hello, %%=v(@fullName)=%%!</p>

Inline form — print directly:

<!-- Directly output a variable -->
<p>Dear %%=v(@firstName)=%%,</p>

<!-- Inline function call — no surrounding block needed -->
<p>Today is %%=Now()=%%</p>

<!-- Inline conditional -->
<p>%%=IIF(Empty(@firstName), "Valued Customer", @firstName)=%%</p>

Tag-based form — per-line delimiters:

%%[IF @age >= 18]%%
  <p>Adult content here</p>
%%[ELSEIF @age >= 13]%%
  <p>Teen content here</p>
%%[ELSE]%%
  <p>Child content here</p>
%%[ENDIF]%%

1.2 The Critical Processing Order

The most commonly asked AMPscript fundamentals question at Lead level. SFMC renders email parts in a fixed sequence:

• Order — HTML Body → Text Body → Subject Line (and pre-header treated like subject).
• HTML body renders first, top-to-bottom.
• Subject line processes LAST.
• Each part is a separate render pass — the subject line does NOT share the HTML body's live AMPscript variable state in the way developers assume.

The bug — variable SET at the bottom, referenced in subject:

<!-- Subject line field: Hello %%=v(@greeting)=%% -->

<!-- HTML body TOP (nothing declared yet) -->
<p>Welcome!</p>

<!-- HTML body BOTTOM — the SET happens down here, TOO LATE for the subject -->
%%[
  SET @greeting = "there"
]%%

The fix — declare and SET at the very top:

%%[
  /* TOP of HTML body — always the first AMPscript block */
  VAR @greeting, @firstName
  SET @firstName = AttributeValue("FirstName")
  SET @greeting  = IIF(Empty(@firstName), "there", @firstName)
]%%

<!-- Subject line now correctly outputs %%=v(@greeting)=%% -->
<html>
  <body>
    <p>Hello, %%=v(@greeting)=%%!</p>
  </body>
</html>

2. Variables: VAR, SET, and Scope Rules

AMPscript variables are prefixed with @, declared with VAR, assigned with SET. There is one document-level scope per render — no block or function scope.

2.1 Declaration and Assignment

• VAR @x — declares one or more variables (comma-separated).
• SET @x = value — assigns. Must be inside a %%[ ]%% block.
• Combined shortcut — VAR @x = value declares and assigns on one line.

%%[
  /* Declare separately */
  VAR @city
  SET @city = AttributeValue("City")

  /* Combined shortcut — declare + assign on one line */
  VAR @region = AttributeValue("Region")

  /* Multiple vars in one VAR statement */
  VAR @score, @tier, @label
  SET @score = AttributeValue("LoyaltyScore")
]%%

2.2 Scope Across Multiple Blocks

• Variables declared in one %%[ ]%% block persist across all later blocks in the same email/CloudPage render.
• Single, document-level scope — no function scopes, no block scopes.

%%[
  VAR @tier
  SET @tier = "Gold"
]%%

<p>Some HTML in between</p>

%%[
  /* @tier is still in scope here */
  IF @tier == "Gold" THEN
    SET @label = "Premium Member"
  ELSE
    SET @label = "Standard Member"
  ENDIF
]%%

<p>%%=v(@label)=%%</p>  <!-- renders "Premium Member" -->

%%[
  VAR @phone, @display

  /* Subscriber has no phone -> AttributeValue returns empty string "" */
  SET @phone = AttributeValue("MobilePhone")

  /* Empty() catches both NULL and "" — preferred for display logic */
  SET @display = IIF(Empty(@phone), "Not provided", @phone)

  /* IsNull() — use only when you must distinguish "" from NULL */
  IF IsNull(@phone) THEN
    SET @display = "NULL in DE"
  ELSEIF Empty(@phone) THEN
    SET @display = "Empty string in DE"
  ELSE
    SET @display = @phone
  ENDIF
]%%

3. Conditionals: IF, ELSEIF, IIF, Nested IIF

AMPscript branching comes in two shapes: the statement form (IF ... ENDIF, for logic inside blocks) and the ternary (IIF, for inline values). There is no switch/case — that gap is a deliberate interview probe.

3.1 Full IF / ELSEIF / ELSE / ENDIF

%%[
  VAR @dob, @ageYears, @ageMessage

  SET @dob      = AttributeValue("DateOfBirth")
  SET @ageYears = DateDiff(@dob, Now(), "Y")

  IF @ageYears >= 65 THEN
    SET @ageMessage = "Senior discount applied"
  ELSEIF @ageYears >= 18 THEN
    SET @ageMessage = "Standard pricing"
  ELSEIF @ageYears >= 13 THEN
    SET @ageMessage = "Teen tier"
  ELSE
    SET @ageMessage = "Junior pricing — parent approval required"
  ENDIF
]%%

<p>%%=v(@ageMessage)=%%</p>

3.2 Birthday Check Example

%%[
  VAR @dob, @dobMonth, @dobDay, @nowMonth, @nowDay, @isBirthday

  SET @dob      = AttributeValue("DateOfBirth")
  SET @dobMonth = DatePart(@dob, "M")
  SET @dobDay   = DatePart(@dob, "D")
  SET @nowMonth = DatePart(Now(), "M")
  SET @nowDay   = DatePart(Now(), "D")

  IF @dobMonth == @nowMonth AND @dobDay == @nowDay THEN
    SET @isBirthday = 1
  ELSE
    SET @isBirthday = 0
  ENDIF
]%%

%%[IF @isBirthday == 1]%%
  <div class="birthday-banner">Happy Birthday! Here is your exclusive gift.</div>
%%[ENDIF]%%

3.3 IIF — The Ternary Operator

%%[
  /* Syntax: IIF(condition, trueValue, falseValue) */
  VAR @salutation
  SET @salutation = IIF(
    AttributeValue("Gender") == "F",
    "Ms.",
    "Mr."
  )
]%%

<p>Dear %%=v(@salutation)=%% %%=AttributeValue("LastName")=%%,</p>

3.4 Nested IIF for Multi-Language Selection

There is no switch/case in AMPscript. Canonical patterns: nested IIF (few branches) or a lookup table (many branches — see Section 5).

%%[
  VAR @lang, @greeting
  SET @lang = AttributeValue("PreferredLanguage")

  SET @greeting = IIF(@lang == "es", "Hola",
                  IIF(@lang == "fr", "Bonjour",
                  IIF(@lang == "de", "Hallo",
                  IIF(@lang == "pt", "Ola",
                  IIF(@lang == "it", "Ciao",
                  IIF(@lang == "nl", "Hallo",
                  IIF(@lang == "ja", "Konnichiwa",
                  IIF(@lang == "zh", "Ni hao",
                  IIF(@lang == "ar", "Marhaba",
                  IIF(@lang == "ko", "Annyeong",
                  "Hello"))))))))))
]%%

<p>%%=v(@greeting)=%%!</p>

%%[
  /* Scalable alternative: one Lookup against a Language_Map_DE */
  VAR @lang, @greeting
  SET @lang     = AttributeValue("PreferredLanguage")
  SET @greeting = Lookup("Language_Map_DE", "Greeting", "LangCode", @lang)
  IF Empty(@greeting) THEN SET @greeting = "Hello" ENDIF
]%%

<p>%%=v(@greeting)=%%!</p>

4. Loops: FOR/NEXT with LookupRows, Row(), Field()

The AMPscript loop is FOR @i = 1 TO @n DO ... NEXT @i. It walks a RowSet returned by LookupRows(), pulling each record with Row() and each column with Field().

4.1 Complete FOR Loop Pattern

• LookupRows(DE, field, value) — returns a RowSet of matching rows.
• RowCount(@rows) — how many rows came back.
• Row(@rows, @i) — the i-th row (1-indexed, not 0).
• Field(@row, "ColName") — a column value from that row.

%%[
  VAR @rows, @row, @rowCount, @i
  VAR @productName, @productPrice, @productSku

  SET @rows     = LookupRows("Product_Recommendations_DE", "SubscriberKey", _subscriberkey)
  SET @rowCount = RowCount(@rows)
]%%

%%[IF @rowCount > 0]%%
<table>
  <thead><tr><th>Product</th><th>SKU</th><th>Price</th></tr></thead>
  <tbody>
%%[
  FOR @i = 1 TO @rowCount DO
    SET @row          = Row(@rows, @i)         /* 1-indexed */
    SET @productName  = Field(@row, "ProductName")
    SET @productSku   = Field(@row, "SKU")
    SET @productPrice = Field(@row, "Price")
]%%
    <tr>
      <td>%%=v(@productName)=%%</td>
      <td>%%=v(@productSku)=%%</td>
      <td>$%%=v(@productPrice)=%%</td>
    </tr>
%%[
  NEXT @i
]%%
  </tbody>
</table>
%%[ELSE]%%
<p>No recommendations available for you today.</p>
%%[ENDIF]%%

Wrong at scale — silent truncation:

%%[
  /* If this subscriber has 2,500 cart items, you silently lose 500 */
  SET @rows = LookupRows("CartItems_DE", "SubscriberKey", _subscriberkey)
]%%

Correct — pre-compute with nightly SQL, single Lookup at send:

%%[
  /* Nightly automation builds one summarized row per subscriber:
     CartSummary_DE = SubscriberKey | TopProductJSON | TotalItems | TotalValue
     At send time: single Lookup — O(1), no row cap, ~100x faster on 2M+ sends */
  VAR @topProductJson, @totalItems, @totalValue

  SET @topProductJson = Lookup("CartSummary_DE", "TopProductJSON", "SubscriberKey", _subscriberkey)
  SET @totalItems     = Lookup("CartSummary_DE", "TotalItems",     "SubscriberKey", _subscriberkey)
  SET @totalValue     = Lookup("CartSummary_DE", "TotalValue",     "SubscriberKey", _subscriberkey)
]%%

5. Lookup Functions

Four lookup functions, one decision tree: one value or many? sorted or not? case-sensitive or not?

5.1 The Four Functions

Lookup() — single field value from the first matching row:

/* Lookup(DE_Name, ReturnField, LookupField, LookupValue) */
%%[
  VAR @loyaltyTier
  SET @loyaltyTier = Lookup("Loyalty_DE", "Tier", "SubscriberKey", _subscriberkey)
  /* "Gold" | "Silver" | "Bronze" | empty if no match */
]%%

LookupRows() — RowSet of all matches (max 2,000):

/* LookupRows(DE_Name, LookupField, LookupValue) */
%%[
  VAR @rows
  SET @rows = LookupRows("RecentPurchases_DE", "EmailAddress", emailaddr)
]%%

LookupOrderedRows() — sorted RowSet (max 2,000):

/* LookupOrderedRows(DE_Name, MaxRows, OrderField, Order, LookupField, LookupValue) */
%%[
  VAR @recentOrders
  /* 5 most recent orders, newest first */
  SET @recentOrders = LookupOrderedRows(
    "Orders_DE", 5, "OrderDate", "DESC",
    "CustomerID", AttributeValue("CustomerID")
  )
]%%

LookupOrderedRowsCS() — case-sensitive sorted RowSet:

/* Syntax identical to LookupOrderedRows — but lookup value is case-sensitive */
%%[
  VAR @promoRows
  /* "SUMMER2024" != "summer2024" */
  SET @promoRows = LookupOrderedRowsCS(
    "Promotions_DE", 1, "ExpiryDate", "ASC",
    "PromoCode", AttributeValue("EnteredPromoCode")
  )
]%%

5.2 Comparison Table

6. Write Functions: The DE vs Data Context Split

The most commonly tested AMPscript area at Lead/Architect level. Candidates who cannot articulate the *DE vs *Data split cleanly do not pass the technical screen.

6.1 The Core Rule

• *DE functions run in the email send context — fire-and-forget, return void (no row count).
• *Data functions run in the CloudPage / landing page context — return an integer you can branch on.
• Using *Data in an email body throws a runtime error. Using *DE in a CloudPage "works" but returns nothing — you cannot capture the count for confirmation logic.

6.2 Complete Function Reference Table

6.3 Code Examples

InsertDE in email body — send log:

%%[
  /* Fire-and-forget: log that this email rendered for this subscriber */
  InsertDE(
    "Email_Send_Log",
    "SubscriberKey", _subscriberkey,
    "EmailName",     _emailname,
    "JobID",         _jobid,
    "RenderedAt",    Now()
  )
]%%

InsertData in CloudPage — form handler with confirmation:

%%[
  VAR @firstName, @email, @rowsInserted

  SET @firstName    = RequestParameter("firstName")
  SET @email        = RequestParameter("email")

  SET @rowsInserted = InsertData(
    "Newsletter_Signups_DE",
    "FirstName",    @firstName,
    "EmailAddress", @email,
    "SignupDate",   Now(),
    "SourcePage",   "LP_Summer2024"
  )

  IF @rowsInserted == 1 THEN
    RedirectTo(CloudPagesURL(123))   /* success */
  ELSE
    /* handle error */
  ENDIF
]%%

UpsertDE in email body — idempotent preference logging:

%%[
  /* First N args after the count are the KEY columns used to match.
     Here: match on SubscriberKey + EmailName (2 key cols), upsert the rest. */
  UpsertDE(
    "Open_Personalization_Log",
    2,                            /* number of key columns */
    "SubscriberKey", _subscriberkey,
    "EmailName",     _emailname,
    "LastRendered",  Now(),
    "JobID",         _jobid
  )
]%%

UpsertData in CloudPage — preference center save:

%%[
  VAR @subKey, @emailFreq, @rowsAffected

  SET @subKey    = RequestParameter("subkey")
  SET @emailFreq = RequestParameter("emailFrequency")

  SET @rowsAffected = UpsertData(
    "Subscriber_Preferences_DE",
    1,                            /* 1 key column: SubscriberKey */
    "SubscriberKey",  @subKey,
    "EmailFrequency", @emailFreq,
    "LastUpdated",    Now()
  )
]%%

7. String Functions

Eight workhorse string functions. All are 1-indexed where position matters, and behave predictably on empty input (returning empty, not erroring — mostly).

7.1 The Eight Functions

Concat(s1, s2, ...) — join two or more strings:

%%[
  VAR @fullName
  SET @fullName = Concat(AttributeValue("FirstName"), " ", AttributeValue("LastName"))
  /* "Akash Panda" */
]%%

Substring(string, start, length) — extract a portion (1-indexed):

%%[
  VAR @excerpt
  SET @excerpt = Substring("Hello World", 7, 5)   /* "World" */
]%%

Length(string) — character count:

%%[
  VAR @phoneRaw, @phoneLen
  SET @phoneRaw = AttributeValue("MobilePhone")
  SET @phoneLen = Length(@phoneRaw)   /* 10 for a bare US number */
]%%

IndexOf(string, substring, startPosition) — position of substring (0 = not found):

%%[
  VAR @emailAddress, @atPos, @domain
  SET @emailAddress = AttributeValue("EmailAddress")
  SET @atPos        = IndexOf(@emailAddress, "@", 1)
  SET @domain       = Substring(@emailAddress, Add(@atPos, 1), Subtract(Length(@emailAddress), @atPos))
]%%

Replace(string, find, replace) — replace all occurrences:

%%[
  VAR @formattedPhone
  /* "555-867-5309" -> "5558675309" */
  SET @formattedPhone = Replace(AttributeValue("Phone"), "-", "")
]%%

Trim(string) — strip leading/trailing whitespace:

%%[
  VAR @cleanCity
  SET @cleanCity = Trim(AttributeValue("City"))
]%%

ProperCase(string) — title-case each word:

%%[
  VAR @displayName
  /* "AKASH PANDA" or "akash panda" -> "Akash Panda" */
  SET @displayName = ProperCase(AttributeValue("FullName"))
]%%

BuildRowSetFromString(string, delimiter) — split delimited string into a RowSet:

%%[
  VAR @tagString, @tagRows, @tagCount, @i, @tag
  /* Interest tags stored as "sports,tech,travel" */
  SET @tagString = AttributeValue("InterestTags")
  SET @tagRows   = BuildRowSetFromString(@tagString, ",")
  SET @tagCount  = RowCount(@tagRows)
]%%

<ul>
%%[
  FOR @i = 1 TO @tagCount DO
    SET @tag = Field(Row(@tagRows, @i), 1)   /* single unnamed column: index 1 */
]%%
  <li>%%=v(@tag)=%%</li>
%%[  NEXT @i ]%%
</ul>

8. Date Functions

Six date functions cover almost every campaign need: current time, arithmetic, differences, component extraction, formatting, and parsing strings back into dates.

8.1 The Six Functions

Now() — current date and time:

SET @sendTime = Now()   /* "10/15/2024 2:30:00 PM" — M/D/YYYY */

DateAdd(date, amount, unit) — add/subtract time:

%%[
  VAR @expiryDate, @thirtyDaysAgo
  SET @expiryDate    = DateAdd(Now(), 30, "D")    /* +30 days */
  SET @thirtyDaysAgo = DateAdd(Now(), -30, "D")   /* -30 days */
  /* Units: Y=Year, M=Month, D=Day, H=Hour, MI=Minute, S=Second */
]%%

DateDiff(date1, date2, unit) — difference between two dates:

%%[
  VAR @dob, @ageInYears, @lastOpen, @daysSinceLastOpen
  SET @dob               = AttributeValue("DateOfBirth")
  SET @ageInYears        = DateDiff(@dob, Now(), "Y")
  SET @lastOpen          = AttributeValue("LastOpenDate")
  SET @daysSinceLastOpen = DateDiff(@lastOpen, Now(), "D")
]%%

DatePart(date, part) — extract a component:

%%[
  /* Parts: Year, Month, Day, Hour, Minute, Second (also Y, M, D, H, MI, S) */
  VAR @year, @month, @day, @hour
  SET @year  = DatePart(Now(), "Year")
  SET @month = DatePart(Now(), "Month")
  SET @day   = DatePart(Now(), "Day")
  SET @hour  = DatePart(Now(), "Hour")
]%%

FormatDate(date, format, locale) — format as a string:

%%[
  VAR @isoDate, @friendlyDate, @displayDate
  SET @isoDate      = FormatDate(Now(), "YYYY-MM-DD")               /* logging */
  SET @friendlyDate = FormatDate(Now(), "MMMM D, YYYY")            /* "October 15, 2024" */
  SET @displayDate  = FormatDate(AttributeValue("EventDate"), "MM/DD/YYYY", "en-US")
]%%

DateParse(string, format) — parse a string into a real date:

%%[
  VAR @rawDate, @parsedDate, @daysLeft
  SET @rawDate    = AttributeValue("CustomDateField")   /* "2024-10-15" */
  SET @parsedDate = DateParse(@rawDate, "YYYY-MM-DD")
  SET @daysLeft   = DateDiff(Now(), @parsedDate, "D")
]%%

8.2 Common Date Patterns

Age gate:

%%[
  SET @age = DateDiff(AttributeValue("DateOfBirth"), Now(), "Y")
  IF @age < 21 THEN
    /* Redirect to age-restricted content message */
  ENDIF
]%%

30-day re-engagement variant:

%%[
  VAR @lastEngageDate, @daysSince, @contentVariant
  SET @lastEngageDate = AttributeValue("LastEngagementDate")
  SET @daysSince      = DateDiff(@lastEngageDate, Now(), "D")

  IF @daysSince > 30 THEN
    SET @contentVariant = "winback"
  ELSE
    SET @contentVariant = "standard"
  ENDIF
]%%

Birthday day-of match:

%%[
  VAR @dob, @isBirthday
  SET @dob = AttributeValue("DateOfBirth")
  IF DatePart(@dob, "Month") == DatePart(Now(), "Month")
  AND DatePart(@dob, "Day")   == DatePart(Now(), "Day") THEN
    SET @isBirthday = TRUE
  ENDIF
]%%

Engagement with empty-guard:

%%[
  VAR @cutoff, @lastOpen, @isEngaged
  SET @cutoff   = DateAdd(Now(), -30, "D")
  SET @lastOpen = AttributeValue("LastOpenDate")

  /* Empty() guards subscribers who never opened */
  IF NOT Empty(@lastOpen) AND @lastOpen >= @cutoff THEN
    SET @isEngaged = 1
  ELSE
    SET @isEngaged = 0
  ENDIF
]%%

9. Content and HTTP Functions

Two families: content retrieval (pull reusable blocks from Content Builder) and HTTP / URL (talk to external systems, build subscriber-scoped links).

9.1 Content Retrieval

ContentBlockByKey(externalKey) — insert a Content Block by External Key:

%%=ContentBlockByKey("legal-footer-v3")=%%

/* Prefer External Key (stable) over Content Block ID (changes on copy).
   AMPscript inside the block IS processed when rendered inline. */

ContentAreaByName(folderPath\\blockName) — legacy content area by name:

/* Legacy — prefer ContentBlockByKey for new builds */
%%=ContentAreaByName("My Emails\Legal\Footer")=%%

TreatAsContent(htmlString) — render a string as live HTML and process its AMPscript:

%%[
  VAR @storedTemplate
  /* DE stores HTML snippets per locale */
  SET @storedTemplate = Lookup("Locale_Templates_DE", "HtmlContent", "Locale", AttributeValue("Locale"))
]%%

%%=TreatAsContent(@storedTemplate)=%%

BeginImpressionRegion / EndImpressionRegion — track section-level impressions:

%%=BeginImpressionRegion("hero-banner")=%%
<div class="hero"><img src="%%=v(@heroImageUrl)=%%" /></div>
%%=EndImpressionRegion()=%%

/* Viewable in Email Studio Tracking > Impression Regions */

9.2 HTTP Functions

HTTPGet(url, expectedStatusCode, timeout) — GET request:

%%[
  VAR @apiUrl, @response
  SET @apiUrl   = Concat("https://api.internal.com/loyalty/", _subscriberkey)
  SET @response = HTTPGet(@apiUrl, 200, 5000)   /* timeout ms; body returned as string */
  /* Prefer CloudPages — in email, latency adds to per-subscriber render time */
]%%

HTTPPost2(url, contentType, body, header1, headerVal1, ...) — POST with headers:

%%[
  VAR @postUrl, @requestBody, @responseBody
  SET @postUrl     = "https://api.internal.com/events"
  SET @requestBody = Concat(
    '{"subscriberKey":"', _subscriberkey, '",',
    '"event":"email_render","timestamp":"', FormatDate(Now(), "YYYY-MM-DDTHH:MM:SS"), '"}'
  )
  SET @responseBody = HTTPPost2(
    @postUrl, "application/json", @requestBody,
    "Authorization", Concat("Bearer ", @apiToken),
    "X-Source",      "SFMC-AMPscript"
  )
]%%

9.3 URL and Redirect

CloudPagesURL(pageId, key, value, ...) — subscriber-scoped CloudPage URL:

%%[
  VAR @prefCenterUrl
  SET @prefCenterUrl = CloudPagesURL(12345, "source", "email", "campaign", _emailname)
  /* At send time: tokenized URL embedding subscriber context.
     In preview / Content Builder test: returns empty. */
]%%

<a href="%%=v(@prefCenterUrl)=%%">Manage Preferences</a>

RedirectTo(url) — redirect from a CloudPage:

%%[
  SET @thankYouPageId = 67890
  RedirectTo(CloudPagesURL(@thankYouPageId, "status", "success"))
]%%

10. Utility and Security Functions

The utility/security tier is where Lead interviews separate people who "write AMPscript" from people who operate sends safely: error handling that doesn't nuke a send, and cryptography that protects PII in URLs.

10.1 Null and Boolean Utilities

%%[
  VAR @val
  IF Empty(@val)  THEN /* ... */ ENDIF   /* TRUE if null OR "" */
  IF IsNull(@val) THEN /* ... */ ENDIF   /* TRUE only if strictly NULL */
  SET @display = IIF(Empty(@val), "N/A", @val)   /* inline ternary */
]%%

10.2 GUID — Unique Keys

%%[
  VAR @transactionId
  SET @transactionId = GUID()
  /* UUID v4, e.g. "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
     Use as a unique log key so retries/re-renders do not collide */
]%%

10.3 RaiseError — the Nuclear Option

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

  IF Empty(@email) THEN
    /* RaiseError(message, skipSubscriber)
       TRUE  = skip THIS subscriber only, send continues
       FALSE = abort the ENTIRE send job immediately (default if omitted) */
    RaiseError(
      Concat("Missing email address for subscriber: ", @subKey),
      TRUE   /* skip this one, do NOT abort the send */
    )
  ENDIF
]%%

• RaiseError(message, skipSubscriber) — the second argument is the whole game.
• TRUE — skip this subscriber only; the send continues for everyone else.
• FALSE (or omitted) — abort the entire send job immediately.
• Use TRUE for per-record data-quality problems. Reserve FALSE for content-level catastrophes where sending broken content to everyone is worse than stopping.

10.4 Encryption and Hashing

%%[
  /* Symmetric encryption — protect PII passed in a CloudPage query string */
  VAR @salt, @iv, @encryptedSubKey, @rawParam, @decryptedSubKey
  SET @salt            = "mySaltValue"
  SET @iv              = "myIVValue"
  SET @encryptedSubKey = EncryptSymmetric(_subscriberkey, "AES", @salt, @salt, @iv, @iv)

  /* On the receiving CloudPage: */
  SET @rawParam        = RequestParameter("sk")
  SET @decryptedSubKey = DecryptSymmetric(@rawParam, "AES", @salt, @salt, @iv, @iv)
]%%

%%[
  /* One-way hashing — analytics, matching, deduplication (NOT reversible) */
  VAR @hashedEmail
  SET @hashedEmail = SHA256(AttributeValue("EmailAddress"))
  /* MD5(value) also available — avoid for security, fine for cache keys */
]%%

10.5 Base64 Encode / Decode

%%[
  VAR @rawJson, @encodedPayload, @rawParam, @decodedPayload
  SET @rawJson        = Concat('{"subKey":"', _subscriberkey, '"}')
  SET @encodedPayload = Base64Encode(@rawJson)

  /* Decode on receiving CloudPage */
  SET @rawParam       = RequestParameter("payload")
  SET @decodedPayload = Base64Decode(@rawParam)
]%%

10.6 GetJWTByKeyName — Secure CloudPage Access

%%[
  /* GetJWTByKeyName(keyName) signs a JWT using a key from
     Setup > Security > Key Management. Verify on the page with VerifyJWTByKeyName(). */
  VAR @jwt, @secureUrl
  SET @jwt       = GetJWTByKeyName("SFMC_Preference_Center_Key")
  SET @secureUrl = CloudPagesURL(12345, "token", @jwt)
]%%

<a href="%%=v(@secureUrl)=%%">View Your Secure Preference Center</a>

11. Exclusion Scripts — The Hidden Interview Topic

Exclusion scripts are AMPscript that runs before the send, per subscriber, to decide who to skip. They live in the Exclusion Script field of a Send Definition or Journey Email activity — NOT in the email body. This is the topic that quietly separates senior candidates.

11.1 How They Work

• The script must evaluate to a single numeric/boolean-like output.
• Any value > 0 (or TRUE) -> EXCLUDE this subscriber.
• 0 or empty -> INCLUDE this subscriber.
• Runs once per subscriber during send-definition processing, before the message renders.

11.2 The Three Critical Rules

• Rule 1 — Personalization strings, not @variables: use emailaddr and _subscriberkey (personalization strings resolved per subscriber before AMPscript). Do not rely on AttributeValue()/@variables as your subscriber identity here.
• Rule 2 — Threshold must be > 0: an empty string evaluates to 0, so the subscriber is included. Only a positive count excludes.
• Rule 3 — Right field, right place: the script goes in the Send Definition / Journey Exclusion field, never the HTML body.

11.3 Correct Exclusion Script

%%[
  /* Exclude subscribers on the Global_Suppression_List DE.
     Use emailaddr (personalization string) — NOT @email (@variable). */
  VAR @rowCount
  SET @rowCount = RowCount(
    LookupRows("Global_Suppression_List", "EmailAddress", emailaddr)
  )
  /* RowCount > 0 -> on suppression list -> exclude */
]%%
%%=v(@rowCount)=%%

11.4 Wrong Version — The Classic Interview Trap

%%[
  /* WRONG: uses AttributeValue -> @variable in exclusion context.
     This can resolve empty before per-subscriber data is injected,
     so the lookup matches nothing and returns 0 for EVERYONE ->
     nobody is excluded (silent no-op). */
  VAR @email, @rowCount
  SET @email    = AttributeValue("EmailAddress")   /* WRONG here */
  SET @rowCount = RowCount(
    LookupRows("Global_Suppression_List", "EmailAddress", @email)
  )
]%%
%%=v(@rowCount)=%%

11.5 Multiple Suppression Lists — Layered

%%[
  VAR @suppressGlobal, @suppressCompetitor, @suppressLegal, @totalSuppressed

  SET @suppressGlobal     = RowCount(LookupRows("Global_Suppress",     "EmailAddress",  emailaddr))
  SET @suppressCompetitor = RowCount(LookupRows("Competitor_Suppress", "EmailAddress",  emailaddr))
  SET @suppressLegal      = RowCount(LookupRows("Legal_Hold_List",     "SubscriberKey", _subscriberkey))

  SET @totalSuppressed = Add(Add(@suppressGlobal, @suppressCompetitor), @suppressLegal)
]%%
%%=v(@totalSuppressed)=%%

12. Advanced Patterns

The architect-level patterns: push work out of render, prove personalization, assemble modular emails, and handle CloudPage forms safely.

12.1 Pre-Compute Pattern for Scale

The core performance design: move expensive LookupRows loops out of render into a nightly SQL Automation that writes one flat row per subscriber.

-- Nightly Automation SQL (Query Activity, not AMPscript)
-- One flat row per subscriber with all personalization pre-computed
SELECT
    s.SubscriberKey,
    s.EmailAddress,
    COUNT(p.OrderID)        AS PurchaseCount_30D,
    SUM(p.OrderValue)       AS TotalSpend_30D,
    MAX(p.ProductCategory)  AS TopCategory,
    MAX(p.LastOrderDate)    AS LastPurchaseDate,
    -- Serialize top 3 recs as delimited string for BuildRowSetFromString
    STUFF((
        SELECT TOP 3 ',' + r.ProductName
        FROM Product_Recs r
        WHERE r.SubscriberKey = s.SubscriberKey
        ORDER BY r.Score DESC
        FOR XML PATH('')
    ), 1, 1, '')            AS Top3RecsCSV
FROM Subscribers s
LEFT JOIN Purchases_30D p ON s.SubscriberKey = p.SubscriberKey
GROUP BY s.SubscriberKey, s.EmailAddress

%%[
  /* Send-time: single Lookup per field — no loops, no 2,000 cap */
  VAR @purchaseCount, @totalSpend, @topCategory, @top3Csv, @recRows

  SET @purchaseCount = Lookup("Personalization_Cache_DE", "PurchaseCount_30D", "SubscriberKey", _subscriberkey)
  SET @totalSpend    = Lookup("Personalization_Cache_DE", "TotalSpend_30D",    "SubscriberKey", _subscriberkey)
  SET @topCategory   = Lookup("Personalization_Cache_DE", "TopCategory",       "SubscriberKey", _subscriberkey)
  SET @top3Csv       = Lookup("Personalization_Cache_DE", "Top3RecsCSV",       "SubscriberKey", _subscriberkey)

  /* Split pre-built CSV into a RowSet for display — no LookupRows loop */
  SET @recRows = BuildRowSetFromString(@top3Csv, ",")
]%%

12.2 Dynamic Subject Line with Conditional

%%[
  /* TOP of HTML body — subject reads @subjectPrefix (see Section 1 ordering) */
  VAR @subjectPrefix, @firstName, @loyaltyTier
  SET @firstName   = AttributeValue("FirstName")
  SET @loyaltyTier = AttributeValue("LoyaltyTier")

  IF @loyaltyTier == "Platinum" THEN
    SET @subjectPrefix = Concat("[Platinum Exclusive] ", IIF(Empty(@firstName), "Your", @firstName))
  ELSEIF @loyaltyTier == "Gold" THEN
    SET @subjectPrefix = "[Gold Member]"
  ELSE
    SET @subjectPrefix = ""
  ENDIF
]%%

<!-- Subject line field: %%=v(@subjectPrefix)=%% New arrivals just for you -->

12.3 Send Log Pattern

Log personalization at render time for post-send analysis and proof of personalization.

%%[
  /* BOTTOM of the body — after all personalization vars are set */
  UpsertDE(
    "Send_Personalization_Log",
    2,                              /* 2 key cols: SubscriberKey + JobID */
    "SubscriberKey",   _subscriberkey,
    "JobID",           _jobid,
    "EmailName",       _emailname,
    "LoyaltyTier",     @loyaltyTier,
    "ContentVariant",  @contentVariant,
    "SubjectPrefix",   @subjectPrefix,
    "RecommendationA", @productA,
    "RecommendationB", @productB,
    "RenderTimestamp", Now()
  )
]%%

12.4 Nested ContentBlockByKey for Modular Architecture

%%[
  VAR @heroKey, @footerKey, @locale
  SET @locale    = AttributeValue("PreferredLocale")
  SET @heroKey   = Concat("hero-", @locale)     /* "hero-en" | "hero-fr" | "hero-de" */
  SET @footerKey = Concat("footer-", @locale)   /* "footer-en" | ... */
]%%

<!-- Modular assembly: each block independently editable in Content Builder -->
%%=ContentBlockByKey("header-universal")=%%
%%=ContentBlockByKey(@heroKey)=%%
%%=ContentBlockByKey("product-recs-module")=%%
%%=ContentBlockByKey(@footerKey)=%%
%%=ContentBlockByKey("unsubscribe-block")=%%

12.5 CloudPage Form Handler — Full Pattern

%%[
  /* CloudPage form-handler: validate -> UpsertData -> redirect.
     Guard on a submitted flag so a direct GET does not process. */
  IF RequestParameter("submitted") == "true" THEN

    VAR @subKey, @firstName, @emailFreq, @catSports, @catTravel, @catTech
    VAR @rowsAffected, @errorMsg

    SET @subKey    = RequestParameter("sk")
    SET @firstName = Trim(RequestParameter("firstName"))
    SET @emailFreq = RequestParameter("emailFrequency")
    SET @catSports = RequestParameter("cat_sports")
    SET @catTravel = RequestParameter("cat_travel")
    SET @catTech   = RequestParameter("cat_tech")

    IF Empty(@subKey) THEN
      SET @errorMsg = "Invalid session. Please click the link in your email again."
    ELSEIF NOT(@emailFreq == "daily" OR @emailFreq == "weekly" OR @emailFreq == "monthly") THEN
      SET @errorMsg = "Please select a valid email frequency."
    ELSE
      SET @rowsAffected = UpsertData(
        "Subscriber_Preferences_DE", 1,
        "SubscriberKey",   @subKey,
        "FirstName",       @firstName,
        "EmailFrequency",  @emailFreq,
        "Interest_Sports", IIF(@catSports == "on", "Y", "N"),
        "Interest_Travel", IIF(@catTravel == "on", "Y", "N"),
        "Interest_Tech",   IIF(@catTech   == "on", "Y", "N"),
        "LastUpdated",     Now()
      )
      RedirectTo(CloudPagesURL(67891, "status", "saved", "freq", @emailFreq))
    ENDIF
  ENDIF
]%%

%%[IF NOT Empty(@errorMsg)]%%
<div class="error-banner">%%=v(@errorMsg)=%%</div>
%%[ENDIF]%%

<form method="POST" action="%%=RequestParameter('PAGEURL')=%%">
  <input type="hidden" name="submitted" value="true" />
  <input type="hidden" name="sk" value="%%=v(@subKeyFromUrl)=%%" />
  <!-- form fields -->
</form>

Quick Reference Card

The whole module compressed to interview-recall triggers. If you can explain every row below in one breath, you are ready.

B02 — Server-Side JavaScript and WSProxy

Deep technical reference for SFMC Lead level. Zero-to-hero on SSJS and WSProxy, with L2/L3 depth, ecosystem callouts, mind maps, memory hooks, and interview scenarios. All code examples are JavaScript unless tagged otherwise.

1. SSJS Fundamentals: Context and Restrictions

SSJS = Server-Side JavaScript. SFMC's server-side scripting language, an ECMAScript-3 era engine (NOT modern JS — no atob, no JSON.parse natively, no arrow functions).

• Runs as <script runat="server">...</script> — the runat="server" attribute is what makes it server-side.
• Sits ALONGSIDE AMPscript, not instead of it. Both can appear in the same CloudPage.
• Best for loops, complex logic, external HTTP, WSProxy SOAP — things AMPscript is clumsy at.

The Mandatory First Line

• Every SSJS block MUST open with Platform.Load("Core", "1").
• It initializes ALL Platform.* APIs. Without it, Platform.Function, Platform.Request, etc. are undefined.
• Arg 1 "Core" — the library to load. Arg 2 "1" — the version, always "1" for standard SFMC SSJS.

Platform.Load("Core", "1");

Valid Execution Contexts

SSJS executes only in these three contexts:

• Why this constraint exists — SSJS needs a server-side runtime that can spawn, execute, and return a result per request.
• Email sends are different — distributed, pre-rendered batch operations. There is no per-subscriber runtime, so SSJS cannot run there.

What Happens If You Put SSJS in an Email Body

• SSJS in an email body renders as literal text — it does NOT execute, does NOT error, is NOT stripped.
• The subscriber sees raw JavaScript in their inbox. Classic mistake from web-background developers.

// THIS IN AN EMAIL BODY just becomes visible text in the rendered email:
Platform.Load("Core","1");
var name = "Akash"; // subscriber sees this as plain text

• Correct approach for dynamic email logic — use AMPscript.
• For complex logic (loops, external API calls) — build a CloudPage that returns data, store results in a DE, then read from the DE via AMPscript at send time.

2. Request Handling

Platform.Request exposes the incoming HTTP request on a CloudPage/Landing Page. This is how a CloudPage becomes a real web endpoint.

• Platform.Request.Method — returns "GET" or "POST".
• GetQueryStringParameter(name) — reads a URL query param. Absent param returns empty string, never null.
• GetFormField(name) — reads a POSTed form field. Checkbox returns "on" or "".
• GetPostData() — reads the raw POST body (used for JSON webhooks).
• Platform.Response.Redirect(url) — issues an HTTP redirect (302).

Detecting Request Method

Platform.Load("Core", "1");

var method = Platform.Request.Method; // returns "GET" or "POST"

if (method === "GET") {
    // render the form
} else if (method === "POST") {
    // process the submitted data
}

Reading Query String Parameters

Platform.Load("Core", "1");

var campaignId = Platform.Request.GetQueryStringParameter("campaignId");
var source     = Platform.Request.GetQueryStringParameter("utm_source");

// If the parameter is absent, returns an empty string — never null
if (Platform.Function.IsNullOrEmpty(campaignId)) {
    // handle missing parameter
}

• URL example: https://cloud.mc.example.com/landing?campaignId=CMP001&utm_source=email

Reading POST Form Fields

Platform.Load("Core", "1");

var email     = Platform.Request.GetFormField("email");
var firstName = Platform.Request.GetFormField("firstName");
var consent   = Platform.Request.GetFormField("consent"); // checkbox returns "on" or ""

Complete CloudPage Form Handler

The canonical pattern: a single CloudPage handling both GET (display form) and POST (process submission) — a self-posting form.

Platform.Load("Core", "1");

var method = Platform.Request.Method;

if (method === "GET") {
    // Nothing to do here — the HTML form below will render
    // You can inject any dynamic content via Variable.SetValue

} else if (method === "POST") {

    var email     = Platform.Request.GetFormField("email");
    var firstName = Platform.Request.GetFormField("firstName");
    var lastName  = Platform.Request.GetFormField("lastName");
    var consent   = Platform.Request.GetFormField("consent");

    // --- Validation ---
    var errors = [];

    if (Platform.Function.IsNullOrEmpty(email)) {
        errors.push("Email is required.");
    }
    if (Platform.Function.IsNullOrEmpty(firstName)) {
        errors.push("First name is required.");
    }
    if (consent !== "on") {
        errors.push("You must agree to the terms.");
    }

    if (errors.length > 0) {
        // Re-render the form with errors
        // Store in AMPscript variable for rendering
        Variable.SetValue("@errorMessage", errors.join(" "));
    } else {

        // --- Insert to Data Extension ---
        var rows = {
            "EmailAddress": email,
            "FirstName":    firstName,
            "LastName":     lastName,
            "ConsentDate":  Platform.Function.Now(),
            "Source":       "WebForm"
        };

        try {
            var insertResult = Platform.Function.InsertDE(
                "Form_Submissions",  // DE external key
                rows
            );
            // Redirect to confirmation page on success
            Platform.Response.Redirect("https://cloud.mc.example.com/confirmation");
        } catch (e) {
            Variable.SetValue("@errorMessage", "A system error occurred. Please try again.");
        }
    }
}

• The HTML form below this SSJS block uses %%=v(@errorMessage)=%% to render any validation message, and posts back to the same page URL.

3. Platform.Function Namespace

Platform.Function exposes the SSJS equivalents of AMPscript functions — DE CRUD, JSON, string/date helpers, HTTP, and MC Connect CRM calls.

• These are the fast path for simple DE work — lighter than WSProxy SOAP.
• Names mirror AMPscript (InsertDE, Lookup, UpsertDE) but take JS-native arguments.

Data Extension CRUD

InsertDE — Insert a new row

• Takes (deKey, rowObject). Returns 1 on success, throws on failure.

Platform.Load("Core", "1");

var deKey = "Newsletter_Subscribers";

var result = Platform.Function.InsertDE(deKey, {
    "SubscriberKey": "akash@example.com",
    "EmailAddress":  "akash@example.com",
    "FirstName":     "Akash",
    "OptInDate":     "2026-07-24"
});
// result is 1 on success, throws on failure

UpsertDE — Insert or update based on primary key

• Signature: (deName, primaryKeyFields[], dataFields[]).
• primaryKeyFields — array of [key, value] pairs for the lookup.
• dataFields — array of [key, value] pairs for remaining columns.
• Returns 1 on insert, 2 on update.

Platform.Load("Core", "1");

// UpsertDE takes: (deName, primaryKeyFields[], dataFields[])
var deKey = "Customer_Profile";

var pkCols = [
    ["SubscriberKey", "akash@example.com"]
];

var dataCols = [
    ["FirstName",      "Akash"],
    ["LastName",       "Panda"],
    ["Tier",           "Gold"],
    ["LastModified",   Platform.Function.Now()]
];

var result = Platform.Function.UpsertDE(deKey, pkCols, dataCols);
// result is 1 on insert, 2 on update

UpdateDE — Update matching rows

Platform.Load("Core", "1");

var rows = Platform.Function.UpdateDE(
    "Newsletter_Subscribers",
    [["SubscriberKey", "akash@example.com"]],   // filter columns
    [["Status", "Unsubscribed"], ["OptOutDate", Platform.Function.Now()]]
);

DeleteDE — Delete matching rows

Platform.Load("Core", "1");

var deleted = Platform.Function.DeleteDE(
    "Temp_Processing",
    [["BatchID", "BATCH_2026_07_24"]]
);

LookupRows — Returns an array of matching rows

Platform.Load("Core", "1");

var rows = Platform.Function.LookupRows(
    "Product_Catalog",  // DE name
    "CategoryCode",     // lookup column
    "APPAREL"           // lookup value
);

for (var i = 0; i < rows.length; i++) {
    var row       = rows[i];
    var productId = row["ProductID"];
    var name      = row["ProductName"];
    var price     = row["Price"];
    // process each row
}

Lookup — Returns a single cell value

Platform.Load("Core", "1");

// Lookup(deName, returnColumn, filterColumn, filterValue)
var tierLevel = Platform.Function.Lookup(
    "Customer_Profile",
    "Tier",
    "SubscriberKey",
    "akash@example.com"
);
// Returns the string value of Tier, or null if not found

JSON Handling

Platform.Load("Core", "1");

// Parse a JSON string into a JavaScript object
var jsonString = '{"name":"Akash","role":"SFMC Lead","years":6}';
var obj = Platform.Function.ParseJSON(jsonString);
var name = obj.name; // "Akash"

// Stringify a JavaScript object into a JSON string
var data = { status: "success", code: 200 };
var serialized = Platform.Function.Stringify(data);
// '{"status":"success","code":200}'

IsNullOrEmpty

Platform.Load("Core", "1");

var val1 = null;
var val2 = "";
var val3 = "hello";

Platform.Function.IsNullOrEmpty(val1); // true
Platform.Function.IsNullOrEmpty(val2); // true
Platform.Function.IsNullOrEmpty(val3); // false

• Use this instead of val === null || val === "" — it handles edge cases like whitespace-only strings depending on SFMC version.

4. MC Connect: SSJS as the CRM Read/Write Path

MC Connect functions let SSJS read and write Sales Cloud / Service Cloud records directly from a CloudPage or Script Activity. They require Marketing Cloud Connect — a connected Salesforce org authorized to the account.

• CreateSalesforceObject(objectApiName, fieldPairs[]) — creates a CRM record, returns the new record Id.
• RetrieveSalesforceObjects(objectApiName, fields, filterField, operator, value) — reads CRM records, returns an array.
• UpdateSalesforceObject(objectApiName, recordId, fieldPairs[]) — updates by record Id.
• These are the flexible CRM path in SSJS — richer than AMPscript's equivalents because you can loop, branch, and combine with WSProxy in one script.

CreateSalesforceObject

Platform.Load("Core", "1");

var sfLeadId = Platform.Function.CreateSalesforceObject(
    "Lead",           // Salesforce object API name
    [
        ["FirstName",  "Akash"],
        ["LastName",   "Panda"],
        ["Email",      "akash@example.com"],
        ["Company",    "GAP Inc"],
        ["LeadSource", "Email Campaign"]
    ]
);
// Returns the Salesforce record ID of the new Lead

UpdateSalesforceObject

Platform.Load("Core", "1");

var result = Platform.Function.UpdateSalesforceObject(
    "Contact",
    "003XXXXXXXXXXXXXXX",   // Salesforce record ID
    [
        ["MailingCity",  "San Francisco"],
        ["Title",        "SFMC Architect"]
    ]
);

RetrieveSalesforceObjects

Platform.Load("Core", "1");

var sfContacts = Platform.Function.RetrieveSalesforceObjects(
    "Contact",                               // object type
    "Id,FirstName,LastName,Email",           // fields to return
    "Email",                                 // filter field
    "=",                                     // operator
    "akash@example.com"                      // filter value
);

for (var i = 0; i < sfContacts.length; i++) {
    var contact = sfContacts[i];
    var sfId    = contact["Id"];
    var email   = contact["Email"];
}

SSJS CRM vs AMPscript CRM

Both AMPscript and SSJS can touch CRM through MC Connect. Choose by complexity.

• Rule of thumb — simple, in-email CRM lookup: AMPscript. Multi-step CRM logic on a CloudPage/Automation: SSJS.

5. HTTP Functions: Calling MuleSoft and External Systems

SSJS HTTP functions let a CloudPage or Automation call any external REST endpoint — MuleSoft APIs, external CRM, loyalty/inventory services, webhooks.

• HTTPGet(url, headers) — GET request, returns the raw response body string.
• HTTPPost(url, contentType, payload, headers, statusCodeOut[]) — POST; the last arg is an output parameter by reference that receives the HTTP status.
• HTTPPost2 — variant with finer control over headers/response objects.

HTTPGet

Platform.Load("Core", "1");

var url     = "https://api.example.com/products?category=apparel";
var headers = {
    "Authorization": "Bearer eyJhbGciOiJSUzI1...",
    "Accept":        "application/json"
};

var response = Platform.Function.HTTPGet(url, headers);
// response is the raw body string
var parsed = Platform.Function.ParseJSON(response);

HTTPPost (Platform.Function.HTTPPost2 for better control)

Platform.Load("Core", "1");

var url     = "https://api.example.com/webhook";
var payload = Platform.Function.Stringify({
    event: "form_submit",
    email: "akash@example.com",
    ts:    "2026-07-24T10:00:00Z"
});

var headers = {
    "Content-Type": "application/json",
    "X-API-Key":    "secret123"
};

var statusCode = [0];  // output param — will be populated by reference
var response   = Platform.Function.HTTPPost(url, "application/json", payload, headers, statusCode);
// statusCode[0] now holds the HTTP status integer
// response holds the body string

6. Variable Bridge: AMPscript and SSJS

The variable bridge lets AMPscript and SSJS share values inside one CloudPage/email so each does what it is best at.

• AMPscript — runs in the rendering pipeline, has direct subscriber context (attributes, _subscriberkey-keyed DE lookups), and does final rendering.
• SSJS — does the heavy lifting: loops, external HTTP, complex string work, WSProxy.
• The bridge combines both: AMPscript for context + render, SSJS for logic in the middle.

The @ is part of the string. Variable.GetValue("@customerId") must include the @. Omitting it returns an empty string silently.

Variable.GetValue — Read an AMPscript variable in SSJS

Platform.Load("Core", "1");

// AMPscript above has already set @customerId via SET or Lookup
var customerId = Variable.GetValue("@customerId");

Variable.SetValue — Write from SSJS back to AMPscript

Platform.Load("Core", "1");

// ... perform logic ...
var result = "PREMIUM";

Variable.SetValue("@tierLabel", result);
// Now %%=v(@tierLabel)=%% in the HTML below this block renders "PREMIUM"

Full Worked Example: AMPscript + SSJS + External API

Used on CloudPages that need subscriber context PLUS external data.

%%[
  /* AMPscript block — runs first, establishes context */
  SET @customerId = QueryParameter("cid")
  SET @email = AttributeValue("EmailAddress")
]%%

<script runat="server">
Platform.Load("Core", "1");

// Read AMPscript variables into SSJS
var customerId = Variable.GetValue("@customerId");
var email      = Variable.GetValue("@email");

// Call external loyalty API
var apiUrl     = "https://loyalty.example.com/api/status?id=" + customerId;
var rawResp    = Platform.Function.HTTPGet(apiUrl, { "Accept": "application/json" });
var loyaltyData = Platform.Function.ParseJSON(rawResp);

var tierLabel  = loyaltyData.tier;      // e.g. "Gold"
var points     = loyaltyData.points;    // e.g. 4200
var nextReward = loyaltyData.nextReward; // e.g. "Free Shipping"

// Write results back to AMPscript namespace
Variable.SetValue("@tierLabel",   tierLabel);
Variable.SetValue("@points",      points);
Variable.SetValue("@nextReward",  nextReward);
</script>

%%[
  /* AMPscript block — runs after SSJS, renders results */
  SET @greeting = Concat("Welcome back, ", @tierLabel, " member!")
]%%

<h1>%%=v(@greeting)=%%</h1>
<p>You have %%=v(@points)=%% points. Next reward: %%=v(@nextReward)=%%</p>

• Execution order — AMPscript pre-processing runs first, SSJS block runs second (at render time for CloudPages), then final AMPscript inline expressions render.

7. WSProxy: The Complete Reference

WSProxy = a thin SSJS wrapper over the SFMC SOAP Web Services API. It is the power tool for objects and operations that Platform.Function cannot reach.

• Initialize once: var prox = new Script.Util.WSProxy();
• Faster than raw HTTP SOAP calls — handles session/auth transparently inside the platform.
• Reaches SOAP-only objects: Subscriber, List, Automation, DataExtensionObject[...], TriggeredSend, send logs, and more.

Initialization

Platform.Load("Core", "1");

var prox = new Script.Util.WSProxy();

Core Methods

retrieve(objectType, properties, filter, options)

Fetches records. properties is a string array of column names. filter is an object or null. options carries pagination (ContinueRequest/RequestID).

Platform.Load("Core", "1");

var prox = new Script.Util.WSProxy();

// Retrieve a subscriber by email
var cols   = ["SubscriberKey", "EmailAddress", "Status", "CreatedDate"];
var filter = {
    Property:       "EmailAddress",
    SimpleOperator: "equals",
    Value:          "akash@example.com"
};

var result = prox.retrieve("Subscriber", cols, filter);

if (result.Results && result.Results.length > 0) {
    var sub    = result.Results[0];
    var status = sub.Status;          // "Active", "Unsubscribed", etc.
    var key    = sub.SubscriberKey;
}

create(objectType, props)

Creates a single object. props is a plain object. DE rows use DataExtensionObject[ExternalKey].

Platform.Load("Core", "1");

var prox = new Script.Util.WSProxy();

var deRow = {
    "CustomerID": "CUST_001",
    "Email":      "akash@example.com",
    "Status":     "Active",
    "EnrollDate": "2026-07-24"
};

var createResult = prox.create(
    "DataExtensionObject[Loyalty_Members]",  // DE external key in brackets
    deRow
);

if (createResult.Status === "OK") {
    // row created
}

createBatch(objectType, propsArray)

More efficient than looping create(). Sends all rows in a single SOAP envelope.

Platform.Load("Core", "1");

var prox = new Script.Util.WSProxy();

var rows = [
    { "ProductSKU": "SKU001", "Category": "APPAREL", "Price": "49.99" },
    { "ProductSKU": "SKU002", "Category": "APPAREL", "Price": "79.99" },
    { "ProductSKU": "SKU003", "Category": "SHOES",   "Price": "129.99" }
];

var batchResult = prox.createBatch(
    "DataExtensionObject[Product_Catalog]",
    rows
);

// batchResult.Results is an array, one entry per row
for (var i = 0; i < batchResult.Results.length; i++) {
    var r = batchResult.Results[i];
    if (r.StatusCode !== "OK") {
        // log the error: r.StatusMessage, r.ErrorCode
    }
}

update(objectType, props, filter)

Platform.Load("Core", "1");

var prox = new Script.Util.WSProxy();

// Update automation "Daily_Sync" — pause it
var automationProps = {
    Name:   "Daily_Sync",
    Status: 4        // 4 = Paused in Automation status codes
};

var updateResult = prox.update("Automation", automationProps);

delete(objectType, filter)

Platform.Load("Core", "1");

var prox = new Script.Util.WSProxy();

// Delete a specific subscriber list member
var deleteProps = {
    SubscriberKey: "akash@example.com",
    List: { ID: 12345 }
};

var deleteResult = prox.delete("ListSubscriber", deleteProps);

describe(objectType)

Returns the full property schema of an SFMC object. Useful during development.

Platform.Load("Core", "1");

var prox = new Script.Util.WSProxy();

var schema = prox.describe("Subscriber");
// schema.ObjectDefinition.Properties is an array of property descriptors

execute(objectType, action, params)

Triggers server-side SFMC operations.

Platform.Load("Core", "1");

var prox = new Script.Util.WSProxy();

// Start an automation by name
var execResult = prox.execute("Automation", "start", {
    Name: "Daily_Data_Sync"
});

Filter Object Reference

The filter object is the same shape for all retrieve calls.

// Simple filter — single condition
var simpleFilter = {
    Property:       "Status",
    SimpleOperator: "equals",
    Value:          "Active"
};

// Complex filter — AND two conditions
var complexFilter = {
    LeftOperand: {
        Property:       "Status",
        SimpleOperator: "equals",
        Value:          "Active"
    },
    LogicalOperator: "AND",
    RightOperand: {
        Property:       "CreatedDate",
        SimpleOperator: "greaterThan",
        Value:          "2026-01-01"
    }
};

// "in" operator — matches any value in array
var inFilter = {
    Property:       "Status",
    SimpleOperator: "IN",
    Value:          ["Active", "Held"]
};

// "between" operator
var betweenFilter = {
    Property:       "Price",
    SimpleOperator: "between",
    Value:          10,
    Value2:         100
};

// "like" operator — uses SQL LIKE wildcards
var likeFilter = {
    Property:       "EmailAddress",
    SimpleOperator: "like",
    Value:          "%@gap.com"
};

• All SimpleOperator values: equals, notEquals, greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual, like, IN, between, isNull, isNotNull.