Mario's Blog: Turn Session Paths into a Clickstream

The challenge

Mario wants to know how visitors move between Home, Products, About, Contact, and Blog. Unpack the session paths into individual page visits, then find what makes Blog navigation stand out.

Start here

Open the SharePoint folder linked above. small_sample_data.csv contains 100 sessions for practice. trueData_clickstream_data_1757343446194.csv contains 100,000 sessions for the main analysis. MariosClickstreamData.pdf is a three-page generator configuration and preview, not a complete raw click log.

Inspect the two CSV headers and several multi-page sessions. The page_sequence field uses an arrow separator, while page_durations is a quoted comma-separated list. The two files have different session timestamps; do not presume the practice sample is the first 100 rows of the larger file.

Try it

1. Convert each session into ordered page visits without misaligning its durations.

2. Validate pages_visited and total_duration against the expanded values.

3. Calculate pageviews, single-page sessions, average session duration, and the most common page transition.

4. Explain the difference between a transition probability conditional on continuing and an exit probability.

What to build

Build sessions and page_visits tables, a five-by-five transition count matrix, a page summary, and answers that explain what each percentage is based on. Keep the practice and main populations separate.