Mathew K Analytics

Lesson 27 · Real-World Data Analytics

Web Traffic Analytics in Python: Sources, Bounce Rate & Campaigns | Data Analytics #27

Which traffic sources actually bring engaged visitors? We analyse real session-level web analytics data in pandas: join a star schema, compare sessions,…

What you'll learn

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

📓 Full notebook

Download .ipynb

Data Analytics 100, Video 27: Web Traffic Analytics and Attribution#

  • Video twenty-seven of the hundred-video real-world data analytics series.
  • Real session-level web analytics data, tying every visit back to a real traffic source, device, and location.
  • Let's get into it.

This lesson is for learners who want to see how web analytics questions are answered with plain pandas rather than a ready-made dashboard. The business question is attribution: which traffic sources bring visitors to the site, and which of them bring visitors who actually engage? Marketing teams use exactly this kind of analysis to decide where to spend their budget.

The data is session-level web analytics in a small star schema (a central fact table linked to descriptive lookup tables by keys). Website_Traffic_Data.csv holds 731 sessions with Session_Id, a Date_Key, Session_Duration(Seconds), Page_Views_Per_Session, Content_Segment and three keys. The keys join to Source_Lookup.csv (source type and campaign), Device_Lookup.csv (device type and browser) and Geo_Lookup.csv (region and city).

In this lesson you will:

  • join a fact table to several lookup tables with chained merge calls
  • compare volume against quality using session counts, duration, page views and bounce rate
  • build cross-tabs and a monthly trend, and save a source summary table

You should be comfortable with pandas groupby and basic merge; see earlier lessons in the series if not.

Part 1: Real Sessions, Real Sources#

Attribution work almost always starts with several tables, so you need to check each one loads correctly before joining. The code reads the main traffic file plus three lookup tables for source, device and geography, then shows all four shapes at once. The output shows 731 sessions with 7 columns, and three lookups of 100 rows each with 4 or 5 columns. The lookups being much smaller than the traffic table is what you expect: each describes a category once, and many sessions point to it.

import pandas as pd
import matplotlib.pyplot as plt
traffic = pd.read_csv('Website_Traffic_Data.csv')
source = pd.read_csv('Source_Lookup.csv')
device = pd.read_csv('Device_Lookup.csv')
geo = pd.read_csv('Geo_Lookup.csv')
traffic.shape, source.shape, device.shape, geo.shape
((731, 7), (100, 4), (100, 5), (100, 5))

Part 2: Real Data Preview#

Previewing rows helps you understand what each column holds and which columns are join keys. The code calls .head(3) on both traffic and source, but Jupyter only displays the last bare expression, so the output shows only the source lookup. You can see Source_Key, Source_Name, Source_Type (Organic, Paid, Referral) and Source_Campaign (Spring Sale, Summer Offer, Winter Discount). To see both previews from one step, wrap each in display().

traffic.head(3)
source.head(3)
Source_Key Source_Name Source_Type Source_Campaign
0 1 Source 1 Organic Spring Sale
1 2 Source 2 Paid Summer Offer
2 3 Source 3 Referral Winter Discount

Part 3: Real Star Schema Join#

Analysis is much easier once every session carries its descriptive labels. The code chains three .merge() calls, joining on Source_Key, Device_Key and Location_Key in turn. By default merge does an inner join, which keeps only rows with a match on both sides. The output (731, 18) is reassuring: all 731 sessions survived, and the columns grew from 7 to 18 as the lookup fields were added. Always compare row counts before and after a join, because unmatched keys silently drop rows in an inner join.

merged = traffic.merge(source, on='Source_Key').merge(device, on='Device_Key').merge(geo, on='Location_Key')
merged.shape
(731, 18)

Part 4: Real Sessions by Traffic Source Type#

Volume is the first attribution question: where do sessions come from? The code groups by Source_Type, counts Session_Id values in each group and sorts from largest to smallest. The output shows Social leading with 135 sessions, followed by Organic (129) and Paid (125), with Email lowest at 113. The spread is narrow, about 22 sessions between top and bottom, so no single channel dominates traffic. Volume alone does not say which channel is most valuable, which is why quality metrics follow.

sessions_by_source = merged.groupby('Source_Type')['Session_Id'].count().sort_values(ascending=False)
sessions_by_source
Source_Type
Social      135
Organic     129
Paid        125
Other       115
Referral    114
Email       113
Name: Session_Id, dtype: int64

Part 5: Visualizing Real Traffic by Source#

A bar chart lets non-technical colleagues see the channel mix at a glance. The code plots the sorted source counts with .plot(kind='bar'), rotates the labels by 30 degrees so they do not overlap, and saves the figure to sessions_by_source.png before closing it, so nothing displays here. When you open the image, notice how similar the bar heights are. A common tip: if differences are this small, starting the y-axis at zero (the default) keeps the chart honest and avoids exaggerating gaps.

plt.figure(figsize=(9, 5))
sessions_by_source.plot(kind='bar', color='darkslateblue')
plt.ylabel('Real Session Count')
plt.title('Real Sessions by Traffic Source')
plt.xticks(rotation=30)
plt.tight_layout()
plt.savefig('sessions_by_source.png', dpi=120)
plt.close()

Part 6: Real Session Duration by Source#

Session duration is a simple measure of engagement: longer visits usually mean people found something worth reading. The code groups by Source_Type, takes the mean of Session_Duration(Seconds) and rounds to one decimal. The output shows Organic visitors staying longest at 720.0 seconds on average (12 minutes), with Paid close behind at 713.3. Referral is lowest at 585.8 seconds. Note that Social, the highest-volume source, sits in the middle at 660.0 seconds, an early sign that volume and quality do not line up.

duration_by_source = merged.groupby('Source_Type')['Session_Duration(Seconds)'].mean().round(1).sort_values(ascending=False)
duration_by_source
Source_Type
Organic     720.0
Paid        713.3
Other       675.1
Social      660.0
Email       645.1
Referral    585.8
Name: Session_Duration(Seconds), dtype: float64

Part 7: Real Bounce Rate by Source#

A bounce is a session where the visitor sees one page and leaves, and bounce rate is the share of sessions that bounce. The code flags bounces with Page_Views_Per_Session == 1, which gives True or False, and the mean of that boolean per source is the bounce share, multiplied by 100. The output shows Referral highest at 12.3% and Paid at 12.0%, while Organic is lowest at 5.4%. A paid channel with a high bounce rate is worth investigating, because you are paying for visits that leave immediately.

merged['is_bounce'] = merged['Page_Views_Per_Session'] == 1
bounce_rate_by_source = merged.groupby('Source_Type')['is_bounce'].mean().round(3) * 100
bounce_rate_by_source.sort_values(ascending=False)
Source_Type
Referral    12.3
Paid        12.0
Email       10.6
Social       9.6
Other        7.8
Organic      5.4
Name: is_bounce, dtype: float64

Part 8: Real Page Views per Session by Source#

Page views per session complete the engagement picture alongside duration and bounce rate. The code takes the mean of Page_Views_Per_Session for each source and sorts it. The output again puts Organic on top at 3.69 pages per session and Paid next at 3.64, with Referral last at 3.12. The ranking closely matches the duration ranking, which gives you more confidence that Organic traffic genuinely engages better rather than this being a quirk of one metric.

pageviews_by_source = merged.groupby('Source_Type')['Page_Views_Per_Session'].mean().round(2).sort_values(ascending=False)
pageviews_by_source
Source_Type
Organic     3.69
Paid        3.64
Email       3.44
Social      3.43
Other       3.33
Referral    3.12
Name: Page_Views_Per_Session, dtype: float64

Part 9: Real Traffic by Device Type#

Device mix affects design decisions: a site used mainly on tablets needs a different layout from one used on desktops. The code groups by Device_Type and counts sessions. The output shows Tablet leading with 163 sessions, then Desktop (156) and Laptop (150), with Mobile lowest at 121. A category called Other with 141 sessions is fairly large, so in a real project you would ask what it contains before drawing conclusions.

sessions_by_device = merged.groupby('Device_Type')['Session_Id'].count().sort_values(ascending=False)
sessions_by_device
Device_Type
Tablet     163
Desktop    156
Laptop     150
Other      141
Mobile     121
Name: Session_Id, dtype: int64

Part 10: Real Bounce Rate by Device#

Bounce rate by device can reveal technical problems, such as a page that renders badly on one screen size. The code reuses the is_bounce column and takes its mean per Device_Type as a percentage. The output shows Desktop with the highest bounce rate at 11.5%, then Laptop at 10.0%, while Tablet and Other are lowest at 8.6% and 8.5%. The differences are small, a few percentage points, so they suggest rather than prove a device issue.

bounce_by_device = merged.groupby('Device_Type')['is_bounce'].mean().round(3) * 100
bounce_by_device.sort_values(ascending=False)
Device_Type
Desktop    11.5
Laptop     10.0
Mobile      9.1
Tablet      8.6
Other       8.5
Name: is_bounce, dtype: float64

Part 11: Real Geography, Sessions by Region#

Geography tells you where demand is concentrated, which matters for regional campaigns and delivery planning. The code groups by Location_Region and counts sessions. The output shows South with 264 sessions and North with 233, together about two-thirds of the 731 total, while East has only 87. That makes East the clear under-served region and a candidate for a targeted campaign, or a sign that the site has less relevance there.

sessions_by_region = merged.groupby('Location_Region')['Session_Id'].count().sort_values(ascending=False)
sessions_by_region
Location_Region
South    264
North    233
West     147
East      87
Name: Session_Id, dtype: int64

Part 12: Real Top Cities by Traffic#

Drilling from region to city shows which specific markets drive traffic. The code counts sessions per Location_City, sorts them and uses .head(10) to keep the top ten. The output shows Chennai on top with 111 sessions, followed by Hyderabad (89), Kolkata (87) and Jaipur (86), with Ahmedabad tenth at 33. Chennai alone accounts for about 15% of all sessions. City-level counts are useful, but with smaller numbers per city, small changes can shift the ranking.

sessions_by_city = merged.groupby('Location_City')['Session_Id'].count().sort_values(ascending=False)
sessions_by_city.head(10)
Location_City
Chennai      111
Hyderabad     89
Kolkata       87
Jaipur        86
Lucknow       76
Delhi         71
Mumbai        66
Bangalore     64
Pune          48
Ahmedabad     33
Name: Session_Id, dtype: int64

Part 13: Visualizing Real Regional Traffic#

Regional differences are larger than source differences, so a chart makes the point quickly. The code draws a bar chart of the regional counts, keeps the labels horizontal with plt.xticks(rotation=0) since region names are short, and saves it to sessions_by_region.png. The figure is closed, so nothing is displayed here. When you open the image, look for the gap between the South and North bars and the much shorter East bar.

plt.figure(figsize=(8, 5))
sessions_by_region.plot(kind='bar', color='seagreen')
plt.ylabel('Real Session Count')
plt.title('Real Sessions by Geographic Region')
plt.xticks(rotation=0)
plt.tight_layout()
plt.savefig('sessions_by_region.png', dpi=120)
plt.close()

Part 14: Real Content Segment Engagement#

Content segments describe what kind of visit each session was, so comparing them shows which content keeps people browsing. The code groups by Content_Segment and takes the mean of Page_Views_Per_Session. The output shows Gaming highest at 3.64 pages per session and Travel and Tourism at 3.54, with Personal lowest at 3.25. The range is narrow, so treat this as a mild preference rather than a strong difference.

pageviews_by_segment = merged.groupby('Content_Segment')['Page_Views_Per_Session'].mean().round(2).sort_values(ascending=False)
pageviews_by_segment
Content_Segment
Gaming                3.64
Travel and Tourism    3.54
Business              3.43
Education             3.40
Personal              3.25
Name: Page_Views_Per_Session, dtype: float64

Part 15: Real Source Type by Content Segment Cross-Tab#

A single groupby looks at one dimension at a time, but marketing questions often involve two, for example which channels bring which kind of visitor. pd.crosstab() counts rows for every combination of Source_Type and Content_Segment, producing a grid. The output shows Email and Paid each bring 35 Business sessions, the largest cells in the table, while Organic is strongest for Travel and Tourism with 34. Email brings only 15 Education sessions, the weakest pairing. These patterns suggest which content each channel should promote.

source_segment_crosstab = pd.crosstab(merged['Source_Type'], merged['Content_Segment'])
source_segment_crosstab
Content_Segment Business Education Gaming Personal Travel and Tourism
Source_Type
Email 35 15 22 19 22
Organic 23 23 23 26 34
Other 25 25 22 21 22
Paid 35 17 21 30 22
Referral 26 19 20 23 26
Social 32 24 27 23 29

Part 16: Real Monthly Traffic Trend#

Trends over time show whether traffic is growing, shrinking or seasonal. The code converts Date_Key to real dates with pd.to_datetime, then .dt.to_period('M') turns each date into a calendar month so sessions can be grouped by month. The output, showing the last 12 months of 2021, has counts of 28 to 31, which match the number of days in each month. That strongly suggests the data holds one session per day, so the monthly trend reflects calendar length rather than real changes in demand.

merged['Date'] = pd.to_datetime(merged['Date_Key'])
merged['Month'] = merged['Date'].dt.to_period('M')
monthly_sessions = merged.groupby('Month')['Session_Id'].count()
monthly_sessions.tail(12)
Month
2021-01    31
2021-02    28
2021-03    31
2021-04    30
2021-05    31
2021-06    30
2021-07    31
2021-08    31
2021-09    30
2021-10    31
2021-11    30
2021-12    31
Freq: M, Name: Session_Id, dtype: int64

Part 17: Visualizing the Real Monthly Trend#

Plotting the monthly series is the standard way to present traffic over time. The code draws a line chart with markers, rotates the month labels by 45 degrees so they fit, and saves it to monthly_traffic_trend.png. The figure is closed, so it does not display here. When you open it, expect a nearly flat line with small dips in shorter months such as February. That is a useful lesson in itself: always check whether a trend could be explained by the way the data was collected.

plt.figure(figsize=(11, 5))
monthly_sessions.plot(kind='line', marker='o', color='crimson')
plt.ylabel('Real Session Count')
plt.title('Real Monthly Website Traffic Trend')
plt.xticks(rotation=45)
plt.tight_layout()
plt.savefig('monthly_traffic_trend.png', dpi=120)
plt.close()

Part 18: Real Campaign Attribution#

Campaign attribution asks which marketing campaigns brought visitors in. The code groups by Source_Campaign and counts sessions. The output shows New Year Deal leading with 135 sessions, then Spring Sale (129) and Summer Offer (125). These counts exactly match Social, Organic and Paid in the source table, which suggests each source type maps to one campaign in this lookup. Spotting identical numbers like this is a good habit, because it tells you two columns may carry the same information.

sessions_by_campaign = merged.groupby('Source_Campaign')['Session_Id'].count().sort_values(ascending=False)
sessions_by_campaign
Source_Campaign
New Year Deal      135
Spring Sale        129
Summer Offer       125
Other              115
Winter Discount    114
Name: Session_Id, dtype: int64

Part 19: Real Campaign Engagement Quality#

Counting sessions per campaign only shows reach. .agg() with named aggregations lets you build several summary columns in one go: sessions=('Session_Id', 'count') reads as name the output column, then take this column and apply this function. The output shows Spring Sale with the longest visits at 720.00 seconds and most page views at 3.69, while Winter Discount is weakest on both (585.79 seconds and 3.12 pages). New Year Deal has the most sessions, 135, but middling engagement at 660.00 seconds.

campaign_quality = merged.groupby('Source_Campaign').agg(sessions=('Session_Id', 'count'), avg_duration=('Session_Duration(Seconds)', 'mean'), avg_pageviews=('Page_Views_Per_Session', 'mean')).round(2)
campaign_quality.sort_values('sessions', ascending=False)
sessions avg_duration avg_pageviews
Source_Campaign
New Year Deal 135 660.00 3.43
Spring Sale 129 720.00 3.69
Summer Offer 125 713.28 3.64
Other 115 675.13 3.33
Winter Discount 114 585.79 3.12

Part 20: Real Volume vs Quality Trade-off#

This step makes the central attribution finding explicit. .idxmax() returns the index label of the largest value, not the value itself. Applied to the duration and session-count series, it names the best channel on each measure. The output is ('Organic', 'Social'): Organic delivers the most engaged visitors, while Social delivers the most visitors. This trade-off between volume and quality is common, and it is why you should never judge a channel on traffic numbers alone.

best_engagement_source = duration_by_source.idxmax()
highest_volume_source = sessions_by_source.idxmax()
best_engagement_source, highest_volume_source
('Organic', 'Social')

Part 21: Real Device-Region Interaction#

Device use can vary by region, which matters if you plan region-specific campaigns. Another pd.crosstab() counts sessions for each combination of Device_Type and Location_Region. The output shows Tablet in the South is the single largest cell with 69 sessions, and Desktop in the North is next with 59. East is small across every device, from 14 to 21 sessions. Raw counts are affected by region size, so dividing each column by its total with normalize='columns' would give a fairer comparison.

device_region = pd.crosstab(merged['Device_Type'], merged['Location_Region'])
device_region
Location_Region East North South West
Device_Type
Desktop 21 59 47 29
Laptop 18 47 54 31
Mobile 14 46 38 23
Other 19 33 56 33
Tablet 15 48 69 31

Part 22: Real Browser Distribution#

Browser share helps a development team decide which browsers to test on first. value_counts() counts how often each value appears in Device_Browser, sorted from most to least common. The output shows Browse_ER_4 used in 226 sessions and Browse_ER_1 in only 132. The browser names are anonymised placeholders here, so the insight is about distribution rather than specific products. Together the top two browsers cover 433 of the 731 sessions.

browser_share = merged['Device_Browser'].value_counts()
browser_share
Device_Browser
Browse_ER_4    226
Browse_ER_3    207
Browse_ER_2    166
Browse_ER_1    132
Name: count, dtype: int64

Part 23: Real Long-Duration Sessions#

Your most engaged visitors are often the most valuable, so it helps to know where they come from. The code uses .quantile(0.9) to find the 90th percentile of session duration, then keeps sessions longer than that, which is roughly the top 10%. value_counts() on Source_Type shows Organic contributing the most long sessions with 9, then Paid and Email with 8 each, while Social contributes only 3. This reinforces the earlier finding that Social brings volume but fewer deeply engaged visits.

long_sessions = merged[merged['Session_Duration(Seconds)'] > merged['Session_Duration(Seconds)'].quantile(0.9)]
long_sessions['Source_Type'].value_counts()
Source_Type
Organic     9
Paid        8
Email       8
Other       5
Referral    5
Social      3
Name: count, dtype: int64

Part 24: Real Zero-Duration Sessions#

Sessions lasting zero seconds may be bots, tracking errors or visitors who left instantly, and they can distort averages. The code filters rows where Session_Duration(Seconds) == 0, divides by the total row count and rounds. The output shows 9.6% of sessions have zero duration. That is close to the bounce rates seen earlier, so it is worth checking how far the two groups overlap. A common mistake is to include these sessions in average duration without noting them.

zero_duration = merged[merged['Session_Duration(Seconds)'] == 0]
zero_duration_pct = round(len(zero_duration) / len(merged) * 100, 1)
zero_duration_pct
9.6

Part 25: Real Correlation, Duration and Page Views#

If two engagement metrics move together, you can often report one with confidence, or use both to cross-check. .corr() computes the Pearson correlation between duration and page views, a number from -1 to 1 where values near 1 mean a strong positive straight-line relationship. The output is 0.72, a strong positive relationship. Sessions with more page views tend to last longer, which matches what you would expect and supports using both metrics together in the source comparison.

duration_pageview_corr = merged['Session_Duration(Seconds)'].corr(merged['Page_Views_Per_Session'])
round(duration_pageview_corr, 3)
np.float64(0.72)

Part 26: Saving the Real Source Performance Table#

A tidy summary table is the main deliverable of an attribution analysis. The code builds a DataFrame from four Series (sessions, duration, bounce rate and page views); pandas lines them up by their Source_Type index, so the different sort orders do not matter. It then saves the table to source_attribution_summary.csv and reloads it with index_col=0 so the source names become the index again. The output True confirms the reloaded shape matches the original.

source_performance = pd.DataFrame({'sessions': sessions_by_source, 'avg_duration_sec': duration_by_source, 'bounce_rate_pct': bounce_rate_by_source, 'avg_pageviews': pageviews_by_source}).round(2)
source_performance.to_csv('source_attribution_summary.csv')
reloaded_summary = pd.read_csv('source_attribution_summary.csv', index_col=0)
reloaded_summary.shape == source_performance.shape
True

Part 27: Real Sanity Check, Session Counts Match#

Joins and groupings can silently gain or lose rows, so you should confirm totals still add up. This step uses a chained comparison: Python reads a == b == c as a == b and b == c. It checks that the summed source counts, the merged table length and the original traffic length are all equal. The output True confirms every one of the 731 sessions was counted exactly once from raw data to final summary.

sessions_by_source.sum() == len(merged) == len(traffic)
True

Part 28: Real Sanity Check, Rates in Range#

Any percentage outside 0 to 100 means something went wrong in a calculation. The code builds two boolean checks on bounce_rate_by_source, joins them with & (each side needs brackets), and all() returns True only if every value passes. The output True confirms all six source bounce rates are valid. Using and instead of & on a pandas Series is a common mistake and raises an error.

all((0 <= bounce_rate_by_source) & (bounce_rate_by_source <= 100))
True

Part 29: Real Attribution Recommendation#

An analysis should end in something a team can act on. The code uses .idxmax() on the bounce-rate series to name the source with the worst bounce rate. The output is 'Referral', the channel where visitors most often see one page and leave, at 12.3%. Referral also had the shortest sessions and fewest page views, so a sensible recommendation is to review which partner sites send this traffic and whether the landing pages match what those visitors expect.

worst_bounce_source = bounce_rate_by_source.idxmax()
worst_bounce_source
'Referral'

Part 30: Real Recap Print#

A one-line recap forces you to state the headline clearly. The f-string drops the stored results into a sentence, so the text stays in sync with the data. The output reads: across 731 sessions, Social drove the most volume, Organic delivered the strongest engagement and Referral showed the highest bounce rate. That sentence is close to what you would put at the top of a report for a marketing manager.

print(f'Across {len(merged)} real sessions, {highest_volume_source} drove the most volume while {best_engagement_source} delivered the strongest real engagement, with {worst_bounce_source} showing the highest real bounce rate.')
Across 731 real sessions, Social drove the most volume while Organic delivered the strongest real engagement, with Referral showing the highest real bounce rate.

Wrap-Up: What You Learned#

  • Joining a real fact table against real dimension lookup tables is the same star-schema pattern genuine analytics warehouses use every day.
  • Attribution is not just about raw session volume, comparing duration, page views, and bounce rate reveals real traffic quality.
  • The real traffic source with the most sessions was not necessarily the real source with the best engagement, a genuinely common real finding.
  • Cross-tabulating dimensions like device and region, or source and content segment, surfaces real interaction patterns a single groupby would miss.
  • Zero-duration and single-page sessions are a real honest signal worth tracking separately from overall real traffic volume.
  • Next video: real email marketing performance analysis, shifting from real website sessions to real campaign send data.

Practice on your own

  • Repeat the bounce-rate and average-duration analysis by Location_Region and check whether East, the smallest region, also has weaker engagement.
  • Rerun the device-by-region cross-tab with normalize='columns' so each region sums to 1, then compare which device dominates in each region.
  • Recalculate average session duration by Source_Type after excluding zero-duration sessions and see whether the ranking of channels changes.

The next video in the series moves to email marketing performance analysis, shifting from website sessions to campaign send data.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.