Cloud

Editor’s note: This article first appeared in Consumer Goods Technology Magazine

Shifting work habits, more online shopping options, rising inflation, and stretched supply chains are just a few factors making it harder to discern what’s top-of-mind for shoppers today.

But we’re starting to get a clearer picture of what consumers say they value most right now. New Harris Poll research commissioned by Google Cloud reveals how U.S. shoppers are thinking about consumer goods brands in new ways—from apparel, electronics, and beauty products, to food and beverage. 

While price unsurprisingly continues to be a major consideration in purchases, the average shopper is increasingly paying close attention to the values of consumer goods brands and how eco-friendly their products and practices are.

Shoppers want to buy from brands aligned with their values

COVID-19 drove people to reflect on their priorities, elevating concepts like community service, equity, and sustainability. A decade ago, most consumer goods companies would not have made these front-and-center, operational priorities. But today’s consumer not only wants savings and convenience, they also want that good feeling that comes from spending their money with a company that aligns with their values.

Our new research reveals that 82% of shoppers prefer a consumer brand’s values to align with their own, and they’ll vote with their wallet if they don’t feel a match. Three-quarters of shoppers reported parting ways with a brand over a conflict in values. 

Even with their favorite consumer goods products, a majority of shoppers will not compromise on principles. If there’s a value mismatch, 39% of shoppers said they’d permanently boycott their favorite brand, and 24% would break ties at least temporarily. Most won’t be quiet about their concerns either: 28% of consumers that found their values at odds with a brand said they have shared their concerns with friends and family, and another 15% have shared their qualms on social media.

Consumer goods companies need to prioritize sustainability 

A majority of today’s consumers (52%) are especially interested in supporting sustainable brands. They want to know how companies are managing their resources, specifically whether they are sourcing responsibly. These shoppers want to see meaningful, measurable efforts from CPG firms to save energy and reduce waste, like how Nuuly, URBN’s digital rental and resale business, has woven sustainability into its business operations, from its distribution centers to reusable packaging.

In fact, 66% of shoppers are now seeking out eco-friendly brands, with 55% saying they would pay more for more sustainable products. But these same shoppers are skeptical too: 72% think that companies and brands overstate their sustainability efforts. And they’re right to question brands’ practical application of their values. According to another Harris Poll survey recently commissioned by Google Cloud, 58% of executives polled across 16 countries admit that their organization has overstated its sustainability efforts.

Product availability is table stakes

A final point from the research: The global supply chain has stretched past its limits, and 60% of consumers are voicing some level of concern about it. At the end of the day, if a preferred brand isn’t actually on the shelves of a real or digital store, it doesn’t matter what the brand’s values or sustainability efforts are. A staggering 98% said they’d either buy from a different brand or search other stores or websites.

What’s a brand to do? 

After more than 25 years working in the consumer goods industry in roles ranging from marketing and product development to business strategy and technology, at companies like Johnson & Johnson, Kimberly Clark, Carter’s, and now Google Cloud, I’ve seen successful brands do four things well when it comes to their values:

1. Don’t be generic.

Your brand’s values need to be authentic, and they need to have teeth. But being too bold could run the risk of alienating some consumer segments. This is where technology can help. Personalizing your messages and outreach to specific shopper profiles is one way to ensure that your core values reach the right customers at the right time.

2. Make your values clear and consistent. 

When focusing on which values to highlight with your consumers and the world, make sure they make sense for your brand and that you’ll stick to them over time. For example, it’s painfully obvious when a brand is being opportunistic and inserting itself into conversations around values like sustainability or social justice, when it doesn’t have a history of voicing those values. The key to clear and consistent messaging of values is balancing authenticity with relatability and the appropriate amount of promotion.

3. Develop sustainability practices and communicate their impact to everyday people.

How everyday people perceive a consumer goods brand’s sustainability initiatives is different from how an investor or general business audience does. Shoppers don’t read business sustainability plans or impact reports. To increase awareness of your brand’s sustainability efforts, consumers need to identify and interact with your brand and products directly. Some of my favorite examples are how I love that Google Maps gives me the choice of eco-friendly driving directions, and that I know I can buy low-waste, packaging-free cosmetics from a company like Lush.

4. Reward customer loyalty.

Shoppers have more choices than ever before, and supply chain woes are testing preferences even further. But when someone chooses a specific brand because they feel aligned with their values or like their eco-friendly products, that shopper doesn’t always get recognized or thanked. Implementing a rewards program or following-up with customers after their purchases is one way you can make loyal shoppers feel appreciated while creating a lasting relationship that extends as long as possible.

In the exponentially growing data warehousing space, it is very important to capture, process and analyze the metadata and metrics of the jobs/queries for the purposes of auditing, tracking, performance tuning, capacity planning, etc. 

Historically, on-premise (on-prem) legacy data warehouse solutions have  mature methods  of collecting and reporting performance insights  via query log reports, workload repositories etc.  However all of this comes with an overhead of cost-storage & cpu.

To give customers easy access and visibility to BigQuery metadata and metrics, Google Cloud launched Information Schema in 2020. The Information Schema gives customers a lens to consume the metadata and performance indicators for every BigQuery job/query/API.  The storage associated with the Information Schema Views is free.  Users only pay for the cost of the Compute associated with analyzing this information. 

There are multiple factors that contribute to BigQuery spend. The two most common are storage and processing (querying), which also tend to be the largest items on your bill at the end of the month. 

In this blog, we will equip you with an easy way to analyze and decipher the key BigQuery metrics using the Information Schema.

