Banyan - Sutra Project Workshop 4 Session
Objectives
Cross Sell and Upsell and Increase Lifetime value of customer
Question that needs to be answered: What campaign should i run to achieve above
Business lines in scope:
- CM (Hotel)
- MYK (Hotel)
- PB (Hotel)
- Banyan Tree Essentials (SPA Retail)
Marketing Campaign
Themes:
- Visit New Property
- Visit Another Property
- Come Back to Hotel One More Time
- Visit Shopify
- Come back to Shopify One More Time
- Upsell Purchases
Data Ingestion, Cleaning & Prep
- Data Ingestion from Oracle On Premise DB, Oracle Cloud, Shopify and Google Analytics [Tool: VAL API Integration, VAL Database Integration]
- Deduplicate Customer List as Universal Customer List [Tool: VAL Dedup]
- Data Prepping [Tool: VAL Function Fields, VAL SQL]
Visit Shopify
Objective: Increase engagement with BT customers through retail outside their stay at BT
Target Audience: BT customers who have not spent at Shopify
Campaign ROI
Step 1: Determine overlap of customers across engagement points
Step 2: Determine customer spent of overlap engagement
Step 3: Calculate potential ROI [Tool: VAL Rules Mining & ROI]
Source | Target | # Potential Customers | Target Avg LT Spent | Confidence | Additional Revenue | Max Potential Revenue | Return on Investment | Target
Effectiveness | support | support_confidence |
['CM'] | ['MYK', 'PB'] | 14473 | 3,287 | 0.02% | 9,990 | 47,572,860 | 9 | 1 | 0.01% | 0.00% |
['MYK', 'PB'] | ['CM'] | 34413 | 1,523 | 30.00% | 15,721,515 | 52,405,049 | 15,721 | 1 | 0.01% | 0.00% |
['PB'] | ['MYK', 'CM'] | 169 | 2,737 | 1.78% | 8,211 | 462,585 | 7 | 1 | 0.01% | 0.00% |
['MYK', 'CM'] | ['PB'] | 47237 | 875 | 0.20% | 83,053 | 41,320,015 | 82 | 1 | 0.01% | 0.00% |
['CM', 'PB'] | ['MYK'] | 14632 | 3,296 | 30.00% | 14,467,887 | 48,226,292 | 14,467 | 1 | 0.01% | 0.00% |
['MYK'] | ['CM', 'PB'] | 34254 | 1,518 | 0.01% | 4,679 | 51,985,559 | 4 | 1 | 0.01% | 0.00% |
['MYK'] | ['SHOP', 'CM'] | 34254 | 1,069 | 0.01% | 3,296 | 36,623,663 | 2 | 1 | 0.01% | 0.00% |
['SHOP', 'CM'] | ['MYK'] | 20304 | 3,296 | 20.00% | 13,384,180 | 66,920,901 | 13,383 | 1 | 0.01% | 0.00% |
['PB'] | ['CM'] | 169 | 1,523 | 5.92% | 15,228 | 257,358 | 14 | 1 | 0.02% | 0.00% |
['CM'] | ['PB'] | 14473 | 875 | 0.07% | 8,735 | 12,660,088 | 8 | 1 | 0.02% | 0.00% |
['CM'] | ['MYK'] | 14473 | 3,296 | 10.30% | 4,910,945 | 47,702,236 | 4,910 | 1 | 2.81% | 0.29% |
['MYK'] | ['CM'] | 34254 | 1,523 | 4.35% | 2,269,087 | 52,162,920 | 2,268 | 1 | 2.81% | 0.12% |
['PB'] | ['MYK'] | 169 | 3,296 | 5.92% | 32,959 | 557,015 | 32 | 1 | 0.02% | 0.00% |
['MYK'] | ['PB'] | 34254 | 875 | 0.03% | 8,689 | 29,963,287 | 8 | 1 | 0.02% | 0.00% |
['MYK', 'SHOP'] | ['CM'] | 39968 | 1,523 | 2.27% | 1,383,447 | 60,864,354 | 1,382 | 1 | 0.01% | 0.00% |
['CM'] | ['MYK', 'SHOP'] | 14473 | 2,726 | 0.02% | 8,285 | 39,454,166 | 7 | 1 | 0.01% | 0.00% |
['SHOP'] | ['MYK'] | 5846 | 3,296 | 2.26% | 435,074 | 19,268,104 | 434 | 1 | 0.25% | 0.01% |
['MYK'] | ['SHOP'] | 34254 | 112 | 0.39% | 14,715 | 3,822,153 | 14 | 1 | 0.25% | 0.00% |
['MYK', 'CM'] | ['SHOP'] | 47237 | 112 | 0.20% | 10,594 | 5,270,830 | 10 | 1 | 0.01% | 0.00% |
['SHOP'] | ['MYK', 'CM'] | 5846 | 2,737 | 0.05% | 8,161 | 16,001,616 | 7 | 1 | 0.01% | 0.00% |
['CM'] | ['SHOP'] | 14473 | 112 | 0.10% | 1,680 | 1,614,936 | 1 | 1 | 0.03% | 0.00% |
['SHOP'] | ['CM'] | 5846 | 1,523 | 0.26% | 22,879 | 8,902,447 | 22 | 1 | 0.03% | 0.00% |
Campaign Flow
Campaign Organisation
Dataset
Customer Filter
- Customer last engagement across Shopify and Hotel post 1 Jan 2017
- Remove Purged customer
- Remove Negative spend
- Remove Banyan Staff using banyan.com email
Transaction Filter
- Remove Cancelled Reservation
- Transaction till 31 Dec 2022 for all hotels and Shopify
- Remove Pseudo Transaction identified by room
Customer Segments
Step 1: Identify spent behaviour of customer at BT hotel that goes to Shopify
Purpose
- Used to identify spent profile of customer who have spent at Shopify
- Used to identify similar spent profile as above who have not spent at Shopify
- For customers who have not spent time at BT hotel, use this to identify what they have engaged with and use images or text to personalise content
Steps
- Identify hotel spent cluster [Tools - VAL Cluster]
- Identify shopify spent cluster [Tools - VAL Dashboard, VAL Function Fields or VAL Cluster]
- Identify RFM of hotel [Tools - RFM Analysis]
- Identify RFM of shopify [Tools - VAL RFM]
Findings: [Tools - VAL Dashboard]
- Population of customers in different clusters split by different dimensions (Hotel Spent)
- Population of customer per cluster
- Demographic per cluster (Age, Country, Kids Flag)
- Hotel RFM Label/Segment
- Population of customers in different clusters split by different dimensions (Shopify Spent)
- Population of customer per cluster
- Demographic per cluster (Age, Country, Kids Flag)
- Shopify RFM Label/Segment
- Population of customers in different clusters split by different dimensions (Hotel and Shopify Spent)
- Population of customer per cluster
- Demographic per cluster (Age, Country, Kids Flag)
- Hotel/Shopify RFM Label/Segment
- Behaviour of existing customer spent in Hotel whom have already spent in Shopify
- People who spent in Shopify don’t spend a lot in hotel.
- People who spent in Shopify don’t stay much in hotel
List of customers with all attributes combined with analytics data from above
Step 2: Calculate recency of visit to existing establishments and which establishment
Purpose
- To identify customers that have fresher memory of BT hotel experience (0 - 3 years)
- Use images of most recent visit to increase familiarity to experience
Steps:
- Filter base on the dimensions and metrics available in universal customer list [Tools - VAL Workspace]
Step 3: Identify customers with children or no children
Purpose
- Use different images or wordings to increase personalisation of content
Steps:
- Filter base on the dimensions and metrics available in universal customer list [Tools - VAL Workspace]
Step 4: Identify what existing customer of BT hotel that goes to Shopify purchase
Purpose
- Use to identify bundle to create for sales or image of product to use to increase chances of sales
Steps
- Perform product basket analysis to identify most bought product combination [Tools - VAL Basket Association]
Step 5: Identify what time customers buy on Shopify and which country they are from
Purpose
- Use to identify timing for marketing email to be sent out for each customer
Steps
- Identify timing for conversion on Shopify [Tools - VAL Dashboard]
Step 6: Validate hypothesis and identify other potential relationship
Purpose
- Get different perspective of relationship to identify unidentified ones or validate existing one
Steps
- Perform correlation analysis of all customer attributes (demo, spent, stay, rfm, etc) [Tools - VAL Correlations]
Findings
- Day Count from Last Shopify Spent is negatively related to Day Count Last Resort Visit