The music industry is rapidly changing. Only 20 years ago, the availability of music and the infrastructure that was required to make an album a sales success were incredibly complex and expensive. With the decline of physical sales and a fundamental shift to digital, music streaming now accounts for more than half of all sales globally.

At the same time, technology has democratized music-making; in many ways, it has made the musical landscape more diverse. Artists can upload their music with the click of a button. But while it’s easier for creators to share their songs with audiences, getting paid has become more fragmented.

Although music is booming, people no longer buy it outright. Instead, listeners download music digitally or subscribe to streaming services to have their libraries with them at all times. To monetize digital content effectively, artists need to know when, where, and how often their songs are played on each service. To help them navigate this complicated royalties landscape and maximize their profits, Berlin-based international music company BMG provides customized, transparent, and fair services to songwriters and artists.

With publishing and recording divisions under one roof, the subsidiary of international media giant Bertelsmann works with both emerging artists and established stars, including John Legend, Kylie Minogue, Mick Jagger, and Keith Richards. With the MyBMG web and mobile application, clients can view and analyze their royalty details in real time and collect payment. When a new record is released, BMG uses data to maximize its impact and revenue for its creators.

“We make sure that everyone who uses our clients’ music knows who needs to be paid the associated royalties, then we collect these royalties and share them out quickly and transparently,” explains Sebastian Hentzschel, Chief Information Officer at BMG. “When our artists release new music, we make sure that it’s marketed and promoted effectively around the world.”

“We needed a scalable solution for our royalty workloads that was intuitive for our developers. We also wanted a partner, not a client-vendor relationship. With autoscaling via BigQuery, excellent customer support, and a clean and simple user interface, Google Cloud ticks every box for us.”

—Gaurav Mittal, Vice President Group Technology, BMG

Getting up to speed with a new way of paying artists

In this digital world, artists aren’t just paid every time a fan buys an album—they’re paid a small amount, or royalty, for each song downloaded or streamed by a listener. So, when the industry shifted to digital, the volume of data that BMG needed to handle grew exponentially. “One CD sale is equivalent to about 1,500 streamed songs or plays,” says Gaurav Mittal, Vice President Group Technology at BMG. “That means IT departments have to process 1,500 times the amount of data to calculate payments for artists, and this makes scalable micropayment processing very important.”

Until 2019, BMG’s infrastructure was entirely hosted on-premises. Hardware limitations made it challenging to scale on-demand, making it harder to handle the data peaks that royalty processing can bring. “With our on-premises infrastructure, we were going to hit a ceiling in a few years,” says Gaurav. “We still managed to process royalty payments for our clients, but it was increasingly time consuming and expensive. To keep focusing on our clients, rather than our infrastructure, we decided to migrate to Google Cloud.”

From the outset, Gaurav and his team had a clear vision for the partnership: “Most importantly, we needed a scalable solution for our royalty workloads that was intuitive for our developers. We also wanted a partner, not a client-vendor relationship,” he says. “With autoscaling via BigQuery, excellent customer support, and a clean and simple user interface, Google Cloud ticks every box for us.”

Keeping artists happy with business-as-usual payouts during migration

To move applications to the cloud while keeping payment cycles on track for its artists, BMG teamed up with Google Cloud partner Rackspace Technology. “We selected Rackspace Technology because it combines strong technical muscle and a global footprint, with the customer service of a local boutique firm,“ shares Gaurav.

BMG’s own technology team put together the outline for the Google Cloud architecture, which they passed on to Rackspace Technology for optimizations and the ultimate stamp of approval. Whenever Gaurav and his developers needed support, Rackspace Technology was ready to go. “So far, we’ve migrated 17 applications successfully, and Rackspace Technology has been 100% spot-on with each suggestion,” says Gaurav. “I can’t recall a single flaw in a Rackspace Technology-approved architecture, and that really says something.”

When BMG began its migration in August 2019, the team developed an ambitious two-year plan. Only 14 months later, however, the project is more than 75% complete. Among the solutions that BMG is using today are Cloud Storage to securely store 130 TB of data, and Cloud SQL as its standard database technology. The web applications run on Compute Engine, App Engine, and Google Kubernetes Engine.

