Syntax Literate: Jurnal Ilmiah Indonesia p–ISSN: 2541-0849 e-ISSN: 2548-1398

Vol. 9, No. 10, Oktober 2024

IMPLEMENTATION OF BUSINESS INTELLIGENCE FOR ANALYSIS DATA OF DRUG SALES USING EXPLORATORY DATA ANALYSIS (EDA) AND VISUALIZATION DATA USING LOOKER STUDIO

Geraldyo Oktafialfa¹, Ari Purno Wahyu²

Universitas Widyatama, Bandung, Indonesia^1,2

Email: [email protected]¹

Abstract

This study aims to analyze pharmaceutical sales performance by leveraging big data and advanced information technology tools, providing insights for improved business strategies. Using the Exploratory Data Analysis (EDA) method, the study processes raw sales data through spreadsheet applications to identify key patterns and trends. The findings are then visualized with Looker Studio on an intelligent dashboard tailored to the needs of the marketing team. The dashboard enables quick, data-driven decision-making by displaying performance metrics and trends relevant to sales and marketing strategies. The results reveal critical insights into sales behavior, including high-performing products, regional sales variations, and temporal sales trends. The analysis equips the marketing team with actionable data to refine their strategies, prioritize product focus, and adjust marketing efforts in response to identified patterns. In conclusion, the use of EDA and data visualization provides a structured approach to big data interpretation, allowing the company to optimize business and marketing performance. The study underscores the potential of data-driven dashboards to enhance strategic planning, suggesting broader applications in various data-intensive business environments.

Keywords: Business Intelligence, Data Analysis, Exploratory Data Analysis (EDA), Data Visualization.

Introduction

Throughout the COVID-19 pandemic period, the business industry is the primary focus of level for society and continues to evolve on a global scale. Information technology plays a vital part in business operations and is a medium which is used in all activities (Maulana et al., 2024). Big data is the term used to describe the accumulation of huge and complicated amounts of data generated by business activities that are processed using traditional data processing technology (Gupta & Kotwani, 2022; Khaled et al., 2024).

Big data gives a firm or business the ability to gather real-time data for analysis of implemented business processes, optimization of resource usage, and support of business evaluations to increase marketing or sales (Ritonga et al., 2021). Although handling large amounts of data does not ensure that a business will function well, it can be optimized by using database design, namely in the form of a data warehouse, to generate information that is quick, accurate, and safe (Naeem et al., 2022; Thivakaran & Ramesh, 2022).

A data warehouse is a group of components and technology that enable strategic data processing to be converted into information that businesses can use to evaluate their current state of operations and make decisions about future growth. Raw data is aligned into the data warehouse through the ETL (Extract, Transform, Load) procedure, which is designed for transaction data synchronization manipulation (Camizuli & Carranza, 2018) (Deming et al., 2018).

Within BI, it is a technology for presenting data that has been processed into useful information with business value, then visualized into smart dashboards consisting of diagrams, graphs, and analytical reports (Gandomi & Haider, 2015). Before creating a smart dashboard, a data analysis process is required to ensure that the data presented in the smart dashboard meets the required needs. This process can be done using the Exploratory Data Analysis (EDA) method. Creating a smart dashboard can be done using various Data Visualization tools, one of which is Looker Studio. The implementation of processed and visualized data into smart dashboards illustrates sales performance based on the data period studied, thus generating reports that can help company authorities analyze data and make decisions.

The results of Exploratory Data Analysis and the presentation of this smart dashboard are expected to assist company performance, especially the marketing team, in determining sales strategies that ultimately lead to increased profits for the company. This study aims to analyze pharmaceutical sales performance by leveraging big data and advanced information technology tools, providing insights for improved business strategies.

Research Method

The research method used is quantitative, where the data used in numerical form will be described descriptively. Meanwhile, analyzing data processes using the EDA method and data visualization using Looker Studio. The steps in exploratory data analysis (EDA) include:

1. Asking relevant questions related to the data analysis goals: Start by posing specific and pertinent questions regarding the objectives of your data analysis. These questions should guide your exploration and provide the desired insights from the data.

2. Acquiring in-depth knowledge of the problem domain: It's crucial to develop a profound understanding of the problem domain you're dealing with. This will aid in interpreting the data more effectively and exploring patterns relevant to that domain.

3. Establishing clear objectives aligned with desired outcomes: Define your data analysis objectives clearly and ensure they align with the desired outcomes. For instance, do you aim to identify trends, uncover anomalies, or test specific hypotheses? Clear objectives will help focus your exploration process and evaluate the results more efficiently.

Result and Discussion

Dataset

The dataset used in this study consists of pharmaceutical sales data from PT. XYZ in ABC hospital that occurred during the period of second quartile to third quartile 2022. We present screenshots of the dataset below. It's just that there is some internal company information that we blur black because the information cannot be opened to the public.

Figure 1. The Dataset

Data Preprocessing

The dataset used in this study consists of pharmaceutical sales data from PT. XYZ that occurred during the period from March to May 2022. In this process, datasets originating from monthly sales data are merged into a single dataset. During the merging process, data with different formats are first converted to the same format. This process aims to:

1. Ensure uniform format and attributes within the data.

2. Eliminate unnecessary attributes from the data.

3. Detecting data redundancy.

EDA Method

Steps in performing EDA:

1. Asking relevant questions related to the data analysis goals: This involves formulating specific questions that are relevant to the data analysis objectives. In this example, the goal of sales data analysis is to provide reference data to the sales team to enhance their performance in the future. Questions such as why sales of product B decreased drastically in April could be the focus of analysis.

2. Acquiring in-depth knowledge of the domain problem: This involves gaining a deep understanding of the issues occurring in the sales domain. Through the data analysis process, information about sales trends, customer preferences, and other factors influencing sales can be obtained. This helps in identifying problems and determining appropriate policies.

3. Setting clear objectives aligned with desired outcomes: The objective of data analysis is to create a smart dashboard presenting sales information over a specific time period. This dashboard will be used as a basis for decision-makers to determine company policies in the future. Thus, the goal of data analysis should align with the desired outcomes, which is the development of policies based on information obtained from data analysis.

Figure 2. Exploratory Data Analysis

Data Visualization

The process of data visualization using Looker Studio (formerly known as Google Data Studio) involves several key steps. Looker Studio is a tool that allows users to create interactive reports and dashboards from various data sources (Mukhiya & Ahmed, 2020). The pre-processed data sources are integrated with Looker Studio and then presented in a Smartdashboard.

Data Preparation

Before integrating data with Looker Studio, it needs to be cleaned, transformed, and combined from various sources. This preparation is already done on the Data Preprocessing step before using Spreadsheet tools.

Connecting Data Sources

Looker Studio supports many types of data sources. In this paper we use a spreadsheet type data source to connect to Looker Studio (Pearson et al., 2020).

Figure 3. Connecting Data Sources

Creating a Dashboard

After connecting the data source, the next step is to create the dashboard or report.

Figure 4. New layout from Looker Studio

Now we just make a design for our sales dashboard performance. We can add data visualization components such as charts, tables, maps, and more by dragging and dropping elements onto the canvas. We can configure each visualization component as needed, setting dimensions, metrics, filters, and the visual style (Yanto et al., 2023). Looker Studio also has Interactive Controls like dropdown filters, date controls, and others that allow end-users to interact with the data in the report. If needed, we can add multiple pages to a single report to present various aspects of the data. Looker Studio allows us to share reports with other users. We can set sharing permissions to allow collaboration or view-only access. We can invite collaborators to edit the report together or share a view-only link with others.

Figure 5. Sales Dashboard

Figure 4 above shows the overall design for Sales Dashboard Performance. In general, it divided into six; Header, Interactive Controls, Scorecard, Line Chart, Doughnut Chart and Table.

Figure 6. Header

Interactive Controls (Figure 7) is a filter view and date range control. We are using 3 fields to control what kind of data that we need to have in the Sales Dashboard. As the name suggests, Interactive Controls, the filter control here is interactive so that when the user uses this feature, it will change the overall appearance of the dashboard according to the filter applied by the user.

Figure 7. Interactive Control

Scorecard (figure 8) is a visual element used to display key metrics or KPIs (Key Performance Indicators) in a clear and concise manner. It provides an at-a-glance view of important data points, allowing users to quickly assess performance against goals or benchmarks. For the Sales Dashboard, we used a scorecard to display total sales, how many products sold and total customer who bought our product in Q2-Q3.

Figure 8. Scorecard

We present month over month growth on a line chart (figure 9). Line chart is the best suitable chart to present time series data (Singh et al., 2017). Month over month growth is data that shows the increase in revenue each month.

Figure 9. Month Over Month Growth

Doughnut chart is used when we are looking into data segmentation (figure 9). It’s really helpful when we have data with various categories within it (Parikh et al., 2023).

Figure 10. Doughnut Chart

We use list tables in data visualization in various scenarios where structured, detailed, and precise information presentation is necessary (Belghith et al., 2024). For example in figure 11, we are using a list table because we need the frequency information.

Figure 11. List Tables

Based on the data from Sales Dashboard, The Digenta product is the highest selling product by PT. XYZ at ABC Hospital. Despite being the highest selling product in terms of value, it is not the highest in terms of quantity. This also applies to the distribution of customer data based on hospital departments. The pediatric department has the highest sales value, while the pulmonary department ranks highest in quantity.

Through the scorecard feature, we can view a summary of PT. XYZ's overall sales at ABC Hospital. Meanwhile, the line chart is used to display the monthly sales trend data. It can be concluded that during Q2 and Q3, PT. XYZ's sales at ABC Hospital experienced fluctuations in both sales value and product quantity.

Conclusion

This study produced sales data for PT. XYZ at ABC Hospital. The analysis results of this data can be used by stakeholders as a consideration in decision-making. The platform used in this study is expected to be implemented in the company to facilitate both sales and leadership, including Sales Supervisors, Sales Managers, and even the stakeholders themselves in monitoring sales activities and as a data source for making future company sales policies and strategies.

BIBLIOGRAPHY

Belghith, M., Ammar, H. Ben, Elloumi, A., & Hachicha, W. (2024). A new rolling forecasting framework using Microsoft Power BI for data visualization: A case study in a pharmaceutical industry. Annales Pharmaceutiques Françaises, 82(3), 493–506.

Camizuli, E., & Carranza, E. J. (2018). Exploratory data analysis (EDA). The Encyclopedia of Archaeological Sciences, 1–7.

Deming, C., Dekkati, S., & Desamsetti, H. (2018). Exploratory Data Analysis and Visualization for Business Analytics. Asian Journal of Applied Science and Engineering, 7(1), 93–100.

Gandomi, A., & Haider, M. (2015). Beyond the hype: Big data concepts, methods, and analytics. International Journal of Information Management, 35(2), 137–144.

Gupta, D. R. S., & Kotwani, D. R. S. (2022). A Visualization Approach For Comparing Financial Performance Of Pharmaceutical Companies. The Journal of Contemporary Issues in Business and Government, 28(4), 517–543.

Khaled, A., Elzer, R., & Alkhazmi, A. (2024). The role of the number of transparent covers in enhancing the efficiency of flat plate collectors. Brilliance: Research of Artificial Intelligence, 4(1), 1–12.

Maulana, D. U., Supriyanto, A., Utomo, H. S., & Rahmanto, O. (2024). Implementation of Web Based Leave Information System at PT Arutmin Indonesia Tambang Kintap. Brilliance: Research of Artificial Intelligence, 4(1), 68–74.

Mukhiya, S. K., & Ahmed, U. (2020). Hands-On Exploratory Data Analysis with Python: Perform EDA techniques to understand, summarize, and investigate your data. Packt Publishing Ltd.

Naeem, M., Jamal, T., Diaz-Martinez, J., Butt, S. A., Montesano, N., Tariq, M. I., De-la-Hoz-Franco, E., & De-La-Hoz-Valdiris, E. (2022). Trends and future perspective challenges in big data. Advances in Intelligent Data Analysis and Applications: Proceeding of the Sixth Euro-China Conference on Intelligent Data Analysis and Applications, 15–18 October 2019, Arad, Romania, 309–325.

Parikh, S., Patel, R., Khunt, D., Chavda, V. P., & Vora, L. (2023). Data analytics and data visualization for the pharmaceutical industry. Bioinformatics Tools for Pharmaceutical Drug Product Development, 55–76.

Pearson, M., Knight, B., Knight, D., Quintana, M., Pearson, M., Knight, B., Knight, D., & Quintana, M. (2020). Connecting to Data. Pro Microsoft Power Platform: Solution Building for the Citizen Developer, 191–210.

Ritonga, A., Nasution, K., & Siambaton, M. Z. (2021). Perancangan aplikasi administrasi desa berbasis website menggunakan metode Booyer Moore. Jurnal Minfo Polgan, 10(1), 1–13.

Singh, M., Ghutla, B., Jnr, R. L., Mohammed, A. F. S., & Rashid, M. A. (2017). Walmart’s Sales Data Analysis-A Big Data Analytics Perspective. 2017 4th Asia-Pacific World Congress on Computer Science and Engineering (APWC on CSE), 114–119.

Thivakaran, T. K., & Ramesh, M. (2022). Exploratory Data analysis and sales forecasting of bigmart dataset using supervised and ANN algorithms. Measurement: Sensors, 23, 100388.

Yanto, B., Sudaryanto, A., & Pratiwi, H. A. (2023). Data Visualization Analysis of Waste Production Volume in Every District of Tangerang Regency in 2021 Using Looker Studio and Big Query Platforms. Journal Of Ict Aplications And System, 2(1), 35–40.

Copyright holder:

Geraldyo Oktafialfa, Ari Purno Wahyu (2024)

First publication right:

Syntax Literate: Jurnal Ilmiah Indonesia

This article is licensed under: