Как посчитать Take Rate в SQL

Зачем Take Rate

Take Rate (комиссия) = % GMV, retained by marketplace. Базовая monetization метрика. Uber 25%, Airbnb 14-16%, Etsy 6-8%. Слишком высокий → пользователи уходят к direct seller. Низкий → не covers costs.

Формула

Take Rate = marketplace_revenue / GMV × 100%

Where:

• GMV = total transaction value
• Marketplace revenue = commission + fees + ads

Базовый расчёт

SELECT
    DATE_TRUNC('month', transaction_date) AS month,
    SUM(transaction_value) AS gmv,
    SUM(platform_commission) AS revenue,
    SUM(platform_commission)::NUMERIC * 100 / NULLIF(SUM(transaction_value), 0) AS take_rate_pct
FROM transactions
WHERE status = 'completed'
  AND transaction_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

По категориям

SELECT
    category,
    COUNT(*) AS transactions,
    SUM(transaction_value) AS gmv,
    SUM(platform_commission) AS revenue,
    SUM(platform_commission) * 100.0 / NULLIF(SUM(transaction_value), 0) AS take_rate_pct
FROM transactions t
JOIN products p USING (product_id)
WHERE t.status = 'completed'
  AND t.transaction_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY category
ORDER BY take_rate_pct DESC;

Different categories — different rates (commodities < unique goods).

Тренды

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', transaction_date) AS month,
        SUM(transaction_value) AS gmv,
        SUM(platform_commission) AS revenue,
        SUM(platform_commission) * 100.0 / SUM(transaction_value) AS take_rate
    FROM transactions
    WHERE status = 'completed'
      AND transaction_date >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY 1
)
SELECT
    month,
    gmv,
    revenue,
    take_rate,
    take_rate - LAG(take_rate) OVER (ORDER BY month) AS mom_change_pp
FROM monthly
ORDER BY month;

Take rate растёт — additional services (ads, premium). Падает — competitive pressure.

Effective vs Listed

«Listed» rate = что заявлено. «Effective» = что реально:

SELECT
    AVG(listed_rate) AS avg_listed_pct,
    SUM(platform_commission) * 100.0 / SUM(transaction_value) AS effective_take_rate_pct,
    -- Gap (discounts / promotions)
    AVG(listed_rate) - SUM(platform_commission) * 100.0 / SUM(transaction_value) AS gap_pct
FROM transactions
WHERE status = 'completed'
  AND transaction_date >= CURRENT_DATE - INTERVAL '30 days';

Effective часто lower из-за discounts / preferred sellers / promo.

Take Rate + Add-on Revenue

SELECT
    DATE_TRUNC('month', DATE) AS month,
    SUM(transaction_value) AS gmv,
    SUM(transaction_commission) AS core_commission,
    SUM(advertising_revenue) AS ads_revenue,
    SUM(payment_processing_fees) AS payment_fees,
    SUM(shipping_fees) AS shipping_fees,
    (SUM(transaction_commission) + SUM(advertising_revenue) + SUM(payment_processing_fees) + SUM(shipping_fees))
    * 100.0 / NULLIF(SUM(transaction_value), 0) AS effective_take_rate
FROM marketplace_revenue
WHERE DATE >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

Частые ошибки

Ошибка 1. Core commission only. Real take rate includes ads, fees, value-add. Always blended.

Ошибка 2. GMV includes refunds? Refunded transactions — count в GMV или нет? Define.

Ошибка 3. Listed vs effective. Reporting listed make-believe. Effective real money.

Ошибка 4. Free shipping subsidies. Marketplace pays shipping → reduces take rate effectively.

Ошибка 5. Different rate tiers. Sellers с different tiers (volume discounts). Aggregate hides distribution.

Связанные темы

• Как посчитать GMV в SQL
• Как посчитать revenue в SQL
• Как посчитать gross margin в SQL
• Как посчитать ARPDAU в SQL

FAQ

Какой Take Rate ok?

Uber/Lyft: 25%. Airbnb: 14-16%. Etsy: 6-8%. Amazon: 15% + ads. Зависит от value-add.

Increase Take Rate как?

1. Ads marketplace. 2) Premium subscriptions. 3) Logistics services. 4) Payment processing.

Take Rate per transaction vs GMV-weighted?

GMV-weighted = total revenue / total GMV. Per transaction = avg take rate. Different numbers.

Listed vs Effective?

Effective — real. Always lower из-за discounts.

Cap take rate?

Buyer / seller sensitivity. Too high → disintermediation (parties book offline).