Spaces:
Paused
Paused
Download mis.py from SuhasGholkar/bot: direct link, hf CLI and curl.
- Browser
- Download file 23.4 kB
-
https://huggingface.co/spaces/SuhasGholkar/bot/resolve/main/mis.py
- Command line
-
hf download hf://spaces/SuhasGholkar/bot/mis.py
-
curl -L -o mis.py https://huggingface.co/spaces/SuhasGholkar/bot/resolve/main/mis.py
23.4 kB
| # mis.py | |
| # MIS widgets for Olist chatbot: compact Customer 360 + Admin Seller Scorecards | |
| from __future__ import annotations | |
| import logging | |
| import sqlite3 | |
| from typing import Callable, Optional, Tuple, List | |
| import pandas as pd | |
| import streamlit as st | |
| import matplotlib.pyplot as plt | |
| # ------------------------------------------------------------------- | |
| # Logging | |
| # ------------------------------------------------------------------- | |
| logger = logging.getLogger("mis") | |
| if not logger.handlers: | |
| _h = logging.StreamHandler() | |
| _h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | mis | %(message)s")) | |
| logger.addHandler(_h) | |
| logger.setLevel(logging.INFO) | |
| # ------------------------------------------------------------------- | |
| # Small helpers | |
| # ------------------------------------------------------------------- | |
| def _run_df(conn: sqlite3.Connection, sql: str, params: Tuple = ()) -> pd.DataFrame: | |
| return pd.read_sql_query(sql, conn, params=params) | |
| def _list_tables(conn: sqlite3.Connection) -> List[str]: | |
| rows = conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall() | |
| return [r[0] for r in rows] | |
| def _table_exists(conn: sqlite3.Connection, name: str) -> bool: | |
| row = conn.execute( | |
| "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", | |
| (name,) | |
| ).fetchone() | |
| return row is not None | |
| def _is_numeric(x) -> bool: | |
| try: | |
| float(x) | |
| return True | |
| except Exception: | |
| return False | |
| # ------------------------------------------------------------------- | |
| # Indexes useful for MIS (created only if table exists) | |
| # ------------------------------------------------------------------- | |
| def ensure_mis_indexes(get_conn: Callable[[], sqlite3.Connection]) -> None: | |
| """Create helpful indexes if they don't exist (idempotent + table-aware).""" | |
| try: | |
| with get_conn() as conn: | |
| tables = set(_list_tables(conn)) | |
| def idx(sql: str): | |
| try: | |
| conn.execute(sql) | |
| except Exception as e: | |
| logger.debug("ensure_mis_indexes: skipped %s", e) | |
| if "orders" in tables: | |
| idx("CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id)") | |
| idx("CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(order_status)") | |
| idx("CREATE INDEX IF NOT EXISTS idx_orders_purchase_ts ON orders(order_purchase_timestamp)") | |
| idx("CREATE INDEX IF NOT EXISTS idx_orders_delivered_ts ON orders(order_delivered_customer_date)") | |
| idx("CREATE UNIQUE INDEX IF NOT EXISTS ux_feedback_chat_user ON feedback(chat_id, user_id)") | |
| if "order_items" in tables: | |
| idx("CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id)") | |
| idx("CREATE INDEX IF NOT EXISTS idx_order_items_seller ON order_items(seller_id)") | |
| idx("CREATE INDEX IF NOT EXISTS idx_order_items_product ON order_items(product_id)") | |
| if "order_reviews" in tables: | |
| idx("CREATE INDEX IF NOT EXISTS idx_reviews_order ON order_reviews(order_id)") | |
| if "order_payments" in tables: | |
| idx("CREATE INDEX IF NOT EXISTS idx_payments_order ON order_payments(order_id)") | |
| if "customers" in tables: | |
| idx("CREATE INDEX IF NOT EXISTS idx_customers_id ON customers(customer_id)") | |
| if "product_category_name_translation" in tables: | |
| idx("""CREATE INDEX IF NOT EXISTS idx_prod_cat_tx | |
| ON product_category_name_translation(product_category_name)""") | |
| conn.commit() | |
| except Exception as e: | |
| logger.warning("ensure_mis_indexes: %s", e) | |
| # ------------------------------------------------------------------- | |
| # Customer 360 (compact) | |
| # ------------------------------------------------------------------- | |
| def _load_customer_360_df(db_path: str, customer_id: str) -> pd.DataFrame: | |
| """One-row summary for a customer.""" | |
| conn = sqlite3.connect(db_path) | |
| try: | |
| sql = """ | |
| WITH cust_orders AS ( | |
| SELECT o.* | |
| FROM orders o | |
| WHERE o.customer_id = ? | |
| ), | |
| delivered AS ( | |
| SELECT * | |
| FROM cust_orders | |
| WHERE order_status = 'delivered' | |
| ), | |
| order_values AS ( | |
| SELECT oi.order_id, | |
| SUM(oi.price) AS item_value, | |
| SUM(oi.freight_value) AS freight_value | |
| FROM order_items oi | |
| JOIN cust_orders o ON o.order_id = oi.order_id | |
| GROUP BY oi.order_id | |
| ), | |
| delivered_values AS ( | |
| SELECT ov.* | |
| FROM order_values ov | |
| JOIN delivered d ON d.order_id = ov.order_id | |
| ) | |
| SELECT | |
| (SELECT customer_city FROM customers WHERE customer_id = ?) AS city, | |
| (SELECT customer_state FROM customers WHERE customer_id = ?) AS state, | |
| (SELECT MIN(order_purchase_timestamp) FROM cust_orders) AS first_order_ts, | |
| (SELECT COUNT(*) FROM cust_orders) AS total_orders, | |
| (SELECT COUNT(*) FROM delivered) AS delivered_orders, | |
| (SELECT IFNULL(SUM(item_value),0) FROM order_values) AS lifetime_gmv, | |
| (SELECT IFNULL(SUM(freight_value),0) FROM order_values) AS lifetime_freight, | |
| ( | |
| SELECT CASE WHEN COUNT(*)=0 THEN 0 | |
| ELSE ROUND(SUM(item_value)*1.0/COUNT(*),2) END | |
| FROM delivered_values | |
| ) AS aov_delivered, | |
| (SELECT ROUND(julianday('now') - julianday(MAX(order_delivered_customer_date))) FROM delivered) AS recency_days, | |
| ( | |
| SELECT ROUND( (COUNT(*)*1.0) / | |
| ( (julianday('now') - julianday(MIN(order_purchase_timestamp))) / 30.0 ), 2) | |
| FROM delivered | |
| ) AS frequency_per_month, | |
| (SELECT IFNULL(SUM(item_value),0) FROM delivered_values) AS monetary_delivered, | |
| ( | |
| SELECT ROUND(AVG(julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp)),2) | |
| FROM delivered | |
| WHERE order_delivered_customer_date IS NOT NULL | |
| ) AS avg_delivery_days, | |
| ( | |
| SELECT ROUND(100.0 * AVG(CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date THEN 1 ELSE 0 END),1) | |
| FROM delivered | |
| WHERE order_delivered_customer_date IS NOT NULL | |
| AND order_estimated_delivery_date IS NOT NULL | |
| ) AS pct_ontime, | |
| ( | |
| SELECT ROUND(AVG(r.review_score),2) | |
| FROM order_reviews r | |
| JOIN cust_orders o ON o.order_id = r.order_id | |
| ) AS avg_review_score, | |
| ( | |
| SELECT COUNT(*) | |
| FROM order_reviews r | |
| JOIN cust_orders o ON o.order_id = r.order_id | |
| ) AS review_count | |
| ; | |
| """ | |
| return pd.read_sql_query(sql, conn, params=(customer_id, customer_id, customer_id)) | |
| finally: | |
| conn.close() | |
| def _load_customer_360_slices(db_path: str, customer_id: str) -> Tuple[pd.DataFrame, pd.DataFrame]: | |
| """Returns (payment_mix_df, top_categories_df).""" | |
| conn = sqlite3.connect(db_path) | |
| try: | |
| sql_pay = """ | |
| SELECT op.payment_type, | |
| COUNT(*) AS payments, | |
| ROUND(SUM(op.payment_value),2) AS amount | |
| FROM order_payments op | |
| JOIN orders o ON o.order_id = op.order_id | |
| WHERE o.customer_id = ? | |
| GROUP BY op.payment_type | |
| ORDER BY amount DESC | |
| LIMIT 5; | |
| """ | |
| pay = _run_df(conn, sql_pay, (customer_id,)) | |
| # Category GMV (translate category name if translation table exists) | |
| tables = set(_list_tables(conn)) | |
| if "product_category_name_translation" in tables: | |
| sql_cat = """ | |
| SELECT COALESCE(t.product_category_name_english, p.product_category_name) AS category, | |
| ROUND(SUM(oi.price),2) AS gmv | |
| FROM order_items oi | |
| JOIN orders o ON o.order_id = oi.order_id | |
| JOIN products p ON p.product_id = oi.product_id | |
| LEFT JOIN product_category_name_translation t | |
| ON t.product_category_name = p.product_category_name | |
| WHERE o.customer_id = ? | |
| AND o.order_status = 'delivered' | |
| GROUP BY category | |
| ORDER BY gmv DESC | |
| LIMIT 5; | |
| """ | |
| else: | |
| sql_cat = """ | |
| SELECT p.product_category_name AS category, | |
| ROUND(SUM(oi.price),2) AS gmv | |
| FROM order_items oi | |
| JOIN orders o ON o.order_id = oi.order_id | |
| JOIN products p ON p.product_id = oi.product_id | |
| WHERE o.customer_id = ? | |
| AND o.order_status = 'delivered' | |
| GROUP BY category | |
| ORDER BY gmv DESC | |
| LIMIT 5; | |
| """ | |
| cat = _run_df(conn, sql_cat, (customer_id,)) | |
| return pay, cat | |
| finally: | |
| conn.close() | |
| def render_customer_360(customer_id: str, | |
| get_conn: Callable[[], sqlite3.Connection], | |
| db_path: Optional[str] = None) -> None: | |
| """ | |
| Compact Customer 360 (scoped). Two KPI rows + two small plots. | |
| """ | |
| if not customer_id: | |
| st.info("No customer selected for this session.") | |
| return | |
| # infer db_path for cache | |
| if db_path is None: | |
| try: | |
| with get_conn() as c: | |
| db_path = c.execute("PRAGMA database_list;").fetchone()[2] | |
| except Exception: | |
| db_path = ":memory:" | |
| st.subheader("Customer 360 (mini)") | |
| try: | |
| df = _load_customer_360_df(db_path, customer_id) | |
| except Exception as e: | |
| logger.exception("Customer360 main fetch failed") | |
| st.error("Could not load customer metrics.") | |
| st.exception(e) | |
| return | |
| if df.empty: | |
| st.info("No orders found for this customer.") | |
| return | |
| row = df.iloc[0].to_dict() | |
| with st.container(border=True): | |
| # Top row KPIs | |
| c1, c2, c3, c4 = st.columns(4) | |
| with c1: | |
| st.metric("Total orders", int(row.get("total_orders") or 0)) | |
| with c2: | |
| st.metric("Delivered", int(row.get("delivered_orders") or 0)) | |
| with c3: | |
| st.metric("Lifetime GMV", f"{(row.get('lifetime_gmv') or 0):.2f}") | |
| with c4: | |
| st.metric("AOV (deliv.)", f"{(row.get('aov_delivered') or 0):.2f}") | |
| # Second row compact KPIs | |
| d1, d2, d3, d4 = st.columns(4) | |
| with d1: | |
| st.metric("Avg review", row.get("avg_review_score") or 0) | |
| with d2: | |
| st.metric("On-time %", f"{row.get('pct_ontime') or 0}%") | |
| with d3: | |
| st.metric("Avg delivery (days)", row.get("avg_delivery_days") or 0) | |
| with d4: | |
| st.metric("Recency (days)", row.get("recency_days") or "-") | |
| # Footer chips (city/state + since) | |
| st.caption( | |
| f"**City/State:** {row.get('city') or '-'}, {row.get('state') or '-'}" | |
| f" • **Customer since:** {row.get('first_order_ts') or '-'}" | |
| ) | |
| # Small plots | |
| try: | |
| pay, cat = _load_customer_360_slices(db_path, customer_id) | |
| except Exception as e: | |
| logger.warning("Customer360 slices failed: %s", e) | |
| pay, cat = pd.DataFrame(), pd.DataFrame() | |
| g1, g2 = st.columns(2) | |
| with g1: | |
| st.caption("Top payment types") | |
| if not pay.empty: | |
| fig, ax = plt.subplots(figsize=(4, 2.4)) | |
| ax.barh(pay["payment_type"].astype(str), pay["amount"].astype(float)) | |
| ax.set_xlabel("Amount") | |
| ax.set_ylabel("Type") | |
| st.pyplot(fig, clear_figure=True, use_container_width=True) | |
| else: | |
| st.caption("—") | |
| with g2: | |
| st.caption("Top categories (GMV)") | |
| if not cat.empty: | |
| fig, ax = plt.subplots(figsize=(4, 2.4)) | |
| ax.barh(cat["category"].astype(str), cat["gmv"].astype(float)) | |
| ax.set_xlabel("GMV") | |
| ax.set_ylabel("Category") | |
| st.pyplot(fig, clear_figure=True, use_container_width=True) | |
| else: | |
| st.caption("—") | |
| # ------------------------------------------------------------------- | |
| # Seller Scorecards (Admin) - dynamic SQL if some tables are missing | |
| # ------------------------------------------------------------------- | |
| def _get_orders_span(conn: sqlite3.Connection) -> Tuple[Optional[str], Optional[str]]: | |
| row = conn.execute(""" | |
| SELECT MIN(order_purchase_timestamp), MAX(order_purchase_timestamp) | |
| FROM orders | |
| """).fetchone() | |
| return (row[0], row[1]) if row else (None, None) | |
| import pandas as pd | |
| def seller_scorecard_df( | |
| conn, | |
| seller_id: str | None = None, | |
| date_from: str | None = None, # 'YYYY-MM-DD' or full timestamp | |
| date_to: str | None = None, | |
| delivered_only: bool = True, | |
| limit: int = 50, | |
| ) -> pd.DataFrame: | |
| """ | |
| Build a seller scorecard from Olist tables. | |
| - date_from/date_to: filter by order_purchase_timestamp (inclusive) | |
| - delivered_only: if True, only include delivered orders | |
| - seller_id: None -> all sellers; otherwise restrict to that seller | |
| """ | |
| # Safe defaults if dates are not supplied | |
| date_from = date_from or "1900-01-01" | |
| date_to = date_to or "2100-01-01" | |
| # Inject the delivered-only filter INSIDE the CTE using the correct alias `o` | |
| status_filter = " AND o.order_status = 'delivered'" if delivered_only else "" | |
| sql = f""" | |
| WITH filtered_orders AS ( | |
| SELECT | |
| o.order_id, | |
| o.order_purchase_timestamp, | |
| o.order_delivered_customer_date, | |
| o.order_estimated_delivery_date, | |
| o.order_status | |
| FROM orders o | |
| WHERE o.order_purchase_timestamp BETWEEN ? AND ?{status_filter} | |
| ), | |
| items AS ( | |
| SELECT | |
| oi.order_id, | |
| oi.seller_id, | |
| oi.price, | |
| oi.freight_value | |
| FROM order_items oi | |
| JOIN filtered_orders fo ON fo.order_id = oi.order_id | |
| ), | |
| reviews AS ( | |
| SELECT order_id, review_score | |
| FROM order_reviews | |
| ) | |
| SELECT | |
| i.seller_id, | |
| COUNT(DISTINCT i.order_id) AS orders, | |
| COUNT(*) AS items, | |
| ROUND(SUM(i.price), 2) AS gmv, | |
| ROUND(SUM(i.freight_value), 2) AS freight, | |
| ROUND(AVG( | |
| CASE | |
| WHEN fo.order_delivered_customer_date IS NOT NULL | |
| THEN JULIANDAY(fo.order_delivered_customer_date) - JULIANDAY(fo.order_purchase_timestamp) | |
| END | |
| ), 2) AS avg_days_to_deliver, | |
| ROUND(AVG(r.review_score), 2) AS avg_review, | |
| CASE | |
| WHEN SUM(CASE WHEN fo.order_delivered_customer_date IS NOT NULL THEN 1 ELSE 0 END) = 0 | |
| THEN NULL | |
| ELSE ROUND( | |
| 100.0 * SUM( | |
| CASE | |
| WHEN fo.order_delivered_customer_date IS NOT NULL | |
| AND fo.order_delivered_customer_date <= fo.order_estimated_delivery_date | |
| THEN 1 ELSE 0 | |
| END | |
| ) / SUM(CASE WHEN fo.order_delivered_customer_date IS NOT NULL THEN 1 ELSE 0 END) | |
| , 1) | |
| END AS on_time_pct | |
| FROM items i | |
| JOIN filtered_orders fo ON fo.order_id = i.order_id | |
| LEFT JOIN reviews r ON r.order_id = i.order_id | |
| WHERE (? IS NULL OR i.seller_id = ?) | |
| GROUP BY i.seller_id | |
| ORDER BY gmv DESC | |
| LIMIT {int(limit)}; | |
| """ | |
| params = (date_from, date_to, seller_id, seller_id) | |
| return pd.read_sql_query(sql, conn, params=params) | |
| def _load_seller_scorecards(db_path: str, top_n: int = 50) -> pd.DataFrame: | |
| conn = sqlite3.connect(db_path) | |
| try: | |
| tables = set(_list_tables(conn)) | |
| if "orders" not in tables or "order_items" not in tables: | |
| # These two are mandatory | |
| return pd.DataFrame() | |
| has_reviews = "order_reviews" in tables | |
| # CTE: base seller-orders | |
| base_cte = """ | |
| WITH seller_orders AS ( | |
| SELECT oi.seller_id, o.order_id, o.customer_id, o.order_status, | |
| o.order_purchase_timestamp, o.order_delivered_customer_date, o.order_estimated_delivery_date | |
| FROM order_items oi | |
| JOIN orders o ON o.order_id = oi.order_id | |
| ), | |
| single_seller_orders AS ( | |
| SELECT oi.order_id | |
| FROM order_items oi | |
| GROUP BY oi.order_id | |
| HAVING COUNT(DISTINCT oi.seller_id) = 1 | |
| ), | |
| agg AS ( | |
| SELECT | |
| so.seller_id, | |
| COUNT(DISTINCT CASE WHEN so.order_status='delivered' THEN so.order_id END) AS delivered_orders, | |
| COUNT(*) AS items_sold, | |
| ROUND(SUM(CASE WHEN so.order_status='delivered' THEN oi.price ELSE 0 END),2) AS gmv, | |
| ROUND(SUM(CASE WHEN so.order_status='delivered' THEN oi.freight_value ELSE 0 END),2) AS freight, | |
| COUNT(DISTINCT so.customer_id) AS unique_customers | |
| FROM seller_orders so | |
| JOIN order_items oi ON oi.order_id=so.order_id AND oi.seller_id=so.seller_id | |
| GROUP BY so.seller_id | |
| ), | |
| aov AS ( | |
| SELECT seller_id, | |
| CASE WHEN delivered_orders=0 THEN 0 | |
| ELSE ROUND(gmv*1.0/delivered_orders,2) END AS aov_delivered | |
| FROM agg | |
| ), | |
| cxl AS ( | |
| SELECT seller_id, | |
| ROUND(100.0 * SUM(CASE WHEN order_status IN ('canceled','unavailable') THEN 1 ELSE 0 END) | |
| / COUNT(*), 2) AS pct_canceled | |
| FROM seller_orders | |
| GROUP BY seller_id | |
| ), | |
| sla AS ( | |
| SELECT so.seller_id, | |
| ROUND(AVG(julianday(so.order_delivered_customer_date) - julianday(so.order_purchase_timestamp)),2) AS avg_delivery_days, | |
| ROUND(100.0 * AVG( | |
| CASE WHEN so.order_delivered_customer_date <= so.order_estimated_delivery_date THEN 1 ELSE 0 END | |
| ),1) AS pct_ontime | |
| FROM seller_orders so | |
| JOIN single_seller_orders sso ON sso.order_id = so.order_id | |
| WHERE so.order_status='delivered' | |
| AND so.order_delivered_customer_date IS NOT NULL | |
| AND so.order_estimated_delivery_date IS NOT NULL | |
| GROUP BY so.seller_id | |
| ) | |
| """ | |
| reviews_cte = """ | |
| , reviews AS ( | |
| SELECT so.seller_id, | |
| ROUND(AVG(r.review_score),2) AS avg_review_score, | |
| COUNT(*) AS reviews_count | |
| FROM seller_orders so | |
| JOIN order_reviews r ON r.order_id = so.order_id | |
| GROUP BY so.seller_id | |
| ) | |
| """ | |
| # Final SELECT assembling pieces conditionally | |
| select_core = """ | |
| SELECT | |
| a.seller_id, | |
| a.delivered_orders, a.items_sold, a.gmv, a.freight, a.unique_customers, | |
| av.aov_delivered, | |
| {review_cols} | |
| c.pct_canceled, | |
| s.avg_delivery_days, s.pct_ontime | |
| FROM agg a | |
| LEFT JOIN aov av ON av.seller_id=a.seller_id | |
| {join_reviews} | |
| LEFT JOIN cxl c ON c.seller_id=a.seller_id | |
| LEFT JOIN sla s ON s.seller_id=a.seller_id | |
| ORDER BY a.gmv DESC | |
| LIMIT ?; | |
| """ | |
| if has_reviews: | |
| full_sql = ( | |
| base_cte | |
| + reviews_cte | |
| + select_core.format( | |
| review_cols="r.avg_review_score, r.reviews_count,", | |
| join_reviews="LEFT JOIN reviews r ON r.seller_id=a.seller_id", | |
| ) | |
| ) | |
| else: | |
| full_sql = ( | |
| base_cte | |
| + select_core.format( | |
| review_cols="NULL AS avg_review_score, NULL AS reviews_count,", | |
| join_reviews="", | |
| ) | |
| ) | |
| return _run_df(conn, full_sql, (top_n,)) | |
| finally: | |
| conn.close() | |
| def render_seller_scorecards(get_conn: Callable[[], sqlite3.Connection], | |
| role: str = "admin", | |
| db_path: Optional[str] = None, | |
| page_size: int = 12) -> None: | |
| """Admin-only seller scorecards with quick filters + pagination.""" | |
| if role != "admin": | |
| st.warning("Seller scorecards are available to admins only.") | |
| return | |
| # infer DB path for caching | |
| if db_path is None: | |
| try: | |
| with get_conn() as c: | |
| db_path = c.execute("PRAGMA database_list;").fetchone()[2] | |
| except Exception: | |
| db_path = ":memory:" | |
| st.subheader("Seller Scorecards") | |
| # Filters | |
| fc1, fc2, fc3 = st.columns([2, 1, 1]) | |
| with fc1: | |
| q = st.text_input("Filter seller_id (contains)", "") | |
| with fc2: | |
| min_deliv = st.number_input("Min delivered orders", min_value=0, value=0, step=10) | |
| with fc3: | |
| top_n = st.selectbox("Top by GMV", [50, 100, 200], index=0) | |
| try: | |
| df = _load_seller_scorecards(db_path, top_n=top_n) | |
| except Exception as e: | |
| logger.exception("Seller scorecards fetch failed") | |
| st.error("Could not load seller scorecards.") | |
| st.exception(e) | |
| return | |
| if df.empty: | |
| st.info("No sellers to show (check data load).") | |
| return | |
| if q: | |
| df = df[df["seller_id"].astype(str).str.contains(q, case=False, na=False)] | |
| if _is_numeric(min_deliv): | |
| df = df[df["delivered_orders"] >= int(min_deliv)] | |
| if df.empty: | |
| st.info("No sellers match the selected filters.") | |
| return | |
| # Pagination | |
| total = len(df) | |
| total_pages = max(1, (total + page_size - 1) // page_size) | |
| cp1, cp2, _ = st.columns([1, 1, 6]) | |
| with cp1: | |
| page = st.number_input("Page", min_value=1, max_value=total_pages, value=1) | |
| with cp2: | |
| st.caption(f"{total} sellers • {total_pages} page(s)") | |
| start = (int(page) - 1) * page_size | |
| end = start + page_size | |
| page_df = df.iloc[start:end].reset_index(drop=True) | |
| # Cards grid | |
| for _, row in page_df.iterrows(): | |
| with st.container(border=True): | |
| st.markdown(f"**Seller:** `{row['seller_id']}`") | |
| c1, c2, c3 = st.columns(3) | |
| with c1: | |
| st.metric("GMV", f"{(row['gmv'] or 0):.2f}") | |
| st.metric("Delivered orders", int(row.get("delivered_orders") or 0)) | |
| st.metric("AOV (deliv.)", f"{(row.get('aov_delivered') or 0):.2f}") | |
| with c2: | |
| st.metric("Items sold", int(row.get("items_sold") or 0)) | |
| st.metric("Unique customers", int(row.get("unique_customers") or 0)) | |
| st.metric("Canceled %", f"{row.get('pct_canceled') or 0}%") | |
| with c3: | |
| st.metric("Avg review", row.get("avg_review_score") or 0) | |
| st.metric("On-time %", f"{row.get('pct_ontime') or 0}%") | |
| st.metric("Reviews", int(row.get("reviews_count") or 0)) | |