Designing Business Intelligence to Set the Stage for Future Growth of an ECOM Giant Using Power BI
Completed in 60 Days
Team of 3 Persons
Time 240+ Hours
Helping an Automobile E-commerce to build a sales & inventory strategy using Power BI consuming their legacy data.
It started with the idea of leveraging large volumes of transactional data lying idle in client’s database for years without ever being analyzed to maximize sales and build intelligent inventory management.
A successful ecommerce platform generates tons and tons of data. Collecting and storing the data may not be as much a challenge today as is to understand the value of the data to leverage it for better business returns. Our client likewise had huge amount of transactional data waiting to be exploited. The aim was to slice, dice and present the data neatly so it shows patterns, patterns that help the business draw insights in order to make decisions to reduce costs or increase revenue.
We wrote data transformation scripts to convert the raw data into a consumable form of data capable of being analyzed independently as well as with all leading data visualization technologies.
This is where we helped our client by extracting and transforming the transactional data from their databases into meaningful data using which we enabled our client to visualize trends and behaviors using different visualizations and empowered the core team with quicker access to all the data and reports they need for an effective and fast decision making.
While deciding the technology for visualizing data reports and trend analysis, our data experts had detailed discussions with client's core team and other stakeholders. As we were searching for a highly tailored tool which is capable of transforming the same data into multiple types of visualizations and even allow us to create our own custom parameters to discover the true sense of business intelligence, we ended up creating our own criteria for selecting the technology listing the following basic requirements / expectations from the tool.
Our search ended at Power BI, being one of the most popular "one size fits all solution". It made its case by ticking all the criteria boxes for our requirements. Now formally, Power BI is a collection of software services, apps and connectors that work together to turn unrelated sources of data into coherent, visually immersive and interactive insights. The data may be an Excel spreadsheet or a collection of cloud-based and on-premises hybrid data warehouses, Power BI assists to easily connect all the data sources, visualize and discover what's important, and share that with relevant stakeholders.
Power BI puts the Business Intelligence creation straightaway into the hands of analysts who can extract source data, create a dataset, transform or manipulate the data, visualize the data and publish the resulting reports and dashboards. For progressive or iterative analysis, analysts can evolve the BI with new measures or dimensions without requiring IT involvement. This is how we dealt with the entire process of conversion and enrichment.
Power BI was used to create near real-time dashboards/reports using the raw data available in different formats which included csv files from the retailer's database and google analytics data.
Working with Power BI is a 3-step process that starts with cleaning of data and removing all junk/unwanted data, followed by Data Modelling where relationships are defined between the data to be analyzed and finally figuring out the best ways to plot that data such as using bar graphs and pie charts for comparison, scatter plots for distribution and dual axis lines for trends etc.
Different dashboards were crafted with drill down capabilities which helped to visually go through important information like:
Few static reports were also created for later consumption. Static reports cannot be updated in real time as a dashboard but they can be used to go through the past data at any point in time in future.
We used Power BI Desktop for Loading, Cleaning, Preparing, Visualizing Data and Designing Reports, Power BI Server to publish reports for sharing among the stakeholders and Power BI Mobile App for on the fly access to data and reports shared on Power BI Server.
Data Modelling - Data modeling and relationship building is a complex process of connecting data from multiple sources and requires a detailed analysis and multi-brain validation to figure out how the data is connected. This is required to build relationships between data sources and deciding what kind of visualizations are needed.
Ever since we started with this data dashboard we kept on learning about new type of graphs and charts that can be used for a particular type of report. We learnt how certain types of visualizations helps in comparatively better understanding of the specific nature of data to some other type of graphs/charts.
In the process of designing our reports to draw insights we iterated multiple times over the selection of right type of visualization to represent the data. As choosing the wrong visual aid or simply defaulting to the most common type of data visualization could cause confusion with the viewer or lead to mistaken data interpretation. To ensure context, fitment and visual impact of chosen visualization, we first questioned ourselves that why we need a chart for a report.
In this process we challenged ourselves for the reasoning behind chosen visualizations and ensured right canvas for data analysis. Points we debated over were:
The visualizations were a great way of understanding customer's shopping behavior and helped predict patterns that enable the business to make better business strategies. Insights included customer's preferences, most popular brands or products, items being viewed multiple times, spike in demand and the time of the year when there is a spike. Festivals like Black Friday and Thanksgiving saw maximum demand due to the holidays and discounts being offered.
Increased focus on customer group targeting, evaluating campaigns & maintaining a competitive advantage, especially during the festive and peak trade season. By analysis and demand forecasting, supply chain management and inventory stocking before the sales period was improved to give optimum discounts, thus increasing the revenue.
The success of any e-commerce business depends on effective customer service. Power BI helped in identifying major issues and resolving them and enhancing their processes so that customers are satisfied with online payments and faster deliveries.
Sharing reports is as important as building them, and Power BI gave several options to share the reports with different stakeholders in different departments. The most useful is the ability to build and publish web dashboards that show key performance indicators and tie them to appropriate visualizations.