Simple dataviz with N1QL and Google Sheets.

Do whatever it takes to present the data to aid analysis and thinking.  — Edward Tufte

How do you create graphs like these if you don’t already have some ready-made cool dataviz tool?

You can run queries to wrangle the data to get the results. Often you want to present the data visually in pie charts, bubble charts, histograms and line graphs. If you have Tableau, Cognos, etc it’s easier to visualize. Otherwise, you’ll have to copy the results into google sheets or excel.   Cut and paste of the results to google sheets is difficult, due to formatting, header-row issues, etc.  However, Couchbase Query Workbench makes it easy. Next to the query results, we have a copy icon that copies the output into a tabular form that you can paste into spreadsheets such as Google Sheets, Excel, etc.  This facility is available in both query workbench and analytics query workbench.

Let’s look at some sample data and charts.

1. Calculate the types of documents and their count.

Here’s the query on the travel-sample dataset shipped with Couchbase.  Click on the image to see the GIF animation.

Here’s the piechart created for the data. This can be easily customized for color, data display, various labels, and legends.

Click on the image to see the GIF animation on how to get the results to google sheets to get the charts in a second.

2. Task:  Find the top 10 cities with the most number of hotels.

Results:

The only customization to the histogram here is to add the data labels to show the actual number of hotels.

2. Task:  Find the top 5 cities with the most number of hotels with “gardens” in the review

Simply choose the “Doughnut piechart” and add the labels.

6. Bubble charts require you to create the percentage value for each row. The window function RATIO_TO_REPORT() help you to do that easily.  Once you have your query, creating a bubble

Bubble chart showing avg rating by location, etc. The size of the bubble shows the %ge of hotels represented within the respective country.

This animated GIF shows how to create this chart.

7. Creating geo charts is pretty easy in google charts since it recognizes the countries and cities. You don’t need to deal with latlongs.

With the data above, simply paste into a google sheet and then create a geo chart. 

8. You can also create a geo chart with markers (proportional bubbles), again, using the RATIO_TO_REPORT() window functions.

You can also customize the charts to the region, for example, the United States.

Finally, the query to generate the first graphic of this blog: