Skip to content

SkillUp Transcript Analysis

This workflow automates the analysis of coaching session transcripts for the SkillUp youth entrepreneurship program in Kenya. It processes transcript files stored in Google Drive, uses AI to extract structured insights about business coaching sessions, and logs the results to Airtable for program quality assessment and learner progress tracking.

Purpose

No business context provided yet — add a context.md to enrich this documentation.

Based on the workflow implementation, this system appears to serve SkillUp program administrators who need to: - Analyze coaching session transcripts at scale - Extract standardized insights from unstructured conversation data - Track coaching quality and learner progress - Generate reports on program effectiveness - Identify patterns in business mentoring sessions

The workflow specifically focuses on extracting key coaching elements like trainer names, business types, milestone actions, obstacles, and quality scores from transcript files.

How It Works

  1. Manual Trigger: An administrator starts the workflow manually when ready to process transcripts
  2. File Discovery: The system searches a specific Google Drive folder for text transcript files
  3. Batch Processing: Files are processed in batches of 10 to manage API rate limits
  4. File Download: Each transcript file is downloaded from Google Drive
  5. Text Extraction: The raw text content is extracted from each file
  6. AI Analysis: Google Gemini analyzes each transcript to extract structured data including:
    • Trainer name and session summary
    • Business type and milestone actions
    • Obstacles identified and actions committed
    • Quality score and detailed remarks
  7. Data Structuring: The AI response is parsed and formatted into standardized fields
  8. Database Logging: Results are saved to an Airtable database for tracking and reporting
  9. Rate Limiting: A 2-second wait between batches prevents API overload
  10. Loop Continuation: The process repeats until all transcripts are analyzed

Workflow Diagram

graph TD
    A[Manual Trigger] --> B[Search Google Drive Files]
    B --> C[Add Limit - 2000 files max]
    C --> D[Split In Batches - 10 files]
    D --> E[Download File from Drive]
    E --> F[Extract Text Content]
    F --> G[Gemini AI Analysis]
    G --> H[Parse & Structure Data]
    H --> I[Create Airtable Record]
    I --> J[Wait 2 seconds]
    J --> D
    D --> K[End - All Batches Complete]

Trigger

Manual Trigger: The workflow is started manually by clicking "Test workflow" in the n8n interface. This gives administrators control over when transcript analysis runs, allowing them to process batches during appropriate times.

Nodes Used

Node Type Purpose
Manual Trigger Allows manual workflow initiation
Google Drive (Search) Finds transcript files in specified folder
Code (Add Limit) Limits processing to first 2000 files
Split In Batches Processes files in groups of 10
Google Drive (Download) Downloads transcript files
Extract from File Extracts text content from files
Google Gemini AI analysis of transcript content
Code (Parse & Structure) Formats AI response into structured data
Airtable Saves analysis results to database
Wait Rate limiting between batches

External Services & Credentials Required

Google Drive

  • Purpose: File storage and retrieval
  • Credential: googleDriveOAuth2Api (Google Drive account)
  • Permissions: Read access to transcript folder

Google Gemini

  • Purpose: AI-powered transcript analysis
  • Credential: googlePalmApi (Ameer account)
  • Model: gemini-2.5-pro
  • Permissions: API access for text analysis

Airtable

  • Purpose: Results database storage
  • Credential: airtableTokenApi (EXP Training Bot)
  • Base: app7ljEXNqhMlhsNS (Transcripts Analysis)
  • Table: tblZv77GimrYguEKg (Transcripts Metadata)

Environment Variables

No environment variables are explicitly configured in this workflow. All configuration is handled through: - Hard-coded folder ID: 1iJooYZmY2IgJhyyTUPBL9IIiHkV6SDb2 - Airtable base and table IDs embedded in node configuration - Batch size: 10 files per batch - Rate limit: 2-second wait between batches

Data Flow

Input

  • Source: Google Drive folder containing text transcript files
  • Format: Plain text files (.txt)
  • Location: Folder ID 1iJooYZmY2IgJhyyTUPBL9IIiHkV6SDb2

Processing

  • AI Analysis: Extracts structured coaching insights
  • Quality Assessment: Scores sessions on 5 SkillUp criteria
  • Standardization: Maps business stages to S0-S13 framework

Output

  • Destination: Airtable database
  • Fields:
    • Trainer Name
    • Session Summary (2-3 sentences)
    • Business Type
    • Milestone Action (raising capital/starting/sales)
    • Adjacent Stage (S0-S13 mapping)
    • Obstacles (bullet-separated)
    • Actions (bullet-separated)
    • Quality Score (0-10)
    • Quality Remarks
    • Transcript Link

Error Handling

The workflow includes basic error handling in the "Parse & Structure Data" node:

  • JSON Parsing Errors: If AI response cannot be parsed, the record is marked as processed_successfully: false
  • Missing Data: Default values are provided for missing fields
  • API Failures: Error details are captured in error_details field
  • File Access Issues: Workflow will continue processing remaining files if individual files fail

Known Limitations

  • File Type Restriction: Only processes plain text files (.txt)
  • Folder Dependency: Hard-coded to specific Google Drive folder
  • Rate Limiting: 2-second delays may make large batches slow
  • Manual Trigger Only: No automatic scheduling capability
  • AI Response Variability: Gemini responses may occasionally fail to parse
  • Batch Size: Fixed at 10 files per batch

No related workflows are specified in the current documentation.

Setup Instructions

1. Import Workflow

  • Import the workflow JSON into your n8n instance
  • The workflow will be inactive by default

2. Configure Google Drive Access

  • Set up Google Drive OAuth2 credentials
  • Ensure access to the transcript folder (1iJooYZmY2IgJhyyTUPBL9IIiHkV6SDb2)
  • Update folder ID if using a different location

3. Configure Google Gemini

  • Set up Google Palm API credentials for Gemini access
  • Verify access to gemini-2.5-pro model
  • Test API connectivity

4. Configure Airtable

  • Set up Airtable API token
  • Verify access to base app7ljEXNqhMlhsNS
  • Ensure table tblZv77GimrYguEKg exists with correct field structure
  • Map field names to match Airtable schema

5. Test Setup

  • Place test transcript files in the Google Drive folder
  • Activate the workflow
  • Click "Test workflow" to run a small batch
  • Verify results appear correctly in Airtable
  • Check error handling with malformed files

6. Production Use

  • Activate the workflow for regular use
  • Monitor batch processing performance
  • Adjust batch size or wait times if needed
  • Set up monitoring for failed analyses