Before getting started, it is important to understand the concept of a “slot” in BigQuery. For the purpose of this blog, we will be looking at the “Jobs Metadata by TimeSlice” view in the Information Schema. More on the Information Schema views here. 

In this blog we’ll look at a couple of use cases.

Analyze BigQuery Slot Consumption and  Concurrency for a Point in Time

Analyze Query Throughput and % Busy for a Time Period

One important highlight is to note the difference between “Concurrent Query Count” and “Query Throughput Count”.  

“Concurrent Query Count” represents the actual number of queries running at a specific point in time.  

“Query Count” which is often used to describe the number of queries running over some interval of time.  

The ultimate goal of this exercise is to produce a result set that we can export to Google Sheets and drop into a Pivot Table. We can then create visualizations for slot consumption and concurrency. This is critically important as it pertains to:

“Right-sizing” reservation allocations

Understanding the impact of newly introduced workloads into your environment

Determining if workloads are relying on idle slot capacity in order to meet SLAs

Proactively identifying trends which might ultimately result in concurrency limit errors

Alternatively, you can run this from Google Sheets using the BigQuery connector. The queries and charting will be similar in either case.

In short, we will use this information to optimize spend while ensuring consistently for the workloads that need it most.

Also, a couple of key points to remember and act on for both the below scripts:

Change the ‘Region’ qualifier of the Information Schema if you aren’t part of the ‘region-US’. For example, if your datasets are in us-east4, change the qualifier to ‘us-east4’. i.e region-us-east4.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT

In the BigQuery Console/ UI, under More→Query Settings→ Additional Settings, change the Data Location to the location where your dataset is residing. For example, if your datasets are in us-east4, select that region.

Analyze / visualize point in time slot consumption and concurrency

The purpose of this query is to collect information for all the jobs that are running during a range of time, broken into smaller intervals in which we will capture a single second (Point In Time).  

The final charts used to visualize this data will have Time on the X-Axis. For the Y-Axis, we will either have Slot Seconds or Concurrent Query Count…or, we can have both with a Primary and Secondary Y-Axis defined.  

Main SQL

Here is the SQL script from the github repo, copy-paste into the BigQuery UI and run it!  

Let’s break down the SQL into smaller chunks for easy understanding.

Declaring and setting variables

We will be declaring 7 variables.  

As you can see, these variables are all related to time. After declaring, it is time to set these variables. 

Let’s set the values for the 7 declared variables.  The first 4 variables will need to be set manually by the individual running the query.  

The first variable (_TIMEZONE) should represent your local TimeZone.  

The second (_RANGE_START_TS_LOCAL) and third (_RANGE_END_TS_LOCAL) variables will represent the range of time using your local TimeZone which you want to analyze.  

The fourth variable (_RANGE_INTERVAL_SECONDS) represents the size of the time intervals you want in which a single Point in Time (a second per Interval) will be collected.  

Note – It is very important to limit the X-Axis (Time) data points to a reasonable number (something between 60 and 360); otherwise, you will have troubles with the size of the query result export and/or you will have issues with having too many X-Axis data points in the final graph.

For analyzing what happened for a 5 minute range of time, it would be appropriate to set the _RANGE_INTERVAL_SECONDS = ‘1’, as this would produce 300 data points on my X-Axis…one per second for every second in my defined 5 minute (300 second) range of time.

For analyzing what happened for a 1 hour range of time, it would be appropriate to set the _RANGE_INTERVAL_SECONDS = ‘10’, as this would produce 360 data points on my X-Axis…one per every 10 seconds in my defined 1 hr (3600 second) range of time.

On the same note, for analyzing what happened for a 24 hour range of time, it would be appropriate to set the _RANGE_INTERVAL_SECONDS = ‘300’, as this would produce 288 data points on my X-Axis…one per every 300 seconds in my defined 24 hr (86,400 second) range of time.

In summary, we are encouraging the user to sacrifice ‘accuracy’ for larger time ranges in order to produce a more ‘readable’ chart.  While this chart is 100% accurate as of a second in time, if we only choose 1 second to visualize for every 300 seconds, then we are producing a chart that is a sample representation of actual slot consumption and concurrent query counts for the range of time being analyzed.

The fifth variable (UTC_OFFSET) represents the offset between your locally defined TimeZone and UTC.  Let’s make it an expression as opposed to a manually defined literal value because of issues with Daylight Savings Time (DST); otherwise, the user would have to remember to change the literal offset throughout the year as DST changes.

The sixth (_RANGE_START_TS_UTC) and seventh (_RANGE_END_TS_UTC) variables represent the range of time you want to analyze converted into UTC time using the derived _UTC_OFFSET value.

You might be asking yourself, “Why spend so much time declaring and setting variables?”  In short, this has been done for readability/supportability and to minimize the amount of manual changes needed every time you run this code for a new range of time.

Now that all of our variables have been declared and set, we can finally start to analyze the query.  The query is built from two derived sets of data aliased as ‘key’ and  ‘query_info’.

The ‘key’ derived table

The ‘key’ derived table is creating a one column result set with a single row for every interval (_RANGE_INTERVAL_SECONDS) that exists within the range of time you are wanting to analyze.  We are able to do this with a couple of really neat array functions.  First, we leverage the GENERATE_TIMESTAMP_ARRAY function which will produce an array (aliased as POINT_IN_TIME) of timestamps between the _RANGE_START_TS_UTC and _RANGE_END_TS_UTC variables for each interval of time defined in _RANGE_INTERVAL_SECONDS.

For example:

_RANGE_START_TS_UTC = ‘2021-08-05 14:00:00.00000’

