An Overview Of The Customer Relationship Management

Published Date: 02 Nov 2017

Introduction

                                                                  Retail is the sale of goods and services from individuals or businesses to the end-user.  Retailers in India can be classified into two segments, organized retailers and unorganized retailers. Organized retailers display definite pattern in making business strategies. Retailing is integral part of Indian economy as it contributes significantly to GDP of India. It implies tremendous competition in this field and hence retailers can’t rely only on marketing to maximize their profit and survive in competition. Marketing can bring you new customers but another way to generate more revenue is by offering incentives to existing customers make them happy so that their loyalty towards your organization will remain intact .This can be achieved through Customer Relationship Management-CRM. Here you basically keep track of customer activities, ponder over them and then strategize so that probability of success shoots up.

Capillary Technologies is a leading provider of cloud-based software solutions that help retailers to intelligently engage with customers through mobile, social and in-store channels. Cloud technology which is extremely powerful technology(centralized approach) today helps to mount entire data on same server and hence helps process of analysis and processing as well. Capillary’s cloud-based platform powers social CRM solutions, end-to-end customer engagement and clienteling and loyalty.

                                      Customer feedback is extremely vital in understanding current consumer inclinations and tendencies. But another way to understand what is preferred, what is selected and what is rejected by consumers without feedback is to analyze available customer data. But just data is not enough, it has to be processed to acquire business insight out of it.

                                                                Data Analysis is one of the hottest sectors in job market today. In simple words data analysis is drawing useful and relevant information from huge data which can be used in formulating business strategies. It tremendously facilitates decision making as there is data to back up your decision. Refining available data and then further analyzing the data helps you in figuring out current client tendencies, strengths of the organization, weaknesses, future opportunities and plausible business threats. As the name suggests, data analysis is eventually working on databases and obtain required information by firing intelligent queries in cost and time effective manner. In order to achieve this prime goal, Business Intelligence Tool is designed. Business Intelligence Tool(abbreviated as BI Tool) is often designed by organization itself so that exact requirements of the organization can be fulfilled and maximum utilization of organization resources can be achieved. Self-developed BI-Tool is called as Inhouse BI Tool.  There are general business tools too such as Microsoft Excel which can be used for same purpose but have size limitations and can’t handle mammoth data.

Business Tool

Software development is a very exhaustive process.Basic question is why should an application software such as BI Tool be developed if available tools and technologies can provide you required output? Two main requirements are satisfied by doing this. First is one of the major functions of any BI tools is reporting . So reporting can be done very properly and accurately by making use of BI Tool. Second requirement is it reduces redundancy in report making. So the vision behind development of BI Tool is.

C:\Users\piyush.deshmukh\Desktop\Picture1.png

Data is captured by using capillary’s another product intouch which is integrated with POS(Point of Sale) or using FTP server. This Data is refined in order to standardize it for analysis. Inhouse BI tool of capillary is used as a platform for configuring and generating reports. These reports are used to draw business insights, formulate strategies and for several other purposes. MySQL is the database language used for query writing purpose. Analysis can be done by using simple database interfaces such as phpMyAdmin but writing query each and every time is a very tedious job and its not possible either to share queries with everyone in company, there has to be a some central mechanism to avoid this repetitive kind of work. Moreover queries are static queries. For example to find out how much revenue is generated by a particular store in a week and how much revenue is generated by a particular store in a month, you will have to write two different queries although there is hardly any difference in those queries except the duration part. Main purpose of any software is to automate work by implementing algorithm. Same is the idea used here. SQL queries are written in a generic fashion so that some of the variables such as stores or dates can be inserted by user but the general structure of query would remain same similar to the algorithm. For an external user BI Tool is a black-box in which he can input required variables to get desired output. Only difference between software coding and report generation is the language, in both cases you input variables and you get the desired report. Using this you can dynamically change names of the tables in query or primarily modify filters in MySQL query.

How a new report can be added in BI Tool?

There are three parts of report.

1.Variables- Variables are the dynamic parts of the query which are either inserted or selected from the available options by the user. Very common variable type is start date and end date so that you can generate report for the data captured between these two dates. Variables are written in this fashion {{variable1}},{{variable2}} in query that indicates these are dynamic parts of the query which will be changed during detemplatization.

2.Stage- Report is written in stages. Report could be a single stage report or multi stage report. Stages are introduced for two reasons. If variables are dynamically dependent on each other, such as I want to run report for some specific stores in particular city, only name of the stores in that particular city should appear in selection box. Other reason is trivial one, if stage is getting too crowded because of overuse of variables so the stage is broken between two two smaller stages.

3.Queries- Queries are written in templatized manner in BIT. These queries are detemplatized during execution and then run on the backend database to attain desired result.

4. Function- Functions are used in queries (tempelatized) in order to place variables properly in standard SQL format during the process of detemplatization which makes the query compatible to be fired on the backend database.

Basic advantage of BI Tool is existing reports are reusable just like software reusable modules. When you want to create a report that is slightly different existing report, it can be configured from existing report with slight modification, you don’t have to write another report from scratch.

Another important feature of this BI tool is it has in memory join concept that joins query results with same primary key horizontally and query results with same report name but different primary key vertically. So inspite of complexity of backend database, results with same primary key are displayed together which plays very crucial role in analysis as you get complete report in form of a single sheet.

Sample Query- Templatized query(Query written on BI Tool)

select

(function1(bill_banding_index,bill_banding)) as Bill_Size,bills,sales,round(sales/bills,0) as ABV

from

(

select

interval(bill_amount,function2(bill_banding)) as bill_banding_index,

count(distinct a.id) as bills,

sum(a.amountofbill) as sales

from

transactionaldata as a,nameofstores as s

where

date(a.dateofbill)>={{startdate}}

and date(a.dateofbill)<={{enddate}}

and a.store_id=s.store_id

and s.storename in ({{storename}})

group by 1

)a

group by 1

order by bill_banding_index asc

Startdate, Enddate and Storename are variables in query. Among these three, Startdate and Enddate are variables which will be inserted from user as user must have freedom to choose whichever dates he wants to but for in case of stores he should be able to choose only stores of that particular brand. So variables are defined by keeping these constrains in mind. Variables are written in this format {{variable1}} and {{variable2}} in templatized query (BI Tool Query).

Function1 and function2 are functions used in templatized query, in detemplatized query it can be seen that function1 is broken into case when statements and Function2 breaks list into array.

This is a single stage function as there are no dependencies in between variables.

Database Query- Detemplatized Query

select

(Case

When bill_banding_index=0 then '<1K'

When bill_banding_index=1 then '1K-2K'

When bill_banding_index=2 then '2K-3K'

When bill_banding_index=3 then '>3K'

else null end) as Bill_Size,bills,sales,round(sales/bills,0) as ABV

from

(

select

interval(bill_amount,'1000','2000','3000') as bill_banding_index,

count(distinct a.id) as bills,

sum(a.amountofbill) as sales

from

transactionaldata as a,namesofstores as s

where

date(a.dateofbill)>='2012-01-01' and date(a.dateofbill)<='2012-05-31'

and a.store_id=s.store_id

group by 1

)a

group by 1

order by bill_banding_index asc

Detemplatized query is a query that runs on backend database which is a normal query obtained after detemplatization of BI Tool query i.e. query that is obtained after processing BI Tool query.

Although the platform(BI Tool) is new and hence we have to keep the standard practices of query writing in mind, ultimately standard SQL knowledge is used in this. Powerful features of SQL such as aggregation functions and other functions for each and every data type play a vital role in generating Business KPIs(Key Performance Index). To write intelligent queries good knowledge of databases is essential and the basic aim of computer science studies is problem solving using smart algorithms. So as a computer science student, main challenge is to design a query when you know what kind of result you want to achieve and things you have to take care of are, backend database schema, MySQL limitations and construction of query to give you result in minimum time as business tools are designed with to reduce time and redundancy both and if it these constraints are not satisfied then purpose of developing a business tool will be failed.

Acknowledgements

I would like to thank Prof. Manish Kumar Gupta, BTP Coordinator of DA-IICT. I would also like to thank Prof.Jaideep Mulherkar, On-Campus Mentor of DA-IICT for guiding me time to time in my off campus B.Tech Project. I am indeed very grateful to Mr. Sridhar Bollam(VP Analytics) and Mr. Jyotiska Bhattacharjee (Head of BI Tool Team and my Off Campus Mentor) of Capillary Technologies Pvt Ltd for giving me opportunity to complete my B.Tech Project under Capillary Technologies Pvt Ltd and for co-operating me completely during my BTP.