bot / mis.py
SuhasGholkar's picture
Update mis.py
90890b3 verified
Raw History Blame Contribute Delete
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)
# -------------------------------------------------------------------
@st.cache_data(show_spinner=False, ttl=300)
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()
@st.cache_data(show_spinner=False, ttl=300)
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)
@st.cache_data(show_spinner=False, ttl=180)
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))