Analysis of Sales Pattern Determination System and Drug Stock Recommendation

Jurnal Ilmu Komputer VOL. XII No. 2

p-ISSN: 1979-5661

e-ISSN: 2622-321X

Analysis of Sales Pattern Determination System and Drug Stock Recommendation

Made Dinda Pradnya Pramita^a1, Made Sudarma^a2, Ida Bagus Alit Swamardika^b3

^a Postgraduate Study Program in Electrical Engineering, Faculty of Engineering, Udayana University

¹[email protected] ²[email protected] ³[email protected]

Abstract

The tight competition in the pharmacy industry, requires pharmacy owners to develop strategies in increasing drug sales. One of the strategies carried out is to analyze patterns of drug sales and determine drug stock recommendations based on sales transaction data. Based on this, an application was built to determine the pattern of drug sales and drug stock recommendations by using a modified Apriori Algorithm and Triple Exponential Smoothing Method. Apriori algorithm modification is used to overcome the problem of large amounts of sales transaction data, thus minimizing the time in the database scan process. Triple Exponential Smoothing method is used in determining drug stock recommendations based on sales patterns that have been generated to prevent excess or lack of stock. Application testing techniques used are performance testing, lift ratio and accuracy testing. The research resulted in a sales pattern that has a strong association rule and time analysis using Apriori Algorithm modification that is faster than using a traditional Apriori Algorithm and the percentage error value of drug stock recommendations by 31.84%.

Keywords: Sales Pattern, Stock Recommendations, Apriori Algorithm Modification and Triple Exponential Smoothing

• 1.    Introduction

Utilization of information technology is widely used in the business world, especially in the pharmacy industry. Anggita Pharmacy as one of the pharmacies that utilizes information technology in determining sales pattern and determining drug stock recommendations that aim to increase drug sales results. Determination of sales patterns and drug stock recommendations can be done by utilizing the Anggita Pharmacy sales transaction data. The resulting sales pattern can be used as a reference for analyzing types of drugs that are often purchased together with other drugs so as to overcome the problem of excess and shortage of drug stocks.

Based on these problems, an application was built to determine the pattern of drug sales by using apriori algorithm modification. Modification of apriori algorithm aim to overcome the problem of repeated database scans every time the iteration process. Database scan process in apriori algorithm modification is done by selecting several transaction data. The selection is made by comparing the support values of each transaction item, then selecting items that have a minimum support value and selecting transaction data that only contains selected items so that the database scan time will be faster than using traditional apriori algorithms.

Data mining association analysis is a method used to determine sales patterns. Association analysis or often called Market Basket Analysis is a data mining technique to find associative rules between a combination of items in a relational database [10].

Research on determining sales patterns using the apriori algorithm has been conducted by [12] which states that “Data Mining with apriori algorithm has a weakness must scan the database each time iteration, so that for very large database requires a long time.”

The pattern of drug sales generated used as a reference in determining stock recommendations by using the Triple Exponential Smoothing Method. The Triple Exponential Smoothing method is used to determine the minimum amount of drug stock that must be available at Anggita

Pharmacy. Sales transaction data used in determining sales patterns and determining stock recommendations are transaction data for the last three months of the analysis process. The reason for choosing three-month data is because in three months there are several different seasons so that more transaction data combinations will be generated.

• 2.    Reseach Methods

Application development of determining sales patterns and determining drug stock recommendations using a prototype method consisting of seven stage as follows.

• a.    Requirement Collection

Requirement collection is done by using interview techniques. Interviews were conducted with pharmacists as user of the system. The active role of pharmacists as system user is needed in developing applications for determining sales patterns and stock recommendations, so that the system formed can meet the needs of the users.

• b.    Building Prototype

The second stage is the stage of designing the system of sales patterns and drug stock recommendations. Design prototype system using use case diagrams and database designs int the form of Entity Relationship Diagrams (ERD).

• c.    Prototyping Evaluation

The result of the designs that have been made are discussed with Anggita Pharmacy so that they can meet the needs of users. If it is suitable, it will be repeated to stages 1, 2 and 3.

• d.    Encoding the System

The approved design results are converted into program code using the Java programming language with Netbeans and database design is implemented with MySQL.

• e.    System Testing

System that have been built are tested using a number of testing techniques, namely lift ratio, performance testing, black box testing and white box testing.

• f.    System Evaluation

System evaluation is the evaluation stage carried out by the user of the system that has been built. If the system that is built is appropriate, it will proceed to stage 7, but if it is not appropriate, it will be repeated to stage 4 and 5.

• g.    System Implementation

The system that has been evaluated and meets user needs will be implemented at Anggita Pharmacy. This system is expected to help in analysing sales patterns and determining drug stock recommendations.

• 3.    Result and Discussion
  
  3.1.    Drug Sales Transaction Data

Determination of drug sales patterns and drug stock recommendations using drug sales transaction data at Anggita Pharmacy in 2018. Table 1 shows drug sales transactions data used in the analysis.

Table 1. Drug Sales Transactions Data in 2018

Number	Transactions Analysis Data	Forecasting Time	Amount Of Data
1	January, February, March	April	1472
2	February, March, April	May	1345

3	March, April, May	June	1215
4	April, May, June	July	1243
5	May, June, July	August	1558
6	June, July, August	September	1963
7	July, August, September	October	2047
8	August, September, October	November	2081
9	September, October, November	December	2025

Sales transaction data used in the analysis of drug sales patterns was the last three months of data analysis process. Figure 1 shows the number of sale patterns based on drug sales transaction data. The graph shows the most number of pattern in December. The minimum support value used is 10 and the minimum confidence is 0.5.

Number Of Drug Sales Patterns Each Month

700

600

■ Number Of Patterns

500

400

300

200

100

0

Figure 1. The Number Of Sale Patterns Base On Drug Sales Transaction Data

• 3.2.    Flowchart Analysis of Sales Patterns & Stock Recommendations

Figure 2 is a flowchart of the process of determining sales patterns and stock recommendations using apriori algorithm modification. The first step taken is to input drug sales transaction data, input a minimum value of support and minimum confidence. The second step is to calculate the support value of each item. Items that have a value below the minimum support will not be used for the formation of further combinations. Based on the combination formed, select the item that has the smallest support value and determine the support value of the formed combination. Repeat this process until no combination of items can be formed again. Based on the pattern that has been formed, determine that value of confidence. The combination that is formed will be deleted if it has a confidence value below the specified minimum confidence value. After that determine the final association rule value and multiply the support value and the confidence value. The results of the formed drug sales pattern will be used to determine drug stock recommendations. This aims to prevent the excess or shortage of drug stocks in Anggita Pharmacy.

Figure 2. Determination of Sales Patterns with Modified Apriori Algorithms

• 3.3.    Results Of Determination of Drug Sales Pattern

Table 2 shows the results of the analysis of determining the pattern of drug sales in April 2018 based on sales data for January, February and March 2018. The value of support shows the percentage level of dominance of an item-set of drugs from all sales transactions the confidence value indicates the percentage of the relationship between the two drug items conditionally. The value of sup x conf is the final percentage value of the sales pattern formed.

Table 2. Results Of Determining The Sales Pattern Of April Months In 2018

Number	Pattern	Support	Confidence	Sup x Conf
1	If you buy an adult infusion set then buy a three way stop jms tail	6,59%	89,25%	5,88%
2	If you buy alcohol swabs, syringe 5 cc one med then buy non-sterile sensi gloves	3,64%	63,90%	2,33%
3	If you buy a terumo adult infusion set, a 5 cc terumo syringe then buy a three way stop jms with a tail	2,28%	97,11%	2,21%
4	If you buy a 3 cc one med syringe then buy a 5 cc one med syringe	5,68%	54,90%	3,12%