_RANGE_END_TS_UTC = ‘2021-08-05 15:00:00.00000’

_RANGE_INTERVAL_SECONDS = 60

Using the above inputs, the GENERATE_TIMESTAMP_ARRAY will produce the following array with 61 elements:

[‘2021-08-05 14:00:00.00000’,’2021-08-05 14:01:00.00000’,’2021-08-05 14:02:00.00000,… ,’2021-08-05 15:00:00.00000’]

In order to convert this array of 61 elements into rows, we simply use the UNNEST Function.

Note: The ‘key’ derived table could be considered optional if you are 100% certain that queries were actively running every second of the time range being analyzed; however, if any point in time exists in which nothing was actively running, then your final chart wouldn’t have a datapoint on the X-Axis to represent that point(s) in time…which makes for a misleading chart.  So, to be safe, it is strongly encouraged to use the ‘key’ derived table.

The query_info derived table

The ‘query_info’ derived table is relatively straightforward.  

In our example, I want to pull Slot Seconds (period_slot_ms / 1000) and Query count information from the INFORAMTION_SCHEMA.JOBS_TIMELINE_BY_PROJECT object for every job for each second that matches the TimeStamps generated in the ‘key’ derived table.  

In this particular query, the ‘GROUP BY’ statement isn’t needed…because every job should have a single row per second; therefore, nothing needs to be aggregated, and I simply could have hard-coded a ‘1’ for Query_Count.  I left the ‘Group By’ in this example in case you aren’t interested in analysis at the Job_ID level.  If you aren’t, you can simply comment out the ‘Job_ID’ field in ‘query_info’, tweak the ‘Group By’ statement accordingly, and comment out ‘Job_ID’ in the outermost query.  In doing so, you would still be able to perform user_email level analysis with the final result set with accurate Slot Sec and Concurrency Query Count data. 

Filters used in the query

We have six filters for this query.  

First, in order to minimize the IO scanned to satisfy the query, we are filtering on ‘job_creation_time’ (the underlying value used to partition this data) where the min value is 6 hours earlier than the defined start time (to account for long running jobs) and the max ‘job_creation_time’ is less than the defined end time. 

Second, we want to only look at rows with a ‘period_start’ timestamp within our defined range of time to be analyzed.  

Third, we only want to look at job_type = ‘query’.  

Fourth, in order to avoid double counting, we are excluding ‘scripts’ (as a script parent job_id contains summary information about its children jobs).  

Fifth, and this is a personal preference, I don’t want to analyze any rows for a job if it isn’t actively using Slots for the respective Point in Time.  

The sixth filter doesn’t actually change the # of rows returned by the final query; however, it provides an increasingly large performance improvement for queries as the value of _RANGE_INTERVAL_SECONDS grows.  We first calculate the difference (in seconds) between the _RANGE_START_TS_UTC and the TimeLine object’s Period_Start timestamp.  Next, we MOD that value by the _RANGE_INTERVAL_SECONDS value.  If the result of the MOD operation does not equal 0, we discard the row, as we know that this respective Timestamp will not exist in the ‘key’ timeline built.  

Note – Yes, these rows would have been discarded when we JOIN the ‘key’ and ‘query_info’ table; however, this requires shuffling a lot of potentially unnecessary rows.  For instance, if the  _RANGE_INTERVAL_SECONDS is set to 300 and a query ran for 300 seconds, then we’d be joining 300 rows of ‘query_info’ data for that job only to filter out 299 rows in the subsequent JOIN to the ‘key’ table.  With this filter, we are pre-filtering the 299 unnecessary rows before joining to the ‘key’ table.

Outermost query  

In the outermost query, we will LEFT OUTER JOIN the ‘key’ timeline table to our pre-filtered ‘query_info’ table based on the cleaned up TimeStamp values from each table.  This needs to be a LEFT OUTER JOIN versus an INNER JOIN to ensure our timeline is continuous, even if we have no matching data in the ‘query_info’ table.

In terms of the select statement, we are using our previously defined _UTC_OFFSET value to convert the UTC Timestamps back to our defined TimeZone.  We also select the job_id, user_email,  proejct_ID, reservation_ID, Total_Slot_Second, and Query_Count from ‘query_info’.  Note, for our two metric columns, we are filling in Null values with a 0 so our final graph doesn’t have null data points.

Plotting the chart

Now that we have a query result set, we need to copy & paste the data to Google Sheets or any equivalent Spreadsheet application. You could follow the below steps

Add a Pivot Table ( For Google Sheets on the menu bar, Data→ Pivot Table). 

In the table editor – Period_Ts goes in as Rows, Total_Slot_Sec and Concuurent_Queries goes as Value.

Once the Pivot Table is created, it is time to add a chart/visual. ( For Google Sheets on the menu bar, Insert→ Chart)

Once the chart is inserted, you will see that the concurrent queries and Total_Slot_Sec are on the same axis. Let’s put them on a different axis i.e add another Y axis.

Double click on the chart and select customize. Click Series. 

Select “Sum of Total_Slot_Sec” and Select Left Axis on the Axis selection.

Select “Sum of Concurrent_Queries” and Select Right Axis on the Axis selection.

Lastly, change the chart type to a line chart. That’s it, your chart is ready!

With a little slicing and dicing, you could also produced a Stacked Area Chart By User with Slot Seconds

Analyze / visualize slot consumption and query throughput for an interval of time

The second part of this blog is to monitor the average slot utilization and query throughput for an interval of time. This query will look very similar to the previous query.  The key difference is that we’ll be measuring query count throughput for an interval of time (as opposed to a Point In Time.)  In addition, we’ll measure Total Slot Seconds consumed for that Interval, and we’ll calculate a Pct_Slot_Usage metric (applicable if you are using fixed slots) and an Avg_Interval_Slot_Seconds metric.

