Real-World Banking FTP Use Case Credit Card Upgrade Eligibility & Nurture Journey

-

 

Real-World Banking FTP Use Case

Credit Card Upgrade Eligibility & Nurture Journey

Introduction

In this project, we will build a complete end-to-end Salesforce Marketing Cloud solution that simulates how a real bank identifies customers eligible for a premium credit card upgrade and nurtures them through a personalized email journey.

Unlike basic SFMC demos that simply import data and send an email, this project demonstrates enterprise-grade concepts such as:

  • Enhanced FTP

  • File Drop Automation

  • Import Activities

  • Staging Data Extensions

  • SQL Data Processing

  • Customer Spend Aggregation

  • Eligibility Rules Engine

  • Delta Processing

  • Journey Builder

  • Engagement-Based Decisioning

  • Personalized AMPscript Emails

This architecture is similar to what banks, insurance companies, and financial institutions use in production.

Business Problem

A bank wants to promote its Premium Credit Card.

However, sending upgrade offers to every customer would be ineffective.

The bank wants to identify customers who:

  • Have a strong credit score

  • Spend heavily using their card

  • Are not already premium card holders

  • Have not unsubscribed from marketing communications

Once identified, those customers should enter a nurture journey and receive upgrade communications.

Solution Architecture

Customer.csv
Transactions.csv
CreditScore.csv
Unsubscribe.csv

↓

Enhanced FTP

↓

File Drop Automation

↓

Import Activities

↓

Staging Data Extensions

↓

SQL Processing Layer

↓

Eligibility Engine

↓

Delta Processing

↓

Journey Entry Data Extension

↓

Journey Builder

↓

Personalized Emails

↓

Customer Upgrade Journey

Source Files

1. Customer.csv

Purpose:

Stores customer profile information.

Fields:

  • CustomerID

  • Email

  • FirstName

  • LastName

  • City

  • CurrentCardType

Why?

Customer profile information is required for personalization and eligibility evaluation.



2. Transactions.csv

Purpose:

Stores card spending transactions.

Fields:

  • TransactionID

  • CustomerID

  • Amount

  • TransactionDate

  • MerchantCategory

Why?

Premium card eligibility is partly based on spending behavior.



3. CreditScore.csv

Purpose:

Stores customer creditworthiness.

Fields:

  • CustomerID

  • CreditScore

  • RiskCategory

Why?

Banks use credit scores before offering financial products.



4. Unsubscribe.csv

Purpose:

Stores customers who opted out of marketing.

Fields:

  • EmailAddress

Why?

To avoid sending communications to unsubscribed contacts.



Enhanced FTP Setup

Files are uploaded to Enhanced FTP.

Folder:

Import/SFMCDrops/DailyFTPDrops

Why FTP?

Most enterprise systems exchange data using files.

Examples:

  • Core Banking Systems

  • Credit Bureau Systems

  • Data Warehouses

  • ERP Platforms

FTP remains one of the most common integrations in SFMC.



File Drop Automation

Automation:

AUT_CC_Daily_File_Ingestion

Trigger:

Contains = Customer

Why?

Customer.csv acts as the trigger file.

When Customer.csv arrives:

  • Transactions file already exists

  • Credit Score file already exists

  • Unsubscribe file already exists

This guarantees all required files are available before processing starts.

Staging Layer

Created Data Extensions:

CC_Customer_Profile_Stage

CC_Transaction_Feed_Stage

CC_Credit_Assessment_Stage

CC_Marketing_Suppression_Stage

Why Use Staging?

Benefits:

  • Easier troubleshooting

  • Better data quality

  • Supports enterprise ETL architecture

  • Prevents corruption of final audiences



Import Activities

Created:

IMP_CC_Customer_Profile

IMP_CC_Transaction_Feed

IMP_CC_Credit_Assessment

IMP_CC_Marketing_Suppression

Data Action:

Overwrite

Why Overwrite?

The source files represent the latest snapshot from source systems.

Overwrite ensures:

  • No duplicates

  • Accurate daily state

  • Cleaner processing



SQL Layer

This layer transforms raw data into business intelligence.

SQL 1 – Customer Spend Aggregation

Target DE:

CC_Customer_Spend_Summary

Fields:

  • CustomerID

  • MonthlySpend

  • TransactionCount

  • LastTransactionDate

Query:

SELECT
CustomerID,
SUM(Amount) AS MonthlySpend,
COUNT(TransactionID) AS TransactionCount,
MAX(TransactionDate) AS LastTransactionDate
FROM CC_Transaction_Feed_Stage
GROUP BY CustomerID

Why?

Raw transactions contain multiple rows per customer.

Marketing teams need customer-level insights.

This query converts:

100 transaction rows

into

50 customer summary rows.

Learnings:

  • SUM()

  • COUNT()

  • MAX()

  • GROUP BY





SQL 2 – Eligibility Engine

Target DE:

CC_Upgrade_Eligibility

Purpose:

Identify customers who qualify for an upgrade.

Business Rules:

CreditScore > 750

MonthlySpend > 5000

CurrentCardType <> Platinum

Not Unsubscribed

Query joins:

  • Customer Profile

  • Credit Assessment

  • Spend Summary

  • Suppression File

Why?

The Eligibility Engine was a SQL-based business rules layer that identified customers eligible for a premium credit card upgrade. It combined customer profile, spend summary, credit score, and suppression data and applied rules such as Credit Score > 750, Monthly Spend > 5000, not already a Platinum customer, and not unsubscribed. Only customers meeting all criteria were passed to the next stage of the campaign.

Not every customer should receive an offer.





SQL 3 – Delta Processing

Target DE:

CC_Upgrade_Eligibility_Delta

Purpose:

Identify newly eligible customers only.

Query Logic:

Compare:

CC_Upgrade_Eligibility

against

CC_Upgrade_Eligibility_Master

If CustomerID does not exist in Master:

Send to Delta

Why?

Without Delta Processing:

Same customers would receive the journey every day.

With Delta Processing:

Only newly eligible customers enter.

This is one of the most important enterprise patterns in SFMC.





SQL 4 – Journey Entry DE

Target:

CC_Upgrade_Journey_Entry

Purpose:

Populate the Journey Builder entry source.

Data Action:

Overwrite

Why?

Only current Delta contacts should enter the journey.



SQL 5 – Master Snapshot

Target:

CC_Upgrade_Eligibility_Master

Purpose:

Store latest eligibility snapshot.

Why?

Future Delta comparisons require a historical reference.

Without Master:

Delta processing cannot work.



Automation Flow

AUT_CC_Eligibility_Processing

Step 1

SQL_CC_Customer_Spend_Summary

↓

Step 2

SQL_CC_Upgrade_Eligibility

↓

Step 3

SQL_CC_Upgrade_Eligibility_Delta

↓

Step 4

SQL_CC_Upgrade_Journey_Entry

↓

Step 5

SQL_CC_Upgrade_Eligibility_Master

Why This Order?

Each SQL depends on the previous output.

Changing the order would break the process.



Journey Builder

Journey Name:

JB_Premium_Card_Upgrade_Offer_Journey

Entry Source:

CC_Upgrade_Journey_Entry

Why?

Only newly eligible customers should enter.



Email Strategy

Email 1



Pre-Approved Upgrade Offer

Purpose:

Introduce the premium card.

Wait 3 Days

Purpose:

Allow customers time to engage.

Engagement Split

Condition:

Opened Email 1?

Why?

Behavior should influence future communication.

Opened Path

Email 2



Premium Benefits Reminder

↓

Wait 5 Days

↓

Email 3

Final Upgrade Offer

Not Opened Path

Email 2

Alternative Subject Line

↓

Wait 5 Days

↓

Engagement Split

Opened?

YES

↓

Email 3



NO

↓

Exit Journey

Why?

Stop sending emails to disengaged customers.

This improves deliverability and customer experience.

AMPscript Personalization

Used:

%%FirstName%%

%%CurrentCardType%%

%%CreditScore%%

%%MonthlySpend%%

Examples:

Hello John,

Based on your monthly spend of $8,500 and strong credit profile, you have been identified as eligible for a premium card upgrade.

Why?

Personalized emails generate higher engagement than generic messages.


What I Learned

  • Enhanced FTP

  • File Drop Automations

  • Import Activities

  • Data Extension Design

  • SQL Query Activities

  • Aggregate Functions

  • Business Rule Implementation

  • Delta Processing

  • Journey Builder

  • Engagement Splits

  • AMPscript Personalization

  • Marketing Suppression Logic

  • Enterprise Architecture Patterns

Conclusion

This project demonstrates how Salesforce Marketing Cloud can be used as a complete customer decisioning and engagement platform.

Starting from raw FTP files, we built:

  • Data ingestion

  • Customer analytics

  • Eligibility scoring

  • Delta processing

  • Personalized journeys

  • Engagement-based nurturing

This architecture closely resembles real-world implementations used by banking and financial organizations and provides hands-on experience with several enterprise SFMC concepts.

Comments

Post a Comment

Popular posts from this blog

Mastering CASE Statement in Salesforce Marketing Cloud (SFMC SQL)

-
Image
  Mastering CASE Statement in Salesforce Marketing Cloud (SFMC SQL) In Salesforce Marketing Cloud (SFMC) , data alone is not useful unless you can convert it into actionable marketing insights . This is where the CASE statement becomes powerful. It allows you to: Segment users Personalize campaigns Define marketing strategies Automate decision-making directly in SQL In this blog, I’ll walk through real-world SFMC use cases , along with queries and outputs , exactly how they are used in marketing scenarios. 📊 Dataset Used (Customer_Master) We are using a sample Data Extension: 🔹 What is CASE in SFMC SQL? The CASE statement works like IF-ELSE logic , helping marketers categorize users dynamically. 👉 Instead of manually creating segments, SQL does it for you.  Use Case 1: Age Group Segmentation 🧾 Query SELECT FirstName, CASE WHEN Age < 18 THEN 'Minor' WHEN Age BETWEEN 18 AND 40 THEN 'Adult' ELSE 'Senior' END AS ...
Read more

A/B Testing in Salesforce Marketing Cloud (Step-by-Step Guide)

-
Image
   A/B Testing in Salesforce Marketing Cloud (Step-by-Step Guide) A/B testing is one of the most powerful features in Salesforce Marketing Cloud that helps marketers optimize email performance using real data. In this blog, I’ll walk you through how I created A/B tests for: ✅ Subject Line ✅ Preheader Let’s dive in 👇 📌 Step 1: Create Two Email Variations The first step is to create two different email versions with: Different subject lines Different preheaders Different content These variations will be used for testing performance. Email A    Email B    📌 Step 2: Create a New A/B Test Go to A/B Testing Click on Create New Test Give your test a name Select Subject Line Test Browse and select both email versions Enter: "vs Subject Line" for testing Click Next 📌 Step 3: Select Recipients Now choose your audience: Select from: List Data Extension Data Filter You can also define: Publicati...
Read more

📧 Creating & Sending a Test Email in Salesforce Marketing Cloud (SFMC)

-
Image
  📧 Creating & Sending a Test Email in Salesforce Marketing Cloud (SFMC) In this task, I learned how to create an email template and send a test email using Email Studio in Salesforce Marketing Cloud. This is a fundamental skill for email marketing because it ensures your email looks perfect before sending it to real users. 🚀 Step-by-Step Process 1. Go to Email Studio Login to SFMC Navigate to Email Studio 👉 This is where all email-related activities like content creation, sending, and tracking are done. 2. Open Content Builder Click on the Content tab 👉 Content Builder is used to create and manage emails, images, and templates. 3. Create a New Email Click on Create Select Email Message 4. Choose Creation Option You will see multiple options: Template Template + AMP HTML HTML + AMP Text Only Existing Email 👉 We select: Template Reason: Easy drag-and-drop builder No coding required 5. Select Layout Choose ...
Read more