Product Analytics Prompts: Funnels, Cohorts & SQL Frameworks
Transform raw data into actionable insights with AI-powered prompts for funnel analysis, cohort tracking, and SQL scaffolding. This comprehensive guide provides frameworks, templates, and real examples to help product managers make data-driven decisions.
Jump to Section:
Funnel Analysis Framework
The 5-Step Funnel Analysis Process
Step 1: Define Your Funnel Stages
Funnel Analysis Metrics & KPIs
Conversion Rates
- • Stage-to-stage conversion
- • Overall funnel conversion
- • Time-to-conversion
- • Drop-off points
Velocity Metrics
- • Time spent in each stage
- • Bounce rates
- • Session duration
- • Feature adoption speed
Quality Metrics
- • User satisfaction scores
- • Support ticket volume
- • Feature usage depth
- • Retention correlation
Business Impact
- • Revenue per user
- • Customer lifetime value
- • Churn prevention
- • Upsell opportunities
Cohort Analysis & Retention Framework
Cohort Analysis Types for Product Managers
Acquisition Cohorts
Group users by when they first signed up or discovered your product. Analyze how different acquisition channels and time periods affect long-term retention.
Behavioral Cohorts
Group users by specific actions they take (first purchase, feature usage, engagement level). Understand which behaviors predict long-term success.
Feature Cohorts
Group users by which features they use first or most frequently. Identify which features drive retention and which may be causing churn.
Retention Analysis Framework
7-Day Retention Analysis:
SQL Scaffolding for Product Managers
Essential SQL Queries for PMs
1. User Funnel Analysis Query
-- Calculate conversion rates between funnel stages
WITH funnel_stages AS (
SELECT
user_id,
MIN(CASE WHEN event = 'signup' THEN created_at END) as signup_date,
MIN(CASE WHEN event = 'first_action' THEN created_at END) as first_action_date,
MIN(CASE WHEN event = 'feature_used' THEN created_at END) as feature_date
FROM user_events
GROUP BY user_id
)
SELECT
COUNT(*) as total_users,
COUNT(first_action_date) as activated_users,
COUNT(feature_date) as engaged_users,
ROUND(COUNT(first_action_date) * 100.0 / COUNT(*), 2) as activation_rate,
ROUND(COUNT(feature_date) * 100.0 / COUNT(first_action_date), 2) as engagement_rate
FROM funnel_stages;
2. Cohort Retention Query
-- Calculate retention rates by acquisition cohort
WITH user_cohorts AS (
SELECT
user_id,
DATE_TRUNC('week', MIN(created_at)) as cohort_week
FROM users
GROUP BY user_id
),
user_activity AS (
SELECT
user_id,
DATE_TRUNC('week', activity_date) as activity_week
FROM user_activity
)
SELECT
cohort_week,
COUNT(DISTINCT uc.user_id) as cohort_size,
COUNT(DISTINCT CASE WHEN ua.activity_week = cohort_week THEN uc.user_id END) as week_0_users,
COUNT(DISTINCT CASE WHEN ua.activity_week = cohort_week + INTERVAL '1 week' THEN uc.user_id END) as week_1_users,
ROUND(COUNT(DISTINCT CASE WHEN ua.activity_week = cohort_week + INTERVAL '1 week' THEN uc.user_id END) * 100.0 / COUNT(DISTINCT uc.user_id), 2) as week_1_retention
FROM user_cohorts uc
LEFT JOIN user_activity ua ON uc.user_id = ua.user_id
GROUP BY cohort_week
ORDER BY cohort_week;
AI Prompts for Product Analytics
🎯 Funnel Analysis Prompt
Use this prompt to analyze your product funnel:
Help me analyze my product funnel by:
1. Identifying the 5 key stages of my user journey
2. Calculating conversion rates between each stage
3. Identifying the biggest drop-off points
4. Suggesting 3-5 optimization strategies
5. Recommending key metrics to track
Context: [describe your product, current funnel stages, and goals]
📊 Cohort Analysis Prompt
Use this prompt to analyze user cohorts:
Help me analyze user cohorts by:
1. Defining 3-4 meaningful cohort types for my product
2. Identifying which cohorts have the highest retention
3. Analyzing what drives retention in successful cohorts
4. Suggesting cohort-based optimization strategies
5. Recommending A/B tests to improve retention
Context: [describe your product, user types, and retention goals]
🔍 SQL Query Generation Prompt
Use this prompt to generate SQL queries:
Help me write a SQL query to:
1. [describe the specific analysis you want to perform]
2. Include proper table joins and filtering
3. Handle edge cases and data quality issues
4. Optimize for performance with large datasets
5. Include comments explaining the logic
Database Schema: [describe your tables and relationships]
Analysis Goal: [describe what insights you want to uncover]
Related Resources for Product Managers
🔬 Research Synthesis Guide
Learn how to synthesize research data into actionable insights for product decisions.
📝 PRD Examples & Templates
Use analytics insights to create better Product Requirements Documents.
📝 Customer Interview Analyzer
Combine analytics data with customer interview insights for comprehensive user understanding.
🚀 Launch Checklist Prompts
Use analytics insights to create better launch checklists and monitoring strategies.
🔍 Competitive Analysis Framework
Combine analytics with competitive research for comprehensive market insights.
🤖 AI Tools & Prompts
Access our complete library of AI prompts for product management.
📊 Get weekly product analytics templates and AI prompts
Join 5,000+ Product Managers receiving:
- ✨ Weekly curated AI prompts that save hours
- 💡 Exclusive PM strategies and frameworks
- 🎯 Early access to new features and prompts
Join your peers from Google, Meta, Amazon and other top companies