القمع والـ cohorts ومعدل الاحتفاظ

طلبان في رأس القائمة. هدى: «ميزانية البحث المدفوع قيد المراجعة. أي مصادر الزيارات تحوّل الزيارات فعلًا إلى طلبات، وأين يتسرّب الناس؟». ورانيا: «اكتسبنا عملاء كثيرين في أشهر Black Friday. هل يعودون بالقدر نفسه الذي يعود به الآخرون؟». الأول قمع (funnel)، والثاني تحليل معدل الاحتفاظ لكل cohort. كلاهما مبني من أشياء تعرفها بالفعل: التجميع الشرطي، والتواريخ، والـ CTEs.

قمع من أحداث الجلسات

كل صف في web_sessions زيارة واحدة، مع علامات للخطوات التي بلغتها: added_to_cart وpurchased (0 أو 1). جمع علامة قيمتها 0/1 يعدّ الجلسات التي بلغت الخطوة، فيكون القمع GROUP BY واحدًا:

SELECT
  source,
  COUNT(*)            AS sessions,
  SUM(added_to_cart)  AS carts,
  SUM(purchased)      AS purchases,
  ROUND(100.0 * SUM(added_to_cart) / COUNT(*), 1)                  AS cart_rate,
  ROUND(100.0 * SUM(purchased) / NULLIF(SUM(added_to_cart), 0), 1) AS checkout_rate,
  ROUND(100.0 * SUM(purchased) / COUNT(*), 1)                      AS conversion
FROM web_sessions
WHERE started_at >= '2025-01-01'
GROUP BY source
ORDER BY conversion DESC;

البريد الإلكتروني هو الأفضل تحويلًا إجمالًا، 10.9% من الجلسات، والإحالات هي الأسوأ بـ 7.1%. لكن معدلات الخطوات تحكي القصة الأنفع. زوّار الإحالات نادرًا ما يضيفون إلى السلة (13.3%)، لكن من يضيف منهم يتمّ الشراء أكثر من أي فئة أخرى (53.6%). والبريد الإلكتروني عكس ذلك. هاتان مشكلتان مختلفتان وإصلاحهما مختلف: الإحالات تحتاج إلى صفحات هبوط أفضل، والبريد يحتاج إلى عملية دفع أسلس.

لهذا يعرض القمع معدلات الخطوات، أي كل خطوة مقسومة على السابقة، لا التحويل من البداية إلى النهاية وحده. اجعل كل مقام صريحًا في اسم العمود؛ فـ«معدل التحويل» بلا مقام هو أكثر رقم يُتجادل حوله في أي اجتماع تسويق. ويحمي NULLIF القسمة لمصدر ليس فيه سلال.

استبدل source بـ device فيظهر اكتشاف أوضح: جلسات الحاسوب المكتبي التي تبلغ السلة تتمّ الشراء في 56% من الحالات، وجلسات الجوال في 37% فقط. والجوال يجلب أكبر قدر من الزيارات، فهذه الفجوة أثمن من أي تعديل على ميزانية الإعلانات.

الـ cohorts: تجميع العملاء حسب وقت بدايتهم

الـ cohort مجموعة من العملاء يتشاركون فترة بداية واحدة. وهي هنا شهر أول طلب غير ملغى لهم. ثم يسأل معدل الاحتفاظ: من العملاء الذين بدأوا في الشهر M، ما نسبة من طلبوا مجددًا بعد شهر أو شهرين أو ثلاثة؟

ثلاث خطوات، وثلاثة CTEs. أولًا، أعطِ كل طلب مؤشّر شهر، year * 12 + month، حتى تكون فروق الأشهر عملية طرح بسيطة حتى عبر حدود السنة. ثانيًا، اعثر على الشهر الأول لكل عميل. ثالثًا، اعرض الأشهر النشطة لكل عميل نسبةً إلى الـ cohort الخاص به:

WITH orders_ok AS (
  SELECT customer_id, order_date,
         CAST(strftime('%Y', order_date) AS INTEGER) * 12
           + CAST(strftime('%m', order_date) AS INTEGER) AS month_index
  FROM orders
  WHERE status <> 'cancelled'
),
firsts AS (
  -- grain: one row per customer
  SELECT customer_id,
         MIN(month_index) AS cohort_index,
         strftime('%Y-%m', MIN(order_date)) AS cohort
  FROM orders_ok
  GROUP BY customer_id
),
activity AS (
  -- grain: one row per customer per active month
  SELECT DISTINCT f.cohort, f.cohort_index, o.customer_id,
         o.month_index - f.cohort_index AS month_number
  FROM orders_ok AS o
  JOIN firsts AS f ON f.customer_id = o.customer_id
)
SELECT
  cohort,
  COUNT(*) FILTER (WHERE month_number = 0) AS customers,
  ROUND(100.0 * COUNT(*) FILTER (WHERE month_number = 1)
        / COUNT(*) FILTER (WHERE month_number = 0), 1) AS m1_pct,
  CASE WHEN MAX(cohort_index) + 2 <= 2025 * 12 + 12 THEN
    ROUND(100.0 * COUNT(*) FILTER (WHERE month_number = 2)
          / COUNT(*) FILTER (WHERE month_number = 0), 1)
  END AS m2_pct
FROM activity
WHERE cohort >= '2025-07'
GROUP BY cohort
ORDER BY cohort;

يحوّل الـ SELECT الأخير أرقام الأشهر إلى أعمدة باستخدام FILTER. والـ DISTINCT في activity ضروري: معدل الاحتفاظ يعدّ أشخاصًا، والعميل الذي يطلب ثلاث مرات في شهر واحد عميل نشط واحد.

«غير معروف» ليس صفرًا

انظر إلى صف ديسمبر 2025 في نتيجة الـ query: قيمة m1_pct هي 0.0. هذا ليس انقطاعًا. الشهر 1 لـ cohort ديسمبر هو يناير 2026، والبيانات تنتهي في 31 ديسمبر. يعرض العمود m2_pct الإصلاح: تعبير CASE يفحص هل عاش الـ cohort ذلك الشهر (أي هل ما زال cohort_index + 2 داخل ديسمبر 2025) ويعيد NULL في غير ذلك. مخطط الاحتفاظ ذو الخانات الفارغة الصادقة مثلث؛ أما المخطط ذو الأصفار فيُظهر انهيارًا وهميًا في كل cohort حديث.

نعود إلى سؤال رانيا. غيّر الشرط إلى 2024، فتجد أن cohort نوفمبر 2024، وهو أول جمهور Black Friday لدى Cartwheel وأكبر cohort في تلك السنة بـ 35 عميلًا، لا يحتفظ إلا بـ 14.3% في الشهر 1، وهذا أقل من معظم cohorts عام 2024. عملاء الخصومات الكبيرة يأتون عادةً من أجل الصفقة. هذه إجابة تستطيع استخدامها حين تخطّط للعرض الترويجي القادم. ويطرح التمرين سؤالًا قريب الصلة: ما نسبة من يعودون من كل cohort أصلًا خلال ثلاثة أشهر؟

بهذا تكتمل أدوات التحليل. أما القسم الأخير فيدور حول الثقة: التأكد من أن البيانات والـ queries التي تكتبها تستحقها.