Marketplace Analytics ve KPI Takibi

-- 1. GMV ve Conversion Rate (günlük)
SELECT
    DATE(created_at) as date,
    COUNT(DISTINCT user_id) as visitors,
    COUNT(DISTINCT CASE WHEN status = 'completed' THEN order_id END) as orders,
    SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) as gmv,
    ROUND(COUNT(DISTINCT CASE WHEN status = 'completed' THEN order_id END) * 100.0 / COUNT(DISTINCT user_id), 2) as conversion_rate,
    ROUND(AVG(CASE WHEN status = 'completed' THEN total_amount END), 2) as aov
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- 2. Top 10 satıcılar (aylık)
SELECT
    seller_id,
    seller_name,
    COUNT(DISTINCT order_id) as order_count,
    SUM(total_amount) as revenue,
    AVG(rating) as avg_rating,
    COUNT(DISTINCT product_id) as product_count
FROM orders o
JOIN sellers s ON o.seller_id = s.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND o.status = 'completed'
GROUP BY seller_id, seller_name
ORDER BY revenue DESC
LIMIT 10;

-- 3. Kategori performansı
SELECT
    category_name,
    COUNT(DISTINCT order_id) as orders,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value,
    ROUND(SUM(total_amount) * 100.0 / SUM(SUM(total_amount)) OVER (), 2) as revenue_share
FROM orders o
JOIN products p ON o.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.status = 'completed'
  AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY category_name
ORDER BY revenue DESC;

-- 4. Cohort retention analysis
WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) as cohort_month
    FROM orders
    GROUP BY user_id
),
cohort_data AS (
    SELECT
        uc.cohort_month,
        DATE_TRUNC('month', o.created_at) as order_month,
        COUNT(DISTINCT o.user_id) as users
    FROM orders o
    JOIN user_cohorts uc ON o.user_id = uc.user_id
    GROUP BY uc.cohort_month, DATE_TRUNC('month', o.created_at)
)
SELECT
    cohort_month,
    order_month,
    users,
    EXTRACT(MONTH FROM AGE(order_month, cohort_month)) as month_number
FROM cohort_data
ORDER BY cohort_month, order_month;

# RFM (Recency, Frequency, Monetary) Segmentation
import pandas as pd
import numpy as np
from datetime import datetime, timedelta

# Veri yükle
df = pd.read_sql("""
    SELECT
        user_id,
        order_date,
        total_amount
    FROM orders
    WHERE status = 'completed'
""", connection)

# RFM hesaplama
snapshot_date = df['order_date'].max() + timedelta(days=1)

rfm = df.groupby('user_id').agg({
    'order_date': lambda x: (snapshot_date - x.max()).days,  # Recency
    'user_id': 'count',  # Frequency
    'total_amount': 'sum'  # Monetary
})

rfm.columns = ['recency', 'frequency', 'monetary']

# RFM skorları (1-5 arası, 5 = en iyi)
rfm['r_score'] = pd.qcut(rfm['recency'], q=5, labels=[5,4,3,2,1])  # Düşük recency = iyi
rfm['f_score'] = pd.qcut(rfm['frequency'].rank(method='first'), q=5, labels=[1,2,3,4,5])
rfm['m_score'] = pd.qcut(rfm['monetary'], q=5, labels=[1,2,3,4,5])

# RFM segment
rfm['rfm_score'] = rfm['r_score'].astype(str) + rfm['f_score'].astype(str) + rfm['m_score'].astype(str)

# Segment tanımları
def rfm_segment(row):
    r, f, m = int(row['r_score']), int(row['f_score']), int(row['m_score'])

    if r >= 4 and f >= 4 and m >= 4:
        return 'Champions'  # En değerli müşteriler
    elif r >= 3 and f >= 3:
        return 'Loyal Customers'
    elif r >= 4 and f <= 2:
        return 'Promising'  # Yeni, potansiyel
    elif r <= 2 and f >= 3:
        return 'At Risk'  # Kaybetme riski
    elif r <= 2 and f <= 2:
        return 'Hibernating'  # Uzun süredir alışveriş yok
    elif r <= 1:
        return 'Lost'  # Kayıp müşteriler
    else:
        return 'Need Attention'

