Forecast Sales System for Airline - Part IV

Executive summary

Within a team I developed an automatic sales system for a undisclosed airline company to replace Sabre. System uses an innovative math model to predict demand and open different sets of offers for sale to maximize revenue and amount of passengers to transfer.

The first version of the the system had been delivered and presented to stakeholders. Propotype on the subset of product data exceeded results of existing solutions on the market.

The core of the system is an innovative math model which is under NDA of my client.

Engineering team was responsible for building infrastructure and implementing math models in code. System includes:

  • The sources of inventory messages which comes over Kafka
  • Message Queue module of the system
  • Math forecast module of the system
  • ML model of the system
  • Postgres databases with over one billion records

Project required from me a cross-displinary knowledge from variety of different fields from business analysis to delivery management.

Tech stack and skills:
Python - Pytest - MyPy - Pydantic - SQLAlchemy - Asyncio - SQL - Postgres - Math - ML - Docker products - Kafka - Business Analysis - Project Management - Delivery Management - Database Optimization - Data Migration

The customer

Worldwide airline company with headquarter in Russia

The problem

Due increased tensions in the political fields part of the western companies left Russian market. It created a demand for new products.

Our goal was to replace Sabre on Russian market and use all existing achievements in math and airline's business domain to create superior system.

Technical and management challenges

Test task

Before starting work and signing the contract, a test task was implemented to confirm skills to the customer in the technologies used in the project.

The test project is a Proof-of-concept (PoC) of a data pipeline for a flight stream. The project is based on Kafka, Kafka Connect and PostgreSQL with a REST server in Python. Everything is containerized in Docker and deployed via Docker Compose.

To read further: Docker-based Kafka - Kafka Connect - PostgreSQL pipeline with automatic export data from queue to database with REST server on Python to read this data. Intended for needs of the airline.

Business Analysis

  • errors in the specification were found and corrected
  • a lot of things not described in the specification were clarified and agreed upon with mathematicians, ML specialists, colleagues from another customer's team (work in this direction was carried out throughout the project on a weekly basis)
  • functionality requirements were formalized at the ticket level in CRM with a description of use cases (User stories) and technical comments on the implementation

Managerial functions

  • requirements for functionality were clarified and formalized at the ticket level in CRM
  • requests for changes to requirements began to be recorded at the ticket level in the backlog
  • each call began to be accompanied by minutes: a recorded list of clarified issues and agreements reached
  • regular demonstration of the developed functionality to the client and feedback collection
  • change management in requirements according to the "iron triangle" of requirements management was introduced into the project (partially implemented)
  • systematic work was carried out to transfer the project work to a number of best practices and processes so that the success of the project was not tied only to the personal heroism of each of its participants, but was steadily and systematically achieved through a number of practices and methods, minimizing known risks
  • definition of key project stakeholders and building regular communication with them: requirements collection, regular demonstration of results, feedback collection (the task is not completed)

To read further: Forecast sales system for an airline company, part III: overcoming organization and management challenges

Raising the level of development to the world standards

  • insistence on preparing comprehensive documentation on application deployment
  • eliminated the situation of a broken master (application code always ready to be shown to the client):
    • closing the master branch for direct recording
    • switching to working with code via Pull Requests (PR) in the git-flow process
    • switching to working with code in separate branches in the git-flow process
    • regular code review by other team members
    • writing and running tests before each PR merge into the master branch
    • proposed raising Continues Integration\Continues Deployment (CI\CD) on the project (not implemented due to high workload and work on higher-priority tasks)
  • introduced the practice of covering the delivered functionality with integration and unit tests
  • transferred the project from a procedural programming style to an object-oriented approach
  • introduced architectural approaches that allow scaling and maintaining the system
  • a proposal was made on options for overcoming the unavailability of key software (docker) due to political risks
  • a proposal was made to increase the security of the system being developed - resistance to hacking and thereby reduce the risk of significant losses for the customer
A lot of work has been done in this area, but many similar tasks are also awaiting their turn according to priorities.

ML module

  • the subject area was analyzed, project knowledge was adopted
  • a model for working with the ML team was built
  • a market analysis of possible solutions for versioning ML models was conducted
  • ML models were integrated into the main project
  • problems with huge amounts of data were solved
  • parallelization problems were solved
  • problems with the lack of static typing in Python were solved
  • a system module was developed that:
    • collects and aggregates data for the model input
    • selects the appropriate model
    • runs the model for each flight and saves the result to the database
    • covered by tests that verify its operation
    • packed in a Docker container
    • runs periodically according to the schedule
  • The work was not completed due to the customer changing priorities and excluding it from deliveries

To read further: Forecast sales system for an airline company, part II: service forecasting bookings by ML

Database optimization

  • optimized work with the database so that a large volume of data does not interfere with development
  • proposals were made that should ensure the speed of work with the database by 10 times (not implemented due to high workload and work on other tasks according to the set priorities)

To read further: Forecast sales system for an airline company, part I: working with one billion records

Data migration

Data migration was carried out regularly:

  • knowledge on this part of the system was adopted
  • requirements were collected and clarified
  • functionality was added/corrected to meet new requirements
  • requested data was selected from csv
  • data was passed through our pipeline
  • converted data was exported to an sql script, ready for import on the customer's side
  • data was imported into the system on the customer's side
  • shell scripts and daemon tasks were developed, automating the most resource-intensive parts of this task

To read further: Forecast sales system for an airline company, part IV: data migrations

Message Queue (MQ) module

The MQ module has been improved/rewritten to meet new requirements:

  • the system has been refactored
  • a new data model has been implemented
  • a layer for working with the database has been implemented
  • business logic for processing new messages has been implemented
  • tests have been implemented that verify the operation of the system
  • additional message filters and tests that check their operation have been implemented
  • the module has been packed into a Docker container

Math Forecast module

The Math Forecast module has been extended:

  • knowledge on this part of the system has been adopted
  • system refactoring has been carried out
  • knowledge on the mathematical logic of the system has been adopted
  • data models and data converters have been implemented
  • a layer for working with a data source has been implemented
  • business logic for initialization and booking event handlers has been implemented
  • part of the unit and integration tests that verify the operation of the system have been implemented
  • the module is packed into a Docker container

Solution

Developed solution is just a part of much bigger and more sophisticated system. What it does is receive data about bookings in real time and update forecasts which are later available over the REST or passed over Kafka to the most critical parts of the system.

The company has different types of sales proposals. Depending on current ratio between demand and offer it opens one or another sales proposal with final goal to fulfill the whole capacity of a jet and maximize a revenue. Forecasts which are made by the system give recommendation to another part of the system which sales proposal to open right now.

Forecasts heavily rely on the historic data about passed bookings, but for not more than one year. Experiments shown more longer trend becomes less effective, because market situaton year-to-year changes significantly which makes older data outdated.

System recognises trends and seasons -- growth or decline in orders for a period of time. System distinguish different periods of bookings and adjust algorithms of foreacasts according to them. System covers corners cases and exceptional situations and adjust sales to meet finals goals.

As a whole, system elegantly adjust sales prices to a current demand.

Main components

The math module

The math module is the core of the system which has been developing for passed two years. Initially made on R it had been ported on python.

The prototype on R which leverages this math module shown 10x better results compare to the Sabre.

Unfortunately, I could not say more - this part of the system is under NDA of my customer.

Kafka as a single source of data

Variety of customer's sources of bookings comes to our system over Kafka. This stream of inventory data is near 100 Mb per second (!) processed by our system.

Message Queue (MQ)

Our MQ is service on Python which reads inventory data from Kafka and scaled up over docker containers to meet with growing flow of the data.

Further MQ disassembles inventory messages on different business domain objects and put into associated tables in database. This process doesn't take longer rather than 0.5 second.

Based on information in a new inventory message and current state of the model, on the MQ level a special object is generated to be processed futher. We call it action.

Math Forecast module

Another container with forecast logic long polling database for upcoming new actions generated by MQ. Based on type of an action, forecast module initate one of four possible steps.

Unfortunately, I could not say more as this part of the system is under NDA of my client. Results of a forecast are put into database and the most critical part of this results send back to the client immediately.

ML module

In parallel to the math forecast module ML forecast module should be run as well. It leverages 10 000 precalculated ML models which recevies historics bookins as input and gives forecast as output. Later this data should be compared with forecast made by classic math and better estimate should be returned back to the client. Team assumed in some periods of sales it might give better result rather than a classic math.

Due increased scope and limits of the budget the ML module has been crossed out from the current delivery three months ago.

Maintenance and history databases

Currently systems stores 600 000 000 records. Search over such amount of data is challenging and with growth of data situation will be even more difficult. Because at least half of this data is historic, it makes sense to move it into a separate database to improve query performance.

Another potential boost in performance could be achieved over partitioning database by month and may be by markets. Queries are extensively uses date ranges, so skipping out dataset out of required date range could gives 10x boost to the performance. This task is partly implemented and stays in the backlog for a future scope.

REST API

Current customer goal is visualise booking dynamic and forecasts in sales on graphics to give maximum infromation to analytics and sellers. For this aim REST API has been prepared and deployed.

However, in future I see potential to automatically open a sales offer without introducing a man into this process.



Related publications: