At Universe, we serve customers day and night and are always working to make sure they have a great experience, whether online or at one of our live events.

Our technology has to make that possible, and our legacy systems weren’t cutting it anymore. What we needed was a consistent, reliable infrastructure that would help our internal teams provide a fast and innovative ticket-buying experience to customers.

With our data well-managed, we could free up time for our developers to bring new web features to customers, like tailored add-ons at checkout.

Our team of about 20 software engineers needed more flexibility and agility in our infrastructure; we were using various data processing tools, and it wasn’t easy to share data across teams so that everyone saw the same information.

We also needed to incorporate streaming data into the data warehouse to ensure the consistency and integrity of data that’s read in a particular window of time from multiple sources. Our developer teams needed to be able to ship new features faster, and the data back ends were getting in the way.   

In addition, when GDPR regulations went into effect, we needed to make sure all our data was anonymized, and we couldn’t do that with our legacy tools.

Finding the right data tools for the job
To make sure our customers were getting a top-notch online experience, we had to make the right technology choices.

Our first step was to centralize multiple data sources and create a single data warehouse that could serve as the foundation for all of our reporting requirements, both internal and external.

The new technology infrastructure we built had to let us move and analyze data easily, so our teams could focus on using that data and insights to better serve our customers.

Previously, we had lots of siloed systems and applications running in AWS. We did a trial using Redshift, but we needed more flexibility than it offered in how we loaded historical data into our cloud data warehouse. Though we were using MongoDB Atlas for our transactional database, it was important to continue using SQL for querying data.  

The trial task that really sold us on BigQuery was when we wanted to alter a small table that had about 20 million rows, used for internal reporting.

We needed to add a value, but our PostgreSQL system wouldn’t allow it. Using Apache Beam, we set up a simple pipeline that moved data from the original source into BigQuery to see if we could add the column there.

BigQuery ingested the data and let us add the new value in seconds. That was a significant moment that led us to start looking at how we could build end-to-end solutions on Google Cloud. BigQuery gave us multiple options to load our historical data in batches and build powerful pipelines. 

We also explored Google Cloud’s migration tools and data pipeline options. Once we saw how Cloud Dataflow worked, with its Apache Beam back-end, we never looked back. Google Cloud provided us with the data tools we needed to build our data infrastructure. 

Cloud for data, and for users
Introducing new technologies isn’t always simple—companies sometimes avoid it altogether because it’s so hard. But our Google Cloud onboarding process has been easy. 

It took us less than two months to fully deploy our BigQuery data warehouse using the Cloud Dataflow-Apache Beam combination.

Moving to Google Cloud brought us a lot of technology advantages, but it’s also been hugely helpful for our internal users and customers. The data analytics capabilities that we’re now able to offer users has really impressed our internal teams, including developers and DevOps, even those who haven’t used this type of technology before.

Some internal clients are already entirely self-service. We’ve hosted frequent demos, and also hosted some “hack days,” where we share knowledge with our internal teams to show them what’s possible. 

We quickly found that BigQuery helped us solve scale and speed problems.

One of our main pain points had been adding upsell opportunities for customers during the checkout process. The legacy technology hadn’t allowed us to quickly reflect those changes in the data warehouse. With BigQuery, we’re able to do that, and devote fewer resources to making it happen. We’ve also eliminated the time we were spending tuning memory and availability, since BigQuery handles that. Database administration and tuning required specialized knowledge and experience and took up time. With BigQuery, we don’t have to worry about configuring that hardware and software. It just works.

We’ve also eliminated the time we were spending tuning memory and availability, since BigQuery handles that. Database administration and tuning required specialized knowledge and experience and took up time. With BigQuery, we don’t have to worry about configuring that hardware and software. It just works.

Two features in particular that we implemented using BigQuery have helped us improve the performance of our core transactional database. First, using Cloud Dataflow to convert raw MongoDB logs to structured rows under a BigQuery table, which we can then query using SQL to identify slow or underperforming queries. Second, we can now query multiple logging tables using wildcards, since we load Fastly logs to BigQuery. 

