Sessions & AI Analytics Cubes

These cubes provide analytics for AI coding sessions uploaded from the GuideMode Desktop app. They track session metadata, performance metrics, and the impact of AI tools on developer productivity.

Sessions Cube

Source Table: agent_sessions Description: Analytics for AI agent sessions including uploads, processing status, and metadata.

Key Dimensions

Dimension	Type	Description
`id`	string	Unique session ID
`provider`	string	AI provider (claude-code, copilot, etc.)
`sessionId`	string	Provider’s session ID
`fileName`	string	Uploaded file name
`processingStatus`	string	Processing status (pending, completed, failed)
`assessmentStatus`	string	Assessment completion status
`sessionStartTime`	time	When session started
`sessionEndTime`	time	When session ended
`createdAt`	time	Record creation time
`uploadedAt`	time	When uploaded
`userId`	string	User who uploaded
`projectId`	string	Associated project

Count Measures

Measure	Description
`count`	Total sessions
`pendingCount`	Sessions awaiting processing
`completedCount`	Successfully processed sessions
`failedCount`	Failed processing sessions

File Size Measures

Measure	Description
`totalFileSize`	Total file size (bytes)
`avgFileSize`	Average file size (bytes)

Duration Measures

Measure	Description
`totalDuration`	Total session time (ms)
`totalDurationHours`	Total session time (hours)
`totalDurationDays`	Total session time (days)
`avgDuration`	Average session duration (ms)

Engagement Measures

Measure	Description
`uniqueUsers`	Count of distinct users
`uniqueProjects`	Count of distinct projects

Use Cases

Upload Monitoring: Track session upload status
Provider Analysis: Compare usage across AI providers
Time Investment: Measure total time spent in AI sessions
User Engagement: Track active users and projects

Metrics Cube

Source Table: session_metrics Description: Detailed performance, usage, quality, and engagement metrics extracted from AI coding sessions.

Key Dimensions

Dimension	Type	Description
`id`	string	Metric record ID
`sessionId`	string	Associated session
`provider`	string	AI provider
`timestamp`	time	Metric timestamp
`createdAt`	time	Record creation time
`usedPlanMode`	boolean	Whether plan mode was used
`usedTodoTracking`	boolean	Whether todo tracking was used
`costSource`	string	How the cost was derived: `derived`, `derived_partial`, `unavailable`, `pending`
`primaryModel`	string	Model that produced the most output, including any context-tier suffix

Performance Measures

Measure	Description
`avgResponseLatency`	Average AI response latency (ms)
`maxResponseLatency`	Maximum response latency (ms)
`avgTaskCompletionTime`	Average task completion time (ms)
`maxTaskCompletionTime`	Maximum task completion time (ms)

Usage Measures

Measure	Description
`avgReadWriteRatio`	Ratio of read to write operations
`avgInputClarityScore`	Average input clarity score
`totalReadOperations`	Total file read operations
`avgReadOperations`	Average reads per session
`totalWriteOperations`	Total file write operations
`avgWriteOperations`	Average writes per session
`totalUserMessages`	Total user messages
`avgUserMessages`	Average messages per session

Error Measures

Measure	Description
`totalErrors`	Total errors across sessions
`avgErrors`	Average errors per session
`maxErrors`	Maximum errors in a session
`totalRecoveryAttempts`	Total recovery attempts
`avgRecoveryAttempts`	Average recoveries per session
`totalFatalErrors`	Total fatal errors
`avgFatalErrors`	Average fatal errors per session

Engagement Measures

Measure	Description
`avgInterruptionRate`	Average interruption rate
`avgSessionLength`	Average session length (minutes)
`maxSessionLength`	Maximum session length (minutes)
`totalSessionLength`	Total session time (minutes)
`totalSessionLengthHours`	Total time (hours)
`totalSessionLengthDays`	Total time (days)
`totalInterruptions`	Total interruptions
`avgInterruptions`	Average interruptions per session

Quality Measures

Measure	Description
`avgTaskSuccessRate`	Average task success rate
`totalIterations`	Total iterations
`avgIterations`	Average iterations per task
`avgProcessQualityScore`	Average quality score
`totalOverTopAffirmations`	Total over-the-top affirmations
`operationSuccessRate`	% successful operations

Plan Mode & Todo Measures

Measure	Description
`totalExitPlanMode`	Total plan mode exits
`avgExitPlanMode`	Average per session
`totalTodoWrites`	Total todo list updates
`avgTodoWrites`	Average per session
`planModeUsageRate`	% sessions using plan mode
`todoTrackingUsageRate`	% sessions using todo tracking

Git Diff Measures

Measure	Description
`totalGitFilesChanged`	Total files changed via git
`avgGitFilesChanged`	Average files changed
`totalGitLinesAdded`	Total lines added
`avgGitLinesAdded`	Average lines added
`totalGitLinesRemoved`	Total lines removed
`avgGitLinesRemoved`	Average lines removed
`totalGitLinesModified`	Total lines modified
`totalGitNetLinesChanged`	Net lines changed
`totalLinesRead`	Total lines read

Git Efficiency Measures

Measure	Description
`avgGitLinesReadPerLineChanged`	Lines read per line changed
`avgGitReadsPerFileChanged`	Reads per file changed
`avgGitLinesChangedPerMinute`	Lines changed per minute
`avgGitLinesChangedPerToolUse`	Lines changed per tool use

Context Management Measures

Measure	Description
`totalInputTokens`	Total input tokens
`avgInputTokens`	Average input tokens
`totalOutputTokens`	Total output tokens
`avgOutputTokens`	Average output tokens
`totalCacheCreated`	Total cache tokens created
`totalCacheRead`	Total cache tokens read
`avgContextLength`	Average context length
`maxContextLength`	Maximum context length
`avgContextUtilization`	Average context utilization %
`maxContextUtilization`	Maximum context utilization %
`totalCompactEvents`	Total context compaction events
`avgTokensPerMessage`	Average tokens per message
`avgMessagesUntilFirstCompact`	Messages before compaction

Cost Measures

Measure	Description
`totalApiEquivalentCost`	Total API-equivalent cost (USD)
`avgApiEquivalentCost`	Average API-equivalent cost per session (USD)
`costPerAgentLine`	API-equivalent cost per line the agent added or removed (USD)
`pricedSessionCount`	Sessions that have a derived cost
`costCoveragePercent`	Percentage of sessions that could be priced
`partiallyPricedSessionCount`	Sessions where at least one model could not be priced
`totalProviderReportedCost`	The provider’s own cost figure, where reported

“API-equivalent cost” is the list-price value of the tokens a session consumed, derived from GuideMode’s own price table. It is not spend: a Max, Pro or Copilot user pays a flat subscription and is billed none of it. Deriving it ourselves is the point — every provider is then measured the same way, including the ones that report no cost of their own. Sessions that could not be priced are excluded from the totals, never counted as zero, so totalApiEquivalentCost is a total over an unknown denominator unless it is read beside costCoveragePercent. Pair them, and read the derivation and its three caveats before quoting a number.

Use Cases

Performance Analysis: Track response times and latency
Quality Monitoring: Measure success rates and errors
Efficiency Metrics: Analyze code changes per session
Feature Adoption: Track plan mode and todo usage
Token Analysis: Monitor context window utilization
Cost Analysis: Track API-equivalent cost per session, per line, and its coverage

AI Productivity Cube

Source: Cross-join of pull_requests and agent_sessions Description: Measures the impact of AI tool usage on developer productivity by comparing AI-assisted vs non-AI-assisted PRs.

Key Dimensions

Dimension	Type	Description
`prId`	string	Pull request ID
`projectId`	string	Project ID
`teamId`	string	Team ID
`hasAiSession`	string	Whether PR had AI assistance (‘true’/‘false’)
`aiProvider`	string	AI provider used (if any)
`createdAt`	time	PR creation time
`mergedAt`	time	PR merge time

Core Measures

Measure	Description
`totalPrCount`	Total PRs
`aiAssistedCount`	PRs with AI assistance
`aiAdoptionRate`	% PRs with AI assistance

Cycle Time Comparison

Measure	Description
`avgAiCycleTimeHours`	Average cycle time for AI-assisted PRs
`avgNonAiCycleTimeHours`	Average cycle time for non-AI PRs
`cycleTimeImprovementPercent`	% improvement from AI assistance

Calculation Details

AI Adoption Rate:

aiAdoptionRate = (aiAssistedCount / totalPrCount) * 100

Cycle Time Improvement:

cycleTimeImprovementPercent =
  ((avgNonAiCycleTime - avgAiCycleTime) / avgNonAiCycleTime) * 100

A positive improvement means AI-assisted PRs merge faster.

Use Cases

AI Impact Assessment: Measure productivity gains from AI tools
Adoption Tracking: Monitor team adoption of AI assistants
Provider Comparison: Compare effectiveness of different AI providers
ROI Justification: Quantify AI tool benefits

Interpretation Guide

Metric	Good	Needs Attention
`aiAdoptionRate`	> 50%	< 20%
`cycleTimeImprovementPercent`	> 20%	Negative
`avgAiCycleTimeHours`	< avgNonAi	> avgNonAi

Common Joins

Sessions Cube Joins

Join	Description
`Users`	User who uploaded
`Projects`	Associated project
`Teams`	Via user membership
`TeamsViaProjects`	Via project assignment

Metrics Cube Joins

Join	Description
`Sessions`	Parent session
`UsersViaSessions`	User via session
`ProjectsViaSessions`	Project via session

AI Productivity Cube

The AI Productivity cube uses raw SQL joins and doesn’t have standard cube joins. Filter using dimensions directly.

Example Queries

Sessions by provider:

measures: [Sessions.count, Sessions.totalDurationHours]
dimensions: [Sessions.provider]

Average metrics by provider:

measures: [Metrics.avgSessionLength, Metrics.avgTaskSuccessRate]
dimensions: [Metrics.provider]

AI productivity by team:

measures: [AIProductivity.aiAdoptionRate, AIProductivity.cycleTimeImprovementPercent]
dimensions: [AIProductivity.teamId]

Frameworks

SPACE Framework - Developer productivity measurement

Analytics

Work Tracking Cubes - Issues, PRs, Deployments
Surveys & Assessments - User feedback
Metrics Methodology - Full rubrics for process_quality_score and input_clarity_score
Analytics Overview - Understanding the analytics system

Sessions & AI Analytics Cubes

Sessions Cube

Key Dimensions

Count Measures

File Size Measures

Duration Measures

Engagement Measures

Use Cases

Metrics Cube

Key Dimensions

Performance Measures

Usage Measures

Error Measures

Engagement Measures

Quality Measures

Plan Mode & Todo Measures

Git Diff Measures

Git Efficiency Measures

Context Management Measures

Cost Measures

Use Cases

AI Productivity Cube

Key Dimensions

Core Measures

Cycle Time Comparison

Calculation Details

Use Cases

Interpretation Guide

Common Joins

Sessions Cube Joins

Metrics Cube Joins

AI Productivity Cube

Example Queries

Related

Frameworks

Analytics