Exploring Ecommerce User Behaviour

import pandas as pd
import numpy as np

# Build the CartFlow session dataset
df = pd.DataFrame({
    "session_id":     ["S001","S002","S003","S004","S005","S006",
                       "S007","S008","S009","S010","S011","S012"],
    "device":         ["Desktop","Mobile","Desktop","Mobile","Tablet","Desktop",
                       "Mobile","Desktop","Tablet","Mobile","Desktop","Mobile"],
    "traffic_source": ["Organic","Social","Paid","Organic","Email","Organic",
                       "Paid","Email","Social","Social","Paid","Organic"],
    "pages_viewed":   [8, 3, 6, 4, 7, 9, 2, 11, 6, 2, 7, 5],
    "session_mins":   [12.4, 2.1, 9.8, 3.5, 11.2, 14.6, 1.8, 16.3,
                       8.9, 1.5, 10.1, 4.2],
    "funnel_stage":   ["Purchase","Product View","Purchase","Add to Cart",
                       "Purchase","Purchase","Session","Purchase",
                       "Checkout","Session","Purchase","Add to Cart"],
    "order_value":    [142, None, 89, None, 198, 115, None, 224,
                       None, None, 167, None]
})

# Inspect
print("Shape:", df.shape)
print("Missing order_value:", df["order_value"].isnull().sum(),
      "(expected — only purchases have a value)")

# Overall conversion rate
df["converted"] = (df["funnel_stage"] == "Purchase").astype(int)
conv_rate = df["converted"].mean()
print(f"\nOverall conversion rate: {conv_rate:.1%}")

# Funnel stage distribution — how many sessions reached each stage?
stage_order = ["Session","Product View","Add to Cart","Checkout","Purchase"]
stage_counts = df["funnel_stage"].value_counts().reindex(stage_order).fillna(0)
stage_counts = stage_counts.astype(int)

print("\nSessions by deepest funnel stage reached:")
for stage, count in stage_counts.items():
    pct = count / len(df) * 100
    print(f"  {stage:<15} {count:>2} sessions  ({pct:.0f}%)")

# Group by device — conversion rate, session quality, and order value
device_analysis = df.groupby("device").agg(
    sessions      = ("session_id",   "count"),
    conv_rate     = ("converted",    "mean"),
    avg_pages     = ("pages_viewed", "mean"),
    avg_duration  = ("session_mins", "mean"),
    avg_order_val = ("order_value",  "mean")  # NaN for non-buyers ignored by default
).round(2).reset_index()

device_analysis["conv_rate_pct"] = (
    device_analysis["conv_rate"] * 100
).round(1)

device_analysis = device_analysis.sort_values(
    "conv_rate", ascending=False
)

print("Device performance summary:")
print(device_analysis[["device","sessions","conv_rate_pct",
                        "avg_pages","avg_duration",
                        "avg_order_val"]].to_string(index=False))

# Gap between best and worst converting device
best  = device_analysis.iloc[0]
worst = device_analysis.iloc[-1]
gap   = best["conv_rate_pct"] - worst["conv_rate_pct"]
print(f"\nConversion gap ({best['device']} vs {worst['device']}): {gap:.1f} pp")

# Group by traffic source
source_analysis = df.groupby("traffic_source").agg(
    sessions      = ("session_id",   "count"),
    conversions   = ("converted",    "sum"),
    conv_rate     = ("converted",    "mean"),
    avg_pages     = ("pages_viewed", "mean"),
    avg_order_val = ("order_value",  "mean")
).round(2).reset_index()

source_analysis["conv_rate_pct"] = (
    source_analysis["conv_rate"] * 100
).round(0)

# Revenue per session — total revenue contribution per channel
# (conversions * avg_order_val) / total sessions = revenue per session
source_analysis["rev_per_session"] = (
    source_analysis["conversions"] * source_analysis["avg_order_val"]
    / source_analysis["sessions"]
).round(2)

source_analysis = source_analysis.sort_values(
    "conv_rate", ascending=False
)

print("Traffic source performance:")
print(source_analysis[["traffic_source","sessions","conv_rate_pct",
                        "avg_order_val","rev_per_session"]].to_string(index=False))

# Best source by conversion
best_conv = source_analysis.iloc[0]["traffic_source"]
best_aov  = source_analysis.loc[
    source_analysis["avg_order_val"].idxmax(), "traffic_source"
]
print(f"\nBest conversion source: {best_conv}")
print(f"Best AOV source:        {best_aov}")

# Session quality: converting vs non-converting
quality = df.groupby("converted")[
    ["pages_viewed","session_mins"]
].mean().round(2)
quality.index = quality.index.map({1:"Converting", 0:"Non-converting"})

print("Session quality by conversion outcome:")
print(quality.to_string())

# Gap between groups
pages_gap = quality.loc["Converting","pages_viewed"] - \
            quality.loc["Non-converting","pages_viewed"]
mins_gap  = quality.loc["Converting","session_mins"] - \
            quality.loc["Non-converting","session_mins"]
print(f"\nConverting sessions view {pages_gap:.1f} more pages")
print(f"Converting sessions last {mins_gap:.1f} minutes longer")

# Cross-tabulate device vs traffic source — count of sessions per combination
cross = pd.crosstab(df["device"], df["traffic_source"])
print("\nSessions by device × traffic source:")
print(cross.to_string())

# Which device-source combo has the highest AOV among buyers?
buyers = df[df["converted"] == 1].copy()
combo_aov = buyers.groupby(["device","traffic_source"])["order_value"].mean().round(2)
print("\nAOV by device × source (buyers only):")
print(combo_aov.sort_values(ascending=False).to_string())