rfm['segment'] = rfm.apply(rfm_segment, axis=1)

# Segment analizi
segment_summary = rfm.groupby('segment').agg({
    'recency': 'mean',
    'frequency': 'mean',
    'monetary': ['mean', 'sum', 'count']
}).round(2)

print(segment_summary)
#                   recency  frequency  monetary
#                                         mean     sum  count
# Champions            12       8.5    15000   1.5M    100
# Loyal Customers      25       5.2     8000   4.0M    500
# At Risk             180       6.8    12000   2.4M    200
# Lost                365       3.1     5000     500K    100

# Aksiyon planı
actions = {
    'Champions': 'VIP benefits, early access, exclusive offers',
    'Loyal Customers': 'Loyalty program, upsell/cross-sell',
    'Promising': 'Onboarding emails, product recommendations',
    'At Risk': 'Win-back campaigns, personalized offers',
    'Hibernating': 'Re-engagement emails, steep discounts',
    'Lost': 'Survey (why left?), last-chance offers'
}

for segment, action in actions.items():
    users = rfm[rfm['segment'] == segment].index.tolist()
    print(f"{segment} ({len(users)} users): {action}")

# Plotly Dash ile interaktif dashboard
import dash
from dash import dcc, html
import plotly.express as px
import plotly.graph_objects as go

app = dash.Dash(__name__)

# KPI Cards
def create_kpi_card(title, value, change):
    return html.Div([
        html.H4(title),
        html.H2(f"{value:,.0f}" if isinstance(value, (int, float)) else value),
        html.P(f"↑ {change}% vs last month", style={'color': 'green' if change > 0 else 'red'})
    ], style={'border': '1px solid #ddd', 'padding': '20px', 'borderRadius': '8px'})

# Layout
app.layout = html.Div([
    html.H1('Marketplace Analytics Dashboard'),

    # KPI Row
    html.Div([
        create_kpi_card('GMV', 5_200_000, 15.3),
        create_kpi_card('Orders', 12_450, 8.7),
        create_kpi_card('AOV', 418, 6.2),
        create_kpi_card('Conversion', '3.2%', -1.5)
    ], style={'display': 'flex', 'gap': '20px', 'marginBottom': '30px'}),

    # Charts Row 1
    html.Div([
        dcc.Graph(id='revenue-trend'),
        dcc.Graph(id='category-breakdown')
    ], style={'display': 'flex', 'gap': '20px'}),

    # Charts Row 2
    html.Div([
        dcc.Graph(id='cohort-heatmap'),
        dcc.Graph(id='rfm-scatter')
    ], style={'display': 'flex', 'gap': '20px'})
])

# Revenue trend
@app.callback(
    dash.dependencies.Output('revenue-trend', 'figure'),
    dash.dependencies.Input('revenue-trend', 'id')
)
def update_revenue_trend(_):
    df_revenue = pd.read_sql("""
        SELECT DATE(created_at) as date, SUM(total_amount) as revenue
        FROM orders WHERE created_at >= CURRENT_DATE - 90
        GROUP BY date ORDER BY date
    """, connection)

    fig = px.line(df_revenue, x='date', y='revenue',
                  title='Daily Revenue Trend (Last 90 Days)')
    return fig

# Category breakdown
@app.callback(
    dash.dependencies.Output('category-breakdown', 'figure'),
    dash.dependencies.Input('category-breakdown', 'id')
)
def update_category_breakdown(_):
    df_category = pd.read_sql("""
        SELECT c.name, SUM(o.total_amount) as revenue
        FROM orders o
        JOIN products p ON o.product_id = p.id
        JOIN categories c ON p.category_id = c.id
        WHERE o.created_at >= CURRENT_DATE - 30
        GROUP BY c.name
        ORDER BY revenue DESC
        LIMIT 10
    """, connection)

    fig = px.pie(df_category, names='name', values='revenue',
                 title='Revenue by Category (Last 30 Days)')
    return fig

if __name__ == '__main__':
    app.run_server(debug=True, port=8050)