-- Data Validation
-- Check rows
SELECT COUNT(*) AS total_rows FROM customer_segmentation;

-- Check Duplicated (DISTINCT)
SELECT campaign_id, COUNT(*) 
FROM customer_segmentation
GROUP BY campaign_id
HAVING COUNT(*) > 1;

-- Chenk Missing (IS NULL)
SELECT 
  SUM(campaign_type IS NULL) AS missing_campaign_type,
  SUM(channel_used IS NULL) AS missing_channel,
  SUM(target_audience IS NULL) AS missing_audience,
  SUM(customer_segment IS NULL) AS missing_segment,
  SUM(conversion_rate IS NULL) AS missing_conversion,
  SUM(engagement_score IS NULL) AS missing_engagement,
  SUM(acquisition_cost IS NULL) AS missing_cost,
  SUM(roi IS NULL) AS missing_roi,
  SUM(date IS NULL) AS missing_date
FROM customer_segmentation;

-- Chenk Conversion Rate & ROI
SELECT * FROM customer_segmentation
WHERE conversion_rate > 1;
SELECT * FROM customer_segmentation
WHERE roi < -100 OR roi > 100;

-- Show dataset 10 row
SELECT * FROM customer_segmentation LIMIT 10;

-- Data Cleaning
-- Delete rows with incomplete data
DELETE FROM customer_segmentation
WHERE campaign_type IS NULL 
   OR customer_segment IS NULL 
   OR conversion_rate IS NULL;
   
-- Adjust conversion_rate if greater than 1  
UPDATE customer_segmentation
SET conversion_rate = conversion_rate / 100
WHERE conversion_rate > 1;

-- Convert format date column
UPDATE customer_segmentation
SET date = STR_TO_DATE(date, '%d/%m/%Y')
WHERE date LIKE '%/%';

-- Create a separate column by Quarter (Q1, Q2...)
ALTER TABLE customer_segmentation ADD COLUMN campaign_quarter VARCHAR(3);

UPDATE customer_segmentation
SET campaign_quarter = 
  CASE 
    WHEN MONTH(date) BETWEEN 1 AND 3 THEN 'Q1'
    WHEN MONTH(date) BETWEEN 4 AND 6 THEN 'Q2'
    WHEN MONTH(date) BETWEEN 7 AND 9 THEN 'Q3'
    WHEN MONTH(date) BETWEEN 10 AND 12 THEN 'Q4'
    ELSE 'N/A'
  END;

-- One-way ANOVA type_ROI
SELECT 
  campaign_type,
  roi
FROM customer_segmentation
WHERE campaign_type IS NOT NULL
  AND roi IS NOT NULL;

-- AVG ROI by Campaign Type  
SELECT 
  campaign_type,
  COUNT(*) AS n,
  AVG(roi) AS avg_roi,
  STDDEV(roi) AS std_dev
FROM customer_segmentation
GROUP BY campaign_type;

-- One-way ANOVA rate_segment
SELECT 
  customer_segment,
  conversion_rate
FROM customer_segmentation
WHERE customer_segment IS NOT NULL
  AND conversion_rate IS NOT NULL;

-- One-way ANOVA type_score
SELECT 
  campaign_type,
  engagement_score
FROM customer_segmentation
WHERE campaign_type IS NOT NULL
  AND engagement_score IS NOT NULL;

-- chi_channel_segment
SELECT 
  channel_used,
  customer_segment,
  COUNT(*) AS count
FROM customer_segmentation
GROUP BY channel_used, customer_segment
ORDER BY channel_used, customer_segment;
  
-- chi_type_segment
SELECT 
  campaign_type,
  customer_segment,
  COUNT(*) AS count
FROM customer_segmentation
GROUP BY campaign_type, customer_segment
ORDER BY campaign_type, customer_segment;

-- chi_channel_type
SELECT 
  campaign_type,
  channel_used,
  COUNT(*) AS count
FROM customer_segmentation
GROUP BY campaign_type, channel_used
ORDER BY campaign_type, channel_used;

import pandas as pd

# read_df
df_anova_type_roi = pd.read_csv('/data/notebook_files/data/anova_type_roi.csv')
df_anova_rate_segment = pd.read_csv('/data/notebook_files/data/anova_segment_rate.csv')
df_anova_type_score = pd.read_csv('/data/notebook_files/data/anova_type_score.csv')

# Check df
print(df_anova_type_score.head())

# Analysis ANOVA
from scipy.stats import f_oneway

def run_anova(df, group_col, value_col):
    groups = []
    for name, group in df.groupby(group_col):
        groups.append(group[value_col].dropna().values)  # handle missing values if any
    
    f_stat, p_val = f_oneway(*groups)
    print(f"ANOVA for '{value_col}' by '{group_col}':")
    print(f"F-statistic: {f_stat:.4f}")
    print(f"P-value: {p_val:.4f}\n")

# anova_type_roi
run_anova(df_anova_type_roi, 'campaign_type', 'roi')

# anova_rate_segment
run_anova(df_anova_rate_segment, 'customer_segment', 'conversion_rate')

# anova_type_score
run_anova(df_anova_type_score, 'campaign_type', 'engagement_score')

import pandas as pd

# read_df
df_chi_channel_segment = pd.read_csv('/data/notebook_files/data/chi_contingency_table.csv')
df_chi_type_segment = pd.read_csv('/data/notebook_files/data/chi_test_type_segment.csv')
df_chi_channel_type = pd.read_csv('/data/notebook_files/data/chi_test_channel_type.csv')

# Check df
print(df_chi_channel_type.head())

from scipy.stats import chi2_contingency

def run_chi_square(df, index_col, columns_col, values_col='count'):
    
    contingency = df.pivot(index=index_col, columns=columns_col, values=values_col).fillna(0)
    chi2, p_val, dof, expected = chi2_contingency(contingency)
    
    print(f"Chi-square Test for '{index_col}' vs '{columns_col}':")
    print(f"Chi-square statistic: {chi2:.4f}")
    print(f"Degrees of freedom: {dof}")
    print(f"P-value: {p_val:.4f}\n")
    
# Chi-square Test 1
run_chi_square(df_chi_channel_segment, 'channel_used', 'customer_segment')

# Chi-square Test 2
run_chi_square(df_chi_type_segment, 'campaign_type', 'customer_segment')

# Chi-square Test 3
run_chi_square(df_chi_channel_type, 'channel_used', 'campaign_type')