By Land, Sea, or Air – Using Alteryx Geo-Spatial Tools

[cbalas]

Author:

Savio Henriques & Nat Balston

Role:

Business Intelligence Analytics Team

Company:

Inmarsat

 

Overview of Use Case

Inmarsat is a leading satellite telecommunication company based in London. This use case shows how their Business Intelligence (BI) team created value out of their internal geospatial data sources. Alteryx helped them clean and validate the data as well as match it with external sources to find aircrafts and vessels globally. This helped bolster their offerings and enhanced their ability to fight commercial fraud, but most importantly it helped the global search and rescue teams who need fixed locations to save lives. Overall, Alteryx’s functionality assisted the BI team to generate cost savings annually in excess of over $100K that Inmarsat spends buying external data from multiple sources.

Describe the Business Challenge Problem

Inmarsat provides communications services to a range of governments, aid agencies, media outlets and businesses (especially in the shipping, airline and mining industries) by leveraging portable and mobile terminals that communicate with ground stations through thirteen geostationary telecommunications satellites.

Inmarsat has lots of data that they couldn’t utilize due gaps in their data sets and information being incomplete. For example, employees would switch sim cards or terminals and thus updated information is not sent back. This data is critical to have and, in some cases, could be considered a life or death situation since Inmarsat provides data to search and rescue teams. Aside from cleaning the data, the BI team realized that they had a tremendous amount of locational data about vessels, but they needed different kinds of additional data features to have a more precise geospatial understanding for reporting.

Describe your Working Solution

Inmarsat’s BI team found a way to compare their incomplete data with more complete and further detailed data. Automatic Identification System (AIS) is a toolkit that broadcasts a vessel's location, the vessel type, speed, and heading information. They needed to figure out how to gather that and match it against their positional information. They used Alteryx Spatial tools to collocate from their data sources and AIS to match International Mobile Subscriber Identity (IMSIs), Maritime Mobile Service Identity (MMSI) and International Maritime Organization (IMO) numbers.

The first step of the process was to find the closest data points by latitude and longitude within a defined distance and window of time. Second, they selected matched results with a high frequency of the same network SIM (IMSI) and Vessel ID (MMSI/IMO) appearing together. They joined match results with pre-validated information for analysis. With the Find Nearest spatial tool in Designer they found the closest points between two data sets, it is now Savio’s favorite tool. They also used the Alteryx/Google Big Query connector to directly access big query databases. They also utilized the formula tool to convert data like timestamps or location data not configured as lat or long. Finally, they filtered out data to make sure the vessel data matched their data in terms of being in the same location within a certain time limit and then exported the output file into Tableau.

They ran a validation set (the pre-validated set of vessels) to compare the match results. They then ran the final report for 1 day’ worth of data and 1 month’ worth and set the criteria to find the nearest 20 matches with a maximum distance between points in each data set of 5 kilometers and a time delay-gap of 10 minutes. They had a data validation rule to run the check 20 times throughout the day and after a month of doing this.

Rather than having to run data validation and location comparison reports for the entire month, the BI team was able to build a macro within Alteryx to run the analysis every day. The macro not only gave them a real-time read on the accuracy of their data but also cuts the run time down from five days to one.

They found 50% of their existing data matched the AIS vessel data. The matching process helped them identify an additional 25% of vessels with limited information and it also showed 3% of their data was incorrect or out of date. Most importantly, the match process showed that 22% of their data was unmatched, likely due to external maritime circumstances; this gave them an opportunity to dig further into how to account for and solve for those unmatched vessels.

The BI team duplicated this effort of using external spatial data to enrich their internal data sources in other areas of Inmarsat. One area specifically that they also provide communications services is for business and private jet users. They used a community-driven plane spotting web site to capture the tail number on planes and to cross-reference the location, photo date, aircraft type, and the number of results with their own internal data set. The team did this by creating the workflow as seen below.

They first took the website URL and put it in a Text Input tool into Designer, then ran it with the Download tool which pulled back the HTML and placed it in a single cell including the tail number, aircraft type, photo date, etc. Even though they had no background in either RegEx or HTML, they looked for patterns that might be visible and then started using the Reg Ex tool in its tokenized format. The tool looks for a feature in the data and splits it into either columns or into rows. Then, the RegEx tool parsed for any expression that can be matched. It created a new column and added that data that matches what you're looking for into a new column. In this case, the BI team wanted to find photo dates.

At this point, the team had time and location information but they needed to turn that into a real geographic location, so they utilized a data set with airport locations in it. They used the IKO code from the airport data, which is a four-digit code unique to an airport, to identify each location and join that with the latitude and longitudes. They matched on IKO code to give the position of the airports. They matched the locations from their network to these airports to see if the IDs of a specific plane were in the same place at the same time as the photos that were taken. In order to do that match, they needed a geofence in the data and the dataset had a very small one already in it.

The Buffer tool helped them expand that geofence to have a larger target. After they brought in the internal data set. They found all the locations of the ID for a specific plane and matched that to photographs taken using the spatial match tool to find the overlap between airport and photo data. To account for time with location they filtered to test that photos and ID’s matched the date they were taken and if they were in fact at their exact airport or location. Once they verified the information was valid, they scaled the workflow out by selecting more dynamic inputs with the help of macros and a control parameter, allowing them to source from a new data stream. They added a list of tables from their internal data source and if the team was looking for a different plane all they’d do is change the end of the URL string to include a new tail ID.

Describe the benefits you have achieved

A major benefit Alteryx provided was the ease of putting a solution together, and the speed at which the team can do it. When the team started, they didn't have any GIS or geospatial background, all they needed was two datasets to begin manipulating the data. They now have users within the business using Alteryx, so they can hand over what’s been built to a commercial user and they can work on it further. They also found it extremely helpful that Alteryx doesn’t require anywhere near the same level of IT support that other data tools require.

With Alteryx, the BI team coordinated point mapping report also gave Inmarsat critical market intelligence data. With the added information about vessels they can now look at gross tonnage, different market segments and improve their customer relations. So, for example, in the future if they receive a customer call, they don't have to ask, "Where are you calling from or who are you?" they now have the information about the vessel, they know what kind and which vessel specifically is calling. With the market intelligence data, they can even detect commercial fraud by simply looking at the number of terminals a vessel has but most importantly this information can now be provided and used for search and rescue operations to help save lives.

Overall, Alteryx helped the BI team steer investment decisions with additional customer insight gained from the Market analysis they developed, as well as deciding where to target millions of dollars in engineering development and marketing resources to drive revenue growth and product enhancement. Alteryx provided cost savings annually in excess of $100K that Inmarsat spends buying external data from multiple sources. This revenue can be repurposed to address other data needs and thus compounding further potential savings.

Related Resources

The entire PowerPoint presentation can be found here.