“Business Intelligence” for Dynamic Pricing and Strategic Margin in Auto Parts Companies: Application Proposal

Andressa Cunha de Oliveira; Fernando Celso de Campos

DOI: 10.22167/2675-6528-202602663

Article derived from a Course Conclusion Work (TCC), with content based on the student’s original work and adapted to the editorial format of the E&S Magazine with the support of the ResumeAI tool, an artificial intelligence solution developed by Instituto Pecege for textual synthesis and organization.

Summary

Pricing in multichannel e-commerce environments presents high complexity, especially in the auto parts sector, due to the diversity of products, tax structures, and cost models per platform. The study aimed to develop and validate a Business Intelligence solution for dynamic pricing and margin analysis in a multichannel environment. The research, of an applied nature and mixed approach, was structured as a single case study. Data were collected from corporate spreadsheets, ERP system, marketplace APIs, and official tax sources, complemented by a semi-structured interview with the organizational decision-maker. An analytical base with over 100 tables and a pricing engine was developed, which calculated the minimum price, suggested price, effective margin, and alerts per product and channel, with daily updates and full portfolio coverage. The analysis identified that 18.17% of operational records showed a negative margin, totaling R$ 3,075,093.28 in net revenue without covering total cost. Technical validation indicated 98% compliance in the cost structure, and managerial evaluation confirmed adherence to the organization’s strategic needs. The solution contributed by integrating data governance, dimensional modeling, and automated pricing in a real multichannel environment, revealing significant distortions in the previous pricing model.

Keywords: Business Intelligence; Dimensional Modeling; Dynamic Pricing; Supabase; Decision Support.

1. Introduction

The auto parts e-commerce sector has consolidated itself as one of the most dynamic segments of digital retail. It is characterized by extensive portfolios, high exchange rate variability in import costs, and simultaneous operation on multiple sales platforms with distinct cost structures. In this context, pricing has ceased to be merely an operational activity and has assumed a strategic role in the competitiveness and sustainability of the business (Foley and Guillemette, 2010).

The speed with which consumers compare prices in real-time on platforms like Magazine Luiza, Shopee, and Mercado Livre makes traditional models based on manual spreadsheets or fixed mark-ups inadequate (Negash and Gray, 2008). These models do not keep up with the scale, tax complexity, and cost dynamics required by the multichannel environment. This limitation can result in the sale of products with unidentified negative margins, compromising the profitability and financial sustainability of the operation.

Business Intelligence (BI) represents, in this context, a structural response to the problem of informational dispersion: by consolidating data from heterogeneous sources into a single analytical architecture, it expands the organizational capacity to support managerial decisions based on traceable and updated evidence. In this study, BI is treated as a sociotechnical system that articulates technological infrastructure, data governance, and analytical practices oriented towards decision-making.

The quality of information therefore becomes a critical factor. Environments without formal integrity and traceability controls tend to produce inconsistent analyses, as discussed by Redman (2001). This reinforces the need for organized data structures as a basis for reliable analytical systems. Data visualization complements this process by translating complex indicators into understandable representations, with interactive analytical dashboards supporting faster and more informed decisions (Sarikaya et al., 2018; Knaflic, 2015).

Despite the relevance of the topic, there is still a scarcity of national studies that integrate data governance, analytical modeling, and pricing support in real digital environments, with complete transactional data, multiple sales channels, and tax complexity. This work seeks to contribute to this gap by developing and applying an analytical solution focused on strategic pricing in the auto parts sector. Given this context, the objective of this study is to develop and validate an automated analytical model capable of calculating the minimum economically viable price per SKU and channel, based on total cost and operational variables, in a multichannel auto parts environment.

2. Material and Methods

This research was characterized as a study of an applied nature, with a mixed methodological approach and descriptive design. It was structured as a single case, focused on a national company in the high-performance auto parts sector, located in the state of Rio Grande do Sul. The central objective was to develop and validate an automated analytical model for strategic pricing in a multichannel environment. The quantitative dimension of the research comprised the development of the analytical solution, including data modeling, construction of the pricing engine, and technical validation. The qualitative dimension was conducted through a semi-structured interview with the organization’s Executive Director, with content analysis oriented towards managerial validation (Malhotra, 2004).

The data were collected from corporate spreadsheets in MS-Excel®, ERP system and marketplace APIs (Mercado Livre, Shopee, Magalu, Amazon and Via Varejo). Complementarily, information from official tax sources (Brazilian Federal Revenue, CONFAZ, NF-e Portal and IBPT) was integrated. The collection occurred with the organization’s authorization and in compliance with confidentiality policies. Data processing was performed in Power Query of Microsoft Power BI, with automated routines for standardization, deduplication, and mapping of critical fields. Data integrity was reinforced in Supabase by granularity uniqueness constraints, preventing conflicting records before analytical consumption.

The data modeling followed the dimensional approach of Kimball and Ross (2013), prioritizing semantic readability and analytical performance. Supabase hosted tables structured into functional blocks (transactional facts, master dimensions, parameters and simulation, audit and governance). Microsoft Power BI connected to Supabase via the PostgreSQL connector, building the semantic model with DAX measures oriented towards pricing and margin analysis.

The dynamic pricing engine was developed to calculate the economically viable minimum price, suggested price, effective margin, and automatic alerts per product and sales channel. The minimum price calculation considered replacement cost, commission, freight, ICMS, PIS/COFINS, IPI, DIFAL, financial cost for transfer period, and target margin (Nagle et al., 2017). Additionally, the estimated provision for returns per channel and the unit cost of promotional items were incorporated, treated as specific parameters and optional variable expense, respectively (Horngren et al., 2018).

A strategic component of the engine was the treatment of suppliers with a fixed selling price. The engine recorded the current list price and compared it with the minimum price calculated from the actual channel costs, aligning with the concept of price-floor management by Talluri and Van Ryzin (2004). Automatic reconciliation between estimated and effectively charged marketplace fees was implemented, validating fees at the order level and adjusting the minimum price based on the actual fee charged per transaction, as recommended by Talluri and Van Ryzin (2004).

The analytical dashboards were developed in Microsoft Power BI and organized into five thematic pages, each oriented towards a specific business question and a decision-maker user profile. The development followed the best practices of data visualization from Few (2004), Tufte (2001), and Knaflic (2015), prioritizing visual clarity and cognitive economy for managerial decision-making (Sharda et al., 2020).

The solution validation occurred in two stages. The first consisted of the technical verification of the automated calculations of the pricing engine, confronting them with the parameters of the previous manual process. The technical audit of the central cost table registered a compliance rate of 98%, indicating adherence of the calculations to the company’s operational logic. The second stage, of a managerial and qualitative nature, was carried out through a semi-structured interview with the Executive Director, upon signing a Free and Informed Consent Term (TCLE). The responses were analyzed by content analysis (Malhotra, 2004), confirming the solution’s adherence to strategic needs.

3. Results and Discussion

The research results demonstrate a fundamental transformation in the pricing process of the studied organization, which evolved from a reactive and fragmented approach to a structured and data-driven model. This change was realized through the development and validation of a Business Intelligence solution, designed for dynamic pricing and strategic margin analysis in a multichannel e-commerce environment. One of the main findings was the creation of a robust analytical foundation, which not only consolidated diverse data sources but also implemented formal integrity controls, enhancing the consistency and auditability of pricing decisions. This initial step was crucial, as it revealed that the central problem was not limited to the calculation of the final price, but encompassed the management and acquisition of information itself, aligning with the importance of data quality in BI systems (Redman, 2001).

The data collection phase highlighted the limitations of the previous pricing model, which relied on scattered information in MS-Excel spreadsheets containing data on active SKUs, acquisition costs, taxes, commissions, variable expenses, and prices charged. The integration of data from the corporate ERP system, from APIs of marketplaces such as Mercado Livre, Shopee, Magazine Luiza, Amazon, and Via Varejo, and from official tax sources (Receita Federal, CONFAZ, Portal da NF-e, IBPT) underscored the pressing need for a unified repository. This initial stage confirmed that the ineffectiveness of pricing was intrinsically linked to the fragmented and inconsistent nature of the underlying data, making a centralized and auditable informational base indispensable for strategic pricing and margin decisions.

The treatment and preparation of data, carried out mainly in Power Query, involved essential transformations to ensure the quality and integrity of the information. Actions included standardizing numeric and textual formats, identifying and removing duplicates, and mapping critical missing fields. A significant advancement was the normalization of tax rates, with cross-referencing to official tax sources such as CONFAZ and the Federal Revenue Service, which exposed structural flaws and outdated rates in the pre-existing manual model. Additionally, the implementation of uniqueness constraints at the granularity level in Supabase, such as `uq_custo_sazonal_grain` and `uq_cobertura_sku`, prevented conflicting records, ensuring data consistency before analytical consumption. This rigorous process resulted in a compliance rate of 98% in the central cost table, validating the technical accuracy of the data structure.

The architecture of the analytical solution was developed following the principles of dimensional modeling, as proposed by Kimball and Ross (2013), prioritizing semantic readability and analytical performance. This choice was fundamental to support quick queries and scenario simulations, essential requirements in Business Intelligence environments. The model was structured in two complementary layers: a persistence layer in Supabase, which hosted over 100 tables organized into functional blocks (transactional facts, master dimensions, simulation parameters, and audit/governance data), and a consumption layer in Microsoft Power BI. This architecture allowed the integration of a vast volume of data, including 151,643 transactional records of marketplace fees and 92,698 supplier records, demonstrating the solution’s ability to support a high-volume, multi-channel portfolio, going beyond point-in-time or sample analyses.

The dynamic pricing engine, a central component of the BI solution, was developed to automate the calculation of four fundamental indicators per product (SKU) and sales channel: the minimum economically viable price, the suggested price, the effective margin, and automatic alerts for products priced below the minimum limit. The engine’s structure follows a cost-oriented pricing logic, where the minimum price reflects the sum of variable costs directly associated with the transaction, plus the minimum margin necessary for business sustainability (Nagle et al., 2017). This comprehensive approach ensures that all relevant cost components are considered in pricing decisions, providing a solid foundation for profitability.

A significant advancement of the pricing engine lies in its ability to integrate diverse cost components that were often overlooked or inconsistently applied in previous manual models. This includes the product’s replacement cost, channel-specific variable expenses such as platform commissions, shipping, exit taxes, and the financial cost arising from marketplace payment terms. Crucially, the model incorporated two frequently omitted operational variables: an estimated provision for returns per channel and the unit cost of promotional gifts, when applicable. The inclusion of these elements enhances the accuracy of the minimum price calculation, ensuring that simulations reflect the true cost of the complete sales cycle and preventing underestimation of costs and erosion of effective margins.

The explicit integration of an estimated provision for channel returns directly addresses historical merchandise return rates, which vary significantly across marketplaces. The disregard of this component in previous models led to an overestimation of net results expected per unit sold. By including this provision in the minimum floor calculation, the engine ensures that simulations contemplate the real cost of the complete sales cycle, which encompasses not only delivery but also the statistical risk of return and its associated operational costs. Similarly, the unit cost of freebies, treated as an optional variable expense for specific operations, such as promotional kits, is added to the total transaction cost. This prevents sales with freebies from being priced based on incomplete costs, which would generate apparently positive but economically deficit margins, aligning with the concept of total service cost (Horngren et al., 2018).

The engine also strategically addresses scenarios with suppliers who impose fixed selling prices, such as exclusive distributors or representatives of high-performance international brands, who establish a suggested retail price (SRP) or a minimum resale price. In these cases, the market price is not a free variable for optimization, but an input data imposed by the commercial relationship. The engine explicitly incorporates this parameter, automatically comparing the fixed price with the minimum price calculated for each channel. Products are flagged as viable if the fixed price is equal to or higher than the determined economic floor, or as unviable if it is lower than the calculated total cost, indicating potential operational loss. This functionality converges with the concept of floor price management (Talluri and Van Ryzin, 2004), which emphasizes the explicit definition of minimum price limits to avoid systematic margin erosion in high-volume, multi-channel environments.

The financial dimension of the solution’s impact was quantified from the consolidated analytical base, which recorded a total net revenue of R$ 32,783,049.99 during the studied period. This revenue was generated from 1,381 distinct SKUs and 7,579 sales records across various operational channels. The total cost of goods sold was R$ 19,297,462.96, complemented by variable expenses of R$ 8,600,676.96, highlighting the relevance of transactional costs in forming operating results and defining the minimum economically viable price. The consolidated contribution margin reached R$ 4,884,910.07, with an average realized margin of 9.54%. These figures demonstrate the complexity of cost structures in multichannel e-commerce and the need for a precise and integrated pricing model.

A critical finding from the analysis was the identification that 18.17% of operational records, totaling 1,377 transactions, presented a negative margin. This corresponded to R$ 3,075,093.28 in net revenue that did not cover the total cost, a significant distortion that remained invisible in the previous manual model. Preliminary analyses, based on partial cuts by sales channel, indicated a significantly lower volume of deficit operations. However, the full consolidation of the analytical base revealed the true magnitude of the problem, highlighting the limitations of fragmented models and the absence of a unified view in the previous process. This finding reinforces the need for integrated analytical architectures to ensure accuracy in margin measurement and reliable support for decision-making.

The decomposition of results by sales channel revealed high heterogeneity in the operation’s profitability structure. Channels such as direct sales presented an average margin of 19.82%, while proprietary e-commerce (JetCommerce) reached 32.19%, configuring themselves as the most profitable environments. In contrast, channels with high dependence on external cost structures performed significantly lower. It is noteworthy that FILIAL SC concentrated the largest absolute loss in negative margin, totaling R$ 273,063.83, even surpassing channels with higher transactional volume. Additionally, the Shopee platform registered an average margin of -30.30%, indicating a structurally deficit operation in the analyzed period. The Mercado Livre (ML RSPERFORMANCE SC) channel, the main source of revenue, accumulated losses of R$ 154,922.42 in transactions with negative margins.

These results demonstrate that the pricing problem is not restricted to a specific channel but is asymmetrically distributed across multiple sales environments. This asymmetry reinforces the inadequacy of homogeneous pricing models and underscores the critical need for channel-specific parameterization, a central feature of the pricing engine developed in this study. Persistent negative margins in certain channels, despite their active presence in the portfolio, highlight a phenomenon of “silent loss,” typical of pricing environments based on estimates, where the absence of a complete variable cost structure leads to the maintenance of structurally deficit offers (Nagle et al., 2017). The solution made these hidden losses visible and auditable.

The development of the management dashboards, a key component of the BI solution, was carried out in Microsoft Power BI, with five thematic pages. Each page was carefully designed to answer a specific business question and cater to a distinct profile of decision-maker user. The design followed best practices in data visualization, according to Few (2004), Tufte (2001), and Knaflic (2015), prioritizing visual clarity, informational hierarchy, and cognitive economy. This focus aimed to reduce cognitive load, a determining factor for the effectiveness of managerial decision-making. The narrative organization of the dashboards followed the logic of Storytelling with Data (Knaflic, 2015), presenting information from aggregated indicators to progressively more detailed analytical views.

The first panel, “Strategic Margin Analysis”, was designed for controlling managers and senior management, offering a consolidated view of portfolio profitability. It presented a contribution margin ranking by product category and sales channel, allowing for immediate identification of the most and least profitable combinations. Key Performance Indicators (KPIs) synthesized critical metrics such as realized margin, deviation from target, net revenue, and alerts for products below the economic floor. Interactive segmentation by brand, fiscal category, channel, and period facilitated navigation from the consolidated to the specific without leaving the page, reducing the time between data acquisition and decision-making, in alignment with user-centric BI architectures for decision-makers (Sharda et al., 2020).

The “Pricing Simulator” panel represents the solution’s greatest direct operational impact. It allows the commercial analyst to adjust the target margin and the analysis period, with an instant reflection on the minimum suggested price for the entire portfolio. This functionality replaced a manual process that previously demanded between two and four business days for partial portfolio reanalysis, resulting in significant gains in efficiency and agility in responding to market dynamics. The simulator’s ability to provide immediate feedback on pricing adjustments is crucial for proactive decision-making in a fast-paced e-commerce environment, enabling rapid adaptation to cost and market fluctuations.

The “Cost Seasonality” panel was developed to address the less visible risk vector of cost fluctuations in multi-channel operations, such as exchange rate variations and import cycles that compress margins without immediate financial impact. This panel offers a historical view of the evolution of replacement costs by SKU and supplier, allowing purchasing managers and financial analysts to make preventive adjustments before cost increases materialize in the results, in line with the concept of cost visibility (Horngren et al., 2018). Complementarily, the “Marketplace Fee Audit” panel, aimed at finance and operations teams, compares estimated fees with those effectively charged by marketplace platforms, transaction by transaction. This automatic reconciliation signals deviations and allows for parameter correction, eliminating an operational blind spot where reconciliation was done only at the consolidated bank statement level (Talluri and Van Ryzin, 2004).

The “Model Governance and Compliance” dashboard is intended for data managers and technical managers responsible for the solution’s maintenance. It presents indicators of the analytical base’s integrity, including the fiscal coverage of active SKUs, the update status of tables, the cost structure compliance index (98%), and records of critical deviations identified by automatic audit processes. The existence of this dashboard dedicated to governance operationalizes Kimball and Ross’s (2013) recommendation that analytical reliability depends on formal data verification mechanisms integrated into the model itself. This approach ensures that governance is not a parallel process but a structural layer of the solution, reinforcing the reliability of analytical outputs.

The validation and implementation phase involved two crucial procedures. Technical validation compared the results generated by the pricing engine with the parameters used in the previous manual process. This audit confirmed a compliance rate of 98% in the central cost table, demonstrating a high degree of adherence between the automated calculations and the company’s operational logic. Non-conformities identified were mainly concentrated in parameterization aspects dependent on the structure exported by the ERP, without implying conceptual errors in the model. This technical verification provided a solid basis for the reliability of the developed solution, ensuring its accuracy and consistency in determining pricing indicators.

In addition to the technical validation, a semi-structured interview with the organization’s Chief Executive Officer (CEO) provided crucial managerial insights. The CEO reported that the previous pricing process was based on individual spreadsheets, without standardization and without consolidated indicators of real net margin, which resulted in subjective price simulations. Critical indicators, such as total cost per unit (with exchange rate variations), effective tax share per channel, and comparative margins between platforms, were not readily accessible. The CEO positively evaluated the new dashboards and indicators, confirming their suitability for strategic needs and praising the clear and intuitive visual organization. Suggestions for improvement included a distinct visual alert for products nearing the minimum limit and a specific dashboard for returns by channel.

Regarding the operational and strategic impact, the Executive Director estimated that the previous manual process required between two and four business days for partial portfolio reanalysis in response to market variations. With the new BI solution, updates occur automatically in a daily cycle, and immediate visualization is available via simulator, drastically reducing response times. This automation eliminated a significant source of inconsistency: the disparity between the tax rates in spreadsheets and official sources. The solution increases competitiveness in digital channels (Shopee, Magalu, Amazon, Via Varejo, Mercado Livre) by enabling a more responsive pricing strategy to commission and shipping variations, allowing for differentiated pricing per channel without loss of traceability.

The overall evaluation of the Executive Director was positive, confirming that the project’s outcome was aligned with the proposed objectives, with satisfactory visualization quality and proven practical applicability. The solution was considered suitable for implementation in the company’s operational workflow. Suggestions for future improvements included the incorporation of competitor price tracking functionalities and a predictive layer based on Machine Learning, which were documented for future development. The CEO also recommended formal training for the commercial and financial teams before final implementation, emphasizing that a technically robust model does not guarantee immediate understanding or adoption by the end-user without adequate visual and textual mediation (Knaflic, 2015; Sharda et al., 2020).

In summary, the implementation of the Business Intelligence solution successfully achieved the objective of developing and validating an automated analytical model for dynamic pricing in a multichannel auto parts environment. The study’s main contribution transcends the technological dimension, manifesting significantly on the managerial level. The solution reorganized the company’s informational base, reduced dependence on scattered manual controls, and established explicit criteria for calculating the minimum price. Furthermore, it provided an analytical interface oriented towards decision-making, transforming pricing into an integrated, verifiable process less dependent on undocumented individual knowledge. This transition from reactive, partial, and subjective pricing to a proactive, comprehensive, and daily updated system represents a substantial operational gain, ensuring greater accuracy, speed, and strategic alignment in margin management.

4. Conclusion

This research sought to develop and validate an automated analytical model capable of calculating the minimum economically viable price per SKU and channel, based on total cost and operational variables, in a multichannel auto parts environment. To this end, a Business Intelligence solution was structured that integrated data governance, dimensional modeling, and a dynamic pricing engine. It was verified that the consolidated analytical base, composed of more than 100 tables, allowed the identification of significant distortions in the previous pricing model, revealing that 18.17% of operational records presented a negative margin, totaling R$ 3,075,093.28 in net revenue without covering the total cost. A pronounced heterogeneity in profitability among sales channels was also observed, with some operating at a structural loss. The solution automated the calculation of minimum price, suggested price, effective margin, and alerts, with daily updates and full portfolio coverage, and its technical validation indicated 98% compliance in the cost structure. The main contribution lies in the operationalization of a proactive and verifiable system, which transformed reactive and subjective pricing into an integrated and data-driven process, reducing dependence on manual controls and undocumented individual knowledge.

Despite the advances, the study presents limitations inherent to its design as a single case study, which restricts the generalization of results to other organizational contexts. Additionally, the quality of the analyses remains dependent on the consistency and availability of the source data, especially those from external systems and marketplace APIs, requiring continuous governance. As a future research agenda, it is suggested to apply the model in multiple organizations for external validation and comparison of results. It is also recommended to incorporate predictive models and Machine Learning algorithms for anticipating cost and demand variations, integrating competitor data to support market-driven pricing, and expanding the approach to other decision-making areas, such as inventory management, purchasing, and financial planning.

Bibliographic References

FEW, Stephen. (2004). Show me the numbers: designing tables and graphs to enlighten. Oakland: Analytics Press.

FOLEY, É.; GUILLEMETTE, M. G. (2010). What is business intelligence? International Journal of Business Intelligence Research, v. 1, n. 4, p. 1–28.

HORNGREN, C. T.; DATAR, S. M.; RAJAN, M. V. (2018). Cost accounting: a managerial emphasis. 16. ed. New Jersey: Pearson.

KIMBALL, Ralph; ROSS, Margy. (2013). The data warehouse toolkit: the definitive guide to dimensional modeling. 3. ed. Indianapolis: Wiley.

KNAFLIC, Cole Nussbaumer. (2015). Storytelling with data. New Jersey: Wiley.

MALHOTRA, Naresh K. (2004). Pesquisa de marketing: uma orientação aplicada. 4. ed. Porto Alegre: Bookman.

NAGLE, Thomas T.; HOGAN, John; ZALE, Joseph. (2017). The strategy and tactics of pricing. 5. ed. New Jersey: Pearson.

NEGASH, Solomon; GRAY, Paul. (2008). Business intelligence. In: BURSTEIN, F.; HOLSAPPLE, C. W. (ed.). Handbook on decision support systems 2. Berlin: Springer.

REDMAN, Thomas C. (2001). Data quality: the field guide. Boston: Digital Press.

SARIKAYA, A. et al. (2018). What do we talk about when we talk about dashboards? IEEE Transactions on Visualization and Computer Graphics, v. 25, n. 1, p. 682–692.

SHARDA, R.; DELEN, D.; TURBAN, E. (2020). Business intelligence, analytics, and data science. 4. ed. New Jersey: Pearson.

TALLURI, K. T.; VAN RYZIN, G. J. (2004). The theory and practice of revenue management. New York: Springer.

TUFTE, Edward R. (2001). The visual display of quantitative information. 2. ed. Cheshire: Graphics Press.

Article originating from the Final Course Work of the Specialization in Data Science and Analytics of the MBA USP/Esalq

To learn more about the course, click here and access the MBX Academy platform