When Blue Apron had issues running its data warehouse on another cloud provider, it built an analytics platform using Looker and Google BigQuery to enable faster business decisions about food inventory.

Blue Apron’s mission is to make incredible home cooking accessible to everyone. Launched in 2012, Blue Apron is reimagining the way that food is produced, distributed, and consumed.

Industries: Retail & Consumer Goods

Location: United States

Google Cloud Results

• Enables near real-time business decisions to better manage food inventory and delivery by reducing query times exponentially
• Helps improve customer service and optimize business processes with faster insights
• Reduces costs while reclaiming up to a week per month of engineering

The popularity of meal kit delivery services has surged in recent years as consumer attitudes toward home cooking and grocery shopping have shifted. As a pioneer in the category, Blue Apron helps its customers create incredible home cooking experiences by sending culinary-driven recipes with high-quality ingredients and step-by-step instructions straight to customers’ doors. Blue Apron also offers a monthly wine subscription service and a la carte culinary tools and products through its marketplace.

If that sounds simple, it isn’t. Ingredients for the meal kits must be sourced at the right time, quality, and price. Orders must be packed efficiently and in exactly the right proportions. Most importantly, meal kits must be delivered to the customer fresh and on time.

To meet these criteria and make data meaningful and intuitive to its managers, one of the tools Blue Apron relies on is Looker, an analytics platform that lets business users explore data and ask sophisticated questions using familiar terms.

A Google Cloud partner and winner of the 2016 Google Cloud Global Partner Award for Solution Innovation, Looker integrates its solution with Google Cloud Platform to help customers modernize their analytics.

Blue Apron previously used Looker with a single database instance hosted on another cloud provider. As data volumes grew and queries became more complex, it became difficult to scale. Blue Apron’s only options were choosing ever-larger server classes and increasing storage throughput by purchasing a higher number of provisioned IOPS. To improve speed, scalability, and cost efficiency, Blue Apron moved its data warehouse to Google BigQuery.

“The combination of Looker and Google BigQuery is powerful, allowing us to get data-hungry analysts essential information much faster,” says Sam Chase, Tech Lead, Data Operations at Blue Apron. “Because we choose to pay by the query, it’s also flexible and cost effective—plus storage is cheap, so we can just put data in and query what we need.”

The analytics platform of the future

When you’re making business decisions about a customer’s dinner, speed matters. Looker takes full advantage of the power of Google BigQuery, making it easy to build a data exploration platform. Blue Apron’s applications publish event data to Kafka—approximately 140 million events per day—and data is then streamed into Google BigQuery, which performs lightning-fast queries on both streamed and static data. Now, business users and analytics teams can make decisions based on near real-time information in Looker, instead of waiting until the next business day for results.

“After we moved to Google BigQuery, query time was reduced exponentially. It’s an astonishing difference, allowing us to run 300 queries per day,” says Sam.

Previously, Blue Apron spent up to a week out of every month optimizing its data warehouse to attempt to improve query performance. With Google BigQuery, all maintenance is handled by Google, reclaiming 25% of up to two engineers’ time. Even when multiple people are using Looker concurrently, query performance never degrades and storage never runs out.

“Because Google BigQuery is architected as a giant, shared cluster, growth is smooth,” says Lloyd Tabb, Founder and CTO of Looker. “Like a race car going from 0 to 120 mph, there are no shift points, just smooth acceleration. To us, it looks like the future.”

An empowering, integrated toolset

Looker takes advantage of aggressive caching and support for date-based table partitioning in Google BigQuery to increase performance, simplify the load process, and improve data manageability. By partitioning data by time, Blue Apron can also take advantage of better long-term storage pricing without sacrificing query performance. When using Google BigQuery with Looker, analysts can easily see how much data is going to be scanned before each query is run.

Blue Apron is also using Looker for Google BigQuery Data Transfer Service to provide actionable analytics for all of the company’s Google marketing data from Google AdWords and DoubleClick by Google in one place to understand campaign performance across channels, saving its data operations team months of work. Using Looker Blocks, marketers can quickly make sense of the data with reports and dashboards, and set alerts when campaign performance hits certain thresholds.

