{ "version": "https:\/\/jsonfeed.org\/version\/1", "title": "LEFT JOIN: blog on analytics, visualisation & data science, posts tagged: redash", "home_page_url": "https:\/\/en.leftjoin.ru\/tags\/redash\/", "feed_url": "https:\/\/en.leftjoin.ru\/tags\/redash\/json\/", "icon": "https:\/\/en.leftjoin.ru\/user\/userpic@2x.jpg", "author": { "name": "Nikolay Valiotti", "url": "https:\/\/en.leftjoin.ru\/", "avatar": "https:\/\/en.leftjoin.ru\/user\/userpic@2x.jpg" }, "items": [ { "id": "60", "url": "https:\/\/en.leftjoin.ru\/all\/redash-dashboard-overview\/", "title": "Redash Dashboard Overview", "content_html": "

Redash is an open-source tool that is available in two versions: self-hosted and cloud. If you use the first one, Redash is free of charge, but for a cloud option you’ll have to pay. In both versions you can connect to a numerous amount of databases (including Clickhouse) or other sources like Google Sheets by API.<\/p>\n

Redash is a SQL query editor that allows building visualizations. To make a dashboard, we first need to run a SQL query and then build a visualization. After that, we can add the queries with their visualizations to the dashboard. The process makes data investigations easy and simple.<\/p>\n

Data preparation<\/h2>\n

To start working with data we need to click settings and create a new data source.<\/p>\n

\n\"\"\n<\/div>\n

When working with Superstore dataset we could not directly connect a .xlsx file as redash works only with databases. Thus, we uploaded our .xlsx into a MySQL database.<\/p>\n

Building reports and visualizations<\/h2>\n

In the beginning of the dashboard, you can see the filters. This is the only way to apply filters to a dashboard in Redash and to choose parameters like province interactively.<\/p>\n

\n\"\"\n<\/div>\n

First of all, we added KPI cards to the dashboard. The functionality of Redash is extremely limited in terms of visualizations, so the only way to build a KPI card was by using so-called counters. A counter is a type of visualization that allows displaying a current and a target value, however, in our case we used the previous year value instead of the target.<\/p>\n

For the KPI cards, we used the query below.<\/p>\n

\n\"\"\n<\/div>\n

It’s a simple query that returns the sum of the profit in current month and the sum of the profit in the previous one. In each query we need to define Year and Month and Province in the WHERE statement, so that all our visualizations are filtered based on the chosen year, month and province. The results are two numbers, curr and prev. Then we click on New Visualization, choose counter as a type and assign curr to the counter value and prev to the target value. We can also change format by adding a dollar sign prefix.<\/p>\n

\n
\n\"\"\n\"\"\n<\/div>\n<\/div>\n

In our previous reviews of BI-tools, we displayed all KPI cards in a single line. In Redash, however, the numbers were too small when displayed in a single line and unnecessary information like the query name and the last update time were cluttering the view. This is another disadvantage of Redash, so we had to make bigger cards and display them in two lines.<\/p>\n

To display top performing provinces we used word clouds. The query returned the sum of sales by provinces. Then the sum of sales was used as a frequency column to define the size of the province names.<\/p>\n

\n\"\"\n<\/div>\n

For Profit Dynamics visualization we used a simple line graph. The query below returned a table with total profits for each month as well as two additional columns that display profit in the current month and the previous one.<\/p>\n

select date_format(orders.OrderDate, '%Y-%m-01') as month, sum(orders.Profit) as profit, curr.curr_profit, prev.prev_profit\r\nfrom orders\r\nleft join (\r\n    select date_format(OrderDate, '%Y-%m-01') as month, sum(Profit) as curr_profit\r\n    from orders\r\n    where MONTH(OrderDate)=MONTH('{{Year and Month}}') and YEAR(OrderDate)=YEAR('{{Year and Month}}')\r\n        and ('{{Province}}'='0. All' or Province = '{{Province}}')\r\n    group by 1\r\n) curr on curr.month=date_format(orders.OrderDate, '%Y-%m-01')\r\nleft join (\r\n    select date_format(OrderDate, '%Y-%m-01') as month, sum(Profit) as prev_profit\r\n    from orders\r\n    where MONTH(OrderDate)=MONTH('{{Year and Month}}') and YEAR(OrderDate)=YEAR('{{Year and Month}}')-1\r\n        and ('{{Province}}'='0. All' or Province = '{{Province}}')\r\n    group by 1\r\n) prev on prev.month=date_format(orders.OrderDate, '%Y-%m-01')\r\nwhere ('{{Province}}'='0. All' or Province = '{{Province}}')\r\ngroup by 1,3,4\r\norder by 1<\/code><\/pre>
We then used the a line graph to display the profit column and a scatter plot to display curr_profit and prev_profit columns as both of them had only one observation.<\/p>\n
\n\"\"\n<\/div>\n
Profit and Sales by Category visualization shows a SQL query table that returns profits and sales by category and sub-category of products.<\/p>\n
Last but not least, we have pivot tables for top products and top customers by profit. Pivot tables in Redash allow grouping elements by using aggregate functions. In our case we grouped products by profit. I do not recommend using this feature for a large amount of data as if you change the aggregations on the fly in the browser, the browser might slow down and even crash.<\/p>\n
\n\"\"\n<\/div>\n
Conclusions<\/h2>\n
You can find the final dashboard here<\/a>.
\nOur team has evaluated the dashboard and the following scores on 1-10 scale 10 being the highest were given:<\/p>\n
    \n
  1. Meets the tasks – 7.3<\/li>\n
  2. Learning curve  – 7.5<\/li>\n
  3. Tool functionality – 5.5<\/li>\n
  4. Ease of use – 7.5<\/li>\n
  5. Compliance with the layout – 6.0<\/li>\n
  6. Visual evaluation – 5.2<\/li>\n<\/ol>\n
    Overall: 6.5 out of 10.<\/p>\n",
            "date_published": "2021-05-07T13:24:27+03:00",
            "date_modified": "2021-05-07T13:24:12+03:00",
            "image": "https:\/\/en.leftjoin.ru\/pictures\/--2021-05-04-192624.png",
            "_date_published_rfc2822": "Fri, 07 May 2021 13:24:27 +0300",
            "_rss_guid_is_permalink": "false",
            "_rss_guid": "60",
            "_e2_data": {
                "is_favourite": false,
                "links_required": [
                    "system\/library\/fotorama\/fotorama.css",
                    "system\/library\/fotorama\/fotorama.js",
                    "system\/library\/highlight\/highlight.js",
                    "system\/library\/highlight\/highlight.css"
                ],
                "og_images": [
                    "https:\/\/en.leftjoin.ru\/pictures\/--2021-05-04-192624.png",
                    "https:\/\/en.leftjoin.ru\/pictures\/--2021-05-05-181541.png",
                    "https:\/\/en.leftjoin.ru\/pictures\/--2021-05-04-193757.png",
                    "https:\/\/en.leftjoin.ru\/pictures\/--2021-05-04-195521.png",
                    "https:\/\/en.leftjoin.ru\/pictures\/--2021-05-04-195956.png",
                    "https:\/\/en.leftjoin.ru\/pictures\/--2021-05-04-195719.png",
                    "https:\/\/en.leftjoin.ru\/pictures\/--2021-05-04-202308.png",
                    "https:\/\/en.leftjoin.ru\/pictures\/--2021-05-04-203727.png",
                    "https:\/\/en.leftjoin.ru\/pictures\/--2021-05-04-204757.png"
                ]
            }
        },
        {
            "id": "50",
            "url": "https:\/\/en.leftjoin.ru\/all\/calculating-user-retention-by-24-hour-windows\/",
            "title": "Calculating User Retention by 24-hour windows and calendar days in SQL",
            "content_html": "
    Yesterday I got a message from one of our blog readers, asking:<\/p>\n
    \n
    Let’s say today’s Monday, and an app was downloaded 187 times. If I want to find out what’s the Retention rate was on day 1, what day of the week should I start taking the count with?<\/i><\/p>\n<\/blockquote>\n
    He is referring to the blog post on Сalculating Retention Rate<\/a>. I would like to clarify that since retention rate can be calculated by 24-hour windows, as well as by calendar days. In our case, day 0 will be Monday, and day 1 will be Tuesday. However, there is one little hiccup...<\/p>\n
    For example, if we started promoting our product on Monday, October 12 at 23:59, then all downloads of this day will have the retention rate of day 1. It’s a problem of performing calendar calculations. To address this, some data analysts calculate retention rate not only by calendar days but also by 24-hour windows.<\/p>\n
    Let’s apply this idea to the above case:<\/p>\n