Transport for Greater Manchester Turns to Crimson Macaw and AWS for Valuable Insights into Public Transport Passenger Activity

Transport for Greater Manchester (TfGM) turned to Crimson Macaw to migrate analytics to the AWS Cloud to gain insights into light rail passenger activities. TfGM manages the largest tram network in the UK. The Crimson Macaw solution runs on an AWS infrastructure powered by AWS Lambda, Amazon Simple Queue Service, and Amazon Simple Storage Service.

As a member of the Amazon Web Services (AWS) Public Sector Partner Program, alongside their professional services, Crimson Macaw offers Datavelocity.io, an out of the box data and analytics enterprise platform which runs on AWS. The applied analytics data warehouse ingests data from multiple sources, applies artificial intelligence and machine learning, and turns the data into valuable insights. This creates what Crimson Macaw Chief Executive Officer Paul Grosart calls, “democratized reporting so insights are easily available to everyone—especially non-technical end-users.”

One Crimson Macaw client benefiting from Datavelocity.io is Transport for Greater Manchester (TfGM), which manages the city’s light rail system. As TfGM was in the process of deploying a contactless ticketing system for the UK’s largest light rail network, the next challenge was to gain visibility into passenger activity. The TfGM management team needed to tap into data to understand how many travelers use each tram on the system and the paths they take to traverse the city.

“With this data, it’s easier to make decisions on how to improve the transportation services we provide to our customers,” says Martin Bell, Product Owner of Contactless Ticketing at TfGM. “But at that time, it was difficult to ingest data from our multiple service providers and then transform it into usable insights.”

Besides collecting data from multiple sources, TfGM also wanted the analytics solution to automate tasks—including the verification of data quality before running any extraction processes. It was also critical for the solution to scale cost effectively to handle several hundred passengers at once (without changes to the architecture) to support the spike in activity.

At first, TfGM tried to run analytics within its on-premises data center. However, with the data volumes and depth of insight required, TfGM recognized that greater computational power and analytics tooling would also be necessary.

Trusted partner Crimson Macaw came through by answering the call to design and deploy a cloud-hosted solution powered by AWS. Within four weeks, the Crimson Macaw team implemented development, test, and production environments to create an applied analytics solution running in the cloud. The Datavelocity.io solution now allows TfGM to receive data feeds from multiple sources, normalize the data, and create reports.

Crimson Macaw built the data and analytics solution for the TfGM contactless ticketing system on the AWS Cloud, using the Terraform infrastructure-as-code tool to create all-native AWS services and database objects. “Fully-scripted development, test, and production environments allow for repeatable patterns,” says Grosart. “As we expand the use of the platform into other areas of the business going forward, full scripting will permit us to create end-to-end environments in hours, meaning we can speed up the delivery of new services to TfGM.”

To enable maximum automation, Crimson Macaw uses AWS Lambda functions to verify flat-file imports from data-service suppliers include correct row counts, column counts, and data types. If a data import fails, the system automatically generates a help desk ticket with the appropriate supplier and prevents the extraction job from commencing on the TfGM side.

Once verified, the extraction environment is initiated using Amazon Simple Queue Service (Amazon SQS), which decouples and scales microservices to orchestrate Matillion instances. Matillion then transforms and loads data into a Snowflake data warehouse hosted by the AWS environment. The system also integrates with another hosted application, Tableau, for reporting and visualization purposes.

With the solution designed and deployed by Crimson Macaw, TfGM gains new insights into travel patterns and passenger behaviors to adapt the tram network and improve contactless services. These new insights come from the ability to store data in Amazon Simple Storage Service (Amazon S3) and analyze any volume of data in the new data warehouse.

The Datavelocity.io solution for TfGM now features more than 200 business logic data transformations and gives internal users access to more than 500 reports. The system also runs hundreds of validations in seconds, piping data into landing tables for processing within 2 minutes.

“We appreciate how the AWS environment allows the Crimson Macaw Datavelocity.io solution to scale on-demand,” Bell says. “The data warehouse stores data cost effectively in Amazon S3, and we can turn up compute resources on Snowflake when it’s time to crunch numbers—and then turn off those compute resources when no longer needed.” Key insights TfGM has gained include the ability to ensure enough room on trams to practice social distancing and improve tram usage distribution by offering price incentives to travel off-peak. TfGM can also see when it’s necessary to add trams or offer parking incentives during popular events, such as football matches and concerts. 

“We not only know when to add trams or take them offline, but also have visibility into how well our decisions work,” says Bell. “It’s as important to ensure we don’t run a surplus of trams as it is to employ too few.”

The Crimson Macaw solution’s ability to scale will play an increased role in the future when TfGM assumes responsibility for the current privately controlled city bus lines. Bell also wants to add an analytical component to Datavelocity.io to support the region’s initiatives for improved air quality for citizens.

“The bus system carries more passengers than our tram system, and for the air quality effort, we’ll track tens of millions of vehicle journeys,” says Bell. “That’s when the combination of Datavelocity.io flexibility and cost-effective AWS compute power will pay off for the entire city of Manchester—as we gain the ability to know how clean our air is.”