Multiple technical limitations didn’t allow customers to self-service customized data
Imagine you are driving down the highway and you pass under a massive bridge and see a new cityscape popping up. Most of us don’t give construction complexity a second thought. But if you work in the concrete industry, these bridges and buildings are structures that require a fine-tuned balance of material science, operational efficiency and environmental responsibility.
GCP Applied Technologies sells chemicals and materials used in construction, including concrete. A major challenge for the industry has been this lack of visibility into what happens between the time the truck leaves the plant and arrives at the site…a problem that GCP refers to as “The Mile in the Middle.” While the concrete recipe loaded at the plant might be perfect, the transport to the job site could introduce problems including excessive revolutions, temperature or age and more commonly, the addition of out of spec water.
Enter GCP Applied Technologies’ VERIFI® in-transit concrete management system, a breakthrough technology that has combined material science, IoT innovations and data science to monitor, measure and manage concrete in the truck as it travels from the plant to a job site. Usually, during the delivery cycle, the ready-mix producer has no visibility into the properties of the concrete, either in transit or at the job site. VERIFI® empowers ready-mix producers with full visibility into concrete properties from the time the truck leaves the plant until it is delivered and discharged.
Customers knew that GCP provided the VERIFI® data and would request daily reports and information to get real-time updates on their orders. However, multiple technical limitations caused GCP to create hundreds of customized reports to various customers.
With VERIFI®, all the ticket information is sent to a third-party database where the loading information—truck number, driver, job location, and concrete recipe along with the thousands of in-transit data points collected from the onboard sensors during each delivery cycle—are stored and then presented on a portal. The sensors installed on the trucks record the age, the number of drum revolutions—both high and low-speed, the concrete temperature, water additions, chemical additions, and product consistency. More importantly, the VERIFI® system will automatically make water and/or chemical additions in transit to ensure that the load is delivered on spec.
With this information available, the ready-mix producers, have been increasingly interested in leveraging this data for their KPIs – measuring performance of drivers, batchmen and quality control managers, monitoring job site wait times, and tracking various quality metrics such as returned and rejected concrete. GCP was faced with a rapidly growing market and limited ability to process the data into intelligible reports that deliver value to their customers.
Faced with multiple technical limitations, GCP was forced to create and email daily reports to each customer with information from the day before. The more data that their customers reviewed, the more data they requested to support decisions for improved productivity, optimized material usage, improved concrete quality and consistency, and commercial leverage. As the cycle continued, GCP was generating and emailing upwards of 750 reports per week. The company did not have a flexible way to gain control over the data and/or a secure way to extend access to its customers. Here are some of the issues GCP encountered:
- The ETL process to the existing BI database and the implemented data structure were not visible to the internal technical team. As a result, to troubleshoot issues or implement any business requested enhancements, GCP had to ask the vendor to intervene.
- The flexibility to rerun the ETL is arduous and as such, rerunning to accommodate a fix was more time consuming than the customer’s tolerance.
- The third-party reporting interface was not user-friendly and filtering was not robust. As a result, users created a multitude of similar reports to satisfy unique variations needed by different customers.
- GCP felt they should provide and their customers requested self-service so that they could pull the data they want when they want it. The existing BI platform did not securely support customer data segmentation.
- As a result of the security noted above, over 750 reports per week were generated and emailed to customers. This time consuming and the expensive process became untenable as the customer base grew.
GCP’s goal was to provide the VERIFI® business unit with a more flexible and cost-effective solution that extends intelligent data to their customers. They partnered with Archetype to assist with a technology migration from their current third-party software environment to another solution. Archetype proposed and implemented a transparent cloud-based solution to improve visibility for the internal technical team and reduce cost over the use of their existing solution. Archetype suggested Matillion for data ingestion ETL mappings and Snowflake as the primary database to make the solution more reliable and scalable. Some of the features of the solution included:
- Decreasing the load time and enabling the ability to troubleshoot load failures and fixes. Snowflake provides complete access for the technical team to perform bug fixes and apply enhancements to improve reporting needs.
- Implemented a scalable and more flexible solution to address each of the business problems and pain points by providing coordinated parallel training to the client technical team and ensuring a successful knowledge transition by involving the client in every phase of the implementation process.
Due to inconsistent archiving policies, GCP wasn’t able to provide a single source of record for the data accumulated over their seven-year history. As a result, Archetype had to identify when and what changed over the years, which took significant extra time. The load summary data files underwent multiple different format changes throughout the years, so Archetype developed eight additional loads tailored to work for specific date ranges. Overall, Archetype developed 42 distinct high-level ELT Orchestration Jobs (aka ETL interfaces in Matillion). Despite this unexpected requirements hurdle, Archetype remained on schedule and within budget for this initiative.
By utilizing Matillion for ETL and Snowflake as the database, GCP has significantly reduced data overhead. GCP’s data is an environment that is scalable and less expensive. They can control and create all of the visualizations they want without relying on a third-party vendor for access. Importantly, GCP has also achieved their goal of allowing customers to pull data from their system. GCP no longer needs to have staff create reports; the customers can readily go to their dashboard and get the holistic view of the data they need for their business. Finally, GCP can now support requests for APIs from their Snowflake database to the customers’ systems.
Susan Dalton, GCP’s VP of Smart Technology & IoT recognized the Archetype team as both cost-effective and efficient. “The Archetype consultants worked on site with us and integrated extremely well with our development staff. Their competence and commitment to quality were apparent from start to finish.”