Visualizing Sales Data with Arctype Dashboards
Tags: mysql, guide
Published: 2021-07-21 19:26:41

## Introduction

### What is Arctype?

[Arctype](https://arctype.com) is a high-performance SQL client that lets you manage your database with a simple user interface. It also enables you to build visualizations straight from queries, modify databases in the same way that you would spreadsheets, and share frequently used SQL queries with your team.
![arctype.com homepage](https://lh4.googleusercontent.com/HAJaGCcyR8RVF7fl0t_ODHbHdOzi5ilVVbzrzoSXx7Cn-plYVZsnAecWNi5jqnnZQMS22b_PyJ5KvjZN-nJBILr8RygmPUCRhxN5vupSg47M4Io7lpiQFZZ3VOWA6tRTff10sUh_)

### What are Arctype Dashboards?

Arctype dashboards allow you to generate charts and tables to examine your data. You can also use them to create tools that modify and visualize data from your database and automate technical and tedious activities.

### Prerequisites

To follow and fully understand this tutorial, you need to have the following:

- [Arctype](https://docs.arctype.com)
- Basic knowledge of [SQL](https://www.tutorialspoint.com/sql/sql-overview.htm)

## Preparing Our Database

### Acquiring Sample Sales Data

We will need to get sales data to visualize. In this tutorial, we will use publicly available data, which you can download [here](https://drive.google.com/file/d/1OfZYbYB6-stVO8j8o8iuYZCwCa-dFYzi/view?usp%3Dsharing). 
The dataset is only 1MB and includes the name, platform, year of release, genre, publisher, and global sales of about 16,000 video games from [Metacritic](https://www.metacritic.com/browse/games/release-date/available).
![meta critic dataset spreadsheet csv](https://lh6.googleusercontent.com/VO2xE5IIsuOLwFdJfZhcOFgOWU08cobXvRVSRcP6F8u_DVzTNIsADH7QUg2btGqeYH8kUy5pPfbowKs-hPL44o8T6OLBwjkPZCBBI4E9KS5EVaT7iah5sBRWTc_twWwK_Nwf0N0u)

### Importing Our Dataset into Arctype

Next, we need to create a table in Arctype and import the dataset. To do this, use the `Import Table` button from the Arctype client.

![arctype import table from csv](https://lh6.googleusercontent.com/99PhgXaeRm3j1Gq284G0VF2BgQijLj7k5PWRsR6tff812tquOIbDGyRo045zb08M_z6RDSm62OVZ2cE7vRrTKKrTQ5C3OqgbfAOSufuTqzFL89Gz_JUwXdJn4SifSaW1FED5hW9i)

After doing that, select the CSV file to import. Arctype will show you a preview of the data you are about to import. If it is correct, press the `Accept` button located at the bottom right of the pop-up.
![csv data import preview](https://lh6.googleusercontent.com/pCW70ZNZZ1daClIyNRNj8-vWLWJfpSQoNOHpqsqNL1NbRnQGSjl97i2UsMKLg-v49bMgOhAtIWV5jMeZC5LxgBzo_Cftampw2JRyG1etL7wIcl4sy5e4M17gp6iX8XD6HVfTSsXG)

Then, we need to confirm the automatically generated schema of our table from the imported CSV:
![csv import schema settings](https://lh6.googleusercontent.com/IWRwIwtZPsrGjhnO2yeFy20C8ge5CRQ3HH-G9OqTn2RqttHn1Ob1cXPTzXCms3UJjY6npz3iZsBb7caUgXULZwkzsq361KiVH6IRkLe5le0t-XiWYBNP1ZcVlGhVGyLZrQ9IS1o8)

We will be renaming the new table to `sales`. We also need to change the datatype of the `year_of_release` column to `int` and the `global_sales` column to `double`.

After doing that, press the `Import CSV` button located at the bottom right of the pop-up.

![arctype table view mysql imported data](https://lh4.googleusercontent.com/sYiBdS1bvFBpYn8UTX8GXAUdk7jhQtsZOEO2X8zBaCgNXpdYttcN_omLpnlI0J8GHrG8pu-H6P1a_G2Ua27mSm8XJlv3p5LL7ug7GdxxZmolKgMLCLBCM45buzFav5DdRZEtQIUH)

## Creating an Arctype Dashboard

### Visualizing Total Sales with Arctype

Now, let’s begin visualizing our database using Arctype. We will start by displaying a count of all sales made. Arctype dashboards provide a handy component called `scorecard` Which shows a value on a card.

To do this, we need to write an SQL query that will fetch the count of the records. Click on the `Queries` tab by the Arctype sidebar (left side of the screen), then click on the `Create Query` button.

![arctype create query from table view MySQL](https://lh5.googleusercontent.com/giMN8L0rO-_oTn4_-_7ybP0QUFqA3J0rKCrn66CTuyhTYClX5LoHbkGGeVXzzWqphqclJ7zZn2tMrkt_iR8aYLqCLDQy1_NpKZqAjBXCAcmiMnxPMMizNcMPkv8PQhAyiIAPu05H)

Paste and save the following query in the editor that comes up:

```
SELECT
	COUNT(*) AS sales_count 
FROM 
	sales;
```
 
You should get a result that looks like the screenshot below when you run the query in Arctype:
![arctype query view with results pane](https://lh3.googleusercontent.com/kWughQoq0u7aPghoCGXUQKsxjQ3R6cx1rQAL2TW26WKBxUFNP0gU1PV2itORQXOkjvGTQzdmFgLYM5-6jEZli-EZWkRyvWeEIcgie5IEZDh5TCObFEWAc9-o7MEgC_yOfZphVk_m)

Next, click on the `Dashboards` tab by the Arctype sidebar (left side of the screen), then click on the `Create Dashboard` button.

![arctype create dashboard from query view](https://lh3.googleusercontent.com/uc3s8bogkMXo8srL1ADVVrMO_0aoGpkkwGBY7F-4xjRFQ6-zp1mpyRvI33bvY7XqHZM0tSTLjxyYTA01OxU8zwExtSOnZgzhAbOIym9jJiYqWkl_rfQ6_NVcy6Npa9un3-9A_JOx)

Then, click the `Add` button at the header of the `Dashboards` section and select `Chart`.
![arctype dashboard add chart](https://lh5.googleusercontent.com/gg6dss0ATB1awgc3Y5YsIq34icU2gDivePp5EaugqIFYJzbSKpCSL_3_rsV3E8sKddBqJGZD5dj7ZPrWocjIXu-GRwhOZDxEAsZON1dWxGnPGcBdczDyjs0HIuyRvVnzmu0TV8oc)

Now, click on the `Select chart data` button and select the `sales count` query we created earlier.
![Arctype dashboard chart select chart data](https://lh4.googleusercontent.com/yq2zEpWMYuonjmozeOh_fv0yJ6heKTKj3GHOQyTC6z-K4o7YyYBRceSK3N8VtlHBfPPQEwHhgzUahcUhrC9aAR08RpWzAFxxbb1Wq60MCH3W2O59ai-hgZfwT_OiSmtZJ0Ip5niM)

Navigate to the right sidebar. Change the title of the dashboard component, select `Scorecard` as the chart type and drag the `sales_count` column to be displayed.
![dashboard](https://lh3.googleusercontent.com/rxDxCtBlnVfAsrcZ-ZsoJTlvn7kJL3sgN9VrP7o5YYmrF9OfYyeCUS-pNt2H-ITsp-yamwK9A8FhIdNe2zkEIlAcl4OZ2wRK9RPeoTf8AP5Ew3Ex4Enj-IUC0Ucr8afFHEIfPclo)

### Visualizing Tables with Arctype

Let’s create a table component with Arctype to display the names of the games with the most sales.

Create a new query called `top sales` and save the following code in it:

```
SELECT 
	name,
	global_sales AS sales 
FROM 
	sales 
ORDER BY 
	global_sales DESC 
LIMIT 10;
```

![arctype sales query view with results](https://lh5.googleusercontent.com/dTo0CReuexaOnvTNwShYZ1B5kx2NDGMeo2TECMHaODeu99QzSGQYZkmcdDMxapB_ihPN-pvt99k2bleSQtC6yOnYhIzFW6B9KpFDzRiyacg4uAbL7m2iNhy0mZZMaEEiFiHbTQHU)

Then, click the `Add` button at the header of the `Dashboards` section and select `Table`.
![dashboard](https://lh5.googleusercontent.com/qxkKEeRpT115-OJWuFHVrg1oEta6L1TbXYu7gwYlWHZjYTpKN81F9PJYCeUqkmJQFng2mDF6xgBSj-JQRsYfqmP-aFjAVqTTpqEj7dr5tzrxqEyhU354gj65SgqmMan7MV2GYZNm)

Once you have done that, click on the `Select chart data` button and select the `top sales` query we created earlier.
![dashboard](https://lh5.googleusercontent.com/SHYiOCjg9FGsmDXWCbJX2w-iWv32KaDcYLp04GtCiSlhN5XsTTUjIMMfiYD6Dx9OWcdaEOXoq6yr8HRCcSVQGFvrLDK9Z-EmSYxj1oTgLXf0a0qiM1AdfTFK4fXNj6r_1MG_BCsa)

### Creating Pie Charts with Arctype

Let’s create chart components with Arctype to display the game platforms and genre grouped into pie charts.

Create a new query called `platforms` and save the following code in it:

```
SELECT 
	platform, 
	COUNT(*) AS platform_count 
FROM 
	sales 
GROUP BY 
	platform;
```

Next, create another new query called `genres` and save the following code in it:

```
SELECT 
	genre, 
	COUNT(*) AS genre_count 
FROM 
	sales 
WHERE 
	genre IS NOT NULL 
GROUP BY 
	genre;
```
    
Then, click the `Add` button at the header of the `Dashboards` section and select `Chart`.  Select `Pie chart` as the chart type, and drag the `platform` column to the `category` box and the `platform_count` column to the `values` box.
![arctype dashboard pie chart game platforms](https://lh5.googleusercontent.com/8qjQy31EOPo-5nNGe_yXGTuQRe8ovoE5iEYaJ0GqtmbtEB3W9QOM4dWKwdN0HqmAltmCEkETVXYzupueRcvPKHmsSKogT7eX26KgQTQtR0DMMGNxnNyFxA6yBS7lQ-LIAcjTzEZp)

Repeat the process to generate a pie chart for the `genres` query:
![arctype dashboard pie chart game genre breakdown](https://lh6.googleusercontent.com/PBk6Lb2tHifwstRKWEexAs6pgWANn2Qoa7yPhMS9zsbEl9A2W8AC0fV4_KB-OBKZhaQoerJ9EVnaoFcFHp1UO5w6dT_ibr7NDaEYmlEHHkO-hGiNk8oq36P5o40oJHkEBvzy2Yo8)

### Creating Bar Charts with Arctype

Let’s create chart components with Arctype to display the number of games released each year and game publishers on a bar chart.

Create a new query called `year_of_release` and save the following code in it:

```
SELECT 
	year_of_release, 
	COUNT(*) as release_count 
FROM 
	sales 
WHERE 
	year_of_release IS NOT NULL 
GROUP BY 
	year_of_release 
ORDER BY 
	year_of_release;
```    

Next, create another new query called `publisher` and save the following code in it:

```
SELECT 
	publisher, 
	COUNT(*) AS publisher_count 
FROM 
	sales 
GROUP BY 
	publisher 
LIMIT 15;
```
    
Then, click the `Add` button at the header of the `Dashboards` section and select `Chart`.

After doing that, click on the `Select chart data` button and select the `year_of_release` query we created earlier. Also, change the title of the dashboard component, select `Bar Chart` as the chart type, and drag the `year_of_release` column to the `x-axis` box and the `release_count` column to the `y-axis` box.
![arctype dashboard bar chart game releases by year](https://lh4.googleusercontent.com/FeHF0H9zZ1H-AqsGsn8Q9xTuuAZjX34EoURjAYVdWDwaeSPpFfUUaKar1c1JAAS1hAXfYEYNK2Zb2pA2QUUHPOREsBeqPJjkbmMNTy91d3GSESBDOPlEnpUJ8dPX8xzz5OEgQ4Yc)

Repeat the process to generate a horizontal bar chart for the `publisher` query:

![arctype dashboard horizontal bar chart](https://lh4.googleusercontent.com/UMXlyW50Snzri0z8jBeLy-aDxH4CEKNvy111BwrN95kyvB6bX0mYZKGd4uECN8haq0vOqMm5xch5utt2728OTynSQCHuwLK1hGyZ8h8U-r5RPuAKggay2jA-NYLIm8DOE38_ARt0)

### Creating Doughnut Charts with Arctype

Let’s create a doughnut chart with Arctype for our publisher query. Click the `Add` button at the header of the `Dashboards` section and select `Chart`.

Change the title of the dashboard component, select `Doughnut Chart` as the chart type, and drag the `publisher` column to the `category` box and the `publisher_count` column to the `values` box.
![arctype dashboards donut chart](https://lh3.googleusercontent.com/khQzVWe9zLYl6l_ypLjy7SKmL4khMnJZfsK4JSXVozUaz2mIgR6OT3wq-oJTYmFKR6aADCvOxstzmRxQS6J9hmEt7nX-dA9fZfvrTGjNJL3s77xiiCWI5-EFZ6s7BGa1XuwEEG91)

### Creating Line Charts with Arctype

Let’s create a chart component with Arctype to display the annual number of game sales on a line chart.

Create a new query called `annual_game_sales` and save the following code in it:

```
SELECT 
	year_of_release, 
	SUM(global_sales) AS sales 
FROM 
	sales 
WHERE 
	year_of_release IS NOT NULL 
GROUP BY 
	year_of_release 
ORDER BY 
	year_of_release;
```
  
Just like all other chart types, select `Line Chart` as the chart type, and drag the `year_of_release` column to the `x-axis` box and the `sales` column to the `y-axis` box.

![arctype dashboard line chart](https://lh3.googleusercontent.com/tEvwHK4UehHQlYlj36KFFHacfyeuoXYJ3ZqB2yN6Jozq-Dfi57ojQP2t6amwZv0heJdOwtLWKOmi6KtTgVn0YhvnoyOb5UxB1fX4d6xWjbLrDL04i2qbbfuo5sEvcsdfUeCwkwdn)

### Creating Area Charts with Arctype

Let’s create an area chart with Arctype for our `annual_game_sales` query. Click the `Add` button at the header of the `Dashboards` section and select `Chart`.

Select `Area Chart` as the chart type, and drag the `year_of_release` column to the `x-axis` box and the `sales` column to the `y-axis` box.

![arctype dashboards line chart](https://lh3.googleusercontent.com/PuaGjezhh8V3vX1IviRl6jlKyGoBsaCn4v9qQcX133m94GMFsET86H067O_sxEgfXay4iSXmms4BpPIBrXi1UQifIcz6Ozeg9xYw4GHaw2jY2P4ANqVNPEcShaclWr3OMicna5fF)

## Introducing Query Variables for Dynamic Results

We have been creating visualizations based on specific queries with set parameters. What if we wanted to give Arctype input and have it create visualizations based on our information? We can use query variables to assist us in addressing this problem.

[Query variables](https://docs.arctype.com/queries/query-variables) allow you to connect variables to your queries and provide various values for them during execution. They function similarly to [prepared statements](https://en.wikipedia.org/wiki/Prepared_statement), which enable you to construct a query's structure and pass values to the parameters inside it.

### Using Query Variables in Arctype

We will create a query to return the number of game sales in a year using query variables. Create a new SQL query named `yearly_sales` and save the following code in it:

```
SELECT 
	SUM(global_sales) AS year_sales 
FROM 
	sales 
WHERE 
	year_of_release = {{year}};
```    

When this query executes in Arctype, you will notice that it lets you provide value for the `year` variable. This is because query variables prepare a query structure, then provide the functionality to pass different values to the variables to get desired results.

![arctype query view query variables](https://lh3.googleusercontent.com/padBQcPbLXJ8Rhfr1eVUug8eNrvypFsezlspWD8GVKUQeanbfFQYE0ucKQRumj2HzCDw3Su7LkGpphaILHkuDgT5UCFCEH9iz9brEwizQR1bOcyd_fVX53Udb-qKgWxZTfvHZhc-)

### Using Query Variables in Dashboards

We will create a scorecard to display the result of the `yearly_sales` query with a query variable. To do this:

- Create a dashboard component,
- Select the `yearly_sales` query as the data source,
- Change the chart type to a scorecard,
- Drag the `year_sales` column to the display text box.

![arctype dashboard scorecard with query variable](https://lh6.googleusercontent.com/YnXfiw6DnsfWiWwS8n76TdaPnHMMLWT2NzNWY5n8LTTJw0OVHnAztAEFIIfjRvyvfNioBNro6LWRejuAZtYLeoU2YBhVRly4_gwxJsKOJ_Bz4YOog3rr_5DgkVT8hgvUh2yYheyX)

You will notice that the `year` variable also appears by the right sidebar. Changing this value will instantaneously update the result of the scorecard.

![actype dashboard query variables before change](https://lh5.googleusercontent.com/BZ-meeb5rJutyz-fvdUoGeYQy6bAOpqXFI2HHOPjOD_vevRYAt85e3vfJiLeMkp383rBS7J8CAbwTLmPYo3Iy3D_Mq8IV9cgyGZhT9y9dBf-twFIBRZD0mJGMIBMB3F-CwR-PI2B)

![arctype dashboard query variables after change](https://lh5.googleusercontent.com/qc6CgvgVIGch3ii6RySgZKvLAFOjI0pdjq_9CSMlnzoK9wSIysdYPUpFGeJFng_sr9yBTLOiNttMXVupDFH7kbdBJRQyiv98G9cjy1YHMjm0tLxiiiZ3Un91gOJAL_elvRiVsF2T)

## Conclusion

In this article, we learned about Arctype and saw how to visualize sales data using its built-in dashboards. We also explored query variables and used them to generate dynamic visualization results.

You can learn more about Arctype from the [official documentation](https://docs.arctype.com). If you have any questions, don't hesitate to contact me on Twitter: [@LordGhostX](https://twitter.com/LordGhostX).