From pm-copilot
Use this skill when the user asks to "write a SQL query", "help me query this data", "how do I get this metric from the database", "generate SQL for", "query for retention", "SQL to find churned users", "write a query for my analytics", or needs SQL to answer a specific product analytics question. This skill generates standard SQL for product analytics use cases — it assumes a typical event-based analytics schema.
npx claudepluginhub productfculty-aipm/pm-copilot-by-product-facultyThis skill uses the workspace's default tool permissions.
Dispatches parallel agents to independently tackle 2+ tasks like separate test failures or subsystems without shared state or dependencies.
Executes pre-written implementation plans: critically reviews, follows bite-sized steps exactly, runs verifications, tracks progress with checkpoints, uses git worktrees, stops on blockers.
Guides idea refinement into designs: explores context, asks questions one-by-one, proposes approaches, presents sections for approval, writes/review specs before coding.
You are generating SQL queries for product analytics use cases. The queries are designed to work with standard event-based analytics schemas (similar to Segment, Amplitude, Mixpanel raw exports, or most data warehouse setups).
Read memory/user-profile.md for analytics tool and any database or schema information the user has provided. Read context/company/analytics-baseline.md for metric definitions that should inform the query.
Ask (or infer from context):
events, tracking_events, user_events)user_id, event_name, timestamp, properties)If the user hasn't specified, use this standard schema and note the assumption:
-- Assumed schema
-- events(user_id, event_name, timestamp, properties JSONB)
-- users(user_id, created_at, plan, segment)
Generate the appropriate query for the requested use case:
User Retention Cohort (N-day retention):
-- D7 retention: % of users who performed an event 7 days after their first event
WITH first_events AS (
SELECT user_id, MIN(DATE(timestamp)) AS cohort_date
FROM events
WHERE event_name = 'app_opened' -- or signup event
GROUP BY user_id
),
day_7_events AS (
SELECT DISTINCT e.user_id
FROM events e
JOIN first_events f ON e.user_id = f.user_id
WHERE DATE(e.timestamp) = f.cohort_date + INTERVAL '7 days'
AND e.event_name = 'app_opened' -- or core action
)
SELECT
f.cohort_date,
COUNT(DISTINCT f.user_id) AS cohort_size,
COUNT(DISTINCT d.user_id) AS retained_users,
ROUND(100.0 * COUNT(DISTINCT d.user_id) / COUNT(DISTINCT f.user_id), 1) AS d7_retention_pct
FROM first_events f
LEFT JOIN day_7_events d ON f.user_id = d.user_id
GROUP BY f.cohort_date
ORDER BY f.cohort_date;
Activation Funnel:
-- Users completing each step of the activation flow
WITH users AS (SELECT DISTINCT user_id FROM events WHERE event_name = 'signed_up')
SELECT
'Step 1: Signed Up' AS step, COUNT(DISTINCT user_id) AS users FROM users
UNION ALL
SELECT 'Step 2: Connected Tool', COUNT(DISTINCT e.user_id)
FROM events e JOIN users u ON e.user_id = u.user_id WHERE e.event_name = 'connected_tool'
UNION ALL
SELECT 'Step 3: Completed Core Action', COUNT(DISTINCT e.user_id)
FROM events e JOIN users u ON e.user_id = u.user_id WHERE e.event_name = 'core_action_completed';
Weekly Active Users (Core Action):
SELECT
DATE_TRUNC('week', timestamp) AS week,
COUNT(DISTINCT user_id) AS weekly_active_users
FROM events
WHERE event_name = 'core_action_completed'
AND timestamp >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;
Churned Users (last 30 days):
WITH last_seen AS (
SELECT user_id, MAX(timestamp) AS last_active
FROM events
GROUP BY user_id
)
SELECT
user_id,
last_active,
CURRENT_TIMESTAMP - last_active AS days_since_active
FROM last_seen
WHERE last_active < CURRENT_TIMESTAMP - INTERVAL '30 days'
ORDER BY last_active;
Feature Adoption Rate:
SELECT
DATE_TRUNC('week', e.timestamp) AS week,
COUNT(DISTINCT e.user_id) AS feature_users,
COUNT(DISTINCT all_users.user_id) AS total_active_users,
ROUND(100.0 * COUNT(DISTINCT e.user_id) / COUNT(DISTINCT all_users.user_id), 1) AS adoption_pct
FROM events e
CROSS JOIN (
SELECT DISTINCT user_id FROM events
WHERE event_name = 'app_opened' AND timestamp >= CURRENT_DATE - INTERVAL '7 days'
) all_users
WHERE e.event_name = 'feature_x_used'
AND e.timestamp >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;
Take the specific metric or question the user wants to answer and:
Provide: