Have you ever struggled to build custom dashboards
fast? We have. It appeared that Google Data Studio is a great solution to represent data. Letâs see how it works. Hi! My name is Sergey and I am a Product Expert
here at Railsware. The dashboard market is really fragmented and there are plenty of different solutions. Usually you have to choose between heavy BI
tools which allow you to do everything, but the licenses can easily cost you 50 thousand
US dollars. Or simpler solutions which are quite limited
and canât cover all of your needs. Thankfully, there are easy to use solutions
available as well. And Google Data Studio is a great example. This is a platform that allows you to create
and share beautiful reports fast and smart. If you run a small or medium size business
or a department in a big corporation, this video is for you. Letâs go and build something great together. This is a Google Data Studio tutorial for
beginners. Today we will cover the following topics:
Data Studio environment setup How to create and manage Data Sources
How to build and share Reports As an example, we will build a Recruitment
Dashboard from scratch. You will learn:
How to connect GSheets to a report What are the Dimensions, Metrics and aggregation
functions How to create tables, charts, and scorecards
How to build and manage filters Also weâll review styling basics and the
way you can share your report See the full agenda in the video description
below. You can switch to a section you need using
time stamps. Donât forget to ask your questions in comments! Data Studio is a part of Google Marketing
Platform and it has its own ecosystem. To get started just visit https://datastudio.google.com
and sign-in using your google account. If itâs your first visit you will need to
set up Data Studio. Press get started
Agree to the terms and conditions Leave the company name blank if you use the
platform for personal needs Sign up for different news subscription
If you donât have a firm in Europe just say NO
If you do have business in EU - press Yes and fill in the needed info. It just takes longer There are four major concepts in Data Studio
- Data Source, Connector, Report and Explorer. Data Sources and Reports have many to many
relationships. This means that one report can use multiple
sources of data, while the same data source can be connected to many reports. Today we are going to play with simple configuration
- one report will use only one data source. Please keep in mind that changes in data source
apply to all the connected reports. Itâs not that evident from the UX perspective. Connector is an interface of receiving data
from various platforms (like BigQuery, Youtube, Google Analytics, Facebook ads, Spreadsheets,
etc.). While Data Source is a blueprint or a wrapper
of a connector that you can modify. There are connectors provided by Google and
created by the community. If you need a custom one, you can either create
it yourself, or transfer data to the data storage with an existing connector to it. Explorer is a âwhat you see is what you
getâ type of Report, which is in beta stage for now. We will review it in our next videos. When you join Data Studio, you can easily
navigate between Reports, Data Sources and Explorers using the top menu. You get a few samples of dashboards; you can
open them and see what are the possibilities to build your custom report. Today we will build a Recruitment Dashboard
using data from our applicant tracking system. The first step is to prepare our data set. With a well organized data you can build a
representative dashboard fast and easy. We use Pipedrive as our ATS(Applicants tracking
system). But there is no Data Studio connector for
this tool yet. This is why we pull data from Pipedrive to
GSheets with the help of Coupler.io - a service that securely transfers data between platforms. And we will use GSheets data source to build
our recruitment dashboard. Not to spend too much time we have already
prepared needed file. With coupler you can select how often you
would like your data to be updated (every hour, day, or by request). In our case, daily update is what we need. All new entries will appear on the list after
the next daily import. We pull the data from Pipedrive to Applicants
tab. And we use Applicants Filtered tab to filter
our data we will use for the Dashboard. If you want your reports to work faster we
recommend to minimize your data sets. To avoid issues with data, follow a few simple
rules when you shape your dataset in GSheets: Donât use cell merge
Keep each data record in a single row Use the first row as a header
Each column should contain the same data type and format
Go back to Data Studio and create a Data Source. Pick a GSheets connector. It will offer you a list of Google Sheets
shared with or owned by you. We select Pipedrive Data Stream Demo and the
Applicants Filtered worksheet. Click âConnectâ. If everything is successfully connected, you
will see the list of column headers from your spreadsheet. Btw in the video description you will find
a link to a Google Sheet Pipedrive Data Stream Demo. So you can repeat all the flow Iâm gonna
show you today. Metrics and dimensions are the building blocks
of your reports. Metrics are marked blue, dimensions - green. Metrics are the numbers contained in your
data. In our case, it is Salary and Applicant ID. Dimensions are attributes of your data. Those are strings, dates, URLs, locations,
etc. Usually, you donât have to change types
of entries as Data Studio detects those correctly. However, in our case country was detected
as string. So we change it to âCountryâ. Also we can spot that Salary is detected as
the number - letâs change it to Currency USD. At this stage you can also add calculated
fields. We will discuss those in our next videos. Name this datasource âApplicants Stream
Demoâ. Once done, create a report. Select a name for your report. Letâs call it âRecruitment Dashboard Demoâ. Here we have our working canvas, properties
panel, and a toolbox. On the top you can find control panel with
refresh data, share report, and change between view and edit modes. You can create multiple pages for your reports
by adding a new page. Use the top left control to switch between
pages. You can duplicate and hide pages in the view
mode. Property panel reflects the properties of
objects. As we donât have any objects selected, we
can see the layout and theme settings for the whole report. Select the header visibility. It is pre-set to âInitially hiddenâ. If you go to the view mode and want to use
the control panel, you need to hover over it. If the header is pre-set to âAlways showâ,
it will be visible at any time. This is a better option for editing. Once done, switch back to âInitially hiddenâ. In the view mode, you can see the list of
your pages in the top left corner. Switch between pages using arrows, or click
in the center to select from the dropdown list. You can also select the navigation to be represented
on the left so that you can see the list of pages like this. You can also change canvas size for your convenience. Letâs create a table with candidates list,
their locations, and salary data. Add a simple table by clicking âadd a chartâ
in the menu and selecting âsimple tableâ. Data Studio is trying to shape a basic table
out of random columns. We need to select the correct ones now. Letâs say we want to see the list of candidatesâ
with their name, position, application date, responsible recruiter, location and stage
of the process. Letâs start. In fact, all the fields weâve mentioned
(except for salaries) are Dimensions. So we work with the dimensions field in the
properties panel. Use âAdd dimensionsâ to see the list of
options. We pull Application Date, Applicant Name,
Position, Stage Name and Owner Name to identify the responsible recruiter. The OwnerName appeared without a space + Itâs
better to call it âRecruiter Nameâ as we need to keep a consistency in Data Source
naming Letâs edit it. Go back to the spreadsheet, find the column,
and rename it to âRecruiter Nameâ. If you changed names of columns in the original
file, you need to update the data source. In Data Studio, click âEdit data sourceâ
in the Properties Panel. Or Resource -> Manage added data sources -> choose
the needed data source -> click edit. You can see a button âRefresh fieldsâ. It shows recently added fields and the missing
fields. Click apply. And our table is broken now. This happened because we refer to the Dimension
âOwnerNameâ. Data Studio is not yet that smart to replace
one dimension with another. So we need to remove missing dimensions and
add the new ones. Data Studio highlights invalid dimensions. Remove them. Now the table is fixed. Add âRecruiter Nameâ. Edit names without changing Data Source
Adjust the size of your table and the width of every column. Click on any vertical border and drag it. But itâs easier to double click on any vertical
border, and Data Studio will adjust the column size to fit text automatically. Change the order of columns by dragging and
dropping dimensions in the properties panel. As you can see, data studio have automatically
added one metric. You might be confused by seeing SUM applied
for Applicant ID, as it doesnât have a lot of sense. We change Applicant ID to Salary Expectations. It still has the SUM stated. This is an aggregation function. We have only 1 salary record for each row. So the SUM and AVERAGE are equal, just as
MEDIAN, MAXIMUM, and MINIMUM values. You can choose between these options in case
you work with aggregational tables. We will show how it works a bit later today. For now, we select SUM here. You might have only SUM stated in the list
and you canât use other aggregational function. This is because fields editing in reports
might be disabled. If you have such a problem, edit data source
in the properties panel, and turn on this option. Now you should see all the functions in the
list. You can select how many rows you would like
to see per page. You can see a summary row, but it doesnât
make sense in our case. You can Sort by metrics or by dimensions. Click on the property and select the dimension
to sort by. We will use application date. We want to see the newest applicants first,
so letâs select descending order. Letâs also select a secondary sorting dimension. We want to also sort by position. Now we can see the applicants sorted by position
and by date. You might want to change the name of columns
right in Data Studio for a specific table. Go to dimensions -> select dimension to edit
-> change âSalary expectationsâ to âSalaryâ, âRecruiter Nameâ to âRecruiterâ. Letâs switch to the view mode. Click 3 dots, here you can sort the table
as you want, as well as export this table. To remove row numbers go to Style -> Table
body -> Turn off row numbers. In Style, you can also remove headers, wrap
text, change text styles, make headers bigger, change alignment per each column, and adjust
styles the way you like. Now we have a simple table representing our
data. Letâs create a salary aggregation table
with more extensive calculations to calculate the minimum, maximum, and median salaries
per position. First, make the current table a bit smaller. Add one more chart -> table with heatmap. As always, Data Studio takes random values
to represent here. We want to see stats per position. Select Position in Dimensions. Remove dimensions you donât need. Here is a simple heatmap. Now it represents sums of salaries that make
no sense for us. Letâs start with Average. Rename the column to âAverageâ and choose
the proper calculation. Now we need to calculate Median. For this, add one more metric - it will also
be a salary, but represented in a new column. We select median function and rename metric
to âMedianâ. Now, we select salary metric again -> select
minimum function -> and rename it as well. We do the same for Maximum. Adjust the table size. We also want to see the number of applicants
per each position. Select Application ID in metrics and (->) count
function. Now, we can see how many applicants we had
per each position. Rename the metric to âApplicants Countâ. Sort by count. Now letâs move the count column closer to
the position name and sort by this metric I personally donât like all those colors. So letâs apply a bit of styling. Go to Style. Here you can see that each column has its
own color. I choose this color for better readablility,
and select it for all columns. Keep a different color for count (as it doesnât
calculate salary metrics, but the number of applicants). Adjust the size of each column to make them
more readable. And here are our salary stats! The Calculations table is quite useful, compact,
and easy to work. But how to work with the 2nd table? Itâs pretty huge and unhandy. We can use filters to navigate through the
information. There are different types of filters:
Those applied locally to a single table/chart or any other control that represents data
And those applied to the whole page. Letâs have a look at these first. In the top menu select the Date range. It can be useful to get stats for a Ńertain
period of time (this month, quarter, year, or a custom timeframe). Click on the date range and drop it on our
page. By default, it takes auto date range that
we can use. Letâs create a default date range âthis
quarterâ and press apply. This filter has been applied to both tables. We can see less positions listed. In the Data table we can see that in the last
quarter 354 applicants applied. Now, letâs see how it looks in the view
mode. You can select a different time frame (this
year) - and apply. Both tables have been updated. You can use a preset or a specific start and
end date. Now, click filter control and drop it to our
board. It automatically picks applicant ID (that
doesnât make sense). I would like to change it to country. Select it in the Dimensions field. The filter has also showed a default SUM. We are interested in COUNT of applicants per
country. We select Applicant ID metric, Count function,
and now we can see the number of applicants. Letâs use the country filter. Say, you want to see candidates from Singapore
only. Hover over it and click âOnlyâ. Now you can see all candidates from Singapore
and the stats updated according to the specific country and timeframe (remember that both
filters work together!). Letâs add one more filter to see how filters
work together. We change dimension to Recruiter Name, rename
it, select COUNT right away in metrics. Go to the view mode, and see the Recruiter
filter. You can see that Leslie Winkle had this number
of applicants. If you apply Poland, youâll see that the
total number of applicants per recruiter has changed. So now you can have an intersection of a specific
Recruiter and the country Poland. You can choose a specific timeframe, and see
that the number of Polish applicants processed by Leslie Winkle has changed as well. Imagine you need to see applicants who are
now at the Profile Review stage. Select the table, go to the properties panel,
select âadd a filterâ, name it âProfile Review Stageâ -> pick âIncludeâ -> select
dimension âStageâ -> equal to -> âProfile reviewâ -> Save. Keep in mind that this filter is not applied
to any other charts and tables, it works only for this table. Use the OR logic and combine 2 or more values
to match. Or switch to âinâ, and write a list of
options separated by commas. You can use the AND operator as well. You can make filter interaction even cooler. Filter the whole page by making a certain
table behave as a filter. It makes sense to use this Calculations table
as a filter. Select the table, scroll down in the properties
panel, and tick âapply filterâ, go to the view mode, now click on each position
in the aggregation table, and youâll see that the second table changed. It works together with other filters we have
just set up for the whole page. I like when things look cool, so letâs add
styling. Add a rectangle area, place it in the top
to mark the filters area, send to back. To make filters visible - color the text white
and remove a border shadow. Now it looks fancier. We can select multiple properties and change
style for all of them at once. You can sort information by specific columns
by clicking on the headers. To get back to default, click on the arrow
up here. Letâs make our report even more informative. Clone this page. Go to Page -> Duplicate page. Letâs rename the first page to âCandidatesâ,
and the second one to âGeographyâ. In the Geography page, remove the table with
data and add a chart -> Geomap. Adjust the size of the map. You can see that Data Studio has correctly
picked the dimension - country (as we identified it from the very beginning). Select the COUNT function for Applicant ID
metric. Now, we can see countries highlighted according
to the number of applications received in a selected timeframe. Try to use filters. Select âUnited States onlyâ. And youâll see the US highlighted only. You can hover over each country and see the
metrics. As for any other table or chart, all the filters
apply in a similar way. If you select âfrontend developerâ, you
will see countries where we got applications for this position. Apply filter on map, click on the US, and
youâll see stats only for the US. Click again to get back to all stats. You can use zoom area (World, Continent, or
Subcontinents). For example we can select America and see
America only. Letâs go to the list of pages, duplicate
Geography page, and rename it to âCharts and score cardsâ. Here we remove map, but leave the calculations
table. Add a chart -> Scorecard
Scorecards show one aggregated metric based on the filtered results. Letâs show the number of applicants on the
scorecard, and compare it to the previous period. Select Applicant ID, COUNT function, rename
to âApplicantsâ. You can apply a comparison date range (letâs
choose for previous period). If we have this month selected, the comparison
range will be the previous month. Letâs filter this year, and weâll see
that we had more applicants compared to the previous year. Letâs tune the style a bit. If you donât want to see percents, but absolute
changes - go to Style and tick absolute change. Align in the middle. Change the color of comparison numbers. I like when scorecards have borders, so I
use colors and border styles to change them. Now we have a beautiful scorecard. Letâs see the proportion of applications
per position from the total number of applications. Letâs use the Treemap. Select Position in Dimensions and COUNT for
Applicant ID in Metrics. Letâs add a 2nd Dimension - Recruiter Name,
and turn on âdrill downâ option. Select 1 level to show. Go to the view mode -> Select Data Analyst,
and click the arrow down to drill down the number of applications processed by each Recruiter. We can see that most of the Data Analyst applications
were processed by Leonard. Now, letâs use a bar chart to see how many
new applications we received per day. Choose the bar chart. Dimension - Application Date. Metrics - Applicant ID with COUNT function. Sort by Application Date (select ascending
order). Even if we have a larger date range selected,
the chart shows only 10 bars. To change this, go to Style -> Amount of bars
-> clear number to see ALL the bars for the selected time range. Back to Data, and letâs select a breakdown
dimension - Position. Now, we have a separate bar for each position
per date. However, we want all the bars to be stacked. Go to Styles and tick stacked bars. Now, all applications per specific date are
divided by position and appear in a single bar. Letâs select a shorter time range - for
example 1 month. When you hover over a field - you can see
the number of applications per position. Letâs adjust the size of this chart. In the view mode, when you click on the position,
the bars with applications for this position will get marked. We can also add a simple line chart to see
the overall number of applicants per day. Donât forget to change the naming of dimensions! Select Application Date in Dimensions and
Applicant ID with COUNT function in Metrics. When you select a filter, it will be applied
to all the graphs, tables, and scorecards. Letâs take a quick look at the objects that
can make your reports even more impressive. Add a new page. In Data Studio, you can type simple text and
stylize it You can Insert images (logo)
Or you can embed a web page right in your report (Visit railsware.com). You can even scroll the website right in Data
Studio. Change the Theme to Simple Dark. Or get back to the light theme. Data Studio has many more helpful features
to use. Today we showed the very basics. For more tutorials about Data Studio subscribe
to our channel. If you found this video helpful - press like. Visit railsware.com to learn more about our
services and products. We help startups to build great data-driven
software solutions. This is all for today. Thank you for your attention and see you next
time!