Google Data Studio – reporting and visualization platform
The 2017 Gartner Magic Quadrant for Business Intelligence and Analytics was released earlier this year and for the second time in a row we have seen a strong presence of the visualization tools at the top of the leader board. It’s quite evident that the traditional BI approach based on enterprise reporting is slowly making its way towards a more business oriented analytics approach. Whilst, the enterprise reporting primarily focussed on metadata, automating reports, scheduling and its distribution, the modern BI also takes the analytics approach into consideration to look at customer insights, predictive analytics, prescriptive analytics and data mining.
Google Data Studio is one of the latest data analytics visualization and reporting tool to join this league. Currently in its beta version, the tool offers some of the advanced features to build dashboards and reports for the businesses to analyse their data and facilitate decision making. If you are a seasoned data analytics professional or a beginner this reporting and visualization platform has a lot to offer to build stunning dashboards in no time. We tested some of the features and compared it with the other leading visualization tools available in the market. Let’s look at some of them:
Google Data Studio is available at no licence cost to the users with the ability to create unlimited number of reports in your account. It’s a web based tool accessible through the portal with no installation required. The reports can be shared by sending a link to the users email address by providing them with view or edit access.
To get started, login to your google account and go to http://datastudio.google.com
The data studio home page offers easy navigation to the sample templates, tutorials and blank sheet to start building a new report.
Google Data Studio in its current version offers connectivity to Google BigQuery, YouTube Analytics, Cloud SQL, Google Sheets, AdWords, MySQL, PostgreSQL or a sample file on your local system. If your enterprise data resides on other platforms such as SAP ECC, HANA , AWS, SQL Server, Oracle, IBM DB2 or any RDBMS then it could be a challenge connecting these sources as there is no direct connector available at this time. Also, you are required to transform the data outside the Data Studio platform and export it to BigQuery or Google Datasheets.
The first step before you start building your new report is to define a data source. Click on the Data Sources and select the (+) option to add a new connection. The list of available connectors populates on the left hand side.
Alternatively, you can choose the sample data sources available list for reporting.
In this example we are connecting to a transaction table on Google BigQuery with close to 3 million records.
After connecting to the data source Data Studio determines the fields and assigns the data types based on the default aggregation. You may choose to change the aggregation based on the field type from the aggregation dropdown. The field names are picked up from the dataset and you can change them by simply selecting the field and giving it a logical name based on the business requirements.
Logical field name
Click on the (+) icon to create a new calculated field and enter the name for the field. The formula bar allows you to autocomplete the calculation recommending the best match from the available list of fields from the data source. Use the formula help to see the different calculations that can be included in your report.
Simple calculations such as Count and Count Distinct can be added directly on top of the existing fields. The datatype format next to the fields allows you to change the default format of that field. You can change a number field to a percentage or a date field to a format you wish to display on your report. Custom formatting options are not available at this time however you can create calculated fields to build some of them. The geographic dimensions like State, City, Region in your dataset can be assigned to Geo datatype whereas Amount, Total Cost and Price can be assigned to a currency for reporting.
Google Data Studio doesn’t offer the advanced features like data blending or data modelling within the tool which could be seen as a limitation, but you can include multiple connections from one or more different datasets within the same report. You can also build custom SQL and extract the data in the format you wish to present in your report.
After defining the data source connection click on ‘Create Report’ to start building your visualization.
Adjust the Layout and Theme of the report as per your reporting requirements. The canvas size can be set to a standard layout or you can also choose a custom layout which suits best for you. We used a custom layout of 1368px : 735px for this report. The primary background colour of the report can be customized from the theme selector or simply select one of the standard themes available – Simple, Simple Dark
New pages can be added to the dashboard by simply clicking on the Page Control option on the left hand top corner of the canvas. If you have worked on Tableau or SAP Lumira before and like to build stories in the dashboard then this feature works very similar to the other tools. The Duplicate page option is quite handy if you are designing a standard layout for all the report pages. Start with defining the layout, report style, background theme colour, page header, footer and your company logo on the first page before commencing your work and then use it on all the pages in your dashboard.
The chart types available for your visualization are:
1. Time series
2. Bar chart
3. Combo chart
4. Pie chart
6. Geo maps
8. Scatter Plot chart
9. Bullet chart
10. Area chart
Select one of the chart options and add it to the report. Data Studio automatically picks up a dimension and a measure from the available list and displays on the chart. Select the dimension you wish to add using Dimension picker. You can add more than one dimension or measures in the chart depending on how you wish to structure the data and the type of chart you select. The style menu provides more options to customize the overall look and feel of the chart.
The Time series chart is a line chart which has fixed time dimension as one of the axis. It is possible that you may encounter a problem first time throwing ‘Invalid Dimension’ warning if you do not have a time dimension defined in your dataset or atleast if it’s not defined in the format which Data Studio expects. You can also add a Trend line to your chart from a range of Linear, Polynomial or Exponential options to choose from the style menu.
The Geo maps can be included only if one or more dimensions in the data set are attributes of geographic location – Country, Region, City. Select the appropriate dimension and change the data type to Region in the connection settings and then add the geo map to your page. Make sure to select the correct zoom area Country to populate the data. The region on the map area is highlighted based on the measure selected.
The Pie chart has the standard features of displaying the data as percentage, absolute values or just the label. You can also change the layout to display it as a doughnut chart from the style menu.
Scoreboard is another smart feature available in the tool which allows the users to add summarized data to your dashboard. Start with setting a date range for the desired measure and enable the comparison metric from the style menu. The comparison metric compares the data with the previous period and displays it in the scoreboard below the actual value. This is particularly useful if you are want to display year over year comparison in your dashboard.
You can also display data in a simple table format in your dashboard. Click to add the table in the page layout and select the dimensions/measures to display. The columnar table is basic and doesn’t allow many customization options.
A cross tab table not available at this time but it would have been helpful to have this as option if you were planning to build some advanced dashboards.
The other chart types such as Bullet chart, Area chart and the Scatter Plot chart offer all standard features required for the analysis.
One of the most powerful feature of any visualization or a reporting tool is its ability to interact with the data. This includes data sorting, ranking, drill down or using filters to achieve the desired results. We will have a close look at some of these functions:
Data can be sorted in ascending or descending order based on the dimensions/measures selected in the chart. Custom sort option is not available in this version which could have been very useful for data analysis in my opinion.
Ranking Top N or Bottom N values is not directly available as a function, but there is an easier workaround I found to get around this one. Sort your data based on the measures in the chart and in the style menu define the number of bars you wish to see in the dashboard. The measures can be sorted in ascending or descending order to get the Top N and Bottom N values.
Creating hierarchies based on any standard dimension within the dataset is not available as an option at this time. This restricts the ability to slice and dice the data for analysis.
Google Data Studio has different options to set the filters in the report layout. The report filter acts as a Global filter which allows you to restrict the data based on certain pre-defined conditions.
The Page filter is similar to a report filter which restricts the data on the selected page layout. To add a Report filter or a Page filter right click on the page and select the appropriate settings
The data displayed in each chart component can be restricted by adding a filter on the chart itself. This can be useful if you do not wish to show the NULL values or blanks.
Date Range component in Data Studio is a type of filter which restricts the data based on the data range. There are different pre-defined options to select the date range based on the frequency of the data refresh.
Filter Control component allows to filter the data based on the dimension selected. Sort the data in ascending or descending format and select the number of list of values to be displayed in the filter. One of the most important features I found was the filter control allows cascading data based on the list of values selected.
Dashboard Layout 1:
Dashboard Layout 2:
The report can be viewed only from the Google Data Studio portal. The output from each component on the report can be exported to CSV file format but the final layout of the report cannot be exported directly to any file format. This could be an issue if you want publish the dashboard on a presentation or publish it to a wider audience in your organization network.
To print the dashboard select the ‘Print’ option from the browser as Data Studio platform doesn’t provide it as a default option. I mentioned earlier that the report output cannot be exported directly to any file format, so I tried the indirect way. If you have the PDF converter utility installed on your desktop then you can print the report and save it in PDF format, just the way I did it as a workaround. The only caveat here, you can’t print all the pages on the report in one go.
Viewing dashboard on Mobile device:
Android and iOS users can view the reports and dashboards on the go if they have google account configured on their mobile device. Start with logging into your google account and open data studio from the mobile device browser. The Data Studio report automatically adjusts within the browser based on the orientation of the device. The filter dropdown and the list of values appear exactly in the same format like they would appear on a desktop browser. It actually shows up pretty well on the screen.
The performance of the report depends on the underlying data structure and the overall granularity of the information presented. Data Studio retrieves the information from the query cache every time it loads the report. This improves the overall performance and helps to load the report much faster as compared to running a query directly from the source. If you are using BigQuery as a source then this feature will definitely help to keep the cost low
The reports can be shared with the users by sending them a link to their email address. You can share the link with multiple people within the team and assign them View or Edit access. Multiple users can access the same report at the same time with edit rights in a shared working environment. You can also create a new data source connection which can be shared with the other users within the team. This allows the other users to create new reports based on the shared data source.
Reports shared with the users can be saved in a folder structure on Google shared drive. Users with access to the shared drive will have access to all the reports available in that drive.
Google has built its reporting platform and included the features that most of the leading tools have to offer. The Data Studio reporting solution offers seamless integration with the Google Analytics 360 platform and other google data connectors. After spending some time with the tool we were able to build a dashboard and achieve most of the results we targeted for, if not all of them. If you rely heavily on Google marketing and advertising content then this can be a great tool for your analysis and reporting.
Start exploring the possibilities to churn your enterprise data with this reporting tool and I am sure you would have as much fun as we did. Share your thoughts and let us know what you think.
For more information on Analytics and BI please visit us at https://forefrontanalytics.com.au
BI Analytics Consultant