Stage 3 — Final Interview + Skills Task

Narrate: "First I'd confirm field names and where opt-in lives. I'll assume a subscriber DE, the _Open data view, and a Purchases DE. 'Has not purchased in 90 days' is an anti-join — a LEFT JOIN with an IS NULL check."

SELECT s.SubscriberKey, s.EmailAddress
FROM   Subscribers s
JOIN   _Open o          ON o.SubscriberKey = s.SubscriberKey
                        AND o.EventDate > DATEADD(day, -30, GETDATE())
LEFT JOIN Purchases p    ON p.SubscriberKey = s.SubscriberKey
                        AND p.PurchaseDate > DATEADD(day, -90, GETDATE())
WHERE  s.OptInStatus = 'true'
   AND s.Country = 'UK'
   AND p.SubscriberKey IS NULL          -- the anti-join: no recent purchase
GROUP BY s.SubscriberKey, s.EmailAddress

Close with judgment: "I'd sanity-check the row count, and because Apple MPP auto-opens inflate open data, I'd sanity this against clicks before treating it as 'engaged.'" — the anti-join pattern + the MPP caveat are what impress here.

• display:flex → unsupported. Layout collapses. Fix: rebuild with <table> rows/cells.
• background-image on a div → ignored. Hero disappears. Fix: VML (<v:rect>/<v:fill>) fallback inside mso conditionals.
• Padding on an <a> → unreliable; border-radius ignored (square button, usually fine). Fix: bulletproof button — padding on the <td>, VML roundrect for Outlook.
• General: styles should be inline; add mso conditional ghost tables for width control.

Say: "I'd isolate the block, swap flex for a table, add a VML background + bulletproof button behind mso conditionals, then confirm in Litmus / Email on Acid across Outlook versions. This is ten years of my daily work."

Clarify: "Which 15% — random, or a segment? Since when? Did anything change?" Then walk one failing contact's path:

1. Entry — are they actually entering? (landing in the source DE / firing the entry event, criteria met)
2. Data — valid email? null SubscriberKey? missing a required personalisation field → send error (very common cause of a silent slice failing)
3. Subscription status — unsubscribed / bounced / held / global suppression
4. Suppression / exclusion lists on the send
5. Decision split — a branch quietly routing them to a no-send / default path?
6. Send config — throttling, send window, publication status
7. Trace one known-failing contact end-to-end, fix, re-test, document so it can't recur

The £200 answer: the 15% is almost never random — it's a shared attribute (held/bounced, or a missing field breaking the send). Name that instinct out loud.

Frame it: "I never send from a DE I didn't build without my own QA pass — on 480k, one bad field isn't a typo, it's a deliverability and reputation hit." Then the catches:

• 1001 duplicated → dedupe on SubscriberKey (GROUP BY/DISTINCT) so no one's mailed twice.
• 1002 null email → filter nulls; a null send errors and can slice the batch.
• 1003 maria@@web → malformed (double @, no TLD) → format validation.
• 1003 OptIn = false → non-opted-in record slipped in → consent filter is non-negotiable (GDPR + reputation).
• 1003 no FirstName → personalisation needs a fallback, or it renders "Hi ,".
• 1004 is DE, not UK → wrong market in a UK send → confirm the audience / market filter.

The repeatable check: "Dedupe → null + format validation → opt-in/consent scrub → global unsub & suppression check → confirm personalisation fallbacks → seed/test send → and for this size, a second pair of eyes signs off. A checklist I'd want standardised, not done from memory." The £200 answer: the bigger the send, the more a process beats a spot-check — the QA-culture instinct Nicola is listening for.

Narrate: "In Braze this is {{first_name | default: 'there'}} with a Liquid conditional. In SFMC it's AMPscript — same muscle, different syntax. Declare vars, read the attributes, guard the empty case, then a conditional block on tier."

%%[
  VAR @name, @tier
  SET @name = AttributeValue("FirstName")
  SET @tier = AttributeValue("LoyaltyTier")
  IF Empty(@name) THEN SET @name = "there" ENDIF
]%%
<p>Hi %%=v(@name)=%%,</p>

