BigQuery performance drives personalization at scale | Google Cloud Blog

• Read original article here

Zakia Bolgot
Senior Product Manager Data Solutions, TELUS
Editor’s note: Today, we’re hearing from TELUS Insights about how Google BigQuery has helped them deliver on-demand, real-world insights to customers.
Collecting reliable, de-identifiable data on population movement patterns and markets has never been easy, particularly for industries that operate in the physical world like transit and traffic management, finance , public health, and emergency response. Unlike online businesses, these metrics might be collected  manually or limited by smaller sample sizes during a relatively short time. 
But imagine the positive impact this data could have if organizations had access to mass movement patterns and trends to solve complicated problems and mitigate pressing challenges such as traffic accidents, economic leakage, and more.
As one of Canada’s leading telecommunications providers, TELUS is in a unique position to provide powerful data insights about mass movement patterns. At TELUS, we recognize that the potential created by big data comes with a huge responsibility to our customers.  We have always been committed to respecting our customers’ privacy and safeguarding their personal information,  which is why we have implemented industry-leading Privacy by Design standards to ensure that their privacy is protected every step of the way. All the data used by TELUS Insights is fully de-identified, meaning it cannot be traced back to  an individual. It is also aggregated into large data pools, ensuring privacy is fully protected at all times.
BigQuery checked all our boxes for building TELUS Insights
TELUS Insights is the result of our vision to help businesses of all sizes and governments at all levels make smarter decisions based on real-world facts. Using industry-leading privacy standards, we can strongly de-identify our network mobility data and then aggregate it so no one can trace back data to any individual. 
We needed to build an architecture that would provide the performance necessary to run very complex queries, many of which were location-based and benefited from dedicated geospatial querying. TELUS is recognized as the fastest mobile operator and ranked first for network quality performance in Canada, and we wanted to deliver the same level of performance for our new data insights business.
We tested out a number of products, from data appliances to an on-premise data lake, but it was BigQuery , Google Cloud’s serverless, highly scalable, and fully managed enterprise data warehouse, that eventually came out ahead of the pack. Not only did BigQuery deliver fast performance that enabled us to easily and quickly analyze large amounts of data at infinity scale, it also offered support for geospatial queries , a key requirement for the TELUS Insights business. 
Originally, the model for TELUS Insights was consultative in nature: we would meet with customers to understand their requirements and our data science team would develop algorithms to provide the needed insights from the available data sets.
However, performance from our data warehouse proved challenging. It would take us six weeks of query runtime to extract insights from a month of data. To best serve our customers,  we began investigating the development of an API that, with simple inputs, would provide a consistent output so that customers could start using the data in a self-serve and secure manner. 
BigQuery proved itself able to meet our needs by combining high performance for complex queries, support for geospatial queries, and ease of implementing a customer-facing API.
High performance enabled new models of customer service
With support for ANSI SQL, our data scientists found the environment very easy to use.  
The performance boost was immediately apparent with project queries taking a fraction of the time compared to previous experiences – and that was before performing any optimization. 
BigQuery’s high performance was also one of the main reasons we were able to successfully launch an API that can be consumed directly and securely by our customers. Our customers were no longer limited on the size of their queries and would now get their data back in minutes. In the original consulting model, customers were dependent on our team and had little direct control over their queries, but BigQuery has allowed us to put the power of our data directly in our customers’ hands, while maintaining our commitment to privacy.
Using BigQuery to power our data platform means we also benefit from the entire ecosystem of Google Cloud services and solutions, opening up new doors and opportunities for us to deepen the value of our data through advanced analytics and AI-based techniques, such as machine learning. 
Cloud architecture enabled a quick pivot to meet COVID challenges
When the COVID-19 pandemic hit, we realized there was a huge value in de-identified and aggregated network mobility data for health authorities and academic researchers in helping reduce COVID-19 transmission without compromising the personal privacy of Canadians. 
As our TELUS Insights API was already in place, we were able to immediately shift focus and meet this public health need. Our API allowed us to provide supervised and guided access to government organizations and academic institutions to our de-identified and aggregated data, after which they were able to build their own algorithms, specific to the needs of epidemiology. BigQuery also enabled us to build federated access environments where we could safelist these organizations and, with appropriate supervision, allow them to securely access views they needed to build their reporting.
COVID-19 Use Case:  The image above shows de-identified and aggregated mass movement patterns in the City of Toronto into outlying regions in May 2020 when stay-at-home orders were issued by the City and residents started traveling to cottage country.  Public Health authorities were able to use this data to inform local hospitals of the surge in population in their surrounding geographic location and to attempt to provision extra capacity at nearby hospitals, including the provisioning of equipment such as much needed ventilators.
Our traditional Hadoop environments could never adapt to that changing set of requirements so quickly. With BigQuery, we were able to get the system up and running in under a month. That program, now called Data for Good , won both awards: the HPE International Association of Privacy Professionals’ Privacy Innovation of the Year award for 2020 and Social Impact & Communications and Service Providers Google Cloud Customer award for 2021. TELUS’ Data for Good program is supporting other areas of social good, in no small part because of the architectural benefits of having built on BigQuery and Google Cloud.
Ready to unleash the power of our data with Google Cloud
BigQuery is a key enabler of TELUS Insights, enabling us to shift from a slow, consultative approach to a more adaptive data-as-a-service model that makes our platform and valuable data more accessible to our customers. 
Moving to BigQuery led to major improvements in performance, reducing some of our initial queries from months of runtime to hours. Switching to a cloud-based solution with exceptionally high performance also made it easier for us to create an API to serve our commercial customers and enabled us to offer a key service, in a time of crisis, to the community with our Data for Good program . 
To learn more about TELUS Insights, or to book a consultation about our products and services, visit our website .
When we built our TELUS Insights platform, we worked with leading industry experts in de-identification. In addition, TELUS has taken a leadership role in de-identification and is a founding member of the Canadian Anonymization Network, whose mission is to help establish strong industry standards for de-identification. The TELUS de-identification methodology and, in fact, our whole Insights service, has been tested through re-identification attacks[1] [2] , stress-tested and, importantly, it has been Privacy by Design Certified. Privacy by Design certification was achieved in early 2017 for our Custom Studies product, and in early 2018 for our GeoIntelligence product.