Along with MongoDB Atlas as our main transactional database, much of our infrastructure now runs as Google Cloud microservices using Google Kubernetes Engine (GKE), including the home page and our payment system. Kubernetes cron jobs power background scheduled jobs, and we also use Cloud Pub/Sub. Cloud Storage handles any data storage if any space constraints emerge. 

Our overall performance has increased by about 10x with BigQuery. Both our customers and internal clients, like our sales and finance teams, benefit from the new low-latency reporting. Reports that used to be weekly or monthly are now available in near-real time. It’s not only faster to read records, but faster to move the data, too.

We have Cloud Dataflow pipelines that write to multiple places, and the speed of moving and processing data is incredibly helpful. We stream in financial data using Cloud Dataflow in streaming mode, and plan to have different streaming pipelines as we grow. We have several batch pipelines that run every day. We can move terabytes of data without performance issues, and process more than 100,000 rows of data per second from the underlying database. It used to take us a month to move that volume of data into our data warehouse. With BigQuery, it takes two days.

We’re also enjoying how easy and productive these tools are. They make our life as software engineers easier, so we can focus on the problem at hand, not fighting with our tools. 

What’s next for Universe
Our team will continue to push even more into Google Cloud’s data platform. We have plans to explore Cloud Datastore next. We’re also moving our databases to PostgreSQL on GCP, using Cloud Dataflow and Beam. BigQuery’s machine learning tools may also come into play as Universe’s cloud journey evolves, so we can start doing predictive analytics based on our data. We’re looking forward to gaining even more speed and agility to meet our business goals and customer needs.    

Digitec Galaxus AG is the biggest online retailer in Switzerland, operating two online stores: Digitec, Switzerland’s online market leader for consumer electronics and media products, and Galaxus, the largest Swiss online shop with a steadily growing range of consistently low-priced products for almost all daily needs. 

Known for its efficient, personalized shopping experiences, it’s clear that Digitec Galaxus understands what it takes to deliver a platform that is interesting and relevant to customers every time they shop. 

The problem: Personalizing decisions for every situation

Digitec Galaxus already had established an engine to help them personalize experiences for shoppers when they reached out to Google Cloud. They had multiple recommendation systems in place and were also extensive early adopters of Recommendations AI, which already enabled them to offer personalized content in places like their homepages, product detail pages, and their newsletter. 

But those same systems sometimes made it difficult to understand how best to combine and optimize to create the most personalized experiences for their shoppers. Their requirements were threefold:

1. Personalization: They have over 12 recommenders they can display on the app, however they would like to contextualize this and choose different recommenders (which in turn select the items) for different users. Furthermore they would like to exploit existing trends as well as experiment with new ones.
2. Latency: They would like to ensure that the solution is architected so that the ranked list of recommenders can be retrieved with sub 50 ms latency.
3. End-to-end easy to maintain & generalizable/modular architecture: Digitec wanted the solution to be architected using an easy to maintain, open source stack, complete with all MLops capabilities required to train and use contextual bandits models. It was also important to them that it is built in a modular fashion such that it can be adapted easily to other use cases which have in mind such as recommendations on the homepage, Smartags and more . 

To improve, they asked us to help them implement a machine learning (ML) contextual bandit based recommender system on Google Cloud taking all the above factors into consideration to take their personalization to the next level. 

Contextual bandits algorithms are a simplified form of reinforcement learning and help aid real-world decision making by factoring in additional information about the visitor (context) to help learn what is most engaging for each individual. They also excel at exploiting trends which work well, as well as exploring new untested trends which can yield potentially even better results. For instance, imagine that you are personalizing a homepage image where you could show a comfy living room couch or pet supplies. 

Without a contextual bandit algorithm, one of these images would be shown to someone at random without considering information you may have observed about them during previous visits. Contextual bandits enable businesses to consider outside context, such as previously visited pages or other purchases, and then observe the final outcome (a click on the image) to help determine what works best. 

Creating a personalization system with contextual bandits

While Digitec Galaxus heavily personalizes their website homepages, they are very very sensitive and also require more cross-team collaboration to update and make changes. 

Together with the Digitec Galaxus team, we decided to narrow the scope and focus on building a contextual bandit personalization system for the newsletter first. The digitec Galaxus team has complete control over newsletter decisions and testing various ML experiments on a newsletter would have less chance of adverse revenue impact than a website homepage. 

The main goal was to architect a system that could be easily ported over to the homepage and other services offered by Digitec with minimal adaptations. It would also need to satisfy the functional and non-functional requirements of the homepage as well as other internal use cases.

Below is a diagram of how the newsletter’s personalization recommendation system works:

• The system is given some context features about the newsletter subscriber such as their purchase history and demographics. Features are sometimes referred to as variables or attributes, and can vary widely depending on what data is being analyzed. 
• The contextual bandit model trains recommendations using those context features and 12 available recommenders (potential actions). 
• The model then calculates which action is most likely to enhance the chance of reward (a user clicking in the newsletter) and also minimize the problem (an unsubscribe). 

Calculating whether a click was a newsletter or an unsubscribe enabled the system to optimize for increasing clicks and avoid showing non-relevant content to the user (click-bait). This enabled Digitec Galaxus to exploit popular trends while also exploring potentially better-performing trends. 

How Google Cloud helps

The newsletter context-driven personalization system was built on Google Cloud architecture using the ML recommendation training and prediction solutions available within our ecosystem. 

Below is a diagram of the high-level architecture used:

The architecture covers three phases of generating context-driven ML predictions, including: 

ML Development: Designing and building the ML models and pipeline 
Vertex Notebooks are used as data science environments for experimentation and prototyping. Notebooks are also used to implement model training, scoring components, and pipelines. The source code is version controlled in Github. A continuous integration (CI) pipeline is set up to automatically run unit tests, build pipeline components, and store the container images to Cloud Container Registry. 

ML Training: Large-scale training and storing of ML models 
The training pipeline is executed on Vertex Pipelines. In essence, the pipeline trains the model using new training data extracted from BigQuery and produces a trained, validated contextual bandit model stored in the model registry. In our system, the model registry is a curated Cloud Storage. 

The training pipeline uses Dataflow for large scale data extraction, validation, processing, and model evaluation, and Vertex Training for large-scale distributed training of the model. AI Platform Pipelines also stores artifacts, the output of training models, produced by the various pipeline steps to Cloud Storage. Information about these artifacts are then stored in an ML metadata database in Cloud SQL. To learn more about how to build a Continuous Training Pipeline, read the documentation guide.

ML Serving: Deploying new algorithms and experiments in production 
The training pipeline uses batch prediction to generate many predictions at once using AI Platform Pipelines, allowing Digitec Galaxus to score large data sets. Once the predictions are produced, they are stored in Cloud Datastore for consumption. The pipeline uses the most recent contextual bandit model in the model registry to evaluate the inference dataset in BigQuery and give a ranked list of the best newsletters for each user, and persist it in Datastore. A Cloud Function is provided as a REST/HTTP endpoint to retrieve the precomputed predictions from Datastore.

All components of the code and architecture are modular and easy to use, which means they can be adapted and tweaked to several other use cases within the company as well.

Better newsletter predictions for millions

The newsletter prediction system was first deployed in production in February, and Digitec Galaxus has been using it to personalize over 2 million newsletters a week for subscribers. The results have been impressive, 50% higher than our baseline. However, the collaboration is still ongoing to improve the results even more. 

“Working at this level in direct exchange with Google’s machine learning experts is a unique opportunity for us. The use of contextual bandits in the targeting of our recommendations enables us to pursue completely new approaches in personalization by also personalizing the delivery of the respective recommender to the user. We have already achieved good results in our newsletter in initial experiments and are now working on extending the approach to the entire newsletter by including more contextual data about the bandits arms. Furthermore, as a next step, we intend to apply the system to our online store as well, in order to provide our users with an even more personalized experience. To build this scalable solution, we are using Google’s open source tools such as TFX and TF Agents, as well as Google Cloud Services such as Compute Engine, Cloud Machine Learning Engine, Kubernetes Engine and Cloud Dataflow.”—Christian Sager, Product Owner, Personalization ( Digitec Galaxus)