Looker Blocks for Google AdWords and DoubleClick by Google provide all the analysis you’d get straight from the Google console, plus additional value-add analysis that’s impossible to replicate without SQL. Complex metrics such as ROI on ad spend, flexible multi-touch attribution, and predictive lifetime value empower marketers with a better understanding of their customers and where to spend their next dollar.

In addition to these turnkey dashboards and pieces of analysis, marketers can customize views to meet their unique needs and workflows. These capabilities help the Blue Apron marketing team make decisions regarding the allocation of spend to maximize customer acquisition and retention.

“Everyone at Blue Apron is excited about using Google BigQuery with Looker,” says Sam. “Business users and marketers are more empowered to look for answers, instead of waiting for analytics teams. Because users know they can get results rapidly, our business processes are evolving and improving.”

For data cleansing and transformation, Blue Apron uses Google Cloud Dataproc to run fully managed Apache Spark clusters on Google Cloud Platform. It’s also leveraging Google BigQuery integration with Google Workspace to bring data into Google Sheets for further distribution and analysis.

“Transferring data between Google tools is fast because it all happens on the Google network,” says Sam. “We can pull data from Google BigQuery, run transformations with Spark, and then write it back to Google BigQuery. That’s very helpful in providing our business users and data analysts with the richest, most current data.”

A perfect match for better insights

As Blue Apron seeks to expand its reach and deepen its engagement with customers, it is making Google BigQuery and Looker available to more users, providing a high-quality interactive analytics experience. “Our ability to pull a lot of data in and compute fast results affects everyone in our company,” says Sam. “Using Google BigQuery and Looker to iterate quickly and build new models to make our operations more efficient will directly impact our customers.”

For Looker, Google BigQuery represents the next step in data warehouse evolution. “Google BigQuery is a perfect match for Looker, combining easy setup with near infinite scale-out and elasticity,” says Lloyd. “People can make smarter decisions faster that directly benefit their business and customers.”

Geospatial data is a critical component for a comprehensive analytics strategy. Whether you are trying to visualize data using geospatial parameters or do deeper analysis or modeling on customer distribution or proximity, most organizations have some type of geospatial data they would like to use – whether it be customer zipcodes, store locations, or shipping addresses. However, converting geographic data into the correct format for analysis and aggregation at different levels can be difficult. In this post, we’ll walk through some examples of how you can leverage the Google Cloud platform alongside Google Cloud Public Datasets to perform robust analytics on geographic data. The full queries can be accessed from this notebook here. 

Public US Geo Boundaries dataset

BigQuery hosts a slew of public datasets for you to access and integrate into your analytics. Google pays for the storage of these datasets and provides public access to the data via the bigquery-public-data project. You only pay for queries against the data. Plus, the first 1 TB per month is free! These public datasets are valuable on their own, but when joined against your own data they can unlock new analytics use cases and save the team a lot of time. 

Within the Google Cloud Public Datasets Program there are several geographic datasets. Here, we’ll work with the geo_us_boundaries dataset, which contains a set of tables that have the boundaries of different geospatial areas as polygons and coordinates based on the center point (GEOGRAPHY column type in BigQuery), published by the US Census Bureau.

Mapping geospatial points to hierarchical areas

Many times you will find yourself in situations where you have a string representing an address. However, most tools require lat/long coordinates to actually plot points. Using the Google Maps Geocoding API we can convert an address into a lat/long and then store the results in the BigQuery table. 

With a lat/long representation of our point, we can join our initial dataset back onto any of the tables here using the ST_WITHIN function. This allows us to check and see if a point is within the specified polygon. 

ST_WITHIN(geography_1, geography_2)

This can be helpful for ensuring standard nomenclature; for example, metropolitan areas that might be named differently. The query below maps each customers’ address to a given metropolitan area name.

  SELECT 
   cust.id as customer_id, 
   metro.name as metro_name 