Main SQL

Here is the SQL script, copy-paste into the BQ UI and run it! 

Remember: Change the ‘Region’ qualifier if you aren’t part of the ‘region-US’:

Just like the previous one, let’s break down the SQL into smaller chunks for easy understanding. 

Declaring and setting variables

This variable declaration segment is exactly the same as the previous query but with three additions.  We will be using a variable named _RANGE_INTERVAL_MINUTES instead of _RANGE_INTERVAL_SECONDS.  We have added two new variables named ‘_SLOTS_ALLOCATED’ and ‘_SLOTS_SECONDS_ALLOCATED_PER_INTERVAL’.

After declaring, it is time to set these variables.

We’ll discuss the 3 new variables in this section.

_RANGE_INTERVAL_MINUTES 

As with the last query, the interval size will determine the number of data points on the X-Axis (time); therefore, we want to set the _RANGE_INTERVAL_MINUTES value to something appropriate relative to the Range of time you are interested in analyzing.  If you are only interested in an hour (60 minutes), then a _RANGE_INTERVAL_MINUTES value of 1 is fine, as it will provide you with 60 X-Axis Data Points (one per minute).  However, if you are interested in looking at a 24 hour day (1440 Minutes), then you’ll probably want to set the _RANGE_INTERVAL_MINUTES to something like 10, as it will provide you with 144 X-Axis Data Points.

_SLOTS_ALLOCATED 

Regarding _SLOTS_ALLOCATED, you will need to determine how many slots are allocated to a specific project or reservation.  For an on-demand project, this value should be set to ‘2000’.  For projects leveraging Flat Rate Slots, you will need to determine how many slots are allocated to the respective project’s reservation.  If your reservation_id only has one project mapped to it, then you will enter a _SLOTS_ALLOCATED value equal to the # of slots allocated to the respective reservation_id.  If multiple projects are linked to a single reservation_id, I’d recommend that you run this query at the ‘ORG’ level (filter on the appropriate reservation_id) and set the variable with the # of slots allocated to the respective reservation_id.

_SLOTS_SECONDS_ALLOCATED_PER_INTERVAL 

This is simply your interval length converted to seconds multiplied by the number of slots allocated.  This value represents the total number of slot seconds that can be consumed in an interval of time assuming 100% utilization.

Now that all of our variables have been declared and set, we can finally start to analyze the query.   

There are 5 key differences between this query and the “Point In Time” query we reviewed earlier.  

First, we will not be pulling information at a job_id level.  Given the intended usage of this query, including the job_id granularity would produce query results which would be difficult to dump to a spreadsheet.  

Second, we will be pulling all timeline values (seconds) within our defined _RANGE_INTERVAL_MINUTES instead of a single second.  This will result in a much more computationally intensive query as we are aggregating much more data.  

Third, we are counting all queries that ran during our defined_RANGE_INTERVAL_MINUTES instead of just counting the queries actively consuming CPU for a given second in an interval.  This means that the same query may be counted across more than one interval and the ultimate ‘Query_Count’ metric represents the number of queries active during the interval being analyzed.  

Fourth, we will be calculating a custom metric called ‘Pct_Slot_Usage’ which will sum all slots consumed for an interval and divide that by the number of slots allocated (_SLOTS_ALLOCATED_PER_INTERVAL) for an interval.  For example, for a 10 minute interval given an allocation of 2000 slots, _SLOTS_SECONDS_ALLOCATED_PER_INTERVAL would equate to 1200K Slot Seconds (10 minutes * 60 seconds * 2000 slots.)  During this interval, if we used 600K Slots Seconds, then 600K/1200K equals a ‘Pct_Slot_Usage’ of 50%.  

Fifth, we will be calculating another custom metric called ‘Avg_Interval_Slot_Seconds’ which will sum all slots consumed for an interval and divide it by _RANGE_INTERVAL_MINUTES * 60 in order to calculate the average slot consumption in Slot Secs for the interval of time.  For example, if a user were to consume 30,000 Slot Seconds in an interval of 5 minutes, the Avg_Interval_Slot_Seconds would equal 100 (30,000 slots consumed / (5 min interval * 60 seconds per minute)).

Note:  It is possible (even likely) that the ‘Pct_Slot_Usage’ metric could have a value greater than 100%.  In the case of an on-demand project, this can occur due to the inherent short query bias built into the scheduler.  

For the first 15-30 seconds of a query’s execution, the scheduler will allow a query to get more than its ‘fair share’ of Slot Seconds (relative to project concurrency and the ‘2000’ slot limit imposed on on-demand projects.)  This behavior goes away for an individual query after 15-30 seconds.  If a workload consists of lots of ‘small’ and computationally intensive queries, you may see prolonged periods of Slot Consumption above the ‘2000’ slot limit.  

In the case of a project tied to a reservation with Fixed Slots, you may see the ‘Pct_Slot_Usage’ metric exceed 100% if Idle Slot Sharing is enabled for the respective reservation and if idle slots are available in the respective Org.

As with the previous script, the outermost query is built from two derived sets of data aliased as ‘key’ and  ‘query_info’.

The ‘key’ derived table

Same as the first query, this derived query is identical to the one used in the previous example.

The query_info derived table

This derived query is very similar to the one in the previous example.  There are 4 key differences.  

First, we are not selecting JOB_ID.  

Second, we are looking at all the TimeLine seconds for an interval of time, not a single Point In Time (Second) per Interval as we did in the previous query.  

