Showcase Power BI - NLA Airline Loyalty Program

Maven Analytics' Guided Project – AIRLINE LOYALTY PROGRAM ANALYSIS is part of the course "Power BI Specialist".

Summary:

Chapter 1. Business Case

The NLA leadership team lacks a consolidated view of patterns/trends across locations, hindering strategic decision-making. They need clear insights into how the recent promotion affected loyalty program enrollments, cancellations, and flight bookings.

Objective: Boost enrollment in “Northern Lights Air's (NLA)” loyalty program and evaluate the recent promotion's effectiveness to guide future strategies.

Situation: As Lead Marketing Analyst for NLA, my task is to assess the success of a February to April 2018 promotion offering 1.5x loyalty points to new members for flights booked through the year.

Task: Analyze data on loyalty program enrollments, cancellations, and flights booked during and after the promotion to provide actionable insights for strategic decision-making.

Core Objectives (3)

Exploratory Data Analysis - EDA:

· Examine loyalty program enrollments and cancellations

· Review flight booking data for new members from the promotional period

· Identify trends and patterns

Data Modeling:

· Create a robust data model using best practices

· Enhance data model: add calculated columns and measures for deeper analysis

· Ensure efficient data retrieval and accurate reporting

Visualization:

· Develop interactive report to consolidate KPIs and trends

· Ensure visualizations are easily understandable for the leadership team

· Use Gestalt principles to visualize key findings – visual hierarchy

Chapter 2. Action Plan

Step 1: Data Collection and Preparation

· Dirty data, data profiling and enhance data quality

· Gather data on loyalty program enrollments, cancellations, and flight bookings from February to April 2018 and beyond.

· Clean and preprocess the data to ensure accuracy and completeness.

Step 2: Data Modeling

· Design a data model that integrates enrollment, cancellation, and booking data.

· Add calculated columns and measures, such as enrollment growth rate, cancellation rate, and average flight bookings per new member.

Step 3: Analysis

· Perform exploratory data analysis (EDA) to uncover trends and patterns.

· Analyze the impact of the promotion on loyalty program enrollment and flight bookings.

Step 4: Visualization

· Create interactive dashboards using Power BI, incorporating Gestalt principles to ensure clarity and effectiveness.

· Highlight key findings, such as changes in enrollment rates, cancellation trends, and booking patterns.

Step 5: Reporting

· Prepare a comprehensive report summarizing the analysis and visualizations.

· Present the findings to the NLA leadership team, providing actionable recommendations for future promotions and strategic decisions.

Step 6: Expected Outcomes

· Obtain a clear understanding of the effectiveness of the promotion in increasing loyalty program enrollment and flight bookings.

· Insight into any unintended consequences, such as increased cancellations.

· A consolidated view of trends, enabling the NLA leadership team to make data-driven decisions.

· Recommendations for optimizing future promotions and loyalty program strategies.

· By addressing the business problem with a well-structured analysis and clear visualizations, I will provide the NLA leadership team with the insights they need to enhance their loyalty program and drive strategic growth.

Chapter 3: Show Insights & Impact

§ Loyalty Member Enrollments (by month)

Findings during Promotion (Feb-Apr 2018):

· Significant increase in enrollments | February: 295 | March: 330 | April: 244

Post-Promotion:

· May: 244 enrollments | June: 272 | July: 231

· Drop in enrollments observed in August, September, October, aligning with the seasonal pattern of 2017.

· End of 2018 showed an increase again

· November: 246 enrollments | December: 238 enrollments

Impact for the Business:

· The promotion effectively boosted enrollments during and immediately after the campaign period.

· Post-campaign enrollment increases suggest a sustained interest possibly driven by the promotion’s momentum.

Recommendations:

· Implement similar promotions periodically to sustain and boost enrollments.

· Investigate August to October drop to understand seasonal factors better and mitigate future dips.

§ Loyalty Member Cancellations (by month)

Findings during Promotion:

· No significant anomalies in cancellations; trends consistent with 2017 seasonal patterns.

Post-Promotion:

· Notable spikes detected in cancellations 2018 AUG 74 | DEC 63 compared to 2017 AUG 44 | DEC 50.

Impact for the Business:

· Stable cancellation rates during the promotion indicate members retained interest.

· The spikes in August/December suggests a possible issue that needs investigation (e.g., service quality, seasonal dissatisfaction).

Recommendations:

· Conduct a root cause analysis for the spikes and address identified issues.

· Implement retention strategies, especially post-promotion, to maintain low cancellation rates.

§ Net Loyalty Members & Running Total (by month)

Findings during Promotion: Significant increases in Net Loyalty Members:

· February: 295 | March: 329 | April: 343 |

Positive impact on running total

· February: 261 | March: 587 | April: 928

Post-Promotion:

· Sustained increase in Net Loyalty Members, notably:

· June: 226 | November: 214 |

Running total shows continuous growth:

· June: 14,431 | November: 15,528

Impact for the Business:

· The promotion had a clear, positive impact on the growth of Net Loyalty Members.

· Running total growth indicates long-term benefits from the promotional campaign.

Recommendations:

· Maintain and enhance loyalty program benefits to ensure continuous growth.

· Monitor Net Loyalty metrics closely to identify and replicate successful strategies.

§ Flights Booked & Enrollment Type (by month)

Findings: significant increase in Flights Booked “type Promotion 2018”:

· May: 6,224 Flights | June: 8,104 Flights | July: 8,460 Flights | August: 7,476 Flights

Significant increase in Flights Booked “type standard” in 2018:

· May: 21,121 Flights | June: 25,736 Flights | July: 29,476 Flights | August: 25,332 Flights