FROM `looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metro
WHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),metro.metdiv_geom)

It can also be useful for converting to designated market area (DMA), which is often used in creating targeted digital marketing campaigns.

  SELECT 
   cust.id as customer_id, 
   dma.dma_name 
FROM `looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.designated_market_area` as dma
WHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),dma.dma_geom)

Or for filling in missing information; for example, some addresses may be missing zip code which results in incorrect calculations when aggregating up to the zipcode level. By joining onto the zip_codes table we can ensure all coordinates are mapped appropriately and aggregate up from there.

  SELECT 
   zip.zip_code, 
   count(distinct cust.id) as unique_customers
FROM `looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.zip_codes` as zip
WHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),zip.zip_code_geom)
GROUP BY 1

Note that the zip code table isn’t a comprehensive list of all US zip codes, they are zip code tabulation areas (ZCTAs). Details about the differences can be found here. Additionally, the zip code table gives us hierarchical information, which allows us to perform more meaningful analytics. One example is leveraging hierarchical drilling in Looker. I can aggregate my total sales up to the country level, and then drill down to state, city and zipcode to identify where sales are highest. You can also use the BigQuery GeoViz tool to visualize geospatial data!

Aside from simply checking if a point is within an area, we can also use ST_DISTANCE to do something like find the closest city using the centerpoint for the metropolitan area table. 

  SELECT 
cust.id as customer_id, 
ARRAY_AGG(
  metro.name order by ST_DISTANCE(
   ST_GEOGPOINT(cust.longitude, cust.latitude),
  metro.internal_point_geom) asc limit 1)[offset(0)] as metro_name
FROM
`looker-private-demo.retail.customers` as cust
,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metro
GROUP BY cust.id

This concept doesn’t just hold true for points, we can also leverage other GIS functions to see if a geospatial area is contained within areas that are listed in the boundaries datasets. If your data comes into BigQuery as a GeoJSON string, we can convert it to a GEOGRAPHY type using the ST_GEOGFROMGEOJSON function. Once our data is in a GEOGRAPHY type we can do things like check to see what urban area the geo is within – using either ST_WITHIN or ST_INTERSECTS to account for partial coverage. Here, I am using the customer’s zip code to find all metropolitan divisions where the zip code polygon and the metropolitan polygon intersect. I am then selecting the metropolitan area that has the most overlap (or the intersection has the largest area) to be the customer’s metro that we use for reporting.

  SELECT 
   cust.id as customer_id, 
   ARRAY_AGG(
      metro.name order by ST_AREA(
        ST_INTERSECTION(zip.zip_code_geom,metro.metdiv_geom)
      ) desc limit 1)[offset(0)] as metro_name  
FROM
`looker-private-demo.retail.customers` as cust
JOIN `bigquery-public-data.geo_us_boundaries.zip_codes` as zip on      cust.zip=zip.zip_code
,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metro
WHERE ST_INTERSECTS(zip.zip_code_geom,metro.metdiv_geom)
GROUP BY cust.id

The same ideas can be applied to the other tables in the dataset including the county, urban areas and National Weather Service forecast regions (which can also be useful if you want to join your datasets onto weather data).

Correcting for data discrepancy

One problem that we may run into when working with geospatial data is that different data sources may have different representations of the same information. For example, you might have one system that records state as a two letter abbreviation and another using the full name. Here, we can use the state table to join the different datasets.

  SELECT 
   st.state_name, 
   sum(ab.sales+fn.sales) as total_sales 
FROM `bigquery-public-data.geo_us_boundaries.states` as st
LEFT JOIN abbreviated_table as ab on ab.state = st.state
LEFT JOIN fullname_table as fn on fn.state = st.state_name
WHERE COALESCE(ab.state, fn.state) IS NOT NULL
GROUP BY 1

Another example might be using the tables as a source of truth for fuzzy matching. If the address is a manually entered field somewhere in your application, there is a good chance that things will be misspelled. Different representations of the same name may prevent tables from joining with each other or lead to duplicate entries when performing aggregations. Here, I use a simple Soundex algorithm to generate a code for each county name, using helper functions from this blog post. We can see that even though some are misspelled they have the same Soundex code.

Next, we can join back onto our counties table so we make sure to use the correct spelling of the county name. Then, we can simply aggregate our data for more accurate reporting. 

  SELECT
 c.county_name,
 sum(sales) as total_sales
FROM
 table
 JOIN `bigquery-public-data.geo_us_boundaries.counties` as c
 on testing.dq_fm_Soundex(table.county) = testing.dq_fm_Soundex(c.county_name)
WHERE c.state_fips_code = cast(36 as string)
GROUP BY 1

Note that fuzzy matching definitely isn’t perfect and you might need to try different methods or apply certain filters for it to work best depending on the specifics of your data.

The US Geo Boundary datasets allow you to perform meaningful geographic analysis without needing to worry about extracting, transforming or loading additional datasets into BigQuery. These datasets, along with all the other Google Cloud Public Datasets, will be available in the Analytics Hub. Please sign up for the Analytics Hub preview, which is scheduled to be available in the third quarter of 2021, by going to g.co/cloud/analytics-hub.

At enterprises across industries, documents are at the center of core business processes. Documents store a treasure trove of valuable information whether it’s a company’s invoices, HR documents, tax forms and much more. However, the unstructured nature of documents make them difficult to work with as a data source. We call this “dark data” or unstructured data that businesses collect, process and store but do not utilize for purposes such as analytics, monetization, etc. These documents in pdf or image formats, often trigger complex processes that have historically relied on fragmented technology and manual steps. With compute solutions on Google Cloud and Document AI, you can create seamless integrations and easy to use applications for your users. Document AI is a platform and a family of solutions that help businesses to transform documents into structured data backed by machine learning. In this blog post we’ll walk you through how to use Serverless technology to process documents with Cloud Functions, and with workflows of business processes orchestrating microservices, API calls, and functions, thanks to Workflows.

At Cloud Next 2021, we presented how to build easy AI-powered applications with Google Cloud. We introduced a sample application for handling incoming expense reports, analyzing expense receipts with Procurement Document AI, a DocAI solution for automating procurement data capture from forms including invoices, utility statements and more. Then organizing the logic of a report approval process with Workflows, and used Cloud Functions as glue to invoke the workflow, and do analysis of the parsed document.

We also open sourced the code on this Github repository, if you’re interested in learning more about this application.

In the above diagram, there are two user journeys: the employee submitting an expense report where multiple receipts are processed at once, and the manager validating or rejecting the expense report. 

First, the employee goes to the website, powered by Vue.js for the frontend progressive JavaScript framework and Shoelace for the library of web components. The website is hosted via Firebase Hosting. The frontend invokes an HTTP function that triggers the execution of our business workflow, defined using the Workflows YAML syntax. 

Workflows is able to handle long-running operations without any additional code required, in our case we are asynchronously processing a set receipt files. Here, the Document AI connector directly calls the batch processing endpoint for service. This API returns a long-running operation: if you poll the API, the operation state will be “RUNNING” until it has reached a “SUCCEEDED” or “FAILED” state. You would have to wait for its completion. However, Workflows’ connectors handle such long-running operations, without you having to poll the API multiple times till the state changes. Here’s how we call the batch processing operation of the Document AI connector:

  - invoke_document_ai:
    call: googleapis.documentai.v1.projects.locations.processors.batchProcess
    args:
        name: ${"projects/" + project + "/locations/eu/processors/" + processorId}
        location: "eu"
        body:
            inputDocuments:
                gcsPrefix:
                    gcsUriPrefix: ${bucket_input + report_id}
            documentOutputConfig:
                gcsOutputConfig: 
                    gcsUri: ${bucket_output + report_id}
            skipHumanReview: true
    result: document_ai_response

Machine learning uses state of the art Vision and Natural Language Processing models to intelligently extract schematized data from documents with Document AI. As a developer, you don’t have to figure out how to fine tune or reframe the receipt pictures, or how to find the relevant field and information in the receipt. It’s Document AI’s job to help you here: it will return a JSON document whose fields are: line_item, currency, supplier_name, total_amount, etc. Document AI is capable of understanding standardized papers and forms, including invoices, lending documents, pay slips, driver licenses, and more.

A cloud function retrieves all the relevant fields of the receipts, and makes its own tallies, before submitting the expense report for approval to the manager. Another useful feature of Workflows is put to good use: Callbacks, that we introduced last year. In the workflow definition we create a callback endpoint, and the workflow execution will wait for the callback to be called to continue its flow, thanks to those two instructions:

  - create_callback:
    call: events.create_callback_endpoint
    args:
        http_callback_method: "POST"
    result: callback_details
...
- await_callback:
    try:
        call: events.await_callback
        args:
            callback: ${callback_details}
            timeout: 3600
        result: callback_request
    except:
        as: e
        steps:
            - update_status_to_error:
              ...

In this example application, we combined the intelligent capabilities of Document AI to transform complex image documents into usable structured data, with Cloud Functions for data transformation, process triggering, and callback handling logic, and Workflows enabled us to orchestrate the underlying business process and its service call logic.

Going further 

If you’re looking to make sense of your documents, turning dark data into structured information, be sure to check out what Document AI offers. You can also get your hands on a codelab to get started quickly, in which you’ll get a chance at processing handwritten forms. If you want to explore Workflows, quickstarts are available to guide you through your first steps, and likewise, another codelab explores the basics of Workflows. As mentioned earlier, for a concrete example, the source code of our smart expense application is available on Github. Don’t hesitate to reach out to us at @glaforge and @asrivas_dev to discuss smart scalable apps with us.

With nearly 2,300 stores, The Home Depot is the world’s largest home-improvement chain — a brand that professional contractors and DIYers alike have come to depend on. The home improvement industry continues to experience unprecedented demand and dramatic increases in online ordering accompanied by expanding consumer expectations for things like curbside pickup and same day delivery. The Home Depot’s decision to migrate to cloud-based infrastructure, including the migration of the company’s SAP applications on Google Cloud which began in 2017, has set it up for success in an increasingly digital world, and helped the company adapt to changing market conditions quickly. 

Interconnected retail at scale

Building on a strong customer-first philosophy, The Home Depot aims to create what it calls interconnected retail—allowing customers to shop however, whenever, and wherever they want. “So many companies are focused on omni-channel retail,” explains Sam Moses, Vice President of Corporate Systems. “At The Home Depot, we wanted to take it to the next level. Interconnected retail puts the customer at the center of everything and enables them to shop in store, online, or both. Customers can begin a transaction online and continue in-store, or vice-versa.”

To support this strategy, the company’s SAP environment needed to be more agile. Running  everything on-premises, from central finance to POS systems, meant that The Home Depot’s IT teams experienced redundancy and repetitive, manual processes. Their data warehouse needed an upgrade to process and analyze growing and increasingly diverse data sets. The Home Depot chose to migrate its SAP environment to Google Cloud to support both the velocity and scale needed for the business as well as critical analytics capabilities needed for its bold digital initiatives. “We chose Google Cloud to support our SAP implementation. Our decision had a lot to do with the relationship between Google Cloud and SAP and also for the applications and services that are offered by Google Cloud, like BigQuery, which are helping to enable data and analytics within our organization,” Moses explains.

After migrating its SAP applications—including S/4HANA, its customer activity repository (CAR), general ledger, e-commerce system, enterprise data warehouse and more to Google Cloud, the company now has the speed, scale and flexibility to tackle enormous spikes in the business, all while staying fully available for their customers. Additionally, The Home Depot was able to transform its financial systems and make them more agile to deliver critical information across multiple business functions in real time.

Maximizing data insights to support customer experiences

By migrating to Google Cloud, The Home Depot is leveraging Google Cloud analytics to build   the industry’s most efficient supply chain including more robust demand forecasting, supplier lead times, estimated delivery times and more, all while maintaining better security than before. “We experienced unprecedented change in our customers’ behavior and their buying patterns, which puts a lot of pressure on our supply chain,” explains Moses. “So having the ability to leverage data and analytics gives us insights to know exactly what it is that our customers need.” 

The company’s analysts now use BigQuery ML for machine learning directly against the company’s BigQuery data and use AutoML to determine the best model for predictions. The Home Depot’s engineers have also adapted BigQuery to monitor, analyze, and act on application performance data across all its stores and warehouses in real time—capabilities that were not as seamless in the on-premises environment.

With hundreds of projects on Google Cloud, The Home Depot’s cloud journey is well on track, but the company is always looking to the future. “As our customers’ needs have continued to evolve, and as technology has continued to evolve, our relationship with Google will continue to advance — to be able to innovate together, to be able to find new solutions together, to better serve our customers.”

Learn more about how The Home Depot is renovating its retail operation with SAP on Google Cloud.