Google Analytics Pipeline

What is being asked

Design an analytics product: a snippet on customers' sites sends events, and customers see dashboards of visitors, pages and conversions for any date range, plus a view of what is happening right now.

Requirements

Functional

• Collect events (page view, click, purchase) with site, visitor, time and attributes.
• Reports by day, country, device and page; funnels and unique visitors.
• A real-time view of the last 30 minutes.

Non-functional

• Very high write volume; the collection endpoint must never slow a customer's page.
• Historical queries over billions of rows in seconds.
• Counts can be approximate for uniques; totals should be exact.

Design

1. The snippet sends small beacons to an endpoint near the visitor, which answers at once and appends the event to a log.
2. A stream job keeps counters for the live view in a fast store with short retention.
3. A batch job cleans events, removes bots, groups them into sessions, adds country and device, and loads them into a columnar warehouse partitioned by date and site.
4. Common reports are served from pre-aggregated tables (per site, per day, per dimension); unusual questions query the detailed data.

Key decisions and trade-offs

• Pre-aggregate or query raw. Roll-ups are fast and cheap but only answer planned questions. Keeping raw events allows any question later at a higher query cost. Most systems keep both.
• Unique visitors cannot be added across days. Store a HyperLogLog sketch per day and merge sketches for a range.
• Stream plus batch gives fresh numbers quickly and corrected numbers later; the two must agree closely or customers lose trust.
• Late and duplicate events from mobile devices need an event id for de-duplication and re-processing of recent days.

Follow-up questions

• How do you tell a returning visitor without third-party cookies?
• How would you isolate one huge customer so they do not slow the others?
• A customer asks for their data to be deleted. What has to change?
• How do you back-fill a new report over two years of history?