Customer Relationship Management

Operational and Analytic CRM

While working with CRM we often see that a good CRM implementation needs to address the requirements of both the analytical as well as operational objectives. A successful implementation often relies on the interactions with the customer for data collection and then leveraging that data for analysis.

Operational CRM often is related to the processes that are related to sales, marketing, operations and services. This is often an end to end solution beginning from the initial contact, to the final payment and the ensuing customer service. CRM succeeds in integrating all the key customer metrics and processes.

Business Intelligence software tools are used to transform the raw data that is gathered from multiple meetings with the client into useful information and provide insight to the stakeholders, to improve the decision making timeliness and accuracy.

The Essential Problem:

Every management faces the quintessential problem, i.e to consistently make the right strategic and operational decisions in a timely and accurate manner. To make the right decisions, one needs to have data that is not incomplete, inaccurate or irrelevant.

The Solution:

Integrating DW/BI with CRM and essentially using it as a repository to first collect and then integrate all the customer related information, not only from the operational systems, but also the analytic systems as well as external sources. The goal is to make the DW the foundation of the CEM process. Once the data has been accumulated and integrated, Analytic CRM can be enabled. The advantage of this is that the results can be measured and the customer data can be leveraged to identify cross-sell opportunities, identify inefficiencies and improve customer retention.

The following steps should be followed to successfully implemented to deploy a CRM system integrated with BI.

Ø  Identify key requirements/ objectives: One of the first steps is to identify the requirements of the stakeholder. Any relevant and first-hand information that can be obtained holds relevance to a successful deployment.

Ø  Resource Allocation: IT participation plays a huge part in implementing BI solutions. Tasks like data cleansing, transfer, consolidation and integration require skill and expertise. The technical resource pool in the team can contribute effectively in these tasks. Meanwhile, focus should also be on business objectives providing operational insight. Business intelligence is best achieved by leveraging skills, from both the IT staff as well as business staff.

Ø  Defining metrics: Key performance Indicators (KPIs) need to be identified as the defining characteristics for a successful implementation. KPIs often drive performance selecting it is depends on the business plan, budgets and the objectives. Customer service is sure to measure strategic metrics such as customer satisfaction and retention. Its important to start with fewer and more relevant metrics and increasing progressively.

Ø  Determine sources of data: For CRM analytics, the bulk of data is likely to reside in the central repository. However, additional sources may exist, like a Content Management System(CMS), Enterprise Resource Planning (ERP) system and some transactional applications.

Ø  Determine the tools to be used: A CRM analytics or operational platform often includes data visualization technologies like dashboards. Scorecards and OLAPs are effective tools for effective querying, reporting and other analysis.

Conclusion:

Information is the fuel that power's intelligent organizations. However, management's traditional use of reviewing static, generic, historical reports which usually only pull data from a single source is no longer sufficient in competitive markets. These reports are designed for passive viewing and do little to proactively advance business initiatives in the shortest cycles possible.

BI solutions are increasingly popular, and beginning to be embedded with traditional and on-demand CRM software solutions. These tools offer increasing value when extended throughout the business. Strategic, Data driven projects such as annual business plans, budgets, workforce analysis and more can strongly benefit from automation and analysis with BI solutions.


References:

www.wikipedia.com
The Data Warehouse Tooklit 3rd Edition
www.kimballgroup.com
www.crmsearch.com
www.google.com

Business Intelligence enhancing Retail Industry

In the current age of Information technology, the retail and wholesale industries have never been more fragmented. From discount warehouse clubs to upscale specialty stores, corner convenience chains to mega e-commerce sites, buyers are better informed, less loyal, and highly cautious about discretionary spending.  Organizations are always striving to balance product, price and service sensitivities, while protecting margins and trying to ensure inventory availability while maintaining reduced carrying costs. Now more than ever information must be leveraged in new ways to gain competitive advantage and renew stability in these uncertain times. And that’s where BI plays a huge part.

Retail Industry Overview:

Increasing competitors: There has been an explosion of products, markets and stores. There is also always the constantly looming threat of new entrants, who look to take away the market share.

Increasing Customer Expectations: There is the ever increasing demand from customers that have to be met. Innovation in technology has brought the services and the products to the fingertips of the customers.

3.     Customer/Supplier bargaining power: The retail industry has to often deal with the pressure of the customers or suppliers influencing the pricing decisions of products.

Retail Industries use the following BI framework for establishing and integrating their Hardware and Software components.

Retail Industries use the following BI framework for establishing and integrating their Hardware and Software components.

The source systems capture the data from the daily transactions and store them. The ETL layer is responsible for data extraction, cleansing, staging, transformation and loading. The Data Warehouse stores the data depending on the needs of the business. The BI Layer is responsible for the number crunching by applying business logic and generates relevant metrics.

Evolution of BI in Retail Industry

The evolution of BI in the retail industry is staggering, from static reports to dynamic dashboards. Insights can be given into data with root cause analysis to determine the functionality and the effectiveness of decision making. These advancements in BI with respect to retail industry has helped in providing the following advantages,

Aligning the business with client needs: Identifying opportunities and enhancing decision making.

Gaining competitive advantage: Analyzing the data and generating reports to increase business functionality and effectiveness.

Resource allocation optimizations: The technical and the financial data needs to be integrated with the operational data in order to maximize the resource allocation and enhance productivity.

In the retail industry, using Business Intelligence (BI) is an obvious choice. But before that, one must understand buying behavior, customer requirements, exact price points for products, shipping and inventory timing and changing trends. Getting the right product on the store shelves at the right time, for the right price is the essence and what retail is all about.

References: http://www.elegantjbi.com/solutions/industry_bi_for_retail.htm

                  www.wikepedia.com

                  http://www.slideshare.net/shwetaj/business-intelligence-in-retail-7642339

Enterprise Data Warehouse : First look

An Enterprise Data Warehouse is a centralized data warehouse, which serves the entire enterprise. It contains a large database repository containing old and current transactions of an organization. The history, or the emergence of the Enterprise Data Warehouse, came about to address the need for data which is increasing exponentially in the last few years. There has been an opportunity and a necessity for Business Enterprises to stay updated with current advancements in technology as well as the industry competition.

The main rules while modeling an Enterprise Data Warehouse are

1. The grain should be clearly defined. The goal of the warehouse design is to essentially come up with a clear representation of the Enterprise’s business data as well as the rules that govern the organization. The more departments and functions of the enterprise, the more definite and defined the Warehouse should be.

2. An enterprise can never contain just a single functional area. Therefore, the Enterprise Data Warehouse should contain multiple areas which may be related to Marketing, Sales, HR, Retail etc.

3. The Enterprise Data Warehouse almost always contains denormalized models like Star or Snowflake, but this is generally not a rule. Many database designers also prefer the normalized design. The key point to note here is the emphasis on flexibility, performance and speed.

4. Every Enterprise Data Warehouse infrastructure should always be able to handle unforeseen and unexpected situations which might potentially result in a loss of income. The emphasis should be on availability, which means that disaster recovery features and security should always be in place.


5. An Enterprise Data Warehouse should always be scalable across many dimensions. This essentially means that it should be able to handle the exponential growth of data as well as increasing complexity with the development and growth of any Business Enterprise.

In this age of Information Technology, all processes are data driven. Hence a successful Enterprise Data Warehouse should be adaptable to change and focus on the above mentioned features.

References : Kimballgroup.com
Wikipedia

Of keys and dimensions!!

After settling down into a routine (classes and quizzes), its now time to dig a little deeper into some concepts. Similar to what was dealt with in class, two of the main concepts that I feel need some discussion are keys and dimensions ( having considerably spoken about facts in my previous post ). I will list some of the types of keys and dimensions that we may come across with examples.

1. Junk dimensions: We will frequently encounter business processes that may involve a number of miscellaneous attributes and indicators (yes/no) along with some flags. While we would normally like to create separate dimensions for each of these, it makes it easier to manage if a single dimension is created and all the data is entered into it.
For example in a transaction involving a item delivery, we might have several indicators like packaged, shipped, delivered, received, refunded etc. All of these attributes can be put into a junk dimension.

2. Outrigger dimensions: Very simply put, this is nothing but a dimension which has a reference to another dimension. For example car models dimensions can reference a separate dimension which represents a specific type of car. We should normally restrict the use of these dimensions and both of these should be in the fact table as separate foreign keys.

3. Conformed dimensions: When the attributes in separate dimensions have the same column names and the same meaning. A conformed dimension can refer to multiple tables. Two tables are said to be conformed if they are identical, or if one is the subset of the other. For example, in most organizations, the date dimension is the most common conformed dimension.

4. Shrunken dimensions: Shrunken dimensions are essentially conformed dimensions which contain a subset of rows and/or columns of a base dimension. This normally occurs when two dimensions are at the same level of detail but one contains only a small portion (subset) of the rows of the other table.  For example, a month dimension could be a shrunken dimension of a date dimension.

Now having spoken about some of the dimensions that we would encounter, let us discuss some keys.

1. Degenerate dimension keys: We may face circumstances where a dimension key has no dimension table associated with it. This is most commonly seen when a fact tables grain is a single transaction. For example, if we have an invoice with no content associated with it, but we still need to model a particular invoice no as a key for the fact table.

2. Dimension surrogate keys: In essence, surrogate keys are those keys that join the dimension tables with the facts. However, sometimes, a single key can be used by multiple instances of the same entity. Therefore this key cannot be used since, over a period of time, there will be multiple rows in the dimension table that use this single key. Therefore, an integer system is used to keep track of keys, starting from 1, every time a new key is assigned. For example, when we model companies who are trying to integrate information from different sources, or after mergers and acquisitions, we need to use surrogate keys because we might have conflicting keys across dimensions.

3. Natural keys: A natural key is a key that exists in the real world. Where this is different from surrogate keys discussed above is that the surrogate keys have no meaning outside the database environments, while a natural key does. For example, a persons SSN could be a natural key.

4. Durable keys/ Durable Supernatural keys: Time to introduce a new concept (dimension ?) in data warehousing, time. A durable key is one that uniquely identifies a record over time. When we integrate data from multiple sources, the natural key might change. The way to tackle this is having keys in a format that is totally independent of the process model. For example, these could be simple integers whose values begin from 1.

I believe with these concepts we will gain a foothold in the vast data warehousing universe. in the words of Ronald Case, a Nobel Prize Laureate, torture the data, and it will confess to anything.

Reference : Wikipedia
                  http://www.kimballgroup.com/

           

Scaling the heights of BI, one grain at a time.

My first step into the world of Business Intelligence, begins with the grain. No amount of emphasis can be made on the importance of this aspect. The Grain, (The Holy Grain as I would like to call it), is the single basic definition of the rows in a fact table, and by extension, the dimensions and the entire star schema.

For example when considering a purchase made at a vendors store, a decision needs to be made, whether the grain, and by extension, the fact table, needs to model the transaction, or the individual purchase of the item. Now, if we were to go with modelling the transaction as a whole, our fact table entries would contain all the specifics regarding the particular transaction including the bill amount, date, no of items purchased etc. On the other hand, if we were to model the individual purchase, we have on our hands, a whole bunch of fact tables which would need to be consolidated for every individual item that is purchased.

The above example is clear proof of the fact that, the choice of the grain can affect, not only how we design the fact tables and the dimensions, but also can alter the entire definition of the system.

Now that we have the grain and its atomicity established, let us discuss facts and dimensions. On a high level, a fact is nothing but real world data that has been recorded, in a business transaction or a business process. Each and every row in the fact table corresponds to the data that can be measured and related to that process. Generally, the data in the fact table is used to make calculations and manipulations in order to generate reports.

A dimension can be thought of as an extension to the fact table in that they contain all the information corresponding to and which describes the particular fact that is related to it. A very common dimension in most of the star schemas is the date.

Now that the concepts have been drilled into my head, I feel confident enough to tackle all the other challenges thrown at me. Now that the first steps have been taken, there is no turning back.


References : The Data Warehouse Toolkit 3rd Edition
                    Wikipedia
                    Class Slides