Third, in order to get an accurate count of all jobs that ran within an interval of time, we will run a Count Distinct operation on Job_ID.  The ‘Distinct’ piece ensures that we do not count a JOB_ID more than once within an interval of Time.  

Fourth, in order to match our ‘Period_TS’ value to the key table, we need to aggregate all minutes for an interval and associate that data to the first minute of the interval so that we don’t lose any data when we join to the key table.  This is being done by some creative conversion of timestamps to UNIX seconds, division, and offsets based on which minute an interval starts.

Outermost query  

In the outermost query, we will again LEFT OUTER JOIN the ‘key’ timeline table to our pre-filtered ‘query_info’ table based on the cleaned up TimeStamp values from each table.  This needs to be a LEFT OUTER JOIN versus an INNER JOIN to ensure our timeline is continuous, even if we have no matching data in the ‘query_info’ table.

In terms of the select statement, I’m using our previously defined _UTC_OFFSET value to convert the UTC Timestamps back to our defined TimeZone.  I also select the user_email,  proejct_ID, reservation_ID, Total_Slot_Second, Query_Count, and calculate Pct_Slot_Usage and Avg_Interval_Slot_Seconds.

Similarly like the first query, here are some sample Charts you can create from the final query result:

I hope you found these queries and their explanations useful..albeit, maybe a bit wordy.  There was a lot to unpack.  The origin of these queries go back to something similar we use to run on a totally different DBMS.  With BigQuery’s scripting support and Nested Array capabilities, the newly ported queries are much ‘cleaner’. They are easier to read and require many less manual changes to the parameters.  Look out for our future blogs in this series.

Vinayak Bhavnani, Co-Founder and CTO of India-based bus transport technology company Chalo, shares how Google Maps Platform is used to improve visibility for commuters and bus operators across India by visualizing geospatial data.  

Effective public transport networks contribute to the local economy and help make cities safe, pleasant, and sustainable. In India, buses make up around 90% of the public transport offering, but when you talk to the people who ride them every day, you find that there’s a lot of room for improvement. Heavy traffic means there are rarely any fixed schedules and it’s impossible to know exactly when your bus is coming. We’ve found that people tend to wait at a bus stop for up to 30 minutes a day, which creates a lot of frustration and wasted time. 

When we founded Chalo, our aim was to make the daily city commute a more positive experience. Reliability is synonymous with visibility: when you know exactly when the bus is coming, you can plan your day better. If you’re in your office, for example, and see the next bus is in 10 minutes, you can be at the stop at the exact time it arrives, instead of waiting around. To enable this, we base our solutions on richly-detailed geospatial data provided by Google Maps Platform.

Eliminating wait times and increasing revenue with geospatial data

In India, bus passengers tend to have fewer resources. The Chalo App, which can be downloaded for free, allows them to see exactly where their bus is on its route and when it will arrive at their nearest stop. They also tend to be late adopters of mobile technology, meaning we had to create an interface that was user-friendly, reassuring and adapted to all age groups and backgrounds. One of the main reasons we opted for Google Maps Platform is that it’s very present in India and other emerging markets and is familiar to our users, which inspires trust. At the same time, we like the fact that Google Maps Platform provides rich geospatial data while being simple to implement and work with. We use the Geocoding API, Reverse Geocoding, and the Directions API to enable location search and provide directions.

We also worked with MediaAgility to identify the Google Maps Platform products most suited to our needs and the best practices to be followed. This helped to ensure that our business objectives could be met efficiently.

The Chalo App also enables digital ticketing, alongside the Chalo Card, a payment card for those who don’t own a smartphone. India, like the rest of the world, is gradually moving away from cash payments, but the public transport system is proving slow to catch up, meaning people still must have cash in hand when they board the bus. Digital ticketing not only makes commuting more convenient, it also helps protect passengers, drivers and conductors during the COVID-19 health crisis by limiting physical contact. 

Chalo also offers solutions aimed at bus operators that help them improve their services and their bottom line. In major Indian cities, buses are run by a combination of public and private agencies and small private individual bus operators, with the majority of the latter only operating one or two buses. The market is very fragmented and there’s not much incentive for bus operators to invest in infrastructure or customer experience – especially when they have little to no visibility on where their fleet is at a given time, how many kilometers it travels in a day, or how much money it takes. 

The Chalo dashboard provides operators with a map-based real-time overview of bus locations, alongside scheduling features and route, ticketing, and passenger statistics. Geospatial intelligence and insight into passenger demand enable operators to explore new avenues of revenue and adapt routes and services to passenger needs. Operators who’ve partnered with Chalo report an average improvement of 10% to 30% of their bus fleet operations.

Chalo is currently powering about 100 million rides a month on 15,000 buses in 37 cities. We’d like to see the number of rides increase tenfold over the next few years. To do that, we’re looking to broaden our offer and expand to other parts of the country and across international borders. A pilot is underway in Bangkok, and we’re considering expansion into South-East Asia, Africa, and the Middle East. Having access to detailed geospatial data anywhere in the world via Google Maps Platform, without having to make any major investments or changes to our technology stack, will make this considerably easier. 

At the same time, we’re exploring artificial intelligence and machine learning to improve the accuracy of our scheduling features and we’re introducing video-based solutions for people-counting on buses. Our aim is to continually improve our offering and optimize our services. We’re looking forward to working closely with Google to make that happen.  

For more information on Google Maps Platform, visit our website.

With more than 2.5 million customers, Italian utility company A2A is committed to delivering electricity, gas, clean water, and waste collection every day. More recently, the company made another significant commitment: To incorporate the principles of the “circular economy” into its way of doing business — part of the UN 2030 Agenda’s Sustainable-Development Goals — all while also aiming to double its client base by 2030. Growing rapidly but sustainably requires operating as efficiently as possible at every level of the organization, from operating smart meters to generating accurate demand projections. That’s why A2A chose to deploy its SAP S/4HANA ERP and the SAP BW/4HANA data warehouse on Google Cloud.

Roadblocks to innovation

Instead of a linear consumption model that starts with raw materials and ends with use and disposal, the circular economy is a continuous cycle that emphasizes repair, recycling, and the creation of materials rather than their disposal. To take an example from A2A’s own success story: The company keeps 99.7 percent of collected waste out of landfills.1 Of the UN’s sustainability goals, A2A is committing to the three most relevant to its industries: 

Ensuring availability and sustainable management of water and sanitation for all

Ensuring sustainable consumption and production patterns

Protecting, restoring, and promoting sustainable use of terrestrial ecosystems

Achieving A2A’s sustainability and customer-first strategies requires high scalability, rapid data ingestion, and rich, accurate analytics. None of this could be reliably supported with the company’s legacy on-premises SAP and Data Warehouse, especially given A2A’s projected growth and the increasing complexity of the data landscape, including IoT deployments and energy market liberalization. 

Provisioning data infrastructure was also slow and complex. Simply adding a new metric could require increasing capacity by an order of magnitude. And analytical and transactional data lived in siloes, which created a fragmented and out-of-date view of each customer across sales and customer support teams. A2A’s fragmented data also made it difficult to take proactive action when changing priorities or processes required shifting focus from one data source to another. 

With a data warehouse that refreshed only once every 24 hours, simple processes such as responding to a customer calling because their power has been cut off due to an unpaid bill became cumbersome. 

Scalability was also a concern. With the on-premises solution, A2A needed to define the budget for its data warehouse over a two-year timeframe, but the rollout of new electricity meters — each sending data every 10 minutes — across Italy made those data requirements hard to predict. 

The move to the cloud: From monolith to microservices

The move has been a giant step forward in A2A’s goal of meeting its data-driven, customer-centric strategy. In deploying its SAP systems to Google Cloud, A2A can take advantage of a highly flexible hybrid environment and powerful data management and analytics. It can replicate data from Salesforce, SAP, and other systems in BigQuery, which operates as a data lake with Google Cloud SQL, connected directly to Google Analytics and Google Ads for data-driven customer service, decision-making, and marketing. 

“From BigQuery we can feed relevant information directly to the people who need it. Our customer operators work on Salesforce, so we use an OData protocol to embed real-time data in that platform. Elsewhere, we present the information through a dashboard, or with a BI component delivering one-page reports.” —Vito Martino, Head of CRM, Marketing and Sales B2C & B2B, A2A

By running SAP on Google Cloud, A2A can also count on an infrastructure platform that provides:

Scalability. The robust data architecture on Google Cloud adapts to shifting and increasing demands without compromising on speed or availability, so A2A doesn’t have to worry about over- or under-provisioning as the rollout of smart meters proceeds.

Speed. The new A2A data solution refreshes every five minutes instead of 24 hours, so the company can respond to its customers’ needs without delays. Customer operators working in Salesforce now receive real-time data from Google BigQuery so that, when a customer calls, operators can see accurate information in seconds. They can now offer value-added services and sustainable options tailored to the customer’s needs, from energy consumption to their preferred method of communication.

Availability. With microservices orchestrated by Google Kubernetes Engine, the team can update the solution through continuous integration and delivery (CI/CD), eliminating the need for downtime when changes are required.

Security and control. The A2A IT team uses Google Kubernetes Engine to orchestrate clusters of instances on Google Compute Engine, with Google Cloud Load Balancing and backups on Google Cloud Persistent Disk. Google Cloud Anthos ensures operational consistency across on-premises and cloud platforms.

Ready to grow the sustainable way

By moving to Google Cloud — the industry’s cleanest cloud, with zero net emissions — A2A is ready to grow quickly while locking down the efficiency it will need to meet its ambitious sustainability goals. “To bring sustainable utilities to market, we need to be both responsive to our customers and responsive to the internal needs of A2A,” explains Davide Rizzo, Head of IT Governance and Strategy at A2A. “Understanding what customers need in detail means we can improve their services and reduce their environmental impact at the same time.” 

Learn moreabout the ways Google Cloud can transform your organization’s SAP solutions with scalability, speed, and advanced analytics capabilities.

1.  Circular Economy: one of the four founding pillars of A2A’s 2030 sustainability policy | Drupal

Enterprise IT admins use Google Cloud Service Catalog Terraform solutions to establish curated catalogs of self-serve Google Cloud infrastructure for their organizations. 

Now, with support for multiple Terraform versions for Service Catalog solutions, administrators create and validate their deployment configs with a particular version of Terraform. The version selected is used by the Service Catalog to perform the deployment, ensuring end users enjoy a seamless experience when deploying a Service Catalog Terraform solution. This feature also enables both the admins and the platform to take advantage of the most recent capabilities available in Terraform, allowing them to quickly address any compliance concerns that may be present in previous releases.

Let’s take a closer look at what is included in this release.

Terraform version selection

With this release, admins can select a specific Terraform version when adding a new Terraform solution to a catalog. The version configuration, once applied, can be viewed on the following pages:

Solutions Listings 

Solution Details

Solution Deployment History

Catalog end users will automatically utilize the version selected by the catalog admin. Different solutions within the same catalog may incorporate different corresponding versions of Terraform, based on an admin’s selection.