Since the existing architecture and system is also dynamic, it will automatically adapt to new behaviours, trends, and users. As a result, Digitec Galaxus plans to re-use the same components and extend the existing system to help them improve the personalization of their homepage and other current use cases they have within the company. Beyond clicks and user engagement, the system’s flexibility also allows for future optimization of other criteria. It’s a very exciting time and we can’t wait to see what they build next!

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.

Millions of people annually view esports, as top players and teams compete in thrilling, fast-paced tests of reflexes, strategy, and teamwork. Fans are a diverse group, sharing a passion for action. A new, revolutionary project we’re working on with Call of Duty League just made that action a lot better.

ActivStat brings fans, players, and commentators the power of competitive statistics in real-time—stats that matter not only to the game at hand, but also for a full roster of competitors globally. Using ActivStat, live broadcasts will soon be enhanced with more depth and color-of-play while they’re happening, building excitement and adding to the overall experience.

Technically, ActivStat is an entirely new capability for esports. It’s a constantly updating catalog of statistics that is sourced, analyzed, updated, and delivered in an easy-to-consume way across global-scale computing systems, with a latency of milliseconds or seconds, rather than minutes or hours. By comparison, many of these stats today are available to fans after a day or more of processing.

Call of Duty League plans to begin rolling out ActivStat during the 2021 season. The initial rollout will include critical information like player and team standings and winning ratios across multiple aspects of virtual combat—including ultimately what these numbers mean for rankings. ActivStats are delivered both in raw statistics and via visualizations and graphics, providing commentators with fast access to the types of insights fans crave. 

For engineers at both companies, building the service has been an epic success all its own. Due to a sponsorship with Call of Duty signed last February, our dedicated game engineering team quickly innovated a new solution incorporating high-speed networking, data pipelines from multiple cloud sources, and data warehousing to create a user-friendly dashboard. Real data was flowing into commentator dashboards by April.

Esports are more complex to cover in many ways than regular sports. Instead of a well-defined physical playing field (often a simple rectangular space), multiplayer games involve complex and sprawling virtual environments that can be the size of a large campus or airport with multiple levels and hidden locations.   

Gameplay between competitors happens across many of these locations simultaneously. In addition, competitors also each choose their own configurations of equipment, known as “loadouts,” which can dramatically affect gameplay and strategy. All of this additional complexity in online gaming involves data that needs to be captured, analyzed, and communicated to the fans in insightful ways.

Two Google Cloud technology capabilities play central roles in the operations of ActivStat. BigQuery—a planet-scale data warehouse that can store and query petabytes of information in real time—is the foundation of the ActivStat platform for gathering and summarizing millisecond-level statistics. Looker, an intuitive analytic dashboard, surfaces those insights to commentators in an easy-to-use, real-time dashboard that enables the commentators to speak to compelling insights and statistics in sync with the gameplay as it is happening in the live broadcast.

While the initial release of ActivStat for this season of Call of Duty League provides compelling and powerful capabilities, it’s only the beginning of this Call of Duty League-Google Cloud co-innovation partnership. Our future vision is mapping gameplay hotspots on the field, and predicting where to place cameras with machine learning as the match evolves–enabling broadcast producers to build excitement for fans by always being in the middle of the best action. Call of Duty League and Google will also look to drive statistics and metrics directly into the broadcast feed.

The implications of the real-time statistical capabilities of ActivStat go beyond gaming and esports. Historically, gaming has been at the leading edge of what computer processing, computer graphics, wide-area networking, data analysis, and insight can do. The real-time data ingestion and output used in ActivStat could one day be useful powering live broadcasts in other types of sporting events, as well as blending live video feeds and data to support use cases in media & entertainment, healthcare, finance, manufacturing, and other verticals. We’re excited about the potential for this bold new solution and partnership between Call of Duty League and Google in esports and beyond.

To learn more about Call of Duty League-Google Cloud partnership, visit our press release.