5	If you buy alcohol swab, a 3 cc syringe terumo then buy a 5 cc syringe terumo	4,25%	65,87%	2,80%
6	If you buy alcohol swab, 3 cc syringe terumo, syringe 5 cc terumo then buy non sterile gloves sensi gloves	3,29%	77,53%	2,55%
7	If you buy a terumo adult infusion set, a 3 cc syringe terumo then buy a three way stop jms with a tail	2,54%	92,55%	2,35%
8	If you buy a three way stop jms with a tail then buy a 500 ml infusion of sanbe	5,70%	50,58%	2,88%
9	If you buy alcohol swabs then buy non-sterile sensi gloves	12,75%	72,80%	9,28%
10	If You buy an adult pink ID bad wcard then buy a three way stop jms with a tail	3,53%	89,43%	3,16%
11	If you buy non-steril sensi gloves then buy alcohol swabs	12,75%	85,43%	10,89%
12	If you buy alcohol swab, syringe 3 cc one med then buy syringe 5 cc one med	3,57%	80,04%	2,85%
13	If you buy iv catheter no 20 g terumo then buy a three way stop jms with tail	3,53%	89,43%	3,16%
14	If you buy alcohol swab, a 3 cc syringe terumo then buy a non-sterile sensi glove	3,41%	84,37%	2,88%
15	If you buy a 5 cc one med syringe then buy a 33 cc one med syringe	5,72%	50,58%	2,89%
16	If you buy an adult infusion set terumo, iv catheter no 20 g terumo then buy a three way stop jms with tail	3,49%	98,01%	3,42%
17	If you buy a 5 cc one med syringe then buy an alcohol swab	5,70%	50,58%	2,88%
18	If you buy iv catheter no 20 terumo then buy an adult infusion set	3,50%	89,45%	3,13%
19	If you buy alcohol swabs, a 5 cc syringe terumo then buy non sterile sensi gloves	6,58%	80,69%	5,31%
20	If you buy a three way stop with a tail jms then buy non sterile sensi gloves	6,23%	83,70%	5,21%

• 3.4.    Lift Ratio Testing

Table 3 shows the results of the monthly lift ratio testing based on the analysis of sales transaction data for the last three months of the analysis process.

Table 3. Results Of Life Ratio Test Every Month

Number	Month	Lowest Lift Ratio Value	Highest Lift Ratio Value
1	April	1,45	46,01
2	May	1,97	28,50
3	June	1,65	17,83
4	July	1,30	44,34

5	August	2,15	30,81
6	September	3,11	89,15
7	October	4,05	77,62
8	November	5,10	73,15
9	December	5.01	59,47

The lift ratio test results on the resulting sales pattern have strong association rule because they have a lift ratio value of more than 1. The highest lift ratio value was found in September. The higher the lift ratio value, the stronger the possibility of an item being purchased together with other items

• 3.5.    Value Of Accuracy Of Drug Stock Forecasting

Table 4 shows the percentage of system error rates for forecasting drug stock. The test results show the percentage of system error rate in making stock forecasting is 31.84% so the percentage of accuracy in making stock forecasting is 68.16%.

Table 4. Stock Accuracy Testing

Number	Month	MAPE
1	April	21,27%
2	May	39,9%
3	June	32,3%
4	July	35,15%
5	August	25,30%
6	September	34,54%
7	October	31,85%
8	November	24,32%
9	December	41,94%
Rata - Rata		31,84%

The high level of error in the stock recommendation process is because the MAPE testing method does not use interval forecasting values but uses specific forecasting values. It is also influenced by the types of drug units that differ between forecasting and sales transactions.

• 3.6.    Performance Testing of Determining Sales Patterns

Table 5 is the result of comparing the time needed by the apriori algorithm and the apriori algorithm modification in determining the sales pattern based on the sales data of the last 3 months of the analysis process.

Table 5. Performance Testing Results Determining Medicine Sales Pattern

Number	Month	Apriori Algorithm	Apriori Algorithm Modification
1	April	1030 s	925 s
2	May	1011 s	921 s
3	June	899s	887 s
4	July	937s	900 s
5	August	1270 s	1108 s
6	September	1459 s	1155 s
7	October	1491 s	1174 s
8	November	1521 s	1189 s
9	December	1468 s	1159 s
Rata – Rata		1228 s	1047 s

Based on the results, the time needed for apriori algorithm modification in determining sales patterns is 1040 (17 minutes) while the apriori algorithm takes 1225 s (20 minutes) in

determining drug sales patterns.

• 4.    Conclusion

Based on the research that has been done, the following conclusion can be drawn.

• 1.    Analysis of drug sales patterns by using apriori algorithm modification results in a strong drug sales pattern because the value of the lift ratio is greater than one and the time required for apriori algorithm modification is faster than traditional apriori algorithms in determining sales patterns.

• 2.    Farecasting drug stock recommendations using the Triple Exponential Smoothing method has a percentage error value of 31.84% so that the forecast accuracy is 68.16%. The high error value is caused by the MAPE testing technique not using the interval system, but it must be able to predict specifically.

References

• [1]    Agus, D. P., & Fitriyadi. (2017). Penerapan Algoritma Asosiasi untuk Penentuan Pola Penjualan pada Rubiyah Sasirangan. 953-960

• [2]    Al-Maolegi, M., & Arkok, B. (2018). An Improved Apriori Algorithm For Association Rules. International Journal on Natural Language Computing (IJNLC), 21-29.

• [3]    Berry, M., & Linoff, G. (2017). Data Mining Techniques For Marketing, Sales, Customer Relationship Management Second Edition. United States of America: Wiley Publishing, Inc.

• [4]   Eaton, C., Dirk, D., Tom, D., George, L., & Paul, Z. (t.thn.). Understanding Big Data. Mc

Graw Hill.

• [5]   Everett, G. D., & McLeod Jr., R. (2007). Software Testing Across the Entire Software

Development Life Cycle. Canada: John Wiley & Sons, Inc. (2002) The IEEE website. [Online]. Available: http://www.ieee.org/

• [6]   Han, J., & Kamber, M. (2018). Data Mining : Concept and Techniques. Morgan Kaufman

Publisher.

• [7]   Hasibuan, Z. (2017). Research Methodology in the Field of Computer Science and

Information Technology. Jakarta: Faculty of Computer Science, University of Indonesia.

• [8]   Kuswardani, D., Widyanto, M., & Trihandini, I. (2017). Metode Association Rule untuk

Analisis Citra CT Organ Pasien . Kursor, 111-120.

• [9]   Larose, D. (2017). Data Mining Methods and Models. Jhon Willey & Sons, Inc: New

Jersey..

• [10]    Santosa, H., Hariyadi, I., & Prayitno. (2017). Data Mining Analysis of Product Purchase Patterns Using the A priori Algorithm Method. National Seminar on Information Technology and Multimedia , 19-24.

• [11]    Sommerville, I. (2017). SOFTWARE ENGINEERING (9 ed.). (M. Horton, M. Hirsch, M. Goldstein, C. Bell, & J. Holcomb, Penyunt.) USA: Pearson Education,Inc.

• [12]    Tampubolon, K., Saragih, H., & Reza , B. (2018). Implementation of Apriori Algorithm Data Mining in Medical Equipment Supplies System. The Scientific Information and Scientific Technology Magazine , 93-106.

• [13]    Virgiawan, D. M., & Muklash, I. (2017). Rule Mining Association Application for Finding Patterns in ITS Mathematics Student Value Data. Journal of the Science and Arts of Pomits, 1-6.

• [14]    Yanto, R., & Khoiriah, R. (2017). Data Mining Implementation with Apriori Algorithm Method in Determining Drug Purchase Pattern.102-113.

59