Retail Sales and Returns (synthetic)

Business scenario

FreshCart is an invented retail chain with hypermarkets, supermarkets, express stores and an online shop in five regions. The category manager wants to understand which products and stores make money after discounts and returns, how promotions affect margin, and whether loyalty customers are worth the extra attention.

Questions to answer with a dashboard

1. What are net sales, cost of goods and gross margin % by category and month?
2. Which stores and store formats have the best margin after discounts, and how do they compare year over year?
3. How much do promotions (discount % above zero) add in units, and what do they cost in margin?
4. What is the average basket: lines per order and net sales per order, by store format and weekday?
5. Do loyalty customers spend more per order than customers without a card, and how does it differ by tier?
6. Which products are returned most often, for what reasons, and what is the refund value by category?
7. How strongly does December shopping change the mix of categories compared with the rest of the year?
8. Create a top 10 products visual that stays correct when a slicer filters by region or format.

Tables, columns and data dictionary

dim_date

One row per calendar day. Mark it as the date table in Power BI and relate every fact table to date_key.

• date_key (integer): Day as a number, yyyymmdd (for example 20240229)
• date (date): Calendar date
• year (integer): Calendar year
• quarter (integer): Calendar quarter, 1 to 4
• month (integer): Month number, 1 to 12
• month_name (text): Month name in English
• day (integer): Day of the month
• day_name (text): Weekday name in English
• is_weekend (boolean): True for Saturday and Sunday

dim_store

Stores and the online shop.

• store_id (integer): Unique store key
• store_name (text): Store name
• region (text): Sales region
• format (text): Hypermarket, Supermarket, Express or Online
• opened_year (integer): Year the store opened
• floor_area_m2 (integer): Selling area in square metres; empty for Online

dim_product

Products sold, with category, price and unit cost.

• product_id (integer): Unique product key
• product_name (text): Product name
• category (text): Top-level category
• subcategory (text): Subcategory
• list_price (decimal): Regular selling price in US dollars
• unit_cost (decimal): Cost to the retailer per unit in US dollars

dim_customer

Loyalty-card customers. Sales to people without a card have an empty customer.

• customer_id (integer): Unique customer key
• loyalty_tier (text): Basic, Silver or Gold
• age_band (text): Age group
• home_region (text): Region where the customer lives
• join_date (date): Date the customer joined the loyalty programme

fact_sales

One row per product on a receipt (a sales line).

• sale_line_id (integer): Unique sales-line key
• order_id (integer): Receipt number; several lines share one order
• date_key (integer): Sale date
• store_id (integer): Store
• product_id (integer): Product
• customer_id (integer): Loyalty customer; empty when no card was used
• quantity (integer): Units sold
• discount_pct (decimal): Discount given, in percent
• net_sales (decimal): Quantity x price after discount, in US dollars
• cost_of_goods (decimal): Quantity x unit cost, in US dollars

fact_return

One row per returned sales line.

• return_id (integer): Unique return key
• sale_line_id (integer): The sales line being returned
• date_key (integer): Return date
• reason (text): Why it was returned
• refund_amount (decimal): Amount refunded in US dollars

License and usage terms

Free to use (synthetic data)

You may use this synthetic data for learning, practice, demos, training material and portfolio projects, including in published reports. You may not resell the files themselves or present them as real data. The data is invented: it does not describe any real person, customer or company.