Showcase: Streamlit BI Dashboard with Google Analytics & E-commerce Intelligence - Democratizing Business Analytics

As data continues to drive business decisions, the need for accessible, powerful, and intelligent analytics tools has never been greater. Today, I'm excited to introduce an open-source business intelligence dashboard that combines the simplicity of Streamlit with the power of BigQuery and cutting-edge AI capabilities.

Ready to build your own? This project is designed to be your starting point. Whether you want to create a custom dashboard for your organization, experiment with AI-powered analytics, or learn modern data engineering patterns, this codebase provides everything you need to get started.

What We Built

Our Streamlit Business Intelligence Dashboard is a comprehensive analytics platform that tackles two critical business domains. The first focuses on Google Analytics 4 (GA4) Analytics, providing deep dives into web traffic, user behavior, and conversion patterns. The second specializes in E-commerce Analytics, delivering complete business intelligence for online retail operations.

What sets this apart from other BI tools is the integration of an AI Data Agent that lets users query their data using natural language, powered by Google's Gemini model and LangGraph.

The Technical Architecture

Modern Python Stack

The foundation is built on a carefully selected modern Python stack:

# Core framework
streamlit = "^1.45.1"
uv = "Fast package management"

# Data layer
google-cloud-bigquery = "^3.34.0"
pandas = "^2.2.3"
polars = "^1.30.0"  # High-performance alternative

# AI & LLM
langgraph = "^0.4.8"
langchain-google-genai = "^2.1.5"
google-genai = "^1.19.0"

# Visualization
plotly = "^6.1.2"
pygwalker = "^0.4.9.15"  # Drag-and-drop EDA

Modular Component Architecture

One of the key design decisions was creating a modular component system. Each analysis type is a separate Python module:

components/
├── ga4/                    # Google Analytics components
│   ├── basic_metrics.py
│   ├── session_anomaly.py
│   ├── user_path.py
│   └── ...
└── ec/                     # E-commerce components
    ├── executive_overview.py
    ├── product_merchandising.py
    ├── data_agent/         # AI agent implementation
    └── ...

This architecture provides maintainability through self-contained components, reusability across dashboards, isolated testing capabilities, and seamless scalability for new analysis types.

Intelligent Data Caching

Performance is crucial for BI dashboards. We implemented comprehensive caching strategies:

@st.cache_data(show_spinner="Querying daily revenue & orders …")
def q_daily_sales(start: date, end: date) -> pd.DataFrame:
    q = f"""
        WITH daily AS (
            SELECT DATE(o.created_at) AS day,
                   COUNT(DISTINCT o.order_id) AS orders,
                   SUM(oi.sale_price) AS revenue
            FROM `bigquery-public-data.thelook_ecommerce.order_items` oi
            JOIN `bigquery-public-data.thelook_ecommerce.orders` o
              ON oi.order_id = o.order_id
            WHERE o.created_at BETWEEN '{iso_format(start)}' AND '{iso_format(end)}'
            GROUP BY day
        )
        SELECT * FROM daily ORDER BY day
    """
    return bigquery_client.query(q).to_dataframe()

Every data-fetching function uses @st.cache_data to ensure fast response times for repeated queries, reduced BigQuery costs by minimizing duplicate queries, and better user experience with loading indicators.

The AI Data Agent Transforms Natural Language to Insights

The AI Data Agent transforms natural language into actionable insights using LangGraph and Google Gemini. Users ask questions like "What are the top 5 products by revenue this month?" and the system automatically generates BigQuery SQL, executes queries, and provides analysis:

SELECT
  p.name,
  SUM(oi.sale_price) as revenue
FROM `bigquery-public-data.thelook_ecommerce.order_items` oi
JOIN `bigquery-public-data.thelook_ecommerce.products` p ON oi.product_id = p.id
WHERE DATE(oi.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 5

The agent maintains conversation context through sophisticated state management, enabling iterative analysis and follow-up questions.

Real-World Applications

Google Analytics 4 Dashboard

The GA4 component provides comprehensive web analytics. Traffic Analysis covers sessions, pageviews, and bounce rates with trend detection. User Behavior analysis examines new vs returning visitors and user journey mapping. Performance Monitoring includes session anomaly detection using 7-day moving averages. Attribution Analysis tracks channel performance and conversion metrics. Geographic Intelligence provides user distribution and regional performance insights.

E-commerce Intelligence

The e-commerce module covers the complete business spectrum. The Executive Dashboard presents KPIs, revenue trends, and growth metrics. Product Intelligence delivers performance analysis and inventory optimization. Customer Analytics provides RFM segmentation, demographics, and lifetime value calculations. Operational Insights cover supply chain metrics and geographic distribution. Profit Analysis includes margin analysis and category performance evaluation.

Getting Started

Setup is straightforward with modern Python tooling:

# Clone and install
git clone https://github.com/squadbase/streamlit-business-intelligence
cd streamlit-business-intelligence
uv sync

# Configure environment
cp .env.example .env
# Add your API keys:
# SERVICE_ACCOUNT_JSON_BASE64=your_bigquery_service_account
# GOOGLE_API_KEY=your_gemini_api_key
# E2B_API_KEY=your_code_execution_key

# Run the application
streamlit run Home.py

The application uses Base64-encoded service account authentication for BigQuery with required permissions: BigQuery Job User and BigQuery Read Session User.

Extending the Platform

Adding New Analysis Components

Creating new components follows a simple pattern:

# components/your_domain/new_analysis.py
import streamlit as st
import pandas as pd
from lib.bigquery_client import bigquery_client

@st.cache_data(show_spinner="Loading analysis...")
def fetch_analysis_data() -> pd.DataFrame:
    query = """
    SELECT * FROM your_analysis_query
    """
    return bigquery_client.query(query).to_dataframe()

def new_analysis_component():
    st.subheader("Your New Analysis")
    data = fetch_analysis_data()
    fig = px.bar(data, x='category', y='value')
    st.plotly_chart(fig, use_container_width=True)

Enhancing the AI Agent

Adding new capabilities to the AI agent is straightforward:

# components/ec/data_agent/tools.py
@tool
def your_custom_tool(query: str) -> str:
    """Custom tool for specific analysis needs."""
    # Implementation here
    return result

# Add to tools list
all_tools = [execute_bigquery_sql, execute_python_code, your_custom_tool]

Deployment Strategies

Development

streamlit run Home.py

From Development to Production Using Squadbase

Once you've customized your dashboard, Squadbase makes deployment effortless:

🔐 Member-Only Access provides perfect solutions for internal company dashboards or client-specific analytics. Set up user groups and permissions in minutes, control who sees what data with role-based access, and eliminate the need to build authentication from scratch.

🚀 One-Click Deployment makes deployment effortless by automatically deploying your latest changes when you push to your Git repository.

# Just push to your Git repository
git push origin main
# Squadbase automatically deploys your latest changes

✨ Enterprise Features Built-In include automatic SSL certificates and secure hosting, usage analytics to see how your team uses the dashboard, a built-in feedback system for continuous improvement, and Git-based CI/CD where every push triggers a new deployment.

Perfect for teams who want to focus on building insights, not managing infrastructure.

Why This Matters

Business intelligence shouldn't be locked behind expensive enterprise licenses or require specialized teams to deploy. This project democratizes access to powerful analytics tools while showcasing how modern Python, AI, and cloud technologies can work together.

The combination of Streamlit's simplicity, BigQuery's power, and AI's intelligence creates a platform that's both accessible to business users and extensible for developers.

Your Dashboard Awaits

Ready to build something amazing?

The complete setup is covered in the Getting Started section above. Customize with your own datasets, branding, and analysis components, then deploy securely with Squadbase for your team.

Links:

• 🔗 GitHub Repository
• 📖 Documentation