[MUSIC PLAYING]
SPEAKER: Welcome to this demo of Connected Sheets.
We'll show you how Connected Sheets combines the power
and scale of BigQuery with the simplicity of a spreadsheet,
making it easy for anyone to analyze data
at an unprecedented scale.
You can now access, analyze, and visualize billions
of rows of BigQuery data in Sheets
without requiring any SQL knowledge.
Organizations that are increasingly awash with data
can now apply familiar Sheets tools,
like pivot tables, charts, and formulas,
to easily derive insights from big data with a live connection
to BigQuery.
We see many teams and industries benefiting
from Connected Sheets, including finance, marketing,
and operations teams, to name a few.
For this video, we'll focus on Chicago taxi data,
but encourage you to try it out on your own data
to really experience the power of Connected Sheets
in a context most relevant for you.
So let's get started and connect to our dataset.
First, open up new Sheets file.
Go to the Data menu.
At the bottom, you'll see Data Connectors with the option
to connect to BigQuery.
Once you do that, you'll be asked
to choose a BigQuery project.
I can search my projects here and then select any project
that has billing enabled.
In this example, I'll choose the Connected Sheets Demo Project.
Here I see all datasets that are available in this project.
We see both private datasets that are specific to this cloud
project as well as public datasets
that are available for anyone to analyze.
For this demo, let's choose the public datasets.
Here I see some interesting datasets
for analysis-- for example, bike share programs, crime data,
baseball stats, et cetera.
Let's search for taxi data, and let's use the Chicago taxi
dataset.
I'll click on Taxi Trips.
You'll notice I didn't write any SQL.
All I did was choose that table.
That's all it takes to make a connection to the full dataset.
Now here we see it has successfully
connected with 191 million rows and 23 columns, which
would be over a billion cells in a traditional spreadsheet.
Now, what I see here is a preview
of the full dataset where I can scroll over and see
all the columns and sample data in the dataset.
If I want, I can filter and sort this preview as well in order
to explore the dataset and understand more about what's
in this table.
Now let's do some analysis using Sheets functions.
At the top of the Preview tab, I see the Function button.
Clicking on it shows me all the functions
that are available for me to use to analyze my data in BigQuery.
I see things like AVERAGE, COUNTIF, SUMIF, MIN, MAX, et
cetera.
To use them, I can click on one directly.
Let's say I'd like to see the number of taxi companies
in Chicago.
I can use COUNTUNIQUE.
So let's click on that.
Let's add it to a new sheet.
And then let's choose Company.
Now let's hit Apply.
And we'll run this against the entire 191 million rows
of data in BigQuery.
Now, it looks like there are 164 distinct taxi companies.
Let's try another way.
Let's say I want to know what percentage of taxi rides
include a tip.
Now, to do that, I can use a COUNTIF
where taxi trips are greater than zero.
Let's hit Apply.
And here we see about 73 million rides that include a tip.
Let's copy that formula over, and now
let's do a COUNTIF where the fare is greater than zero.
We'll hit Apply again, and we see 190 million rows.
So if we write our formula, we can see that approximately 38%
of the rides in Chicago included a tip.
Now let's do some analysis using Sheets charts.
To get started with charts, I'll click on the Charts button
at the top of the Connected Sheets preview.
Let's now insert the chart on a new sheet.
Here I see my Chart Editor, and I can
get started building my chart.
First I'd like to see what forms of payment people
are using for their rides.
I can choose whatever chart type I like.
In this case, I'd like to see a pie chart,
so let me select that.
Then on the right, I can see the various columns in my dataset.
I can drag them over into the different inputs.
So let's drag Payment Type over into Label
and then drag Fare over into Value.
Let's turn SUM into COUNT, and then let's click Apply.
We now created this chart against the 191 million rows
in BigQuery, and we see here that the result is cash
beats out credit cards, 60% to 40%.
However, if we instead change from COUNT to SUM,
we can weight the chart by fair amount,
and then we see that cash just slightly edges credit cards,
implying credit cards have higher fares on average.
So you can see it's pretty easy to create a chart.
Again, no SQL required.
Now let's say I'm curious about Chicago taxi rides over time.
Let's go back to my preview.
I'll click the Chart button again,
and let's insert this chart on another new sheet.
Instead of a pie chart, let's change it into a line chart.
For my x-axis, I'll drag over Trip Start Timestamp,
and we'll see a new option here called Group By.
With Group By, I can roll up dates into higher-level units.
For example, I have the option of rolling up by hour, by day
of week, by month, et cetera.
So let's do a ROLLUP by year and month
to see how taxi revenue is changing over time.
Let's drag in Fare into Series and then hit Apply.
Now I should get back a time series
for how taxi ridership has changed over time.
Interesting.
It looks like overall, revenue peaked in 2015,
and it's been on the decline since.
What if I want to filter my results to focus
on a particular payment type?
Four example, how has mobile payments changed over time?
To do that, let's add a filter, choose Text Contains,
and then input mobile.
Now when I hit Apply, I can see that mobile payments and taxis
is in a steady increase.
[MUSIC PLAYING]
Let's try our hand at doing some analysis using pivot tables.
To get started, let's return to our preview,
click on the Pivot Table button, and insert it onto a new sheet.
On the left, we see our pivot table placeholder,
and on the right, we see our Pivot Table Editor.
I'm interested in understanding which time of day
do we see the most rides.
So to do that, I'm going to drag Trip Start
Timestamp into my rows, and then I'm
going do a GROUPBY using hour of day.
Then I'll drag Fare into values
Let's make this a COUNT rather than a SUM,
and then we'll hit Apply to run this.
So it looks like 18, which is 6:00 PM,
sees the most taxi rides.
Now, what if we were to break it out by day of week as well?
So let's drag Trip Start Timestamp into Columns
into a group by day of week.
Now let's hit Apply.
And this will give us a breakout by both hour
of day and day of week.
To make this easier to read, let's adjust the number
formatting.
And then let's apply some conditional formatting.
We can use what's called color scale,
and we can make the high values green and the low values white.
Now we have something else.
It looks like during the week, rides
are most frequent during the morning and evening rush hour,
while on the weekend nights, rides
are most frequent into the early hours of the morning.
What about the most expensive times for taxis?
To do that, let's change from COUNT to AVERAGE, and let's
see what happens when we hit Apply there.
Now we see a different pattern.
It looks like the most expensive taxi rides
are early in the morning, especially on Monday,
perhaps business travelers taking long rides to catch
an early business flight.
You can make many different kinds of pivot tables
using Connected Sheets.
Now let's jump over to a tab where I see number of rides
by payment type.
Let's say I wanted to see the top five taxi companies
by revenue per payment type.
I can simply drag in company, change number of rows
from all to five, to limit to the top five.
I also will then change the sort to sort by sum fare
rather than the default, which is alphabetical.
And we'll make a descending rather than
ascending to sort from highest to the lowest.
When I hit Apply here, I'll see the top five
highest-grossing taxi companies per payment type.
So these are a few examples of how
you can use pivot tables to analyze millions or even
billions of rows of data in BigQuery.
By default, Connected Sheets shows
you a preview of 500 rows of raw data in BigQuery.
However, if you want import more raw data from BigQuery
into Sheets, you can use the extract function.
To do this, click on Extract from the preview sheet.
So let's say I want import the 25,000 most recent taxi rides.
I can choose the columns I want to import and then specify
any filters and also how I want to sort it.
So let's say I want to sort by Trip Start Timestamp
descending.
And then specify how many rows I want to import, hit Apply,
and this will import the raw data from BigQuery into Sheets.
[MUSIC PLAYING]
Connected Sheets also allows you to create what
are called calculated columns.
These are new columns you can add
that are transformations or combinations of other columns
in the dataset.
It's easy to create your own.
Simply click the Add Calculated Column button
from the Connected Sheets preview.
You can see the columns available to you on the right
as well as the various functions you can use in your formula.
You can also see some example calculated columns
by clicking on the question mark at the top.
In this case, I want to create a calculator column that
calculates tip percentage.
So let's name it tip percentage.
And then we can write our formula.
In my case, I'll write if fare greater than 0,
tips divided by fare times 100, otherwise 0.
Then click Add to see the column added to the preview.
I hit apply and I now see the column calculated
for each row in my preview.
Additionally, this calculated column
will be available throughout Connected Sheets,
meaning you can use it in charts, pivot tables,
functions, and extracts.
[MUSIC PLAYING]
By default, all the analysis you do in Connected Sheets
remains unchanged until you decide to refresh it.
So even if the data is changing in BigQuery,
your charts, pivot tables, and your formula values
will not change unexpectedly without your control.
Each item provides the date that it was last updated,
and you have the option to hit the Refresh button to update
each item individually.
Alternatively, you can click the Refresh Options
from the top of the preview, which will open up the Refresh
Option sidebar.
From there, you can choose Refresh All
to make sure all connected Sheets analyses are
updated to the latest data.
You can also choose to schedule a refresh
by clicking the Setup Now under the Scheduled Refresh section.
Say that you can update your report every day,
every week, or every month.
You can also specify what time of day or day of week or day
within the month that you want it to be refreshed.
This allows you to refresh your report on a regular basis
without you manually doing any refreshing yourself.
[MUSIC PLAYING]
For those who are familiar with SQL,
we have another option called Query Parameters.
This allows you to create a SQL query that contains references
to data that lives in particular cells in your file.
For example, let's say I have the Chicago taxi report
that I built, with a dropdown that shows different taxi
companies in Chicago.
I can write a query that will then update depending
on the value in that dropdown.
To do this, let's edit my underlying query.
So if I go back to my preview and click the gear icon,
I can choose Write Custom Query at the bottom.
Here we can see that what we really
have is a simple select star from a particular table query.
Let's make this a little more advanced by adding a WHERE
clause and say where company equals.
And I'll go to the right and choose
Add under Query Parameters, select my dashboard sheet,
and then click on the cell within the dropdown.
So now my results are going to be
a select star from the Chicago taxi table
where company equals the value in that dropdown.
Let's update my report now.
Let's change the dropdown value, open up
the refresh options also available
from each individual Items Overflow menu,
and then click Refresh All to update this report.
If I change the company again and hit Refresh All again,
we can see the report again changes
based on the new value being passed into the query.
[MUSIC PLAYING]