Harnessing the Power of BigQuery and Looker to Uncover Insights and Optimize Costs
by Pedro Arellano
Director of Looker Outbound Product Management, Google Cloud
Data analysis, accelerated
Sunrun, the leader in residential solar power, offers clean, reliable, affordable solar energy and battery storage solutions. With the increasing demand for renewable energy, Sunrun needed a better way to manage their growing volumes of data across installation operations, installed systems, customer operations, and sales.
Their legacy data stack required IT and data team support for almost every internal data request. Sunrun’s legacy Oracle data warehouse wasn’t equipped to scale across growing analytics demands or easily unlock predictive insights, and this limitation led to data silos and conflicts.
After their evaluation process, Sunrun migrated to Google Cloud’s smart analytics platform—including BigQuery and Looker —to reduce extract, transform, and load (ETL) complexity, run fast queries with ease, and make data accessible and trusted throughout the organization.
- Optimization of construction processes through insights into productivity and labor data, making planning more efficient and identifying areas of opportunity.
- A 50% reduction in data warehouse design time, ETL, and data modeling.
- A reduction of their entire data development cycle by more than 60% to enable accelerated decision-making with a modernized, simplified architecture.
- An enablement of self-service analytics across their core business through a hub-and-spoke analytics model, ensuring all metrics are governed and trusted.
- A unification of metric definitions throughout the company with LookML, Looker’s modeling layer.
- Looker dashboards that facilitate regular executive huddles to set and execute data-driven strategies based on a single source of truth.
With Looker, Sunrun was able to bring the IT and business sides of the organization closer together, and improve their ability to recognize trends across their retail business, including the performance and impact of their relationships with major retail partners. Across Sunrun, data is analyzed with the customer’s experience and business goals in mind. Since Sunrun’s migration from their on-premises legacy data stack to a modern cloud environment, they’ve created infrastructure and business-wide efficiencies to help them meet the growing demand for solar power.
Business intelligence you can build upon
After relying upon Excel workbooks for data analysis, Emery Sapp & Sons, a heavy civil engineering company, chose BigQuery and Looker as key components of a new data stack that could scale with their business growth. This unified their wide variety of data sources and provided them with a holistic view of the business. Looker met their need to enable user-friendly self-service across the organization, so that all teams could access and act on accurate data through a business-user friendly interface, all with minimal maintenance.
- Pre-built, automated cost and payroll reports in Looker deliver data on schedule in a fraction of the time that Emery Sapp & Sons teams used to spend generating reports.
- A weekly profitability and accounts receivable dashboard with real-time data allows them to better predict cash flows and provide guidance on which customers they need to be talking with.
- Tracking of Zendesk support tickets in Looker easily shows what’s open, urgent, high priority, pending, and closed, allowing them to identify trends.
- Instant access to total outstanding amounts and bills owing reports for the accounts receivable team. Branch managers can sort that information by customer and prioritize follow-up communications.
Now able to visualize the necessary information intuitively, Emery Sapp & Sons can quickly understand and act upon important data. Since modernizing their data stack, they’ve cut hours they once spent on manual activities and freed up time to concentrate on what the data means for their business. They can now focus on strategic initiatives that will fuel their growth and serve their customers.
Advancing care in an uncertain time
Commonwealth Care Alliance (CCA) is a community-based healthcare organization providing and coordinating care for high-need individuals who are often vulnerable or marginalized. At the first signs of COVID-19 last winter, CCA knew their members would need enhanced care and attention. Their staff and clinicians would need reliable data that was available quickly and integrated across many domains and sources.
Fortunately, they had already put in place an advanced analytics platform with BigQuery and Looker, which the CCA data science team has used to deliver valuable information and predictive insights to CCA’s clinicians, and to develop and deploy data ops and machine learning (ML) ops capabilities. All of Google Cloud was available under a single business associate agreement (BAA) to meet CCA’s HIPAA requirements, and BigQuery proved elastic and available as a service. These two features offered reliable platform performance and allowed the small data science team to stay focused and nimble while remaining compliant.
Using a query abstraction and a columnar-based data engine, CCA could adapt to clinicians’ changing needs and provide data and predictive insights via general dashboards and role-specific dashboards—internally referred to as action boards, which help clinicians decide how to react to the specific needs of each member.
- Regular updates to BigQuery and Looker from CCA’s internal care management platform and electronic health records.
- Quick creation and distribution of custom concepts—such as “high risk for COVID-19”—in Looker’s flexible modeling layer, LookML.
- Tailored dashboards allow each clinician and care manager to access data relevant to their members, including recommended actions for coordinated care.
- Looker’s user attributes and permissions integrate with data, such as service disruptions, to allow clinicians to understand and react to changing conditions.
Using BigQuery and Looker, CCA’s data science team provides secure, companywide access to trusted data without burdening internal resources. As the COVID-19 pandemic and its effects continue to evolve, CCA continually uses the latest available information to update and guide their member support and care strategies. Now, the data science team can move on to deeper feature engineering and causal inference to enrich the insights delivered to their clinicians and the care provided to their members.
Saving $10,000 a month and more
Label Insight helps retailers and brands stay on top of trends and market demand by analyzing the packaging and labeling of different products. Their customers use this information to inform decisions around repackaging existing products or creating new products that are in line with the latest dietary trends.
Before, with their on-premises legacy BI system, numerous data silos, and cumbersome processes, it became increasingly costly, complicated, and time-consuming to quickly extract helpful insights from the data. Though Label Insight had rich data sets, accessing them would often take one person an entire week of analysis. This process was not scalable, repeatable, or reliable.
Today, Label Insight’s new data platform includes BigQuery as their data warehouse and Looker for business intelligence. When evaluating data warehouse offerings, their executive team found that the more they used BigQuery, the more they’d receive significant benefits and ROI for the company. BigQuery now offers them virtually infinite, cost-effective, scalable storage capacity and unrivalled performance.
With easy-to-set-up dashboards, reporting, and analytics, Looker democratizes data for users across the entire Label Insight organization. Looker also enables governance and control, helping them make use of the high-quality data in BigQuery, and freeing up their data team from constantly managing reporting requests. With Looker’s ability to integrate insights via embedded analytics into its existing applications like Slack, Label Insight can access consistent, accurate data in their favorite task management tools, enabling everyone to continue providing value to their customers.
- An ROI of 200%, with a savings of 120 labor hours on reporting per week, which has opened up time and resources for their teams to pursue new initiatives.
- A recurring savings amounting to $10,000/month.
- An approximately 60% (and growing) user engagement score on the platform, and with the help of their Looker superusers, goals to continue growing that number.
- Extract, transform and load (ETL) automation with Fivetran provides quick and easy access to data across their 17 different sources.
Modernizing Label Insight’s data technology stack has transformed their business in all the ways they were hoping for.
Home-run engagement for fans and clubs
The fan data engineering team at Major League Baseball (MLB) is responsible for managing more than 350 data pipelines to ingest data from third-party and internal sources and centralize it in an enterprise data warehouse (EDW). That EDW drives data-related initiatives across the internal product, marketing, finance, ticketing, shop, analytics, and data science departments, and from all 30 MLB Clubs. The team had previously used Teradata as their EDW.
MLB was experiencing issues such as query failures and latency and synchronization problems with their EDW. Providing direct user access was often challenging due to network connectivity restrictions and client software setup issues. With a migration from Teradata to BigQuery completed in 2019, MLB has realized numerous benefits from their modern, cloud-first data warehouse platform.
- Side-by-side performance tests run with minimal cost and no commitment. By switching from on-demand to flat-rate pricing, MLB could fix costs, avoid surprise overages, and share unused capacity between departments.
- Data democratization boosted by the secure, one-click sharing of datasets with any Workspace user or group.
- Access to BigQuery’s web console to review and run SQL queries on data, and to use Connected Sheets to analyze large data sets with pivot tables in a familiar interface.
- A 50% increase in query completion speed compared with the previous EDW.
- Integrations with several services MLB uses, including Google Ads, Google Campaign Manager, and Firebase.
- Integration of BigQuery with Looker, MLB’s new BI tool, which provides a clean and high-performing interface for business users to access and drill into data.
- A reduction in operational overhead of the previous database administration.
- Support coverage by Google for any major service issues, letting IT teams focus on more strategic work.
MLB can now take a more comprehensive and frictionless approach to using data to serve their fans and the league. Two projects already facilitated by their move to BigQuery and Looker include:
- OneView: This initiative compiles over 30 pertinent data sources into a single table, with one row per fan, to facilitate downstream personalization and segmentation initiatives like news article personalization.
- Real-time form submission reporting: By using the Google-provided Dataflow template to stream data from Pub/Sub in real time to BigQuery, MLB creates Looker dashboards with real-time reporting on form submissions for initiatives such as their “Opening Day Pick ‘Em” contest. This allows their editorial team to create up-to-the-minute analyses of results.
With MLB’s new data stack up and running, they’re able to serve data stakeholders better than ever before, and can harness their new data-driven capabilities to create better online and in-person experiences for their fans.