Get started with Service Catalog

These new features are available now to all Service Catalog customers. To learn how to use these features, refer to our documentation:

Create a Terraform configuration in Service Catalog

Manage and update your Terraform configurations in Service Catalog

Google Cloud VMware Engine delivers an enterprise-grade, cloud-native VMware experience that is built on Google Cloud’s highly performant and scalable infrastructure. By enabling a consistent VMware experience, the service allows customers to adopt Google Cloud rapidly, easily, and with minimal modifications to their vSphere workloads, bringing the best of VMware and Google Cloud together on one platform for a variety of use-cases. These include rapid data center exit, application lift and shift, disaster recovery, virtual desktop infrastructure, or modernization at your own pace.  

Here are seven ways VMware Engine outshines alternatives for running your VMware workloads in the cloud, simplify your operations, and help you innovate faster:

1. Dedicated 100Gbps east-west networking  

Google Cloud VMware Engine nodes come with redundant switching and dedicated 100Gbps east-west networking with no oversubscription of bandwidth, unlike other options where there is generally oversubscription. This is especially important when it comes to running latency-sensitive workloads. 

2. Four 9’s of availability in a single zone 

The service offers 99.99% uptime SLA for a cluster in a single Zone with five to 16 nodes and FTT=2 or more without the need for stretched clusters, which is higher than the alternatives. Further, dedicated connectivity for core service functions such as vSAN and vMotion enables better solution stability and availability. This enables the service to support the needs of enterprise workloads that require high availability.

Note: “Cluster” means a deployment of three or more dedicated bare metal nodes running VMware ESXi and associated networking managed via management interfaces.

3. Global networking without complex routing

Google Cloud VMware Engine networking is built based on Google Cloud’s powerful networking architecture. With simplified regional and global routing modes—which allow a VPC’s subnets to be deployed in any region where our service is available—you can architect global networks without the need or overhead of creating and connecting regional network designs. You get instant, direct Layer 3 access between them. In alternative cloud environments, you may have to configure special networking between regions, often requiring VPN-based tunnels over the WAN to enable global uniform network communication. This adds to the deployment and operational complexity, in addition to cost.

4. Integrated multi-VPC networking

Users often have application deployments in different VPC networks, such as separate dev/test and production environments or multiple administrative domains across business units. The service supports “many-to-many” access from VPC networks to Google Cloud VMware Engine networks with multi-VPC networking, allowing you to retain existing deployed architectures and extend them flexibly to your VMware environments. In addition, by providing multi-VPC networking, you can pool their VMware needs—say for QA and dev—to a smaller set of clusters, effectively reducing their costs.

For more information about the end-to-end networking capabilities and services available in Google Cloud VMware Engine, please refer to the Private Cloud Networking for Google Cloud VMware Engine whitepaper. Here, you’ll find details about network flows, configuration options, and the differentiated benefits of running your VMware workloads in Google Cloud.

5. Unified, cloud-integrated model 

Google Cloud VMware Engine is a fully managed Google first-party service, operated and supported by Google and its world-class team. With fully integrated identities, billing, and access control, you have a simpler end-to-end experience that is different from other services. You access Google Cloud VMware Engine service via the Google Cloud console, like any other Google Cloud service. You can also access other native Google Cloud services privately from your VMware private cloud running in Google Cloud VMware Engine over local connections.

6. Flexibility in third-party ecosystem compatibility 

With Google Cloud VMware Engine, you can set up existing VMware on-premises third-party tools or products that require additional privileges by using a solution user account. This uniquely enables operational consistency, ensuring that the tools you have invested in and used over the years work on Google Cloud VMware Engine. Furthermore, in key areas such as vSAN data encryption, you have the choice of not only using Google Cloud Key Management Service (KMS)—which is turned on by default on vSAN datastores—but also external KMS providers such as HyTrust, Thales, and Fortanix. 

7. Dense nodes with high storage:core and memory:core ratios and fast provisioning

Google Cloud VMware Engine nodes are dense. Each node is powered by Intel® Xeon® Scalable Processors and comes with 36 cores, 72 hyperthreaded cores, 768 GB memory, 19.2 TB NVMe data and 3.2 TB NVMe cache storage. This, along with oversubscription, leads to high consolidation ratios and compelling storage:core per dollar and memory:core per dollar. In addition, you can rapidly spin up these nodes in a VMware private cloud often in under an hour, enabling on-demand, VMware-consistent capacity in Google Cloud for your needs.

These are just a few examples of customer-centric innovation that set Google Cloud VMware Engine infrastructure apart. In addition, migrating to Google cloud can save you up to 38% in TCO.Get started by learning about Google Cloud VMware Engine and your options for migration, or talk to our sales team to join the customers who have embarked upon this journey.

The authors would like to thank the Google Cloud VMware Engine product team for their contributions on this blog.

Designing your cloud network can be a critical part of your cloud journey. How the final design looks will depend on your own use case. Thinking ahead can save you some time and help avoid potential issues in the future. These ten tips provide some insight into helping you design a better cloud network architecture from the beginning.

#1 Auto mode network or custom network

When creating a VPC network you currently have two options which are an auto mode or custom mode network. Auto mode networks automatically populate a subnet in each region from the 10.128.0.0/9 IPv4 address range. This type of network can be used for isolated VPCs that would not be connecting directly to other auto mode VPC networks.

When connecting your VPCs together, if they have the same IP addresses you will run into the problem of overlapping addresses. This can occur if you connect two auto mode VPC networks together. If you plan to connect your VPC network to other environments, it is recommended to use custom mode networks in production. This gives you the ability to plan and control your IP addressing and firewall rules.

