The most successful businesses understand the market that they serve. They understand that talking with a customer about their needs is more effective than babbling about their own product or service. We can use the marketing perspective to analyze actual customers, potential customers, business competitors, and society at large. Although we have a fair amount of internal data about our own customers, we also look for other data resources to examine other market variables.
One of the internal data sources that we can exploit is the Customer Relationship Management (CRM) system. This includes data about current customers that isn't necessarily related to actual sales, such as visits, sales opportunities, and service calls. It also stores sales opportunities with potential customers. In addition to a CRM, we can also use the same sales data from Chapter 2, Sales Perspective, to better understand our current customers' purchasing behavior.
Depending on the company, we may also find data that is useful from external sources. If the business is actively involved in social networks, then we can gather market data from Facebook, Twitter, or LinkedIn. We can also purchase data from market research companies or download free, public data from several governmental and nongovernmental organizations.
In this chapter, we will review the following topics while we create the QlikView marketing perspective:
- Marketing data model
- Customer profiling
- Market analysis
- Social media analysis
- Sales opportunity flow analysis
Let's get started with a look at how we combine the CRM data with the existing sales data model from Chapter 2, Sales Perspective.
A CRM system serves several functions. Along with keeping track of our sales process and the level of customer service, it also gives us first-hand data about our customers and leads. It contains an evolving event called a sales opportunity that, in itself, contains various discrete events, such as visits, and calls, and sales quotes. All this data is important first-hand information about our market. This is especially true in the case of sales quotes, which are documents that are similar to invoices and give us an idea what customers are interested in buying, how much they plan to purchase, and at what price. An opportunity may also include information about its origins, competing offers, and any reason why we failed to convert it into an actual sale.
A CRM system also tends to add more information to the customer catalog, such as demographic information. If customers are people, then we may gather data about their age, sex, education level, income level, marital status, and so on. Otherwise, if our customers are businesses, then we may gather data about the industry group that they belong to along with the number of employees and annual revenue. We may also add more detailed geographical data, such as latitude and longitude.
We can find the marketing perspective container called 1003.Marketing_Perspective in the Chapter 4, Marketing Perspective, folder of the book's exercises. Let's load this container into the QDF in the same way that we did in Chapter 2, Sales Perspective. Once we've transferred the container, let's open the marketing perspective called Marketing_Perspective_Sandbox.qvw in the 1.Application folder and see how sales opportunities and other marketing data combine with the previous sales data model.
As you can see, the following data model is quite similar to that of the sales perspective. We've added some additional fields in the Facts table to help us measure the following events:
- Sales opportunities
- Sales quotes
- Customer-related activities, such as visits and calls
Sales quotes and activities are discrete events that occur on a given date. However, sales opportunities are evolving events that go through several stages. Each stage represents a step that we expect to perform in every sales process. For example, going to the first meeting, sending a sales quote, and negotiating the final sales terms are common steps in a sales process.
In our analysis, we want to know how the process is evolving. More specifically, we want to identify its current step along how it progressed through past steps. We use intervalmatch() in the load script to link the start and end dates of each step with the corresponding discrete dates in the MasterCalendar table. A side effect of using intervalmatch() is the existence of a synthetic key table in the data model. A synthetic table is QlikView's way of linking tables that share more than one key field. Usually, we avoid using these tables as they may affect the performance of the data model, but in this case we leave it in the data model. Any attempt to eliminate the synthetic key table created by intervalmatch() often nullifies its purpose or causes the related tables to grow too large.
Finally, we add business demographic data and geographical data to the customer catalog along with a related table with public census data that helps us look for new markets that share the same attributes as the business's current customers.
Note
We can download the latest US business census data from http://www.census.gov/. The census data that we use in this data model is from http://www.census.gov/econ/susb/.
Apart from this additional data, we reuse many of the dimensions and metrics that we saw in Chapter 2, Sales Perspective. For example, Customer, Sales Person, and Item also exist in this data model. Let's take a closer look at some of the new dimensions and metrics that pertain to marketing.
Now that we have a marketing data model, let's create current customer profiles and discover where we can find similar businesses according to the census data.