THIS POST IS IN PROGRESS

Calculate Percentage of Dev Time Using Data Export and DuckDB in TimeCamp

Time tracking is essential for understanding where your development team's hours are going. While TimeCamp provides excellent built-in reporting, sometimes you need deeper insights or custom analytics. By combining TimeCamp's data export feature with DuckDB, you can perform powerful SQL queries on your time tracking data without setting up a full database infrastructure.

In this tutorial, we'll show you how to export your TimeCamp data and use DuckDB to calculate what percentage of your time is spent on actual development versus meetings, admin tasks, and other activities.

Why DuckDB?

DuckDB is an embedded analytical database that's perfect for this use case. It can:

Prerequisites

Before we begin, make sure you have:

Step 1: Prepare and Export Your TimeCamp Data

First, you'll need to export your time tracking data from TimeCamp:

  1. Log into your TimeCamp account
  2. Open "Custom fields" settings and add custom field with name "Type" (available in Ultimate plan)
  3. Open "Project" page and add value for custom field Type = project to every project you would like to see in statistics. You don't need to be perfect here, you can repeat this step later if needed.
  4. Wait about 30 min. for changes to propagate (in the future it will be instant)
  5. Navigate to Manage → Data export
  6. Select your desired date range (e.g., this year)
  7. Export the report as a CSV file

Save the file as timecamp_export.csv in your working directory.

Step 2: Set Up DuckDB

Install DuckDB if you haven't already, then launch it in your terminal:

duckdb timecamp_analysis.db

Step 3: Load and Explore Your Data

First, let's take a look at the structure of your exported data:

-- Query placeholder: Select first 10 rows to inspect data structure
SELECT * FROM 'timecamp_export.csv' LIMIT 10;

Check the total time tracked:

-- Query placeholder: Calculate total hours tracked in the dataset
SELECT [CALCULATION] FROM 'timecamp_export.csv';

Step 4: Categorize Development Activities

To calculate the percentage of dev time, you first need to identify which activities count as "development." This might include tasks tagged with keywords like "coding," "development," "bug fix," "feature," etc.

Create a view that categorizes your activities:

-- Query placeholder: Create view categorizing activities as dev vs non-dev
CREATE VIEW categorized_time AS
SELECT [COLUMNS],
    CASE
        WHEN [CONDITION] THEN 'Development'
        WHEN [CONDITION] THEN 'Meetings'
        WHEN [CONDITION] THEN 'Admin'
        ELSE 'Other'
    END AS activity_category
FROM 'timecamp_export.csv';

Step 5: Calculate Development Time Percentage

Now for the main calculation - determining what percentage of your time was spent on development:

-- Query placeholder: Calculate percentage of time spent on each category
SELECT 
    activity_category,
    [DURATION_CALCULATION] AS total_hours,
    [PERCENTAGE_CALCULATION] AS percentage
FROM categorized_time
GROUP BY activity_category
ORDER BY total_hours DESC;

Step 6: Break Down by Team Member

If you're analyzing team data, you'll want to see individual breakdowns:

-- Query placeholder: Calculate dev percentage per team member
SELECT 
    user_name,
    [DEV_TIME_CALCULATION] AS dev_hours,
    [TOTAL_TIME_CALCULATION] AS total_hours,
    [PERCENTAGE_CALCULATION] AS dev_percentage
FROM categorized_time
GROUP BY user_name
ORDER BY dev_percentage DESC;

Step 7: Trend Analysis Over Time

Understanding how your development time percentage changes over time can reveal important patterns:

-- Query placeholder: Calculate weekly dev percentage trends
SELECT 
    [DATE_GROUPING] AS week,
    [PERCENTAGE_CALCULATION] AS dev_percentage
FROM categorized_time
GROUP BY week
ORDER BY week;

Advanced Analysis: Project-Level Insights

You can also analyze development time at the project level:

-- Query placeholder: Calculate dev vs non-dev time per project
SELECT 
    project_name,
    activity_category,
    [TIME_CALCULATION] AS hours,
    [PERCENTAGE_CALCULATION] AS percentage_of_project
FROM categorized_time
GROUP BY project_name, activity_category
ORDER BY project_name, hours DESC;

Exporting Results

Once you've generated your insights, export them for sharing with stakeholders:

-- Query placeholder: Export results to CSV
COPY (
    [YOUR_ANALYSIS_QUERY]
) TO 'dev_time_analysis.csv' (HEADER, DELIMITER ',');

Tips for Better Analysis

1. Consistent Tagging: Ensure your team uses consistent tags or project naming conventions in TimeCamp. This makes categorization much easier.

2. Regular Exports: Schedule monthly or quarterly exports to track trends over time.

3. Custom Categories: Adjust the activity categories to match your organization's needs. You might want to separate "client communication," "code review," "testing," etc.

4. Combine with Other Data: DuckDB can join multiple CSV files. Consider combining your TimeCamp data with project budgets or sprint data for deeper insights.

Conclusion

By combining TimeCamp's data export with DuckDB's analytical power, you can gain deep insights into how your development time is distributed. Whether you're trying to reduce meeting overhead, justify headcount requests, or optimize team workflows, having concrete data on development time percentages is invaluable.

The best part? This entire analysis requires no database servers, no ETL pipelines, and minimal setup time. You can run this analysis ad-hoc whenever you need fresh insights.

Next Steps

Have you tried analyzing your time tracking data with SQL? Share your insights and queries in the comments below!

Note: The specific SQL queries will depend on your TimeCamp export structure and your organization's specific categorization needs. Adjust the placeholder queries according to your data schema.