Let’s explore some data wrangling techniques to understand how loyal airline customers are distributed across different demographic segments. Who are the most loyal customers and how are they segmented in different locations?
Here is the challenge. Let’s use this thread to post our solutions to it, which should be uploaded to your public KNIME Hub spaces with tag JKISeason4-8 .
Need help with tags? To add tag JKISeason4-8 to your workflow, go to the description panel in KNIME Analytics Platform, click the pencil to edit it, and you will see the option for adding tags right there. Let us know if you have any problems!
In this one, I am reading the data, grouping the flight history and joining with customer data. Then removing the customers that cancelled their loyalty membership.
I calculate the loyalty score using a simple formula in which we can specify weights (e.g., number of flights are more important). This is not using config nodes, but directly within the expression node - this could be improved. Values are normalized before calculation.
And Choropleth Map component always improve dashboarding.
The visualization allows the user to filter the number customer we want to keep (based on the loyalty score, best first).
Data Preparation
I filtered out customers who are no longer enrolled and calculated a loyalty score using a weighted combination of normalized CLV and normalized accumulated points.
Data Visualization
Users can select different fields to explore the data and view the top 10 most loyal customers, ranked in descending order by their loyalty score.
Hey BRO,
I just had a chance to explore your submission — fantastic job! Your visual storytelling is incredibly clear, and that geo-mapping analysis added a whole new dimension I haven’t quite dared to explore yet myself.
While reviewing your Loyalty Score formula, I noticed you included both total_flights and flights_with_companions. Just wanted to share a small perspective I picked up during my process: in the dataset, total_flights is the sum of flights_booked and flights_with_companions, so I realized that including both total_flightsand its components might unintentionally overweight that particular metric.
Of course, context and assumptions matter — and your approach still delivers solid insights. I’ve genuinely learned a lot from the way you presented the data, especially with those elegant visuals!
Thanks for the inspiration — let’s keep pushing each other forward!
What an inspiring geo-journey, solution WOW !
Your mastery of the Geospatial extension is nothing short of monumental—each insight more dazzling than the last. I especially loved the way you layered customer clusters with a sunburst; it’s like watching a data-driven blockbuster unfold.
One tiny (and admittedly nit-picky) footnote: don’t forget that there’s a London in Ontario, too—not just the one across the pond!
Thanks for sharing such a polished walkthrough—your work keeps raising the bar for all of us in the KNIME community.
Looking forward to your next geo-masterpiece! …
P.S. These challenges are starting to feel like a full-time gig—now I really need to hustle to finish mine!
Hey @PVergati
Thanks for your message! You always have nice words.
Agreed, my formula can (needs to) be improved - this metric might be overweighted.
It depends also on the rules of the company - what about if the company cares about the overall engagement (volume of travel, total_flights) and wants to reward group travels (flights_with_companions)? Just thinking out loud.
Really appreciate the kind words, it totally made my day!
And yep, you got me I totally missed that there’s a London in Ontario too. Thanks for pointing it out so nicely! I usually squeeze these challenges in between other stuff I’m working on, so sometimes the details slip past me.
Can’t wait to see what you come up with this week, let’s keep having fun with these challenges!
• An interesting TIP I don’t know if someone already noticed it but I discovered that “Newfoundland” must be “Newfoundland and Labrador” to geocode correctly—turns out place names can be trickier than booking codes!
I’m still mastering KNIME’s map tools, so if anyone has tips for handling duplicate place names (hello, US cities with the same name!), I’d love to learn your approach. Let’s keep sharing ideas and refining our loyalty models together!
One of the cool features of KNIME is the ability to repurpose old workflows.
Wanted to map the customer locations and some standard tries to get the geocordinates failed - with mapping of some of the cities to strange non-Canadian locations. Ended up using the AI query component from an earlier challenge to pull them.
As an aside, I really wanted to map the postal codes but could not do this from any open source.
Also used the AI ML component from an earlier challenge to try and identify key features for Loyal and not-Loyal customers (ie the one’s who cancelled). This gave length of membership and points earned as key categories - not surprising.
The visualization needs some more work but enough is enough!
Here’s my solution. It produces a static table:
Top 100 Loyal Customers (# Flights)
and the following dynamic outputs:
Number of Loyalty Card Types table
Number of Loyalty Members chart
Total Flights chart
Booked flights chart
Companion Flights chart
I made no attempt to construct a “loyalty score.” I simply used the number of flights as a loyalty indicator. It seems to me that things like flight length have little to do with customer loyalty. It certainly can be argued that long flight customers may be more desirable, but the loyalty weight is questionable without knowing about viable competition, etc.
I used the Generic ECharts which proved to be tricky if you want them to dynamically change without manually recoding. I finally figured out how to pass them an array flow variable which did the trick. Here are the steps:
Due to the limitations of my corporate laptop, I couldn’t perform neither API related nor AI related tasks for some weeks but here I am back with a proposed solution to the 8th challenge!
Due to time constraints this week, I couldn’t explore every angle, but I aimed to make the report visually appealing and easy to navigate. I’m always open to feedback and suggestions for improvement!
Now boarding: talented data visualization specialist with a flair for elegance and a tolerance for long loops. Final destination: Singapore Airlines’ Advanced Reporting Department.
if this is what you deliver when you’re short on time… I’m both impressed and mildly intimidated . Your 31-page PDF isn’t just a KNIME report — it’s basically a first-class cabin experience in data form. All that’s missing is a glass of champagne and a hot towel … (I’ll write you my address to have a complete service).
The 5–10 minute loop? Totally worth it. Feels less like a delay and more like an intentional intermission to admire the view .
You didn’t just present data — you staged a performance. Bravo