- Project Overview
- Key Performance Indicators
- Data Sources
- Tools Used
- Data Cleaning/Preparation
- Feature Engineering
- Exploratory Data Analysis (EDA)
- Data Analysis & Visualization
- Results/Findings
- Recommendations
- Limitations
This project analyzes Northern Lights Air’s fictional loyalty program to uncover insights on customer retention, cancellations, and promotional impact. Using Excel, Power BI, and statistical techniques, the analysis explores:
📈 Impact of the Feb–Apr 2018 promotional campaign on customer enrolment and retention.
🎯 Top enrolment channels driving active and retained customers.
🔄 Behavioural shifts before vs. after loyalty program enrolment.
👥 Demographic segments (age, gender, location) influencing retention and cancellations.
The findings highlight key retention drivers, cancellation risks, and campaign success factors, showcasing data-driven decision-making in the competitive airline industry.
- Customer Flight Activity: Contains monthly-level flight data for each customer.
- Customer Loyalty History: - Contains demographic and loyalty status information.
- Calendar Dataset : - Contains formatted date fields and flags for start of year/month/quarter.
- Excel - Data Cleaning, Feature Engineering, Exploratory Data Analysis
- Power BI - Feature Engineering and Data Visualization
Key data cleaning tasks included:
- Accurate data types of the fields were assigned.
- Replaced negative salaries and missing salaries with median values to avoid skewing analysis.
EDA involved exploring the airline's data to answer key questions, such as:
- What's the total number of males and females in our loyalty programme?
- What's the distribution of loyalty members across different age groups?
- What's the overall retention rate?
After data cleaning and preprocessing in Excel, all files were imported into Power BI. Leveraging DAX formulas, interactive visualizations, and other advanced Power BI features, three dashboards were developed to comprehensively address the problem statement:
-
Purpose: Analyze customer enrolment patterns and behavioural drivers.
-
Visualization:
-
Purpose: Understand retention vs. cancellation trends.
-
Visualization:
-
Purpose: Evaluate the impact of the loyalty programme on customer engagement.
-
Visualization:
- The promotional period (Feb–Apr 2018) successfully boosted Enrolments, indicating strong marketing effectiveness.
- A high retention rate (88.16%) among promo-period enrolees suggests that short-term campaigns can lead to long-term loyalty.
- Demographic factors such as age and income influenced customer loyalty, with younger and higher-income customers showing greater retention.
- Mobile App ,Website channels proved to be the most effective in acquiring and in retaining customers mobile app and instore are most effective. Instore brings customer with highest average CLV.
- Behavioural changes post-Enrolment — such as increased travel frequency and points accumulation — highlighted greater engagement among loyalty members at the same time decline in points redemption rate shows customer feel less rewarded .
- Normalized cancellation analysis revealed that absolute cancellations alone do not indicate loyalty strength — city-level performance varied when adjusted for Enrolment volume.
- People with low travel frequency have high cancellation rate but at the same time they generate high average CLV may be due to long distance travel so special steps should be taken to keep them retained.
- Cancelled Customers actually giving more CLVs than active and in cities like Winnipeg , Sudbury, St.Jhones cancelled people generates more CLVs than average CLV.
-
Plan better marketing campaigns by learning what worked well and what didn’t.
-
Focus on the best-performing channels (like mobile app or website) to acquire more loyal customers.
-
Introduce tailored offers for different age groups:
-
Younger customers → funny rewards or bonus points for referrals
-
Older customers → simple benefits like priority boarding or early check-in offers
-
-
Introduce special deals and discounts in states and provinces with high cancellation rates to win back customers.
-
Reward long-term program users with bonuses after 3 or 6 months of membership to reduce cancellation rates.
-
Improve redemption policies for loyal customers, as poor policies may cause lower retention and higher cancellations.
-
Collect feedback from customers who leave the program, using their responses to enhance the program in the future.
-
Test different types of offers across various channels and customer types to identify what works best.
-
Provide personalized rewards for low travel frequency customers who show the highest average CLV: luxury upgrades, bonus points for next bookings, priority access to new services, and limited-time offers.
-
Monitor key loyalty program metrics regularly to track improvements and overall program performance.
- I had to replace all the negative and missing salaries with median salaries to avoid skewing analysis. They would have affected the accuracy of my conclusions from the analysis although by using median salaries I had tried to reduce the effect as much as possible.
- Due to lack of enrollment channel data and age related informations for each customers I had to generate them using Power BI DAX formulas and with the help of generative AI tools. They would have affected the accuracy of my conclusions from the analysis