As a leading French agricultural cooperative, Groupe Dauphinoise places collaboration at the heart of its philosophy. Working with farmers in the Rhone-Alpes region, Groupe Dauphinoise takes on a diverse range of activities from agricultural production to research and development to running retail outlets.

As its operations expanded and strained its existing infrastructure, Groupe Dauphinoise saw the opportunity to upgrade its technology solutions and adopted G Suite, Chrome devices and ultimately Google Cloud Platform (GCP).

“We transitioned to G Suite to improve our staff’s collaboration. We quickly realized that as the company grew, we needed a new infrastructure. Based on our satisfaction with G Suite, we chose GCP. With Google, ‘any device, anytime, anywhere’ is not just a dream,” says Sylvain Claudel, Head of IT at Groupe Dauphinoise.

“We transitioned to G Suite to improve our staff’s collaboration. We quickly realized that as the company grew, we needed a new infrastructure. Based on our satisfaction with G Suite, we chose GCP. With Google, ‘any device, anytime, anywhere’ is not just a dream.”

—Sylvain Claudel, Head of IT, Groupe Dauphinoise

Flexible workforce, stable infrastructure

Many of Groupe Dauphinoise’s 1,500 employees spend their days out of the office. Five years ago, with the help of Google partner GoWizYou, the company transitioned to G Suite to improve the flexibility and collaboration of its staff.

As Groupe Dauphinoise began to expand and collect more data, the company reached the limits of its on-premise infrastructure. Adding new storage was not a simple matter. Acquiring, configuring and synchronising new servers costs Groupe Dauphinoise time as well as money. In addition, with all its servers stored in a single room, security was a concern. Groupe Dauphinoise needed a new infrastructure.

Google Cloud Platform was the only solution in mind after Groupe Dauphinoise’s experience with G Suite and Chromebooks. Disruption was kept to a minimum thanks to Google’s licensing agreements with Microsoft products, allowing the company to migrate without affecting its operations.

After migrating its infrastructure to Compute Engine, Groupe Dauphinoise can let Google look after the security and maintenance. Cloud IAM makes it easy for Groupe Dauphinoise to hand out permissions to sensitive resources across a number of sites with maximum security and minimum fuss. The company placed its archives in Cloud Storage, while Cloud SQL allows it to continue to make use of its MySQL databases without disrupting the day to day business. Meanwhile, BigQuery provides Groupe Dauphinoise with the raw power to analyse large datasets quickly.

“Our company is growing and we have more and more data to collect and analyze like sales data, weather patterns or production numbers. With GCP, we have more than a single on-premise data store, so our disaster recovery plan is much more flexible. Compute Engine allows us to add more storage quickly and easily, without having to spend days synchronizing data and installing new servers. We have improved security and maintained the stability of our infrastructure while keeping costs down,” says Sylvain.

Safeguarding the present, looking to the future

With Google Cloud Platform, Groupe Dauphinoise has expanded and secured its infrastructure without sinking costs into on-premise servers or DevOps staff. Its investment in Chrome devices and adoption of G Suite mean that its mobile workforce can fully reap the benefits of a cloud-based infrastructure while dramatically cutting the cost of hardware. Meanwhile, working with a 200 million line table of sales data in Google BigQuery, the cooperative found that queries ran ten times faster than with its previous database provider. Groupe Dauphinoise is experimenting with Google BigQuery to expand its BI capabilities. Products like Google BigQuery help Groupe Dauphinoise grow its business, safe in the knowledge that its infrastructure is stable and secure.

“The amount of data we collect is growing very quickly. We need to break our rules and evolve from the mindset that we had with on-premise infrastructure and our old databases. We can now look at collecting more customer fidelity data, or big data for our farmers. With our data and infrastructure in Google’s care, we can concentrate on growing our customer base instead of our IT department!” says Sylvain.

Lung cancer is the leading cause of cancer death in the United States and like any cancer, early detection is crucial to survival. Screening at-risk populations is an important part of reducing mortality, and if concerning nodules are found on imaging, further testing may be required. Today, we’ll share how Northwell Health uses Google Cloud products such as Cloud Healthcare APIs and BigQuery to increase caregiver productivity and deliver better care for patients with findings that indicate potential development of lung cancer.

Northwell is New York’s largest healthcare provider

Northwell Health is New York’s largest healthcare provider with 23 hospitals and nearly 800 outpatient facilities. Northwell’s nearly 4,000 doctors care for millions of patients each year, and at this scale, there is an immense amount of healthcare data to manage. To better manage and leverage this data, Northwell Health partnered with Google Cloud starting in 2018.

Enabling caregivers to spend more time with patients

Nic Lorenzen, the lead developer of Northwell Emerging Technology and Innovation team, has a mission to put together data for caregivers in a way that makes sense. It is no secret that inefficient electronic health records systems have a negative impact on a physician’s ability to deliver quality care. Traditional EHRs have information distributed across many tabs, which forces caregivers to spend considerable time at the computer trying to find information. Moreover, speed of care matters. If care is delayed, patients may have to spend more time in the hospital and may suffer worse health outcomes.

To solve this problem, Nic’s team focused on giving caregivers the most relevant pieces of data at the right time by developing an intelligent clinician rounding app. The data needed to derive these insights can depend on the caregiver’s role–a nurse cares about different things than a cardiologist. This system aggregates multiple data sources, and provides patient-specific insights to caregivers.

This system would not have been possible before with traditional EHRs and data warehouses that have proprietary data models and rarely sync data in real time. Now with data easily accessible through Google Cloud’s Healthcare solutions, Nic’s team can deliver the right clinical information to the right people instantly. These days, Nic says, “instead of spending 75% of our time dealing with architecting the underlying platforms, we spend 75% of our time focused on  higher value use cases for clinicians and patients. Google Cloud’s Healthcare solutions have greatly improved our developer productivity and time to value.”

Caregivers have found this new system to be a game changer.Before the implementation of this system, caregivers would spend, on average, seven to nine minutes finding the data needed to make medical decisions for one patient. Now, that aggregated information is delivered to a caregiver’s mobile device in less than a second.

Ensuring patients get the right care with the power of AI

There are a number of reasons why patients might not get the care that they need. For example, patients today can go to multiple hospitals and clinics settings, and coordinating care across multiple facilities is complex. Regional hospitals and clinics have their own siloed view of their data, so pertinent information gathered by one clinic might not be seen by another. These gaps in clinical data lead to gaps in patient care.

When a patient gets radiologic imaging, they may have findings unrelated to the reason they initially got the imaging. For example, a chest CT for a car accident might reveal an incidental lung nodule that could be cancerous. Unfortunately, research shows that a large portion of patients do not get follow up for these incidental findings because it isn’t the primary reason why the patient is seeing a doctor. Moreover, social determinants of health are a factor that affects which patients receive follow-up care. Identifying these patients and providing the necessary follow up care prevents adverse events related to delayed detection of cancer.

With Cloud Healthcare solutions, Northwell built an AI model to identify these patients so that oncologists can appropriately follow up with patients who have findings suspicious for lung cancer. The AI model detects incidental pulmonary nodules in radiology reports so that doctors can then contact the patients that need follow-up care. Nic says his team was able to build this system in a week: “Google Cloud did a lot of heavy-lifting for us and allowed us to get to the AI applications much faster. It allowed us to build a platform that just works.” 

Healthcare systems can now rapidly generate healthcare insights with one end-to-end solution, Google Cloud Healthcare Data Engine. It builds on and extends the core capabilities of the Google Cloud Healthcare API to make healthcare data more immediately useful by enabling an interoperable, longitudinal record of patient data. Northwell Health uses Google Cloud as the core of their platform, enabling their developers to create solutions to the most pressing healthcare problems.

Special thanks to Kalyan Pamarthy, Product Management Lead on Cloud Healthcare and Natural Language APIs for contributing to this blog post.

