Welcome to Phase 5.1 Understanding User Paths to Conversion
You have done a great job with Exploratory Data Analysis EDA You understand overall traffic trends user behavior by segments and individual page performance Now it is time to move into the Business Insights phase We will start by analyzing the user journey through your website We will specifically focus on funnels and identifying drop off points A funnel is a series of steps a user takes to complete a goal like signing up for a newsletter or finding a specific tutorial Understanding where users leave this path is like finding the broken rungs on a ladder It helps you fix problems and guide more users to their goals
This step is crucial for optimizing your websites conversion rates and improving user experience
Funnel analysis provides a clear visual of your users journey and pinpoints areas for improvement Here is why it is so valuable
Identify Bottlenecks See exactly where users are abandoning a process This helps you prioritize your optimization efforts
Improve Conversion Rates By fixing drop off points you can guide more users to complete desired actions This directly impacts your business goals
Understand User Intent Funnel analysis helps you understand how users navigate your site when they have a specific goal in mind
Optimize Design and Content If a step has a high drop off rate it might mean the page is confusing irrelevant or has technical issues
Measure Impact of Changes After making improvements you can use funnel analysis to see if the changes reduced drop off and improved conversion
Funnel analysis transforms your data into actionable insights for improving your websites effectiveness
For our analysis we will define a common user journey on a tutorial website. This will be our sample funnel.
Step 1 Homepage View A user lands on your websites main page
Step 2 Any Tutorial Page View The user navigates from the homepage to any general tutorial category page or a list of tutorials
Step 3 Specific Article View The user clicks from a category page into a specific tutorial article to read its content
Step 4 Contact or About Page View The user shows deeper interest by visiting a contact us or about us page This indicates a potential lead or deeper engagement
You can define any funnel steps that are important for your websites specific goals
We will write a Python script to fetch your event data from SQL Server. It will then analyze user paths through the defined funnel steps and calculate drop off rates.
Practical Python Code Example
Here is a basic example of the Python code you will write. This code will connect to your SQL Server database. It will fetch event data. It will then identify users who completed each step of the funnel and calculate conversion and drop off rates.
# SCRIPT SETUP 1: Import necessary libraries
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
def connect_to_db():
"""Establishes a connection to the SQL Server database."""
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.")
return conn
except pyodbc.Error as ex:
sqlstate = ex.args[0]
print(f"Database connection error: {sqlstate}")
print(ex)
return None
# FUNCTION 2: Fetch event data
def fetch_event_data(conn):
"""Fetches necessary event data from the SQL Server events table."""
query = """
SELECT
user_pseudo_id,
session_id,
event_timestamp,
event_name,
page_location
FROM
events
ORDER BY
user_pseudo_id, session_id, event_timestamp;
"""
try:
df = pd.read_sql(query, conn)
print(f"Fetched {len(df)} event records for funnel analysis.")
return df
except Exception as e:
print(f"Error fetching event data: {e}")
return None
# FUNCTION 3: Perform Funnel Analysis
def perform_funnel_analysis(df_events):
"""Analyzes user journey through predefined funnel steps."""
if df_events is None or df_events.empty:
print("No event data for funnel analysis.")
return None
# Ensure page_location is string and fill NaNs
df_events['page_location'] = df_events['page_location'].fillna('').astype(str)
# Define your funnel steps using conditions on page_location
# You can adjust these conditions to match your specific website URLs
funnel_steps = {
"Step 1: Homepage View": lambda df: df[
(df['event_name'] == 'page_view') &
(df['page_location'].str.contains('sankalandtech.com/$|sankalandtech.com/index.html', regex=True))
],
"Step 2: Any Tutorial Page View": lambda df: df[
(df['event_name'] == 'page_view') &
(df['page_location'].str.contains('/Tutorials/', regex=False))
],
"Step 3: Specific Article View": lambda df: df[
(df['event_name'] == 'page_view') &
(df['page_location'].str.contains('/Tutorials/', regex=False)) &
(~df['page_location'].str.contains('/index.html', regex=False)) # Exclude tutorial index pages
],
"Step 4: Contact or About Page View": lambda df: df[
(df['event_name'] == 'page_view') &
(df['page_location'].str.contains('/contact.html|/about.html', regex=True))
]
}
funnel_results = {}
previous_step_sessions = 0
# STEP 3.1: Process each funnel step
for step_name, condition_func in funnel_steps.items():
# Filter events for the current step
step_events = condition_func(df_events)
# Get unique sessions for this step
# To ensure sequential flow, we need to track sessions that completed the *previous* step
# This simplified version counts sessions that *contain* the step.
# For true sequential funnel, you'd need to sort events by timestamp within session
# and check if events occurred in order.
# For a simple funnel, we count unique sessions that had an event matching the step criteria
current_step_sessions = step_events['session_id'].nunique()
# STEP 3.2: Calculate drop-off from previous step
drop_off_rate = 0
if previous_step_sessions > 0:
drop_off_rate = ((previous_step_sessions - current_step_sessions) / previous_step_sessions) * 100
funnel_results[step_name] = {
"sessions": current_step_sessions,
"drop_off_from_previous_step_percent": round(drop_off_rate, 2)
}
previous_step_sessions = current_step_sessions
# STEP 3.3: Print Funnel Analysis Summary
print("\n--- Funnel Analysis Results ---")
print("Step Name | Sessions | Drop Off From Previous (%)")
print("-" * 70)
for step_name, data in funnel_results.items():
print(f"{step_name:<35} | {data['sessions']:<8} | {data['drop_off_from_previous_step_percent']:<25.2f}")
# STEP 3.4: Calculate overall conversion rate
if "Step 1: Homepage View" in funnel_results and "Step 4: Contact or About Page View" in funnel_results:
start_sessions = funnel_results["Step 1: Homepage View"]["sessions"]
end_sessions = funnel_results["Step 4: Contact or About Page View"]["sessions"]
overall_conversion_rate = (end_sessions / start_sessions) * 100 if start_sessions > 0 else 0
print(f"\nOverall Conversion Rate (Homepage to Contact/About): {overall_conversion_rate:.2f}%")
return funnel_results
# MAIN EXECUTION 1: This block runs when the script starts
if __name__ == "__main__":
# MAIN EXECUTION 2: Connect to the database
conn = connect_to_db()
if conn:
# MAIN EXECUTION 3: Fetch event data
events_df = fetch_event_data(conn)
# MAIN EXECUTION 4: Perform funnel analysis
if events_df is not None:
funnel_data = perform_funnel_analysis(events_df)
if funnel_data is not None:
print("\n--- Funnel Analysis Completed ---")
# You can now use 'funnel_data' for reporting or visualization
# MAIN EXECUTION 5: Close the database connection
conn.close()
print("Database connection closed.")
else:
print("Could not establish database connection. Exiting.")
This script connects to your SQL Server database to pull event data. It then uses Pandas to identify sessions that completed each step of a predefined funnel. The funnel steps are defined by conditions on 'page_location' and 'event_name'. You will need to adjust these conditions to perfectly match the URLs and event names relevant to your website's specific funnel. The bounce rate calculation here is a common simplified approach. It counts sessions with only one event.
A more strict definition might require checking if that single event was specifically a page view. This script calculates the number of sessions reaching each step and the drop off rate from the previous step. For a truly sequential funnel where events must happen in a specific order within a session more complex logic involving sorting and checking event sequences would be needed.
This example provides a good starting point for understanding overall funnel performance. Remember to fill in your actual SQL Server connection details in the DB CONFIG section. This includes your server name database name username and password.
This Python script helps you understand how users navigate through key paths on your website and where they might be leaving. It pulls data from your SQL Server database and analyzes their journey through predefined funnel steps. Let us break down each part of the code.
At the very top of the script you see some import lines. These bring in the necessary tools for our work. The first is import pandas as pd which brings in Pandas used to work with data in a table-like format and perform calculations. The next is import pyodbc which lets Python talk to your SQL Server database. Then from datetime import datetime which is used for working with dates and times. Next you see DB_CONFIG which holds all the details for connecting to your SQL Server. You need to update this with your actual server name, database name, username, and password.
The connect_to_db function is responsible for making the connection to your database. It tries to open a link to your SQL Server using the details from DB_CONFIG. It builds a connection string which helps pyodbc find and log in to your database. It then attempts the connection and prints messages to let you know if it was successful or if there was an error.
The fetch_event_data function gets the raw event data from your database that is needed for funnel analysis. It runs a SQL query to pull specific columns like user pseudo id, session id, event timestamp, event name, and page location from your events table. It orders the data by user, session, and timestamp. It uses pd.read_sql to run the query and put the results directly into a Pandas DataFrame, which is the starting point for analyzing user journeys. It also prints how many records were fetched and handles errors if any.
This is the core function where the funnel analysis happens. It takes the event data and determines how many sessions complete each step of your defined funnel. It identifies unique sessions that reach each step and calculates the drop off rate between steps.
Here is how it works:
page_location column is a string and handles empty values.This part runs when you start the Python file. It connects to the database, fetches event data, performs the funnel analysis, prints the results, and finally closes the database connection. Closing the connection is important to free up resources.
This Python script is a powerful tool for optimizing your website's conversion paths. By mapping the user journey and identifying drop off points, you get actionable insights to improve user experience and increase conversion rates. It highlights your skills in business analysis and web analytics.
Once you run this Python script, you will see a clear breakdown of your user funnel and where users are dropping off. This means you have successfully analyzed user journeys. The next phase will be to analyze new versus returning users to understand differences in their behavior. For now, save this script in your E drive SankalanAnalytics backend folder and name it something like analyze_funnel.py.
Previous Topic==> Traffic Drop Analysis Next Topics==> New vs Returning Users
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