%%[ IF @tier == "Gold" THEN ]%%
  <p>As one of our VIP players, here's an exclusive bonus for you.</p>
%%[ ENDIF ]%%

Close with judgment: "The bit that matters is the fallback — I'd test with a record that has no FirstName so I see the fallback actually render, and confirm nulls are caught by Empty(). Rendering 'Hi ,' to 480k is the classic embarrassing miss, and it's entirely preventable." Naming the Liquid→AMPscript equivalence + the fallback discipline are what land here.

Narrate: "Latest row per key is a ranking problem — ROW_NUMBER() partitioned by SubscriberKey, ordered by date descending, then keep rank 1. SFMC Query Activities support window functions."

SELECT SubscriberKey, PurchaseDate, Amount
FROM (
  SELECT SubscriberKey, PurchaseDate, Amount,
         ROW_NUMBER() OVER (PARTITION BY SubscriberKey
                            ORDER BY PurchaseDate DESC) AS rn
  FROM   Transactions
) t
WHERE rn = 1

Close with judgment: "If two purchases share a timestamp I'd add a tiebreaker (e.g. , TransactionID DESC). This is the pattern that feeds 'your last order' personalisation."

• Recent changes first — new template / IP / sending domain / big volume jump / content shift. A cliff is almost always a change.
• Authentication — do SPF / DKIM / DMARC still pass & align? A DNS edit can silently break DKIM.
• Reputation & blocklists — check Google Postmaster + Microsoft SNDS; run the domain/IP against Spamhaus & co.
• Volume spike — a sudden surge or a cold-list blast tanks reputation.
• Content / spam traps — spammy copy, image-only, or an old list that hit a trap.
• Engagement — mailing unengaged users drags inbox placement; tighten to engaged segments.

Say: "I'd lead with 'what changed this week', because a sudden drop is a change, not decay — then confirm auth and reputation. This is the deliverability piece I'm keenest to own."

Narrate: "LookupOrderedRows to pull the top 3 by date, RowCount to guard the empty case, a FOR loop over Row/Field. Always an ELSE for the no-data path."

%%[
  VAR @rows, @row, @count, @i
  SET @rows  = LookupOrderedRows("Transactions", 3, "PurchaseDate DESC", "SubscriberKey", _subscriberkey)
  SET @count = RowCount(@rows)
]%%
%%[ IF @count > 0 THEN ]%%
  <table>
  %%[ FOR @i = 1 TO @count DO
        SET @row = Row(@rows, @i) ]%%
    <tr><td>%%=Field(@row,"PurchaseDate")=%%</td><td>%%=Field(@row,"Amount")=%%</td></tr>
  %%[ NEXT @i ]%%
  </table>
%%[ ELSE ]%%
  <p>No recent orders — here's what's popular right now.</p>
%%[ ENDIF ]%%

Close with judgment: "The discipline is the empty-state fallback — never render an empty table. In Braze this was Connected Content + a Liquid for loop; same shape."

1. Entry — a scheduled SQL/automation populates a "lapsed" DE (no purchase 90d, still opted-in, not already in-journey); re-evaluate on a cadence.
2. Message 1 — soft "we miss you", no discount yet.
3. Wait ~5–7 days → decision split — purchased/engaged? If converted → exit.
4. Message 2 — a real incentive (offer / code).
5. Wait → decision split again → converted → exit.
6. Message 3 — last chance + a preference/downgrade option; then suppress if still silent, to protect deliverability.
7. Global — suppression lists, frequency caps, respect unsub/held.

The senior move: "I'd hold out a control group so we prove incremental revenue, not revenue we'd have won anyway." — that's the line that impresses Nicola.

• Syntax error, most likely — an unbalanced delimiter (an unclosed %%[ / ]%% or %%=…=%%) makes the parser bail and print raw.
• Wrong context — the code sits in a content area / version that isn't evaluated (e.g. a plain-text part, or pasted where AMPscript isn't processed).
• No send context — a raw preview without the subscriber context that resolves attributes.

Debug: "Isolate the block, check the delimiters balance, test with one record in an interpreted content area, re-send. I'd hunt the unbalanced delimiter first — that's 90% of these."

1. Contain — pause any remaining sends / journey steps still firing. Stop it getting worse first.
2. Assess blast radius — how many received it, who, and is the code fully dead or partially working?
3. Communicate up — flag stakeholders immediately with facts, not panic. Bad news travels best early.
4. Fix — the remedy that protects trust: honour the intended offer via a corrected/apology send, or a follow-up with a working code.
5. Prevent — post-mortem: how did a broken code clear QA? Add the check so it can't recur.

The signal: you stop the bleeding before you explain it, and you end on prevention — operational maturity Nicola will clock.

• Delivered ≈ 98% → ~980k (bounces/failures removed).
• Inbox placement ≈ 85% of delivered → ~830k in the inbox (rest → spam/promotions).
• Opens ≈ 25% → ~200k — but flag it: Apple MPP auto-opens inflate this, so "read by a human" is lower.
• Clicks ≈ 2–3% of sends → ~20–30k — the truer measure of real attention.

The signal: not the exact number — that you break it into a funnel, state assumptions, and know which metric to trust (clicks over MPP-polluted opens).

• 90% is anomalously high → Apple MPP / bot prefetch / security scanners fire the tracking pixel without a real read.
• Near-zero clicks is consistent with that — plus check the boring failures: broken/missing links, link tracking not wired up, or images blocked so the CTA never shows.

Conclusion: don't trust the open metric — validate against delivery and clicks, confirm the links render. The logic move: when a number looks impossible, suspect the measurement before the audience.

• Rank the 4 by business value × relevance to this segment — not by who shouted loudest.
• Consolidate — one email can carry more than one message with clear hierarchy, so 4 asks may fit in 2 sends without spamming.
• Sequence by deadline/urgency; honestly defer or drop the lowest-value one.
• Weigh fatigue / unsub risk — over-mailing costs future reach, a real cost.

The signal: you protect long-term list health and the customer's inbox, and you make the trade-off explicit rather than trying to please everyone.

• 500 is a tiny sample and a 2% gap sits well inside the noise → not statistically significant. Needs proper sample size / confidence before calling it.
• Open rate is a weak, MPP-polluted metric — judge on the metric tied to the goal (clicks / conversions / revenue), not opens.
• Check confounds — was the split truly random? Same send time, same audience?

Conclusion: let it reach significance on the right metric, then ship. The signal: you don't act on noise, and you tie the decision to a business outcome — judgment James and Nicola are both testing.

SMS opt-ins are already your most engaged, loyal customers — the channel didn't cause the spend, the customer type did. Rolling SMS out to everyone won't transplant that behaviour.

Answer: "Measure it properly — a randomised holdout: same customers, some get SMS, some don't, compare. That isolates the incremental effect from the selection effect." Naming the confound is the whole point.

The aggregate is misled by uneven segment mix — A only "won overall" because more traffic landed in an easy segment. If B wins in every country, B is genuinely better.

Answer: "Ship B, or segment-target — I trust the per-segment result over the aggregate. When totals and segments disagree, the aggregate is usually hiding a composition effect."

If only ~5% actually churn, a model that always says "won't churn" is also 95% accurate — accuracy is nearly useless here. And every false positive wastes an incentive on someone who'd have stayed.

Answer: "I'd ask for precision & recall, not accuracy, and weigh the cost of a false positive. Target where the expected saved value beats the incentive cost — not everyone with a flag."

• Opens are timestamped when the mail is fetched — MPP prefetch & overnight batches distort "time of open".
• Survivorship — you only see openers, not the segment that never opens at all.
• Herd effect — if everyone ships at 6am, the inbox is crowded and you lose the edge.

Answer: "Don't move on that number — run a proper randomised send-time test per segment measured on clicks/conversions, not raw open timing."

You could hit ~0 by mailing less or hiding the unsub link — but that's worse: a hidden unsubscribe becomes a spam complaint, which hurts deliverability far more, and it games the number without serving the business.

Answer — reframe it: "Zero unsubscribes isn't actually the goal — an engaged, consented list with low complaints is. I'd measure that instead, and push back constructively rather than optimise a proxy that backfires." That maturity is exactly what Nicola's listening for.