Statsig is a modern feature management and experimentation platform used by hundreds of organizations. Statsig’s end-to-end product analytics platform simplifies and accelerates experimentation with integrated feature gates (flags), custom metrics, real-time analytics tools, and more, enabling data-driven decision-making and confident feature releases. Companies send their real-time event-stream data to Statsig’s data platform, which, on average, adds up to over 30B events a day and has been growing at 30-40% month over month. 

With these fast-growing event volumes, our Spark-based data processing regularly ran into performance issues, pipeline bottlenecks, and storage limits. In turn, this rapid data growth negatively impacted the team’s ability to deliver product insights on time. The sustained Spark tuning efforts that we were dealing with made it challenging to keep up with our feature backlog. Instead of spending time building new features for our customers, our engineering team was controlling significant increases in Spark’s runtime and cloud costs. 

Adopting BigQuery pulled us out of Spark’s recurring spiral of re-architecture and into a Data Cloud, enabling us to focus on our customers and develop new features to help them run scalable experimentation programs.

The growing data dilemma

At Statsig, our processing volumes were growing rapidly. The assumptions and optimizations we madea month ago would become irrelevant the next month. While our team was knowledgeable in Spark performance tuning, the benefits of each change were short lived and became obsolete almost as quickly as it was implemented. As the months passed, our data teams were dedicating moretime to optimizing and tuning Spark clusters instead of building new data products and features. We knew we needed to change our data cloud strategy.

We started to source advice from companies and startups who had faced similar data scaling challenges – the resounding recommendation was “Go with BigQuery.” Initially, our team was reluctant. For us, a BigQuery migration would require an entirely new data warehouse and a cross-cloud migration to GCP.

However, during a company-wide hackathon, a pair of engineers decided to test BigQuery with our most resource-intensive job. Shockingly, this unoptimized job finished much faster and at a lower cost than our current finely-tuned setup. This outcome made it impossible for us to ignore a BigQuery migration any longer. We set out to learn more. 

Investigating BigQuery

When we started our BigQuery journey, the first and obvious thing we had to understand was how to actually run jobs. With Spark, we were accustomed to daunting configurations, mapping concepts like executors back to virtual machines, and creating orchestration pipelines for all the tracking and variations required. 

When we approached running jobs on BigQuery, we were surprised to learn that we only needed to configure the number of slots allocated to the project. Suddenly, with BigQuery, there were no longer hundreds of settings affecting query performance. Even before running our first migration test, BigQuery had already eliminated an expensive and tedious task list of optimizations our team would typically need to complete.

Digging into BigQuery, we learned about additional serverless optimizations that BigQuery offered out of the box that addressed many of the issues we had with Spark. For example, we often had a single task get stuck with Spark because virtual machines would be lost or the right VM shape needed to be attainable. With BigQuery’s autoscaling, SQL jobs are much more granularly defined and can move resources as needed between multiple jobs. As another example, we sometimes encountered a storage issue with Spark due to the shuffled data overwhelming a machine’s disk. On BigQuery, there is a separate in-memory shuffle service that eliminates the need for our team to worry about predicting and sizing shuffle disk sizes. 

At this point, it was clear that the migration away from the DevOps of Spark and into the serverless BigQuery architecture would be worth the effort. 

Spark to BigQuery migration 

When migrating our pipelines over, we ran into situations where we had to rewrite large blocks of code, making it easy to introduce new bugs. We needed a way to simultaneously stage this migration without committing to huge rewrites . Dataproc is a very useful tool for this purpose. Dataproc provides us with a simple yet flexible API to spin up Spark clusters and gives us access to the full swath of configurations and optimizations that we’re accustomed to from our previous Spark deployments.

Additionally, BigQuery offers a direct Spark integration through stored procedures with Apache Spark, which provides a fully managed and serverless Spark experience native to BigQuery and allows you to call Spark code directly from BigQuery SQL. It can be configured as part of the BigQuery autoscaler and called from any orchestration tool that can execute SQL, such as dbt. 

This ability to mix and match BigQuery SQL and multiple options for Spark gave us the flexibility to move to BigQuery immediately but roll out the entire migration on our timeline.

