BI & Data Warehouse Development for FMCG Company
Budget: $30 – $250 USD
Project Overview
We are a Fast-Moving Consumer Goods (FMCG) company looking for an experienced freelance BI developer and data engineer. Our company currently uses an in-house ERP system with standard reporting capabilities. We aim to unlock deeper insights from our data by creating a robust business intelligence infrastructure.
The goal of this project is to design and build a centralized data layer (data warehouse/mart) that pulls data from our ERP. This data will then be used to create a suite of interactive and automated reports in Microsoft Power BI or Google Looker across our key business functions: Finance, Sales, and Procurement.
Key Objectives & Scope
The project is divided into three main areas:
1. Data Warehouse / Data Mart Design
Collaborate with our team to understand the data structure of our in-house ERP system.
Design scalable data warehouse or data mart optimized for analytical queries.
Model the data using best practices (e.g., star schema) to facilitate efficient reporting and ad-hoc analysis.
2. ETL Process Automation
Develop a reliable and automated ETL (Extract, Transform, Load) process.
This process must extract daily transactions and relevant master data from the ERP database.
It should then transform and clean the data before loading it into the new data warehouse.
The entire process must be scheduled to run automatically on a daily basis to ensure the BI reports have fresh data.
3. BI Dashboard & Report Development
Connect the chosen BI tool (Power BI or Google Looker) to the newly created data warehouse.
Develop a set of interactive and user-friendly dashboards and reports as specified in the section below.
The final dashboards should provide both high-level summaries for management and the ability to drill down into details for analysis.
Required BI Reports (Phase 1)
Sales Dashboards
Sales Performance Dashboard: Tracking key metrics like Total Sales, Sales Volume, Average Order Value, and Sales Growth against targets. Should be filterable by region, salesperson, product category, and time period.
Product Performance Analysis: A report identifying top-selling products, product profitability, and sales trends for each product or category.
Customer Analysis: A dashboard to identify top customers by revenue and volume, and analyze purchasing patterns.
1. Sales Overview Dashboard
Purpose: Executive-level snapshot
KPIs/Visuals:
Total Sales
Total Invoices
Total Units Sold
Average Invoice Value
Sales by Region/Location (Map visual)
Sales Trend (Monthly/Weekly)
Top 5 Customers
Sales by Product Category
2. Sales by Customer Workbook
Purpose: Analyze customer behaviors and contributions
Pages/Reports:
Customer Ranking by Sales
RFM Analysis (Recency, Frequency, Monetary)
Year-over-Year Sales by Customer
Customer Churn / Retention Tracker
Customer Lifetime Value Estimation
3. Sales Rep Performance Dashboard
Purpose: Evaluate sales reps
KPIs/Visuals:
Sales per Rep
Revenue vs Quota (if available)
Number of Invoices per Rep
Conversion Rate (if lead data is integrated)
Heatmap: Rep Performance by Region
Trend of Sales per Rep over Time
4. Sales by Location Dashboard
Purpose: Geographic analysis
Visuals:
Sales by Region, Country, or City
Store Performance Comparison
Location Heatmaps
Trend of Sales by Region
Regional Product Preferences
5. Time-Based Sales Analysis
Purpose: Identify seasonal trends and patterns
Reports:
Monthly Sales Trend (Current vs Last Year)
Weekly and Daily Sales Breakdown
Sales Comparison (YoY, MoM)
Day of Week / Hour of Day Analysis
Rolling 12-Month Sales
6. Customer Order Pattern Report
Purpose: Understand repeat buying
Reports:
Time Between Purchases
of Repeat Orders per Customer
Product Reordering Patterns
Cross-Sell / Up-Sell Opportunities
Required Freelancer Skills
Proven experience in data warehouse design and modeling.
Strong proficiency in SQL and developing automated ETL pipelines.
Expert-level skills in either Microsoft Power BI or Google Looker, including DAX for Power BI if applicable.
Proficiency with MS SQL Server as the ERP backend is built on MS SQL Server.
Excellent communication skills and the ability to translate business requirements into technical solutions.
(Preferred) Experience working with data from ERP systems in the FMCG or manufacturing sector.
Arabic is a very big plus
We are a Fast-Moving Consumer Goods (FMCG) company looking for an experienced freelance BI developer and data engineer. Our company currently uses an in-house ERP system with standard reporting capabilities. We aim to unlock deeper insights from our data by creating a robust business intelligence infrastructure.
The goal of this project is to design and build a centralized data layer (data warehouse/mart) that pulls data from our ERP. This data will then be used to create a suite of interactive and automated reports in Microsoft Power BI or Google Looker across our key business functions: Finance, Sales, and Procurement.
Key Objectives & Scope
The project is divided into three main areas:
1. Data Warehouse / Data Mart Design
Collaborate with our team to understand the data structure of our in-house ERP system.
Design scalable data warehouse or data mart optimized for analytical queries.
Model the data using best practices (e.g., star schema) to facilitate efficient reporting and ad-hoc analysis.
2. ETL Process Automation
Develop a reliable and automated ETL (Extract, Transform, Load) process.
This process must extract daily transactions and relevant master data from the ERP database.
It should then transform and clean the data before loading it into the new data warehouse.
The entire process must be scheduled to run automatically on a daily basis to ensure the BI reports have fresh data.
3. BI Dashboard & Report Development
Connect the chosen BI tool (Power BI or Google Looker) to the newly created data warehouse.
Develop a set of interactive and user-friendly dashboards and reports as specified in the section below.
The final dashboards should provide both high-level summaries for management and the ability to drill down into details for analysis.
Required BI Reports (Phase 1)
Sales Dashboards
Sales Performance Dashboard: Tracking key metrics like Total Sales, Sales Volume, Average Order Value, and Sales Growth against targets. Should be filterable by region, salesperson, product category, and time period.
Product Performance Analysis: A report identifying top-selling products, product profitability, and sales trends for each product or category.
Customer Analysis: A dashboard to identify top customers by revenue and volume, and analyze purchasing patterns.
1. Sales Overview Dashboard
Purpose: Executive-level snapshot
KPIs/Visuals:
Total Sales
Total Invoices
Total Units Sold
Average Invoice Value
Sales by Region/Location (Map visual)
Sales Trend (Monthly/Weekly)
Top 5 Customers
Sales by Product Category
2. Sales by Customer Workbook
Purpose: Analyze customer behaviors and contributions
Pages/Reports:
Customer Ranking by Sales
RFM Analysis (Recency, Frequency, Monetary)
Year-over-Year Sales by Customer
Customer Churn / Retention Tracker
Customer Lifetime Value Estimation
3. Sales Rep Performance Dashboard
Purpose: Evaluate sales reps
KPIs/Visuals:
Sales per Rep
Revenue vs Quota (if available)
Number of Invoices per Rep
Conversion Rate (if lead data is integrated)
Heatmap: Rep Performance by Region
Trend of Sales per Rep over Time
4. Sales by Location Dashboard
Purpose: Geographic analysis
Visuals:
Sales by Region, Country, or City
Store Performance Comparison
Location Heatmaps
Trend of Sales by Region
Regional Product Preferences
5. Time-Based Sales Analysis
Purpose: Identify seasonal trends and patterns
Reports:
Monthly Sales Trend (Current vs Last Year)
Weekly and Daily Sales Breakdown
Sales Comparison (YoY, MoM)
Day of Week / Hour of Day Analysis
Rolling 12-Month Sales
6. Customer Order Pattern Report
Purpose: Understand repeat buying
Reports:
Time Between Purchases
of Repeat Orders per Customer
Product Reordering Patterns
Cross-Sell / Up-Sell Opportunities
Required Freelancer Skills
Proven experience in data warehouse design and modeling.
Strong proficiency in SQL and developing automated ETL pipelines.
Expert-level skills in either Microsoft Power BI or Google Looker, including DAX for Power BI if applicable.
Proficiency with MS SQL Server as the ERP backend is built on MS SQL Server.
Excellent communication skills and the ability to translate business requirements into technical solutions.
(Preferred) Experience working with data from ERP systems in the FMCG or manufacturing sector.
Arabic is a very big plus
Related categories:
SQL
Database Administration
Business Intelligence
Data Visualization
Data Analysis
Power BI
ETL
Data Modeling