Loading…
This lesson covers A/B testing and experimental design for making data-driven decisions and optimizing outcomes.
Control Group (A)
Variant Group (B)
1. Simple A/B Test
Control: Current website design
Variant: New website design
Metric: Conversion rate
2. A/B/n Test
Control: Current design
Variant A: Design change 1
Variant B: Design change 2
Variant C: Design change 3
3. Multivariate Test
Control: Current headline and button
Variant 1: New headline, current button
Variant 2: Current headline, new button
Variant 3: New headline and new button
Test Configuration and Assignment
-- Create A/B test configuration and user assignment
WITH test_configuration AS (
SELECT
'homepage_redesign_2024' as test_id,
'Homepage Design Test' as test_name,
'2024-01-01' as start_date,
'2024-01-31' as end_date,
0.5 as traffic_split,
'conversion_rate' as primary_metric,
'page_views, time_on_page, bounce_rate' as secondary_metrics
),
user_assignment AS (
SELECT
user_id,
test_id,
-- Random assignment using hash function
CASE
WHEN MOD(ABS(FROM_UNIXTIME(UNIX_TIMESTAMP(user_id, 'yyyyMMdd')), 100) < 50 THEN 'control'
ELSE 'variant'
END as test_group,
assignment_timestamp,
-- Ensure consistent assignment
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY assignment_timestamp) as assignment_rank
FROM user_sessions
WHERE session_date BETWEEN '2024-01-01' AND '2024-01-31'
),
test_groups AS (
SELECT
user_id,
test_id,
test_group,
assignment_timestamp
FROM user_assignment
WHERE assignment_rank = 1 -- First assignment only
)
SELECT
tc.test_id,
tc.test_name,
tc.start_date,
tc.end_date,
tc.traffic_split,
tc.primary_metric,
COUNT(CASE WHEN tg.test_group = 'control' THEN 1 END) as control_users,
COUNT(CASE WHEN tg.test_group = 'variant' THEN 1 END) as variant_users,
ROUND(COUNT(CASE WHEN tg.test_group = 'control' THEN 1 END) * 100.0 / COUNT(*), 2) as control_percentage,
ROUND(COUNT(CASE WHEN tg.test_group = 'variant' THEN 1 END) * 100.0 / COUNT(*), 2) as variant_percentage
FROM test_configuration tc
LEFT JOIN test_groups tg ON tc.test_id = tg.test_id
GROUP BY tc.test_id, tc.test_name, tc.start_date, tc.end_date, tc.traffic_split, tc.primary_metric;
Test Implementation Tracking
-- Track test implementation and user behavior
WITH test_exposures AS (
SELECT
user_id,
test_group,
session_id,
page_url,
timestamp as exposure_time,
-- Track which version was shown
CASE
WHEN page_url LIKE '%variant=true%' THEN 'variant_implemented'
WHEN page_url LIKE '%variant=false%' THEN 'control_implemented'
ELSE 'implementation_error'
END as implementation_status
FROM user_sessions
WHERE test_id = 'homepage_redesign_2024'
AND session_date BETWEEN '2024-01-01' AND '2024-01-31'
),
conversion_events AS (
SELECT
user_id,
test_group,
event_type,
event_timestamp,
-- Time to conversion
TIMESTAMPDIFF(MINUTE, exposure_time, event_timestamp) as minutes_to_conversion,
-- Conversion value
CASE
WHEN event_type = 'purchase' THEN order_value
WHEN event_type = 'signup' THEN 25.0 -- Estimated lifetime value
ELSE 0
END as conversion_value
FROM test_exposures te
JOIN conversion_log cl ON te.user_id = cl.user_id
AND te.session_id = cl.session_id
WHERE cl.event_timestamp BETWEEN te.exposure_time
AND DATE_ADD(te.exposure_time, INTERVAL 7 DAY) -- 7-day attribution window
)
SELECT
test_group,
COUNT(DISTINCT user_id) as unique_users,
COUNT(DISTINCT session_id) as total_sessions,
COUNT(CASE WHEN implementation_status = 'control_implemented' THEN 1 END) as control_implementations,
COUNT(CASE WHEN implementation_status = 'variant_implemented' THEN 1 END) as variant_implementations,
COUNT(CASE WHEN implementation_status = 'implementation_error' THEN 1 END) as implementation_errors,
ROUND(COUNT(CASE WHEN implementation_status = 'implementation_error' THEN 1 END) * 100.0 / COUNT(*), 2) as error_rate,
-- Conversion tracking
COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) as purchase_conversions,
COUNT(CASE WHEN event_type = 'signup' THEN 1 END) as signup_conversions,
SUM(conversion_value) as total_conversion_value,
AVG(minutes_to_conversion) as avg_time_to_conversion
FROM test_exposures te
LEFT JOIN conversion_events ce ON te.user_id = ce.user_id
GROUP BY test_group
ORDER BY test_group;
Why Randomization Matters
Randomization Methods
1. Simple Random Assignment
-- Simple random assignment using hash
SELECT
user_id,
CASE
WHEN MOD(ABS(CRC32(user_id)), 100) < 50 THEN 'control'
ELSE 'variant'
END as test_group
FROM users;
2. Stratified Random Assignment
-- Stratified random assignment by user segment
WITH user_segments AS (
SELECT
user_id,
customer_segment,
-- Random within each segment
ROW_NUMBER() OVER (PARTITION BY customer_segment ORDER BY RAND()) as segment_rank,
COUNT(*) OVER (PARTITION BY customer_segment) as segment_size
FROM users
WHERE customer_segment IS NOT NULL
),
stratified_assignment AS (
SELECT
user_id,
customer_segment,
segment_size,
CASE
WHEN segment_rank <= segment_size * 0.5 THEN 'control'
ELSE 'variant'
END as test_group
FROM user_segments
)
SELECT
customer_segment,
test_group,
COUNT(*) as users_in_group,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY customer_segment), 2) as segment_percentage
FROM stratified_assignment
GROUP BY customer_segment, test_group
ORDER BY customer_segment, test_group;
3. Block Randomization
-- Block randomization for balanced assignment over time
WITH time_blocks AS (
SELECT
user_id,
session_timestamp,
-- Create time blocks (e.g., hourly)
DATE_TRUNC('hour', session_timestamp) as time_block,
ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('hour', session_timestamp) ORDER BY RAND()) as block_rank
FROM user_sessions
WHERE session_date BETWEEN '2024-01-01' AND '2024-01-31'
),
block_assignment AS (
SELECT
user_id,
time_block,
CASE
WHEN block_rank % 2 = 0 THEN 'control'
ELSE 'variant'
END as test_group
FROM time_blocks
)
SELECT
time_block,
test_group,
COUNT(*) as assignments,
-- Check balance within blocks
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY time_block), 2) as block_percentage
FROM block_assignment
GROUP BY time_block, test_group
ORDER BY time_block, test_group;
Key Statistical Concepts
1. Null and Alternative Hypotheses
H0 (Null): No difference between control and variant
H1 (Alternative): Significant difference exists
2. P-value Interpretation
3. Confidence Intervals
SQL for Statistical Analysis
-- Calculate statistical significance for A/B test results
WITH test_results AS (
SELECT
test_group,
COUNT(DISTINCT user_id) as total_users,
COUNT(CASE WHEN converted = 1 THEN 1 END) as conversions,
-- Conversion rates
COUNT(CASE WHEN converted = 1 THEN 1 END) * 1.0 / COUNT(DISTINCT user_id) as conversion_rate,
-- Standard error calculation
SQRT((conversion_rate * (1 - conversion_rate)) / COUNT(DISTINCT user_id)) as standard_error
FROM test_participants
WHERE test_id = 'homepage_redesign_2024'
GROUP BY test_group
),
control_variant AS (
SELECT
MAX(CASE WHEN test_group = 'control' THEN total_users END) as control_users,
MAX(CASE WHEN test_group = 'variant' THEN total_users END) as variant_users,
MAX(CASE WHEN test_group = 'control' THEN conversions END) as control_conversions,
MAX(CASE WHEN test_group = 'variant' THEN conversions END) as variant_conversions,
MAX(CASE WHEN test_group = 'control' THEN conversion_rate END) as control_rate,
MAX(CASE WHEN test_group = 'variant' THEN conversion_rate END) as variant_rate,
MAX(CASE WHEN test_group = 'control' THEN standard_error END) as control_se,
MAX(CASE WHEN test_group = 'variant' THEN standard_error END) as variant_se
FROM test_results
),
statistical_calculations AS (
SELECT
control_users,
variant_users,
control_conversions,
variant_conversions,
control_rate,
variant_rate,
-- Absolute difference
variant_rate - control_rate as absolute_difference,
-- Relative difference (lift)
(variant_rate - control_rate) / control_rate * 100 as relative_lift,
-- Pooled standard error
SQRT(POWER(control_se, 2) + POWER(variant_se, 2)) as pooled_se,
-- Z-score
(variant_rate - control_rate) / SQRT(POWER(control_se, 2) + POWER(variant_se, 2)) as z_score,
-- P-value (two-tailed)
2 * (1 - NORMSDIST(ABS((variant_rate - control_rate) / SQRT(POWER(control_se, 2) + POWER(variant_se, 2))))) as p_value,
-- Confidence interval
(variant_rate - control_rate) - 1.96 * SQRT(POWER(control_se, 2) + POWER(variant_se, 2)) as ci_lower,
(variant_rate - control_rate) + 1.96 * SQRT(POWER(control_se, 2) + POWER(variant_se, 2)) as ci_upper
FROM control_variant
)
SELECT
'Control' as group_type,
control_users as users,
control_conversions as conversions,
ROUND(control_rate * 100, 2) as conversion_rate_pct
UNION ALL
SELECT
'Variant' as group_type,
variant_users as users,
variant_conversions as conversions,
ROUND(variant_rate * 100, 2) as conversion_rate_pct
UNION ALL
SELECT
'Results' as group_type,
NULL as users,
NULL as conversions,
NULL as conversion_rate_pct
UNION ALL
SELECT
'Absolute Difference' as group_type,
NULL as users,
NULL as conversions,
ROUND(absolute_difference * 100, 2) as conversion_rate_pct
UNION ALL
SELECT
'Relative Lift' as group_type,
NULL as users,
NULL as conversions,
relative_lift as conversion_rate_pct
UNION ALL
SELECT
'P-value' as group_type,
NULL as users,
NULL as conversions,
ROUND(p_value, 4) as conversion_rate_pct
UNION ALL
SELECT
'CI Lower' as group_type,
NULL as users,
NULL as conversions,
ROUND(ci_lower * 100, 2) as conversion_rate_pct
UNION ALL
SELECT
'CI Upper' as group_type,
NULL as users,
NULL as conversions,
ROUND(ci_upper * 100, 2) as conversion_rate_pct;
Sample Size Calculation
-- Calculate required sample size for A/B test
WITH baseline_metrics AS (
SELECT
0.05 as baseline_conversion_rate, -- 5% baseline
0.20 as minimum_detectable_effect, -- 20% relative lift
0.80 as statistical_power, -- 80% power
0.05 as significance_level -- 5% significance
),
sample_size_calculation AS (
SELECT
baseline_conversion_rate,
minimum_detectable_effect,
statistical_power,
significance_level,
-- Z-scores for power and significance
1.96 as z_alpha, -- For 95% confidence
0.84 as z_beta, -- For 80% power
-- Effect size
baseline_conversion_rate * minimum_detectable_effect as absolute_effect,
-- Sample size formula
POWER(z_alpha + z_beta, 2) *
(baseline_conversion_rate * (1 - baseline_conversion_rate) +
(baseline_conversion_rate + absolute_effect) * (1 - baseline_conversion_rate - absolute_effect)) /
POWER(absolute_effect, 2) as sample_size_per_group
FROM baseline_metrics
)
SELECT
baseline_conversion_rate,
minimum_detectable_effect,
ROUND(sample_size_per_group) as required_sample_size_per_group,
ROUND(sample_size_per_group * 2) as total_required_sample_size,
-- Daily requirements
ROUND(sample_size_per_group / 30) as daily_users_per_group,
ROUND(sample_size_per_group * 2 / 30) as total_daily_users,
-- Test duration estimate
CEIL(sample_size_per_group * 2 / 1000) as estimated_days_1000_daily_users,
-- Confidence intervals
baseline_conversion_rate * (1 - minimum_detectable_effect) as worst_case_rate,
baseline_conversion_rate * (1 + minimum_detectable_effect) as best_case_rate
FROM sample_size_calculation;
1. Email Campaign Testing
-- Email subject line A/B test
WITH email_test_setup AS (
SELECT
campaign_id,
'subject_line_test' as test_name,
'2024-01-15' as send_date,
-- Test groups
'Control: Summer Sale Starts Now!' as control_subject,
'Variant: 🔥 Summer Sale - 50% Off!' as variant_subject
),
email_performance AS (
SELECT
campaign_id,
test_group,
COUNT(DISTINCT recipient_id) as emails_sent,
COUNT(CASE WHEN opened = 1 THEN 1 END) as emails_opened,
COUNT(CASE WHEN clicked = 1 THEN 1 END) as emails_clicked,
COUNT(CASE WHEN converted = 1 THEN 1 END) as conversions,
-- Calculate rates
COUNT(CASE WHEN opened = 1 THEN 1 END) * 100.0 / COUNT(DISTINCT recipient_id) as open_rate,
COUNT(CASE WHEN clicked = 1 THEN 1 END) * 100.0 / COUNT(DISTINCT recipient_id) as click_rate,
COUNT(CASE WHEN converted = 1 THEN 1 END) * 100.0 / COUNT(DISTINCT recipient_id) as conversion_rate,
-- Revenue metrics
SUM(CASE WHEN converted = 1 THEN order_value END) as total_revenue,
AVG(CASE WHEN converted = 1 THEN order_value END) as avg_order_value
FROM email_campaign_results
WHERE campaign_id = 'summer_sale_2024'
GROUP BY campaign_id, test_group
),
statistical_analysis AS (
SELECT
test_group,
emails_sent,
open_rate,
click_rate,
conversion_rate,
total_revenue,
-- Statistical significance for open rates
(open_rate - LAG(open_rate) OVER (ORDER BY test_group)) /
SQRT(POWER(open_rate * (100 - open_rate) / emails_sent, 2) +
POWER(LAG(open_rate) OVER (ORDER BY test_group) * (100 - LAG(open_rate) OVER (ORDER BY test_group)) /
LAG(emails_sent) OVER (ORDER BY test_group), 2)) as z_score_open,
-- Statistical significance for conversion rates
(conversion_rate - LAG(conversion_rate) OVER (ORDER BY test_group)) /
SQRT(POWER(conversion_rate * (100 - conversion_rate) / emails_sent, 2) +
POWER(LAG(conversion_rate) OVER (ORDER BY test_group) * (100 - LAG(conversion_rate) OVER (ORDER BY test_group)) /
LAG(emails_sent) OVER (ORDER BY test_group), 2)) as z_score_conversion
FROM email_performance
)
SELECT
test_group,
emails_sent,
ROUND(open_rate, 2) as open_rate_pct,
ROUND(click_rate, 2) as click_rate_pct,
ROUND(conversion_rate, 2) as conversion_rate_pct,
ROUND(total_revenue, 2) as total_revenue,
-- Statistical significance
CASE
WHEN ABS(z_score_open) > 1.96 THEN 'Significant'
ELSE 'Not Significant'
END as open_rate_significance,
CASE
WHEN ABS(z_score_conversion) > 1.96 THEN 'Significant'
ELSE 'Not Significant'
END as conversion_significance,
-- Recommendations
CASE
WHEN conversion_rate > LAG(conversion_rate) OVER (ORDER BY test_group)
AND ABS(z_score_conversion) > 1.96 THEN 'Winner - Implement variant'
WHEN conversion_rate < LAG(conversion_rate) OVER (ORDER BY test_group)
AND ABS(z_score_conversion) > 1.96 THEN 'Winner - Keep control'
ELSE 'Inconclusive - Run longer test'
END as recommendation
FROM statistical_analysis
ORDER BY test_group;
2. Ad Creative Testing
-- Ad creative performance A/B test
WITH ad_test_results AS (
SELECT
ad_group_id,
creative_variant,
impressions,
clicks,
conversions,
cost,
revenue,
-- Calculate metrics
clicks * 100.0 / impressions as ctr,
conversions * 100.0 / clicks as conversion_rate,
cost / impressions * 1000 as cpm,
cost / clicks as cpc,
cost / conversions as cpa,
revenue / cost as roas
FROM ad_performance
WHERE test_id = 'q1_creative_test'
AND date BETWEEN '2024-01-01' AND '2024-01-31'
),
creative_comparison AS (
SELECT
creative_variant,
SUM(impressions) as total_impressions,
SUM(clicks) as total_clicks,
SUM(conversions) as total_conversions,
SUM(cost) as total_cost,
SUM(revenue) as total_revenue,
AVG(ctr) as avg_ctr,
AVG(conversion_rate) as avg_conversion_rate,
AVG(cpa) as avg_cpa,
AVG(roas) as avg_roas,
-- Performance ranking
ROW_NUMBER() OVER (ORDER BY AVG(roas) DESC) as roas_rank,
ROW_NUMBER() OVER (ORDER BY AVG(cpa) ASC) as cpa_rank
FROM ad_test_results
GROUP BY creative_variant
)
SELECT
creative_variant,
total_impressions,
total_clicks,
ROUND(avg_ctr, 2) as avg_ctr_pct,
ROUND(avg_conversion_rate, 2) as avg_conversion_rate_pct,
ROUND(avg_cpa, 2) as avg_cpa,
ROUND(avg_roas, 2) as avg_roas,
roas_rank,
cpa_rank,
-- Overall performance score
(roas_rank + cpa_rank) / 2.0 as performance_score,
-- Recommendation
CASE
WHEN roas_rank = 1 AND cpa_rank = 1 THEN 'Clear Winner - Scale this creative'
WHEN roas_rank <= 2 OR cpa_rank <= 2 THEN 'Strong Performer - Consider scaling'
WHEN roas_rank = 3 AND cpa_rank = 3 THEN 'Average - Optimize or replace'
ELSE 'Poor Performer - Discontinue'
END as recommendation
FROM creative_comparison
ORDER BY performance_score;
1. Feature Adoption Testing
-- Feature adoption A/B test
WITH feature_test_users AS (
SELECT
user_id,
test_group,
feature_enabled,
signup_date,
last_active_date,
-- Feature usage metrics
feature_clicks,
feature_sessions,
feature_time_spent,
-- Overall engagement
total_sessions,
total_time_spent,
retention_days
FROM feature_test_results
WHERE test_id = 'new_dashboard_feature'
AND signup_date >= '2024-01-01'
),
feature_adoption_metrics AS (
SELECT
test_group,
COUNT(DISTINCT user_id) as total_users,
COUNT(CASE WHEN feature_clicks > 0 THEN 1 END) as users_adopted_feature,
COUNT(CASE WHEN feature_sessions > 5 THEN 1 END) as power_users,
-- Adoption rates
COUNT(CASE WHEN feature_clicks > 0 THEN 1 END) * 100.0 / COUNT(DISTINCT user_id) as adoption_rate,
COUNT(CASE WHEN feature_sessions > 5 THEN 1 END) * 100.0 / COUNT(DISTINCT user_id) as power_user_rate,
-- Engagement metrics
AVG(feature_time_spent) as avg_feature_time,
AVG(total_time_spent) as avg_total_time,
AVG(retention_days) as avg_retention,
-- Impact on overall engagement
AVG(feature_time_spent) * 100.0 / AVG(total_time_spent) as feature_engagement_share
FROM feature_test_users
GROUP BY test_group
),
statistical_significance AS (
SELECT
test_group,
adoption_rate,
avg_retention,
-- Calculate statistical significance for adoption
(adoption_rate - LAG(adoption_rate) OVER (ORDER BY test_group)) /
SQRT(POWER(adoption_rate * (100 - adoption_rate) / total_users, 2) +
POWER(LAG(adoption_rate) OVER (ORDER BY test_group) * (100 - LAG(adoption_rate) OVER (ORDER BY test_group)) /
LAG(total_users) OVER (ORDER BY test_group), 2)) as z_score_adoption
FROM feature_adoption_metrics
)
SELECT
test_group,
total_users,
ROUND(adoption_rate, 2) as adoption_rate_pct,
ROUND(power_user_rate, 2) as power_user_rate_pct,
ROUND(avg_feature_time, 2) as avg_feature_time_minutes,
ROUND(avg_total_time, 2) as avg_total_time_minutes,
ROUND(feature_engagement_share, 2) as feature_engagement_share_pct,
ROUND(avg_retention, 1) as avg_retention_days,
CASE
WHEN ABS(z_score_adoption) > 1.96 THEN 'Significant'
ELSE 'Not Significant'
END as adoption_significance,
-- Business recommendation
CASE
WHEN adoption_rate > 50 AND avg_retention > 30 THEN 'Roll out to all users'
WHEN adoption_rate > 25 AND avg_retention > 20 THEN 'Expand test to larger audience'
WHEN adoption_rate < 10 THEN 'Redesign feature or abandon'
ELSE 'Continue testing with modifications'
END as business_recommendation
FROM feature_adoption_metrics fam
JOIN statistical_significance ss ON fam.test_group = ss.test_group
ORDER BY fam.test_group;
2. Pricing Strategy Testing
-- Pricing strategy A/B test
WITH pricing_test_data AS (
SELECT
user_id,
price_point,
test_group,
purchase_made,
order_value,
-- Calculate revenue per user
CASE WHEN purchase_made = 1 THEN order_value ELSE 0 END as revenue_per_user,
-- User segment
customer_segment,
acquisition_channel
FROM pricing_test_results
WHERE test_id = 'q1_pricing_test'
AND test_date BETWEEN '2024-01-01' AND '2024-01-31'
),
pricing_analysis AS (
SELECT
price_point,
test_group,
COUNT(DISTINCT user_id) as total_users,
COUNT(CASE WHEN purchase_made = 1 THEN 1 END) as purchases,
SUM(order_value) as total_revenue,
AVG(order_value) as avg_order_value,
-- Conversion and revenue metrics
COUNT(CASE WHEN purchase_made = 1 THEN 1 END) * 100.0 / COUNT(DISTINCT user_id) as conversion_rate,
SUM(order_value) * 1.0 / COUNT(DISTINCT user_id) as revenue_per_user,
-- Segment-specific analysis
COUNT(CASE WHEN customer_segment = 'premium' AND purchase_made = 1 THEN 1 END) * 100.0 /
NULLIF(COUNT(CASE WHEN customer_segment = 'premium' THEN 1 END), 0) as premium_conversion_rate,
COUNT(CASE WHEN customer_segment = 'standard' AND purchase_made = 1 THEN 1 END) * 100.0 /
NULLIF(COUNT(CASE WHEN customer_segment = 'standard' THEN 1 END), 0) as standard_conversion_rate
FROM pricing_test_data
GROUP BY price_point, test_group
),
price_elasticity AS (
SELECT
price_point,
test_group,
conversion_rate,
revenue_per_user,
-- Calculate price elasticity
LAG(conversion_rate) OVER (ORDER BY price_point) - conversion_rate as conversion_change,
price_point - LAG(price_point) OVER (ORDER BY price_point) as price_change,
CASE
WHEN LAG(price_point) OVER (ORDER BY price_point) > 0 THEN
(conversion_change / LAG(conversion_rate) OVER (ORDER BY price_point)) /
(price_change / LAG(price_point) OVER (ORDER BY price_point))
ELSE NULL
END as price_elasticity
FROM pricing_analysis
)
SELECT
price_point,
test_group,
total_users,
ROUND(conversion_rate, 2) as conversion_rate_pct,
ROUND(avg_order_value, 2) as avg_order_value_usd,
ROUND(revenue_per_user, 2) as revenue_per_user_usd,
ROUND(price_elasticity, 2) as price_elasticity_coefficient,
-- Optimal price recommendation
CASE
WHEN revenue_per_user = MAX(revenue_per_user) OVER () THEN 'Optimal Price Point'
WHEN conversion_rate < 5 THEN 'Too High - Consider Lowering'
WHEN conversion_rate > 20 THEN 'Too Low - Consider Raising'
ELSE 'Acceptable Range'
END as price_recommendation,
-- Business impact
CASE
WHEN revenue_per_user > LAG(revenue_per_user) OVER (ORDER BY price_point) THEN 'Positive Impact'
WHEN revenue_per_user < LAG(revenue_per_user) OVER (ORDER BY price_point) THEN 'Negative Impact'
ELSE 'Neutral Impact'
END as business_impact
FROM price_elasticity
ORDER BY price_point;
1. Statistical Significance
2. Practical Significance
3. Decision Framework
Clear Winner (Statistically and Practically Significant)
Action: Implement winning variant
Consider: Full rollout strategy
Monitor: Post-implementation performance
Document: Lessons learned
Statistically Significant but Not Practically Significant
Action: Don't implement (costs > benefits)
Consider: Test with larger effect size
Monitor: Baseline performance
Document: Negative result insights
Practically Significant but Not Statistically Significant
Action: Extend test duration
Consider: Increase sample size
Monitor: Trend direction
Document: Inconclusive results
No Clear Winner
Action: Analyze why test failed
Consider: New hypothesis or approach
Monitor: External factors
Document: Learning and insights
Complete Test Result Dashboard
-- Comprehensive A/B test result analysis
WITH test_summary AS (
SELECT
test_id,
test_name,
start_date,
end_date,
DATEDIFF(end_date, start_date) as test_duration_days,
primary_metric,
secondary_metrics
FROM test_configuration
WHERE test_id = 'homepage_redesign_2024'
),
group_performance AS (
SELECT
test_id,
test_group,
COUNT(DISTINCT user_id) as sample_size,
-- Primary metric performance
AVG(primary_metric_value) as avg_primary_metric,
STDDEV(primary_metric_value) as stddev_primary_metric,
-- Secondary metrics
AVG(secondary_metric_1) as avg_secondary_1,
AVG(secondary_metric_2) as avg_secondary_2,
-- Conversion metrics
COUNT(CASE WHEN converted = 1 THEN 1 END) as conversions,
COUNT(CASE WHEN converted = 1 THEN 1 END) * 100.0 / COUNT(DISTINCT user_id) as conversion_rate
FROM test_results
WHERE test_id = 'homepage_redesign_2024'
GROUP BY test_id, test_group
),
statistical_tests AS (
SELECT
test_group,
sample_size,
avg_primary_metric,
stddev_primary_metric,
conversion_rate,
-- Calculate statistical significance
(avg_primary_metric - LAG(avg_primary_metric) OVER (ORDER BY test_group)) /
SQRT(POWER(stddev_primary_metric, 2) / sample_size +
POWER(LAG(stddev_primary_metric) OVER (ORDER BY test_group), 2) /
LAG(sample_size) OVER (ORDER BY test_group)) as t_statistic,
-- Degrees of freedom
sample_size + LAG(sample_size) OVER (ORDER BY test_group) - 2 as degrees_of_freedom,
-- Confidence intervals
avg_primary_metric - 1.96 * stddev_primary_metric / SQRT(sample_size) as ci_lower,
avg_primary_metric + 1.96 * stddev_primary_metric / SQRT(sample_size) as ci_upper
FROM group_performance
),
business_impact AS (
SELECT
test_group,
sample_size,
conversion_rate,
-- Revenue impact calculation
conversion_rate * 100.0 * 50.0 as estimated_revenue_per_1000_users, -- $50 avg order value
-- Cost impact
CASE
WHEN test_group = 'variant' THEN 5000 -- Implementation cost
ELSE 0
END as implementation_cost,
-- ROI calculation
(conversion_rate * 100.0 * 50.0 - 5000) / 5000 as roi
FROM statistical_tests
)
SELECT
'Test Overview' as section,
test_name as metric,
test_duration_days as value,
'days' as unit
FROM test_summary
UNION ALL
SELECT
'Sample Size' as section,
test_group as metric,
sample_size as value,
'users' as unit
FROM statistical_tests
UNION ALL
SELECT
'Primary Metric' as section,
test_group as metric,
ROUND(avg_primary_metric, 4) as value,
'rate' as unit
FROM statistical_tests
UNION ALL
SELECT
'Conversion Rate' as section,
test_group as metric,
ROUND(conversion_rate, 2) as value,
'%' as unit
FROM statistical_tests
UNION ALL
SELECT
'Statistical Significance' as section,
'T-Statistic' as metric,
ROUND(t_statistic, 3) as value,
'score' as unit
FROM statistical_tests
WHERE t_statistic IS NOT NULL
UNION ALL
SELECT
'Confidence Interval' as section,
test_group || ' Lower' as metric,
ROUND(ci_lower, 4) as value,
'rate' as unit
FROM statistical_tests
UNION ALL
SELECT
'Confidence Interval' as section,
test_group || ' Upper' as metric,
ROUND(ci_upper, 4) as value,
'rate' as unit
FROM statistical_tests
UNION ALL
SELECT
'Business Impact' as section,
test_group || ' Revenue/1K Users' as metric,
ROUND(estimated_revenue_per_1000_users, 2) as value,
'USD' as unit
FROM business_impact
UNION ALL
SELECT
'Business Impact' as section,
test_group || ' ROI' as metric,
ROUND(roi, 2) as value,
'multiple' as unit
FROM business_impact;
Segmentation Analysis
-- Analyze test results by user segments
WITH segment_performance AS (
SELECT
test_group,
user_segment,
acquisition_channel,
COUNT(DISTINCT user_id) as segment_users,
COUNT(CASE WHEN converted = 1 THEN 1 END) as segment_conversions,
COUNT(CASE WHEN converted = 1 THEN 1 END) * 100.0 / COUNT(DISTINCT user_id) as segment_conversion_rate,
-- Segment-specific lift calculation
COUNT(CASE WHEN converted = 1 THEN 1 END) * 100.0 / COUNT(DISTINCT user_id) -
LAG(COUNT(CASE WHEN converted = 1 THEN 1 END) * 100.0 / COUNT(DISTINCT user_id)) OVER (
PARTITION BY user_segment ORDER BY test_group
) as segment_lift
FROM test_results
WHERE test_id = 'homepage_redesign_2024'
GROUP BY test_group, user_segment, acquisition_channel
),
segment_significance AS (
SELECT
test_group,
user_segment,
acquisition_channel,
segment_users,
segment_conversion_rate,
segment_lift,
-- Calculate statistical significance for segments
CASE
WHEN ABS(segment_lift) > 2 * SQUT(POWER(segment_conversion_rate * (100 - segment_conversion_rate) / segment_users, 2)) THEN 'Significant'
ELSE 'Not Significant'
END as segment_significance
FROM segment_performance
)
SELECT
user_segment,
acquisition_channel,
test_group,
segment_users,
ROUND(segment_conversion_rate, 2) as conversion_rate_pct,
ROUND(segment_lift, 2) as lift_pct,
segment_significance,
-- Segment-specific recommendations
CASE
WHEN segment_lift > 5 AND segment_significance = 'Significant' THEN 'Strong positive impact - prioritize rollout'
WHEN segment_lift > 0 AND segment_significance = 'Significant' THEN 'Moderate positive impact - consider rollout'
WHEN segment_lift < -5 AND segment_significance = 'Significant' THEN 'Negative impact - avoid rollout'
WHEN segment_significance = 'Not Significant' THEN 'Inconclusive - need more data'
ELSE 'Minimal impact - optional rollout'
END as segment_recommendation
FROM segment_significance
ORDER BY user_segment, acquisition_channel, test_group;
Common Pitfalls
1. Statistical Errors
2. Implementation Errors
3. Interpretation Errors
Best Practices
1. Test Design
2. Implementation
3. Analysis
4. Decision Making
In the final lesson, we'll explore machine learning overview to understand advanced analytics techniques.