With BigQuery, we’re back to building features 

With BigQuery, we could to tap into performance improvements, direct cost savings, and experienced a reduction in our data pipeline error rates. However, BigQuery really changed our business by unlocking new real-time features that we didn’t have before. A couple of examples are:

1. Fresh, fast data results

On our Spark cluster, we needed to pre-compute tens of thousands of possible results each day if a customer wanted to look at a specific detail. While only a small percentage of results would get viewed each day, we couldn’t predict which results would be needed, so we had to pre-compute it all. With BigQuery, the queries run much faster, so we now compute specific results when customers need them. We benefit from avoiding expensive jobs. To our customers, this translates into fresher data.

2. Real-time decision features

Since our migration to BigQuery began, we have rolled out several new features powered by BigQuery’s ability to compute things in near real-time, enhancing our customers’ ability to make real-time decisions.

1) A metrics explorer that lets our customers query their metric data in real-time.

2) A deep dive experience that lets our customers instantly dig into a specific user’s details instead of waiting on a 15-minute Spark job to process. 

3) A warehouse-native solution that lets our customers use their own BigQuery project to run analysis. 

Migrating from Spark to BigQuery has simplified many of our workflows and saved us significant money. But equally importantly, it has made it easier to work with massive data, reduced the strain on our perpetually stretched-thin data team, and allowed us to build awesome products faster for our customers.

Getting started with BigQuery

There are a few ways to get started with BigQuery. New customers get $300 in free credits to spend on BigQuery. All customers get 10GB storage and up to 1TB queries free per month, not charged against their credits. You can get these credits by signing up for the BigQuery free trial. Not ready yet? You can use the BigQuery sandbox without a credit card to see how it works. 

The Built with BigQuery advantage for ISVs 

Google is helping tech companies like Statsig build innovative applications on Google’s data cloud with simplified access to technology, helpful and dedicated engineering support, and joint go-to-market programs through the Built with BigQuery initiative. 

More on Statsig

Companies want to understand the impact of their new features on the business, test different options, and identify optimal configurations that balance customer success with business impact. But running these experiments is hard. Simple mistakes can cause you to make wrong decisions, and lack of standardization can make results hard to compare, and reduce user trust. When implemented poorly, an experimentation program can become a bottleneck, causing feature releases to slow down or, in some cases, causing teams to skip experimentation altogether. Statsig provides an end-to-end product experimentation and analytics platform that makes it simple for companies to leverage best-in-class experimentation tooling.

If you’re looking to improve your company’s experimentation process, visit statsig.com and explore what our platform can offer. We have a generous free tier and a community of experts on Slack to help ensure your efforts are successful. Boost your business’s growth with Statsig and unlock the full potential of your data.

Last week in our BigQuery Reference Guide series, we spoke about the BigQuery resource hierarchy – specifically digging into project and dataset structures. This week, we’re going one level deeper and talking through some of the resources within datasets. In this post, we’ll talk through the different types of tables available inside of BigQuery, and how to leverage routines for data transformation. Like last time, we’ll link out to the documentation so you can learn more about using these resources in practice. 

What is a table?

A BigQuery table is a resource that lives inside a dataset. It contains individual records organized into rows, with each record composed of columns (also called fields) where a specified data type is enforced. BigQuery supports numerous different data types including GEOGRAPHY for geospatial data, STRUCT and ARRAY for more complex data, and new parameterized data types to add specific constraints like the number of characters in a string. 

Data access can also be controlled at the table, row and column levels; more details on data governance will be covered later in the series. Metadata, such as descriptions and labels, can be used for surfacing information to end users and as tags for monitoring. You can create and manage a table directly in the UI, through the API / Client SDKs or in a SQL query using a DDL statement.

  bq show \
--schema \
--format=prettyjson \
project1:dataset3.table

Managed and external tables

Managed tables are tables that are backed by native BigQuery storage, which has many benefits that improve query performance including support for partitions and clusters. We’ll cover more details on BigQuery storage later in this series. Another advantage of using a managed table is that BigQuery allows you to use time travel to access data from any point within the last seven days and query data that was updated, expired or deleted. And now you can even create a snapshot of your table, to preserve its contents at a given time. 

  # create a snapshot of transactions in the library_backup dataset as of one hour ago
CREATE SNAPSHOT TABLE
  library_backup.sales
  CLONE retail.transactions
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

While managed tables store data inside BigQuery storage, external tables are backed by storage external to BigQuery. BigQuery currently supports creating an external table from Cloud Storage, Cloud Bigtable and Google Drive. Besides an external table, you can create a connection to Cloud SQL, which is somewhat analogous to an external dataset. Here, you can leverage federated queries to send a query that executes in Cloud SQL but returns the results to be used within BigQuery.

Using external tables or federated queries may result in queries that aren’t as fast as if the data had been stored in BigQuery itself. However, they can be useful for some data transformation patterns –  for example, you may want to schedule a  DDL/DML query that hydrates a managed table using a federated query, which selects and transforms data from Cloud SQL. An external table might also be useful for multi-consumer workflows where BQ storage isn’t the source of truth. Like, if you have a dataproc cluster accessing data in a Cloud Storage bucket that you’re not quite ready to port into BigQuery (although I do recommend taking a look at our connector if you need some convincing). You can learn more about querying external data in this video. 

Logical and materialized views

In BigQuery, you can create a virtual table with a logical view or a materialized view. With logical views, BigQuery will execute the SQL statement to create the view at run time, it will not save the result anywhere. Additionally, you can grant users access to an authorized view to share query results without giving them access to the underlying tables.

  # create a view that aggregates daily sales from a retail transaction table
CREATE VIEW retail.daily_sales as (
SELECT date(t.transaction_timestamp) as date, sum(li.sale_price) as total_sales
  FROM retail.transaction_detail as t
  LEFT JOIN UNNEST(t.line_items) as li
  GROUP BY 1)

 On the other hand, materialized views are re-computed in the background when the base data changes. No user action is required – they are always fresh! Better yet, if a query, or part of a query, against the source table can be resolved by querying the materialized view, BigQuery will reroute for improved performance. However, materialized views use a restricted SQL syntax and a limited set of aggregation functions. You can find details on limitations here.  

  # create a materialized view that aggregates daily sales 
CREATE MATERIALIZED VIEW retail.daily_sales as (
SELECT date(t.transaction_timestamp) as date, sum(li.sale_price) as total_sales
  FROM retail.transaction_detail as t
  LEFT JOIN UNNEST(t.line_items) as li
  GROUP BY 1)

Temporary and cached results tables

Aside from the tables we’ve mentioned so far, you can also create a temporary managed table using the TEMP or TEMPORARY keyword. This table is saved in BigQuery storage and can be referenced for the duration of the script. Temporary tables can be a good alternative to WITH clauses because the defining query is only executed  once as opposed to being inlined every place the alias is referenced.

It’s also important to mention that BigQuery writes all query results to a table – one either explicitly identified by the user or to a cached results table. Temporary, cached results tables are maintained per-user, per-project. There are no storage costs for temporary tables.

User defined functions & procedures

In BigQuery, a routine is either a user defined function (UDF) or a procedure. Routines allow you to re-use logic and handle your data in a unique way. A UDF is a function that is created using either SQL or Javascript, it takes arguments as input and returns a single value as an output. UDFs are often used for cleaning or re-formatting data. For example, extracting parameters from a URL string,  restructuring nested data, or cleaning up strings:

  # UDF to clean up string values
 CREATE OR REPLACE FUNCTION
  my_dataset.cleanse_string_test (text STRING)
  RETURNS STRING
  AS (REGEXP_REPLACE(LOWER(TRIM(text)), '[^a-zA-Z0-9 ]+', ''));

We even have a community driven open-source repository of BigQuery UDFs! Just like logical views, you can create an authorized UDF that protects aspects of the underlying data. For more details on UDFs checkout our video here. You might also want to take a look at table functions – a preview feature where you can create a SQL UDF that returns a table instead of a scalar value. 

