ANNIVERSARY CAMPAIGN
Gourmet Table
Navigate To:
Description
An anniversary campaign for “Gourmet Table”, built in Salesforce Marketing Cloud using SQL.
Scenario
Gourmet Table, a nationwide restaurant chain with an exclusive membership program, wants to celebrate the one-year membership anniversary of their loyal customers by offering them a personalized discount for their next in-store purchase when they scan their membership card.
The offer encourages members to visit a store and is valid for one month from the date the email is sent.
Discounts vary based on the member’s total spend during their first year as a member, making this campaign highly personalized and rewarding.
A reminder email is sent out two weeks later for any member who has yet to redeem their discount code.
Target Audience
-Members are selected on their 1 year anniversary from member joined date
-Members who opted into email
-Members with at least one transaction made within the first year
Email Field Requirements
-Dynamic Discount Percentage (based on spend range)
-Dynamic Expiration Date (one month after the email is sent)
-Last Store Visited (to encourage them to revisit)
Discount Rules
The discount percentage depends on the total spend during the one-year membership period:
Spend Range 1: $0 – $500 → 10% Discount
Spend Range 2: $501 – $1,000 → 15% Discount
Spend Range 3: $1,001+ → 20% Discount
SQL Features:
-Joins
-Subqueries
-Aggregations
-Conditional Logic
-Date & Time Functions
-Inclusion/Exclusion Criteria
Studios/Builders:
-Email Studio
-Contact Builder
-Automation Studio
-Journey Builder
Project Walkthrough:
Step 1: Set up and imported data into Data Extensions in Email Studio
Membership_Master_DE:
Contains members and their associated contact details
Transactions_1_DE:
Contains transactional data associated with each member
*There may be duplicate records for each member, as a member can make multiple transactions across different stores*
Redemption_Log_DE:
Contains members who have already redeemed along with their date of redemption
Step 2: Built the automation containing three SQL Query steps in Automation Studio
Automation:
SQL Query #1: 1Y_Membership_Anniversary_Staging
Code:
Summary:
Resulting Data Extension (1Y_Membership_Anniversary_Staging):
Data Validation:
Bob Green (MemberID: M004):
Transaction DE Criteria:
-Calculation of total spent within last year
-Including members who have made a transaction within the last year
Staging DE Criteria:
-MembershipDiscount of 15% because TotalSpent = $800.00 (within Spend Range 2: $501 – $1,000)
-Dynamic Expiration Date (one month after the email is sent)
-Including members who are opted into email (OptInStatus = True)
-Including members on their 1 year anniversary from member joined date
Journey Entry DE Criteria:
-Checking that records do not exist in Journey Entry DE already (no repeat records as it is one-time only)
-Not applicable at this stage as data extension has not been populated by other SQL Query steps yet
David Lee (MemberID: M006):
Transaction DE Criteria:
-Calculation of total spent within last year
-Including members who have made a transaction within the last year
Staging DE Criteria:
-MembershipDiscount of 10% because TotalSpent = $300.00 (within Spend Range 1: $0 – $500)
-Dynamic Expiration Date (one month after the email is sent)
-Including members who are opted into email (OptInStatus = True)
-Including members on their 1 year anniversary from member joined date
Journey Entry DE Criteria:
-Checking that records do not exist in Journey Entry DE already (no repeat records as it is one-time only)
-Not applicable at this stage as data extension has not been populated by other SQL Query steps yet
SQL Query #2: 1Y_Membership_Anniversary_JourneyEntry
Code:
Summary:
Resulting Data Extension (1Y_Membership_Anniversary_JourneyEntry):
Data Validation:
*In between the steps, I manually updated one of record M001’s transactions in the Transactions_1_DE for testing purposes, resulting in an updated MembershipDiscount of 20%*
Otherwise, the records are identical to results in 1Y_Membership_Anniversary_Staging data extension
SQL Query #3: 1Y_Membership_Anniversary_JourneyEntry – Update Flags
Code:
Summary:
Resulting Data Extension (1Y_Membership_Anniversary_JourneyEntry):
Data Validation:
Records with MemberID of M001, M006, M007, M010 all have HasClaimedDiscount = True
These same records appear in the Redemption_Log_DE (indicating they’ve already redeemed their discount), confirming that HasClaimedDiscount = True was correctly applied in the 1Y_Membership_Anniversary_JourneyEntry data extension
Step 3: Created “Journey Builder DEs” Attribute Group and linked to 1Y_Membership_Anniversary_JourneyEntry (Contact Key to MemberID) in Contact Builder
Step 4: Created “Master Data” Attribute Group and linked to Membership_Master_DE (Contact Key to MemberID)
Step 5: Built the journey and set the appropriate settings in Journey Builder
Journey:
Decision Split:
Contact Data is used in the decision split to ensure journey reacts to the most up-to-date customer information
Contact Evaluation Settings:
Evaluate new records only, as the Journey Entry DE will become a log to capture all records that entered the journey along with their entry dates
Journey Settings:
