jlim04/usda-rd-analytics

🌾 USDA Rural Development — Digital Analytics Dashboard

An interactive analytics dashboard analyzing 3.97 million users and 6.43 million sessions on the USDA Rural Development website (rd.usda.gov). Built for MGMT 389 at Purdue University.

Dashboard Sections

How the Data Was Processed

Step 1 — Raw Data

The original dataset was a GA4 export from rd.usda.gov containing 100,000 rows × 51 columns. Each row represented a unique combination of page + day + device type + country. The 51 columns were grouped into four device segments (desktop, mobile, tablet, smart TV) plus a totals section, each containing:

The three columns used for clustering were pulled from the totals segment: total_users (column index 49), bounce_rate (index 46), and avg_session_duration (index 45).

Step 2 — Aggregation

The 100K rows were aggregated down to 20 page-level summaries — one row per top page — by grouping on the page path column (index 4) and computing:

• —Total active users — summed across all days, devices, and countries
• —Average bounce rate — averaged across all rows for that page
• —Average session duration — averaged across all rows for that page

The top 20 pages by total active users were selected for clustering. This covers the highest-traffic content on the site and represents the majority of all user activity. All pages below the top 20 were excluded to keep the feature matrix meaningful — with K-Means, including hundreds of near-zero-traffic pages would distort centroid positions.

Step 3 — Normalization

All three features were min-max normalized to a 0–1 scale using the formula:

x_normalized = (x - x_min) / (x_max - x_min)

This was necessary because active users ranged from ~60K to ~760K while bounce rate ranged from 0.045 to 0.369 — without normalization, active users would dominate the Euclidean distance calculation and the algorithm would cluster almost entirely on traffic volume, ignoring bounce rate and session duration entirely.

Step 4 — K-Means Clustering

Library: sklearn.cluster.KMeans Parameters: random_state=42 (for reproducibility), n_init=10, max_iter=300 Input: 20 × 3 matrix of normalized features [users_norm, bounce_norm, duration_norm] K tested: 2 through 8

Optimal k was selected using two independent methods:

• —Elbow Method — plots inertia (within-cluster sum of squares) vs k. The steepest drop occurred at k=3, with a 45% SSE reduction from k=2 → k=3 vs. only 34% from k=3 → k=4, indicating diminishing returns beyond 3 clusters
• —Silhouette Score — measures how similar each point is to its own cluster vs. other clusters (range −1 to +1). Score peaked at 0.487 at k=3, the highest value across all tested k, confirming maximum inter-cluster separation with minimum intra-cluster variance

Step 5 — Unit of Analysis

An important methodological note: clustering was performed at the page level, not the individual user session level. Each of the 20 data points represents a page, not a person. This is a content-based segmentation approach — the assumption is that pages attracting similar behavioral patterns serve similar user types. A true user-level clustering would require session-level data for all 6.43M sessions, which was not available in this export format.

Step 6 — Cluster Results

The algorithm produced 3 clusters:

Traffic share is calculated as each cluster's total users divided by the combined total users across all 20 pages — not as a share of the full 3.97M site-wide users.

Important Methodological Note

The app does not load or process the raw CSV at runtime. The 100K-row dataset was analyzed offline using Python. The key metrics — page-level averages, K-Means cluster assignments, elbow scores, and silhouette scores — were pre-computed and embedded directly into app.py as Python data structures.

This means:

• —All site-wide metrics (total users, sessions, bounce rate, device breakdown, monthly trends) were extracted directly from the real CSV and are accurate
• —All page-level metrics (top 20 pages, their bounce rates, session durations, active users) were extracted directly from the real CSV and are accurate
• —The K-Means cluster assignments shown in the dashboard are pre-computed approximations based on the real page-level features, rather than live sklearn output at runtime

To verify the K-Means inputs, see the KMEANS_FEATURES_RAW DataFrame in app.py — it contains all 20 pages with their raw and normalized feature values exactly as fed into the algorithm.

Key Findings

• —Mobile-first audience: 53.9% of users are on mobile, yet session duration is 19% shorter than desktop — a critical optimization gap
• —Housing programs dominate: SFHGLP alone drives 760K active users (19% of all traffic) with the lowest bounce rate of any major program page
• —Seasonal drop-off: Traffic peaks Jan–Mar and falls ~41% by October, reflecting seasonal Rural Development program demand cycles
• —Most underserved cluster: Business & Grants users have the longest sessions (165s avg) but the highest bounce — high intent with inadequate digital support
• —Low return rate: Only 19.6% of users return — major retention opportunity through an application status portal

Tech Stack

• —[Gradio](https://gradio.app/) — UI framework
• —[Plotly](https://plotly.com/python/) — Interactive charts
• —[Pandas](https://pandas.pydata.org/) — Data processing
• —Python 3.13

Data Source

GA4 analytics export from rd.usda.gov (USDA Rural Development only). Dataset: MGMT_389_Dataset — 100,000 rows × 51 columns, device-segmented daily page-level data covering a 12-month period.

Course: MGMT 389 — Purdue University