| import streamlit as st
|
| import pandas as pd
|
| import numpy as np
|
| import plotly.express as px
|
| import plotly.graph_objects as go
|
| from plotly.subplots import make_subplots
|
| from scipy import stats
|
| import holidays
|
| from sqlalchemy import create_engine, exc
|
| import os
|
| import tempfile
|
|
|
|
|
|
|
|
|
| st.set_page_config(
|
| page_title="Sales Insight Dashboard",
|
| page_icon="📊",
|
| layout="wide",
|
| initial_sidebar_state="expanded"
|
| )
|
|
|
|
|
|
|
|
|
| st.markdown("""
|
| <style>
|
| /* Main background */
|
| .stApp {
|
| background: linear-gradient(135deg, #1a1a2e 0%, #16213e 50%, #0f3460 100%);
|
| }
|
|
|
| /* Metric cards */
|
| div[data-testid="metric-container"] {
|
| background: linear-gradient(135deg, rgba(255,255,255,0.1) 0%, rgba(255,255,255,0.05) 100%);
|
| border: 1px solid rgba(255,255,255,0.1);
|
| border-radius: 15px;
|
| padding: 20px;
|
| backdrop-filter: blur(10px);
|
| box-shadow: 0 8px 32px rgba(0,0,0,0.3);
|
| }
|
|
|
| div[data-testid="metric-container"] label {
|
| color: #a0aec0 !important;
|
| font-size: 0.9rem !important;
|
| }
|
|
|
| div[data-testid="metric-container"] div[data-testid="stMetricValue"] {
|
| color: #ffffff !important;
|
| font-size: 1.8rem !important;
|
| font-weight: 700 !important;
|
| }
|
|
|
| div[data-testid="metric-container"] div[data-testid="stMetricDelta"] {
|
| font-size: 0.9rem !important;
|
| }
|
|
|
| /* Headers */
|
| h1, h2, h3 {
|
| color: #ffffff !important;
|
| font-weight: 700 !important;
|
| }
|
|
|
| /* Sidebar */
|
| section[data-testid="stSidebar"] {
|
| background: linear-gradient(180deg, #1a1a2e 0%, #16213e 100%);
|
| border-right: 1px solid rgba(255,255,255,0.1);
|
| }
|
|
|
| section[data-testid="stSidebar"] .stMarkdown {
|
| color: #e2e8f0;
|
| }
|
|
|
| /* Cards/Containers */
|
| .stExpander {
|
| background: rgba(255,255,255,0.05);
|
| border: 1px solid rgba(255,255,255,0.1);
|
| border-radius: 10px;
|
| }
|
|
|
| /* Dataframes */
|
| .stDataFrame {
|
| border-radius: 10px;
|
| overflow: hidden;
|
| }
|
|
|
| /* Tabs */
|
| .stTabs [data-baseweb="tab-list"] {
|
| gap: 8px;
|
| background: rgba(255,255,255,0.05);
|
| border-radius: 10px;
|
| padding: 5px;
|
| }
|
|
|
| .stTabs [data-baseweb="tab"] {
|
| background: transparent;
|
| border-radius: 8px;
|
| color: #a0aec0;
|
| padding: 10px 20px;
|
| }
|
|
|
| .stTabs [aria-selected="true"] {
|
| background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
|
| color: white !important;
|
| }
|
|
|
| /* Info boxes */
|
| .info-box {
|
| background: linear-gradient(135deg, rgba(102, 126, 234, 0.2) 0%, rgba(118, 75, 162, 0.2) 100%);
|
| border: 1px solid rgba(102, 126, 234, 0.3);
|
| border-radius: 15px;
|
| padding: 20px;
|
| margin: 10px 0;
|
| }
|
|
|
| .success-box {
|
| background: linear-gradient(135deg, rgba(72, 187, 120, 0.2) 0%, rgba(56, 161, 105, 0.2) 100%);
|
| border: 1px solid rgba(72, 187, 120, 0.3);
|
| border-radius: 15px;
|
| padding: 20px;
|
| margin: 10px 0;
|
| }
|
|
|
| .warning-box {
|
| background: linear-gradient(135deg, rgba(237, 137, 54, 0.2) 0%, rgba(221, 107, 32, 0.2) 100%);
|
| border: 1px solid rgba(237, 137, 54, 0.3);
|
| border-radius: 15px;
|
| padding: 20px;
|
| margin: 10px 0;
|
| }
|
|
|
| /* Section headers */
|
| .section-header {
|
| background: linear-gradient(90deg, rgba(102, 126, 234, 0.3) 0%, transparent 100%);
|
| border-left: 4px solid #667eea;
|
| padding: 10px 20px;
|
| margin: 20px 0;
|
| border-radius: 0 10px 10px 0;
|
| }
|
|
|
| /* File uploader */
|
| .stFileUploader {
|
| background: rgba(255,255,255,0.05);
|
| border: 2px dashed rgba(255,255,255,0.2);
|
| border-radius: 15px;
|
| padding: 20px;
|
| }
|
| </style>
|
| """, unsafe_allow_html=True)
|
|
|
|
|
|
|
|
|
| def find_column(df, keys):
|
| cols = {c.lower(): c for c in df.columns}
|
| for k in keys:
|
| for c in cols:
|
| if k in c:
|
| return cols[c]
|
| return None
|
|
|
| def auto_insight_engine(df, date_col=None, sales_col=None, inventory_col=None):
|
| insights = []
|
|
|
|
|
| if date_col and sales_col:
|
| monthly = df.groupby(df[date_col].dt.to_period("M"))[sales_col].sum()
|
|
|
| if len(monthly) > 1:
|
| mom = monthly.pct_change() * 100
|
| last = mom.iloc[-1]
|
|
|
| if last > 10:
|
| insights.append(("success", "📈 Strong Growth",
|
| f"Sales increased {last:.1f}% last month."))
|
| elif last < -10:
|
| insights.append(("warning", "📉 Sharp Decline",
|
| f"Sales dropped {abs(last):.1f}% last month."))
|
|
|
| if len(monthly) > 2:
|
| vol = monthly.pct_change().std() * 100
|
| if vol > 25:
|
| insights.append(("warning", "⚠️ High Volatility",
|
| f"Monthly sales volatility is {vol:.1f}%."))
|
|
|
|
|
| if "Profit_Margin" in df:
|
| avg = df["Profit_Margin"].mean()
|
|
|
| if avg < 15:
|
| insights.append(("warning", "🔴 Low Margins",
|
| f"Average margin only {avg:.1f}%."))
|
|
|
| elif avg > 40:
|
| insights.append(("success", "🟢 Strong Margins",
|
| f"Healthy margin at {avg:.1f}%."))
|
|
|
|
|
| if "Ad_ROI" in df:
|
| roi = df["Ad_ROI"].mean()
|
|
|
| if roi < 1:
|
| insights.append(("warning", "📢 Ads Losing Money",
|
| "ROI below 1 — campaigns are unprofitable."))
|
|
|
| elif roi > 2:
|
| insights.append(("success", "🚀 Efficient Advertising",
|
| f"Average ROI is {roi:.2f}."))
|
|
|
|
|
| if inventory_col and "Days_of_Inventory" in df:
|
| over = (df["Days_of_Inventory"] > 60).sum()
|
|
|
| if over:
|
| insights.append(("warning", "📦 Overstock Risk",
|
| f"{over} SKUs have more than 60 days stock."))
|
|
|
|
|
| if date_col and sales_col:
|
| daily = df.groupby(df[date_col].dt.date)[sales_col].sum()
|
|
|
| if len(daily) > 10:
|
|
|
| if daily.std() > 0:
|
| z = np.abs(stats.zscore(daily))
|
| spikes = (z > 3).sum()
|
|
|
| if spikes:
|
| insights.append(("warning", "🚨 Sales Anomalies",
|
| f"{spikes} abnormal sales days detected."))
|
|
|
|
|
| if date_col and sales_col:
|
| df_temp = df.copy()
|
| df_temp['is_weekend'] = df_temp[date_col].dt.dayofweek >= 5
|
| weekend_sales = df_temp[df_temp['is_weekend']][sales_col].mean()
|
| weekday_sales = df_temp[~df_temp['is_weekend']][sales_col].mean()
|
|
|
| if weekend_sales > weekday_sales * 1.2:
|
| insights.append(("success", "🎉 Weekend Surge",
|
| f"Weekend sales are {weekend_sales/weekday_sales:.1f}x higher than weekdays."))
|
|
|
|
|
| if date_col and sales_col:
|
| us_holidays = holidays.US()
|
| df_temp = df.copy()
|
| df_temp['is_holiday'] = df_temp[date_col].dt.date.apply(lambda x: x in us_holidays)
|
|
|
| holiday_sales = df_temp[df_temp['is_holiday']][sales_col].mean()
|
| normal_sales = df_temp[~df_temp['is_holiday']][sales_col].mean()
|
|
|
| if pd.notnull(holiday_sales) and pd.notnull(normal_sales) and normal_sales > 0:
|
| if holiday_sales > normal_sales * 1.3:
|
| insights.append(("success", "🎆 Holiday Boom",
|
| f"Sales on holidays are {holiday_sales/normal_sales:.1f}x higher than average!"))
|
| elif holiday_sales < normal_sales * 0.7:
|
| insights.append(("info", "💤 Holiday Slump",
|
| f"Sales tend to dip by {100 - (holiday_sales/normal_sales*100):.0f}% on holidays."))
|
|
|
| return insights
|
|
|
| def apply_chart_style(fig, num_items=None, is_horizontal=False):
|
| """Apply consistent dark theme styling to charts"""
|
| layout_args = {
|
| 'template': 'plotly_dark',
|
| 'paper_bgcolor': 'rgba(0,0,0,0)',
|
| 'plot_bgcolor': 'rgba(0,0,0,0)',
|
| 'showlegend': False,
|
| 'margin': dict(l=120, t=50, r=20, b=50)
|
| }
|
| if is_horizontal and num_items:
|
| layout_args['height'] = max(400, num_items * 35)
|
| fig.update_layout(**layout_args)
|
| return fig
|
|
|
|
|
|
|
|
|
| with st.sidebar:
|
| st.markdown("# 📊 Sales Insight")
|
| st.markdown("---")
|
|
|
| source_type = st.radio("Data Source", ["📂 CSV File", "🗄️ Database"], horizontal=True)
|
|
|
| df = None
|
|
|
| if source_type == "📂 CSV File":
|
| uploaded_files = st.file_uploader(
|
| "Upload CSV File(s)",
|
| type=['csv'],
|
| accept_multiple_files=True,
|
| help="Upload one or more sales data CSV files"
|
| )
|
|
|
| if not uploaded_files:
|
| st.info("Using default dataset")
|
| try:
|
| df = pd.read_csv("6_months_sells.csv")
|
| except:
|
| st.error("No default file found. Please upload a CSV.")
|
| st.stop()
|
| else:
|
| try:
|
| dfs = []
|
| for f in uploaded_files:
|
| dfs.append(pd.read_csv(f))
|
| df = pd.concat(dfs, ignore_index=True)
|
| if len(uploaded_files) > 1:
|
| st.success(f"✅ Successfully merged {len(uploaded_files)} files into one dataset ({len(df):,} rows)")
|
| except Exception as e:
|
| st.error(f"Error merging files: {e}")
|
| st.stop()
|
|
|
| else:
|
| st.markdown("### 🔌 Database Connection")
|
|
|
| with st.expander("📝 Connection URI Guide"):
|
| st.markdown("""
|
| **Google Cloud SQL / AWS RDS / Azure SQL**
|
| Support for standard PostgreSQL, MySQL, and other drivers.
|
|
|
| **Templates:**
|
| - **PostgreSQL**: `postgresql+psycopg2://USER:PASSWORD@IP:5432/DB_NAME`
|
| - **MySQL**: `mysql+pymysql://USER:PASSWORD@IP:3306/DB_NAME`
|
| - **SQL Server**: `mssql+pyodbc://USER:PASSWORD@IP/DB_NAME`
|
| - **SQLite**: `sqlite:///data.db`
|
|
|
| *Note: Ensure your database allows public IP access or use a proxy.*
|
| """)
|
|
|
| with st.expander("Connection Details", expanded=True):
|
| conn_method = st.tabs(["🧱 Form Builder", "🔗 Raw URI String"])
|
|
|
| db_uri = ""
|
|
|
| with conn_method[0]:
|
| col1, col2 = st.columns(2)
|
| with col1:
|
| db_type = st.selectbox("Database Type", ["PostgreSQL", "MySQL", "SQL Server", "SQLite"])
|
| with col2:
|
| if db_type == "SQLite":
|
| db_host = ""
|
| db_port = ""
|
| elif db_type == "PostgreSQL":
|
| db_port = st.text_input("Port", "5432")
|
| elif db_type == "MySQL":
|
| db_port = st.text_input("Port", "3306")
|
| elif db_type == "SQL Server":
|
| db_port = st.text_input("Port", "1433")
|
|
|
| if db_type != "SQLite":
|
| db_host = st.text_input("Host IP/Address", placeholder="34.12.34.56 or db.example.com")
|
| db_name = st.text_input("Database Name", placeholder="sales_db")
|
|
|
| col1, col2 = st.columns(2)
|
| with col1:
|
| db_user = st.text_input("Username", placeholder="admin")
|
| with col2:
|
| db_pass = st.text_input("Password", type="password")
|
|
|
|
|
| if st.checkbox("Show Generated URI"):
|
|
|
| preview_uri = f"{db_type.lower()}://{db_user}:****@{db_host}:{db_port}/{db_name}"
|
| st.caption(f"URI Preview: `{preview_uri}`")
|
|
|
| if db_type == "PostgreSQL":
|
| db_uri = f"postgresql+psycopg2://{db_user}:{db_pass}@{db_host}:{db_port}/{db_name}"
|
| elif db_type == "MySQL":
|
| db_uri = f"mysql+pymysql://{db_user}:{db_pass}@{db_host}:{db_port}/{db_name}"
|
| elif db_type == "SQL Server":
|
| db_uri = f"mssql+pyodbc://{db_user}:{db_pass}@{db_host}:{db_port}/{db_name}?driver=ODBC+Driver+17+for+SQL+Server"
|
| else:
|
|
|
| sqlite_path = st.text_input("Database Path", "data.db")
|
| db_uri = f"sqlite:///{sqlite_path}"
|
|
|
| with conn_method[1]:
|
| db_uri_input = st.text_input(
|
| "Connection String",
|
| placeholder="postgresql+psycopg2://user:pass@service-account.aivencloud.com:5432/db",
|
| help="Enter your SQLAlchemy connection string directly"
|
| )
|
| if db_uri_input:
|
| db_uri = db_uri_input.strip()
|
|
|
| if db_uri.startswith("postgres://"):
|
| db_uri = db_uri.replace("postgres://", "postgresql+psycopg2://")
|
|
|
| query = st.text_area(
|
| "SQL Query",
|
| value="SELECT * FROM sales_data LIMIT 5000",
|
| help="Enter your SQL query to fetch data"
|
| )
|
|
|
|
|
| st.markdown("#### 🔐 Security (Optional)")
|
| use_ssl = st.checkbox("Enable SSL/TLS (Required for Aiven, Azure, etc.)")
|
| ssl_ca = None
|
| if use_ssl:
|
| ssl_ca = st.file_uploader("Upload CA Certificate (.pem)", type=["pem", "crt", "ca"])
|
|
|
| if st.button("🔌 Connect & Load"):
|
| if not db_uri or not query:
|
| st.warning("Please provide Connection Details and Query.")
|
| else:
|
| tmp_ca_path = None
|
| try:
|
| connect_args = {}
|
|
|
|
|
| if use_ssl and ssl_ca:
|
| with tempfile.NamedTemporaryFile(delete=False, suffix='.pem') as tmp:
|
| tmp.write(ssl_ca.getvalue())
|
| tmp_ca_path = tmp.name
|
|
|
|
|
| if "postgres" in db_uri:
|
| connect_args = {
|
| "sslmode": "require",
|
| "sslrootcert": tmp_ca_path
|
| }
|
| elif "mysql" in db_uri:
|
| connect_args = {
|
| "ssl": {"ca": tmp_ca_path}
|
| }
|
|
|
|
|
| with st.spinner("Connecting to database..."):
|
| engine = create_engine(db_uri, connect_args=connect_args)
|
| with engine.connect() as conn:
|
| df = pd.read_sql(query, conn)
|
|
|
| except exc.NoSuchModuleError as e:
|
| st.error("🚫 Missing Driver Error")
|
| st.warning(f"The required database driver is missing: {e}")
|
| st.info("💡 Tip: Standard Google Cloud connections require specific libraries. \n\n"
|
| "- For Postgres: `pip install psycopg2-binary`\n"
|
| "- For MySQL: `pip install pymysql`\n"
|
| "- For SQL Server: `pip install pyodbc`")
|
|
|
| except Exception as e:
|
| st.error(f"Connection Error: {e}")
|
| st.markdown("Try checking: 1. IP Whitelisting 2. SSL Configuration 3. Correct Credentials")
|
|
|
| finally:
|
|
|
| if tmp_ca_path and os.path.exists(tmp_ca_path):
|
| os.unlink(tmp_ca_path)
|
|
|
|
|
| if df is None:
|
| st.info("👈 Connect to a database to proceed")
|
| st.stop()
|
|
|
|
|
| nan_count = df.isnull().sum().sum()
|
|
|
| with st.expander("🧹 Data Cleaning & Handling", expanded=(nan_count > 0)):
|
| if nan_count > 0:
|
| st.warning(f"⚠️ Found {nan_count:,} missing values in your data.")
|
|
|
|
|
| null_cols = df.columns[df.isnull().any()].tolist()
|
| st.write("Columns with missing data:", null_cols)
|
|
|
| clean_mode = st.radio(
|
| "Choose how to handle missing values:",
|
| ["✨ Auto-Fill (Recommended)", "🗑️ Drop Rows", "🛑 Keep Raw Data"],
|
| help="Auto-Fill replaces numbers with 0 and text with 'Unknown'. Drop Rows removes the entire record."
|
| )
|
|
|
| if clean_mode == "✨ Auto-Fill (Recommended)":
|
| for col in df.columns:
|
| if df[col].dtype in ['float64', 'int64']:
|
| df[col] = df[col].fillna(0)
|
| else:
|
| df[col] = df[col].fillna('Unknown')
|
| st.success("✅ Applied: Numeric -> 0, Text -> 'Unknown'")
|
|
|
| elif clean_mode == "🗑️ Drop Rows":
|
| initial_rows = len(df)
|
| df = df.dropna()
|
| st.success(f"✅ Dropped {initial_rows - len(df)} rows")
|
|
|
| elif clean_mode == "🛑 Keep Raw Data":
|
| st.info("⚠️ Keeping data as-is. Some charts may show errors.")
|
|
|
| else:
|
| st.success("✅ Data is clean! No missing values found.")
|
|
|
| st.success(f"✅ Loaded {len(df):,} records")
|
|
|
| st.markdown("---")
|
|
|
|
|
|
|
| st.markdown("### ⚙️ Column Mapping")
|
|
|
|
|
| def get_index(options, target):
|
| try:
|
| return options.index(target)
|
| except ValueError:
|
| return 0
|
|
|
| all_cols = ["None"] + list(df.columns)
|
|
|
|
|
| default_date = find_column(df, ["date", "month", "time", "day", "period"])
|
| default_sales = find_column(df, ["sales", "revenue", "amount", "value", "turnover"])
|
| default_inventory = find_column(df, ["inventory", "stock", "quantity", "volume"])
|
| default_ad = find_column(df, ["adcost", "ad_cost", "marketing", "spend", "ads"])
|
| default_clicks = find_column(df, ["click", "visits"])
|
| default_impr = find_column(df, ["impr", "views", "reach"])
|
| default_brand = find_column(df, ["brand", "manufacturer", "maker", "vendor"])
|
| default_cat = find_column(df, ["subcategory", "category", "type", "segment"])
|
| default_price = find_column(df, ["price", "mrp", "selling_price"])
|
| default_cost = find_column(df, ["mfrcost", "cost", "mfr_cost", "buying_price"])
|
| default_cpc = find_column(df, ["avgcpc", "cpc", "avg_cpc", "cost_per_click"])
|
| default_pos = find_column(df, ["position", "our position", "rank", "ranking"])
|
| default_cart = find_column(df, ["abdcarts", "abandoned", "cart", "basket"])
|
|
|
| with st.expander("⚙️ Column Configuration", expanded=True):
|
| st.caption("Map your CSV columns to the dashboard fields.")
|
|
|
| st.markdown("#### 📌 Core Columns")
|
| date_col = st.selectbox("Date Column", all_cols, index=get_index(all_cols, default_date))
|
| if date_col == "None": date_col = None
|
|
|
| sales_col = st.selectbox("Sales/Revenue Column", all_cols, index=get_index(all_cols, default_sales))
|
| if sales_col == "None": sales_col = None
|
|
|
| brand_col = st.selectbox("Brand Column", all_cols, index=get_index(all_cols, default_brand))
|
| if brand_col == "None": brand_col = None
|
|
|
| cat_col = st.selectbox("Category Column", all_cols, index=get_index(all_cols, default_cat))
|
| if cat_col == "None": cat_col = None
|
|
|
| st.markdown("#### 📦 Product & Inventory")
|
| inventory_col = st.selectbox("Inventory/Stock Column", all_cols, index=get_index(all_cols, default_inventory))
|
| if inventory_col == "None": inventory_col = None
|
|
|
| price_col = st.selectbox("Unit Price Column", all_cols, index=get_index(all_cols, default_price))
|
| if price_col == "None": price_col = None
|
|
|
| cost_col = st.selectbox("Unit Cost Column", all_cols, index=get_index(all_cols, default_cost))
|
| if cost_col == "None": cost_col = None
|
|
|
| st.markdown("#### 📢 Marketing & Metrics")
|
| ad_col = st.selectbox("Ad Cost Column", all_cols, index=get_index(all_cols, default_ad))
|
| if ad_col == "None": ad_col = None
|
|
|
| clicks_col = st.selectbox("Clicks Column", all_cols, index=get_index(all_cols, default_clicks))
|
| if clicks_col == "None": clicks_col = None
|
|
|
| impr_col = st.selectbox("Impressions Column", all_cols, index=get_index(all_cols, default_impr))
|
| if impr_col == "None": impr_col = None
|
|
|
| avgcpc_col = st.selectbox("CPC Column", all_cols, index=get_index(all_cols, default_cpc))
|
| if avgcpc_col == "None": avgcpc_col = None
|
|
|
| position_col = st.selectbox("Search Position Column", all_cols, index=get_index(all_cols, default_pos))
|
| if position_col == "None": position_col = None
|
|
|
| cart_col = st.selectbox("Abandoned Carts Column", all_cols, index=get_index(all_cols, default_cart))
|
| if cart_col == "None": cart_col = None
|
|
|
|
|
| if not date_col or not sales_col:
|
| st.warning("⚠️ 'Date' and 'Sales' columns are recommended for full functionality.")
|
|
|
| detected = {
|
| "📅 Date": date_col,
|
| "💰 Sales": sales_col,
|
| "📦 Inventory": inventory_col,
|
| "📢 Ad Cost": ad_col,
|
| "🖱️ Clicks": clicks_col,
|
| "👁️ Impressions": impr_col,
|
| "🏷️ Brand": brand_col,
|
| "📂 Category": cat_col,
|
| "💵 Price": price_col,
|
| "🏭 Mfr Cost": cost_col,
|
| "📊 Position": position_col,
|
| "🛒 Abnd Carts": cart_col,
|
| }
|
|
|
| st.markdown("### 📋 Active Columns")
|
| active_cols = []
|
| for name, col in detected.items():
|
| if col:
|
| active_cols.append(f"<small>{name}: `{col}`</small>")
|
|
|
| if active_cols:
|
| st.markdown("<br>".join(active_cols), unsafe_allow_html=True)
|
| else:
|
| st.info("No columns mapped yet.")
|
|
|
|
|
|
|
|
|
| st.markdown("---")
|
| st.markdown("### 🔍 Filters")
|
|
|
|
|
| if date_col:
|
| df[date_col] = pd.to_datetime(df[date_col], dayfirst=True)
|
|
|
|
|
| df_original = df.copy()
|
|
|
|
|
| if brand_col:
|
| brands = ["All"] + sorted(df[brand_col].dropna().unique().tolist())
|
| selected_brand = st.selectbox("Select Brand", brands)
|
| if selected_brand != "All":
|
| df = df[df[brand_col] == selected_brand]
|
|
|
|
|
| if cat_col:
|
| categories = ["All"] + sorted(df[cat_col].dropna().unique().tolist())
|
| selected_category = st.selectbox("Select Category", categories)
|
| if selected_category != "All":
|
| df = df[df[cat_col] == selected_category]
|
|
|
|
|
| if date_col:
|
| df[date_col] = pd.to_datetime(df[date_col])
|
| min_date = df[date_col].min().date()
|
| max_date = df[date_col].max().date()
|
|
|
| date_range = st.date_input(
|
| "Date Range",
|
| value=(min_date, max_date),
|
| min_value=min_date,
|
| max_value=max_date
|
| )
|
|
|
| if len(date_range) == 2:
|
| df = df[(df[date_col].dt.date >= date_range[0]) & (df[date_col].dt.date <= date_range[1])]
|
|
|
|
|
| if len(df) != len(df_original):
|
| st.info(f"📊 Showing {len(df):,} of {len(df_original):,} records")
|
|
|
| st.markdown("---")
|
|
|
|
|
| st.markdown("### 📈 Quick Stats")
|
| if sales_col:
|
| st.metric("Filtered Sales", f"{df[sales_col].sum():,.0f}")
|
| if brand_col:
|
| st.metric("Brands", f"{df[brand_col].nunique()}")
|
| if cat_col:
|
| st.metric("Categories", f"{df[cat_col].nunique()}")
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| if price_col and cost_col:
|
| df["Profit_Margin"] = (df[price_col] - df[cost_col]) / df[price_col].replace(0, np.nan) * 100
|
|
|
| if clicks_col and impr_col:
|
| df["CTR"] = df[clicks_col] / df[impr_col].replace(0, np.nan) * 100
|
|
|
| if sales_col and clicks_col:
|
| df["Conversion_Rate"] = df[sales_col] / df[clicks_col].replace(0, np.nan) * 100
|
|
|
| if inventory_col and sales_col:
|
| df["Inventory_Turnover"] = df[sales_col] / df[inventory_col].replace(0, np.nan)
|
|
|
| if ad_col and sales_col:
|
| df["Cost_per_Sale"] = df[ad_col] / df[sales_col].replace(0, np.nan)
|
| df["Ad_ROI"] = df[sales_col] / df[ad_col].replace(0, np.nan)
|
|
|
| if inventory_col and sales_col:
|
| df["IS_Ratio"] = df[inventory_col] / df[sales_col].replace(0, np.nan)
|
|
|
|
|
|
|
|
|
| st.markdown("""
|
| <div style="text-align: center; padding: 20px 0;">
|
| <h1 style="font-size: 2.5rem; margin-bottom: 10px;">
|
| 📊 <span style="background: linear-gradient(90deg, #667eea, #764ba2, #f093fb);
|
| -webkit-background-clip: text; -webkit-text-fill-color: transparent;">Sales Insight Dashboard</span>
|
| </h1>
|
| <p style="color: #a0aec0; font-size: 1.1rem;">Comprehensive Business Intelligence & Analytics</p>
|
| </div>
|
| """, unsafe_allow_html=True)
|
|
|
|
|
|
|
|
|
| st.markdown('<div class="section-header"><h3>🎯 Executive KPIs</h3></div>', unsafe_allow_html=True)
|
|
|
| col1, col2, col3, col4, col5 = st.columns(5)
|
|
|
| with col1:
|
| if sales_col:
|
| st.metric("Total Sales", f"{df[sales_col].sum():,.0f}", "Revenue")
|
|
|
| with col2:
|
| if inventory_col:
|
| st.metric("Total Inventory", f"{df[inventory_col].sum():,.0f}", "Units")
|
|
|
| with col3:
|
| if ad_col:
|
| st.metric("Ad Spend", f"${df[ad_col].sum():,.0f}", "Marketing")
|
|
|
| with col4:
|
| if clicks_col:
|
| st.metric("Total Clicks", f"{df[clicks_col].sum():,.0f}", "Engagement")
|
|
|
| with col5:
|
| if impr_col:
|
| st.metric("Impressions", f"{df[impr_col].sum()/1e6:.1f}M", "Reach")
|
|
|
|
|
| col1, col2, col3, col4, col5 = st.columns(5)
|
|
|
| with col1:
|
| if "Profit_Margin" in df.columns:
|
| st.metric("Avg Margin", f"{df['Profit_Margin'].mean():.1f}%", "Profitability")
|
|
|
| with col2:
|
| if "CTR" in df.columns:
|
| st.metric("Avg CTR", f"{df['CTR'].mean():.2f}%", "Click Rate")
|
|
|
| with col3:
|
| if "Conversion_Rate" in df.columns:
|
| st.metric("Conversion", f"{df['Conversion_Rate'].mean():.4f}%", "Sales/Clicks")
|
|
|
| with col4:
|
| if "Ad_ROI" in df.columns:
|
| st.metric("Ad ROI", f"{df['Ad_ROI'].mean():.2f}", "Sales/$")
|
|
|
| with col5:
|
| st.metric("Records", f"{len(df):,}", "Data Points")
|
|
|
|
|
|
|
|
|
| st.markdown("<br>", unsafe_allow_html=True)
|
|
|
| tab1, tab2, tab3, tab4, tab5, tab6, tab7, tab8, tab9, tab10, tab11, tab12 = st.tabs([
|
| "📈 Trends", "🏆 Performance", "💰 Profitability",
|
| "📢 Marketing", "📦 Inventory", "🔮 Forecast", "📊 Advanced",
|
| "🔄 Comparison", "🔍 Data Explorer", "🎯 Goals", "🛒 Conversion",
|
| "🤖 Insights"
|
| ])
|
|
|
|
|
|
|
|
|
| with tab1:
|
| st.markdown('<div class="section-header"><h3>📈 Sales Trends & Patterns</h3></div>', unsafe_allow_html=True)
|
|
|
| if date_col and sales_col:
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
|
|
| monthly = df.groupby(df[date_col].dt.to_period("M"))[sales_col].sum()
|
| monthly.index = monthly.index.astype(str)
|
|
|
| fig = px.bar(
|
| x=monthly.index, y=monthly.values,
|
| title="Monthly Sales Trend",
|
| labels={'x': 'Month', 'y': 'Sales'},
|
| color=monthly.values,
|
| color_continuous_scale='Viridis'
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)',
|
| showlegend=False
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
| with col2:
|
|
|
| daily = df.groupby(df[date_col].dt.date)[sales_col].sum().reset_index()
|
| daily.columns = ['Date', 'Sales']
|
|
|
|
|
|
|
| sales_series = pd.Series(daily['Sales'].values)
|
| lag_7_corr = sales_series.autocorr(lag=7)
|
| lag_30_corr = sales_series.autocorr(lag=30)
|
|
|
| optimal_window = 7
|
| pattern_msg = "Defaulting to weekly trend."
|
|
|
| if lag_30_corr > lag_7_corr and lag_30_corr > 0.3:
|
| optimal_window = 30
|
| pattern_msg = f"✨ Detected strong monthly seasonality (Corr: {lag_30_corr:.2f}). Applied 30-Day MA."
|
| elif lag_7_corr > 0.3:
|
| optimal_window = 7
|
| pattern_msg = f"✨ Detected strong weekly seasonality (Corr: {lag_7_corr:.2f}). Applied 7-Day MA."
|
| else:
|
| optimal_window = 7
|
| pattern_msg = "No strong pattern detected. Using 7-Day MA for smoothing."
|
|
|
| daily['MA_Optimal'] = daily['Sales'].rolling(optimal_window, min_periods=1).mean()
|
| daily['MA_7'] = daily['Sales'].rolling(7, min_periods=1).mean()
|
| daily['MA_30'] = daily['Sales'].rolling(30, min_periods=1).mean()
|
|
|
|
|
| view_mode = st.radio("Daily Chart View",
|
| ["✨ Smart MA (Auto)", "Detailed (All)", "Manual Comparison (7 vs 30)"],
|
| horizontal=True, label_visibility="collapsed")
|
|
|
| if view_mode == "✨ Smart MA (Auto)":
|
| st.caption(pattern_msg)
|
|
|
| fig = go.Figure()
|
|
|
| if view_mode == "Detailed (All)":
|
| fig.add_trace(go.Scatter(x=daily['Date'], y=daily['Sales'],
|
| name='Daily Sales', mode='lines',
|
| line=dict(color='#667eea', width=1), opacity=0.4))
|
| fig.add_trace(go.Scatter(x=daily['Date'], y=daily['MA_7'],
|
| name='7-Day MA', mode='lines',
|
| line=dict(color='#f093fb', width=2)))
|
|
|
| elif view_mode == "Manual Comparison (7 vs 30)":
|
| fig.add_trace(go.Scatter(x=daily['Date'], y=daily['MA_7'],
|
| name='7-Day MA', mode='lines',
|
| line=dict(color='#f093fb', width=2)))
|
| fig.add_trace(go.Scatter(x=daily['Date'], y=daily['MA_30'],
|
| name='30-Day MA', mode='lines',
|
| line=dict(color='#48bb78', width=2)))
|
|
|
| else:
|
| fig.add_trace(go.Scatter(x=daily['Date'], y=daily['Sales'],
|
| name='Daily Sales', mode='lines',
|
| line=dict(color='#667eea', width=1), opacity=0.2))
|
| fig.add_trace(go.Scatter(x=daily['Date'], y=daily['MA_Optimal'],
|
| name=f'{optimal_window}-Day Optimal MA', mode='lines',
|
| line=dict(color='#f093fb', width=3)))
|
|
|
| fig.update_layout(
|
| title='Daily Sales Trends',
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)',
|
| legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
| st.markdown("#### 🎆 Holiday Effects")
|
| us_holidays = holidays.US()
|
| df['Is_Holiday'] = df[date_col].dt.date.apply(lambda x: x in us_holidays)
|
| df['Holiday_Name'] = df[date_col].dt.date.apply(lambda x: us_holidays.get(x))
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| holiday_stats = df.groupby('Is_Holiday')[sales_col].mean()
|
| if len(holiday_stats) > 1:
|
| fig = px.bar(
|
| x=['Normal Days', 'Holidays'],
|
| y=[holiday_stats.get(False, 0), holiday_stats.get(True, 0)],
|
| title="Average Sales: Normal vs Holidays",
|
| color=['Normal', 'Holiday'],
|
| color_discrete_sequence=['#667eea', '#f093fb']
|
| )
|
| apply_chart_style(fig)
|
| st.plotly_chart(fig, use_container_width=True)
|
| else:
|
| st.info("Not enough holiday data for comparison.")
|
|
|
| with col2:
|
| holiday_breakdown = df[df['Is_Holiday'] == True].groupby('Holiday_Name')[sales_col].sum().sort_values(ascending=True)
|
| if not holiday_breakdown.empty:
|
| fig = px.bar(
|
| x=holiday_breakdown.values, y=holiday_breakdown.index,
|
| orientation='h',
|
| title="Total Sales by Holiday",
|
| color=holiday_breakdown.values,
|
| color_continuous_scale='RdYlGn'
|
| )
|
| apply_chart_style(fig, len(holiday_breakdown), is_horizontal=True)
|
| st.plotly_chart(fig, use_container_width=True)
|
| else:
|
| st.info("No sales occurred on holidays in this dataset.")
|
|
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| df["DayOfWeek"] = df[date_col].dt.day_name()
|
| weekly = df.groupby("DayOfWeek")[sales_col].mean().reindex([
|
| "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
|
| ])
|
|
|
| fig = px.bar(
|
| x=weekly.index, y=weekly.values,
|
| title="Average Sales by Day of Week",
|
| color=weekly.values,
|
| color_continuous_scale='RdYlGn'
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)',
|
| showlegend=False
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
| with col2:
|
|
|
| monthly_vals = df.groupby(df[date_col].dt.to_period("M"))[sales_col].sum()
|
| seasonal_idx = (monthly_vals / monthly_vals.mean() * 100).round(1)
|
| seasonal_idx.index = seasonal_idx.index.astype(str)
|
|
|
| fig = go.Figure(go.Bar(
|
| x=seasonal_idx.index,
|
| y=seasonal_idx.values - 100,
|
| base=100,
|
| marker_color=['#48bb78' if v > 100 else '#f56565' for v in seasonal_idx.values]
|
| ))
|
| fig.add_hline(y=100, line_dash="dash", line_color="white", opacity=0.5)
|
| fig.update_layout(
|
| title='Seasonal Index (100 = Average)',
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)',
|
| yaxis_title='Index'
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
|
|
|
|
| with tab2:
|
| st.markdown('<div class="section-header"><h3>🏆 Brand & Category Performance</h3></div>', unsafe_allow_html=True)
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| if brand_col and sales_col:
|
| brand_sales = df.groupby(brand_col)[sales_col].sum().sort_values(ascending=True)
|
|
|
| fig = px.bar(
|
| x=brand_sales.values, y=brand_sales.index,
|
| orientation='h',
|
| title="Sales by Brand",
|
| color=brand_sales.values,
|
| color_continuous_scale='Viridis'
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)',
|
| showlegend=False,
|
| yaxis_title='',
|
| xaxis_title='Sales',
|
| height=max(400, len(brand_sales) * 30),
|
| margin=dict(l=100, t=50)
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
| with col2:
|
| if cat_col and sales_col:
|
| cat_sales = df.groupby(cat_col)[sales_col].sum()
|
|
|
| fig = px.pie(
|
| values=cat_sales.values, names=cat_sales.index,
|
| title="Sales by Category",
|
| hole=0.4,
|
| color_discrete_sequence=px.colors.qualitative.Set3
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
| if brand_col and cat_col and sales_col:
|
| st.markdown("#### 📊 Brand × Category Sales Matrix")
|
| matrix = pd.pivot_table(df, values=sales_col, index=brand_col, columns=cat_col, aggfunc='sum')
|
|
|
| fig = px.imshow(
|
| matrix,
|
| title="Sales Heatmap: Brand vs Category",
|
| color_continuous_scale='Viridis',
|
| aspect='auto'
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, width="stretch")
|
|
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| st.markdown("#### 🥇 Top 10 Best Sellers")
|
| top_cols = [sales_col]
|
| if brand_col: top_cols.insert(0, brand_col)
|
| if cat_col: top_cols.insert(0, cat_col)
|
| st.dataframe(df.nlargest(10, sales_col)[top_cols], use_container_width=True)
|
|
|
| with col2:
|
| st.markdown("#### 📉 Bottom 10 Performers")
|
| st.dataframe(df.nsmallest(10, sales_col)[top_cols], use_container_width=True)
|
|
|
|
|
|
|
|
|
| with tab3:
|
| st.markdown('<div class="section-header"><h3>💰 Profitability Analysis</h3></div>', unsafe_allow_html=True)
|
|
|
| if "Profit_Margin" in df.columns:
|
| col1, col2, col3 = st.columns(3)
|
|
|
| with col1:
|
| st.metric("Average Margin", f"{df['Profit_Margin'].mean():.1f}%")
|
| with col2:
|
| st.metric("Min Margin", f"{df['Profit_Margin'].min():.1f}%")
|
| with col3:
|
| st.metric("Max Margin", f"{df['Profit_Margin'].max():.1f}%")
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| if brand_col:
|
| brand_margin = df.groupby(brand_col)["Profit_Margin"].mean().sort_values(ascending=True)
|
|
|
| fig = px.bar(
|
| x=brand_margin.values, y=brand_margin.index,
|
| orientation='h',
|
| title="Profit Margin by Brand",
|
| color=brand_margin.values,
|
| color_continuous_scale='RdYlGn'
|
| )
|
| apply_chart_style(fig, len(brand_margin), is_horizontal=True)
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
| with col2:
|
|
|
| fig = px.histogram(
|
| df, x="Profit_Margin",
|
| title="Profit Margin Distribution",
|
| nbins=50,
|
| color_discrete_sequence=['#667eea']
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
| if price_col:
|
| st.markdown("#### 💵 Price Tier Analysis")
|
| df["Price_Tier"] = pd.qcut(df[price_col], q=4, labels=["Budget", "Economy", "Premium", "Luxury"])
|
|
|
| tier_data = df.groupby("Price_Tier", observed=True).agg({
|
| sales_col: ['sum', 'mean', 'count']
|
| }).round(0)
|
| tier_data.columns = ['Total Sales', 'Avg Sales', 'Products']
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| st.dataframe(tier_data, use_container_width=True)
|
|
|
| with col2:
|
| tier_sales = df.groupby("Price_Tier", observed=True)[sales_col].sum()
|
| fig = px.pie(
|
| values=tier_sales.values, names=tier_sales.index,
|
| title="Revenue by Price Tier",
|
| hole=0.4,
|
| color_discrete_sequence=['#667eea', '#764ba2', '#f093fb', '#48bb78']
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
| else:
|
| st.warning("Price and Cost columns required for profitability analysis")
|
|
|
|
|
|
|
|
|
| with tab4:
|
| st.markdown('<div class="section-header"><h3>📢 Marketing Performance</h3></div>', unsafe_allow_html=True)
|
|
|
| col1, col2, col3, col4, col5 = st.columns(5)
|
|
|
| with col1:
|
| if "CTR" in df.columns:
|
| st.metric("Avg CTR", f"{df['CTR'].mean():.2f}%")
|
| with col2:
|
| if "Conversion_Rate" in df.columns:
|
| st.metric("Avg Conversion", f"{df['Conversion_Rate'].mean():.4f}%")
|
| with col3:
|
| if "Ad_ROI" in df.columns:
|
| st.metric("Avg ROI", f"{df['Ad_ROI'].mean():.2f}")
|
| with col4:
|
| if avgcpc_col:
|
| st.metric("Avg CPC", f"${df[avgcpc_col].mean():.2f}")
|
| with col5:
|
| if position_col:
|
| st.metric("Avg Position", f"{df[position_col].mean():.1f}", help="Lower is better (0 = Top Rank)")
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| if brand_col and "CTR" in df.columns:
|
| brand_ctr = df.groupby(brand_col)["CTR"].mean().sort_values(ascending=True)
|
|
|
| fig = px.bar(
|
| x=brand_ctr.values, y=brand_ctr.index,
|
| orientation='h',
|
| title="CTR by Brand",
|
| color=brand_ctr.values,
|
| color_continuous_scale='Blues'
|
| )
|
| apply_chart_style(fig, len(brand_ctr), is_horizontal=True)
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
| with col2:
|
| if brand_col and "Ad_ROI" in df.columns:
|
| brand_roi = df.groupby(brand_col)["Ad_ROI"].mean().sort_values(ascending=True)
|
|
|
| fig = px.bar(
|
| x=brand_roi.values, y=brand_roi.index,
|
| orientation='h',
|
| title="Ad ROI by Brand",
|
| color=brand_roi.values,
|
| color_continuous_scale='Greens'
|
| )
|
| apply_chart_style(fig, len(brand_roi), is_horizontal=True)
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
| if brand_col and "Cost_per_Sale" in df.columns:
|
| st.markdown("#### 💸 Marketing Cost per Sale")
|
| cps = df.groupby(brand_col)["Cost_per_Sale"].mean().sort_values()
|
|
|
| fig = px.bar(
|
| x=cps.index, y=cps.values,
|
| title="Cost per Sale by Brand (Lower is Better)",
|
| color=cps.values,
|
| color_continuous_scale='RdYlGn_r'
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)',
|
| showlegend=False
|
| )
|
| st.plotly_chart(fig, width="stretch")
|
|
|
|
|
| if position_col:
|
| st.markdown('<div class="section-header"><h3>🔍 Search Visibility & Position</h3></div>', unsafe_allow_html=True)
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
|
|
| if brand_col:
|
| brand_pos = df.groupby(brand_col)[position_col].mean().sort_values(ascending=True)
|
|
|
| fig = px.bar(
|
| x=brand_pos.values, y=brand_pos.index,
|
| orientation='h',
|
| title="Avg Search Position by Brand (Lower is Better)",
|
| color=brand_pos.values,
|
| color_continuous_scale='RdYlGn_r'
|
| )
|
| apply_chart_style(fig, len(brand_pos), is_horizontal=True)
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
| with col2:
|
|
|
| fig = px.histogram(
|
| df, x=position_col,
|
| title="Search Position Distribution",
|
| nbins=20,
|
| color_discrete_sequence=['#f093fb']
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
| if date_col:
|
| st.markdown("#### 📉 Daily Average Position Trend")
|
| daily_pos = df.groupby(df[date_col].dt.date)[position_col].mean().reset_index()
|
| daily_pos.columns = ['Date', 'Avg Position']
|
|
|
| fig = go.Figure()
|
| fig.add_trace(go.Scatter(
|
| x=daily_pos['Date'], y=daily_pos['Avg Position'],
|
| mode='lines',
|
| line=dict(color='#f093fb', width=3),
|
| name='Avg Position',
|
| fill='tozeroy',
|
| fillcolor='rgba(240, 147, 251, 0.1)'
|
| ))
|
| fig.update_layout(
|
| title="Average Search Position (Inverted Axis - Lower is Better)",
|
| yaxis=dict(autorange="reversed", title="Avg Position"),
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
|
|
|
|
| with tab5:
|
| st.markdown('<div class="section-header"><h3>📦 Inventory Analysis</h3></div>', unsafe_allow_html=True)
|
|
|
| if inventory_col and sales_col:
|
|
|
| daily_sales_rate = df[sales_col].sum() / 90
|
| df["Days_of_Inventory"] = df[inventory_col] / daily_sales_rate
|
|
|
| overstocked = df[df["Days_of_Inventory"] > 60]
|
| understocked = df[df["Days_of_Inventory"] < 7]
|
|
|
| col1, col2, col3 = st.columns(3)
|
|
|
| with col1:
|
| st.metric("Avg Days of Inventory", f"{df['Days_of_Inventory'].mean():.0f} days")
|
| with col2:
|
| st.metric("Overstocked (>60 days)", f"{len(overstocked):,}", f"{len(overstocked)/len(df)*100:.1f}%")
|
| with col3:
|
| st.metric("Understocked (<7 days)", f"{len(understocked):,}", f"{len(understocked)/len(df)*100:.1f}%")
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| if brand_col:
|
| brand_turnover = df.groupby(brand_col)["Inventory_Turnover"].mean().sort_values(ascending=True)
|
|
|
| fig = px.bar(
|
| x=brand_turnover.values, y=brand_turnover.index,
|
| orientation='h',
|
| title="Inventory Turnover by Brand",
|
| color=brand_turnover.values,
|
| color_continuous_scale='Viridis'
|
| )
|
| apply_chart_style(fig, len(brand_turnover), is_horizontal=True)
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
| with col2:
|
| if brand_col:
|
| brand_is = df.groupby(brand_col)["IS_Ratio"].mean().sort_values()
|
|
|
| fig = px.bar(
|
| x=brand_is.index, y=brand_is.values,
|
| title="Inventory-to-Sales Ratio (Lower is Better)",
|
| color=brand_is.values,
|
| color_continuous_scale='RdYlGn_r'
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)',
|
| showlegend=False
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
| st.markdown("#### ⚠️ Low Inventory Turnover (Risk)")
|
| worst = df.sort_values("Inventory_Turnover").head(10)
|
| display_cols = ["Inventory_Turnover"]
|
| if cat_col: display_cols.insert(0, cat_col)
|
| if brand_col: display_cols.insert(0, brand_col)
|
| st.dataframe(worst[display_cols], use_container_width=True)
|
|
|
|
|
|
|
|
|
| with tab6:
|
| st.markdown('<div class="section-header"><h3>🔮 Forecasting & Predictions</h3></div>', unsafe_allow_html=True)
|
|
|
| if date_col and sales_col:
|
| monthly = df.groupby(df[date_col].dt.to_period("M"))[sales_col].sum()
|
| monthly_values = monthly.values
|
|
|
| if len(monthly_values) >= 2:
|
| forecast_model = st.radio("Forecast Model",
|
| ["Linear Growth (Momentum)", "Moving Average (3-Month)"],
|
| horizontal=True)
|
|
|
|
|
| last_month = monthly_values[-1]
|
|
|
| if forecast_model == "Moving Average (3-Month)":
|
|
|
| history = list(monthly_values)
|
| forecasted = []
|
| for _ in range(3):
|
|
|
| next_val = np.mean(history[-3:])
|
| forecasted.append(next_val)
|
| history.append(next_val)
|
|
|
| forecast_next, forecast_next_2, forecast_next_3 = forecasted
|
| avg_change = forecast_next - last_month
|
|
|
| else:
|
|
|
| avg_change = np.mean(np.diff(monthly_values))
|
| forecast_next = last_month + avg_change
|
| forecast_next_2 = last_month + (avg_change * 2)
|
| forecast_next_3 = last_month + (avg_change * 3)
|
|
|
| col1, col2, col3, col4 = st.columns(4)
|
|
|
| trend = "📈 GROWING" if avg_change > 0 else "📉 DECLINING" if avg_change < 0 else "➡️ STABLE"
|
|
|
| with col1:
|
| st.metric("Last Month", f"{last_month:,.0f}")
|
| with col2:
|
| st.metric("Avg Monthly Change", f"{avg_change:+,.0f}")
|
| with col3:
|
| st.metric("Next Month Forecast", f"{forecast_next:,.0f}")
|
| with col4:
|
| st.metric("Trend", trend)
|
|
|
|
|
| historical = list(monthly.values)
|
| forecasted = [forecast_next, forecast_next_2, forecast_next_3]
|
|
|
| all_periods = list(monthly.index.astype(str)) + ['M+1', 'M+2', 'M+3']
|
| all_values = historical + forecasted
|
| colors = ['#667eea'] * len(historical) + ['#f093fb'] * 3
|
|
|
| fig = go.Figure()
|
| fig.add_trace(go.Bar(
|
| x=all_periods,
|
| y=all_values,
|
| marker_color=colors
|
| ))
|
| fig.add_vline(x=len(historical)-0.5, line_dash="dash", line_color="white", opacity=0.5)
|
| fig.update_layout(
|
| title='Sales Forecast (Historical + Projected)',
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| weekly = df.groupby(df[date_col].dt.isocalendar().week)[sales_col].sum()
|
| wow = weekly.pct_change() * 100
|
|
|
| st.markdown("#### 📊 Growth Rate Analysis")
|
| st.metric("Avg WoW Growth", f"{wow.mean():.2f}%")
|
| st.metric("Best Week", f"+{wow.max():.2f}%")
|
| st.metric("Worst Week", f"{wow.min():.2f}%")
|
|
|
| with col2:
|
|
|
| if price_col:
|
| price_corr = df[price_col].corr(df[sales_col])
|
|
|
| if price_corr < -0.3:
|
| elasticity = "🔴 ELASTIC (Price Sensitive)"
|
| elif price_corr > 0.3:
|
| elasticity = "🟢 POSITIVE (Premium Effect)"
|
| else:
|
| elasticity = "🟡 INELASTIC (Price Insensitive)"
|
|
|
| st.markdown("#### 📈 Price Elasticity")
|
| st.metric("Price-Sales Correlation", f"{price_corr:.4f}")
|
| st.info(f"Demand Type: {elasticity}")
|
|
|
|
|
|
|
|
|
| with tab7:
|
| st.markdown('<div class="section-header"><h3>📊 Advanced Analytics</h3></div>', unsafe_allow_html=True)
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
|
|
| if sales_col:
|
| st.markdown("#### 📊 Pareto Analysis (80/20 Rule)")
|
|
|
| sorted_sales = df.sort_values(sales_col, ascending=False).reset_index(drop=True)
|
| sorted_sales["Cumulative_Sales"] = sorted_sales[sales_col].cumsum()
|
| sorted_sales["Cumulative_Pct"] = sorted_sales["Cumulative_Sales"] / sorted_sales[sales_col].sum() * 100
|
|
|
| products_for_80 = (sorted_sales["Cumulative_Pct"] <= 80).sum()
|
| pct_products = products_for_80 / len(df) * 100
|
|
|
| st.metric("Products for 80% Revenue", f"{products_for_80:,}", f"{pct_products:.1f}% of total")
|
|
|
| fig = go.Figure()
|
| fig.add_trace(go.Scatter(
|
| y=sorted_sales["Cumulative_Pct"].values[:1000],
|
| mode='lines',
|
| fill='tozeroy',
|
| line=dict(color='#667eea')
|
| ))
|
| fig.add_hline(y=80, line_dash="dash", line_color="#f093fb")
|
| fig.update_layout(
|
| title='Cumulative Sales Distribution',
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)',
|
| yaxis_title='Cumulative %',
|
| xaxis_title='Products (ranked)'
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
| with col2:
|
|
|
| if sales_col:
|
| st.markdown("#### 🔤 ABC Classification")
|
|
|
| abc_df = df.groupby(df.index)[sales_col].sum().sort_values(ascending=False)
|
| abc_cumsum = abc_df.cumsum() / abc_df.sum() * 100
|
|
|
| total = len(df)
|
| a_count = (abc_cumsum <= 70).sum()
|
| b_count = ((abc_cumsum > 70) & (abc_cumsum <= 90)).sum()
|
| c_count = (abc_cumsum > 90).sum()
|
|
|
| abc_data = pd.DataFrame({
|
| 'Class': ['A (Top 70%)', 'B (Next 20%)', 'C (Bottom 10%)'],
|
| 'Items': [a_count, b_count, c_count],
|
| 'Percentage': [a_count/total*100, b_count/total*100, c_count/total*100]
|
| })
|
|
|
| fig = px.pie(
|
| abc_data, values='Items', names='Class',
|
| title='ABC Classification',
|
| color_discrete_sequence=['#48bb78', '#ecc94b', '#f56565']
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
| if sales_col:
|
| st.markdown("#### 📉 Variance Analysis")
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| mean_val = df[sales_col].mean()
|
| overall_cv = (df[sales_col].std() / mean_val) * 100 if mean_val != 0 else 0
|
| st.metric("Coefficient of Variation", f"{overall_cv:.1f}%", "Sales consistency")
|
| st.metric("Std Deviation", f"{df[sales_col].std():.2f}")
|
|
|
| with col2:
|
| if brand_col:
|
| def safe_cv(x):
|
| if x.mean() == 0:
|
| return 0
|
| return (x.std() / x.mean()) * 100
|
|
|
| brand_cv = df.groupby(brand_col)[sales_col].apply(safe_cv).sort_values()
|
|
|
| fig = px.bar(
|
| x=brand_cv.index, y=brand_cv.values,
|
| title="Variance by Brand (CV% - Lower = More Consistent)",
|
| color=brand_cv.values,
|
| color_continuous_scale='RdYlGn_r'
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)',
|
| showlegend=False
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
| st.markdown("#### 🔗 Correlation Analysis")
|
| num_cols = df.select_dtypes(np.number).columns[:10]
|
| corr_matrix = df[num_cols].corr()
|
|
|
| fig = px.imshow(
|
| corr_matrix,
|
| title="Correlation Heatmap",
|
| color_continuous_scale='RdBu_r',
|
| aspect='auto'
|
| )
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, width="stretch")
|
|
|
|
|
|
|
|
|
| with tab8:
|
| st.markdown('<div class="section-header"><h3>🔄 Comparison Analysis</h3></div>', unsafe_allow_html=True)
|
|
|
| comparison_type = st.radio(
|
| "Compare by:",
|
| ["Brand vs Brand", "Category vs Category", "Time Period vs Time Period"],
|
| horizontal=True
|
| )
|
|
|
| if comparison_type == "Brand vs Brand" and brand_col and sales_col:
|
| col1, col2 = st.columns(2)
|
|
|
| brands_list = sorted(df_original[brand_col].unique().tolist())
|
|
|
| with col1:
|
| brand1 = st.selectbox("Select Brand 1", brands_list, key="brand1")
|
| with col2:
|
| brand2 = st.selectbox("Select Brand 2", brands_list, index=1 if len(brands_list) > 1 else 0, key="brand2")
|
|
|
| df1 = df_original[df_original[brand_col] == brand1]
|
| df2 = df_original[df_original[brand_col] == brand2]
|
|
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| st.markdown(f"### 🏷️ {brand1}")
|
| st.metric("Total Sales", f"{df1[sales_col].sum():,.0f}")
|
| st.metric("Avg Sale", f"{df1[sales_col].mean():.2f}")
|
| if "Profit_Margin" in df1.columns:
|
| st.metric("Avg Margin", f"{df1['Profit_Margin'].mean():.1f}%")
|
| if "CTR" in df1.columns:
|
| st.metric("Avg CTR", f"{df1['CTR'].mean():.2f}%")
|
| if "Ad_ROI" in df1.columns:
|
| st.metric("Avg ROI", f"{df1['Ad_ROI'].mean():.2f}")
|
|
|
| with col2:
|
| st.markdown(f"### 🏷️ {brand2}")
|
| st.metric("Total Sales", f"{df2[sales_col].sum():,.0f}")
|
| st.metric("Avg Sale", f"{df2[sales_col].mean():.2f}")
|
| if "Profit_Margin" in df2.columns:
|
| st.metric("Avg Margin", f"{df2['Profit_Margin'].mean():.1f}%")
|
| if "CTR" in df2.columns:
|
| st.metric("Avg CTR", f"{df2['CTR'].mean():.2f}%")
|
| if "Ad_ROI" in df2.columns:
|
| st.metric("Avg ROI", f"{df2['Ad_ROI'].mean():.2f}")
|
|
|
|
|
| if date_col:
|
| comp_data = df_original[df_original[brand_col].isin([brand1, brand2])]
|
| monthly_comp = comp_data.groupby([comp_data[date_col].dt.to_period("M"), brand_col])[sales_col].sum().unstack()
|
| monthly_comp.index = monthly_comp.index.astype(str)
|
|
|
| fig = go.Figure()
|
| for brand in [brand1, brand2]:
|
| if brand in monthly_comp.columns:
|
| fig.add_trace(go.Scatter(
|
| x=monthly_comp.index, y=monthly_comp[brand],
|
| name=brand, mode='lines+markers'
|
| ))
|
| fig.update_layout(
|
| title=f"Sales Trend: {brand1} vs {brand2}",
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
| elif comparison_type == "Category vs Category" and cat_col and sales_col:
|
| col1, col2 = st.columns(2)
|
|
|
| cats_list = sorted(df_original[cat_col].unique().tolist())
|
|
|
| with col1:
|
| cat1 = st.selectbox("Select Category 1", cats_list, key="cat1")
|
| with col2:
|
| cat2 = st.selectbox("Select Category 2", cats_list, index=1 if len(cats_list) > 1 else 0, key="cat2")
|
|
|
| df1 = df_original[df_original[cat_col] == cat1]
|
| df2 = df_original[df_original[cat_col] == cat2]
|
|
|
|
|
| metrics_df = pd.DataFrame({
|
| 'Metric': ['Total Sales', 'Avg Sale', 'Records'],
|
| cat1: [df1[sales_col].sum(), df1[sales_col].mean(), len(df1)],
|
| cat2: [df2[sales_col].sum(), df2[sales_col].mean(), len(df2)]
|
| })
|
|
|
| st.dataframe(metrics_df, use_container_width=True)
|
|
|
|
|
| fig = go.Figure(data=[
|
| go.Bar(name=cat1, x=['Total Sales', 'Records'], y=[df1[sales_col].sum(), len(df1)]),
|
| go.Bar(name=cat2, x=['Total Sales', 'Records'], y=[df2[sales_col].sum(), len(df2)])
|
| ])
|
| fig.update_layout(
|
| title=f"{cat1} vs {cat2}",
|
| barmode='group',
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, width="stretch")
|
|
|
| elif comparison_type == "Time Period vs Time Period" and date_col and sales_col:
|
| st.markdown("#### Select two time periods to compare")
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| st.markdown("**Period 1**")
|
| p1_start = st.date_input("Start", value=df_original[date_col].min().date(), key="p1s")
|
| p1_end = st.date_input("End", value=df_original[date_col].min().date() + pd.Timedelta(days=30), key="p1e")
|
|
|
| with col2:
|
| st.markdown("**Period 2**")
|
| p2_start = st.date_input("Start", value=df_original[date_col].max().date() - pd.Timedelta(days=30), key="p2s")
|
| p2_end = st.date_input("End", value=df_original[date_col].max().date(), key="p2e")
|
|
|
| df1 = df_original[(df_original[date_col].dt.date >= p1_start) & (df_original[date_col].dt.date <= p1_end)]
|
| df2 = df_original[(df_original[date_col].dt.date >= p2_start) & (df_original[date_col].dt.date <= p2_end)]
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| st.markdown(f"### 📅 Period 1")
|
| st.caption(f"{p1_start} to {p1_end}")
|
| st.metric("Total Sales", f"{df1[sales_col].sum():,.0f}")
|
| st.metric("Daily Avg", f"{df1[sales_col].sum() / max(1, (p1_end - p1_start).days):,.0f}")
|
| st.metric("Records", f"{len(df1):,}")
|
|
|
| with col2:
|
| st.markdown(f"### 📅 Period 2")
|
| st.caption(f"{p2_start} to {p2_end}")
|
| p2_sales = df2[sales_col].sum()
|
| p1_sales = df1[sales_col].sum()
|
| change = ((p2_sales - p1_sales) / p1_sales * 100) if p1_sales > 0 else 0
|
| st.metric("Total Sales", f"{p2_sales:,.0f}", f"{change:+.1f}%")
|
| st.metric("Daily Avg", f"{df2[sales_col].sum() / max(1, (p2_end - p2_start).days):,.0f}")
|
| st.metric("Records", f"{len(df2):,}")
|
|
|
|
|
|
|
|
|
| with tab9:
|
| st.markdown('<div class="section-header"><h3>🔍 Data Explorer</h3></div>', unsafe_allow_html=True)
|
|
|
|
|
| col1, col2, col3 = st.columns(3)
|
|
|
| with col1:
|
| search_col = st.selectbox("Search in column", df.columns.tolist())
|
|
|
| with col2:
|
| search_term = st.text_input("Search term", "")
|
|
|
| with col3:
|
| rows_to_show = st.slider("Rows to display", 10, 500, 100)
|
|
|
|
|
| if search_term:
|
| display_df = df[df[search_col].astype(str).str.contains(search_term, case=False, na=False)]
|
| else:
|
| display_df = df
|
|
|
| st.markdown(f"**Showing {min(rows_to_show, len(display_df)):,} of {len(display_df):,} records**")
|
| st.dataframe(display_df.head(rows_to_show), use_container_width=True)
|
|
|
|
|
| st.markdown("#### 📊 Column Statistics")
|
|
|
| stat_col = st.selectbox("Select column for statistics", df.columns.tolist())
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| if df[stat_col].dtype in ['int64', 'float64']:
|
| stats = df[stat_col].describe()
|
| st.dataframe(stats, use_container_width=True)
|
| else:
|
| value_counts = df[stat_col].value_counts().head(10)
|
| st.markdown("**Top 10 Values**")
|
| st.dataframe(value_counts, use_container_width=True)
|
|
|
| with col2:
|
| if df[stat_col].dtype in ['int64', 'float64']:
|
| fig = px.histogram(df, x=stat_col, title=f"Distribution of {stat_col}",
|
| color_discrete_sequence=['#667eea'])
|
| else:
|
| value_counts = df[stat_col].value_counts().head(10)
|
| fig = px.bar(x=value_counts.index, y=value_counts.values,
|
| title=f"Top 10 {stat_col}", color_discrete_sequence=['#667eea'])
|
|
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, width="stretch")
|
|
|
|
|
| st.markdown("#### 🔗 Quick Correlation Check")
|
|
|
| numeric_cols = df.select_dtypes(include=[np.number]).columns.tolist()
|
|
|
| if len(numeric_cols) >= 2:
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| corr_col1 = st.selectbox("Column X", numeric_cols, key="corr1")
|
| with col2:
|
| corr_col2 = st.selectbox("Column Y", numeric_cols, index=1 if len(numeric_cols) > 1 else 0, key="corr2")
|
|
|
| corr_value = df[corr_col1].corr(df[corr_col2])
|
| st.metric(f"Correlation: {corr_col1} vs {corr_col2}", f"{corr_value:.4f}")
|
|
|
|
|
| sample_df = df.sample(min(1000, len(df)))
|
| fig = px.scatter(sample_df, x=corr_col1, y=corr_col2,
|
| title=f"Scatter: {corr_col1} vs {corr_col2}",
|
| opacity=0.5)
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| plot_bgcolor='rgba(0,0,0,0)'
|
| )
|
| st.plotly_chart(fig, width="stretch")
|
|
|
|
|
|
|
|
|
| with tab10:
|
| st.markdown('<div class="section-header"><h3>🎯 Goals & Target Tracking</h3></div>', unsafe_allow_html=True)
|
|
|
| st.markdown("Set your business targets and track progress")
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| st.markdown("#### 📊 Set Targets")
|
|
|
| if sales_col:
|
| sales_target = st.number_input("Sales Target", value=int(df[sales_col].sum() * 1.2), step=10000)
|
|
|
| margin_target = st.number_input("Profit Margin Target (%)", value=50.0, step=1.0)
|
|
|
| ctr_target = st.number_input("CTR Target (%)", value=12.0, step=0.5)
|
|
|
| roi_target = st.number_input("Ad ROI Target", value=1.0, step=0.1)
|
|
|
| with col2:
|
| st.markdown("#### 📈 Current Progress")
|
|
|
| if sales_col:
|
| current_sales = df[sales_col].sum()
|
| sales_progress = min(100, (current_sales / sales_target) * 100) if sales_target > 0 else 0
|
|
|
|
|
| fig = go.Figure(go.Indicator(
|
| mode="gauge+number+delta",
|
| value=current_sales,
|
| delta={'reference': sales_target},
|
| title={'text': "Sales Progress", 'font': {'color': 'white'}},
|
| gauge={
|
| 'axis': {'range': [0, sales_target * 1.2]},
|
| 'bar': {'color': "#667eea"},
|
| 'bgcolor': "rgba(255,255,255,0.1)",
|
| 'steps': [
|
| {'range': [0, sales_target * 0.5], 'color': "#f56565"},
|
| {'range': [sales_target * 0.5, sales_target * 0.8], 'color': "#ecc94b"},
|
| {'range': [sales_target * 0.8, sales_target], 'color': "#48bb78"}
|
| ],
|
| 'threshold': {
|
| 'line': {'color': "white", 'width': 4},
|
| 'thickness': 0.75,
|
| 'value': sales_target
|
| }
|
| }
|
| ))
|
| fig.update_layout(
|
| template='plotly_dark',
|
| paper_bgcolor='rgba(0,0,0,0)',
|
| height=250
|
| )
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
| st.markdown("#### 📋 Target Summary")
|
|
|
| targets_data = []
|
|
|
| if sales_col:
|
| targets_data.append({
|
| 'Metric': 'Sales',
|
| 'Target': f"{sales_target:,.0f}",
|
| 'Current': f"{df[sales_col].sum():,.0f}",
|
| 'Progress': f"{min(100, (df[sales_col].sum() / sales_target) * 100):.1f}%" if sales_target > 0 else "0%",
|
| 'Status': "✅" if df[sales_col].sum() >= sales_target else "⏳"
|
| })
|
|
|
| if "Profit_Margin" in df.columns:
|
| current_margin = df['Profit_Margin'].mean()
|
| targets_data.append({
|
| 'Metric': 'Profit Margin',
|
| 'Target': f"{margin_target:.1f}%",
|
| 'Current': f"{current_margin:.1f}%",
|
| 'Progress': f"{min(100, (current_margin / margin_target) * 100):.1f}%" if margin_target > 0 else "0%",
|
| 'Status': "✅" if current_margin >= margin_target else "⏳"
|
| })
|
|
|
| if "CTR" in df.columns:
|
| current_ctr = df['CTR'].mean()
|
| targets_data.append({
|
| 'Metric': 'CTR',
|
| 'Target': f"{ctr_target:.2f}%",
|
| 'Current': f"{current_ctr:.2f}%",
|
| 'Progress': f"{min(100, (current_ctr / ctr_target) * 100):.1f}%" if ctr_target > 0 else "0%",
|
| 'Status': "✅" if current_ctr >= ctr_target else "⏳"
|
| })
|
|
|
| if "Ad_ROI" in df.columns:
|
| current_roi = df['Ad_ROI'].mean()
|
| targets_data.append({
|
| 'Metric': 'Ad ROI',
|
| 'Target': f"{roi_target:.2f}",
|
| 'Current': f"{current_roi:.2f}",
|
| 'Progress': f"{min(100, (current_roi / roi_target) * 100):.1f}%" if roi_target > 0 else "0%",
|
| 'Status': "✅" if current_roi >= roi_target else "⏳"
|
| })
|
|
|
| if targets_data:
|
| targets_df = pd.DataFrame(targets_data)
|
| st.dataframe(targets_df, use_container_width=True)
|
|
|
|
|
| st.markdown("#### 💡 Recommendations to Hit Targets")
|
|
|
| recommendations = []
|
|
|
| if sales_col and df[sales_col].sum() < sales_target:
|
| gap = sales_target - df[sales_col].sum()
|
| recommendations.append(f"📊 **Sales Gap**: Need {gap:,.0f} more sales. Consider increasing ad spend or running promotions.")
|
|
|
| if "Profit_Margin" in df.columns and df['Profit_Margin'].mean() < margin_target:
|
| recommendations.append("💰 **Margin Gap**: Focus on higher-margin products or reduce costs.")
|
|
|
| if "CTR" in df.columns and df['CTR'].mean() < ctr_target:
|
| recommendations.append("🖱️ **CTR Gap**: Improve ad creative and targeting to boost click-through rates.")
|
|
|
| if "Ad_ROI" in df.columns and df['Ad_ROI'].mean() < roi_target:
|
| recommendations.append("📢 **ROI Gap**: Optimize ad spend allocation - shift budget to better-performing campaigns.")
|
|
|
| if recommendations:
|
| for rec in recommendations:
|
| st.markdown(rec)
|
| else:
|
| st.success("🎉 Congratulations! You're meeting or exceeding all targets!")
|
|
|
|
|
|
|
|
|
| with tab11:
|
| st.markdown('<div class="section-header"><h3>🛒 Conversion & Position Analysis</h3></div>', unsafe_allow_html=True)
|
|
|
| if position_col:
|
| st.info("Search Position analysis has been moved to the Marketing tab.")
|
|
|
| if cart_col:
|
| st.markdown("---")
|
| st.markdown("#### 🛒 Cart Abandonment Analysis")
|
|
|
| total_carts = df[cart_col].sum()
|
|
|
| col1, col2, col3 = st.columns(3)
|
| with col1:
|
| st.metric("Total Abandoned Carts", f"{total_carts:,.0f}")
|
|
|
| with col2:
|
| avg_carts = df[cart_col].mean()
|
| st.metric("Avg Carts/Product", f"{avg_carts:.2f}")
|
|
|
| with col3:
|
| if price_col:
|
| lost_revenue = (df[cart_col] * df[price_col]).sum()
|
| st.metric("Est. Lost Revenue", f"${lost_revenue:,.0f}", help="Total Abandoned Carts * Price")
|
|
|
|
|
| if sales_col:
|
| sample_size = min(1000, len(df))
|
| chart_df = df.sample(sample_size)
|
|
|
| fig = px.scatter(
|
| chart_df, x=cart_col, y=sales_col,
|
| title=f"Sales vs Abandoned Carts (Sample {sample_size})",
|
| opacity=0.6,
|
| trendline="ols" if sample_size > 10 else None
|
| )
|
| apply_chart_style(fig)
|
| st.plotly_chart(fig, use_container_width=True)
|
|
|
|
|
| corr = df[sales_col].corr(df[cart_col])
|
| st.info(f"💡 Correlation between Sales and Abandoned Carts: **{corr:.2f}**")
|
|
|
|
|
|
|
|
|
| with tab12:
|
| st.markdown('<div class="section-header"><h3>🤖 Auto Insight Engine</h3></div>', unsafe_allow_html=True)
|
| st.markdown("AI-powered analysis of your sales performance.")
|
|
|
| if st.button("🚀 Generate Insights"):
|
| with st.spinner("Analyzing data patterns..."):
|
| insights = auto_insight_engine(df, date_col, sales_col, inventory_col)
|
|
|
| if not insights:
|
| st.info("No significant anomalies or strong trends detected at this time.")
|
| else:
|
| for type_, title, msg in insights:
|
| if type_ == "success":
|
| st.success(f"**{title}**\n\n{msg}")
|
| elif type_ == "warning":
|
| st.warning(f"**{title}**\n\n{msg}")
|
| elif type_ == "error":
|
| st.error(f"**{title}**\n\n{msg}")
|
| else:
|
| st.info(f"**{title}**\n\n{msg}")
|
|
|
|
|
|
|
|
|
| st.markdown('<div class="section-header"><h3>📝 Executive Summary</h3></div>', unsafe_allow_html=True)
|
|
|
| col1, col2 = st.columns(2)
|
|
|
| with col1:
|
| st.markdown("""
|
| <div class="info-box">
|
| <h4 style="color: #667eea; margin-top: 0;">🎯 Key Insights</h4>
|
| <ul style="color: #e2e8f0;">
|
| """, unsafe_allow_html=True)
|
|
|
| insights = []
|
| if sales_col:
|
| insights.append(f"<li>Total Sales Volume: <strong>{df[sales_col].sum():,.0f}</strong></li>")
|
| if "Profit_Margin" in df.columns:
|
| insights.append(f"<li>Average Profit Margin: <strong>{df['Profit_Margin'].mean():.1f}%</strong></li>")
|
| if "Ad_ROI" in df.columns:
|
| insights.append(f"<li>Average Ad ROI: <strong>{df['Ad_ROI'].mean():.2f}</strong> sales per dollar</li>")
|
| if date_col and sales_col:
|
| monthly = df.groupby(df[date_col].dt.to_period("M"))[sales_col].sum()
|
| volatility = monthly.pct_change().abs().mean() * 100
|
| insights.append(f"<li>Monthly Volatility: <strong>{volatility:.1f}%</strong></li>")
|
|
|
| st.markdown("".join(insights) + "</ul></div>", unsafe_allow_html=True)
|
|
|
| with col2:
|
| st.markdown("""
|
| <div class="success-box">
|
| <h4 style="color: #48bb78; margin-top: 0;">✅ Recommendations</h4>
|
| <ol style="color: #e2e8f0;">
|
| <li>Focus on high-margin brands for profitability</li>
|
| <li>Reallocate marketing budget based on ROI</li>
|
| <li>Address low inventory turnover items</li>
|
| <li>Optimize pricing for price-sensitive segments</li>
|
| <li>Focus on Class A products for maximum impact</li>
|
| </ol>
|
| </div>
|
| """, unsafe_allow_html=True)
|
|
|
|
|
|
|
|
|
| st.markdown('<div class="section-header"><h3>📥 Export Data</h3></div>', unsafe_allow_html=True)
|
|
|
| col1, col2, col3 = st.columns(3)
|
|
|
| with col1:
|
| if st.button("📊 Download Summary CSV", use_container_width=True):
|
| summary_data = {
|
| "Metric": ["Total Sales", "Total Records", "Avg Profit Margin", "Avg CTR", "Avg Ad ROI"],
|
| "Value": [
|
| df[sales_col].sum() if sales_col else 0,
|
| len(df),
|
| df["Profit_Margin"].mean() if "Profit_Margin" in df.columns else 0,
|
| df["CTR"].mean() if "CTR" in df.columns else 0,
|
| df["Ad_ROI"].mean() if "Ad_ROI" in df.columns else 0
|
| ]
|
| }
|
| summary_df = pd.DataFrame(summary_data)
|
| st.download_button(
|
| "⬇️ Download",
|
| summary_df.to_csv(index=False),
|
| "analysis_summary.csv",
|
| "text/csv"
|
| )
|
|
|
| with col2:
|
| if brand_col and sales_col:
|
| if st.button("🏷️ Download Brand Report", use_container_width=True):
|
| brand_report = df.groupby(brand_col).agg({sales_col: 'sum'}).round(2)
|
| if "Profit_Margin" in df.columns:
|
| brand_report["Avg_Margin"] = df.groupby(brand_col)["Profit_Margin"].mean()
|
| if "Ad_ROI" in df.columns:
|
| brand_report["Avg_ROI"] = df.groupby(brand_col)["Ad_ROI"].mean()
|
| st.download_button(
|
| "⬇️ Download",
|
| brand_report.to_csv(),
|
| "brand_analysis.csv",
|
| "text/csv"
|
| )
|
|
|
| with col3:
|
| if st.button("📋 Download Full Data", use_container_width=True):
|
| st.download_button(
|
| "⬇️ Download",
|
| df.to_csv(index=False),
|
| "full_data_with_metrics.csv",
|
| "text/csv"
|
| )
|
|
|
|
|
| st.markdown("""
|
| <div style="text-align: center; padding: 40px 0 20px 0; color: #718096;">
|
| <p>Built with ❤️ using Streamlit | Sales Insight Dashboard v1.0</p>
|
| </div>
|
| """, unsafe_allow_html=True)
|
|
|
|
|
