Connected Sheets: A Deeper Dive

As of January 3rd, 2022, the Small Business Administration provided 11.47 million loans totaling $792.6 billion. The intent of the Paycheck Protection Program was to provide incentives for small businesses to keep their workers on the payroll. Nationally, small businesses reported retaining 89.9 million jobs. The value of the Pandemic Relief Dataset increases with time and creates a lasting asset for business research and business development for Cloud9 offerings. 

The first step in accessing Google Connected Sheets is logging into your Google account and creating a new Sheet. At the top of the sheet, you will find the “Data” tab; within that tab, you should see “Data Connectors” near the bottom of the list. By selecting that, you will be prompted to connect your sheet to a project in Google BigQuery, a Google Cloud Platform tool for further data analysis and storage. Don’t worry, we’ll guide you through uploading your data to BigQuery as well! In this demo, we will be connecting to our Pandemic Relief Dataset. Our Dataset contains 14 million rows and 74 columns of data, the equivalent of over one billion cells of data. For comparison, an Excel Spreadsheet is only able to handle about one million rows of data, and a regular Google Sheet holds even less. Connected Sheets is a revolutionary platform that uses familiar tools like Pivot Tables and Charts but creates these insights based on huge sets of data in a way that has never been done before.

Once the dataset is connected and the sheet is opened, a preview of the data will be displayed (as seen below). You will be able to view all columns in the dataset, along with the first 500 rows (0.000005% of our full dataset). The Connected Sheet looks just like a regular Google Sheet but has an included set of action buttons: Chart, Pivot Table, Function, Extract, Calculated Column, and Column Stats. It is important not to overlook these functions, everything they do is run on the entire dataset, allowing users to develop business insights on millions or billions of rows of data in minutes!

If you know exactly what question you want to answer based on your data, functions are the best tool to use.  For example, we wanted to determine the total amount of PPP Loan money given to the businesses that received a PPP loan across the country. To do this, we used a SUM function of the column labeled “CurrentApprovalAmount”. To use functions in Connected Sheets, simply click the function button on the main preview sheet and create a new sheet for the function.

 The formula in the sheet looks like SUM(ppp_ver220403_221118!CurrentApprovalAmount)

The formula gave the answer of 792616412701

This tells us that $792,616,412,701 in Paycheck Protection Program Loans were given out to businesses across the nation.

In Comparison:

If you were to find this same information on other platforms, it would require a simple SQL code query. However, this task is easily done with Connected Sheets without needing to have a coding background. In Google BigQuery, this would look like:

Extracts help filter and sort the data if you only want to look at specific aspects of the dataset. For example, if we want to look at all the businesses that are based in Smithfield/North Smithfield, Rhode Island; that received a PPP loan, we can use the extract feature! Simply click the Extract Button, create a new sheet, and 

filter or sort the data specific to your needs. In the screenshot below, you can see that the extracted data is only showing businesses in Smithfield/North Smithfield. We are also able to see other helpful information by adding the necessary columns; we added the Business Name (“BorrowName”), the City the business is located in (“BorrowerCity” filtered to only show “Smithfield” or “North Smithfield”), the Number of Employees (“JobsReported”) and the loan amount they received (“CurrentApprovalAmount”). 

Calculated columns are useful for creating new columns based on previous table columns using simple math functions. If you are familiar with writing a formula in a column in an Excel spreadsheet, this is almost the same thing. The only difference is typing it into a pop-up box for which the formula will later be used to create a new column. Calculated columns always can offer new insights, especially in Connected Sheets, as the calculated column is run on millions or billions of rows of data.  For example, if we were curious to discover the possible opportunity value of each business for tax engagement, we can create that as a new calculated column. Simply click the “Calculated Column” button on the preview page and a pop-up should appear. Create a name for the new column and type the formula into the box starting with an “=” sign. Use the column names on the right to refer to different existing columns in the dataset, and use the functions (22 additional functions included here than with the regular “Functions” button) to perform functions such as finding the “sum” or “average”. In this example, the formula we want to use is multiplying the “JobsReported” column by the number 275. By clicking “add”, we will now have this calculated column as part of the dataset. We will now be able to see the Opportunity Value for Tax Engagement for every business in the dataset which is extremely helpful to us. 

