Welcome to Phase 4 1 Understanding Your Website Growth
You have cleaned your raw GA4 data and loaded it into your SQL Server database. You have also calculated important metrics like sessions and bounce rate. Now it is time to see what your data tells you. In this phase we will start Exploratory Data Analysis or EDA. We will look at website traffic trends over time. Understanding these trends is like checking the pulse of your website. It shows if your site is growing if it has busy times or if recent changes made a difference.
This step is very important for making smart choices about your website. It helps you see how your audience uses your content over time.
Understanding traffic trends gives you a clear view of how your website is doing. Here is why it is helpful.
Identify Growth or Decline See if your website is getting more visitors or fewer. This helps you act quickly when things change.
Spot Seasonality Many websites have busy times during the year or week. Knowing these patterns helps you plan your content and marketing.
Measure Impact of Changes Did a new marketing campaign or website update bring more visitors? Traffic trends help you check these results.
Predict Future Performance By looking at past trends you can better guess how your website will do in the future.
Support Business Decisions Traffic data gives solid proof to back business choices. This includes where to spend marketing money or what content to focus on.
Analyzing trends turns your data into a story. This story shows how your website is performing over time.
We will focus on these core metrics to understand your website traffic trends.
Total Users
The total number of unique people who visited your website over a period.
New Users
The number of people visiting your site for the very first time. This shows your reach.
Sessions
The total number of visits to your website. One user can have multiple sessions.
Page Views
The total number of pages viewed. This includes repeated views of the same page.
We will write a Python script to fetch your event data from SQL Server. It will then group this data by day or week. This will show you how your traffic metrics change over time.
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 and user data. It will then calculate daily traffic trends.
# 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 data from events and users tables
def fetch_analytics_data(conn):
"""Fetches combined event and user data from SQL Server."""
# We need events for sessions and page views
# We need users for new users (first_visit_timestamp)
query_events = """
SELECT
user_pseudo_id,
session_id,
event_timestamp,
event_name,
page_location
FROM
events
ORDER BY
event_timestamp;
"""
query_users = """
SELECT
user_pseudo_id,
first_visit_timestamp
FROM
users;
"""
try:
df_events = pd.read_sql(query_events, conn)
df_users = pd.read_sql(query_users, conn)
print(f"Fetched {len(df_events)} event records and {len(df_users)} user records.")
return df_events, df_users
except Exception as e:
print(f"Error fetching analytics data: {e}")
return None, None
# FUNCTION 3: Analyze Traffic Trends
def analyze_traffic_trends(df_events, df_users):
"""Calculates daily traffic trends for users, sessions, and page views."""
if df_events is None or df_events.empty:
print("No event data to analyze trends.")
return None
# STEP 3.1: Convert timestamps to readable datetime and extract date
# GA4 timestamps are usually in microseconds. Convert to seconds for datetime.
df_events['event_datetime'] = pd.to_datetime(df_events['event_timestamp'], unit='us')
df_events['event_date'] = df_events['event_datetime'].dt.date
# For users table, convert first_visit_timestamp
df_users['first_visit_datetime'] = pd.to_datetime(df_users['first_visit_timestamp'], unit='us')
df_users['first_visit_date'] = df_users['first_visit_datetime'].dt.date
# STEP 3.2: Calculate Daily Sessions
daily_sessions = df_events.groupby('event_date')['session_id'].nunique().reset_index(name='sessions')
# STEP 3.3: Calculate Daily Page Views
daily_page_views = df_events[df_events['event_name'] == 'page_view'].groupby('event_date').size().reset_index(name='page_views')
# STEP 3.4: Calculate Daily Total Users
daily_total_users = df_events.groupby('event_date')['user_pseudo_id'].nunique().reset_index(name='total_users')
# STEP 3.5: Calculate Daily New Users
daily_new_users = df_users.groupby('first_visit_date')['user_pseudo_id'].nunique().reset_index(name='new_users')
daily_new_users = daily_new_users.rename(columns={'first_visit_date': 'event_date'}) # Align column name for merge
# STEP 3.6: Merge all daily metrics into a single DataFrame
# Start with sessions, then merge others
traffic_trends_df = daily_sessions
traffic_trends_df = pd.merge(traffic_trends_df, daily_page_views, on='event_date', how='outer')
traffic_trends_df = pd.merge(traffic_trends_df, daily_total_users, on='event_date', how='outer')
traffic_trends_df = pd.merge(traffic_trends_df, daily_new_users, on='event_date', how='outer')
# Fill any missing values (e.g., if no new users on a specific day) with 0
traffic_trends_df = traffic_trends_df.fillna(0).sort_values('event_date').reset_index(drop=True)
print("\nDaily Traffic Trends:")
print(traffic_trends_df.head()) # Show first few rows of trends
return traffic_trends_df
# 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 and user data
events_df, users_df = fetch_analytics_data(conn)
# MAIN EXECUTION 4: Analyze traffic trends
if events_df is not None and users_df is not None:
traffic_trends = analyze_traffic_trends(events_df, users_df)
if traffic_trends is not None:
print("\n--- Traffic Trend Analysis Completed ---")
# You can now use 'traffic_trends' DataFrame for plotting or further analysis
# For example, to save to a new CSV:
# traffic_trends.to_csv('E:/SankalanAnalytics/data/processed/daily_traffic_trends.csv', index=False)
# 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 the necessary data. It then uses Pandas to process and group this data by date. The unit='us' in pd.to_datetime is important because GA4 timestamps are in microseconds. If your timestamps are in milliseconds or seconds you will need to change this. The session_id and user_pseudo_id are used with nunique to count unique sessions and users. This avoids counting the same session or user more than once on the same day. Remember to fill in your actual SQL Server details in the DB CONFIG section. This includes your server name, database name, username, and password.
This Python script helps you see how your website traffic changes over time. It pulls data from your SQL Server database and calculates daily trends for key metrics. Let’s break down each part of the code.
At the top of the script, you’ll see some import lines. These bring in the tools we need:
import pandas as pd: We use Pandas to work with data in table form and perform calculations
import pyodbc: This lets Python connect to your SQL Server database
from datetime import datetime: Used for working with dates and times.
Next is DB_CONFIG
, which holds your database connection details. You need to update it with your actual server name, database name, username, and password.
The connect_to_db function tries to connect to your database using the details in DB_CONFIG. It builds a connection string and attempts to log in. It also prints messages to tell you if the connection was successful or if there was an error.
The fetch_analytics_data function runs two SQL queries to get event data and user data (including first visit times). It uses pd.read_sql to load this data into Pandas DataFrames. It prints how many records were fetched and shows an error message if something goes wrong.
This is the main part where trends are calculated. The function analyze_traffic_trends does this by:
Converting timestamps into readable dates so data can be grouped by day
Counting daily sessions by unique session IDs
Counting daily page views filtered by page view events
Counting daily total users by unique user IDs
Counting daily new users by their first visit date
Merging all these daily metrics into one table, filling missing values with zero
It then prints the first few rows so you can quickly see the results.
The main block of the script runs when you start the Python file. It:
This script is a key part of your web analytics work. It turns raw database data into clear, useful traffic trends. Knowing these trends helps you make smarter choices about your website content and marketing. It shows your skill in pulling, cleaning, and analyzing data — a must-have for any data analyst.
After running this script, you’ll see daily traffic trends for your site. You’ve just analyzed an important part of how your website performs. Next, you’ll dive into understanding user behavior by device, location, and traffic source. This will give you even deeper insights into who your visitors are and how they use your site.
For now, save this Python script in your E:\SankalanAnalytics\backend folder with a name like analyze_traffic.py.
Previous Topic==> Session Bounce Rate! || Next Topics==> User Bhehavior Analysis
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