“After successfully moving a few applications, it was clear that with the strong teamwork of BMG and Rackspace Technology, together with the ease of use of Google Cloud, we could speed up the project without sacrificing quality,” says Gaurav. “We’re set to complete our migration six months before schedule, helping us to quickly move out of our hybrid environment.”

“Our income-tracking teams are very savvy on the data, and the simplicity of Google Cloud empowers them to self-serve analytics, rather than wait for IT. Our teams are much more productive.”

—Gaurav Mittal, Vice President Group Technology at BMG

Royalty reporting and processing with BigQuery and Dataproc

So far, all of BMG’s critical workloads are up and running on Google Cloud. Royalty calculations, for example, which require incredible processing power and the collection of many micropayments to ensure full and timely payout, run entirely on Dataproc with output stored on BigQuery for downstream integration and reporting.

Enabling more harmonious workflows through self-serve analytics

As the new beating heart of BMG’s royalty reporting, BigQuery changed the rhythm of collaboration company-wide. In the past, income tracking teams had to contact IT departments if they needed deeper data insights for their work. By integrating Data Catalog with BigQuery, BMG has made the data more accessible to all teams. This helps them detect missing income and new revenue streams independently, maximizing profits for artists.

“Our income-tracking teams are very savvy on the data, and the simplicity of Google Cloud empowers them to self-serve analytics, rather than wait for IT,” says Gaurav. “Our teams are much more productive.”

“Google Cloud enables us to be more client focused and deliver better features faster. We believe it’s just the beginning. We offer rights and royalty services for music publishing, recorded music, neighboring rights, and books. Without scalability limitations, it’s absolutely conceivable to offer our platform as a service to other companies or industries, such as gaming. Google Cloud has opened a world of possibilities.”

—Sebastian Hentzschel, Chief Information Officer, BMG

With a leaner IT environment, BMG can focus its effort on the needs of its clients. Beyond improvements in royalty processing, it can concentrate on app development, releasing new features and enhancements more frequently. By hosting applications on Google Kubernetes Engine, App Engine, and Compute Engine, BMG has built a CI/CD pipeline with automated deployments and testing to significantly speed up workflows.

“In our old system, it could take several weeks to set up an environment,” says Gaurav. “With Google Kubernetes Engine, any of our developers can complete the process in a few clicks. Having that autonomy makes our developers more motivated and self-driven.”

“Google Cloud enables us to be more client focused and deliver better features faster,” adds Sebastian. “We believe it’s just the beginning. We offer rights and royalty services for music publishing, recorded music, neighboring rights, and books. Without scalability limitations, it’s absolutely conceivable to offer our platform as a service to other companies or industries, such as gaming. Google Cloud has opened a world of possibilities.”

With the migration almost complete, BMG is looking forward to its next technology project. It plans to leverage AutoML to further scale and automate royalty tracking with machine learning. On the marketing side, advanced analytics will help BMG determine the effectiveness of promotional campaigns around the world, further increasing profits for artists. By connecting Google Data Studio to BigQuery, BMG will increase the quality of its analyses, helping musicians better understand the reach of their music around the world.

In the end, Gaurav shares, helping musicians is what it all comes down to. “We’re a new kind of music company because we build our services around our artist, songwriter, and publisher clients, not the other way around,” he says. “Google Cloud is helping us maintain strong relationships with our clients, and that’s music to our ears.”

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.

Many of today’s games are rich, immersive worlds that engage the audience in ways that make a gamer a part of a continuing storyline. To create these persistent experiences, numerous storage technologies are required to ensure game data can scale to the standards of gamers’ demands. Not only do game developers need to store different types of data—such as saves, inventory, patches, replays, and more—but they also must keep the storage system high-performing, available, scalable, and cost-effective.

Enter Open Saves, a brand-new, purpose-built single interface for multiple storage back ends that’s powered by Google Cloud and developed in partnership with 2K. Now, development teams can store game data without having to make the technical decisions on which storage solution to use, whether that’s Cloud Storage, Memorystore, or Firestore. 

