Natural Language Statistics: Ask Questions, Get Visual Answers

Traditional task managers show you what's done and what's not, but they don't answer deeper questions:

• "How much time did I spend on client work last month?"
• "What percentage of tasks do I complete on time?"
• "Which tags have the most incomplete tasks?"
• "Am I working out more consistently than last quarter?"

Answering these questions requires exporting data, writing SQL queries, building pivot tables, and creating charts manually. By the time you have insights, a week has passed and you've lost motivation.

AI-generated statistics eliminate this friction. Instead of learning SQL or fighting with spreadsheets, you ask questions in plain English:

• "Show average workout duration per week"
• "Chart tasks completed per month by tag"
• "Display time spent in each state for work tasks"

To-Track's AI stat generator (Pro feature) turns natural language prompts into:

• SQL queries (optimized for your SQLite database)
• Visual charts (line, bar, pie, single value)
• Context-aware insights (respects your current list filters)

This guide shows you how to generate custom statistics, understand your productivity patterns, and build a personal analytics dashboard.

The Problem with Manual Analytics

Getting insights from task data shouldn't require a data science degree.

Manual Process (Traditional Approach)

1. Export task data to CSV
2. Import to Google Sheets or Excel
3. Clean/format data (fix dates, parse tags, etc.)
4. Create pivot tables
5. Build charts
6. Repeat monthly when you want updated data

Time: 30-60 minutes per analysis. Result: One-off static charts that become stale immediately.

AI-Generated Statistics (To-Track Approach)

1. Type question: "Show tasks completed per week"
2. AI generates SQL query
3. Chart appears instantly

Time: 10 seconds. Result: Reusable stat that updates as data changes.

How AI Stat Generation Works

The Workflow

1. Input: Describe what you want to visualize
2. AI Processing: Cloud-based LLM generates SQL query
3. Execution: Query runs against your local SQLite database
4. Visualization: Chart renders based on query results
5. Save: Statistic saved to your stats grid for reuse

Context Awareness

To-Track's AI understands your current context:

Active list filters: If you're viewing "Work tasks" list, AI includes that filter in generated queries.

Example:

• Current list: "Work" (filtered by tag "work")
• Prompt: "Show tasks completed per week"
• AI-generated query: SELECT ... WHERE tags LIKE '%work%' ...

Result: Chart shows only work task completions, not all tasks.

Available data: AI knows your database schema (todos, tags, states, properties, etc.) and generates valid queries.

Chart Types

AI selects appropriate visualization based on data:

1. Single Value: One number (e.g., "Total tasks completed this month" → 47)
2. Line Chart: Time series (e.g., "Tasks completed per week" → line graph over time)
3. Bar Chart: Comparisons (e.g., "Tasks per tag" → bars for each tag)
4. Pie Chart: Proportions (e.g., "Task distribution by state" → pie slices)

Time Formatting for Duration Stats

To-Track automatically formats time-spent statistics:

• Y-axis: Abbreviated (e.g., "2h", "45m", "1d") for readability
• Tooltips: Full breakdown (e.g., "2h 15m 30s") on tap
• Smart units: Skips zero values ("2h 15m" instead of "0d 2h 15m 0s")

Example: "Average time per task" displays "3h 24m" not "12240 seconds".

Example Queries & Results

Example 1: Tasks Completed Per Week

Prompt: "Show me tasks completed per week"

AI-generated SQL:

SELECT
  strftime('%Y-%W', created_at/1000, 'unixepoch', 'localtime') AS week,
  COUNT(*) AS count
FROM state_log
WHERE state_id IN (SELECT id FROM states WHERE category = 2)
GROUP BY week
ORDER BY week DESC
LIMIT 12

Chart: Line chart showing last 12 weeks, with task completion count on Y-axis.

Insight: "I completed 32 tasks the week of Jan 6, but only 18 last week—productivity dip?"

Example 2: Time Spent by Tag

Prompt: "Chart total time spent on each tag this month"

AI-generated SQL:

SELECT
  t.tag,
  SUM((sl2.created_at - sl1.created_at) / 1000) AS total_seconds
FROM state_log sl1
JOIN state_log sl2 ON sl1.todo_id = sl2.todo_id AND sl2.id = (
  SELECT MIN(id) FROM state_log WHERE todo_id = sl1.todo_id AND id > sl1.id
)
JOIN todos ON sl1.todo_id = todos.id
JOIN todo_tags tt ON todos.id = tt.todo_id
JOIN tags t ON tt.tag_id = t.id
JOIN states s ON sl1.state_id = s.id
WHERE s.category = 1
  AND DATE(sl1.created_at/1000, 'unixepoch', 'localtime') >= DATE('now', 'start of month', 'localtime')
