---
title: PBIUK - Case study
tag: pbionline
---
## Deal tracker
**Requirement**: During Halloween's weeks, we need to track daily sellout, local inventory, and upcoming qty (before Oct 23rd) on a number of lines across EU. This is to help define action qty whether it be placing additional and/or cancelling PO's to mitigate out-of-stock and/or avoid overstock situations for both the event and the entire Q4.
**Data**:
- Input Template: Information of the products we would like to track. Local inventory and forecast qty can be obtained by running the company's macro in Excel.
- Data - Daily Update: Downloaded from a web interface by filtering the desired attributes
- Reporting Tracker: This is the report using data from the above. The tab 'Action' also contains measures.
**Measures**:
```
Sell_Though_Rate = Sellout/Halloween Forecast (%)
QtyForAction = Local Inventory
+ Qty coming before Oct 23rd
- Forecast Rest of the Season (total Q4)
```
### Solution
We will use Power Pivot Add-in of Excel.
**Assumption**:
- Cannot find macro in “Input Template” for reversing formula -> assume that we already have local inventory and forecast quantity.
- Assume that “Data - Daily Update” excel file is overwrite daily with the same name.
- “Reporting Tracker” receives data from Input template to present necessary measures (`Sell_Through_Rate` and `QtyForAction`).
- Report is build in Excel with Power Pivot Add-in enable.
**Comment**:
- Error handling: Investigate error then replace error as null value.
- Load data: Should only create connection, not load data to sheet.
- Transform data process: No need to use group rows.
- Should use Slicer for pivot table in Excel.
- Model: Should create hierarchy in case visualize by pivot table excel.
## Funding Calculation
**Requirement**: We need to work out quotations (column 'Funding/Unit) for our clients in each country and share a summary with them. The whole process is rather time-consuming so I am looking for an alternative to automate/simplify it.
Currently, I am working on each country (with different VAT, costs, financial requirements, etc.) separately from Deal Calculator then copying the desired data into this file which can be shared externally. Whenever there are any changes from Deal Calculator (e.g.: more products added, financial guardrails requirements adjusted, etc.), I will need to manually repeat the whole process again to update this summary and share it with the client.
**Measures**:
- Deal Calculator: Using macros and pre-defined Excel formulas to work out the funding/unit, forecasting, etc.
### Solution
We need to separate raw data and build appropriate data model in this case.
**Assumption**:
- All countries have the same calculation logic but different parameter (like VAT, Discounts, etc.)
**Data Model**
**Dimension**: Country table (optional)
**Parameter tables**:
```
- VAT rates in each countries
- Target CM table
- WHAT-IF parameter tables: (separated for each country)
- Discount
- Uplift
- Min deal CM per unit
```
**Fact table** (or table from which we calculate our targeted measures) has the following columns:
```
- Country
- Event/DealID
- Product Ranges
- ASIN
- ASIN2
- ASIN3
- Item name values
- publishedPriceWithTax
- websitePriceForwardLookingCostMargin
- forwardLookingFullyLoadedCost
- Lowest YTD Price
- Lowest 28D Price
- Forecast (no deal)
- Adjusted forecast
```
Derived measures to calculate:
```
* Existing CM
* Funding Per Unit
* Deal Price
* Deal CPPU
* Deal CM
* % Off Current Site Price
* Forecast Deal Units
* Weighted current pricing
* Weighted deal pricing
* Forecast OPS
* current price
* current Revenue
* old CP
* new CP
* deal Revenue
```
Targeted measures:
```
* Average CM (current pricing)
* Average CM (deal pricing)
* Expected OPS
* Expect Total CP
```
File `Funding Summary` has the following measure:
```
- SOA by Country
- In each sheet (per country):
- Qty cap
- SOA/unit
- SOA/ASIN
per: ASIN, SKU, Product Name, (Long) Product Name
```
### Questions
❓It’s not clear the above measures related to measures in Deal Calculator? If be able can you describe in detail how summarize from Deal Calculator file (country=UK for example) to the Funding Summary file?
**Suggestions**:
- Build DAX measures for each calculated fields above
- Use Slicer visual, WHAT-IF parameters, and SWITCH measures to dynamic change between countries’ parameters (discount, tax rate, etc.)
## IDQ
**Requirement**: How to best transform and analyze this type of data format to call out actions?
**Comments**:
- Data seems well cleaned and is qualified for mining.
- Determine structure of the data (what is grain? what is primary key? -> watch out for duplicate value in primary key)
- Determine data type and special data value (remove or replace blank, null, NaN, Error values)
### Mining Suggestions
It’s depends on what kind of actions you want to call out (aka Business Questions). Firstly, try to summarize your data to deliver some “facts” before diving deeper.
Assumptions:
- We need to analyze product performance in terms of IDQ grade, its ratings, and unit ordered based on product features.
- Product features: Online selling product properties.
**Suggestions**:
1. try to categorize your columns into common groups (picture above).
2. find the relationship between these groups. In this case: it seems features derive to performance which means there’re some kinds of causal effect or correlation here.
3. prioritize the measures to focus on. There are three measure groups:
- IDQ (item data quality) - score or grade is similar
- customer review (avg.rating and number of ratings) -> actually we can find the raw data and come up with another fact table with granularity of per rating per customer.
- Unit Orders <- this measure should analyze in correlation with other measures
4. work with features group:
- understand each feature
- divide the features into common (related) groups
5. mapping measures with feature group to describe the dataset first, try:
- describe distribution of each features
- combine several features and compare where the difference is
- can use AI visual of Power BI for inital clue: [Key Influencer](https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-influencers), [Decomposition Tree](https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-decomposition-tree) <- they allow combine feature on a measure and compare for insights
- figure out the characteristics of products having low/high measure values
6. because it’s not clear that which actions needed to call out (each department has their own interests), here are some suggestions:
- scenario 1: improve how product is displayed and presented in the website based on IDQ score and related features (has leaf node, has image, has brand, etc.) <- should use A/B testing. this scenario is based on the assumption that there are strong correlation between selling unit and IDQ score
- scenario 2: improve inventory management based on inventory level and unit ordered, though we need more data with time series feature for this insight
**Comment**:
- customer review measures seems not insightful in this case since its data is summarized while we need more detail data. But you can combine customer review with IDQ measure to support the first call out action scenario
- during EDA process (exploratory data analysis), you can find the outlier, the weird pattern between measures in subset of features. example:
- high IDQ but low quantity ordered;
- low IDQ but high customer ratings;
- low IDQ, low quantity ordered but high customer review (high average score, low number of reviews);
- etc.
Sign in with Wallet
Connect another wallet