· In 2018 totally increased flights booked: contains 10,183 in comparison to 2017 same period!


Impact for the Business:

· Understanding booking patterns helps tailor future promotions to maximize flight bookings.

Recommendations:

· Develop targeted campaigns based on booking behavior to maximize engagement and revenue/CLV.

§ Total CLV & Marital Status (by month)


Findings Marital Status Impact on CLV:

Married Members:

· May: Total CLV $ 5,123,500 | June: $ 5,624,800 | July: $ 6,130,200 | August: $ 6,256,700

Single Members:

· May: Total CLV $ 2,452,300 | June: $ 2,698,400 | July: $ 2,976,500 | August: $ 2,834,900

Impact for the Business:

· Understanding the CLV differences between married and single members helps tailor marketing strategies to target high-value demographics effectively.

· Higher CLV for married members suggests they might be more loyal or have higher spending power.

Recommendations:

· Develop targeted marketing campaigns focused on married members to maximize their engagement and further enhance their CLV.

· Investigate and implement strategies to increase CLV for single members, potentially by offering tailored benefits or incentives.

Impact for the Business:

· Understanding CLV across different demographics can help in personalizing marketing strategies.

Recommendations:

· Collect and analyze demographic data to tailor loyalty programs and maximize CLV.

§ Geospatial Analysis

Findings provinces:

· Most successful provinces: Ontario, British Columbia, Quebec.

· These provinces lead in enrollments, cancellations, flights booked and Net Loyalty Members.

Impact for the Business:

· Identifying regional performance helps focus marketing efforts where they are most effective.

Recommendations:

· Design targeted marketing campaigns for successful provinces to sustain and grow the program.

· Develop strategies to enhance engagement in less successful regions to balance overall performance.

Summary of Key Insights and Recommendations:

· Enrollments: Significant boost during the promotion, sustained post-promotion.

· Cancellations: Stable during promotion with a post-promotion spike(s) needing investigation.

· Net Loyalty Members: Strong growth during and after the promotion.

· Flights Booked: Significant increases in both promotion and standard types.

· CLV & Marital Status: Higher CLV for married members; opportunities to enhance CLV for single members.

· Geospatial: Success in key provinces; opportunities in less successful areas.

· To capitalize on this success, periodic promotions, targeted marketing, and continuous monitoring of loyalty metrics are essential. By addressing the identified challenges and leveraging the insights gained, NLA can optimize future promotional strategies and enhance overall customer engagement and loyalty.

Chapter 4: Data Storytelling - Visualizations Used

Homepage:

Implemented a homepage which I made in PowerPoint. Imported it in Power BI and applied page navigation for (3) pages: (Net) Loyalty | Flights and Customer Lifetime Value (CLV) | Geospatial Analysis. The other pages also contain a separate home made “Canvas background”.

New Slicers:

Enhanced slicing and dicing capabilities for UI/UX (promotions and time intelligence date-hierarchy month, quarter and year).

Line Chart(s):

Displays patterns/trend(s) in loyalty member enrollments and cancellations concerning a longer period (starting from 2012/2013 till 2018).

Clustered Column Chart(s):

Displays “comparisons within “Net Loyalty Members inclusive Running Total”. Whereas TopN 3 periods are automatically highlighted (dark blue) to enhance readability.

Clustered Column Chart(s):

Displays “Flight Booked inclusive Enrollment Type and Total CLV based upon Marital Status”. It provides a clear view of which marital status is most successful during and after 2018 promotion. In this case (settings) I applied for the new feature “overlapping” bars to maintain optimal UI/UX and readability.

Field Parameter Total Metrics:

Enrollments | Cancellations | Flights Booked (Previous Year) | Net Loyalty.

Matrix:

Breakdown by Province and above-mentioned Field Parameter Total Metrics.

Maps:

Geospatial breakdown by province combined with above mentioned Field Parameter Total Metrics for pinpointing successful locations. I’ve applied dynamic titles to enhance readability.

10-Second Rule:

Visualizations are chosen/designed to be easily understood within 10 seconds, ensuring quick and effective communication of insights.

Chapter 5: Provide Technical Depth

Sources: Excel | Power BI | Power Point | ChatGPT

Dataset: CSV | Multiple tables | 412,230 records | 28 fields

Data Preparation:

· Reviewed table columns for blank or null values.

· Ensured correct data types and established primary and foreign keys.

Relational Model:

· Created a Star Schema with proper filter directions (1: Many) between dimensions and fact table(s).

· The “Customer Flight Activity” and “Customer Loyalty History” tables serve as fact tables containing detailed transactional data, such as flights and cancellations, while the “Calendar” (Bridge) table is a dimension table that provides time-related context.

Measures and Fields:

· Created a separate measure (table)

· Added field parameters for extra filtering capabilities.

Technical Challenges and Solutions:

· (New) slicer: contains hover and selected status. Selected Status presents a checkmark when selected. I’ve created the following measure to make it work:

Checkmark = https://www.dropbox.com/scl/fi/z9fxqzk0a7cvqr3ife7em/Checkmark-Button.svg?rlkey=vr24bem35dtcs3eti4a5ho05z&st=rp6git25&raw=1 .

· Challenge: Integrating geographical data for accurate visualization.

· Solution: I’ve used the column “Customer Loyalty History [Province]” and modified “Date Category” to “State or Province”.

· Field Parameter Total Metrics: maintain the correct values in the map view after selected value has been applied.

Conclusion:

The February to April 2018 promotion offering 1.5x loyalty points to new members for flights booked throughout the year was highly successful. It significantly increased loyalty program enrollments and had a positive effect on net loyalty members and running totals. Post-promotion periods continued to show positive trends, indicating lasting impact.