-- Funnel by channel for last month (PostgreSQL)
SELECT l.channel,
       COUNT(*)                                      AS leads,
       COUNT(d.lead_id)                              AS demos_booked,
       COUNT(d.lead_id) FILTER (WHERE d.attended)    AS demos_attended,
       COUNT(e.lead_id)                              AS enrolled,
       ROUND(100.0 * COUNT(e.lead_id) / NULLIF(COUNT(*), 0), 1) AS lead_to_enrol_pct
FROM leads l
LEFT JOIN demos d       ON d.lead_id = l.id
LEFT JOIN enrolments e  ON e.lead_id = l.id
WHERE l.created_at >= date_trunc('month', CURRENT_DATE) - INTERVAL '1 month'
  AND l.created_at <  date_trunc('month', CURRENT_DATE)
GROUP BY l.channel
ORDER BY enrolled DESC;

import pandas as pd

leads = pd.read_csv("leads.csv", parse_dates=["created_at", "first_contact_at"])
leads["hours_to_contact"] = (leads["first_contact_at"] - leads["created_at"]).dt.total_seconds() / 3600
leads["bucket"] = pd.cut(leads["hours_to_contact"], [0, 1, 24, 72, float("inf")],
                         labels=["<1h", "1-24h", "1-3d", ">3d"])
print(leads.groupby("bucket", observed=True)["enrolled"].mean().round(3))

import pandas as pd

week = pd.read_csv("learner_week.csv")   # learner_id, attendance_pct, submitted, due, score
week["submission_rate"] = week["submitted"] / week["due"].where(week["due"] > 0)
week["at_risk"] = (
    (week["attendance_pct"] < 0.5) |
    (week["submission_rate"] < 0.5) |
    (week["score"] < week["score"].median() * 0.7)
)
print(week.loc[week["at_risk"], ["learner_id", "attendance_pct", "submission_rate", "score"]])
# thresholds are examples — agree them with the academic team

KPI: Weekly Active Learner Rate
Definition: enrolled learners with at least one attended session OR submitted assignment in the week ÷ enrolled learners
Grain: weekly, by batch and course
Owner: Academic operations

KPI: Assignment Submission Rate
Definition: assignments submitted on time ÷ assignments due
Exclusions: learners on approved breaks

-- Weekly retention by start-month cohort (PostgreSQL)
WITH cohort AS (
  SELECT learner_id, date_trunc('month', start_date) AS cohort_month, start_date
  FROM enrolments
), activity AS (
  SELECT a.learner_id, c.cohort_month,
         FLOOR((a.activity_date - c.start_date) / 7) AS week_no
  FROM learner_activity a
  JOIN cohort c USING (learner_id)
)
SELECT cohort_month, week_no, COUNT(DISTINCT learner_id) AS active_learners
FROM activity
WHERE week_no BETWEEN 0 AND 12
GROUP BY cohort_month, week_no
ORDER BY cohort_month, week_no;

Unit economics worksheet (use your own figures)
Marketing + sales cost this month:  A
New paid enrolments this month:     B
CAC = A / B
Average revenue per enrolment:      C
Delivery cost per learner:          D
Contribution per learner = C - D
Healthy sign: contribution comfortably above CAC,
plus extra value from referrals and repeat courses.

EdTech KPI Sheet
Acquisition: leads, lead-to-demo %, demo-to-enrol %, CAC by channel
Engagement:  weekly active learner rate, attendance %, submission %
Outcomes:    completion %, assessment pass %, learner satisfaction
Growth:      referral rate, repeat enrolment %, revenue per learner
For each KPI: definition, grain, source, owner

EdTech Leadership Dashboard
Top row:   New enrolments | Lead-to-enrol % | Active learners | Completion %
Page 1:    Funnel by channel (bar) + trend by week (line)
Page 2:    Cohort retention heatmap
Page 3:    At-risk learners by batch (table for support team)
Filters:   course, batch, city, month

Portfolio case study: 'Improving demo-to-enrolment conversion'
Data: synthetic leads, demos, enrolments tables
Questions:
1. Which channel and demo slot convert best?
2. Does contact speed affect enrolment?
3. What would you test next?
Deliverables: SQL, dashboard, one-page recommendation