Procedures, on the other hand, are blocks of SQL statements that can be called from other queries. Unlike UDFs, stored procedures can return multiple values or no values – which means you can run them to create or modify tables. In BigQuery, you can also leverage scripting capabilities within procedures to control execution flow with IF and WHILE statements. Plus, you can call your UDFs within your procedure! These aspects make procedures great for extract-load-transform (ELT) driven workflows.

  # Procedure to create daily sales rollup, starting from startDate until endDate
 CREATE OR REPLACE PROCEDURE my_dataset.sum_sales(startDate STRING, endDate STRING)
  BEGIN
  CREATE OR REPLACE TABLE retail.sales_result
  AS (SELECT 
          date(t.transaction_timestamp) as date, 
          sum(li.sale_price) as total_sales
      FROM retail.transaction_detail as t
      LEFT JOIN UNNEST(t.line_items) as li
      WHERE transaction_timestamp >= TIMESTAMP(startDate) AND transaction_timestamp <= TIMESTAMP(endDate) 
      GROUP BY 1);
  END;
  CALL retail.sum_sales('2020-08-01', '2020-01-20');

To ensure consistent analytics across your organization, I recommend that you create a library dataset to house UDFs and procedures. You can easily grant everyone in your organization the BigQuery Data Viewer role to the library dataset so that all analysts use consistent and up-to-date logic in their queries. 

Stay tuned!

We hope this gave you an understanding of how to leverage some of the different resources inside of a BigQuery dataset, and to help you make decisions like using native versus external storage, logical versus materialized views, and user defined functions or procedures. 

Next up we’ll be talking about workload management in BigQuery by taking a look at jobs and the reservation model. Be sure to keep an eye out for more in this series by following me on LinkedIn and Twitter, and subscribing to our Youtube channel.

Japan is home to millions of vending machines installed on streets and in buildings, sports stadiums and other facilities. Vending machine owners and operators, including beverage manufacturers, stock these machines with different product combinations depending on location and demand. For example, they primarily display coffee and energy drinks in machines placed in offices and sports drinks and mineral water in machines at sports facilities. The combinations also vary by season: for example, owners and operators may display cold beverages in summer and hot beverages in winter. 

Traditionally, vending machine operators have relied on the intuition and experience of sales managers to determine the optimum product mix for each vending machine. However, in recent years, manufacturers such as Coca-Cola Bottlers Japan (CCBJ) have turned to data to analyze and make strategic decisions about when and where to locate products in machines.

CCBJ is the number one Coca-Cola bottler in Asia and vending machines comprise the bulk of its business. The organization operates about 700,000 machines across Tokyo, Osaka, Kyoto, and 35 prefectures. Minori Matsuda, Google Developer Expert and also Data Science Manager at CCBJ, says “The billions of data records collected from 700,000 physical devices are a great asset and a treasure trove we can take advantage of.”

Minori points out that when considering the mix of products in vending machines in sporting facilities, the managers naturally assume sports drinks would generally sell well. However, analysis of purchase data – including hot drinks and hot drinks plus sports drinks – found many parents purchased sweet drinks such as milk tea when they attended games or sessions involving their children.  “Analyzing data gives us new discoveries and, by using catchy storytelling techniques from exploratory data analysis, we are instilling a data culture within our company,” he says. “It’s worth creating by looking at facts rather than making assumptions!”

Minori believes that to analyze the vast amount of data collected from more than 700,000 vending machines, the business needs a powerful analytical platform. However, until recently, CCBJ had to extract data for analysis from its core systems, load this data into a warehouse it created and perform the required analyses.  The billions of records of data generated across the fleet – including transaction data – exposed some challenges for traditional analysis platforms. They could not efficiently process data at a considerable scale: it could take a day to return results and required extensive maintenance due to the size.

CCBJ considered building a machine learning (ML) platform as a layer on top of existing systems in August 2020 and opted for Google Cloud the following month.  “I feel that Google Cloud has an edge in all products and is very well thought out,“ says Minori, noting the scalability and cost of the platform allow the business to take a ‘trial and error’ approach to achieve the best outcomes from ML. Google Cloud also delivered the required visibility and flexibility to help the business deliver change every day against key performance indicators. 

