Create beautiful reports using Data Studio

Create beautiful reports using Data Studio


Make sure that you've taken a look at the following tutorial(s) before you continue:

You are going to learn

  • how to create Data Studio reports with beautiful charts
  • how to add BigQuery data source to your reports
  • how to add an interactive filter to your report

Charts and tables

Where would any good bussines be without reports filled with charts and tables ? Showing your boss raw data in the quarterly report could get you fired. Data visualization is a form of communication. By representing data in a visual way we are able to convey information more clearly and efficiently. We take numbers and transform them to dots, lines, bars and pie charts because in this form they are more understandable and usable. For humans it is easier to compare and draw conclusions from information if it is presented visually.

There are many ways to visualize data and many tools and techniques you can use. I would like to present one of them to you – Google’s Data Studio.

1
Create new Data Studio report

Reliability: HighUpdated: 24 January 2018CopiedBookmarkedBookmark removed

First you need to go to Data Studio home. Then crete new report by clicking plus button. There is also Edit button at the top right that you may need to click.

Start new report. Click plus button

In the top left corner you can name your report eg. Air pollution.

2
Add BigQuery data source

Reliability: HighUpdated: 24 January 2018CopiedBookmarkedBookmark removed

Your report will have to take data from somewhere. Data Studio gives you plenty of data source options to choose from. Air pollution report will use results from a query executed using BigQuery.

Query from previous tutorial in this series - BigQuery in action, will be the base of our report. You can find gist with the query here bigquery-air-pollution-caqi-index-openaq.sql

Click Create new data source button and click Authorise button too if it appears.

Create new data source button

 

Name your data source. Change Untitled Data Source in the left top and enter your own name eg. pollutants_daily.

Next you have to choose connector you are going to use. We are going to use BigQuery connector and a table that has been created in earlier tutorial - pollutants_daily.

Select BigQueryMY PROJECTSYour projectair_pollution_caqi_openaq.pollutants_daily and click Connect.

On the next screen you can modify the data source that Data Studio will use to retrieve data from pollutants_daily table.

First thing you should do is change accuracy field representation to percent. This way your report will display accuracy as percentage instead of a float number. Set Aggregation of accuracy field to None.
Data source definition - Accuracy as percentage

Latitude and longitude fields will need to be modified as well. We do not need them in our report so we will disable them. For both latitude and longitude click three dots and choose Disable.

Data source - disabling fields

Instead of having two separate fields for longitude and latitude Data Studio requires one. We are going to add a calculated field to our data source. First click plus button to reveal the form for adding calculated fields.

Plus button for adding calculated fields

Form will open above the field list. Formula section of that form has working autocomplete. In this section you will enter SQL expression that will be used to calculate the value of latlng field.

In Name section enter latlng. Paste the expression from listing below in Formula section and click Create field button.

CONCAT(latitude, ',', longitude)

Your new calculated field is on the list now but it's type is Text. You need to change the type to Geo Latitude,Longitude. Data Studio can now use this field as dimension for the charts.

Fields that can be used as chart dimensions are green and fields that can be used as metrics are blue.

We need two of the fields on the list to be metrics - caqi and sensor_reading.value. Set Aggregation of caqi to Average and Aggregation of sensor_reading.value to Sum.

Before you click Add to report button, compare your settings with the image below.

Final data source field list for our Data Studio report

 

3
Show you data on a world map with Geo map charts

Reliability: HighUpdated: 24 January 2018CopiedBookmarkedBookmark removed

We will add two charts that display air pollution data on a world map. First of them will show CAQI level by country code.

Select Geo map from the tool bar and then select an area on the blank report page. On the right a new pane called Geo map Properties will open with two tabs inside - Data and Style.

Add Geo map to Data Studio report button

In Data tab choose following settings:

  • Data source: pollutants_daily
  • Date Range Dimension: date
  • Dimension: country
  • Metric: caqi
  • Zoom Area: World
  • Date Default Range: Auto

In Style tab change Max colour value to red, Mid colour value to orange, Min colour value to green and Dataless value to grey.

Styling Geo map chart in Data Studio

Your first chart is ready. If you have data in your pollutants_daily table then you should see some countries on the world map marked with a color corresponding to an average CAQI index value in that country.

Geo map chart with average air pollution by country

Second of our Geo map charts is going to show CAQI level by location.  You are going to use exactly the same settings with just one difference. For dimension it will use the calculated field latlng instead of country.

