Check denotes the winner based on which tool has stronger capabilities
Data connections
Having good API connections facilitates efficient data preparation. Both Google Data Studio and Google Sheets have API connections to Google Analytics. Google Data Studio has additional connectors to other Google products such as Adwords, Attribution 360, YouTube Analytics, and Search Console. Additionally, it has connectors to other databases such as MySQL and PostgresSQL.
While Google Sheets does not offer the same range of direct API connections as Data Studio you can download plugins to serve the same purpose, such as Supermetrics.
Verdict: Google Data Studio wins
Data preparation & blending
Once your data sources are connected, additional preparation may be needed to display the metrics as desired. For data preparation, Google Data Studio is much easier to use than Google Sheets. After you connect it to a data source, Google Data Studio presents a special interface, which gives you a preview of the data fields. Here, you can update the field names and data types. Google Sheets does not have capabilities allowing you to change field names before importing the data, and no interface to assist with data blending.
Google Data Studio data preview interface:
However, if data blending is required, Google Sheets is a much better tool. In Google Sheets, you can blend data in a manner similar to how you would do so in Excel (with formulas to join the multiple data sources housed across various tabs).
While the method for blending data in Google Sheet is complex, in Google Data Studio, you cannot do any data blending (joins/unions) at all. Data blending is a functionality often required for analysts.
Verdict: Google Sheets wins
Overall Usability
Google Data Studio offers a very intuitive click, drop, and drag interface. The tool comes with a pre-created blank canvas that you can populate with various charts, text, and shapes by dragging and dropping. Users have the ability to control all elements displayed with the right-rail properties panel. The properties panel has an easy selector for controlling data sources, dimensions, metrics, filters, segments, and formatting, displayed directly in the selected chart. For Google Analytics data sources, the names of the dimension, metrics and segments (default and custom) are intuitive – they match the names that you would typically see from within the Google Analytics interface.
Google Data Studio properties panel:
Google Data Studio also makes it easy to add in some end user interactivity. Date range filters and other filters, such as by device or source, can be added not just at the individual chart level but also at the dashboard level. Thus, an end user would be able to select a custom date range or a pre-existing date range such as past 30 days and all charts in the dashboard will update accordingly. Similarly, a dimension-based filter, such as device, can be added so that the end user can select “desktop” and all charts in the dashboard will update to only display desktop sessions.
Google Data Studio date range filter selection:
Google Sheets was modeled after Excel’s spreadsheets. Its main purpose is not for creating data visualizations, and its usability for dashboarding purposes is less intuitive. All tabs in Google Sheets are simply spreadsheets. In order to make a dashboard, you must create your own canvas by selecting your canvas size, choosing a background color, hiding the gridlines, and deleting all extra columns and rows. Like with Google Data Studio, you are able to easily drag and drop charts.
A drawback to using Google Sheets, is that you cannot easily insert shapes and textboxes. While a drawing functionality exists where you can create a shape or text to be inserted, the inserted shapes are often not sharp especially when resized. The most reliable way to insert rectangular shapes and textboxes is to use cells and merge them into the sizes needed for the shape or textbox.
It’s worth noting, Google Analytics has a direct connection to Google Sheets for building charts, but creating them in Sheets is also less intuitive than in Data Studio. The data needs to first be pulled into a spreadsheet and then the charts are built off the raw data. To pull in the data, the user needs to specify the property, view, dimensions, metrics, filters, and segments in the Report Configuration tab. While there is a right rail interface to create a new report to pull in the dimension and metrics selected by the user, there is no drop down to easily select segments to be applied. To apply a segment, the user needs to find the segment ID (which is different from the segment name) and append it in the Report Configuration tab.
It is possible to build end user interactivity with Google Sheets dashboards, but the capabilities are limited and the process is complicated. A combination of data validation and formulas can be used to create a drop down menu filter for end users to select a specific month or single consistent time period of data (day/week/year) or device type. Back end raw data will need to be formatted accordingly to support the granular view. However, a custom date range selector is not available.
Verdict: Google Data Studio wins
Design Capabilities
Google Data Studio and Google Sheets feature almost the same gallery of graphs but Google Data Studio has one additional holy grail chart —the scorecard. The scorecard is a large metric box designed to display KPIs, perfect for providing end users a quick summary of performance. Below each KPI in the scorecard, a percent change metric can also be displayed (month- over-month or period-over-period change). In Google Data Studio, you can also add default up and down arrows to indicate positive or negative change.
While you can still create a scorecard in in Google Sheets, adding these arrows is difficult. Although the arrows may not be necessary, they make the dashboards appear more professional.
Scorecard Metrics in Google Data Studio
As previously mentioned, while graphs can be easily dragged and placed anywhere in both Google Data Studio and Google Sheets, the placement of shapes and textboxes is more limited for Google Sheets. In Google Sheets, you need to merge existing cells to create shapes and textboxes in the sizes desired. Thus, you can be more creative and flexible with the placement of objects in Google Data Studio.
Verdict: Google Data Studio wins
Calculated Metrics
Many analysts need the ability to create calculated metrics across data sources. In Google Data Studio, you can create calculated metrics within single data sources easily in their metrics detail interface where you can select from existing metrics. You can use complicated formulas such as case statements. However, you cannot create calculated metrics across data sources.
Google Data Studio metrics interface:
In Google Sheets, there is no interface to create calculated metrics, but you can create calculated metrics using formulas that reference cells either from a single data source or across data sources. Many businesses utilize KPI formulas with fields across multiple data sources. For example, a KPI may be the call lead conversion rate which is a calculated metric that takes the sum of call leads (external call data) divided by total sessions (Google Analytics data).
Verdict: Google Sheets wins
Data Refreshes
All Google Studio data sources automatically refresh when users open the report and can also be manually refreshed. Google Sheets’ only direct data source, Google Analytics, does not automatically refresh every time users open the report, but refresh schedules can be set up so that the data refreshes every hour, day, week, or month.
Verdict: Google Data Studio wins
Custom Automation
Google Data Studio does not currently have any custom automation abilities. In contrast, Google Sheets can be supplemented with Google Apps Scripting. Custom scripts can be written so that a PDF copy of the reports gets emailed to a distribution list every month or a PDF copy is saved in Google Drive titled by month.
Verdict: Google Sheets wins
Shareability
Both Google Data Studio and Google Sheets can be shared to any Google account. End users do not need direct access permissions to the original data source. Additionally, both tools have collaborative edit capabilities and can export to PDF. The only difference is that if you have tables displayed in Google Data Studio, the numbers cannot be copied and pasted into Excel for additional analysis, whereas in Google Sheets this is not possible.
Verdict: Google Sheets wins
Recap
In conclusion, both Google Data Studio and Google Sheets each have their own pros and cons. Google Data Studio is a very intuitive tool for creating dashboards. But if you need more in-depth and advanced metrics, Data Studio’s current capabilities will not suffice given that it does not allow you to create calculated metrics across data sources. Using Google Sheets for dashboards with more complex KPIs is still the better tool for visualizations.
However, Google Data Studio is still in beta. It is likely that additional features and capabilities will continue to be released and improved upon. We’ll stay tuned for the updates from Google, and in turn, will keep you updated!