Project: Okul Database · Hub: Okul Database — Guides

Data Scientist Quick Start Guide

Everything you need to start querying the Okul.com.tr database. Read this first.

---

What Is This Platform?

Okul.com.tr is Turkey’s K-12 school directory and B2B marketplace. Two channels:

Channel	Who	What They Do
D2C	Parents and students (users table)	Search for schools, submit inquiry forms (leads)
B2B	School management firms (customers table)	Purchase product subscriptions, manage school profiles

The same database serves both. Key distinction: users.customer_id IS NOT NULL = the user is a B2B portal member.

---

The 7 Tables You’ll Use Most

Table	Why	Key Filter
school_filters	School analytics — 100+ cols, no join needed	deleted_at IS NULL
leads	Lead pipeline, conversion funnel	deleted_at IS NULL AND (is_test=0 OR is_test IS NULL)
schools	Core entity when you need the source of truth	deleted_at IS NULL AND status='1'
customer_agreements	Revenue, contracts	deleted_at IS NULL AND status='active'
payments	Financial transactions	deleted_at IS NULL AND status='success' AND paid_at IS NOT NULL
users	User demographics and activity	deleted_at IS NULL AND is_active='1'
school_interactions	CRM activity log	deleted_at IS NULL AND is_latest=1

---

5 Rules That Will Save You

Rule 1: ALWAYS filter soft deletes

WHERE deleted_at IS NULL

Almost every table has this column. Missing it means silently including deleted records.

Rule 2: Use school_filters, not raw schools

-- BAD: joining 10 tables for basic school attributes
SELECT s.name, st.type, l.name as city, p.name as product ...
FROM schools s JOIN schools_types st ... JOIN locations l ... JOIN products p ...
 
-- GOOD: everything already there
SELECT name, school_type_name, city_name, product_name
FROM school_filters WHERE deleted_at IS NULL

Rule 3: Exclude test leads

WHERE is_test = 0 OR is_test IS NULL

Test leads inflate all lead counts. Always exclude.

Rule 4: Know what “converted” means

-- A lead is enrolled when:
stage_id = 11    -- "Kayıt" (Enrolled)
OR stage_id = 38 -- "Manuel Kayıt" (Manual Enrollment)
OR school_answered_deal_status = 2453  -- "Kayıt gerçekleşti"

See ref-lead-stages for all stage meanings.

Rule 5: Revenue = status='success' payments only

WHERE status = 'success' AND paid_at IS NOT NULL

There are 10 payment status values. Only success = actual money collected.

---

Domain Map — Where to Look

Question	Table	Reference
How many active schools?	school_filters WHERE is_active=1	table-school-filters
Which schools are paying customers?	school_filters WHERE is_customer=1	table-school-filters
Lead count / conversion rate?	leads	table-leads + ref-lead-stages
Monthly revenue?	payments WHERE status='success'	table-payments
Active contracts?	customer_agreements WHERE status='active'	table-customer-agreements
User registrations?	users	table-users
School engagement over time?	school_daily_logs	table-school-daily-logs
CRM activity?	school_interactions WHERE is_latest=1	table-school-interactions
Why did deals fail?	leads.deal_cause_of_reason	ref-interaction-values
School rating/sentiment?	school_comments	domain-schools
Geographic breakdown?	school_filters.city_name/county_name	ref-locations
School type breakdown?	school_filters.school_type_id	ref-school-types
Private vs public schools?	school_filters.sector_id (1=public, 2=private)	ref-sectors
What facilities does a school have?	school_facilities JOIN facilities	ref-services-facilities
University programs?	universities → faculties → faculty_programs	domain-content-education
Turkish province names?	school_filters.city_name (no join needed)	ref-locations
What does config ID 1 mean?	configs WHERE type=? AND item_id=1	ref-configs

---

Turkey-Specific Context

Term	Meaning
Devlet (sector_id=1)	Public / state-funded school
Özel (sector_id=2)	Private school
Anaokulu (school_type_id=1)	Kindergarten, ages 3-6
İlkokul (school_type_id=2)	Primary school, grades 1-4
Ortaokul (school_type_id=3)	Middle school, grades 5-8
Lise (school_type_id=4)	High school, grades 9-12
YÖK	Yükseköğretim Kurulu — Turkish higher education authority
IYS	İleti Yönetim Sistemi — Turkey’s email/SMS consent registry (like GDPR)
KVKK	Turkish GDPR equivalent (users.kvkk_accepted_at)
ERP	LOGO ERP — accounting system used by B2B customers
Pipedrive	External CRM synced to pipedrive_* tables; has lag
PY	Internal project management system; py_profile_point = profile quality score

---

JSON Columns — Handle With Care

Table	Column	Contents
customer_agreements	products	JSON array of purchased products
customer_agreements	product_arge	R&D product details
customer_agreements	missing_inputs	List of missing contract fields
notifications	options	Notification configuration
payment_transactions	gateway_response	Raw iyzico/bank API response
pipedrive_deals	details	Raw Pipedrive deal JSON

-- Parse JSON (MySQL 5.7+ required)
SELECT JSON_UNQUOTE(JSON_EXTRACT(products, '$[0].name')) FROM customer_agreements;

---

Timestamp Patterns

Most tables follow this pattern:

Column	Meaning
created_at	Record creation
updated_at	Last modification
deleted_at	Soft delete timestamp (NULL = not deleted)
paid_at	Payment completion (NULL = not yet paid)
customer_viewed_at	When B2B customer first viewed a lead (NULL = unseen)
approved_at	Approval timestamp (comments, agreements)

---

Common Gotchas Summary

Full list: guide-common-gotchas

Gotcha	Fix
Forgetting deleted_at IS NULL	Add to every query
Using schools with 20 joins	Use school_filters instead
Including test leads	Add is_test = 0 OR is_test IS NULL
Treating Pipedrive tables as real-time	They have sync lag; use leads for live data
source_action being NULL before April 2026	Filter by created_at >= '2026-04-02' when needed
configs FK resolution	Join on type + item_id, not just item_id

---

Related

• guide-common-gotchas — Extended gotcha list
• guide-key-relationships — Table relationship map
• ref-lead-stages — All lead stage IDs decoded
• ref-interaction-values — All config-backed lookup values
• ref-payment-statuses — Payment status enum decoded