Sales Dashboard Agent - Natural Language to Code Analytics

📋 Project Overview & Problem Statement

Challenge: Business analysts spend hours writing SQL queries and Python code to extract insights from sales data. Non-technical stakeholders struggle to get timely answers without developer support. Traditional BI dashboards are rigid, require upfront configuration, and don't adapt to ad-hoc questions.

Solution: Sales Dashboard Agent converts natural language business questions into executable Python analytics code. Ask questions in plain English like "What were our top 5 products by revenue in Q4?" and get instant answers with automated visualizations. The "code-as-plan" pattern provides full transparency - you can see and verify the exact analytics code generated by the AI.

Key Benefits

Natural Language Interface: Ask business questions in plain English without SQL or Python knowledge
Code Transparency: See the exact Python code generated for every query
Auto-Visualizations: Request charts with phrases like "show me a bar chart"
Safe Execution: Sandboxed read-only environment prevents data corruption
Fast Insights: 1-3 seconds from question to answer with visualizations

🤖 Code-as-Plan Architecture

💬 Natural Language Processing

Gemini 2.0 Flash analyzes business questions and understands intent: filtering, aggregation, grouping, visualization requests.

🔧 Code Generation

AI generates executable Python code with TinyDB queries dynamically based on your data schema and question context.

🔒 Safe Execution

Code runs in sandboxed environment with read-only access. Only safe operations allowed (aggregations, filtering, visualization).

📊 Auto-Visualization

When you request charts, AI generates complete Matplotlib/Seaborn code with proper labels, titles, and formatting.

How It Works

User Question → AI Code Generation → Safe Execution → Results

Step 1: User asks: "Which region had the highest profit in 2024?"
Step 2: Gemini generates TinyDB query and aggregation code
Step 3: Code executes in controlled namespace (no file access, no mutations)
Step 4: Returns answer: "West region with $2.3M profit (45% margin)"
Optional: If chart requested, generates and displays visualization

📖 Sample Queries & Capabilities

Query Category	Example Questions
Revenue & Profit	"What were total sales in November 2024?" "Which region generated the most profit?" "Show revenue trends by month in a line chart"
Product Analysis	"Top 5 best-selling products by revenue" "Which products have profit margins above 30%?" "Show product performance in a bar chart"
Customer Insights	"Which customer spent the most in electronics?" "Top 10 customers by quantity ordered" "Show customer distribution by region"
Category Performance	"Average order value for each category" "Revenue breakdown by product category" "Which category has the lowest margin?"
Margin Analysis	"What products have margins below 10%?" "Calculate profit margin by region" "Show margin distribution in table format"

🛠️ Technical Architecture

AI & Processing Stack

Gemini 2.0 Flash Experimental TinyDB Python 3.8+ Google Colab Pandas

Data Visualization

Matplotlib Seaborn HTML Output Color-Coded Logs

Code Execution Workflow

Prompt Engineering & Safe Execution:

Schema Context: AI receives database schema with sample records
Example Patterns: Prompt includes TinyDB query examples for guidance
Output Requirements: Must set answer_text with human-friendly summary
Visualization Patterns: Includes Matplotlib/Seaborn code examples
Safe Globals: Only approved functions (sum, len, sorted, Query)

Data Schema Support

Sales Order Schema (Customizable):

Order Info: Order_ID, Order_Date, Customer_Name
Location: City, State, Region, Country
Products: Category, Sub_Category, Product_Name
Metrics: Quantity, Unit_Price, Revenue, Profit

📖 Usage Examples & Code Generation

Example 1: Simple Query

# User asks:
"What were our total sales in November 2024?"

# AI generates this code:
Item = Query()
nov_orders = orders_tbl.search(
    (Item.Order_Date >= '2024-11-01') &
    (Item.Order_Date <= '2024-11-30')
)
nov_revenue = sum(o['Revenue'] for o in nov_orders)
answer_text = f"November 2024: ${nov_revenue:,.2f} from {len(nov_orders)} orders."

# Result:
"November 2024: $1,234,567.89 from 2,845 orders."
            

Example 2: Top Products with Aggregation

# User asks:
"Show me the top 5 products by profit"

# AI generates this code:
from collections import defaultdict
product_profit = defaultdict(float)
for order in orders_tbl.all():
    product_profit[order['Product_Name']] += order['Profit']

top_5 = sorted(product_profit.items(), key=lambda x: x[1], reverse=True)[:5]
answer_text = f"Top product: {top_5[0][0]} with ${top_5[0][1]:,.2f} profit."

# Result:
"Top product: MacBook Pro 16\" with $45,230.50 profit."
            

Example 3: Visualization Request

# User asks:
"Show me a bar chart of total revenue by region"

# AI generates this code:
import matplotlib.pyplot as plt
region_revenue = {}
for order in orders_tbl.all():
    region = order['Region']
    revenue = order['Revenue']
    region_revenue[region] = region_revenue.get(region, 0) + revenue

plt.figure(figsize=(10, 6))
plt.bar(region_revenue.keys(), region_revenue.values())
plt.title('Total Revenue by Region', fontsize=14, fontweight='bold')
plt.xlabel('Region')
plt.ylabel('Revenue ($)')
plt.show()

answer_text = f"West region leads with ${max(region_revenue.values()):,.2f}"

# Result: Bar chart displayed + summary text
            

🚀 Getting Started with Google Colab

Quick Start (3 Steps)

Step 1: Open Google Colab Notebook
Step 2: Upload your sales dataset CSV file when prompted
Step 3: Add Gemini API key:
- Get free key from Google AI Studio
- Paste directly in notebook or use Colab Secrets

Interactive Query Interface

How to use the agent:

Ask Questions: Type natural language queries in plain English
View Code: See the exact Python code generated by AI
Get Answers: Receive business-friendly summaries with metrics
Request Charts: Add "show in bar chart" or "display line chart"
Iterate: Refine questions based on initial results

📊 Performance & Security Metrics

1-3s

Query Response Time

95%+

Code Generation Accuracy

100K+

Supported Order Records

100%

Read-Only Safety

Security Features

Sandboxed Execution: No file system access, network calls, or system commands
Read-Only Operations: Database mutations (insert, update, delete) blocked
Controlled Namespace: Only whitelisted functions and libraries allowed
No Data Persistence: In-memory database, data cleared after session
Code Transparency: Every query shows generated code before execution

Supported Chart Types

Bar Charts: Revenue/profit by category, region, product, customer
Line Charts: Time series trends (daily, monthly, quarterly)
Scatter Plots: Price vs profit, quantity vs revenue correlations
Tables: Formatted data displays, sorted rankings, filtered results

🎯 Business Use Cases

Sales Analytics

Track revenue and profit trends over time
Identify top-performing products, regions, and customers
Analyze seasonal patterns and growth rates
Compare performance across product categories

Product Performance

Calculate profit margins by product and category
Find underperforming products (low margins, low sales)
Identify bestsellers and top revenue generators
Analyze price-profit relationships

Customer Insights

Segment customers by spending, order frequency, and preferences
Identify high-value customers and VIPs
Analyze customer distribution by region and category
Track customer lifetime value

Operational Intelligence

Monitor inventory turnover and demand
Optimize pricing strategies based on margin analysis
Forecast revenue based on historical trends
Identify cross-selling and bundling opportunities

📊 Sales Dashboard Agent