Learning Qlik Sense The Official Guide (2015)

Chapter 9. Sales Discovery

Throughout this book, we have shared the driving forces in the creation of Qlik^® Sense and key capabilities to aid in helping organizations make better business decisions. This chapter is the first of four that will apply Qlik Sense to the challenges of analyzing sales performance within your organization. This example and many others are available for you to explore live at http://sense-demo.qlik.com. Please bookmark this link as additional demonstrations and examples are constantly being added and updated. Now let's turn our attention to the following challenge of sales analysis and how Qlik Sense addresses this common business challenge.

In this chapter, we will cover the following topics:

· Common sales analysis problems

· The unique way Qlik Sense addresses these problems

· How the Sales Discovery application was built

The business problem

Analyzing sales information can be a difficult process for any organization, and is critical to meeting sales expectations and understanding customer demand signals. What makes sales analysis so difficult is that many perspectives can be taken on the enormous amount of information that is captured during the sales process.

Some key questions can include:

· Who are our top customers?

· Who are our most productive sales representatives?

· How are our high margin products selling and to whom?

The key thing here is that during the analysis process, one answered question always leads to further questions depending on the results; in other words, the analysis process's diagnostics. These paths to discovery cannot be precalculated or anticipated. With this in mind, let's take a look at how the Sales Discovery application seeks to meet these requirements.

Application features

Qlik Sense's associative model allows users to answer the common questions outlined in the preceding section through the selection of elements in filter boxes, but more importantly, drive follow-up questions. Often, this relies on Qlik Sense's ability to instantly identify the associated and nonassociated data, which is also known as "The Power of Gray" after the color assigned to nonassociated elements highlighted in Chapter 5, Empowering Next Generation Data Discovery Consumers. The following are two key beginner questions that will drive additional questions as the analysis begins.

Key questions can include:

· Who are our top customers?

· What are these customers buying?

· Where are these customers buying from?

· Are the products getting there?

· Who are our bottom five customers?

· Can we cross-promote products?

· Who are our most productive sales representatives?

· What products are the most productive sales representatives selling?

· Whom are they selling to?

· Which regions are they being sold in?

Before we begin, let's review the main sheets in the Sales Discovery application. As noted in the following screenshot, the application is made up of the Performance Dashboard, Top Customers, Shipments, Sales and Margin, US Regional Analysis,Transactions, and finally, Past Dues sheets:

The application overview

Given the nature of the associative model, all filters are global, allowing a user to explore each application sheet in the context of the selected filters and associative results. Filters serve as a way to ask questions to the Qlik Sense application.

Who are our top customers?

So with that said, let's begin with our first question, "Who are our top customers?". This is a typical question that can be handled by a number of BI solutions in the marketplace.

Our top customers

In the preceding screenshot, we can see that the top five customers in terms of sales are Tandy Corporation, Paracel, Acer, Talarian, and Boston and Albany Railroad Company.

360-degree customer view

Now is where things get interesting in Qlik Sense and the associative experience. Once we select these customers, as seen in the following screenshot, we get a 360-degree view of them across the application. Immediately, we can see which representatives have sold to these accounts, the trended revenue, year-on-year sales, as well as what percentage of the regions these sales were made. The percentage of the regions (noted by the green arrow) where the sales where made is highlighted in the filter list shade, which shows approximately 25 percent of the regions:

Top five customer sales

Filtering customers

The preceding information leads naturally to the next question: what are these customers buying and from where?

Again, because of Qlik's associative indexing engine, this information is linked together automatically. Based on this, let's view the impact that filtering these top five customers has on sales and gross margins, as shown in the following screenshot. Note that the customer filter box with selections is globally available at the top of the screen. In the Sales and Margin sheet, we can see that Canned Foods and Produce account for the largest sales, and Baking Goods has the highest gross margin with just over 50 percent.

What are these customers buying?

As we continue our analysis, the next question that is most likely to arise is where are these sales occurring? Again, this data is available in the sales transaction, and Qlik's associative indexing engine makes this easily available and interactive within the application. Note that in the next figure, the US Regional Analysis sheet displays the sales by states, customers, and the important shipments as well:

Where are these customers buying from?

Reviewing shipments for top customers

We can see in the preceding screenshot that Minnesota and Ohio account for all top five customers sales that are between 5.94 million and 11.89 million. After reviewing this sheet, a number of questions can arise and be analyzed. Let's follow one specific thought on shipments. Are products getting there?

As we know, shipments play a critical role in a sales process because without shipping, you cannot book revenue and continue to grow sales. With this in mind, let's turn our attention to the Shipments sheet as shown in the following screenshot. From here, we can see the trending shipment information on two levels: % On time shipments and Number of shipments late vs on time. Additionally, we see that the on time shipment goal is 86%. Based on this, we can see problems in meeting these goals in September, October, and November 2014:

Are the products getting there?

Reviewing the bottom five customers

Now, let's turn our attention to an equally important topic: who are our bottom five customers and how can we increase sales to them? In the following screenshot, we can see the bottom five customers: Edna Design, Teammax, Champion International, Fokas, and Renegade Info Crew. Our sales to them are 2 million products or less and they purchase lower margin products:

Who are our bottom five customers?

Noting this, let's dig in a bit deeper on the products they purchase. In the following screenshot, we can see that these customers purchase a large amount of Produce and Snack Foods. Now, the question arises—can we cross-promote products to increase oursales from these customers?

Can we cross-promote products?

Based on the information gleaned, we can see some opportunities to cross-promote products. For example, with the high purchase of Produce by these customers, perhaps a cross-promotional program that introduces Eggs (at a 67% margin) to them to supplement their produce may raise sales. Additionally, with strong sales of Snack Foods, perhaps we can expand the sales of Baked Goods (at a 52% margin) to these customers as well. Now let's turn our attention to the analysis of sales representatives.

Who are our most productive sales representatives?

As often is the case, a key area for analysis is the performance of sales representatives. So, let's turn our attention to the Sales and Margin sheet in our Sales Discovery application, as shown in the following screenshot. Here, we can see that Judy Thurman,Steward Wind, and Lee Chin lead the sales team in revenue terms:

Who are our most productive sales reps?

Analyzing products

The next question that arises is what products are they selling? As we can see, Canned Foods and Produce are the top selling products. After identifying these sales representatives and top selling products, we will need to combine this information with an understanding of which customers are driving these sales.

What products are they selling?

Analyzing customer sales

Navigating back to the Top Customers sheet, we can see from which customers these sales are generated. Perhaps, while working with these sales representatives, additional promotions can be developed to expand the sales of products such as Canned Products to these customers.

Who are they selling to?

The final area to help improve sales representative performance is to analyze where these products are being sold. In the US Regional Analysis sheet, we can see that Sales by State, Customers, and Ships to are nicely dispersed, and additional information is not necessary for the next step:

What regions are they selling to?

As you can see, the Sales Discovery application provides a 360-degree view of a sales analysis. This is primarily driven by Qlik's associative indexing engine that drives all Qlik-based applications. Additionally, like most analysis processes, the path to discovery of new information cannot be prestaged but rather unfolds based on the next question asked. This is where Qlik Sense excels in enabling a level of interaction with data to drive insight and is only limited by the data that is available. Now, let's turn our attention to how this application was built.

Building the application

Let's start our review of the heart of a Qlik Sense application, the data model. As you can see from the following screenshot, there are twelve tables in the Sales Discovery associative model. At the heart of this application is the SalesDetails table. All these tables were created through Data Load Editor, which was covered in Chapter 7, Creating Engaging Applications. It is worth noting that Qlik and Qlik partners provide both general-purpose connectors and specialized connectors to access a broad array of data sources.

The Sales Discovery model

Let's dig a bit deeper into the key tables. The key tables that drive this application are covered in the following sections.

The SalesDetails table

The SalesDetails table contains all the key information about the sales transaction for a specific order. This includes information such as the order number, date, and so on, as shown in the following screenshot:

The SalesDetails table

The Customers table

The Customers table contains all the key information about the customer: channel, region, account management, and so on:

The Customers table

The AggSales table

The AggSales table contains all the sales KPI information and is associated with the model so that sales information is available by customer, product, region, and so on:

The AggSales table

US States ISO CODE 2 polygons

The US States ISO CODE 2 polygons table drives the map visualization in the US Regional Analysis sheet. The key field is defined by the state, which drives the associative sections, and the field US States_Area is an imported Keyhole Markup Language (KML) file that contains the map geographic information. This is stored as blob data in the model, and the map object interprets this information when used in a sheet. This table is shown in the following screenshot:

The US States ISO CODE 2 polygons table

Analyzing the Sales Discovery Library

Now let's turn our attention to what has been exposed in the Sales Discovery Library by the developer to facilitate the creation and sharing of personal sheets.

Dimensions

In the next screenshot, we can see the dimensions that were created. One particular dimension that needs attention is the Region > Cust dimension, which provides a drill-down navigation from Region Name to Customer. This capability usually requires extensive modeling or complex scripts in other BI software products, but with Qlik Sense, this is a simple selection process when creating a dimension. This is another example of the power of Qlik's associative indexing engine in action, but this time, easing the development of navigation within the application.

Dimensions

Measures

The next area to cover is Measures. These are calculated expressions that most often form the KPIs within an application. We can see in the following screenshot the list of measures that are used and exposed to contributors to allow them to create private sheets. Note that hovering the pointer over any of these objects makes a preview popup appear to provide additional context. In this case, you can see how the measure is calculated. The following screenshot shows Measures:

Measures

Additionally, the following table contains the measure definitions that directly tie to the KPIs used in this application:

Visualizations

The last category of objects in Library (Master items) is Visualizations. These are preformed visualizations that are typically the most popular or requested visualizations. They are defined to help facilitate a user's analysis and can be easily dragged and dropped onto a private sheet. Here, we see a trend line chart for Number of shipments vs late vs on time. Each of these visualizations contain predefined dimensions, measures, and chart definitions:

Visualizations

Summary

In summary, Qlik Sense provides unique capabilities to meet the challenging task of analyzing sales data. Without the capabilities offered by Qlik, this task can be difficult due to the size of the data and the many perspectives that can be taken in trying to understand customer buying habits, sales representative productivity, and the responsive nature of the organization in meeting customer needs. Qlik's associative indexing engine powers this exploration. This means that meeting these requirements is no longer challenging at all.

In the next chapter, we will explore how Qlik Sense will meet the needs of Human Resource Discovery.