In this project, we address a complex challenge to transform raw data into actionable business insights, tackling the absence of predefined variable definitions across multiple datasets. The methodology initiates with the ingestion of all databases into the Google Cloud Platform (GCP), where data transformation is executed through BigQuery with SQL language, providing a scalable and efficient processing environment. A direct integration between GCP and Power BI enables near real-time data access, bypassing manual pipelines and intermediate files, which optimizes the workflow for enhanced productivity and a smoother analytical process. A key step involves conducting a detailed examination of each variable and dataset, utilizing statistical correlation analyses to pinpoint the most influential data elements. The core objective is to refine expertise in Data Modeling, DAX, M, Data Visualization and Dashboard-Driven Storytelling, in order to optimize the processes and add value to the business.
Keywords: Data Analysis, Business Analysis, PowerBI, PowerQuery, DAX, M, Data Visualization, Google Cloud Platform, BigQuery, SQL.
MAPPING REVENUE LEADERS
The first analysis aims to map the key drivers of revenue for our business, highlighting which products, client categories, and cities are generating the greatest financial impact in 2024. With a total revenue of $5.36 million, the data reveals that the beer category leads impressively, accounting for 79.17% of the revenue, or approximately $4.25 million, with product 49121 standing out individually at $396 thousand. Among client types, bars contribute 53.79% ($2.88 million), while restaurants account for 46% ($2.47 million). Geographically, Campinas leads with 51.52% ($2.76 million), followed by Rio de Janeiro with 45.82% ($2.46 million), while São Paulo lags with only 2.66% ($142 thousand).
A valuable insight is the strength of beer among bars, especially in Campinas, while Rio de Janeiro shows diverse potential with nab generating $1.01 million in restaurants. On the other hand, shops, with a minimal contribution of just $11 thousand, and São Paulo, with a limited share of $142 thousand, indicate areas that require attention due to underperformance. Based on this, the suggestion is to intensifying investment in beer, prioritizing bars in Campinas and Rio de Janeiro to maximize this trend, exploring opportunities with nab in Rio’s restaurants to diversify revenue, implementing revitalization strategies for shops such as promotions or new incentives, directing resources to São Paulo to boost its relevance in revenue, and providing ongoing strategic support to clients 75361105 and 37404863 to ensure the continuity and growth of their leading performance.
ANALYSIS OF RECURRENCE
The second analysis aims to identify which products and client categories demonstrate the highest order recurrence throughout 2024, providing insights into purchase consistency and behavioral patterns. The data shows that bars lead with 165 orders across 117 different days, followed by restaurants with 160 orders over 90 days, while shops account for only 29 orders in 29 days, indicating minimal participation. Among the products, beer stands out with 344 total orders, with product 49121 being the most recurrent at 116 orders over 83 days, followed by nab with 76 orders in 62 days; soda and liquor, with just 3 and 8 orders respectively, exhibit low frequency.
The temporal analysis reveals a growing trend in orders throughout the year, with notable peaks in September, suggesting a potential seasonal pattern. Based on this,the recommendation is to intensify efforts to retain bars and restaurants, prioritizing the promotion of products like 49121 beer and nab, especially during peak periods such as September, while implementing incentives to boost shop recurrence and monitoring the performance of soda and liquor to explore future growth opportunities.
VALUATION OF SUCCESS GOALS
The third analysis aims to*assess the percentage of combinations of products, clients, and cities that successfully meet their monthly revenue targets in 2024, providing a comprehensive view of our performance against set goals. The data indicates a total revenue of $9,221 against a target of $13,600, resulting in an overall achievement of 67.80%, highlighting both strengths and areas for improvement. Notably, the user 86808677 from São Paulo stands out by achieving 100% of the $1,200 target for nab in August ($2,529) and February ($1,339). However, user 75361105 from Campinas, despite generating $4,480, only reached 44.80% of a challenging $10,000 target for beer in September. Bars account for 54.55% of the revenue, with Campinas leading at 54.55% among cities, yet the performance reveals significant gaps, including the fact that the product categories liquor and soda failed to meet any of their targets.
Based on these insights, I recommend adjusting the targets for users like 75361105 to better reflect actual performance, recognizing the consistency of 86808677 and 83334046 with incentives, and focusing on boosting revenue from shops and São Paulo, which lag in meeting goals. Additionally, exploring strategies to enhance beer performance in Campinas and addressing the underperformance of liquor and soda could help align results more closely with our objectives.
This project successfully transformed raw datasets into strategic business insights, driven by a rigorous methodology that utilized the Google Cloud Platform (GCP) and SQL for scalable data processing, seamlessly integrated with Power BI for dynamic visualization. These dashboards, as seen in the analyses of revenue leadership, order recurrence, and success goal evaluation, provided a platform to sharpen our expertise in Data Modeling, DAX, M, and Data Visualization. The iterative development of visual tools—like revenue distribution charts and time-series order patterns—enhanced our ability to craft compelling dashboard-driven narratives, deepening our understanding of client and product dynamics across regions. By prioritizing the development of these technical skills, the project overcame the challenge of undefined data variables and laid a solid foundation for future analyses, delivering a versatile and insightful reporting framework to support strategic decision-making.