1. Dataset was given by Mindx School
1.1. 7 files csv about order, orderpayment, product, order review and customer
1.1.1. Import to PBI
1.1.2. Cleanning dataset
1.1.2.1. **1. product dataset:** + There are some rows that have product_id but null for category_name, product_name,product_description_lenght, product_photos_qty, but others don't. => We replace "null" with "unknown " or 0 for numeric type columns.
1.1.2.2. **2. product_category_name_english:** + Remove duplicated rows null + Replace null with "unknown"
1.1.2.3. **3.Order_payment_dataset:** + payment sequential: the numeric order of payment method Ex: an order value combine discount, vouncher, pay by credit card or cash
1.1.2.4. **4. Order _items_dataset:** Problem: order_id or product_id is not unique. Ex: Based on filtering duplicates. 0008288aa423d2a3f00fcb17cd7d8719 has same product id but different in order_item_id (1, 2)
1.1.2.4.1. Count order_item_id is total quantity for corresponding product_id
1.1.2.4.2. Error in dataset during crawling data (rarely because duplicates are up to 420)
1.1.3. Building appropriate model
1.1.3.1. Determine fact, dim table (https://learn.microsoft.com/en-us/training/modules/ design-model-power-bi/1-introduction)
1.1.3.1.1. **Fact** contain observational or event data values: sales orders, product counts, prices, transactional dates and times, and quantities. Fact tables can contain several repeated values.
1.1.3.1.2. **Dimension** contain the details about the data in fact tables: products, locations, employees, and order types. These tables are connected to the fact table through key columns. The fact tables contain the measurable data, such as sales and revenue, and each row represents a unique combination of values from the dimension tables.
1.1.4. **Create a date table:** learn.microsoft.com/en-us/training/modules/design-model-power-bi/3-date-table
1.1.4.1. Problem about creating relationship with date table https://www.youtube.com/watch?v=pux4Tsyv8TQ
1.1.4.1.1. Change date/ time data types of order_reviews, orders_dataset to date type in Power Query
2. Snowflake schema
3. Business Overview
3.1. Total order by time, YTD quantity
3.2. Total GMV by time, YTD GMV
3.3. filter for country, region
3.3.1. insight
3.4. Statistic of product catagories
3.4.1. Number of catagories
3.4.2. Which product, categories or payment method that customer use frequently ?
3.4.2.1. => Insight for improve that field
3.5. Seller_id analysis
3.6. Check effect of product price, shipping fee to amount of orders and revenues
3.6.1. Customer behavior
4. Customer satisfaction index
4.1. Average review scores for order by on seller, product type or shipping time
4.1.1. check if their factor could improve service quality
4.2. review classification
4.2.1. Determine which shop can boost promotion, which shop can take penalty
4.3. Average time for answer review
4.3.1. improve customer service
4.4. Average order value
4.4.1. determine how much money which customer cost per order
4.4.1.1. but cardinality is 1 to 1 for customer_dataset to orders_dataset
4.4.1.1.1. It cant get conclusion here cause customer_id is not customer_unique_id