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

Executive Summary

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.

Democratized Reporting Makes Insights Available to Everyone

As a member of the Amazon Web Services (AWS) Public Sector Partner Program, alongside their professional services, Crimson Macaw offers, 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 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.”

"The AWS environment allows the Crimson Macaw solution to scale on-demand. We can turn up compute resources when it’s time to crunch numbers—and then turn off those resources when no longer needed."

- Martin Bell, Product Owner of Contactless Ticketing, Transport for Greater Manchester

A Cloud Data Warehouse in 4 Weeks

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 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.”

AWS Environment Automates Analytic Processes

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 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.

Ability to Scale Handles Spikes in Activity

“We appreciate how the AWS environment allows the Crimson Macaw 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.”

Need for Analytics About to Take a Quantum Leap

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 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 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.”

Transport for Greater Manchester

About Transport for Greater Manchester

Transport for Greater Manchester (TfGM), a UK government body, is responsible for delivering Greater Manchester’s transport strategy and commitments. More than 5.6 million journeys are made across the TfGM transport network each day, and it’s the organization’s mission to keep the city and region moving by providing safe and simple travel across a better-connected Greater Manchester.

About Crimson Macaw

Located in the UK, Crimson Macaw specializes in cloud, data warehousing, business intelligence, and data science consulting. Partnering with Amazon Web Services, the company helps customers leverage the value of data through quick-to-implement, scalable cloud technologies alongside leading analysis and visualization tools. Whether customers shelve data initiatives, run into obstacles in their current operations, or need a quick tactical project, Crimson Macaw can help at any stage of a data journey.

Published June 2022