E‑commerce Analytics Report
Background
This is an E-commerce Sales analysis of a global software retailer that sells subscriptions and add-ons across analytics, design, collaboration, and AI across different countries (USA, United Kingdom, Netherlands, Germany, Australia, Canada, Philippines, Spain and France) and intends to understand customer behavior (loyal customers), how channels and promotional campaigns perform, and how those result in/drive repeat purchases.
Aim of Analysis
Sales are occurring across various acquisition channels (how the customer first found or was acquired by the business), from different countries, channels (where the sales occurred), with various order quantities, and at the same time, refunds are occurring across the same dimensions, which birthed the need to:
- Track sales and volumes of loyal customers over time.
- Highlight revenue drivers and repeat-purchase patterns.
- Understand how discounts and pricing influence sales performance.
- What causes refunds?
Analysis Questions Addressed
- How do total sales change by month?
- Which channels bring in the most sales?
- Which channels bring the most repeat (loyal) customers?
- What percent of monthly sales comes from loyal customers?
- Which products or plans sell the most?
- Which products are most popular with loyal customers?
- How long do customers wait before their second purchase?
- Which discount codes are used most, and do they increase repeat purchases?
- What is the average selling price (ASP) by country or currency?
- Where do refunds happen most (by product or channel)?
- Do annual plans bring higher revenue per customer than monthly plans?
- Which add-ons are most often bought with core products (attach rate)?
Tools Used & Analysis Approach
The dataset contained 3 tables, which were used for this analysis, and they are:
- The Products table contains details of all the different products, ranging from the product name to the project category, type of subscription for each product, and many more.
- The Customer table contained details of the various customers the retailer has, which included the customer’s signup date, the customer’s location (country and region), the customer’s age group, and many more.
- The Events table, which contained details of customer activity like the channel a customer used to make a purchase, the location of the customer during the purchase, payment method, and so on.
Tools Used:
The Microsoft Power BI ecosystem was utilized for this project, spanning from Power Query Editor in Power BI Desktop to publishing in Power BI Service.
- Power Query Editor was used to perform data cleaning tasks, such as changing data types, shortening long field names in certain columns, and creating a reference table from the events table to identify the dates when a customer made their first and second purchases (Customer Order Date table), among other purposes.
- DAX (Data Analysis Expressions), the Power BI formula language, was used to create important calculated columns, measures, and a calendar table.
- Data model was created to enable interaction between the different tables and well as ensure the dashboard is optimized for better performance.
- Power BI Service was used to create an embed link for other users who do not have access to the service to interact with the dashboard.
- Figma was also used to design the background of the dashboard.
Analysis Approach:
Get AMOLE OLUWAFERANMI’s stories in your inbox
Join Medium for free to get updates from this writer.
Data Cleaning: This data was largely clean, but some other cleaning I carried out was:
- Changed column datatypes to ensure accuracy.
- There were blank rows in the region column, and after a thorough check, I found that they were for countries in North America, so the blank in the region was changed to North America (NA).
- Created the last refreshed date table to show a user when the report was last updated.
Calculated Columns: I created some columns in the Customers table like days to 2nd purchase (calculates the difference between the first and second order dates), purchase count (number of orders a customer has made regardless of if there was a refund or not), customer type (differentiates between different customers where customers with orders > 9 are loyal customers and others are new & developing customers) and time to 2nd purchase BIN (this groups the days to purchase).
- I created a table to serve as my central calendar table specifically for my time intelligence calculation.
In the customer table, I created some calculated columns to make my analysis easier, and they were:
- First Order Date: Coming from the Customer Order Date Table I created in Power Query.
- Second Order Date: This also followed the same path as the first order date.
- Days to 2nd Purchase: This is basically a difference between the first and second order dates.
- Time to 2nd Purchase (BINs): This was just needed to put days to 2nd purchase in groups.
- Purchase Count: I needed to decide who loyal customers were and to do that, I needed to count how many orders a customer has made.
- Customer Type: With purchase count done, I determined loyal customers by pegging them at customers having purchase count of > 9.
Data Modelling: As I mentioned earlier, a data model was created between the different datasets in the table to ensure there is interaction between those tables, ensuring accurate results as well as optimizing the performance of the dataset.
Dashboard Creation: Various visuals were used to create a visually appealing dashboard that drives insights and ensure accurate decisions are taken by organization/company stakeholders.
Dashboard
The dashboard was divided into 3 pages, with each page having a specific aim, and they are:
Page 1 (Overview page): This just shows a high-level summary of the data itself, showing some important KPIs and visuals.
Page 2 (Customer Behavior): This page was dedicated to understanding how customers behave, as well as what drives customers to be loyal, how loyal customers are acquired, what discount codes drive more revenue amongst loyal customers, among others.
Page 3 (Product Behavior): Just like the second page, this page was built to understand the different products this retailer sells, which product generates the most revenue, what add-on products are purchased with core products (attach rate), where refunds happen the most, and others.
Report Link: Interact with the report here.
Conclusion
This dashboard helps the global software retailer understand the behavior of their customers and products. This helps improve sales, resulting in more revenue generated, reducing refunds as much as possible, and improving how many customers can be retained across various regions and countries.
Connect with me on LinkedIn.
