Airline Loyalty Program

End-to-end Power BI analysis of a Canadian airline’s loyalty program.
Focused on enrollment trends, customer retention, and campaign ROI.


✈️ Airline Loyalty Program
Role: Senior Business Analyst
Tools: Power BI, DAX, Data Modeling, Data Quality & Profiling

🧩 Business Context
Northern Lights Air, a Canadian airline, launched a promotion (Feb–Apr 2018) to boost its loyalty program enrollments and flight activity. Leadership wanted to understand whether the campaign drove sustainable membership growth and increased customer engagement over time.

💡 Business Questions
What impact did the campaign have on loyalty program memberships (gross and net)?
Did specific customer segments or enrollment types respond more strongly?
How did the campaign affect flights booked by loyalty members—during and after the promotion?

🔍 Approach & Methodology
1. Data Preparation & QA
Profiled and validated ~400 K records across Customer Loyalty History, Flight Activity, and Calendar tables.
Cleaned column headers, standardized data types, and derived monthly “Start of Month” fields for consistent time-based joins.
Added binary cancellation flags to accurately track churn.

2. Data Modeling & DAX Enhancements
Built a star schema linking Calendar ↔ Customer Loyalty ↔ Flight Activity via monthly relationships.
Created reusable measures:
Total Enrollments, Total Cancellations, Net Members, Running Total of Net Members
Flights Booked (Current Year) vs. Flights Booked (Previous Year)
Implemented a Measures Table for organized model management and future scalability.

3. Analysis & Visualization
Developed a time-series dashboard showing enrollments vs. cancellations and cumulative membership growth.
Added comparison visuals for flights booked by loyalty status and enrollment type, enabling year-over-year insights.
Enabled interactive slicers to analyze seasonal effects and campaign impact by member segment.

📈 Key Insights
The campaign generated a sharp rise in enrollments during the promotion period (+37%), followed by a moderate cancellation uptick (~12%)—indicating short-term promotional success but limited retention beyond Q2 2018.
Net membership growth remained positive throughout the year, with the running total showing sustained gains post-promotion.
Loyalty flights booked increased notably in the months following enrollment, suggesting that new members quickly engaged with the airline.
Enrollment types tied to credit-card or premium offers showed the highest conversion and retention rates.

💼 Business Impact
This analysis provided executives with a data-driven view of customer retention and campaign ROI. By identifying which enrollment channels drove lasting engagement, the airline could:
Re-allocate marketing spend toward high-value acquisition channels.
Improve retention strategies (e.g., tiered incentives for new members).
Forecast future campaign performance using seasonally adjusted baselines.

🚀 Outcome & Value
Value to Business: Translated raw customer and flight data into actionable insights that informed strategic decisions on loyalty marketing and customer engagement.
Analytical Value: Demonstrated mastery of end-to-end analytics—from data preparation to DAX modeling and storytelling with Power BI dashboards.