MLOps platform streamlines ML pipeline development

CCBJ built its analysis platform using Vertex AI (formerly AI Platform) centered on a BigQuery analytics data warehouse, and partly using AutoML for tabular data. “We have created a prediction model of where to place vending machines, what products are lined up in the machines and at what price, how much they will sell, and implemented a mechanism that can be analyzed on a map,” says Minori, adding that building the platform with Google Cloud was not difficult. “We were able to realize it in a short period of time with a sense of speed, from platform examination to introduction, prediction model training, on-site proof of concept to rollout.”

The new data analytics platform of CCBJ consists of the following parts:

Data Sources

• The data collected from the vending machines are all stored on BigQuery.

Data Discovery and Feature Engineering

• Minori and other data scientists at CCBJ are using Vertex Notebooks, where they access the data on BigQuery by executing SQL queries directly from the Notebooks. This environment is used for the data discovery process and feature engineering. 

ML Training

• For ML training, CCBJ uses AutoML for Tabular data, Custom model training on Vertex AI, and BigQuery ML. AutoML gives model performance with AUC curves and also feature importance graphs.

ML Prediction and Serving

• For ML prediction, CCBJ uses Online Prediction for AutoML models and Online Prediction for custom models for real-time prediction when the salesperson finds the interesting point
• Batch Prediction is used for generating a large prediction map that covers the whole country
• The prediction results are distributed to sales managers’ tablets

CCBJ started constructing the platform in September 2020, and completed it within a month. The business has conducted proofs of concept at its base in Kyoto since February 2021, and since April, has rolled out the platform to sales managers in 35 prefectures in one metropolitan area. “Data analysis is built into the day-to-day routines of sales managers with 100% utilization,” says Minori. “They can utilize the prediction results on tablets that were able to achieve pretty high accuracy from the start.”

The hardest part was the education of sales managers in the field; having them understand the reasoning behind the ML prediction results for particular outcomes, so they could be convinced to make use of the results. “For example, regarding a new installation location predicted by the model, it seemed that there was no effective information for installation from the map information, but when I actually went there, there was a motorcycle shop and it was a place where young people who like motorcycles gathered,” says Minori. “Or there is a small meeting place where the elderly in the neighborhood are active. 

“In many cases, new discoveries that cannot be understood from map information alone can be derived from the data.”

Minori also points to a phenomenon whereby humans pursued and confirmed factors inferred by the model – meaning that once they experienced analysis and it worked effectively, they asked why the same type of analysis or prediction could not be undertaken next time. The resulting cycle of more inquiries generated, more information gathered and more data captured for analysis meant the accuracy of results was improved.

Minori describes Vertex AI as having a number of strengths in helping CCBJ build a ML data analysis platform. “One of the major merits of Vertex AI was that we were able to realize MLOps that streamlines the entire development life cycle from construction of the ML pipeline to its execution,” he says.

With near real-time data analysis through Google Cloud, CCBJ teams can spend time developing strategies rather than waiting for data requested from the IT systems department. Exploratory data analysis is also considerably easier as repeated trial and error has greatly improved the accuracy of analyses. Before we used Machine Learning, most machine placement processes were done by human senses, by looking at a map to find the suggestion points. By using Machine Learning to generate a massive number of placement point suggestions, the efficiency of routing of salespeople has been dramatically improved. 

In the future, CCBJ aims to automate the continuous training pipeline with Vertex AI. “CCBJ is a tech company that operates in the food industry,” says Minori. With the organization operating a vending machine network of 700,000 units, it would like to create new businesses based on utilization and analyzing data. Some of these businesses may be based on Sustainable Development Goals (SDGs) initiatives such as the utilization of recycled PET bottles, measures to prevent food loss and ways of using vending machines to contribute to local communities, which we have been working on for some time. It would be interesting if we could collaborate with Google Cloud on these in the future.”