ANLY482 AY2017-18T2 Group32 : Project Findings / Final
| Interim | Finalnew! |
Contents
Revised Dataset
After Interim, our sponsor have provided us with 2 new dataset - Brand Target and Therapy Group, to be used when we build our dashboard.
Data Preparation and Cleaning
We looked through the new datasets and found that there is inconsistency in the brand name used in these 2 files as compared to the Invoice file. Hence, we have standardized spelling of brand names to ensure consistency with Invoice file.
Data Transformation
Using Qlik Sense, we have joined the datasets as follow.
Literature Review on Dashboard Design and Elements
In order to provide additional insights to our sponsor beyond the KPIs, this paper seeks to explore alternative ways of visualizing data that is not available in visualization dashboard software like Qlik Sense. The Sankey Diagram has been chosen due to their ability to provide users with more granular details than traditional bar graphs or pie chart.
Bullet Chart
The bullet graph is developed by Stephen Few as a replacement for the usual gauges and meters charts in dashboards because of its ability to convey a lot of information in a compact space. It displays a key measure, along with a comparative measure and qualitative ranges to represent if the measure is good, bad, or in some other state. The qualitative scale is color coded by using distinct intensities from dark to light of a single hue. The darker color intensities are used for the poor states while lighter color intensities are used for the favorable states. It is also easy to detect if the measure has met or exceeded the comparative measure, represented by the short line that intersects the bar as a cross shape is formed. Thus, it is perceived pre-attentively due to its prominent form.
Sankey Diagram
A Sankey diagram is used to capture the flow of measures between many dimensions within a network. Developed more than 100 years ago by Irish Engineer Riall Sankey to analyze how efficient steam engines were, Sankey has continued to play an instrumental role in visualizing the quantitative flow of information from one node (usually a dimension) to another. An early adaptation of it was French Engineer Charles Minard's depiction of the failed French invasion of Russia. The observer is able to detect the path of Napoleon's army and realize it was slowly dwindling, as seen from the thinning of brown line.
Dashboard Visualization
A dashboard is an effective tool in communicating a great deal of useful but varied information clearly for users within eye span. We have looked upon Stephen Few's book to set out the guidelines when building the dashboard, focusing on presenting the most crucial variables in a simple and concise manner.
Some of them include:
1. Minimising the non-data pixels (visual content that is not data, e.g. grid lines, color fill, axis lines, etc.) and enhancing data pixels
2. Choosing appropriate color schemes, fonts, graphs, to make the viewing experience aesthetically pleasing, and to be inclusive towards people who are color-blind
3. Increasing the degree in which users comprehend information, by limiting chunks of information per page to within 9, which is the upper-bound in which memory is stored in short-term memory
Throughout the entire dashboard, we avoid the use of green/red combination as much as possible since the colors are not easily differentiated by color-blind users. However, due to some of the limitations of Qlik Sense, the preferred visualization software of our sponsor, some of the colors and fonts are fixed.
Dashboard Design and Elements
Display 1 is the first tab on the Main KPI page, where the 3 KPIs that are the subject of our project are displayed prominently. This page provides an overview for the management to see the TCE sales performance in terms of sales dollars. Hence, for the YTD TCE Sales in 2018, it is placed prominently in the top-left corner of the page. At the bottom-left corner, 2017 values are provided as context for comparison to YTD 2018 values in the line chart.
As suggested by Stephen Few, the KPIs are placed over-and-under to better compare them, using a similar range of 100%. The difference in grey hues background fill in the bullet chart represents the qualitative ranges. The blue bar represents the performance measure while the black marker represents the target. For instance, in KPI #1, the marker is at 95% and the blue bar spans to the "Good" range of the chart. Hence, the bullet chart helps users to see how the KPIs are standing against the targets.
Display 2 - 7 seek to present the 3 different KPIs in greater detail, according to channel and product. We have placed the similar elements of each page in the same orientation as to let users easily orientate themselves around the dashboard. These graphs are made to be easily understood by users.
Instead of using bullet charts for the individual KPI, we have used the KPI feature embedded in Qlik Sense. This horizontal bar chart allows us to set the previous year KPI result as a reference line so that the management can compare the current year performance with it. The difference in blue hues in the bar graph shows where the target is. For instance, in Display 2, 2017 renewal rate is 77.26% as compared to the current 96.28%, which is in the dark blue region (from 95%-100%) for the target. Due to the different sales demands to various channels and products, we did not use bullet charts in these dashboards as the qualitative ranges for each varies. Instead, we used bar charts with markers to represent the sales in these channels and products against the target set.
In Display 2, we also used a Pareto chart to find out the cumulative sales from renewed accounts across different channels. As the management wants to find out the difference of actual sales from the target, we have chosen to display the information in a table form as this allows users to understand the sales gap accurately. This is a trade-off from displaying them in graphs which might have been quicker, but will require users to calculate the difference, hampering the usability of the dashboard.
To prevent exhausting users visually, we used only less saturated colors such as blue, in 2 hues (light/dark) to present our data.
Filters
We have included filters to allow our sponsor to zoom into the details they are interested in at the bottom-right corner of the dashboards. Even though they serve an important function, they do not display data and hence should not be given prominence, thus occupying the least prominent real-estate of the dashboard.
Main Findings and Analysis
Understanding the shift in sales across different time periods is crucial for the company to manage its expectations and operational requirements. Hence, it is important for them to identify key factors and understand why the sales performance is (not) meeting their expectations.
Common visualization tools such as bar and line chart are used to better understand sales patterns across time periods.
TCE Overall Sale Analysis
In Display 8, we can see that the line chart provides a great overview of the past years' sales numbers across the months. The bar charts allow different segments within the same category to be compared side-by-side. There are also line markers for past years data, making it easy to compare at a glance how each segment of the business is faring.
With the filters for months, the visualization using bar and line graphs are dynamic and able to effectively show the shift in sales patterns across specific time periods across Therapy groups and sales Channels.
Some analysis that we have obtained from the above diagram:
- Overall TCE sales for 2018 is lower than previous years
- Sales follow a seasonal pattern, Q1 being the highest
- Across different channels, 2016 TCE sales are highest, followed by 2017 and 2018
- The best channel (W02) and therapy group (CNS) have been underperforming as compared to previous years
Overall Renewal Rate Insights
From Display 9, we can see that the renewal rate for Jan 2018 (96%) far exceeds 2017 (77%). However, upon closer inspection, we see that the sales across different channels in 2017 are almost as good as current sales or significantly higher for (W02). This means that the renewal rate for 2018 far exceeds 2017 not because of an absolute improvement of sales dollars. This is evident from the table where it shows the difference in sales for the different years. The total difference in sales between Jan 2018 and 2017 is significantly lower than between Jan 2017 and 2016.
Hence, due to strong sales dollars in 2016, which is used as a base year comparison for 2017 renewal rate, the renewal rate for 2017 has performed poorly.
Renewal Rate - Channel and Product Analysis
In Figure 5, the renewal rate for Jan 2018 is 96.28%, a 19% improvement from Jan 2017. From the factors identified from Display 8, we observed that due to strong sales performance in 2016, it has resulted in 2017 renewal rate KPI being much lower.
In Table 2, we could see that it did not meet the targets in some channels and products. Channel W02 and product AGK posed the largest negative difference from the target, despite the huge improvement from last year's renewal rate.
The Pareto chart in Figure 6, the top 4 channels (W02, X04, X03, X05) attributed to more than 80% of renewal sales.
_Cumulative_Sales_in_Channel.png)
Additional Visualization Tools
As our sponsor is interested in finding out the performance of the account representatives (ARs), a split bar chart was initially used to compare their performance across the sales channels and product category as seen below in Display 10.
However, this method fails to capture information at a more granular detail i.e. the account representatives is unable to detect his performance for each product category across multiple channels at a glance. Furthermore, with multiple sales channels mean that users have to scroll through the chart which has an adverse impact on user experience. This is because critical information can be lost when you lose sight of some data by scrolling.
Sankey Diagram
The Sankey diagram becomes very useful in capturing the flow of sales (as seen from the green lines) from the individual ARs to the different channels and trickle down to the therapy group (represented by a node) as seen below in Figure 10.
Furthermore, using the characteristic of Qlik Sense where users can filter just by clicking on the dimensions, they can also compare the AR's performance in individual channels or therapy group as seen below in Figure 11.
Limitations of Qlik Sense
Limited visualization tools
Only basic visualization tools like Line, Bar, Pie charts are embedded in Qlik Sense. Other visualization tools (chart extensions) such as bullet charts and Sankey diagram have to be downloaded from Open Source.
Unable to modify colors
Qlik Sense limits the font size (Small, Medium, Large) for text and colors are mostly fixed automatically, so we cannot change them to our desired size and color. For diagrams that were integrated into Qlik Sense using charts extensions developed by other developers, the colors were fixed based on their designs. Hence, we were not able to apply proper color coding as recommended by the textbook to these diagrams.
High number of product
The TCE brand encompasses 44 products. Incorporating all of them into the charts like Sankey diagram would make them look very cluttered, preventing users from deriving any useful information. Hence, there is a need to show only the product categories to balance the granularity and simplicity of information it can give to them.
Conclusion | Key Takeaways
Having a dashboard enables companies to visualize key sales information, allowing them to understand business performance instantly. The characteristic of Qlik Sense allows users to drill down to the specifics by clicking on a dimension will certainly allow multiple stakeholders of our sponsor to either get the big picture or narrow down to the products or channels they are in charge of. Nonetheless, creating a dashboard is not just about putting in bar graphs and pie charts. It involves striking a balance amongst business needs, visualization design elements as well as alternative diagrams that empower users to analyze cross-sectional data. However, dashboards should be intuitive and emphasis on clarity and user experience rather than advanced albeit complicated diagrams that not everybody can understand.
References
- Few, S. (2006). Information Dashboard Design. Sebastopol, California: O'Reilly
- Sankey Diagrams: Six Tools for Visualizing Flow Data. (2017). Retrieved from https://www.azavea.com/blog/2017/08/09/six-sankey-diagram-tool/
- Rasmussen, N., Bansal, M., Chen, C., & Ebooks Corporation. (2009). Business Dashboards A Visual Catalog for Design and Deployment., Hoboken: John Wiley & Sons, Inc. Hoboken: John Wiley & Sons.
Acknowledgement
We would like to thank Prof Kam Tin Seong (Associate Professor of Information Systems; Senior Advisor, SIS) and Instructor Meenakshi Gopalakrishnan who have provided our team with valuable feedback and guidance throughout this entire project. We would also like to thank our sponsor for kindly providing us with dataset and support.