#2 – Shared VPC or VPC Network Peering

Google Cloud allows you to connect different VPC networks together on the platform by using a shared VPC or VPC network peering design. You should consider which option works best for you at the start.

Shared VPCallows you to set up a host project and connect service projects to it.

The security foundations blueprint uses a Shared VPC network architecture for its sample architecture. Administration is centralized and responsibilities can be delegated to service projects. This capability allows network administrators to manage IP addressing, firewall rules and other routing configuration for the environment.

VPC Network Peeringallows you to connect between 2 VPC networks. This feature supports different organizations and projects in Google Cloud. The peerings are 1-1 and not transitive. An example of how it works is as follows: VPC B is connected to VPC A and C. VPC C and A are not directly connected therefore they cannot communicate with each other across VPC B.

VPC network peering also has some limitations including a max number of peering to a VPC network as 25. 

Some sample architectures using VPC network peering are a Hub-and-spoke network architecture or architectures using centralized network appliances.

#3 – VPN or no VPN

Depending on the requirements you may choose to use virtual private networks (VPNs) internally or externally. Cloud VPN is easy to set up and cheaper than other external interconnect options. It can be configured in a high availability (HA) configuration. The bandwidth available per tunnel is 3GBit/s, but you can combine multiple tunnels to reach a higher bandwidth.

#4 – Which Interconnect (dedicated or partner)

Both of these options require some work from your internal team and the service providers teams. You should consider when you would actually need your interconnect to be functional because this work may have timed SLAs which can affect delivery. In addition to that there are several other things you can consider when making your choice.

General considerations

Do you have equipment in the colocation facility or not?

How much bandwidth do you need?

Do you need a SLA?

Where to terminate the connection in your VPC? e.g Terminate on a project dedicated to interconnects, or terminate directly on production VPC. This may be something to consider if you have a project that disappears quickly and you want to avoid having to reprovision a new connection everytime.

Other tricky considerations

The total cost. e.g. Google cost, service provider cost

You can see a high level of comparisons in this document’s Compare Cloud Interconnect solutions section and also you can check out Patterns for connecting other cloud service providers with Google Cloud.

#5 – Organization policy and VPC service controls

Organization policies are very helpful ways to administer activities within your cloud network. The organization policy service allows organization policy administrators the ability to configure constraints at all levels of the resource hierarchy. There are constraints that apply to compute engine and networking which can make security and administration much easier. An example of one such constraint is the constraints/compute.vmExternalIpAccess. This allows you to specify the VMs that can use external IPs.

VPC service controls allow you to establish a service perimeter to control access to your resources in Cloud services like BigQuery or Cloud Storage. These restrict certain actions and add additional security to your VPCs.

#6 – Bring your own IP (BYOIP)

This capability allows you to bring external IP addresses you already own and use them for your services in Google Cloud. If this option is something you want to do please consider the time it may take to get the IPs transferred across so that you have them available for when you need to use them. Check out the planning your deployment section to get some more insights.

#7 – DNS

DNS is another major element in network design. Depending on how your DNS is set up you may have to add additional configuration when resolving queries for your cloud and on-prem environments. Cloud DNS is very flexible and can support various zone forwarding options that can be configured to support your environment needs. Google Cloud also has some DNS best practices that may be worth considering for your designs.

#8 – Limit external IP address usage

External IP addresses are internet routable and allow direct connection to your resources. Limiting the use of external IP addresses should be considered to help provide additional security. There are several services that allow you to connect to your private network from the internet. A few of these are:

Cloud NAT. This allows internal resources to access the internet through a NAT gateway.

Load Balancers. These have public-facing anycast IP addresses and direct traffic to the connected backend resources.

Identity Aware proxy (IAP). This allows only authorized user access to the permitted resources.

#9 – IP address management

IP addresses should be unique on connected networks. If you are going to be connecting your VPCs, on-prem and other clouds you need to manage your IP ranges. Managing IPs may require some additional effort or even an IP Address Management (IPAM) tool. This tool can make management of your IPs easier as you scale.

Large Kubernetes deployments require many IPs. In those environments IP address management is very important. Here are a few IP address management strategies that you can consider for GKE.

#10 – How does failure look in my environment

What are the availability requirements and BCP requirements for the organization? Depending on the nature of the service, high availability (HA) may be a key factor.

The question of how failure looks will help you determine how to design for anticipated failure based on the business requirements. If high availability is not an issue then the design would be much different from an environment where HA is a top requirement.

You may have to do additional configuration using Cloud DNS, multiregion design and autoscaling options to meet these needs. A trade off for this can be cost but cost can be controlled in HA design architectures. There is a disaster recovery (DR) planning guide that can be helpful for review.

Additional tip

In addition to the 10 tips above it may also be wise to consider the future expansion of the business. Some examples are acquisitions, scaling of the current environment, launching new products, connecting to on-prem and other clouds. Whenever you expand a network, IP addressing and routing comes into play.  You should consider questions like:

Do I need to communicate between new VPC networks, on-prem, or other networks?

Do I have enough IP addresses for my services?

How many systems will I be exposing externally?

Do I know what my free addresses look like?

How many routes can my Cloud router support via BGP?

Most companies have strategic plans which should determine the actions the company takes and its growth objectives. These tips should be a good starting point to gain information in regard to planning for future expansion.

To learn more about designing on Google Cloud check out the following:

Documentation: Design your network infrastructure

Documentation: Secure your network 

Documentation: Optimize cost networking