Enhancing Website Traffic Analysis with SQL: Comprehensive Insights through Advanced Querying

Understanding website traffic is pivotal for improving user engagement and optimizing content strategy. SQL (Structured Query Language) serves as a powerful tool for extracting detailed insights from website traffic data. Here’s how advanced SQL queries can be used to analyze session duration, page views, bounce rates, and more.

1. Analyzing Session Duration and Page Views

To gain insights into how different device types and traffic sources affect user engagement, we can query session duration and page views.

Session Duration and Page Views by Device Type

sql code: 
SELECT
 DL.Device_Type,
ROUND(SUM(Session_Duration_Seconds) * 1.0 / 3600, 2) AS Total_Duration_Sessions, ROUND(AVG(Session_Duration_Seconds) * 1.0 / 3600, 2) AS Avg_Duration_Session, SUM(Page_Views_per_session) AS Total_Page_Views 
FROM website_traffic_analytics.dbo.Website_Traffic_Data WD 
INNER JOIN website_traffic_analytics.dbo.Device_Lookup DL
ON WD.Device_Key = DL.Device_Key 
GROUP BY Device_Type;        

Session Duration and Page Views by Source Type

sql code:
SELECT 
SL.Source_Type, 
ROUND(SUM(Session_Duration_Seconds) * 1.0 / 3600, 2) AS Total_Duration_Sessions, ROUND(AVG(Session_Duration_Seconds) * 1.0 / 3600, 2) AS Avg_Duration_Session, SUM(Page_Views_per_session) AS Total_Page_Views 
FROM website_traffic_analytics.dbo.Website_Traffic_Data WD 
INNER JOIN website_traffic_analytics.dbo.Source_Lookup SL 
ON WD.Source_Key = SL.Source_Key 
GROUP BY Source_Type;        

2. Calculating Bounce Rates

Bounce rates are essential for understanding user engagement. Here’s how to calculate overall and segmented bounce rates.

Overall Bounce Rate

sql code: 
WITH Bounced_Sessions_CTE AS ( 
SELECT 
COUNT(Page_Views_Per_Session) AS Bounced_Sessions 
FROM Website_Traffic_Data 
WHERE Page_Views_Per_Session < 2 
)
, Total_Sessions_CTE AS ( 
SELECT 
COUNT(Session_id) AS Total_Number_Sessions 
FROM Website_Traffic_Data 
) 
SELECT 
Bounced_Sessions, 
Total_Number_Sessions, 
Bounced_Sessions * 100.00 / Total_Number_Sessions AS Bounce_Rate 
FROM Bounced_Sessions_CTE, Total_Sessions_CTE;        

Bounce Rate by Device Type

sql code: 
WITH Total_Sessions_Per_Device AS (
SELECT 
D.Device_Type, 
COUNT(W.Session_Id) AS Total_Device_Sessions 
FROM Website_Traffic_Data W 
INNER JOIN Device_Lookup D 
ON W.Device_Key = D.Device_Key 
GROUP BY D.Device_Type 
)
, Bounced_Sessions_Device AS ( 
SELECT 
D.Device_Type, 
COUNT(W.Page_Views_Per_Session) AS Total_Device_Bounced_Sessions 
FROM Website_Traffic_Data W 
INNER JOIN Device_Lookup D 
ON W.Device_Key = D.Device_Key 
WHERE W.Page_Views_Per_Session < 2 
GROUP BY D.Device_Type 
) 
SELECT 
T.Device_Type, 
T.Total_Device_Sessions, 
B.Total_Device_Bounced_Sessions, 
B.Total_Device_Bounced_Sessions * 100.00 / T.Total_Device_Sessions AS Bounced_rate FROM Total_Sessions_Per_Device AS T 
INNER JOIN Bounced_Sessions_Device AS B 
ON T.Device_Type = B.Device_Type;        

Bounce Rate by Device Browser

sql code: 
With Total_Sessions_Per_Device as (
Select D.Device_Browser, Count(W.Session_Id) As Total_Device_Sessions from Website_Traffic_Data W
inner join Device_Lookup D
on W.Device_Key = D.Device_Key
Group by D.Device_Browser
),
Bounced_Sessions_Device as (
Select D.Device_Browser, Count(W.Page_Views_Per_Session) As Total_Device_Bounced_Sessions 
from Website_Traffic_Data W
inner join Device_Lookup D
on W.Device_Key = D.Device_Key
where w.Page_Views_Per_Session<2
Group by D.Device_Browser
)
Select T.Device_Browser, T.Total_Device_Sessions, B.Total_Device_Bounced_Sessions
, B.Total_Device_Bounced_Sessions * 100.00 /T.Total_Device_Sessions as Bounced_rate from 
Total_Sessions_Per_Device as T
inner join Bounced_Sessions_Device as B
on T.Device_Browser = B.Device_Browser;        

Bounce Rate by Content Segment

sql code: 
With Total_Sessions_Per_Device as (
Select D.Content_Segment, Count(W.Session_Id) As Total_Device_Sessions from Website_Traffic_Data W
inner join Device_Lookup D
on W.Device_Key = D.Device_Key
Group by D.Content_Segment
),
Bounced_Sessions_Device as (
Select D.Content_Segment, Count(W.Page_Views_Per_Session) As Total_Device_Bounced_Sessions 
from Website_Traffic_Data W
inner join Device_Lookup D
on W.Device_Key = D.Device_Key
where w.Page_Views_Per_Session<2
Group by D.Content_Segment
)
Select T.Content_Segment, T.Total_Device_Sessions, B.Total_Device_Bounced_Sessions
, B.Total_Device_Bounced_Sessions * 100.00 /T.Total_Device_Sessions as Bounced_rate from 
Total_Sessions_Per_Device as T
inner join Bounced_Sessions_Device as B
on T.Content_Segment = B.Content_Segment;        

Bounce Rate by Traffic Source

sql code: With Total_Sessions_Per_Device as (
Select S.Source_Type, Count(W.Session_Id) As Total_Device_Sessions from Website_Traffic_Data W
inner join Source_Lookup S
on W.Source_key = S.Source_Key
Group by S.Source_Type
),
Bounced_Sessions_Device as (
Select S.Source_Type, Count(W.Page_Views_Per_Session) As Total_Device_Bounced_Sessions 
from Website_Traffic_Data W
inner join Source_Lookup S
on W.Source_key = S.Source_Key
where w.Page_Views_Per_Session<2
Group by S.Source_Type
)
Select T.Source_Type, T.Total_Device_Sessions, B.Total_Device_Bounced_Sessions
, B.Total_Device_Bounced_Sessions * 100.00 /T.Total_Device_Sessions as Bounced_rate from 
Total_Sessions_Per_Device as T
inner join Bounced_Sessions_Device as B
on T.Source_Type = B.Source_Type;        

3. Tracking Trends and Patterns

Tracking trends helps to understand changes in user behavior over time. Here’s how to analyze session duration trends and bounce rates.

Total Duration in Hours - Trend for Device Types

sql code: 
Select D.Device_Type, 
Sum(Session_Duration_Seconds) * 1.0/3600 as Total_Duration_Sessions_Hrs, 
Year(Date_key) as Year , 
'Q'+ Cast(DATEPART(Quarter, Date_Key) as varchar(2)) as Quarter_of_Year
from website_traffic_analytics.dbo.website_Traffic_Data WS
inner join Device_Lookup D
on WS.Device_Key = D.Device_Key
Group by D.Device_Type, Year(Date_key), 'Q'+ Cast(DATEPART(Quarter, Date_Key) as varchar(2));        

Total Duration in Hours - Trend for Source Types

sql code: 
SELECT 
S.Source_Type, 
SUM(Session_Duration_Seconds) * 1.0 / 3600 AS Total_Duration_Sessions_Hrs, YEAR(Date_key) AS Year, 
'Q' + CAST(DATEPART(Quarter, Date_Key) AS VARCHAR(2)) AS Quarter_of_Year 
FROM website_traffic_analytics.dbo.website_Traffic_Data WS 
INNER JOIN Source_Lookup S 
ON WS.Source_Key = S.Source_Key 
GROUP BY S.Source_Type, YEAR(Date_key), 
'Q' + CAST(DATEPART(Quarter, Date_Key) AS VARCHAR(2));        

Average Session Duration Hours - Overall Trend

sql code: 
SELECT 
YEAR(Date_key) AS Year, 
'Q' + CAST(DATEPART(Quarter, Date_Key) AS VARCHAR(2)) AS Quarter, AVG(Session_Duration_Seconds) * 1.0 / 3600 AS Avg_Session_Duration 
FROM Website_Traffic_Data
GROUP BY YEAR(Date_key), 
'Q' + CAST(DATEPART(Quarter, Date_Key) AS VARCHAR(2)) 
ORDER BY YEAR(Date_key);        

Bounce Rate - Monthly Trend

sql code:
with Bounced_Sessions_CTE as (
Select 
DATENAME(Month, Date_Key) as Month_Name, 
Datepart(M, Date_Key) as Month_Num,
count(Page_Views_Per_Session)  as Bounced_Sessions
from Website_Traffic_Data
where Page_Views_Per_Session < 2
group by 
DATENAME(Month, Date_Key) , 
Datepart(M, Date_Key)
),
Total_Sessions_CTE as (
Select 
DATENAME(Month, Date_Key) as Month_Name, 
Datepart(M, Date_Key) as Month_Num,
Count(Session_id) as Total_Number_Sessions from Website_Traffic_Data
group by 
DATENAME(Month, Date_Key) , 
Datepart(M, Date_Key)
)
Select 
BS.Month_Name,
sum(BS.Bounced_Sessions) * 100.00 /  sum(TS.Total_Number_Sessions) as Bounce_Rate
from
Bounced_Sessions_CTE BS
inner join 
Total_Sessions_CTE TS
on BS.Month_Name = TS.Month_Name
Group by BS.Month_Name, BS.Month_Num
Order by BS.Month_Num;        

Bounce Rate - Day Trends

sql code:
with Bounced_Sessions_CTE as (
Select 
DATENAME(WEEKDAY, Date_Key) as Week_Name, 
Datepart(WEEKDAY, Date_Key) as Day_Num,
count(Page_Views_Per_Session)  as Bounced_Sessions
from Website_Traffic_Data
where Page_Views_Per_Session < 2
group by 
DATENAME(WEEKDAY, Date_Key) , 
Datepart(WEEKDAY, Date_Key)
),
Total_Sessions_CTE as (
Select 
DATENAME(WEEKDAY, Date_Key) as Week_Name, 
Datepart(WEEKDAY, Date_Key) as Day_Num,
Count(Session_id) as Total_Number_Sessions from Website_Traffic_Data
group by 
DATENAME(WEEKDAY, Date_Key), 
Datepart(WEEKDAY, Date_Key)
)
Select 
BS.Week_Name,
sum(BS.Bounced_Sessions) * 100.00 /  sum(TS.Total_Number_Sessions) as Bounce_Rate
from
Bounced_Sessions_CTE BS
inner join 
Total_Sessions_CTE TS
on BS.Week_Name = TS.Week_Name
Group by BS.Week_Name, BS.Day_Num
Order by BS.Day_Num;        

Conclusion

Advanced SQL queries provide a comprehensive approach to analyzing website traffic. By examining session duration, page views, bounce rates, and tracking trends, you can derive actionable insights to enhance user engagement and optimize your website. Mastering these SQL techniques and some Date functions, CTEs and Joins will empower you to make data-driven decisions and drive meaningful improvements.