Welcome to Phase 8.1 Visualizing Your Web Analytics Insights
You have successfully applied machine learning to segment users and predict bounce rate. That is a significant achievement in your web analytics project. Now it is time to bring all your hard work to life. In this phase we will learn how to create an interactive web dashboard using Streamlit. Think of this as building the control panel for your website's performance. It allows you to visually present all the key insights you have gathered and the models you have built in an easy to understand and interactive way. This is crucial for sharing your findings with stakeholders and making data driven decisions.
This step is crucial for effective data storytelling and making your analytics accessible.
Streamlit is an amazing open source Python library that makes it incredibly easy to create beautiful custom web apps for data science and machine learning. Here is why it is perfect for your web analytics dashboard.
• Rapid Development Build interactive dashboards with just a few lines of Python code. No need for complex web development frameworks like Flask or React.
• Pure Python Write your entire dashboard in Python. This means you can reuse your data fetching and analysis code directly.
• Interactivity Add sliders buttons text inputs and more to allow users to explore data dynamically.
• Data Storytelling Present your insights with compelling charts tables and metrics. Guide your audience through your findings.
• Deployment Friendly Streamlit apps can be easily deployed and shared with others.
• Integration with ML Models You can directly integrate your machine learning models (like the bounce prediction model) into the dashboard for real time predictions.
Streamlit empowers you to transform your data analysis into an engaging and accessible web application.
Streamlit App Structure: A Streamlit app is simply a Python script. The script runs from top to bottom every time a user interacts with it. This makes the app reactive and ensures it always displays the latest output based on user input.
Widgets: These are interactive elements such as st.slider(), st.button(), and st.selectbox() that allow users to control the data displayed or choose how the analysis is performed. They make the dashboard interactive and easy to explore.
Display Elements: Functions like st.write(), st.dataframe(), st.metric(), st.line_chart(), and st.bar_chart() are used to show text, tables, key metrics, and charts in your dashboard. These help you present your data clearly and visually.
Data Caching: Streamlit’s @st.cache_data decorator helps improve performance. It stores the results of data loading functions so the app doesn’t fetch the same data from the database again unless the input changes. This makes your dashboard load faster and run more efficiently.
Connecting to SQL Server: You will use pyodbc to connect your Streamlit app to your local SQL Server database. This is the same method used in earlier phases of your project to fetch and analyze web data.
We will create a simple Streamlit dashboard that connects to your SQL Server database. It will fetch overall website metrics like total users sessions and page views. It will display these as key performance indicators KPIs. It will also show a simple trend chart.
Practical Python Code Example (dashboard_app.py)
Here is the basic Python code you will write for your Streamlit dashboard. Save this file in your 'dashboard/' folder.
# SCRIPT SETUP 1: Import necessary libraries
import streamlit as st
import pandas as pd
import pyodbc
from datetime import datetime
# SCRIPT SETUP 2: Database connection details for SQL Server
# Replace with your actual server name, database name, and credentials
DB_CONFIG = {
'driver': '{ODBC Driver 17 for SQL Server}',
'server': 'YOUR_SQL_SERVER_NAME', # e.g., 'localhost' or 'SERVER_NAME\SQLEXPRESS'
'database': 'YOUR_DATABASE_NAME',
'uid': 'YOUR_USERNAME',
'pwd': 'YOUR_PASSWORD'
}
# FUNCTION 1: Connect to the database (cached for performance)
@st.cache_resource # Use st.cache_resource for connection objects
def connect_to_db():
"""Establishes a connection to the SQL Server database and caches it."""
conn_str = (
f"DRIVER={DB_CONFIG['driver']};"
f"SERVER={DB_CONFIG['server']};"
f"DATABASE={DB_CONFIG['database']};"
f"UID={DB_CONFIG['uid']};"
f"PWD={DB_CONFIG['pwd']};"
)
try:
conn = pyodbc.connect(conn_str)
print("Successfully connected to SQL Server from Streamlit.") # This will print in the console
return conn
except pyodbc.Error as ex:
sqlstate = ex.args[0]
st.error(f"Database connection error: {sqlstate}")
st.exception(ex) # Display full exception in Streamlit app
return None
# FUNCTION 2: Fetch overall website metrics (cached for performance)
@st.cache_data # Use st.cache_data for dataframes
def fetch_overall_metrics(conn):
"""Fetches overall website metrics (total users, sessions, page views)."""
query = """
SELECT
COUNT(DISTINCT user_pseudo_id) AS total_users,
COUNT(DISTINCT session_id) AS total_sessions,
COUNT(CASE WHEN event_name = 'page_view' THEN 1 END) AS total_page_views
FROM
events;
"""
try:
df = pd.read_sql(query, conn)
return df.iloc[0] # Return the single row of results
except Exception as e:
st.error(f"Error fetching overall metrics: {e}")
st.exception(e)
return None
# FUNCTION 3: Fetch daily traffic trends (cached for performance)
@st.cache_data
def fetch_daily_trends(conn):
"""Fetches daily traffic trends."""
query = """
SELECT
CAST(DATEADD(s, event_timestamp / 1000000, '1970-01-01') AS DATE) AS event_date,
COUNT(DISTINCT user_pseudo_id) AS daily_users,
COUNT(DISTINCT session_id) AS daily_sessions,
COUNT(CASE WHEN event_name = 'page_view' THEN 1 END) AS daily_page_views
FROM
events
GROUP BY
CAST(DATEADD(s, event_timestamp / 1000000, '1970-01-01') AS DATE)
ORDER BY
event_date;
"""
try:
df = pd.read_sql(query, conn)
df['event_date'] = pd.to_datetime(df['event_date'])
return df
except Exception as e:
st.error(f"Error fetching daily trends: {e}")
st.exception(e)
return None
# MAIN STREAMLIT APP LAYOUT
def main():
st.set_page_config(layout="wide", page_title="Sankalan Web Analytics Dashboard")
st.title("📊 SankalanTech.com Web Analytics Dashboard")
st.markdown("---")
# Connect to database
conn = connect_to_db()
if conn is None:
st.warning("Could not connect to the database. Please check your DB_CONFIG.")
return
# Fetch overall metrics
overall_metrics = fetch_overall_metrics(conn)
if overall_metrics is None:
st.error("Failed to load overall metrics. Check database connection and query.")
return
# Display KPIs
st.header("Overall Website Performance")
col1, col2, col3 = st.columns(3)
with col1:
st.metric(label="Total Users", value=f"{overall_metrics['total_users']:,}")
with col2:
st.metric(label="Total Sessions", value=f"{overall_metrics['total_sessions']:,}")
with col3:
st.metric(label="Total Page Views", value=f"{overall_metrics['total_page_views']:,}")
st.markdown("---")
# Daily Trends Chart
st.header("Daily Traffic Trends")
daily_trends_df = fetch_daily_trends(conn)
if daily_trends_df is not None and not daily_trends_df.empty:
# Create an interactive date range slider
min_date = daily_trends_df['event_date'].min().date()
max_date = daily_trends_df['event_date'].max().date()
date_range = st.slider(
"Select Date Range",
min_value=min_date,
max_value=max_date,
value=(min_date, max_date),
format="YYYY-MM-DD"
)
filtered_trends_df = daily_trends_df[
(daily_trends_df['event_date'].dt.date >= date_range[0]) &
(daily_trends_df['event_date'].dt.date <= date_range[1])
]
# Display line charts for trends
st.subheader("Daily Users")
st.line_chart(filtered_trends_df.set_index('event_date')['daily_users'])
st.subheader("Daily Sessions")
st.line_chart(filtered_trends_df.set_index('event_date')['daily_sessions'])
st.subheader("Daily Page Views")
st.line_chart(filtered_trends_df.set_index('event_date')['daily_page_views'])
st.subheader("Raw Daily Data")
st.dataframe(filtered_trends_df)
else:
st.warning("No daily trend data available to display.")
st.markdown("---")
st.info("This is a basic dashboard. Expand it with more metrics, charts, and interactive features!")
# Close connection (Streamlit handles this with st.cache_resource, but explicit close is good practice if not cached)
# conn.close() # No need to explicitly close if using st.cache_resource, Streamlit manages it.
if __name__ == "__main__":
main()
Important Notes on This Code:
This Streamlit script sets up a simple and effective web analytics dashboard. It connects to your SQL Server database using pyodbc and retrieves data for display. The @st.cache_resource decorator is used to ensure the database connection is established only once. The @st.cache_data decorator is used to fetch data efficiently. This makes sure the data is only queried again when inputs change which helps keep the dashboard responsive.
The dashboard shows overall metrics as KPIs including total users sessions and page views. It also displays daily traffic trends using interactive line charts. An interactive date range slider allows users to filter and explore the trend data easily.
Before running the app fill in your actual SQL Server connection details in the DB CONFIG section. This includes your server name database name username and password.
To run the dashboard app follow these steps:
Install Streamlit using pip install streamlit
Open your command prompt or terminal
Go to your dashboard/ folder
Run the command streamlit run dashboard_app.py
Understanding Your Python Streamlit Dashboard Script
This Python script is your first interactive web dashboard. It brings your web analytics data to life in a visual and accessible way. Let us break down each part of the code.
1. Setting Up Your Tools and Connections (Refer to SCRIPT SETUP 1, 2 in code)
At the very top of the script you see some import lines. These lines bring in the necessary tools for our work.
import streamlit as st (SCRIPT SETUP 1) This is the core library for building our web app
import pandas as pd (SCRIPT SETUP 1) This brings in Pandas for data manipulation
import pyodbc (SCRIPT SETUP 1) This lets Python talk to your SQL Server database
from datetime import datetime (SCRIPT SETUP 1) This is used for working with dates and times
Next you see DB_CONFIG.
• DB_CONFIG (SCRIPT SETUP 2) This holds all the details for connecting to your SQL Server. You need to update YOUR SQL SERVER NAME YOUR DATABASE NAME YOUR USERNAME and YOUR PASSWORD with your actual information.
2. Connecting to Your Database connect_to_db (Refer to FUNCTION 1 in code)
This function establishes the connection to your SQL Server database.
What it does It tries to open a link to your SQL Server using the details from DB_CONFIG
How it works The @st.cache_resource decorator is key here. It tells Streamlit to run this function only once and reuse the connection object across all user interactions. This prevents opening a new database connection every time the app updates
Safety check It prints messages to the console and displays errors in the Streamlit app if the connection fails
3. Fetching Overall Website Metrics fetch_overall_metrics (Refer to FUNCTION 2 in code)
This function retrieves high level summary statistics for your website
What it does It runs a SQL query to get the total number of unique users sessions and page views from your events table
How it works The @st.cache_data decorator ensures that this data is fetched from the database only when the function's inputs change. Since the query has no parameters it will typically run only once when the app starts
Output It returns a Pandas Series containing the total users sessions and page views
4. Fetching Daily Traffic Trends fetch_daily_trends (Refer to FUNCTION 3 in code)
This function retrieves data to show how your website's traffic changes over time
What it does It runs a SQL query to get daily counts of users sessions and page views
How it works Similar to fetch_overall_metrics the @st.cache_data decorator ensures efficient data retrieval. It also converts the event_date column to a datetime object which is important for plotting
Output It returns a Pandas DataFrame with daily trend data
5. Main Streamlit App Layout main (Refer to MAIN STREAMLIT APP LAYOUT in code)
This is the main function that defines the structure and content of your Streamlit dashboard.
What it does It sets up the page configuration. It displays the title and sections. It calls the data fetching functions. It uses Streamlit widgets to display KPIs and charts.
How it works st.set_page_config sets the browser tab title and the layout of the app (e.g. wide for more space). st.title and st.header are used for main titles and section headers. Database Connection It calls connect_to_db() to get the database connection. If connection fails it shows a warning. Displaying KPIs It calls fetch_overall_metrics() and uses st.columns(3) to arrange three st.metric() widgets side by side. Each st.metric() displays a key performance indicator. Daily Trends Chart It calls fetch_daily_trends() to get the time series data. It then uses st.slider() to create an interactive date range selector. This allows users to filter the data shown in the charts. st.line_chart() is used to plot the daily trends for users, sessions, and page views. st.dataframe() displays the raw filtered data. Informational Messages st.info() is used to display helpful messages to the user.
6. Running the Script The Main Execution Block (Refer to MAIN EXECUTION 1 to 6 in code)
This part of the script tells Python to run your Streamlit app when the file is executed.
• 'if __name__ == "__main__":' This standard Python construct ensures that 'main()' is called only when the script is run directly (not when imported as a module).
• Execution Flow It first attempts to connect to the database. If successful it fetches data and then calls the 'main()' function to build and display the dashboard. If the database connection fails it prints an error.
Overall Value of a Streamlit Dashboard
Building a Streamlit dashboard is the culmination of your web analytics project. It allows you to transform raw data and complex analyses into an intuitive and interactive tool. This dashboard can be used by anyone from business stakeholders to marketing teams to quickly understand website performance and make informed decisions. This demonstrates your ability to not only analyze data but also to effectively communicate insights through compelling visualizations and user friendly applications. This is a vital skill for any data professional.
Next Steps
You have successfully created a basic interactive web dashboard using Streamlit. This means you are now proficient in building user friendly data visualization applications. The next exciting phase will be to visualize KPIs using Python Matplotlib and Seaborn. This will allow you to create more customized and publication quality static charts for reports and presentations.
For now make sure you save this Python script in your E drive SankalanAnalytics dashboard folder. Name it: dashboard_app.py.
To run your dashboard:
1. Open your Command Prompt (CMD).
2. Navigate to your E:\SankalanAnalytics\dashboard\ folder:
cd E:\SankalanAnalytics\dashboard\
3. Run the Streamlit app:
streamlit run dashboard_app.py
4. Your browser should automatically open to the Streamlit dashboard.
Previous Topic==> Recommendation Pages or Contents Next Topics==> Visual KPI with Python
SQL All topics Wise Interview Questions
Employee Salary Management SQL FAQ!. C FAQ
Top 25 PL/SQL Interview Questions
Joins With Group by Having
Equi Join
Joins with Subqueries
Self Join
Outer Join