This project leverages SQL and Amplitude Analytics to analyze feature adoption and user engagement after a product launch. SQL is used to process and structure raw event data, while Amplitude provides real-time insights into user interactions, retention, and conversion trends.
✅ SQL Server – Data extraction, transformation, and aggregation
✅ Amplitude Analytics – User event tracking and visualization
✅ GitHub – Project documentation & version control
SQL is essential for handling large-scale event data before uploading it to Amplitude. Key uses include:
📊 Data Cleaning & Preprocessing – Extracting relevant user event logs
📈 Trend Analysis – Calculating key feature adoption metrics
📌 Exporting Data – Preparing structured CSVs for Amplitude integration
📌 How many users find my feature every day?
Insight: Track Daily Active Users (DAU)—the number of unique users interacting with the feature each day.
SELECT CAST(event_time AS DATE) AS event_date, COUNT(DISTINCT user_id) AS daily_active_users FROM user_events WHERE event_type = 'feature_used' GROUP BY CAST(event_time AS DATE) ORDER BY event_date DESC;
📌 How often do users get value from your feature?
Insight: Measures how frequently users return to use the feature.
SELECT user_id, COUNT(event_id) AS usage_count FROM user_events WHERE event_type = 'feature_used' GROUP BY user_id ORDER BY usage_count DESC;
📌 Which platforms do users experience my feature on?
Insight: Shows feature adoption across different platforms (e.g., Web, iOS, Android).
SELECT platform, COUNT(DISTINCT user_id) AS user_count FROM user_events WHERE event_type = 'feature_used' GROUP BY platform ORDER BY user_count DESC;
📌 What percentage of users find your feature for the first time each week?
Insight: Tracks new user adoption trends on a weekly basis.
WITH first_time_users AS ( SELECT user_id, MIN(event_time) AS first_use_date FROM user_events WHERE event_type = 'feature_used' GROUP BY user_id ) SELECT DATEPART(WEEK, first_use_date) AS week_number, COUNT(user_id) AS new_users FROM first_time_users GROUP BY DATEPART(WEEK, first_use_date) ORDER BY week_number DESC;
📌 After users discover your feature, how many get value from it?
Insight: Measures conversion from feature discovery to feature usage.
WITH viewed_users AS ( SELECT DISTINCT user_id FROM user_events WHERE event_type = 'feature_viewed' ), used_users AS ( SELECT DISTINCT user_id FROM user_events WHERE event_type = 'feature_used' ) SELECT COUNT(used_users.user_id) * 100.0 / COUNT(viewed_users.user_id) AS conversion_rate FROM viewed_users LEFT JOIN used_users ON viewed_users.user_id = used_users.user_id;
📌 How long does it take users to find value in your feature?
Insight: Calculates the time between feature discovery (viewed) and feature adoption (used).
WITH time_to_use AS ( SELECT user_id, MIN(CASE WHEN event_type = 'feature_viewed' THEN event_time END) AS first_viewed, MIN(CASE WHEN event_type = 'feature_used' THEN event_time END) AS first_used FROM user_events GROUP BY user_id ) SELECT AVG(DATEDIFF(MINUTE, first_viewed, first_used)) AS avg_time_to_value FROM time_to_use WHERE first_viewed IS NOT NULL AND first_used IS NOT NULL;
📌 How many users return after getting value from my feature?
Insight: Measures retention rate by tracking users who return after their first successful feature use.
WITH first_usage AS ( SELECT user_id, MIN(event_time) AS first_used_date FROM user_events WHERE event_type = 'feature_used' GROUP BY user_id ) SELECT DATEDIFF(DAY, first_usage.first_used_date, ue.event_time) AS return_days, COUNT(DISTINCT ue.user_id) AS returning_users FROM first_usage JOIN user_events ue ON first_usage.user_id = ue.user_id AND ue.event_type = 'feature_used' WHERE DATEDIFF(DAY, first_usage.first_used_date, ue.event_time) > 0 GROUP BY DATEDIFF(DAY, first_usage.first_used_date, ue.event_time) ORDER BY return_days ASC;
📌 How many times does a user explore my feature on average?
Insight: Measures the average number of feature interactions per user.
SELECT COUNT(event_id) * 1.0 / COUNT(DISTINCT user_id) AS avg_feature_usage FROM user_events WHERE event_type = 'feature_used';
Amplitude is a product analytics platform that helps track user interactions, visualize feature adoption, and measure retention trends. With SQL-powered insights, we can integrate Amplitude to gain real-time, actionable insights into user behavior.
✅ Event Segmentation: Track DAU, user engagement, and platform adoption.
✅ Retention & Cohort Analysis: Measure repeat users and long-term feature adoption.
✅ Funnel Analysis: Optimize the user journey from feature discovery to retention.
✅ Breakdown & Segmentation: Understand user behavior based on device, platform, or demographics.