Customer relationship management database design case

Document directory

• 　

[Customer relationship management] [stakeholders] 1/upstream customers:
Supplier _ (commodity) 2/downstream customers:

Item Sales _ major customers

Item Sales _ Retailers

3/sales department:

Manager

Marketing Personnel

4/product:

Ordinary products

Other products

[Requirement Description] 1. Archive Management 1. Archive 1. Upstream customer archive 1. Supplier basic info. 2. Maintain association information between customer and commodity 3. Supplier image archive (...) 4/supplier anniversary Management 5/supplier correspondence files 6/supplier contact information 7/supplier contact information collection 2/downstream customer files

Indicator values related to evaluation:

Customer credit limit, license credit period

1. Basic customer information

Basic information:

(Basic features) Customer Code. Customer name. Customer abbreviation. Location. business nature. Legal Representative. Owner.

(Financial content) Tax No.. bank account. bank account

(Other content )...

2. Maintenance of business information

Customer level. Main business format. Network Access time. Business status. Order Date. Order Method {...}. Settlement method {cash. Check. Electronic Settlement }.

3/customer image information maintenance

Photo of owner, store image, and counter Image

4/customer anniversary Management

5. Customer Contact Information

Contact. Contact info. {Phone. Fax. Zip code. Email}

Name, gender, nationality, nationality, political outlook, cultural quality, date of birth, marital status, family members, social relations, personality, specialty, hobbies

6. Collect customer contact information

7. customer files

3/product archives 1/product Basic Information Management

Product Code, barcode, product name, specification, country of origin, province of origin code, packaging type, Manufacturer no., usage mark, mnemonic code, series name, provincial/foreign classification, name-excellent classification and product category.

2. product image Maintenance

Product pictures, packaging appearance pictures

3/product price management

Price Adjustment/wholesale price/retail price (reference) and cost price in Finance

Latest prices

Product Price Change records

4/Key Brand Maintenance

You can set the maintenance of Key Brands of the marketing party/manufacturer in the month.

5. Track brand Maintenance

You can set the brand to be tracked by month

4/Department structure file

Organizational structure of all departments in the system

5/company employee archive 1/Staff archive used in the system 2/Staff Working Condition collection 3/Staff family living condition collection 6/operator Archive

File of operating system personnel

7/system function files 1/System Function Definition 2/system permission Definition 3/User Role Definition 4/role permission division 5/user permission Division 6/role Group definition 7/group permission Division 8/log settings 1/system logon log 2/system operation log 3/version Update log 4/system alarm log 2/attribute File

[This document defines attribute files as basic files and value-added files, it also includes the attribute elements of future evaluation standards, as well as the attribute fields in the corresponding tables in data collection for management, data dictionary center management, and so on for centralized management.]

2/work reminder 1/Perpetual calendar 2/announcement board 3/internal message board 4/work reminder (daily reminder... ...) 5/employee identification 6/employee appointment reminder 7/today's customer event reminder (personal aspect) 3/Marketing Management

Provides a comprehensive presentation of sales-to-customer data and analysis results.

From the standpoint of an enterprise, slice analysis is performed on product sales by time. the following dimensions need to be created:

Time Dimension (month, quarter, year, specific)

Item dimension (Key Brand. Tracking brand. General brand. supply product brand .)

Regional dimensions (area. A. There are also salesclerks and Regional Managers)

Upstream customer dimensions (suppliers. Suppliers in a region)

Downstream customer dimensions (retailers. retailers with some common characteristics)

Information Content:

1/basic attributes:

Product. Time. Upstream customer. downstream customer. Sales Volume. Sales Volume. Sales profit.

2. associated attributes:

Marketer, marketing region, marketing department, marketing method.

Product Level. Product type. Upstream customer type. Upstream customer level. downstream customer type. downstream customer level ....

We require customers to be divided by region. Therefore, we need to obtain the corresponding peripheral data in the Regional Information Collection. Such data will be processed in <Information Collection>.

1. View marketing evaluation (Customer Manager/salesman) 2. query the customer's payment settlement status 3. sales work log management 4. Marketing Performance 5. Salesman analysis 4. Commodity Management

(It is an analysis conducted from the commodity-dominated perspective)

1. View product evaluation and analysis 2. Commodity correlation analysis 5. Customer Management [customer management content]

(Customer-oriented analysis)

1. View customer evaluation/view Customer Value Analysis

Different companies have different requirements. If you set the weight value of each indicator, you can calculate the value of each customer. Otherwise, you can only query the customer's overall evaluation score. You can also query the data of each metric through drilling.

1. View customer evaluation results 2. ranking analysis of customer Evaluation Levels 3. Structure Analysis of customer Evaluation Levels

Statistical analysis of the number of customers and evaluation scores by customer rating classification.

4. Customer contribution value analysis 2. View customer sales analysis

[Because the customer's sales are not completely or completely affected by many factors, the customer's sales are taken from the customer's important goods volume, it is an idealized situation to obtain the corresponding customer sales volume and sales amount.]

[Slice analysis of product sales by time from the customer's standpoint. The dimensions to be created here are:

Time Dimension (month, quarter, year, specific)

Item dimension (Key Brand. Tracking brand. General brand. supply product brand .)

]

[We require customers to be divided by region. Therefore, we need to obtain the corresponding peripheral data in the Regional Information Collection. Such data will be processed in <Information Collection>]

Query the total number of customer sales, total sales amount, and total gross profit. You can also query the sales quantity, sales amount, and gross profit of a certain type of products and individual brands through drilling.

You can perform drilling to achieve the same function.

[Sales view]

1/day sales data view

2/month sales data view

3. View quarterly sales data

4/year sales data view

5. View accumulated sales data this year

6. view the sales data of a specific date

[Analysis]

1. Customer sales Product Structure Analysis

2. Customer Business Analysis

3/Customer Business Environment Analysis

4. Customer Category Analysis

1. Customer sales volume

2. Customer sales amount

4/customer sales gross profit ranking analysis

6. Personalized Management

(Hierarchical management/management of specific customers in special circumstances)

1/product structure management 2/product supply price management (discount rate management) 3/purchase limit management (goods purchase limit) 4/Customer Care Management

For example, holiday condolences (Email, SMS, etc)

5. Customer settlement management

Reflect the customer's bank account assignment, such as the customer's monthly purchases, the number of bills, the number of successes, and the number of failures.

6. View related financial accounts and other such items... [Customer level management] (customer level standards)

(The reference is based on evaluation management)

Standards for customer levels

Validation of basic customer information items

View the customer level (view the impact of each evaluation result on the customer level)

Customer Level Correction (after the customer evaluation is complete, you can manually or automatically configure the method in the system parameters)

[Standards involved in customer Division]

This refers to the default classification of the customer system. In practice, the classification of the customer level can be customized, allowing the customer to customize the level standards. that is, there are multiple standard classifications in the system, and the default category includes the custom classification. in property management or in customer level management, the comparison between custom levels and default system levels is required.

Important Contribution to customer management

(View key customers/potential customers)

Common Customer Management

(View by common customers/view by potential General customers (downgrade by key customers and upgrade by warning customers ))

Alert Customer Management

(Alert customer view/potential alert customer view (that is, this customer can be eliminated ))

Potential customer management

6/supplier management

(From the perspective of suppliers)

1. View supplier evaluation/supplier Value Analysis

Specify evaluation indicators, such as contribution value, quality level, delivery capability, price level, post-sales service, and cooperation period, and rank suppliers by weight and scoring standards.

2. view the sales list of supply goods cards

(View and track Key Brand Groups)

3. view the supplier's purchase information. 4. Supplier care management. 5. Supplier payment settlement information.

Reflect the settlement of goods payment between the company and suppliers, and prepare data for analysis.

6. Supplier contribution value analysis

Reflects the total sales volume, sales volume, and profit percentage of the supplied products of the supplier.

7/view related financial accounts and other 7/Information Collection 1/Computer Data Collection

(Data interfaces, with other systems or from data warehouses)

1. Data Interface Definition

[From... Retrieve Data]

...

[Send data... ]

...

2. Data Extraction configuration (Data Source/data table) management 3/data interface control Relationship Management 4/Data Extraction Process Management 5/Data Extraction effectiveness 6/Data Extraction 7/data clearing 8/Data Extraction description 2/other data collection 1/industry information collection 2/domestic and international Important Information Collection 8/Service Center

(Determine the content to provide customer service)

Customer Complaint Center... Receive customer complaints

Customer Service Center... Resolve customer complaints

Customer Information Center... Record customer dynamic information (obtained from outside the system)

1. Maintenance of customer reception complaint information

Enter the customer's petition or telephone complaint information, including the record time, name, gender, age, contact number, complaint object, and complaint content.

2. Customer Complaint Handling

Relevant departments of the company handle the submitted complaint form, and enter the handling person, processing time, and handling opinions.

3/customer feedback

Feedback the customer's information to the customer in various ways.

4. Care topic Maintenance

If the company regularly performs customer care activities, it maintains the care subject and sets the care rules, so that the system can automatically generate care objects. Caring tasks include: Caring subjects, caring objects, task occurrence time, task execution time, and task executor.

5. Manage caring tasks

The task executor modifies the running status of the caring task and queries the execution status of each caring task.

6/customer survey

Satisfaction Survey

Enter the customer satisfaction survey form, including the survey time, the name of the commodity retailer under investigation, and the survey content.

7. Customer Service Rule Maintenance

Set the subject and time of the customer service activity and select the rules of the service object. The system automatically generates customer service tasks.

8/customer service activity execution

Record the time, content, and effect of the customer service activities.

9/situation evaluation

(It is the result of situation evaluation based on monopoly/sales/customer service and other basic conditions of customers)

1/evaluation management 1/manual evaluation of upstream customers 2/manual evaluation of downstream customers 3/marketing evaluation (Customer Manager/salesman) manual evaluation automatic evaluation 4. Manual Evaluation 2. Evaluation solution configuration 1. Evaluation Index Management 1. Evaluation Function Definition

Evaluation functions such as evaluation function number, evaluation function name, evaluation function description, and the corresponding evaluation instruction code, such as a evaluation function corresponding to the customer evaluation

2. indicator definition

Define the evaluation indicators of the evaluation object, such as the evaluation function number, Indicator Name, indicator code, indicator category, indicator superior code, indicator score, parent indicator, indicator description, whether or not the last indicator, weight, subjective and objective indicator identification, whether to enable and so on.

3. Indicator score Definition

Define the score of the last indicator, such as the evaluation function number, Indicator Name, indicator code, and indicator score definition of the indicator.

4/metric evaluation data interface

Define evaluation data interfaces, such as evaluation function number, evaluation object ID, Indicator Name, indicator code, evaluation data, and evaluation score

5. Evaluation Level Definition

The customer level definition includes the evaluation function number, level, and score range.

6. Rating

The evaluation object is scored, which can be divided into two types of weighted weights and no-weight ratings.

7. rating of evaluation objects

Rating the evaluation objects based on the evaluation results.

2/evaluation solution setting 1/upstream customers 2/downstream customers 3/product 4/marketing (Customer Manager/salesman) 10/trend prediction 1/next stage sales Prediction

Generate an expected table based on the analysis results after situation evaluation

1/product brand structure 2/customer sales amount 3/customer sales quantity 4/customer sales contribution value 5/customer sales gross profit 6/customer sales growth 7/product average price 2/next stage situation Estimation (described in text) 1/system analysis and Estimation

Customer evaluation level trend analysis

2/out-of-System Analysis Estimation

Obtain external information to generate a table, so that the computer can make an evaluation standard

3/prepare a plan for the next phase 1/purchase plan

Result The sales prediction and Situation Estimation above are used to get a solution.

2. Sales Plan Management

(Company/department/Customer Manager/salesman/customer) plan formulation

(Company/department/Customer Manager/salesman/customer) Plan Completion

11 help management 1/password Modification

The operator can modify the password.

2/system help

Operating system description.

3/about

A brief description of the system.

[Evaluation indicators] 1/upstream customers

2/downstream customers 3/marketing (Customer Manager/Marketing Personnel) 4/product [function description] 1/internal data extraction 2/External Data Acquisition 3/automatic evaluation system 4/Data Analysis Presentation 5/Next Step Development Trend 6/Customer Service Center 7/ system permission management appendix: download the visio Version of the database relationship and dictionary -->