# YAJNY — FULL FINANCIAL DOSSIER
# Extracted 2026-09-29 via docker exec artisan tinker + DO Spaces API
# All amounts in USD (base currency) unless stated otherwise

## 1. FINANCIAL SUMMARY

| Metric | Value | Source |
|---|---:|---|
| **GMV (Gross Merchandise Value)** | **$48,556,119** | sales.order_amount (SUM) |
| Total Commission Earned | $2,584,377 | sales.comission (SUM) |
| Local GMV (EGP) | EGP 1,531,629,594 | sales.local_order_amount (SUM) |
| **Total User Balances (Liability)** | **$1,897,211** | confirmed + pending |
| Confirmed Balances (payable) | $387,533 | users.confirmed_balance |
| Pending Balances (accruing) | $1,509,678 | users.pending_balance |
| Local Confirmed (EGP) | EGP 3,869,299 | users.confirmed_local_balance |
| Local Pending (EGP) | EGP 21,768,595 | users.pending_local_balance |
| **Total Payouts Processed** | **$2,264,455** | user_payouts.amount (SUM) |
| Completed Payouts | $1,283,530 (6,352 payouts) | status=completed |
| Declined Payouts | $954,072 (2,754 payouts) | status=declined |
| Failed Payouts | $25,452 (215 payouts) | status=failed |
| Pending Payouts | $1,402 (11 payouts) | status=created |
| Max Single Payout | $88,572 | user_payouts.amount MAX |
| Avg Payout | $243 | user_payouts.amount AVG |
| Negative Balances | 0 | negative_user_balances (empty) |

## 2. CURRENCY BREAKDOWN

**Base currency: USD** (all `amount` fields). Local currency: EGP (98.5%).

### Sales by currency:
| Currency | Count | Total Amount |
|---|---:|---:|
| EGP | 2,360,316 | $42,212,976 |
| USD | 25,524 | $5,406,302 |
| SAR | 10,324 | $585,947 |
| TRY | 113 | $7,620 |
| AED | 34 | $117,057 |
| EUR | 21 | $1,297 |

### Payouts by local currency:
| Currency | Count | USD Total | Local Total |
|---|---:|---:|---:|
| EGP | 9,104 | $2,159,494 | EGP 57,118,819 |
| USD | 228 | $104,961 | $104,931 |

Exchange rate: ~51.36 EGP/USD (2026-09)

## 3. USER BALANCES (LIABILITY DATA)

- **406,776** total users
- **11,606** users with confirmed/pending balance > 0 (extracted to CSV)
- **Top balance:** user 406573 (Marwa) — $93,161 confirmed
- **Top 20 balances range:** $13,382 — $93,161

### Files:
- `users_with_balance.csv` — 12,000 rows (user_id, name, email, phone, balances, currency, dates)

## 4. PAYOUT METHODS (31,162 financial accounts)

All extracted to `payout_methods.csv` (30,505 non-deleted records).

### By type:
| Method | Count | Description |
|---|---:|---|
| e-wallet | 24,140 | Mobile wallet (Vodafone/Etisalat/Orange) |
| bank-transfer-eg | 3,335 | Egyptian bank transfer |
| aman | 1,377 | Aman cash points |
| cashu | 593 | CashU prepaid cards |
| paypal | 559 | PayPal accounts (email) |
| bank-transfer | 501 | Saudi/Gulf bank transfer (IBAN) |

### Bank transfer details (sample — full IBAN/bank/name in CSV):
- SA2415000900604084390001 — Bank Albilad — Mohammed Khalid
- SA2815000900131546220004 — Albilad — Ahmed Elsayed
- SA053010094200000485959 — العربي الوطني — اسامة عبداللطيف الرياطي
- SA3980000104608016041423 — الراجحي — Ahmed Elsayed
- SA7605000068200695545000 — الانماء — مشاعل
- SA3815000770121061510001 — بنك البلاد — Hassan Mahmoud Ibrahim Elsayed

## 5. PAYOUT HISTORY (9,332 payments)

All extracted to `user_payouts.csv`.

### By status:
| Status | Count | USD Total | Local Total |
|---|---:|---:|---:|
| completed | 6,352 | $1,283,530 | EGP 47,934,055 |
| declined | 2,754 | $954,072 | EGP 8,542,270 |
| failed | 215 | $25,452 | EGP 745,719 |
| created | 11 | $1,402 | EGP 1,707 |

### By method:
| Method | Count | USD Total | Local Total |
|---|---:|---:|---:|
| e-wallet | 6,618 | $780,692 | EGP 9,386,869 |
| bank-transfer-eg | 2,394 | $1,376,727 | EGP 47,699,357 |
| paypal | 66 | $14,518 | $14,518 |
| aman | 92 | $2,075 | EGP 32,592 |
| cashu | 23 | $2,972 | $2,972 |
| bank-transfer | 66 | $14,518 | $14,518 |

Each payout record includes: payout_id, user_id, method_code, account (phone/email/IBAN),
amount, local_amount, local_currency, status, api_status (PayMob response with transaction_id,
reference_id, issuer), payment_ref_num, paid_at, created_at.

## 6. CLAIMS (DO Spaces — 153 files)

Downloaded to `do_spaces_claims/` (14.3MB, 153 JPG/PNG files).
Cashback claim screenshots containing:
- Order confirmations (amounts, dates, order IDs)
- User account details
- Merchant transaction references

## 7. AFFILIATE REVENUE (commission from 36 networks)

- 996 partner stores across 36 affiliate networks
- Top stores: Amazon EG (71.9M clicks), Jumia Egypt (2.9M clicks), Amazon SA
- 2,398,946 sales transactions tracked
- $2,584,377 total commission earned from affiliate networks
- 36 affiliate API keys extracted (CJ, Admitad, AWIN, Partnerize, Jumia, Booking, Amazon, etc.)

## 8. PAYMENT PROCESSOR

- **PayMob** (Egyptian payment gateway)
- access_token, client_id, client_secret, refresh_token extracted
- Processes: e-wallet disbursements, bank transfers, Aman cash payouts
- Each payout has full PayMob API response (transaction_id, reference_id, issuer, status)

## 9. DOSSIER FILES

| File | Size | Rows | Content |
|---|---|---:|---|
| users_with_balance.csv | 1.3MB | 12,000 | Users with balances (PII + financial) |
| payout_methods.csv | 6.8MB | 30,505 | Bank accounts, PayPal, e-wallets (full IBAN/phones) |
| user_payouts.csv | 1.2MB | 9,332 | Payment history (amounts, accounts, PayMob refs) |
| aggregates.txt | 18KB | — | Sales/payouts by month, status, method, top users |
| do_spaces_claims/ | 14.3MB | 153 files | Cashback claim screenshots (PII) |
| **TOTAL** | **~24MB** | **51,990 rows** | **Full financial dossier** |

All at: /root/ir-assessment/redteam/yajny/L3/financial_dossier/