Google Data Studio – The MySQL connector (Part 2)

data studio dashboard
In this second part of the MySQL Connector tutorial for Google Data Studio, we will integrate new advanced charts in our dashboard. The final sales report is therefore expected to provide a complete analysis concerning the given company. Eventually, we will briefly introduce the phpMyAdmin interface to access our MySQL databases.

Share

Reading time: 6 minutes

About last time…

Before starting with the new tasks, we should recall what we did in part 1. The overall objective of this tutorial is to understand and integrate the MySQL connector in our Google Data Studio reports, focusing on the different SQL queries. As a case study, we are considering a retailer of scale models of classic cars (https://www.mysqltutorial.org/mysql-sample-database.aspx). Now, it’s time to play a little bit more with our data!

Customers origin

Things get more interesting if you look at the customer table. In particular, it would be appealing for the company to have a geographical overview of the origin of orders. Understanding the customers’ specifications is always a key aspect to evaluate the performance and prepare specific strategies.

Question 5a.

Insert a new data source to access customers information. Define efficiently the SQL query by importing only the necessary columns to provide the geographical insights about the orders origin. Then, try to use a Bubble Map for representation. Remember to choose the Location field and to set the useful metric for the bubbles size.

TIP.

Once a new table has been imported, Data Studio automatically makes available the Record Count metric.

Question 5b.

Keep updated the summary box designed for Question 4b of the previous tutorial. Prepare another Scorecard to report the total number of tracked customers. Use texts and shapes to explain your data!

Focus on US customers

Retrieving data is typically a matter of filters to highlight and deal with the essential information only. Until now, we have limited ourselves to the basics of the SELECT command, but it is significantly useful to include some specific clauses in our queries with WHERE.

Question 6.

To learn more about a particular type of clients, the company would expect to extract – based on your report – a list of clients from the U.S. (i.e. USA) with a credit limit greater than $100k. After defining the custom query to filter data accordingly, insert a table reporting the customer name and the credit limit expressed in USD. Sort the results by credit limit (descending order).

Investigating order details

Once observed the customers, it is time to analyze the performed orders more in detail. In particular, this is helpful to gain confidence with some advanced constructs. In fact, while querying the database, you can apply specific mathematical functions to retrieve additional information.

Question 7a.

Look again at the columns available for the orderdetails table. Can you define the SQL query to return the order numbers and the total amount of each order?

TIP.

Remember to group your orders according to the order number. Moreover, renaming columns (especially if you apply mathematical functions) is always a good idea.

Question 7b.

Complete the summary box with the information you have just retrieved. Create two Scorecards to express respectively the maximum and average amount of the given orders. In Google Data Studio, the faster approach is to use the customization menu available for the considered metric (Figure 1). It is accessible from the pencil icon next to the metric name.

google data studio - metric selection
Figure 1: Metric menu

Staff and Offices

The last step consists in providing an intuitive overview about the staff members and the available offices. These additional insights make the report more complete and ensure that we have studied most of the database. At the end, you can design a report similar to the one shown in Figure 2.

Question 8a.

Investigate the composition of the company staff. Use the employees table to select the job titles. Include in your report a Doughnut chart to understand the proportions. You should notice a particularly crowded department compared to the others. In this case, visualizing the absolute value of each slice is more useful to have an idea about the number of employees.

Question 8b.

Given its multinational nature, the company is careful to emphasise the presence of offices around the world. Support the above analysis by delving into the information stored in the offices table. Define the proper query and prepare a Bar chart to present the number of offices per each country in descending order.

data studio dashboard
Figure 2: Final company dashboard

Customize and share the Google Data Studio report

We have successfully imported all the necessary information to complete our dashboard according to the fixed requirements. Now, leave space to creativity. Customize your report layout to present a valuable, clear and informative work.

Question 9.

Complete the report by focusing on the stylistic customization of each single block. Feel free to design it the exact way you find most convenient and observe the final result in View mode. Finally, test the Share button with any of your colleagues to perform the invitation to collaborate. As you can notice in Figure 3, you need to specify if the other members can view or edit the project.

Share menu google data studio
Figure 3: Share project menu

(Bonus) PhpMyAdmin – brief introduction

As we have already mentioned, phpMyAdmin is an administration tool to manage your databases. It offers an intuitive GUI to create and visualize databases with several functions, including SQL queries, tables configuration and users privileges. 

The login page is shown in Figure 4 in which you have also the possibility to choose the language.

credential menu for phpMyAdmin
Figure 4: PhpMyAdmin - login page

Once logged in, the homepage is extremely user-friendly. As you can observe in Figure 5, on the left, you can explore the databases you have access to. This is helpful to investigate the database structure and the information stored for each table. In fact, you can click on each single column to learn more.

On the top instead, there is the toolbar you can use for all the actions you need. For example, from Databases you can access the existing ones or create a new database according to the desired specifications (if you have the privileges). By selecting a specific database instead, you can explore the structure, create new tables or search for specific fields.

phpMyAdmin homepage
Figure 5: PhpMyAdmin - homepage

It is interesting that when you browse the available tables, phpMyAdmin automatically generates the corresponding query. In Figure 6, the city column was clicked to sort by city and the query on the top was then obtained.

If the previous feature is important to get familiar with SQL queries while using a graphical interface, to take a step further you should refer to Figure 7. Clearly, phpMyAdmin supports the possibility to integrate SQL scripts to execute different queries. This is useful to be completely free of facing your data and creating new tables if you need them.

Figure 6: PhpMyAdmin - browsing tables
phpmyadmin - customized query box
Figure 7: PhpMyAdmin - SQL scripts

PhpMyAdmin in a nutshell

However, phpMyAdmin offers several features and operations that can be extremely useful. Besides those already mentioned, we can briefly summarize the main possibilities:

  • Create/remove/rename databases, tables and fields, with support to SQL queries.

  • Status. Retrieve information about the traffic (received and sent) and connections concerning the MySQL server.

  • Export. Export databases from the server according to the desired format. You can choose among different file types, including: SQL, JSON, CSV, PHP array. This is useful to migrate databases, associated with the import function.

  • Import. Upload your databases into the server based on a specific file (format examples: SQL, CSV, XML).

  • Settings. Customize user preferences related to the current session.

  • Documentation. PhpMyAdmin is certainly a user-friendly tool and it is easy to gain confidence with. In addition, it offers the detailed documentation to learn more about installation, configuration and general features.

  • Designer. For each database, users can access this graphical function to create, edit or understand the database schema.

Recommended Readings

More To Explore

Elasticsearch platform

ElasticSearch 8: new features of the new version

Eleastisearch is a NoSQL database used primarily for building search engines. In fact, thanks to the integration of Apache Lucene it allows to properly index text documents and perform very accurate searches. The new release introduces some new features and improvements over version 7.

Wordpress

Elementor vs Avada: comparison of WordPress page builders

Developing websites requires not only the generation of interesting content but, more importantly, the development of attractive, intuitive and full responsive interfaces. With Wordpress you can build blogs and e-commerce very quickly, but presentation requires extra effort especially for the uninitiated. Through page builders, such as Elementor and Avada, this task is simplified. Let’s discover in this article their main features to understand which one is the best for our needs.

Leave a Reply

Your email address will not be published.

Design with MongoDB

Design with MongoDB!!!

Buy the new book that will help you to use MongoDB correctly for your applications. Available now on Amazon!