Using Big Data To Deliver Insights Into Proppant Logistics

Proppant producers tracking customer shipments require much more than simply accessing a railroad company website and entering a tracking number. Oftentimes proppants of varying grades are stored in multiple locations both off and on track, in silos or bags, in sidelined boxcars or at various plants. Having all these data on hand, updated and at the ready is no small challenge.

Consider the inventory process. As proppant is produced it is entered in an enterprise resource platform (ERP) at the plant and shared with logistics and management. As the product moves to different storage locations or allocated to client orders, it does not take very long before these data become convoluted. Rounded numbers are moved between various columns within spreadsheets or pivot tables, the daily differentials of which are unknown to most, and within a couple of months, the data in the ERP no longer match what is in the field.

Tracking orders presents its own challenges as the orders travel to the client. Railroad companies only track what is on their lines. Rail cars are often transferred from one line to the next to get to their destination. To track one order may require logging into as many as four different company websites, most of which are basic databases and require manual interaction. These processes are often not the most efficient way to determine where the order is and when it might arrive. There are third-party consolidators that specialize in rail data collection and delivery, but as with the rail company websites, they are basic operational databases and don’t come cheap.

Case study
Given these challenges, Drillbox was tasked by a major ceramic proppant producer in the second quarter of 2018 with delivering a customized cloud-based instance of its oil and gas geographic information system (GIS) through which the data for the producer’s 400-plus rail cars, including location and contents, could be aggregated and delivered in a user-friendly format.

The first challenge to overcome was to create an automated ETL (extract, transform and load) from five web-based operational databases and the client’s on-premises data warehouse. It became apparent early on that Drillbox would need to truncate the time it took to gather these data. Each of the individual rail lines housed their data behind login credentials, and only one allowed a mass download via file transfer protocol. In addition, most rail companies’ sites require the user to enter a rail car ID number either one at a time or paste a list. Scraping data from web pages has progressed rapidly in the past few years, and there are several open source automation tools available.

Within a couple of weeks, the Drillbox team successfully navigated the various pages and portals and configured a tool that would enter each and extract what was needed. The result was a substantial amount of unstructured data, which grows by the day.

Once the data were automatically extracted, the Drillbox team spent the next several weeks constructing a transformation hierarchy based on a dynamic and expanding set of rules. The most basic question at onset was whether the rail car in question was empty or full. If full, did it have a purchase order associated with it or was it in storage? If empty, was it being returned or was it sitting on a track waiting to be filled? If it had a purchase order, what was in it and where was it going? From this information, Drillbox began to gather useful insights into how rail cars moved as well as how cars sat idle. The company saw that as rail cars reached their destination, they would vanish from the tracking platforms until they reappeared as empty rail cars on their return trip.

From this a “Current Status Days” column was created in the platform, which allows Drillbox to see how long a full rail car sat at a plant, how long cars took to return to a plant from a particular client location, and—perhaps most valuable of all—how many days it took on average for the product to move to a customer site and how that differed from the rail company estimates. “Current Status Days” provided insights that would have been undiscovered in an operational data query.

Tracking proppant
As the rail car data took shape, new questions emerged that led Drillbox to integrate inventory totals into the platform. For both sales and management personnel, it was of great importance answering the question of what was on hand and where. The company started by developing a few charts and graphs from which users could select location or proppant type to see their ERP inventory or storage for that day. What was discovered was that data from the field differed from the ERP data over time. The Daily Delta view was created for the program to provide some insight into this differential. Daily Delta calculates value plus or minus from the previous day for both field storage data and ERP storage data. This allows the users to judge which number most likely best represented the actual field totals and provided greater accuracy when quoting delivery to clients.

User feedback further refined the search architecture and historical tables, which were added to deliver both on-track and off-track inventory by location over time. At this point, the client had even more insight into how the product moved in a particular month or quarter and assisted in optimizing production and storage. Date selectors were added so that users could customize their data range and get even more granular in their inventory movements. Export options were added so that results could be transferred to a comma-separated value file and added to reports or shared between departments.

Study results
By the end of the third quarter last year, the client was able to identify 40-plus rail cars that were idle and realized a cost saving by having those rail cars roll off the company’s lease.

As with any project of this scope, there were several challenges along the way, the greatest of which was how to manage the large amount of data that were accumulating almost exponentially within the platform every week. Within four months Drillbox started to notice a reduced response time when switching between its chart and table views and decided to move some of the on-the-fly calculations within the app to the database. Additionally, the client realized that most of its users did not find value in some of the datapoints that were being collected, and categories were consolidated to simplify the user experience. Other challenges included expiring login credentials or other disruptions in the automated ETL for which the Drillbox team set up a series of automatic alerts that would notify the team of any disruptions.

Conclusion
It took less than 90 days from kickoff in the second quarter of 2018 to delivery of a working system. Incorporation of proppant inventory and final delivery was in the fourth quarter of 2018. Future plans include the incorporation of transatlantic ship data for international orders and the creation of a native mobile application for both IOS and Android.