Design a Clickstream / Event Analytics Pipeline

The operational database knows an order was placed. It has no idea the customer viewed twelve products first, abandoned their cart twice, searched "return policy" before buying, and rage-tapped a button that didn't respond. That behavior lives in the clickstream: every view, tap, scroll, and add-to-cart, fired from the app as it happens. It is how a product team learns not what was bought, but why - where users drop out of a funnel, how long a session lasts, which screen quietly kills conversion. And it arrives as a firehose that behaves nothing like a well-mannered database.

PhoenixWorld's product team wants two things: a funnel - view to add-to-cart to checkout to purchase, with the drop-off at each step - and a live dashboard of active users and top products to watch during a flash sale. Today there is nothing usable. App engineers fire events at a homegrown endpoint that appends JSON to a log file nobody reads. Sana's job is to turn that firehose into funnels and sessions the analysts can trust, without the numbers being quietly wrong.

The hard parts are not the ones a beginner expects. Volume is real, but the traps are subtler: the contract with app engineers who ship a new app version that renames an event, phones that go offline on a flight and dump three hours of events at once with old timestamps, bots inflating every count, duplicate events from client retries, and the architectural fork that decides the whole shape - do you run one streaming path, or a streaming path plus a batch path that corrects it? This chapter is about all of that.

Step 1 - Understand the Problem and Establish Design Scope

"Build product analytics from our events" hides a collector, a contract, a stream processor, and a serving store. The clarifying questions decide how big each has to be.

Candidate: What questions do the analysts need answered?

Interviewer: Two shapes. A conversion funnel - what fraction of sessions go view to cart to checkout to purchase, and where they drop - and a live operations dashboard: active users right now, top products, events per minute, watched during a sale.

Candidate: How fresh does each need to be?

Interviewer: The live dashboard should be seconds to a minute or two behind. The funnel analysis can be a few minutes behind; it's for decisions, not firefighting.

Candidate: What are the sources and the volume?

Interviewer: A web SDK and a mobile SDK emit events. Normal days are around 500 million events; a big sale peaks near 2 billion a day, and it's bursty - a flash sale spikes hard.

Candidate: Who defines the events, and can that change under us?

Interviewer: App engineers emit them. They add events and fields constantly, and occasionally rename or restructure one when they ship a new app version. That's the reality; the pipeline has to live with it.

Candidate: How correct does this have to be? Finance-grade, or is approximate acceptable?

Interviewer: Not finance-grade. An active-user count that's approximately right in real time is fine. But funnels drive roadmap decisions, so they shouldn't be grossly wrong - no double-counting a step, no dropping a real conversion.

Candidate: Do events arrive late or out of order?

Interviewer: Constantly, especially mobile. A phone offline on a plane buffers events and sends them hours later, stamped with when they actually happened.

Candidate: I'll assume bots and duplicate events from client retries are in scope to filter.

Interviewer: They are, and they're where naive dashboards go wrong.

That pins it down.

Functional requirements

• Ingest web and mobile events through a collector that validates them against a shared schema.
• Group each user's events into sessions by inactivity gap.
• Compute funnels: ordered-step conversion and drop-off across a session.
• Serve a near-real-time dashboard (active users, top products, event rate) and analytical tables for funnel and session analysis.
• Deduplicate retried events and filter bots before they reach the numbers.