GROUP BY t.tag
ORDER BY total_seconds DESC

Chart: Bar chart showing hours per tag (e.g., "work": 87h, "personal": 34h, "fitness": 12h).

Insight: "I'm spending 72% of active time on work tasks—need more balance."

Example 3: On-Time Completion Rate

Prompt: "What percentage of tasks do I complete on time?"

AI-generated SQL:

SELECT
  ROUND(
    SUM(CASE WHEN is_completed_on_time = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
    1
  ) AS on_time_percentage
FROM state_log
WHERE state_id IN (SELECT id FROM states WHERE category = 2)
  AND created_at >= strftime('%s', 'now', '-30 days') * 1000

Chart: Single value "73.5%"

Insight: "I hit deadlines 73.5% of the time—better than I thought!"

Example 4: Tasks by State Distribution

Prompt: "Show distribution of tasks across states"

AI-generated SQL:

SELECT
  s.state,
  COUNT(*) AS count
FROM todos t
JOIN states s ON t.state = s.id
GROUP BY s.state
ORDER BY count DESC

Chart: Pie chart showing proportions (TODO: 45%, IN-PROGRESS: 30%, DONE: 15%, BLOCKED: 10%).

Insight: "10% of tasks are blocked—need a dedicated unblocking session."

Example 5: Average Task Duration

Prompt: "What's the average time to complete a task?"

AI-generated SQL:

SELECT
  AVG((sl_close.created_at - sl_open.created_at) / 1000 / 3600.0) AS avg_hours
FROM state_log sl_open
JOIN state_log sl_close ON sl_open.todo_id = sl_close.todo_id
JOIN states s_close ON sl_close.state_id = s_close.id
WHERE s_close.category = 2
  AND sl_close.id = (
    SELECT MIN(id)
    FROM state_log
    WHERE todo_id = sl_open.todo_id AND state_id IN (SELECT id FROM states WHERE category = 2)
  )

Chart: Single value "12.3 hours" (automatically formatted with time units).

Insight: "Tasks take 12 hours on average—plan accordingly for new work."

Example 6: Most Productive Day of Week

Prompt: "Which day of the week do I complete the most tasks?"

AI-generated SQL:

SELECT
  CASE CAST(strftime('%w', created_at/1000, 'unixepoch', 'localtime') AS INTEGER)
    WHEN 0 THEN 'Sunday'
    WHEN 1 THEN 'Monday'
    WHEN 2 THEN 'Tuesday'
    WHEN 3 THEN 'Wednesday'
    WHEN 4 THEN 'Thursday'
    WHEN 5 THEN 'Friday'
    WHEN 6 THEN 'Saturday'
  END AS day,
  COUNT(*) AS count
FROM state_log
WHERE state_id IN (SELECT id FROM states WHERE category = 2)
GROUP BY strftime('%w', created_at/1000, 'unixepoch', 'localtime')
ORDER BY count DESC

Chart: Bar chart (Thursday: 38, Wednesday: 34, Tuesday: 31, Monday: 28, Friday: 22).

Insight: "Thursdays are my power day—schedule important work then."

Example 7: Property Trends (Workout Example)

Prompt: "Show my average workout duration per week"

AI-generated SQL:

SELECT
  strftime('%Y-%W', pl.created_at/1000, 'unixepoch', 'localtime') AS week,
  AVG(pl.value_numeric) AS avg_duration
FROM property_log pl
JOIN properties p ON pl.property_id = p.id
WHERE p.name = 'Duration'
  AND pl.todo_id IN (
    SELECT id FROM todos WHERE todo LIKE '%workout%'
  )
GROUP BY week
ORDER BY week DESC
LIMIT 12

Chart: Line chart showing average workout duration over 12 weeks (trend: increasing from 45 min to 62 min).

Insight: "Workout duration increased 38% over 3 months—progressive overload working!"

Creating Custom Statistics

Step 1: Navigate to Stats Page

Swipe right from Todo List Page or tap stats icon.

Step 2: Tap "Create Stat" Button (Sparkles Icon)

Opens AI stat generator modal.

Step 3: Describe What You Want

Prompt guidelines:

• Be specific: "Tasks completed per week" (not "task stuff")
• Include time frame if relevant: "last 3 months", "this year"
• Specify groupings: "by tag", "by state", "per day"
• Mention chart type if you have a preference: "bar chart of", "pie chart showing"

Good prompts:

• "Show tasks completed per month for the last 6 months"
• "Bar chart of time spent in each state for work tasks"
• "What percentage of tasks have the 'urgent' tag?"
• "Average task duration by tag"

Vague prompts (less effective):

• "Show me stuff" (what stuff?)
• "Task things" (too generic)
• "Data" (what data?)

Step 4: Review Generated Stat

AI returns:

• Title: Auto-generated based on your prompt
• Chart type: Chosen by AI based on data (line, bar, pie, single value)
• SQL query: (Visible if you want to inspect/modify)

Step 5: Save to Stats Grid

Tap "Save" to add the stat to your stats page. It appears as a card with:

• Title
• Chart visualization
• Last updated timestamp

Step 6: Refresh or Delete

• Refresh: Tap stat card to re-run query with latest data
• Delete: Long-press stat card → "Delete" to remove

Context-Aware Statistics

AI respects your current list context:

Scenario: Viewing "Work Tasks" List

Filters:

• Tags: "work"
• State categories: ACTIVE

Prompt: "Show tasks completed per week"

AI includes filter:

WHERE tags LIKE '%work%'
  AND state IN (SELECT id FROM states WHERE category = 1)

Result: Chart shows only work task completions while active, not all tasks.

Scenario: Viewing "High Priority Personal" List

Filters:

• Tags: "personal", "high-priority"
• State categories: OPEN, ACTIVE

Prompt: "Average task duration"

AI includes filter:

WHERE todo_id IN (
  SELECT id FROM todos
  WHERE id IN (SELECT todo_id FROM todo_tags WHERE tag_id IN (...))
)
AND state IN (SELECT id FROM states WHERE category IN (0, 1))

Result: Average duration for high-priority personal tasks only.

Benefit: Contextual Insights

Instead of generic stats ("All tasks completed per week"), you get targeted insights ("Work tasks completed per week while active").

Built-In Time-Spent Statistics

To-Track includes 4 default time-spent charts (no AI required):

1. Daily Time Spent: Bar chart showing hours worked per day (last 30 days)
2. Weekly Time Spent: Bar chart showing hours per week (last 12 weeks)
3. Monthly Time Spent: Bar chart showing hours per month (last 6 months)
4. Yearly Time Spent: Bar chart showing hours per year (last 3 years)

All use time formatting:

• Y-axis: Abbreviated ("2h", "45m", "1d")
• Tooltips: Full breakdown ("2h 15m 30s")

Data source: State logs for ACTIVE states (IN-PROGRESS, REVIEW, etc.).

Timezone Handling in Stats

Critical: All stats use 'localtime' modifier in SQL date functions to convert UTC timestamps to your local timezone.

Pattern:

DATE(timestamp_ms/1000, 'unixepoch', 'localtime')

Why: Database stores all timestamps in UTC. Without 'localtime', charts show wrong dates (e.g., task completed 11 PM local time appears as next day in UTC).

Applies to:

• DATE() functions (grouping by day)
• strftime() functions (formatting dates)
• datetime() functions (date comparisons)

Always include 'localtime' in generated queries.

Comparing AI Stats to Manual Analysis

Aspect	AI Stats (To-Track Pro)	Manual (Spreadsheets)
Time to create	10 seconds	30-60 minutes
SQL knowledge required	None	Advanced
Reusability	Save & refresh anytime	Recreate each time
Context awareness	Automatic (respects filters)	Manual filtering needed
Chart generation	Automatic	Manual chart creation
Time formatting	Auto (2h 15m)	Manual (convert seconds → hours)
Updates	Tap to refresh	Re-export, re-import, rebuild
Complexity limit	LLM-dependent	Your SQL skills

Verdict: AI stats are 100x faster for 90% of use cases, with the option to export data for advanced analysis if needed.

Advanced: SQL Query Editing

For power users, To-Track exposes the generated SQL:

Viewing Queries

Tap a stat card → "View Query" to see the SQL.

Editing Queries

Advanced users can modify SQL directly:

• Add additional filters
• Change grouping logic
• Adjust time ranges
• Optimize performance

Example modification:

-- AI-generated query
SELECT COUNT(*) FROM todos WHERE state = 'DONE'

-- Your modification (last 30 days only)
SELECT COUNT(*) FROM todos
WHERE state = 'DONE'
  AND created_at >= strftime('%s', 'now', '-30 days') * 1000

Save edited query as a new stat.

Limitations

• Must return valid data structure (chart type depends on column count)
• Must include 'localtime' for date conversions
• Performance: Complex queries may be slow on large datasets

Privacy & Data Security

AI stat generation follows the same privacy model as AI task creation:

1. Local data stays local: SQLite database never leaves your device
2. Only schema & prompt sent: AI receives database structure (table names, column names) and your prompt—not task contents
3. Ephemeral processing: Prompt processed in cloud, immediately deleted
4. No data retention: Your prompts aren't stored or used for training

What's sent:

• Database schema (table/column names)
• Your prompt ("Show tasks completed per week")
• Current list filter context (tag names, state names—not task contents)

What's NOT sent:

• Task names, notes, or details
• Actual data (row contents)
• Full database export

Tips for Better Stats

1. Start Simple, Then Refine

First attempt: "Show tasks completed" AI returns: Total count (single value: 247) Refinement: "Show tasks completed per week" AI returns: Line chart over time

Iterate on prompts based on results.

2. Specify Time Ranges

Vague: "Show completed tasks" Specific: "Show tasks completed in the last 3 months"

Time ranges prevent overwhelming data (100k+ rows) and improve relevance.

3. Use Tag/State Names Explicitly

Generic: "Show work stuff" Specific: "Show tasks tagged 'work' completed per week"

AI understands tag/state names if you mention them.

4. Request Aggregations

Raw data: "Show task durations" → list of 500 numbers Aggregated: "Show average task duration by tag" → 5 meaningful bars

Aggregations (SUM, AVG, COUNT) make data actionable.

5. Combine Metrics

Prompt: "Show tasks completed and time spent per week"

AI can generate queries with multiple Y-axes (e.g., count on left axis, hours on right axis).

Best Practices for Analytics

Weekly Review Stats

Create stats for weekly reflection:

• "Tasks completed this week"
• "Time spent in BLOCKED state this week"
• "On-time completion rate this week"

Review every Monday to assess past week.

Monthly Trends

Track long-term patterns:

• "Tasks completed per month (last 12 months)"
• "Average task duration by month"
• "Tag distribution over time"

Spot trends like declining productivity or shifting focus.

Goal Tracking

Monitor progress toward goals:

• "Workout completion rate this quarter" (target: 80%)
• "Blog posts published per month" (goal: 4/month)
• "Client tasks completed vs. personal tasks ratio"

Adjust behavior based on data.

Bottleneck Identification

Find process problems:

• "Time spent in REVIEW state vs. IN-PROGRESS state"
• "Percentage of tasks that get blocked"
• "Which tags have the most incomplete tasks?"

Fix systemic issues revealed by stats.

Conclusion: Data-Driven Productivity

Insights without effort. That's the promise of AI-generated statistics.

Instead of exporting CSVs, writing SQL, and building pivot tables, you ask questions in plain English:

• "How am I spending my time?"
• "Am I hitting deadlines?"
• "Which projects consume the most effort?"

To-Track's AI stat generator transforms queries into charts instantly, with:

• Context awareness (respects your current list filters)
• Automatic time formatting (2h 15m, not 8100 seconds)
• Reusable stats (save & refresh as data changes)
• Privacy-first processing (schema sent, not data)

Whether you're optimizing productivity, tracking habit trends, or analyzing project time, AI stats eliminate the friction between questions and answers.

Stop wrestling with spreadsheets. Start asking questions and getting visual answers.

FAQ

Q: Do I need to know SQL? A: No! Describe what you want in plain English, and AI generates the SQL. Advanced users can edit queries if desired.

Q: How many custom stats can I create? A: Unlimited. Create as many as you need for different insights.

Q: Can I export stats as images? A: Currently no direct export. Use screenshot functionality as a workaround. Future updates may add export.

Q: Do stats update automatically? A: No—tap a stat card to refresh with latest data. This prevents unnecessary re-queries.

Q: What if AI generates an incorrect query? A: Rephrase your prompt or manually edit the SQL (advanced users). Report persistent issues for model improvements.

Q: Can I share stats with others? A: Not directly (To-Track is individual-focused). Share screenshots or export raw data to share insights.

Q: Does stat generation work offline? A: No—AI processing requires internet. Pre-created stats can be refreshed offline (using saved SQL).

Q: What's the maximum data size for stats? A: SQLite handles millions of rows efficiently. Very complex queries (100k+ rows) may be slow. Use time ranges to limit data.

Q: Can I create stats for properties? A: Yes! Example: "Show average workout weight over time" uses property_log table.

---

Ready to unlock productivity insights? Upgrade to To Track Pro and start generating custom statistics with AI.