Google Sheets Query function: The Most Powerful Function in Google Sheets

The Google Sheets Query function is the most powerful and versatile function in Google Sheets.

It allows you to use data commands to manipulate your data in Google Sheets, and it’s incredibly versatile and powerful.

This single function does the job of many other functions and can replicate most of the functionality of pivot tables.

This video is lesson 14 of 30 from my free Google Sheets course: Advanced Formulas 30 Day Challenge

Continue reading Google Sheets Query function: The Most Powerful Function in Google Sheets

6 advanced techniques to master in Google Data Studio

Google launched a new business intelligence tool called Data Studio in May 2016. It’s a really smart reporting tool for quickly creating powerful, stunning dashboards from multiple Google data sources.

It’s a great option for small/medium businesses already using Google tools, who want to build bespoke dashboards for that 40,000ft view of their business.

Google Data Studio Example Reports

Here are two example reports for a mid-size website (~500k pageviews a month).

Firstly, a mobile performance dashboard:

Data Studio Mobile Performance Dashboard Continue reading 6 advanced techniques to master in Google Data Studio

Introduction to Google Data Studio: a free BI tool for small businesses

Regular readers will know of my enthusiasm for building dashboards, especially using Google apps (like this one or this how-to article).

So I was super excited in May of this year (2016) when Google launched Data Studio, a free data visualization and dashboard tool to compete against incumbent dashboard vendors Microsoft PowerBI, Tableau and Qlickview.

Here, I’m excited to share my initial impressions and show you some of the basics steps to build dashboard reports using this tool.

I’ve been using it these past few days to build several different test dashboards.

First impressions: I love it. It’s simple and intuitive. Impressive.

Of course, since it’s a beta launch and it’s a nascent product, there are still areas where it’s lacking sufficient depth or flexibility and it’s a little buggy in places (see discussion below) but overall it’s a significant entrance into the Business Intelligence world for Small and Medium Businesses.

For Enterprise businesses, Google has Data Studio 360 – the full-fat, paid platform.

This article focusses on the free Data Studio version.

Data Studio: The smart way to build dashboards and reports with your Google data

So, another data analysis tool? What’s the value proposition here then?

The premise is that you can connect all of your disparate business data sources (only Google services at the moment but there are other non-Google data connections coming soon) and easily build beautiful, interactive dashboard reports to display that information. And all atop Google’s super-reliable, powerful, scalable architecture. Plus, as with so many Google products, it’s free.

It’s a simple drag-and-drop process to add charts and build reports, and it doesn’t require any knowledge of coding. That makes it super quick to create and modify reports. There’s a boat-load of data and formatting options so you can customize reports to match your needs.

Example: Social Media Reporting Dashboard with Google Data Studio

Want to see what this tool is really about? Heck yeah, I thought so!

Well there’s no better way to do this than seeing a real dashboard with real data.

So I’ll show you a dashboard for reporting social media referral traffic of a mid-size website (~500k pageviews a month). I’ll show some of the steps below and discuss how easy it is.


Great, well here’s our dashboard:

Data Studio Social Referral Dashboard
Data Studio Social Referral Dashboard – Page 1
Data Studio Social Referral Traffic Dashboard - Page 2
Data Studio Social Referral Traffic Dashboard – Page 2

I’m not going to go through every step in detail, instead I’ll mention a few of the main steps and key points to keep in mind.

There are three steps to using Data Studio: 1) connecting to a data source, 2) creating visualizations in a dashboard, and 3) sharing your finished creation.

Step 1: Setting up Data Studio and connecting to a data source

Start by signing up or logging in to Data Studio here:

You’ll be taken to your home page, showing a bunch of example Google templates (well worth a look) and any of your own dashboards:

Data Studio homepage

Clicking the big blue plus in the bottom right of the home page creates a new dashboard from where you can connect to different Google data sources:

Data Studio data connectors
Data Studio data connectors

I’ve chosen to connect to Google Analytics and then to a specific web property, as shown in the following image:

Data Studio with Google Analytics

This imports all of the data into my report and makes it available for charting or displaying on my dashboard. I discuss these steps in more detail in the video at the beginning of this post.

Step 2: Creating visualizations and creating a dashboard

In the control bar along the top of the window, there are 9 chart types available (line, bar, pie, table, geographic, scorecard, scatter plot, bullet chart and area chart) and the ability to add images, text, rectangles and circle shapes. Plus there are two filters available: date and a general “type” filter.

Data Studio Control Bar

Adding charts is as simple as selecting the one you want in the toolbar, then creating a container for it somewhere on your dashboard canvas. Google will then build the chart, with some default data (which you can easily modify). The following GIF shows this step, along with changing the data being displayed and formatting the chart:

Adding chart to data studio

I’d encourage you to play around with the tool for a while at first, adding and deleting different charts to get a feel for what they look like and what sort of data they can display. There’s more info in the video at the start of this post.

For the Social Media dashboard above, I’ve made use of line charts, tables, scorecards and a geographic map. I’ve added a filter to the data source to restrict the data to show Social visits only:

Data filter

Once you’ve finished building, switch to view mode to lock everything down and remove the gridlines. The filters still work in this view mode.

Switching between editing and viewing mode is done by toggling the blue toggle switch in the top R corner of the window, as shown in the following GIF:

Editing and viewing in Data Studio

Step 3: Sharing your dashboard

As a final step, you’ll probably want to share your dashboard with colleagues, clients or friends. Click the big Share button in the top R corner of the screen to bring up the share menu pane.

Here, you can enter email addresses of people you want to share the dashboard with or, as shown in the following image, copy the shareable link and send that to people, share on social media or your website. In both cases you can control whether others will have editing access or see the dashboard as view-only.

Data Studio sharing

What I really liked about Data Studio

It’s flexible and can connect to multiple different data sources.

It’s super easy to create charts. Without too much effort you can produce really professional-looking, slick dashboards.

It’s really quick to create reports.

There’s a huge level of customization, especially with the Google Analytics data connection.

Multiple pages allow you to create a hierarchy of dashboards of increasing levels of detail/complexity, to tell a story.

Best of all, the data range and filter range controls are very easy to implement but incredibly powerful in how they work.

And of course, like many Google products, it’s free!

Areas for improvement imho

It would be awesome to have object guidelines show up when moving objects around on the dashboard (e.g. photo editing software Pixelmator does this very well), to make it easier to line them up.

On a similar note, snapping to a grid to help line up objects would be helpful. Provided it could be toggled on/off of course!

More data connections, for example to SQL databases. This is coming soon…

I’d love to see more granular control over the chart details, for example formatting of the axes and working with data labels. The range of options is a great step in that direction and you can already make beautiful looking charts but this would take it to the next level.

I could not select multiple items by holding down the Ctrl (or Cmd) key and selecting, which struck me as odd. You can select multiple objects by dragging and holding the cursor across them however.

When I tried sharing, I was prompted to log into Google despite choosing the option to share with anyone. So, it seems that non-Google users can not view these dashboards at the moment.

When in Full Screen display mode, the page buttons are hidden and barely accessible, making it difficult to navigate through the dashboard.

Closing thoughts

If you’re invested in the Google ecosystem, work with data, especially Google Analytics, then I’d encourage you to give it a try. I’m sure Google will invest in this product and build out the features and data connection options. It’ll be interesting to see where it goes and how it competes with Microsoft’s PowerBI and other vendor offerings.

Building a dynamic dashboard for a 3-day digital flash sale

This time last year I worked with The Write Life during their three-day sale of The Writer’s Bundle, a collection of digital products for writers. I built a dynamic dashboard using Google spreadsheets so the team could visually monitor progress throughout the sale.

This year The Writer’s Bundle 2015 was bigger and better than ever, so I wanted to create an even more useful dashboard for the team. The dashboard was a key tool for the team, to monitor both overall and individual sales channel performance, as well as to be a motivating force by giving everyone a visual sense of progress.

Continue reading Building a dynamic dashboard for a 3-day digital flash sale

Creating a custom Google Analytics report in a Google spreadsheet

Continuing my recent infatuation with dashboards in Google spreadsheets, I’ve been playing around with the Google Analytics Add-on to build a custom web analytics dashboard in a Google spreadsheet.

The idea originated from discussions with my wife about the tedium of preparing month-end website performance reports for her content management clients. This dashboard was a solution to streamline the monthly web reporting and offer insights that might otherwise be missed. Rather than having to visit Google Analytics for each website in turn and click around gathering the necessary data, this pulls it all together in one place.

Continue reading Creating a custom Google Analytics report in a Google spreadsheet