Select Geo map from the tool bar again and then select an area where you want to place the second Geo map chart.

In Data tab choose following settings:

  • Data source: pollutants_daily
  • Date Range Dimension: date
  • Dimension: latlng
  • Metric: caqi
  • Zoom Area: World
  • Date Default Range: Auto

Use the same style settings as you did for the first Geo map.

Now you should see a world map with specific locations marked with circles. Circles are color coded according to the style settings.

Geo map chart with air pollution by location

4
Visualize top pollutant levels on a Bar chart

Reliability: HighUpdated: 24 January 2018CopiedBookmarkedBookmark removed

Which of the locations have the highest pollutant levels ? Let's say that we want to visualize top ten locations with highest air pollution levels along with amount of each pollutant in µg/m³.

We can use Bar chart to do it. For this to work we will have to use both location name and pollutant type as dimensions for the chart. The chart will use values for each pollutant as metrics.

Select Bar chart from the tool bar and then select an area where you want to place new chart.

Add Bar chart to Data Studio report button

In Data tab choose following settings:

  • Data source: pollutants_daily
  • Date Range Dimension: date
  • Dimension
    • sensor_reading.pollutant
    • location
  • Metric: sensor_reading.value
  • Sort and Secondary Sort: sensor_reading.value - Descending
  • Date Default Range: Auto

We also want only to consider locations that have readings for all six pollutant types. Remember that accuracy field ? This is where it comes in handy.

At the bottom of Data tab you will find Filter section with Add a filter plus button. Click Add a filterCreate a filter.

Set your filter to Include records that have accuracy equal to 100 and click Save.

Setting a filter on a bar chart in Data Studio

Filters are a great way to adjust your charts. For example if you wanted only particulate matter readings (PM10 and PM2.5) visible on your chart then you could extent this filter by adding more clauses to it.

Add multiple clauses filter to Bar chart in Data Studio

We are not going to change the style of this chart but I urge you to have a look at the Style tab to find out for yourself how adjustable it is.

This is what your chart might look like.

Bar chart made using Data Studio - Top 10 locations with highest pollutant levels

5
Filtering report input data by country

Reliability: HighUpdated: 24 January 2018CopiedBookmarkedBookmark removed

Data Studio reports offer more than just charts and tables. You can also enhance your report with date and dimensions filtering functionality. Adding a filter is straightforward and similar to adding a chart.

Let's add a filter that will allow us to exclude some countries from our report. Select Filter control from the tool bar and then select an area where you want to place the select box for your filter.

Add filter control to Data Studio report button

In Data tab choose following settings:

  • Data source: pollutants_daily
  • Date Range Dimension: date
  • Dimensioncountry
  • Metric: caqi
  • Order: Dimension, Ascending
  • Date Default Range: Auto

Now adjust the placeholder of your filter's select box. In Data tab's Dimension section hover over the globe button and it will change into edit button. When you click Edit button a form will pop up. In Name section of the form you can adjust the placeholder that will be visible in your filter's select box.

Edit filter control select box button in Data Studio

Data Studio - Filter control - Change select box placeholder

You can also play around with filter's style to make it look more attractive. Final result could look like this:

Filter control in Data Studio report - styled select box with custom placeholder

 Filter control in Data Studio report - after select box is clicked

6
Interactive report on world's air pollution

Reliability: HighUpdated: 24 January 2018CopiedBookmarkedBookmark removed

You can beautify your Data Studio reports and make them look amazing. If you want some ideas on styling then you should check out all the templates in Data Studio gallery.

Here we have created one page report with only one filter. Keep in mind that reports can have multiple pages and filters. Navigation between pages is intuitive and somewhat adjustable.

Remember that you are using BigQuery as your data source so while viewing your report you are querying a table and thus using your data processing quota. Check out Query history on your BigQuery homepage. You will see a whole lot of queries that were generated by your report. Most of them probably did not influence your data processing quota because they used cached results. Results are cached for about 24 hours so only first query of it's type in that day will add to your Bytes Billed.

BigQuery - Processed and Billed Bytes

BigQuery - No Bytes Billed because of cache
Your final air pollution report could look like this:
Data Studio report on air pollition in the world
Good job. You deserve a bottle of Grog. Arrrr !
Success ! Let's open a case of Rum. Arrrr !
New skills gained. Beers all around! Arrrr !
Thanks for reading ! Please
  •  
  •  
  • 1
  •  
  •  
  •  
  •  
  •  

Leave a comment

Your email address will not be published. Required fields are marked *