Unleashing data insights with Google’s Duet AI, zero code required

By Simon Margolis, SADA Associate CTO, AI & ML

Google’s Duet AI can help a lot of people accomplish a diverse set of tasks, ranging from email generation to code assistance to troubleshooting cloud architectures. In this blog, we’ll walk through the process of leveraging a few different versions of Duet AI in order to help us gain novel insights from a large dataset without writing any code. You’ll see how we can explore the data, answer our questions, and visualize our results, all without any data engineering experience required!

Getting grounded: understanding our dataset

The first thing we need to do is access our dataset in Google BigQuery Studio. There are numerous Duet AI integrations within the Studio, but for now we’re going to work with the “Codey” model to help us better understand our data.

For the purposes of this example, we’ll work with the public dataset available in BigQuery called bigquery-public-data.austin_311.311_service_requests. This dataset, as the name suggests, contains information about 311 phone calls in the city of Austin, Texas. As the schema (Figure 1–1) shows, the data includes information such as the type of service request made to the city, the time it took place, and in some cases, the geographical location of the request.

Figure 1–1 The schema of the data we’re working with

The first thing we’ll do is get an understanding of what our data actually contains. For example, let’s learn how many records contain geolocation data. To do this, we’ll simply ask Duet AI in English to give us the relevant SQL code for the table we’re looking at.

Prompt:

How many records in the table bigquery-public-data.austin_311.311_service_requests contain geolocation data?

Duet SQL:

SELECT
CAST(count(*) as BIGNUMERIC)
FROM
`bigquery-public-data.austin_311.311_service_requests`
WHERE
ST_GEOGPOINT(longitude, latitude) IS NOT NULL

This SQL returns the number 1710986 which tells us that we have over 1.5 million records with geolocation information. We may want to look at where service requests are coming from, and possibly understand trends regarding the geolocation of the request and the type of request. Let’s use a few more prompts to gain a better understanding of the data.

Prompt:

In the table bigquery-public-data.austin_311.311_service_requests, what are the top 4 most common complaint descriptions?

Duet SQL:

SELECT
 complaint_description,
 COUNT(*) AS num_complaints
FROM
 `bigquery-public-data.austin_311.311_service_requests`
GROUP BY
 complaint_description
ORDER BY
 num_complaints DESC
LIMIT
 4

Result:

[{
 "complaint_description": "Austin Code - Request Code Officer",
 "num_complaints": "213566"
}, {
 "complaint_description": "Traffic Signal - Maintenance",
 "num_complaints": "98681"
}, {
 "complaint_description": "Loose Dog",
 "num_complaints": "80168"
}, {
 "complaint_description": "ARR Missed Recycling",
 "num_complaints": "55380"
}]

This is already showing me some interesting trends. I’d like to dive deeper into the “Loose Dog” occurrences since that surprised me as a top 4 complaint! Let’s see if we can learn more about where these are taking place.

Prompt:

In the table bigquery-public-data.austin_311.311_service_requests, what are the top 5 locations with “Loose Dog” in the complaint description? Also include the latitude and longitude.

Duet SQL:

SELECT
 incident_address,
 latitude,
 longitude,
 COUNT(*) AS frequency
FROM
 `bigquery-public-data.austin_311.311_service_requests`
WHERE
 complaint_description LIKE '%Loose Dog%'
GROUP BY
 incident_address,
 latitude,
 longitude
ORDER BY
 frequency DESC
LIMIT
 5;

Result:

[{
 "incident_address": "11716 GEMMER ST, AUSTIN, TX 78617",
 "latitude": "30.15911499",
 "longitude": "-97.64598773",
 "frequency": "217"
}, {
 "incident_address": "11716 GEMMER ST, DEL VALLE, TX 78617",
 "latitude": "30.159115",
 "longitude": "-97.64598773",
 "frequency": "101"
}, {
 "incident_address": "5801 AINEZ DR, AUSTIN, TX 78744",
 "latitude": "30.18683439",
 "longitude": "-97.73822222",
 "frequency": "78"
}, {
 "incident_address": "2016 BLUEBONNET LN, AUSTIN, TX 78704",
 "latitude": "30.25162171",
 "longitude": "-97.77451642",
 "frequency": "78"
}, {
 "incident_address": "7609 LONGVIEW RD, AUSTIN, TX 78745",
 "latitude": "30.2031482",
 "longitude": "-97.82694257",
 "frequency": "71"
}]

Now I’m starting to see that there is a high concentration of these complaints in a specific location. Out-of-the-box, I can simply click “Chart” in BigQuery to see these results visualized to help me better understand them (Figure 1–2). While this is helpful, I’d like to go deeper with the data and better visualize the trends.

Figure 1–2 BigQuery visualizes the results without any explicit configuration by generating what it believes are relevant charts.

But, before we move on, I want to make sure I’m learning as we go here. I see that the generated SQL has “complaint_description LIKE ‘%Loose Dog%’” in the WHERE clause, and I’d like clarification on its function. To be sure my results are correct, I need to know if this query is correct as well. To do this, I’ll simply ask Duet AI to explain the query to me in English (Figure 1–3) by highlighting my text, right-clicking, and selecting “explain current selection”.

