<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

/**
 * InPractice — Marketing Generic Cases [09/10]
 * Customer Acquisition & Retention: MG-CAR-03 to MG-CAR-06
 * Marketing Analytics: MG-MA-01
 *
 * Run: php artisan db:seed --class=MarketingGenericCaseSeeder_09
 */
class MarketingGenericCaseSeeder_09 extends Seeder
{
    public function run(): void
    {
        $this->command->info('📢 [09/10] Customer Acquisition (3-6) + Marketing Analytics (1) ...');

        $hub   = DB::table('hubs')->where('slug', 'marketing')->first();
        $admin = DB::table('users')->where('role', 'admin')->first();

        if (! $hub || ! $admin) {
            $this->command->error('Hub or admin not found. Run HubSeeder and AdminSeeder first.');
            return;
        }

        $hubId   = $hub->id;
        $adminId = $admin->id;
        $now     = now();


        $cases = [

        // ── MG-CAR-03 ─────────────────────────────────────────────────
    [
        'hub_id'        => $hubId,
        'created_by'    => $adminId,
        'topic_slug'    => 'customer_acquisition_retention',
        'topic_name_en' => 'Customer Acquisition & Retention',
        'topic_name_ar' => 'استقطاب العملاء والاحتفاظ بهم',
        'layer'         => 'generic',
        'difficulty'    => 'intermediate',
        'status'        => 'published',
        'published_at'  => $now,
        'sort_order'    => 703,
        'read_time_minutes' => 12,

        'title_en'    => 'The Referral Programme That Brought the Wrong Customers',
        'subtitle_en' => 'Why incentivising referrals without qualifying them is more expensive than paid ads',
        'situation_en' => "You are a growth manager at **Leva**, an Egyptian buy-now-pay-later (BNPL) fintech. The company launched a referral programme 6 months ago: existing users who refer a new user who completes their first purchase both receive EGP 100 credit.\n\nThe referral programme results:\n\n| Metric | Result |\n|---|---|\n| New users via referral | 8,400 |\n| Cost of programme (EGP 200/pair) | EGP 1,680,000 |\n| Referral users: completed 1+ purchase | 71% |\n| Referral users: completed 3+ purchases | 24% |\n| Referral users: default rate | 18% |\n| Organic users: default rate | 7% |\n| Referral users: 6-month LTV | EGP 340 |\n| Organic users: 6-month LTV | EGP 890 |\n\nThe CFO is alarmed: the referral programme is generating users with 2.5× the default rate of organic users and 38% of their LTV. The growth team calls it a success because of the user volume.",
        'challenge_questions_en' => json_encode([
            "Calculate the true cost of acquisition for referral users when default losses are factored in alongside the EGP 200 referral cost.",
            "Why do referred users in a BNPL product have higher default rates and lower LTV than organically acquired users?",
            "The growth team says the programme is a success. The CFO says it is a liability. Who is right — and what metric should settle the argument?",
            "Redesign the referral programme to maintain volume while qualifying referrals for creditworthiness — without making the programme so complex that referrals stop happening.",
        ]),
        'solution_en' => "## Referral Programmes in Financial Products: Quality Over Volume\n\n### The True Cost of Acquisition\n\nSimple referral cost: EGP 200 per new user pair\nDefault rate: 18% of referral users default\nAverage outstanding balance at default (assumption: EGP 1,200): 18% × EGP 1,200 = EGP 216 average default loss per referral user\n\n**Blended CAC including defaults:**\n= EGP 200 (referral cost) + EGP 216 (expected default loss)\n= **EGP 416 true cost per referral user**\n\nVs. organic user CAC (assume EGP 180) + EGP 63 default loss (7% × EGP 900)\n= **EGP 243 true cost per organic user**\n\nThe referral programme costs 71% more per user when default losses are included — and delivers an LTV of EGP 340 vs. EGP 890 for organic users.\n\n**Unit economics:** Referral user generates EGP 340 LTV at EGP 416 true CAC = **negative return before operating costs.**\n\n### Why Referred Users Default More\n\nIn BNPL, organic users discover the product through advertising, research, or organic search — and they are usually motivated by a specific purchase they have planned. They have purchase intent and (typically) the means to repay.\n\nReferral users are often recruited by friends with: *'Download this app, we both get EGP 100.'* Their primary motivation is the credit, not a planned purchase. They may:\n- Have lower creditworthiness\n- Make impulse purchases they cannot repay\n- Not understand the product's repayment terms\n- Abandon after the first EGP 100 is spent\n\nThis is called **incentive-driven adverse selection** — the incentive systematically attracts the customers who are least suitable for the product.\n\n### Settling the Growth vs. Finance Argument\n\nThe metric that settles it: **LTV:CAC ratio**\n\n- Referral users: EGP 340 LTV ÷ EGP 416 CAC = **0.82** (below 1 = destroying value)\n- Organic users: EGP 890 LTV ÷ EGP 243 CAC = **3.67** (healthy)\n\nThe CFO is right. Volume is not success when LTV:CAC < 1.\n\n### Redesigning the Programme\n\n**Gate 1 — Soft qualification before credit release:**\nThe EGP 100 credit for the new user is not released immediately. It is released after:\n- Identity verified\n- First repayment successfully completed\n- 7-day cooling off period\n\nThis eliminates users who signed up only for the credit with no intent to use the product properly.\n\n**Gate 2 — Tiered credit based on repayment:**\n- New referred user: EGP 200 first purchase limit (vs. standard EGP 600)\n- Limit increases to standard after 2 successful repayments\n\n**Gate 3 — Referrer quality score:**\nReferrers with default history or low repayment scores earn reduced referral credit (EGP 50 instead of EGP 100). This incentivises good users to refer other good users.\n\n**Expected outcome:** Referral volume drops 30–40% (lower than without gates). Referral default rate drops to 10–12%. LTV improves to EGP 600+. Unit economics become positive.",

        'bigger_lesson_en' => "**In financial products, a new user is not an asset until they repay. Volume without quality is a fast path to a bad-debt problem.**\n\nGrowth metrics and financial metrics must be evaluated together. A programme that looks great on a growth dashboard and terrible on a risk dashboard is a problem — not a success to be celebrated and scaled.",

        'skills_taught_en' => json_encode([
            'True CAC calculation including default costs',
            'Referral programme design for fintech',
            'LTV:CAC ratio as a health metric',
            'Adverse selection in incentive programmes',
            'Quality-gated referral mechanics',
        ]),

        'title_ar'    => 'برنامج الإحالة الذي جلب العملاء الخاطئين',
        'subtitle_ar' => 'لماذا تحفيز الإحالات دون تأهيلها أكثر تكلفةً من الإعلانات المدفوعة',
        'situation_ar' => "أنت مدير نمو في **ليفا**، شركة فينتك مصرية للشراء الآن والدفع لاحقاً. أطلقت الشركة برنامج إحالة قبل 6 أشهر: المستخدمون المُحيلون والمستخدمون الجدد يحصلان على 100 جنيه رصيد لكل منهما.\n\n| المقياس | النتيجة |\n|---|---|\n| مستخدمون جدد عبر الإحالة | 8,400 |\n| تكلفة البرنامج | 1,680,000 جنيه |\n| معدل التخلف عن السداد (إحالة) | 18% |\n| معدل التخلف عن السداد (عضوي) | 7% |\n| LTV 6 أشهر (إحالة) | 340 جنيه |\n| LTV 6 أشهر (عضوي) | 890 جنيه |\n\nفريق النمو يسمّيه نجاحاً بسبب الحجم. المدير المالي يُحذّر.",
        'challenge_questions_ar' => json_encode([
            "احسب التكلفة الحقيقية للاستقطاب لمستخدمي الإحالة حين تأخذ في الاعتبار خسائر التخلف عن السداد.",
            "لماذا يمتلك المستخدمون المُحالون في منتج BNPL معدلات تخلف أعلى وLTV أقل من المستخدمين العضويين؟",
            "فريق النمو يقول البرنامج ناجح. المدير المالي يقول إنه مسؤولية. من المحق — وما المقياس الذي يحسم الجدل؟",
            "أعد تصميم البرنامج للحفاظ على الحجم مع تأهيل الإحالات — دون جعله معقداً لدرجة وقف الإحالات.",
        ]),
        'solution_ar' => "## برامج الإحالة في المنتجات المالية: الجودة فوق الحجم\n\n### التكلفة الحقيقية للاستقطاب\n\nتكلفة الإحالة: 200 جنيه\nخسارة التخلف المتوقعة: 18% × 1,200 جنيه = 216 جنيه\n**تكلفة الاستقطاب الحقيقية: 416 جنيه/مستخدم**\n\nمقارنة بالمستخدم العضوي: 243 جنيه — البرنامج أغلى بـ71%.\n\n**نسبة LTV:CAC:**\n- إحالة: 340 ÷ 416 = **0.82** (أقل من 1 = تدمير قيمة)\n- عضوي: 890 ÷ 243 = **3.67** (صحي)\n\n### إعادة تصميم البرنامج\n\n**البوابة 1:** الرصيد لا يُفرَج إلا بعد أول سداد ناجح\n**البوابة 2:** حد شراء أولي أقل (200 جنيه) يرتفع بعد سدادَين ناجحَين\n**البوابة 3:** تقييم جودة المُحيل — أصحاب السجل الائتماني الجيد يحصلون على مكافأة أعلى",

        'bigger_lesson_ar' => "**في المنتجات المالية، المستخدم الجديد ليس أصلاً حتى يسدد. الحجم بلا جودة طريق سريع لمشكلة الديون المعدومة.**\n\nمقاييس النمو والمقاييس المالية يجب تقييمها معاً. البرنامج الذي يبدو رائعاً على لوحة النمو ومريعاً على لوحة المخاطر مشكلة — لا نجاح يُحتفى به ويُوسَّع.",

        'skills_taught_ar' => json_encode([
            'حساب التكلفة الحقيقية للاستقطاب بما فيها تكاليف التخلف',
            'تصميم برنامج الإحالة للفينتك',
            'نسبة LTV:CAC كمقياس صحة',
            'الانتقاء العكسي في برامج الحوافز',
            'آليات الإحالة المؤهَّلة',
        ]),
        'created_at' => $now,
        'updated_at' => $now,
    ],

        // ── MG-CAR-04 ─────────────────────────────────────────────────
    [
        'hub_id'        => $hubId,
        'created_by'    => $adminId,
        'topic_slug'    => 'customer_acquisition_retention',
        'topic_name_en' => 'Customer Acquisition & Retention',
        'topic_name_ar' => 'استقطاب العملاء والاحتفاظ بهم',
        'layer'         => 'generic',
        'difficulty'    => 'intermediate',
        'status'        => 'published',
        'published_at'  => $now,
        'sort_order'    => 704,
        'read_time_minutes' => 12,

        'title_en'    => 'The Onboarding That Lost Half the New Users',
        'subtitle_en' => 'The first 30 days determine whether a new customer stays for years or disappears forever',
        'situation_en' => "You are the product marketing manager at **Moashr**, an Egyptian payroll and HR management SaaS serving SMEs. The product has a 14-day free trial, after which companies must subscribe at EGP 1,200/month.\n\nCurrent metrics:\n\n| Metric | Result |\n|---|---|\n| Trial starts per month | 340 |\n| Trial-to-paid conversion | 18% |\n| Industry benchmark conversion | 35–45% |\n| Paid subscribers who cancel in Month 1 | 31% |\n| Paid subscribers who cancel in Month 2 | 19% |\n| Paid subscribers who stay 12+ months | 38% |\n\nOf the 340 who start trials, only 61 convert to paid. Of those 61, 19 cancel in Month 1, 12 more cancel in Month 2. Only 23 of the original 340 (6.8%) become 12-month subscribers.\n\nThe head of sales says the problem is pricing. The head of product says the problem is features. You believe the problem is onboarding.",
        'challenge_questions_en' => json_encode([
            "18% trial-to-paid conversion vs. 35-45% benchmark. What onboarding failures would cause users to not convert — before they even see the paywall?",
            "31% of paid subscribers cancel in Month 1. This is a post-purchase problem, not a sales problem. What causes a paying customer to cancel so quickly after subscribing?",
            "What is the 'aha moment' concept in SaaS onboarding — and what would Moashr's aha moment be for a typical HR manager?",
            "Design a 30-day onboarding journey for Moashr that improves both trial-to-paid conversion and Month 1 retention.",
        ]),
        'solution_en' => "## SaaS Onboarding: The Make-or-Break Window\n\n### Why Trial Users Don't Convert\n\nIn a 14-day trial, most users who don't convert fall into one of three groups:\n\n**Group 1 — Never activated (never experienced core value)**\nThey signed up, logged in once, saw a complex setup screen, and never returned. They did not fail the product — the product failed to get them to the point where they could see its value.\n\n**Group 2 — Got partially set up, got stuck**\nThey added some data (company info, maybe one employee) but hit a step they could not complete alone (payroll calculation setup, bank integration). No help arrived. They stopped.\n\n**Group 3 — Did not see the payoff fast enough**\nThey understood the product but the benefit — automated, error-free payroll — requires full setup to be experienced. In 14 days without active guidance, full setup almost never happens.\n\n### Why Month 1 Paid Subscribers Cancel\n\nThe 31% who cancel in Month 1 likely converted on hope, not value. They paid expecting the product to quickly solve their payroll problem. In Month 1, if they still:\n- Have not completed employee data entry\n- Have not run their first payroll through the system\n- Have not received the promised time savings\n\n...they cancel because the product never delivered what it promised. This is a **value gap** — the gap between what customers expect at purchase and what they experience in the first billing cycle.\n\n### The 'Aha Moment' for Moashr\n\nThe aha moment is the specific point in product usage where the user thinks: *'This product is genuinely solving my problem. I need this.'*\n\nFor Moashr's typical user — an HR manager or business owner who previously did payroll manually in Excel:\n\n> **Aha moment: Running their first payroll through Moashr and seeing it complete in under 10 minutes with zero calculation errors — compared to the 3 hours it used to take.**\n\nEvery onboarding action should be designed to get the user to this moment as fast as possible.\n\n### 30-Day Onboarding Journey\n\n**Day 1 — Activation (the first 20 minutes)**\n- Welcome email arrives within 5 minutes of signup: 'One thing to do before you sleep today: add your first employee'\n- In-app: skip the full setup wizard; start with adding ONE employee and previewing their payroll calculation\n- Goal: the user sees the product working with real data within 20 minutes of signup\n\n**Days 2–7 — Progress (getting to the aha moment)**\n- Day 2 email: 'You've added [X] employees — here's what your payroll preview looks like'\n- In-app progress bar: 'You're 60% to your first payroll run'\n- If user stalls for 48 hours: automated message from account manager offering a 30-minute setup call\n- Day 7: If not completed first payroll run — escalate to personal outreach\n\n**Days 8–14 — Conversion (before the paywall)**\n- Day 10: 'You're 4 days from the end of your trial. Here's what your first month's payroll would look like if you subscribe.'\n- Show: time saved, errors caught, tax calculation summary\n- Day 13: Offer a 20% Month 1 discount if they subscribe today (urgency + incentive)\n\n**Days 15–30 — Month 1 success (post-subscription)**\n- Structured check-in on Day 20: 'Have you run your first real payroll? Here is how.'\n- Day 25: Feature spotlight email — 'Did you know Moashr also handles social insurance filing automatically?'\n- Day 30: Success email with data: 'In your first month, Moashr processed [X] employee salaries, saving you an estimated [Y] hours.'",

        'bigger_lesson_en' => "**In SaaS, the first 30 days are not the beginning of the customer relationship — they are the audition.**\n\nCustomers who experience the product's core value in their first week convert at 3× the rate of those who don't. Customers who run their first payroll in Month 1 cancel at half the rate of those who don't. The entire job of onboarding is to manufacture that experience as quickly as possible for every single user.",

        'skills_taught_en' => json_encode([
            'SaaS trial-to-paid conversion optimisation',
            'Onboarding journey design',
            'Aha moment identification and acceleration',
            'Month 1 churn prevention',
            'Value gap diagnosis',
        ]),

        'title_ar'    => 'التأهيل الذي أفقد نصف المستخدمين الجدد',
        'subtitle_ar' => 'الـ30 يوماً الأولى تحدد ما إذا كان العميل الجديد سيبقى لسنوات أم يختفي إلى الأبد',
        'situation_ar' => "أنت مدير تسويق المنتج في **مؤشر**، شركة مصرية SaaS لإدارة الرواتب والموارد البشرية للشركات الصغيرة. للمنتج تجربة مجانية 14 يوماً ثم اشتراك بـ1,200 جنيه/شهر.\n\n| المقياس | النتيجة |\n|---|---|\n| بدايات التجربة/شهر | 340 |\n| معدل تحويل التجربة إلى مدفوع | 18% |\n| معيار الصناعة | 35-45% |\n| المشتركون المدفوعون الملغون في الشهر 1 | 31% |\n| المشتركون الباقون 12+ شهراً | 38% |\n\nمن الـ340 يبدأون التجربة، فقط 23 (6.8%) يصبحون مشتركين لـ12 شهراً.",
        'challenge_questions_ar' => json_encode([
            "معدل تحويل 18% مقابل معيار 35-45%. ما إخفاقات التأهيل التي تجعل المستخدمين لا يتحولون قبل رؤية الجدار المدفوع؟",
            "31% من المشتركين المدفوعين يلغون في الشهر الأول. هذه مشكلة ما بعد الشراء لا مشكلة مبيعات. ما الذي يجعل عميلاً يدفع يلغي بهذه السرعة؟",
            "ما مفهوم 'لحظة الآها' في تأهيل SaaS — وما ستكون لحظة الآها في مؤشر لمدير HR نموذجي؟",
            "صمم رحلة تأهيل 30 يوماً لمؤشر تُحسّن كلاً من تحويل التجربة والاحتفاظ في الشهر الأول.",
        ]),
        'solution_ar' => "## تأهيل SaaS: نافذة الصنع أو الكسر\n\n### لحظة الآها في مؤشر\n\n> **لحظة الآها: تشغيل أول راتب عبر مؤشر ورؤيته يكتمل في أقل من 10 دقائق بصفر أخطاء — مقارنة بـ3 ساعات كانت تستغرقها يدوياً.**\n\nكل إجراء تأهيل يجب تصميمه لوصول المستخدم لهذه اللحظة بأسرع ما يمكن.\n\n### رحلة تأهيل 30 يوماً\n\n**اليوم 1:** تفعيل — إضافة موظف واحد ومعاينة راتبه خلال 20 دقيقة من التسجيل\n**الأيام 2-7:** تقدم — تحديثات بريدية، شريط تقدم، تواصل شخصي عند التوقف\n**الأيام 8-14:** تحويل — معاينة الشهر الأول، خصم 20% لمن يشترك اليوم\n**الأيام 15-30:** نجاح ما بعد الاشتراك — تسجيل دخول يوم 20، بريد نجاح يوم 30 بالبيانات الفعلية",

        'bigger_lesson_ar' => "**في SaaS، الـ30 يوماً الأولى ليست بداية علاقة العميل — إنها التجربة.**\n\nالعملاء الذين يختبرون القيمة الجوهرية للمنتج في أسبوعهم الأول يتحولون بمعدل 3 أضعاف أولئك الذين لا يفعلون. المهمة الكاملة للتأهيل هي صنع تلك التجربة بأسرع ما يمكن لكل مستخدم.",

        'skills_taught_ar' => json_encode([
            'تحسين تحويل التجربة إلى مدفوع في SaaS',
            'تصميم رحلة التأهيل',
            'تحديد لحظة الآها وتسريعها',
            'منع الرحيل في الشهر الأول',
            'تشخيص فجوة القيمة',
        ]),
        'created_at' => $now,
        'updated_at' => $now,
    ],

        // ── MG-CAR-05 ─────────────────────────────────────────────────
    [
        'hub_id'        => $hubId,
        'created_by'    => $adminId,
        'topic_slug'    => 'customer_acquisition_retention',
        'topic_name_en' => 'Customer Acquisition & Retention',
        'topic_name_ar' => 'استقطاب العملاء والاحتفاظ بهم',
        'layer'         => 'generic',
        'difficulty'    => 'advanced',
        'status'        => 'published',
        'published_at'  => $now,
        'sort_order'    => 705,
        'read_time_minutes' => 14,

        'title_en'    => 'The Customer Segment Nobody Was Talking To',
        'subtitle_en' => 'How behavioural segmentation unlocks revenue that demographic segmentation misses',
        'situation_en' => "You are the CMO at **Almaza Premium Foods**, an Egyptian gourmet food company offering premium imported cheeses, charcuterie, and specialty pantry items through a website and WhatsApp ordering. Annual revenue: EGP 18M.\n\nYour customer database has 11,200 customers. The marketing team has been segmenting them demographically: age, location, gender — and running the same communications across all segments.\n\nA data analyst joins the team and builds a behavioural segmentation model based on purchase history. The findings:\n\n| Segment | Size | Annual Revenue | Avg Order Value | Purchase Frequency | Last Purchase |\n|---|---|---|---|---|---|\n| Champions | 420 | EGP 8.1M | EGP 1,850 | 8.2x/year | < 30 days |\n| Loyal Regulars | 1,100 | EGP 5.4M | EGP 890 | 4.1x/year | < 60 days |\n| At-Risk High-Value | 380 | EGP 2.2M | EGP 780 | was 3x, now 0.8x | 61-180 days |\n| One-Time Buyers | 6,800 | EGP 2.1M | EGP 310 | 1x | 180+ days |\n| New Customers | 2,500 | EGP 0.2M | EGP 220 | 1x | < 30 days |\n\nThe analyst presents this data and says: *'The company is treating 11,200 customers identically, even though 3.7% of them (the Champions) generate 45% of revenue.'*",
        'challenge_questions_en' => json_encode([
            "What is the difference between demographic segmentation and RFM (Recency, Frequency, Monetary) segmentation — and why does the latter predict revenue better?",
            "The 'At-Risk High-Value' segment has 380 customers who used to buy 3x/year but are now at 0.8x. What is the revenue at stake — and what is the urgency of acting on this segment?",
            "Design a specific marketing strategy for each of the 5 segments — different objectives, different messages, different offers.",
            "The Champions segment (420 customers) generates 45% of revenue. What specific risks exist with this concentration — and what does a healthy customer base distribution look like?",
        ]),
        'solution_en' => "## Behavioural Segmentation: The Revenue Map Hidden in Your Data\n\n### Demographic vs. RFM Segmentation\n\n**Demographic segmentation** groups customers by who they are: age 35–45, Cairo, female, high income. It is useful for media targeting but tells you almost nothing about likelihood to buy again.\n\n**RFM segmentation** groups customers by what they do:\n- **R**ecency — how recently did they last buy?\n- **F**requency — how often do they buy?\n- **M**onetary — how much do they spend?\n\nRFM predicts future purchase behaviour because past behaviour is the best predictor of future behaviour. A customer who bought 3 days ago is 10× more likely to buy again than one who bought 6 months ago — regardless of demographics.\n\n### The At-Risk High-Value Emergency\n\n380 customers, average annual spend EGP 780, currently buying at 0.8x/year vs. previous 3x/year.\n\n**Revenue at stake:**\n- Current annual revenue from segment: EGP 780 × 0.8 × 380 = EGP 237,000\n- Previous annual revenue: EGP 780 × 3 × 380 = EGP 889,000\n- **Revenue gap: EGP 652,000/year**\n\nIf these 380 customers fully churn, the company loses EGP 889,000 that cost zero to acquire (already acquired customers). This is the highest-ROI marketing intervention available.\n\n### Segment-Specific Strategies\n\n**Champions (420 customers, EGP 8.1M revenue)**\nObjective: Protect and deepen the relationship\n- Personal outreach: quarterly call or WhatsApp message from a named account manager — not a marketing template\n- Exclusive access: first to know about new arrivals, pre-order option before public launch\n- VIP programme: free delivery always, annual 'Gourmet Box' gift\n- Do NOT discount — they are price-insensitive loyalists; discounting cheapens the relationship\n\n**Loyal Regulars (1,100 customers, EGP 5.4M)**\nObjective: Increase frequency and basket size\n- Monthly 'New Arrival' curated email with personal recommendation: 'Based on your love of French cheese, we think you'll love this Comté'\n- Upsell: bundle recommendations ('Pair your usual cheese order with this charcuterie at 10% off')\n- Nudge to Champion tier: 'You're 2 orders away from VIP status'\n\n**At-Risk High-Value (380 customers, EGP 2.2M at current rate)**\nObjective: Win-back immediately — 48-hour intervention\n- Personal WhatsApp from a human (not automated): 'We've missed you — it's been [X] months since your last order. Is there anything we got wrong?'\n- Listen first. Understand why they stopped. Common reasons: found a competitor, life change, product availability issue\n- Win-back offer: 'Welcome back' box at 20% off, with a new product they haven't tried\n- This segment is worth 3× the discount it costs to recover them\n\n**One-Time Buyers (6,800 customers, EGP 2.1M)**\nObjective: Convert to second purchase (the conversion from 1st to 2nd purchase is the most predictive of long-term retention)\n- Automated 14-day post-purchase sequence: 'How was your order?' → 'Customers who loved [product X] also ordered [product Y]' → 'Ready for your next order? Here's 10% off'\n- Segment by product purchased: a cheese buyer gets cheese follow-up, not charcuterie\n\n**New Customers (2,500 customers)**\nObjective: Drive first repeat purchase within 30 days\n- Day 3: WhatsApp satisfaction check\n- Day 7: 'New arrivals this week'\n- Day 21: 'Haven't seen you back — here's 10% off your second order'\n- Day 30: 'Did you know we offer [subscription / weekly box]?'",

        'bigger_lesson_en' => "**Revenue is not distributed evenly across your customer base — and your marketing investment should not be either.**\n\nThe single most impactful marketing decision a company can make is to understand who their most valuable customers are, treat them accordingly, and design every other segment strategy around protecting and growing that core.",

        'skills_taught_en' => json_encode([
            'RFM customer segmentation',
            'Behavioural vs. demographic segmentation',
            'Segment-specific marketing strategy',
            'Customer concentration risk',
            'Win-back campaign design',
        ]),

        'title_ar'    => 'الشريحة التي لم يتحدث إليها أحد',
        'subtitle_ar' => 'كيف يُطلق التجزئة السلوكية إيرادات يفوتها التجزئة الديموغرافية',
        'situation_ar' => "أنت CMO في **الماظة للأطعمة الفاخرة**، شركة مصرية للأغذية الراقية. قاعدة عملائك: 11,200 عميل. التسويق يقسّمهم ديموغرافياً: العمر والموقع والجنس.\n\nمحلل بيانات ينضم للفريق ويبني نموذج تجزئة سلوكي:\n\n| الشريحة | الحجم | الإيرادات السنوية | تكرار الشراء |\n|---|---|---|---|\n| الأبطال | 420 | 8.1 مليون | 8.2 مرة/سنة |\n| الأوفياء المنتظمون | 1,100 | 5.4 مليون | 4.1 مرة |\n| عالي القيمة في خطر | 380 | 2.2 مليون | كان 3 مرات، الآن 0.8 |\n| مشترون مرة واحدة | 6,800 | 2.1 مليون | مرة |\n\nيقول المحلل: *'الشركة تعامل 11,200 عميل بنفس الطريقة، رغم أن 3.7% منهم يولّدون 45% من الإيرادات.'*",
        'challenge_questions_ar' => json_encode([
            "ما الفرق بين التجزئة الديموغرافية وتجزئة RFM — ولماذا يتنبأ الأخير بالإيرادات بشكل أفضل؟",
            "شريحة 'عالي القيمة في خطر' بها 380 عميلاً اعتادوا الشراء 3 مرات/سنة لكنهم الآن عند 0.8 مرة. ما الإيرادات المعرضة للخطر وما إلحاحية التصرف؟",
            "صمم استراتيجية تسويق محددة لكل شريحة من الخمس.",
            "شريحة الأبطال (420 عميلاً) تولّد 45% من الإيرادات. ما المخاطر المحددة لهذا التركز؟",
        ]),
        'solution_ar' => "## التجزئة السلوكية: خريطة الإيرادات المخفية في بياناتك\n\n### الإيرادات في خطر من الشريحة عالية القيمة\n\n**الفجوة السنوية: 652,000 جنيه** (الإيرادات السابقة 889,000 جنيه - الحالية 237,000 جنيه)\n\nإذا رحل هؤلاء الـ380 بالكامل، تخسر الشركة 889,000 جنيه استقطابها لم يكلف شيئاً.\n\n### الاستراتيجيات المحددة لكل شريحة\n\n**الأبطال:** تواصل شخصي، وصول حصري، هدايا VIP — لا خصومات أبداً\n**الأوفياء المنتظمون:** توصيات مخصصة، حزم لزيادة السلة، دفع نحو مستوى VIP\n**عالي القيمة في خطر:** واتساب شخصي من إنسان في 48 ساعة، استمع أولاً، عرض استعادة 20%\n**مشترو مرة واحدة:** تسلسل بريد تلقائي 14 يوماً لتحويل الشراء الثاني\n**الجدد:** فحص رضا يوم 3، خصم 10% يوم 21",

        'bigger_lesson_ar' => "**الإيرادات لا تتوزع بالتساوي على قاعدة عملائك — واستثمارك التسويقي لا يجب أن يكون كذلك.**\n\nأكثر قرار تسويقي مؤثر يمكن أن تتخذه شركة هو فهم من هم أكثر عملائها قيمة، معاملتهم وفق ذلك، وتصميم كل استراتيجية شريحة أخرى حول حماية تلك القاعدة وتنميتها.",

        'skills_taught_ar' => json_encode([
            'تجزئة عملاء RFM',
            'التجزئة السلوكية مقابل الديموغرافية',
            'استراتيجية تسويق مخصصة للشرائح',
            'مخاطر تركز العملاء',
            'تصميم حملات الاستعادة',
        ]),
        'created_at' => $now,
        'updated_at' => $now,
    ],

        // ── MG-CAR-06 ─────────────────────────────────────────────────
    [
        'hub_id'        => $hubId,
        'created_by'    => $adminId,
        'topic_slug'    => 'customer_acquisition_retention',
        'topic_name_en' => 'Customer Acquisition & Retention',
        'topic_name_ar' => 'استقطاب العملاء والاحتفاظ بهم',
        'layer'         => 'generic',
        'difficulty'    => 'advanced',
        'status'        => 'published',
        'published_at'  => $now,
        'sort_order'    => 706,
        'read_time_minutes' => 14,

        'title_en'    => 'The NPS Score Nobody Acted On',
        'subtitle_en' => 'Turning customer satisfaction data into a retention and growth engine',
        'situation_en' => "You are the head of marketing at **Tariq Telecom Services**, an Egyptian B2B telecommunications company providing dedicated internet, VoIP, and cloud connectivity to corporate clients. The company has 820 active clients.\n\nEighteen months ago, on the advice of a consultant, the company launched a quarterly NPS survey. Results have come in consistently:\n\n| Quarter | NPS Score | Promoters | Passives | Detractors | Response Rate |\n|---|---|---|---|---|---|\n| Q1 | +12 | 38% | 36% | 26% | 61% |\n| Q2 | +14 | 39% | 36% | 25% | 58% |\n| Q3 | +11 | 37% | 38% | 25% | 54% |\n| Q4 | +13 | 38% | 37% | 25% | 51% |\n| Q5 | +12 | 38% | 36% | 26% | 48% |\n| Q6 | +13 | 38% | 37% | 25% | 46% |\n\nThe NPS is flat. The response rate is declining. The same 25% are detractors every quarter.\n\nThe CEO says: *'We've been doing NPS for 18 months and nothing has improved. Either the methodology is wrong or we're using it wrong.'*",
        'challenge_questions_en' => json_encode([
            "NPS has been flat for 6 quarters. What does this tell you about how the company has been using the data — and what is missing from their process?",
            "25% detractors for 6 consecutive quarters. What happens to those detractors if the company never follows up with them?",
            "The response rate has dropped from 61% to 46% in 6 quarters. What does a declining response rate signal — and what causes it?",
            "Design a closed-loop NPS system that turns survey data into concrete retention actions, revenue growth from promoters, and measurable improvement over time.",
        ]),
        'solution_en' => "## NPS: The Measurement Tool That Only Works If You Close the Loop\n\n### What Flat NPS for 6 Quarters Reveals\n\nFlat NPS does not mean the company is consistently good. It means the company is measuring consistently but not acting. The score stays flat because:\n- Detractors are not being contacted — their issues persist\n- Passives are not being converted — no effort to understand what would make them promoters\n- Promoters are not being activated — no referral or expansion programme\n\nNPS is a **diagnostic tool**, not a reporting tool. Collecting the score and filing the report is not using NPS — it is performing NPS.\n\n### What Happens to Unresponded Detractors\n\nA detractor who scores you 0–6 and never receives a follow-up goes through a predictable cycle:\n1. They assumed the survey was just for data collection — their score sent a message nobody cared to receive\n2. Their dissatisfaction hardens into resolved churn — they are now actively looking for an alternative\n3. When their contract renews, they switch. Or they tell colleagues at other companies about their experience\n\n**In B2B, one detractor can influence 3–8 potential customers through industry networks.** The 25% of 820 clients = 205 detractors per quarter, all of whom are unharvested churn risk and negative word-of-mouth.\n\n### Why Response Rate Is Declining\n\nWhen a survey is sent repeatedly and the respondent sees no change, the implicit message is: *'We collect your feedback and do nothing with it.'*\n\nRespondents eventually stop responding to surveys they believe are performative. The decline from 61% to 46% is a signal that the survey audience has lost faith that it matters.\n\n### The Closed-Loop NPS System\n\n**The loop has three tracks:**\n\n**Detractor Track (0–6 score)**\n- Day 1: Automated acknowledgment: 'Thank you for your honest feedback. A senior team member will contact you within 48 hours.'\n- Day 2: Account manager calls. Script: 'I'm calling because you told us something was not working. I want to understand it and fix it — not to defend us.'\n- Action taken: documented. Root cause logged.\n- Follow-up in 30 days: 'We changed [X] based on your feedback. Has this improved your experience?'\n- Target: 40% of detractors converted to passives within 90 days\n\n**Passive Track (7–8 score)**\n- Not emergency, but not ignored\n- Automated email: 'You gave us a 7. What would make us a 9 or 10 for you?'\n- Open text responses reviewed monthly by marketing and product\n- Passives converted to promoters through feature announcements, service improvements, or proactive account reviews\n- Target: 20% conversion to promoters per quarter\n\n**Promoter Track (9–10 score)**\n- Immediately activated for referrals: 'You gave us a 10! We'd love to ask a favour — would you be willing to introduce us to one company that might benefit from our services?'\n- Case study invitation: 'Would you share your experience with us in a short testimonial?'\n- Target: 15% of promoters generate at least one referral per quarter\n\n**The NPS Review Meeting (quarterly)**\nThe NPS score is presented to the leadership team alongside:\n- Number of detractors contacted vs. issues resolved\n- Number of passives converted\n- Number of referrals from promoters\n- **The only metric that matters: did the score improve because of actions we took?**",

        'bigger_lesson_en' => "**NPS is not a score — it is a conversation starter.**\n\nThe value of the survey is zero until someone picks up the phone and calls the people who gave you a 3. The companies that move NPS from +12 to +40 do not do better surveys — they build operational processes that systematically respond to every piece of feedback and close every loop.",

        'skills_taught_en' => json_encode([
            'Closed-loop NPS system design',
            'Detractor recovery programmes',
            'Promoter activation for referrals',
            'Customer satisfaction survey best practices',
            'NPS as a retention and growth tool',
        ]),

        'title_ar'    => 'درجة NPS التي لم يتصرف أحد بناءً عليها',
        'subtitle_ar' => 'تحويل بيانات رضا العملاء إلى محرك احتفاظ ونمو',
        'situation_ar' => "أنت رئيس التسويق في **طارق للاتصالات**، شركة مصرية B2B للاتصالات تخدم 820 عميلاً مؤسسياً. قبل 18 شهراً أطلقت استطلاع NPS ربع سنوي.\n\n| الربع | درجة NPS | المروّجون | السلبيون | المعارضون | معدل الاستجابة |\n|---|---|---|---|---|---|\n| R1 | +12 | 38% | 36% | 26% | 61% |\n| R6 | +13 | 38% | 37% | 25% | 46% |\n\nدرجة NPS ثابتة. معدل الاستجابة ينخفض. نفس الـ25% معارضون كل ربع.\n\nيسأل الرئيس: *'نقوم بـNPS منذ 18 شهراً ولم يتحسن شيء. إما المنهجية خاطئة أو نستخدمها بشكل خاطئ.'*",
        'challenge_questions_ar' => json_encode([
            "NPS ثابتة لـ6 أرباع. ما الذي يخبرك هذا عن كيفية استخدام الشركة للبيانات — وما المفقود في العملية؟",
            "25% معارضون لـ6 أرباع متتالية. ما الذي يحدث لهم إذا لم تتابع معهم الشركة أبداً؟",
            "انخفض معدل الاستجابة من 61% إلى 46% في 6 أرباع. ما الذي يُشير إليه هذا الانخفاض؟",
            "صمم نظام NPS مغلق الحلقة يحوّل بيانات الاستطلاع إلى إجراءات احتفاظ ونمو ملموسة.",
        ]),
        'solution_ar' => "## NPS: أداة القياس التي تعمل فقط إذا أغلقت الحلقة\n\n### ما يحدث للمعارضين غير المُجاب عليهم\n\nمعارض يمنح 0-6 ولا يتلقى متابعة:\n1. افترض أن الاستطلاع لجمع البيانات فقط — درجته أرسلت رسالة لم يهتم أحد باستقبالها\n2. عدم رضاه يتصلّب إلى قرار رحيل — يبحث الآن عن بديل\n3. عند تجديد العقد، يتحول إلى منافس\n\n**في B2B، معارض واحد يمكنه التأثير في 3-8 عملاء محتملين عبر الشبكات الصناعية.**\n\n### نظام NPS مغلق الحلقة\n\n**مسار المعارضين:** تواصل 48 ساعة، فهم المشكلة، توثيق الإجراء، متابعة بعد 30 يوماً\n**مسار السلبيين:** سؤال مفتوح 'ما الذي يجعلنا 9-10؟'، مراجعة شهرية\n**مسار المروّجين:** تفعيل فوري للإحالات، دعوة دراسة حالة",

        'bigger_lesson_ar' => "**NPS ليست درجة — إنها بداية محادثة.**\n\nقيمة الاستطلاع تساوي صفراً حتى يرفع أحد الهاتف ويتصل بمن منحه درجة 3. الشركات التي ترفع NPS من +12 إلى +40 لا تجري استطلاعات أفضل — بل تبني عمليات تنظيمية تستجيب بشكل ممنهج لكل ملاحظة وتُغلق كل حلقة.",

        'skills_taught_ar' => json_encode([
            'تصميم نظام NPS مغلق الحلقة',
            'برامج استعادة المعارضين',
            'تفعيل المروّجين للإحالات',
            'أفضل ممارسات استطلاعات رضا العملاء',
            'NPS كأداة احتفاظ ونمو',
        ]),
        'created_at' => $now,
        'updated_at' => $now,
    ],

    // ══════════════════════════════════════════════════════════════
    //  TOPIC 8 — MARKETING ANALYTICS (MG-MA-01 to MG-MA-06)
    // ══════════════════════════════════════════════════════════════,

        // ── MG-MA-01 ──────────────────────────────────────────────────
    [
        'hub_id'        => $hubId,
        'created_by'    => $adminId,
        'topic_slug'    => 'marketing_analytics',
        'topic_name_en' => 'Marketing Analytics & Reporting',
        'topic_name_ar' => 'تحليلات التسويق والتقارير',
        'layer'         => 'generic',
        'difficulty'    => 'beginner',
        'status'        => 'published',
        'published_at'  => $now,
        'sort_order'    => 801,
        'read_time_minutes' => 10,

        'title_en'    => 'The Marketing Report Nobody Understood',
        'subtitle_en' => 'Building reports that executives can use — not just data that analysts can read',
        'situation_en' => "You are a marketing analyst at **Raya Group Services**, an Egyptian holding company. Each month you submit a marketing performance report to the CEO and the board. The report currently includes:\n\n- 14 pages of charts\n- Metrics: impressions, reach, CPM, CPC, CTR, engagement rate, follower growth, email open rate, click rate, bounce rate, time on site, organic vs. paid sessions, and 12 more\n- Data from 6 platforms: Facebook, Instagram, Google Ads, LinkedIn, Email, and the website\n- Commentary: 'Performance was generally in line with expectations'\n\nAfter the last board meeting, the CEO pulls you aside: *'I read the report every month and I still can't tell you whether our marketing is working or not. What are we actually trying to achieve?'*",
        'challenge_questions_en' => json_encode([
            "The CEO cannot tell whether marketing is working from a 14-page report. What is wrong with the report's structure — and what is the CEO actually trying to understand?",
            "List the difference between 'activity metrics' (what we did), 'output metrics' (what happened), and 'outcome metrics' (what it achieved for the business) — with examples from the current report.",
            "Design a one-page marketing dashboard that answers the CEO's question directly — with no more than 8 metrics.",
            "What is a 'North Star Metric' for a marketing function — and how do you choose the right one for a diversified holding company?",
        ]),
        'solution_en' => "## Marketing Reporting: The Purpose Is the Audience\n\n### What Is Wrong With a 14-Page Report\n\nA 14-page report with 26 metrics is written for the analyst who collected the data — not for the executive who needs to make decisions. The problems:\n\n1. **No hierarchy** — all metrics appear equally important\n2. **No interpretation** — data without context ('reach was 450,000') means nothing without the question 'is that good or bad, and why?'\n3. **No action signal** — the reader cannot tell what to do differently\n4. **Platform-centric** — organises by data source (Facebook, Google), not by business question\n5. **Activity focus** — most metrics measure what the team did, not what the business achieved\n\n### Activity vs. Output vs. Outcome Metrics\n\n| Type | What It Measures | Example |\n|---|---|---|\n| Activity | What the team did | 'Published 23 posts this month' |\n| Output | What the activity produced | 'Posts reached 180,000 people' |\n| Outcome | What the business achieved | 'Marketing generated 47 qualified leads' |\n\nOutcome metrics are what the CEO needs. Activity and output metrics belong in an appendix or operational dashboard — not the executive report.\n\n### The One-Page Executive Dashboard\n\n| Section | Metric | This Month | Last Month | Target | Status |\n|---|---|---|---|---|---|\n| **Revenue Impact** | Leads generated from marketing | 58 | 49 | 60 | 🟡 |\n| | Revenue from marketing-attributed clients | EGP 890K | EGP 720K | EGP 900K | 🟡 |\n| **Acquisition** | Cost per qualified lead | EGP 340 | EGP 390 | < EGP 350 | 🟢 |\n| | Top performing channel (this month) | Google Search | — | — | — |\n| **Retention** | Active customer retention rate | 91% | 89% | > 90% | 🟢 |\n| **Brand Health** | NPS score (quarterly) | +18 | +14 | > +20 | 🟡 |\n| **Budget** | Marketing spend vs. budget | EGP 280K | EGP 265K | EGP 300K | 🟢 |\n| | ROAS (blended) | 3.2× | 2.9× | > 3.0× | 🟢 |\n\nThis dashboard answers the CEO's question in under 2 minutes.\n\n### Choosing a North Star Metric\n\nA North Star Metric (NSM) is the single most important metric that captures the core value marketing delivers to the business. It must be:\n- An outcome, not an activity\n- Directly linked to business growth\n- Understandable to non-marketers\n\nFor a diversified holding company, the NSM might be: **'Qualified leads generated per month'** or **'Revenue attributed to marketing per quarter.'**\n\nEvery marketing decision, budget allocation, and campaign is evaluated by: *'Does this move the North Star Metric?'*",

        'bigger_lesson_en' => "**A marketing report that nobody can act on is not a report — it is a document.**\n\nThe purpose of a report is to enable decisions. If the CEO reads 14 pages and cannot answer 'is marketing working?', the report has failed its only job. Simplify to the essential, interpret the data, and always answer the question the reader is actually trying to answer.",

        'skills_taught_en' => json_encode([
            'Executive marketing dashboard design',
            'Activity vs. outcome metrics',
            'North Star Metric selection',
            'Marketing reporting best practices',
            'Data-to-decision communication',
        ]),

        'title_ar'    => 'تقرير التسويق الذي لم يفهمه أحد',
        'subtitle_ar' => 'بناء تقارير يستطيع المديرون التنفيذيون استخدامها — لا مجرد بيانات يستطيع المحللون قراءتها',
        'situation_ar' => "أنت محلل تسويق في **مجموعة راية للخدمات**. تُقدّم شهرياً تقرير أداء تسويقي يتضمن 14 صفحة من المخططات و26 مقياساً من 6 منصات.\n\nبعد آخر اجتماع مجلس إدارة، يسحبك الرئيس التنفيذي جانباً: *'أقرأ التقرير كل شهر ولا أستطيع إخبارك ما إذا كان تسويقنا يعمل أم لا. ما الذي نحاول تحقيقه فعلاً؟'*",
        'challenge_questions_ar' => json_encode([
            "لا يستطيع الرئيس التنفيذي تحديد ما إذا كان التسويق يعمل من تقرير 14 صفحة. ما الخطأ في بنية التقرير؟",
            "أدرج الفرق بين 'مقاييس النشاط' و'مقاييس الإخراج' و'مقاييس النتائج' — مع أمثلة من التقرير الحالي.",
            "صمم لوحة معلومات تسويقية في صفحة واحدة تجيب على سؤال الرئيس مباشرةً — بلا أكثر من 8 مقاييس.",
            "ما 'المقياس القطبي الشمالي' للوظيفة التسويقية — وكيف تختار الصحيح لشركة قابضة متنوعة؟",
        ]),
        'solution_ar' => "## تقارير التسويق: الغرض هو الجمهور\n\n### مقاييس النشاط مقابل الإخراج مقابل النتائج\n\n| النوع | ما يقيسه | مثال |\n|---|---| ---|\n| نشاط | ما فعله الفريق | 'نشرنا 23 منشوراً' |\n| إخراج | ما أنتجه النشاط | 'المنشورات وصلت 180,000 شخص' |\n| نتائج | ما حققه العمل | 'التسويق ولّد 47 عميلاً مؤهلاً' |\n\n### لوحة المعلومات التنفيذية في صفحة واحدة\n\n| القسم | المقياس | هذا الشهر | الهدف | الحالة |\n|---|---|---|---|---|\n| **الأثر على الإيرادات** | العملاء المحتملون من التسويق | 58 | 60 | 🟡 |\n| **الاستقطاب** | تكلفة العميل المؤهَّل | 340 جنيه | <350 | 🟢 |\n| **الاحتفاظ** | معدل الاحتفاظ | 91% | >90% | 🟢 |\n| **الميزانية** | ROAS المدمج | 3.2× | >3.0× | 🟢 |",

        'bigger_lesson_ar' => "**تقرير التسويق الذي لا يستطيع أحد التصرف بناءً عليه ليس تقريراً — بل وثيقة.**\n\nغرض التقرير هو تمكين القرارات. إذا قرأ الرئيس التنفيذي 14 صفحة ولم يستطع الإجابة على 'هل التسويق يعمل؟'، فشل التقرير في مهمته الوحيدة.",

        'skills_taught_ar' => json_encode([
            'تصميم لوحة المعلومات التسويقية التنفيذية',
            'مقاييس النشاط مقابل النتائج',
            'اختيار المقياس القطبي الشمالي',
            'أفضل ممارسات التقارير التسويقية',
            'التواصل من البيانات إلى القرار',
        ]),
        'created_at' => $now,
        'updated_at' => $now,
    ],

        ]; // end file 09

        DB::table('cases')->insert($cases);
        $this->command->info('  ✅ 5 cases inserted (MG-CAR-03 to MG-CAR-06, MG-MA-01)');
    }
}
