Sessionization and User Journey Reconstruction from Raw Web Events,\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n
SQL queries to transform raw pageview events into sessions, reconstruct user journeys, and calculate engagement metrics from event-level web analytics data.
Build SQL-based sessionization logic that groups raw events into meaningful sessions, reconstructs user paths, and calculates engagement metrics typically found in analytics platforms.
At a glance
Access
Free prompt
Open to copy without upgrading.
Prompt objective
Build SQL-based sessionization logic that groups raw events into meaningful sessions, reconstructs user paths, and calculates engagement metrics typically found in analytics platforms.
Real use case
A startup's analytics team exports raw ClickHouse event data and needs to build their own session-level metrics because their analytics tool doesn't support custom event properties. They have 2 million events per day.
Customize these fields first
Replace the placeholders with your own context before you run the prompt. That usually improves the first output more than adding more instructions later.
Prompt
Write SQL queries to sessionize and analyze user journeys from raw web events for [COMPANY NAME].\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nTable: \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`events\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\`\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- event_id, user_id (or anonymous_id), event_type, page_url, page_title, referrer, timestamp, device, browser, session_id (NULL — needs to be calculated)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Query 1 — Session Assignment:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Define session: 30-minute gap between events = new session\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Use LAG() to detect gaps > 30 minutes\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Assign session_id using cumulative SUM of session breaks\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Output: session_id, user_id, start_time, end_time, duration, page_count\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Query 2 — Session Metrics:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Average session duration\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Pages per session\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Bounce rate (single-page sessions)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Sessions by device, browser, hour of day\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Returning vs. new visitor sessions\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Query 3 — User Journey Paths:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Most common page sequences (first 5 pages of session)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Entry pages (most common first page)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Exit pages (most common last page)\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Internal search: what do users search for and where do they go after?\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Query 4 — Conversion Attribution:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- For users who converted, what was their most common path?\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Number of sessions before conversion\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Time from first visit to conversion\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Multi-touch: all pages visited before conversion\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n**Query 5 — Engagement Scoring:**\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Session engagement score based on: duration, pages, scroll depth (if available), interactions\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- User-level engagement: aggregate across all sessions\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n- Identify power users vs. casual visitors\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\n\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\nSQL dialect: [PostgreSQL/BigQuery/ClickHouse]. Optimize for large datasets (use partitioning).
Open directly in an AI — the text is pre-filled:
How to use this prompt
- 1Replace the key placeholders first: COMPANY NAME, PostgreSQL/BigQuery/ClickHouse.
- 2Replace any bracketed placeholders like [this] with your own context.
- 3Add extra background information when you want more tailored results.
- 4Combine multiple prompts in one conversation when you need a richer output.
- 5Save your best-performing prompts so they are easy to reuse later.
Next best step
Open the guide first, then branch only if you still need more.
A guide for technical builders choosing between prompts, coding workflows, and agent-based implementation.
If this prompt is close but not quite right, generate variants next. If the job is recurring, move into the course library after the guide.
Related prompts
View allSales analysis queries with CTEs and window functions
Writes advanced SQL queries using CTEs and window functions for sales performance analysis.
Best for
Create a set of reusable SQL queries that analyze sales by period, product, seller, and region using CTEs (Common Table Expressions) and window functions for rankings and comparisons.
Database Schema Design for Recurring Subscription Systems
Designs an optimized database schema for a subscription business with SaaS metrics.
Best for
Create a complete relational model for a recurring subscription system, including tables, indexes, constraints, and ready-to-use queries for calculating MRR, churn, and LTV.
Slow Query Optimization with EXPLAIN ANALYZE and Indexes
Diagnoses and optimizes slow SQL queries using execution plans, strategic indexes, and query rewriting.
Best for
Learn to identify SQL query performance bottlenecks using EXPLAIN ANALYZE, create efficient indexes, and rewrite queries to reduce response time.
Stored Procedures for Automatic Sales Commission Calculation
Creates stored procedures that automate commission calculations with complex tiered rules and bonuses.
Best for
Develop SQL stored procedures that calculate sales commissions considering tiered quotas, product mix bonuses, cancellation clawbacks, and team splits.
Explore other prompt categories
Move sideways into adjacent libraries when the current category is not the full answer.
Free browsing stays open. Premium prompts unlock the reusable workflow layer.
Use the guides and role paths to validate the job first. Upgrade when you want the full prompt text, editable premium prompts, and the surrounding course paths in one place.
Free access
- Browse guides, role paths, and category pages.
- Preview prompts before you decide to upgrade.
- Find the right starting point without friction.
Membership access
- Unlock premium prompts and the full copy text.
- See more workflow paths and course connections.
- Keep the reusable templates in one place.
