How to Integrate Retool with Khan Academy API

Log in to your Khan Academy teacher or admin account. For Khan Academy for Teams, navigate to your Teacher Dashboard (khanacademy.org/teacher/dashboard) and select the class you want to report on. Click on the 'Progress' tab to see the class's exercise and mastery data. Look for an Export or Download option — Khan Academy provides CSV exports of student progress data from the progress report views. The exact export path varies by interface version: look for a download icon or 'Export to CSV' link in the top-right of the progress table. The exported CSV typically includes columns for student email or anonymized ID, exercise name, skill domain, mastery level (Not Started / Attempted / Familiar / Proficient / Mastered), number of correct answers, number of problems attempted, time spent (seconds), and last activity date. For Khan Academy for Schools with district-level access, additional data exports may be available from the district admin portal including cross-class progress and SIS-matched student identifiers. Download the CSV and open it in a spreadsheet application to verify the column structure before importing into Retool. Note the column names exactly as they will become the table schema in Retool Database.

Navigate to your Retool workspace and click on the 'Database' section in the left sidebar (or navigate to retool.com/database). Retool Database is a managed PostgreSQL instance included in all Retool plans with 5GB of storage. Click 'Create table' and name it 'khan_progress'. You can either define the schema manually by adding columns one by one, or use Retool Database's CSV import feature to auto-detect the schema from your export file. To use CSV import: in the Retool Database interface, click Import → Upload CSV, and select your Khan Academy export file. Retool will preview the detected columns and suggest data types — review these carefully. String columns like student_email, exercise_name, skill_domain, and mastery_level should be VARCHAR or TEXT. Numeric columns like correct_count, attempted_count, and time_spent_seconds should be INTEGER or BIGINT. Date columns like last_activity should be TIMESTAMP or DATE. Click 'Import' to create the table and populate it with the CSV data. After the initial import, note the table schema so you can append future exports consistently. Consider adding an 'imported_at' column (TIMESTAMP, default CURRENT_TIMESTAMP) to track when each batch of data was imported, enabling you to filter to the most recent import when multiple exports are loaded.

1-- SQL: Create khan_progress table in Retool Database (or your PostgreSQL)
2CREATE TABLE IF NOT EXISTS khan_progress (
3  id SERIAL PRIMARY KEY,
4  student_email VARCHAR(255),
5  student_id VARCHAR(100),
6  exercise_name VARCHAR(500),
7  skill_domain VARCHAR(255),
8  mastery_level VARCHAR(50),
9  correct_count INTEGER DEFAULT 0,
10  attempted_count INTEGER DEFAULT 0,
11  time_spent_seconds INTEGER DEFAULT 0,
12  last_activity TIMESTAMP,
13  class_name VARCHAR(255),
14  course_name VARCHAR(255),
15  imported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
16);

In Retool's Resources tab, click Add Resource and select Retool Database from the list — it appears as a built-in resource pre-configured for your workspace's Retool Database instance. Name it 'Khan Academy Data' and click Save. If you are using an external PostgreSQL database instead, select PostgreSQL and enter your connection details (host, port, database name, username, password). Open or create a Retool app. In the Code panel, click + to create a new query named 'getStudentProgress'. Select your Retool Database resource. In the query editor, write a SQL query that fetches progress data with filters for the class and date range controls you will add in the UI. The query uses Retool's {{ }} syntax for dynamic parameterization, and Retool automatically converts these to prepared statements to prevent SQL injection. For the mastery level filter, use a CASE statement to convert the text mastery level into a numeric score (e.g., Not Started=0, Attempted=1, Familiar=2, Proficient=3, Mastered=4) for sorting and charting. Add aggregation queries for class-level summaries using GROUP BY student_email or GROUP BY skill_domain.

1-- Query: student progress with filters
2SELECT
3  student_email,
4  student_id,
5  exercise_name,
6  skill_domain,
7  mastery_level,
8  CASE mastery_level
9    WHEN 'Mastered' THEN 4
10    WHEN 'Proficient' THEN 3
11    WHEN 'Familiar' THEN 2
12    WHEN 'Attempted' THEN 1
13    ELSE 0
14  END AS mastery_score,
15  correct_count,
16  attempted_count,
17  ROUND(time_spent_seconds / 60.0, 1) AS time_spent_minutes,
18  last_activity
19FROM khan_progress
20WHERE
21  ({{ classFilter.value }} = '' OR class_name = {{ classFilter.value }})
22  AND ({{ domainFilter.value }} = '' OR skill_domain = {{ domainFilter.value }})
23  AND last_activity >= {{ dateRange.start || '2020-01-01' }}
24  AND last_activity <= {{ dateRange.end || 'now()' }}
25ORDER BY student_email, skill_domain, mastery_score DESC;

In the Retool canvas, add a stat row at the top of the app with three Statistic components showing class-level summary metrics: Total Students (COUNT DISTINCT student_email), Average Mastery Score (ROUND(AVG(mastery_score), 2)), and Total Time Spent (SUM(time_spent_minutes) formatted as hours). Below the stats row, add filter controls: a Select for class_name (named 'classFilter', data from a DISTINCT class_name query), a Select for skill_domain (named 'domainFilter', data from a DISTINCT skill_domain query), and a Date Range Picker (named 'dateRange'). Add a Table component below the filters bound to {{ getStudentProgress.data }} with columns for student_email (or a display name if your data includes one), skill_domain, exercise_name, mastery_level (formatted with conditional coloring — red for Not Started, yellow for Attempted, light green for Familiar, green for Proficient, dark green for Mastered), time_spent_minutes, and last_activity. Add a Chart component below the table showing mastery distribution: a Bar chart with skill_domain on the x-axis and the count of exercises at each mastery level as stacked bars. Use a GROUP BY transformer to prepare the Chart data. Add a second Chart showing weekly activity trends — exercises completed per week over the last 8 weeks — using a DATE_TRUNC('week', last_activity) GROUP BY query.

1// Transformer: prepare mastery distribution data for Chart component
2const rows = data || [];
3const domains = [...new Set(rows.map(r => r.skill_domain))];
4const levels = ['Mastered', 'Proficient', 'Familiar', 'Attempted', 'Not Started'];
5const colors = ['#16a34a', '#65a30d', '#ca8a04', '#f97316', '#dc2626'];
6
7return {
8  labels: domains,
9  datasets: levels.map((level, i) => ({
10    label: level,
11    backgroundColor: colors[i],
12    data: domains.map(domain =>
13      rows.filter(r => r.skill_domain === domain && r.mastery_level === level).length
14    )
15  }))
16};

Rather than manually re-importing CSVs each week, you can partially automate the refresh process using Retool Workflows combined with any automated export delivery that Khan Academy may offer. Create a new Retool Workflow by navigating to Retool → Workflows → Create new Workflow. Name it 'Khan Academy Data Refresh'. For organizations where Khan Academy exports can be automatically delivered (via SFTP, Google Drive, or email attachment), configure the Workflow with a Schedule trigger (e.g., every Monday at 8 AM). Add a Resource Query block that reads from the data source where the latest export file lands (e.g., if you move the CSV to an S3 bucket via automation, use an S3 Resource Query to fetch it). Add a Code block with JavaScript that parses the CSV content into row objects. Add a second Resource Query block that performs a bulk upsert into the khan_progress table using Retool Database's INSERT ... ON CONFLICT DO UPDATE syntax to merge new progress data with existing records without creating duplicates. Add error handling with a Global Error Handler block that sends a Slack notification if the import fails. For fully manual refresh workflows, the Workflow can also serve as a reminder system — schedule it to send a Slack message to the admin each Monday asking them to run the export and upload the file. For complex multi-source education analytics pipelines integrating Khan Academy exports with your SIS, LMS, and HR data, RapidDev's team can help architect your Retool data operations solution.

1// Retool Workflow Code block: parse CSV and upsert into Retool Database
2// Assumes 'csvContent' is the raw CSV string from a previous block
3const lines = csvContent.split('\n').filter(l => l.trim());
4const headers = lines[0].split(',').map(h => h.trim().replace(/"/g, ''));
5
6const rows = lines.slice(1).map(line => {
7  const values = line.split(',').map(v => v.trim().replace(/"/g, ''));
8  const row = {};
9  headers.forEach((h, i) => row[h] = values[i] || null);
10  return row;
11});
12
13// rows is now an array of objects ready for bulk upsert
14return rows;