“Open Saves demonstrates our commitment to partnering with top developers on gaming solutions that require a combination of deep industry expertise and Google scale,” said Joe Garfola, Vice President of IT and Security at 2K. “We look forward to continued collaboration with Google Cloud.”

Game development teams can save game data against Open Saves without having to worry about the optimal back-end storage solution, while operations teams can focus on needed scalability and storage options. Here’s how it looks in practice:

With Open Saves, game developers can run a cloud-native game storage system that is:

• Simple: Open Saves provides a unified, well-defined gRPC endpoint for all operations for metadata, structured, and unstructured objects.
• Fast: With a built-in caching system, Open Saves optimizes data placements based on access frequency and data size, all to achieve both low latency for smaller binary objects and high throughput for big objects.
• Scalable: The Open Saves API server can run on either Google Kubernetes Engine or Cloud Run. Both platforms can scale out to handle hundreds of thousands of requests per second. Open Saves also stores data in Firestore and Cloud Storage, and can handle hundreds of gigabytes of data and up to millions of requests per second.

Open Saves is designed with extensibility in mind, and can be integrated into any game—whether mobile or console, multiplayer or single player—running on any infrastructure, from on-prem to cloud or a hybrid. The server is written in Go, but you can use many programming languages and connect from client or server since the API is defined in gRPC.

Writing to and reading from Open Saves is as simple as the following code:

  // To write
	record := &pb.Record{
		Key:      uuid.New().String(),
		Tags:     []string{"tag1", "tag2"},
		OwnerId:  "owner",
	}
	createReq := &pb.CreateRecordRequest{
		StoreKey: storeKey,
		Record:   record,
	}
	_, err := client.CreateRecord(ctx, createReq)
	if err != nil {
		t.Fatalf("CreateRecord failed: %v", err)
	}
	// To read
	getReq := &pb.GetRecordRequest{StoreKey: storeKey, Key: recordKey}
	response, err := client.GetRecord(ctx, getReq)
	if err != nil {
		t.Errorf("GetRecord failed: %v", err)
	}

We are actively developing Open Saves in partnership with 2K Games, and would love for you to come join us on GitHub. There are a few ways to get involved:

• Install and deploy your Open Saves service
• Check out the API reference
• Read the development guide and start contributing
• Join the open-saves-discuss mailing list

If you are like many of your peers you are looking to Google Cloud to reduce overheads, drive innovation, and agility with a wide variety of databases for your Oracle workloads. From Bare Metal Solution to cloud-native databases, Google Cloud offers the infrastructure to meet your needs.

The Benefits

Optimizing your Oracle workloads with Google Cloud solutions has a number of benefits.

Dramatically reduce TCO: Reduce up-front hardware, software, and maintenance costs. Migrating Oracle workloads to Google Cloud could result in up to 78% in TCO savings.

High scale and high availability: Mission-critical applications need dependable databases that scale with your business. Google Cloud offers SLA-backed databases to protect your apps.

Simplify operation: Reduce data center and database management by leveraging managed infrastructure and fully managed database services.

Migration Strategies

Google Cloud provides multiple strategies for your specific migration journey to help you with licensing, maintenance cost, and manageability.

Rehost: To migrate Oracle workloads with specific configurations, Google Cloud offers Bare Metal Solution, where you can simply lift and shift your workloads. Bare Metal Solution provides hardware, hardware support, and integrated billing and support. This infrastructure is connected with a highly resilient interconnect and connects to all native Google Cloud services with less than 2ms latency.

Replatform: Keep your core application code and migrate to compatible platforms to help reduce licensing fees and maintenance costs. You can migrate to an open source database such as Cloud SQL for PostgreSQL that has a certain amount of compatibility with PL/SQL and stored procedure. You can also offload data analytics-centric workloads to SQL-compatible data warehousing solutions like BigQuery.

Rewrite: Rewrite your application to take full advantage of cloud-native databases. If your application requires a relational database with global scalability, you can migrate to Cloud Spanner, which provides scalability with industry-leading high availability of 99.999% SLA.