Figure 1–3 Duet AI makes it easy to learn as you build.

This opens up a chat window with Duet AI for Google Cloud which explains the intent of the query. This feature even lets me ask follow-up questions for additional clarity, all while maintaining the context of our ongoing conversation (Figure 1–4).

Figure 1–4 Chatting with Duet AI while working on my data helps me better understand what I’m doing and how to get the insights I’m searching for.

Now, armed with the confidence that my query is indeed giving me the results I want, I can move forward.

Data science tools without a data scientist

Duet AI has been super helpful so far in helping me better understand the data I’m working with and uncovering some interesting trends within it. Without Duet AI, this would have required either a lot of time, a lot of SQL expertise, or both. We did this today in just a few minutes with no experience with SQL or any other coding language.

While this alone provides valuable information, a professional data scientist would use a very different approach for distilling insights. A data scientist likely would be working in a proper programming language like Python, she would probably use a Python Jupyter notebook to inspect the data, and she would invoke various pre-built libraries for data science, such as pandas to better understand her data.

While that type of work comes with a steep learning curve and years of experience, we can still invoke some of the same tools without any experience in the field or related technologies by leaning on Duet AI for help.

To get started, we’ll take the results from our previous SQL queries and ask BigQuery to spin up a Python Jupyter notebook containing the specific results we’ve been analyzing. With one click of a button (Figure 2–1), we’re able to get into a notebook that has been pre-populated with all of the code required to get back to our results. All we have to do is click “play” (Figure 2–2).

Note, for the purposes of this example, I’ve expanded our result set from the top 5 locations to the top 100. Now that we have our notebook loaded, we can ask questions about our data such as:

Prompt:

Plot the results on a map using the coordinates in latitude and longitude.

Duet Python:

import pandas as pd
import geopandas as gpd
import matplotlib.pyplot as plt

# Read the results DataFrame
results = results.dropna()

# Create a GeoDataFrame from the latitude and longitude columns
geometry = gpd.points_from_xy(results['longitude'], results['latitude'])
geo_df = gpd.GeoDataFrame(results, geometry=geometry)

# Plot the GeoDataFrame on a map
geo_df.plot(figsize=(10, 10))
plt.show()

Result:

Great! We’ve just accomplished a couple of major goals. Firstly, we used plain language to generate code which calls upon tools like pandas and matplotlib — typically utilized by seasoned data scientists. We did this without even knowing these tools existed, let alone how to use them! Second, we generated a visualization which clearly shows we have at least one outlier in our data. Let’s remove this before we move forward.

Once again, we’ll ask Duet AI for help within our notebook. We simply type the plain text request “remove the outliers from the coordinate data” and Duet AI handles the rest (Figure 2–3) with its knowledge of the data we’re working with.

Figure 2–3

With our data cleansed, we can ask again for a plotting of our incident locations to see a trend emerge (Figure 2–4).

Figure 2–4

To make things even easier to read, I’ll modify our prompt to “plot the results on a map where the frequency of each point can easily be seen” and get an output (Figure 2–5) which easily lets us see where the hotspots are for loose dog calls (emphasis added).

Figure 2–5

Armed with this information, we may be able to take action in the provided neighborhoods to reduce the occurrences of loose dogs. We may even go further in the future and start to visualize our data with timestamps, time of day, day of week, or other filters to better understand the trends in the data.

I hope this encourages readers of all backgrounds and experience levels to start playing with the data in their lives. The barrier to entry has never been lower for extracting meaningful insights from large sets of data, and in today’s world of cheap storage and “big data,” the opportunities for discovering new findings are vast!

Learn more about Duet AI with SADA, An Insight company

Don’t hesitate to reach out and schedule a complimentary consultation with a SADA AI expert to explore the ways you can integrate generative AI solutions like Duet AI into your organization. We’ll be happy to help you get started.

About Simon Margolis

Simon Margolis, a dedicated technology professional and practice leader, focuses on fostering forward-thinking strategies and empowering organizations to operate smarter and more efficiently. With 12+ years of experience in IT and cloud solutions, Simon has held many roles from engineering and solutions architecture to sales and business development. As a Google Cloud Qualified Developer, Simon helps guide SADA and its customers through the rapidly expanding AI and ML space.

About SADA, An Insight company

SADA is a professional services market leader and solutions provider of Google Cloud. Since 2000, SADA has helped organizations of every size in healthcare, media, entertainment, retail, manufacturing, and the public sector solve their most complex digital transformation challenges. With offices in North America, India, the UK, and Armenia providing sales, customer support, and professional services, SADA has become Google’s leading partner for generative AI solutions. SADA’s expertise also includes Infrastructure Modernization, Cloud Security, and Data Analytics. A 6x Google Cloud Partner of the Year award winner with 10 Google Cloud Specializations, SADA was recognized as a Niche Player in the 2023 Gartner® Magic Quadrant™ for Public Cloud IT Transformation Services. SADA is a 15x honoree of the Inc. 5000 list of America’s Fastest-Growing Private Companies and has been named to Inc. Magazine’s Best Workplaces four years in a row. Learn more at www.sada.com.

If you’re interested in becoming a part of the SADA team, please visit our careers page.