Google Connected Sheets is a great visualization tool as well. Users can create new sheets that function as dashboards. Simply click on the Chart Button on the preview sheet to create a new sheet with a chart. Connected Sheets allows users to choose from a range of chart types, and customize charts to the user’s needs.  Using the Pandemic Relief dataset, we wanted to visualize the average Loan Amount for each C9 House (our specialty areas) for businesses located in Massachusetts. We picked “Column Chart” as our Chart Type, used the “C9_House” variable on the x-axis, used “CurrentApprovalAmount” as the series, and filtered it with “BorrowerState” containing “MA”. 

Pivot tables are a handy way to explore the details of your data. They have the ability to summarize the specific aspects of the data you want to look at; and can easily show you new insights simply by switching out the variables shown in the table using the pivot table editor pop-up. Pivot tables in Connected Sheets have the capacity to show some of the same results as complicated queries, aggregating millions or billions of rows of data in a way that anyone who has ever worked with a basic pivot table can understand and use. Pivot tables can provide immense detail, incomparable even to complicated coding tools. They are an excellent resource for providing comfort to those not familiar with the data, as they offer high-level totals with extended detail that traditional methods may not offer. This ability to drill down offers greater data reliability and accuracy, as it provides the ability to understand the reasons behind the bigger numbers and insights found from the data. Furthermore, by looking at the details, anyone would be able to find and fix inconsistencies in the data if there are any. 

For this example, we made a pivot table detailing total fraud amounts per state. We listed “BorrowerState” as a row, “Fraud” as a column and as a filter (filtered to “1”, the binary equivalent of “yes” to fraud), and “CurrentApprovalAmount” as a value. View the top half of the pivot table (this is only a preview of the first rows as it contains a row for each state)

If we wanted to take a look at what industries were the most fraudulent, we could switch out “BorrowerState” as a row and simply add “Industry”. Take a look at the pivot table to the left to see how that shows us so many new insights so easily. We could continue making quick and easy changes to this pivot table to show even greater detail!

By adding “ProcessingMethod” and “BorrowerName” as rows to the pivot table, we will now be able to see which loan type (PPP or PPS) was more prevalent for fraud. We will also be able to see the specifics of which lenders were involved. 

Column Stats are a unique feature in Google Connected Sheets that can prove to be extremely useful. In traditional platforms, users typically are required to create a query to discover how many values are in a given column, how many blanks are in a given column (null values), the number of unique values, and the frequency of each value. However, with Connected Sheets, all this information can be found with the click of a button – literally. To view column stats, simply highlight a column on the preview sheet by clicking on the column name. Then click the “Column Stats” button, and a Column Stats window will pop up on the left-hand side of the sheet. You can even create a pivot table or chart on the data right from the “Column Stats” window. 

Due to the “Column Stats” Feature in Connected Sheets, we can now see a variety of information about the BorrowerState column, for example. We now easily know that there are just over 2 million null values in the column. Furthermore, there are  57 unique values; which could be interesting to take a look at because there are only 50 states, so it is probable that some state abbreviations were entered incorrectly. This can be a huge asset for data accuracy, as we could check out those details to further understand the data. We are also able to see that California has the most businesses that received a loan, which makes sense based on population.

Exploring AI in Sheets

Connected Sheets now has a new AI feature. It allows users to enter a prompt and it will create a customized template. At its current stage, it generates sample data to fill the template. However, it is still being perfected, and will eventually be able to organize and analyze your data. In this example, I prompted the AI with the statement: ‘product development steps’. It answered with the below spreadsheet, which outlines the process of product development complete with a timeline. This can save valuable time and leave the actual planning to you and the formatting to AI.