## Introduction
Tracking weekly velocity—the amount of work completed by teams within a given week—is critical for operations teams looking to optimize efficiency, forecast workload, and identify bottlenecks. Particularly in cross-departmental contexts, gathering consistent data on task completion from different tools and consolidating it into actionable reports can be challenging.
In this article, we will build a comprehensive, automated workflow using n8n to track weekly velocity across departments by integrating common tools such as Jira (for issue tracking), Google Sheets (for central data storage), and Slack (for notifications). This workflow benefits operations teams by providing up-to-date visibility into progress across departments without manual reporting.
—
## Tools and Services to Integrate
– **n8n**: Open-source workflow automation tool. Acts as the orchestrator.
– **Jira API**: Source of task and story completion data for software and project teams.
– **Google Sheets**: Serves as a centralized, accessible repository to store and visualize weekly velocity data.
– **Slack**: Channel for sending automated progress notifications or alerts.
You can extend this workflow by integrating other project management tools (e.g., Asana, Trello) or communication platforms.
—
## Workflow Overview
The workflow can be triggered weekly (e.g., Monday mornings) to gather completed work items from department-specific Jira projects over the previous week, aggregate the data, push it to Google Sheets, and send a summarized alert message to operations or leadership via Slack.
**Trigger**: Cron node in n8n scheduling the workflow once per week.
**Steps**:
1. Trigger workflow on schedule.
2. Query Jira API for each department’s completed issues in past week.
3. Aggregate counts and compute velocity metrics.
4. Update Google Sheets with latest weekly data.
5. Send Slack notification summarizing weekly velocity.
—
## Step-by-Step Tutorial
### Step 1: Setup the Scheduled Trigger
– Use the **Cron** node to schedule the workflow to run every Monday at 8 AM.
– This ensures your velocity data is refreshed weekly.
### Step 2: Query Jira for Completed Issues
– Add a **HTTP Request** node configured to call Jira’s REST API.
– Endpoint: `/rest/api/3/search`
– Query parameters:
“`json
{
“jql”: “project = DEPT_PROJECT AND status = Done AND updated >= -7d”,
“fields”: [“key”]
}
“`
*Replace `DEPT_PROJECT` with your Jira project key for each department.*
– Repeat this node for each department or use a loop node to cycle through an array of project keys.
– Authentication: Use OAuth2 or Basic Auth depending on your Jira instance setup.
– The result will return issues completed in the last 7 days.
### Step 3: Aggregate Velocity Data
– Use a **Function** node to extract the count of completed issues from each HTTP response.
– Create a JSON object like `{ department: ‘Engineering’, completedIssues: 23 }` for each.
– Collect all departments’ data into an array to prepare for the Google Sheets update.
### Step 4: Update Google Sheets
– Use the **Google Sheets** node to append or update rows with the current week’s velocity data.
– Structure your sheet with columns: Date, Department, Completed Issues.
– For each department’s data:
– Add a new row with the current date (e.g., ISO string), department name, and completed issues count.
### Step 5: Send Slack Notification
– Use the **Slack** node, configured with a bot token or webhook.
– Format a summary message:
“`
Weekly Velocity Report:
– Engineering: 23 issues
– Marketing: 15 issues
– Sales: 12 issues
“`
– Send this message to an operations or leadership channel.
—
## Common Errors and Tips
– **Authentication Fails**: Ensure Jira API tokens or OAuth credentials are current and have proper permissions.
– **Jira Rate Limits**: If querying many projects or large volumes, consider adding delays or batching to avoid hitting API limits.
– **Date Handling**: Be precise with time zones and date definitions in Jira JQL queries to get accurate last 7 days.
– **Google Sheets Quotas**: Avoid exceeding Google Sheets API limits by batching writes.
– **Slack Formatting**: Use markdown formatting for clearer messages.
—
## Scaling and Adaptation
– **Adding Departments**: Simply extend your project keys array and ensure corresponding Jira queries.
– **Supporting Other Tools**: Add nodes for other PM tools (like Asana) and map their data into the velocity schema.
– **Visualization**: Add a **Google Data Studio** report connected to the Google Sheets data for visual insights.
– **Alerts for Anomalies**: Add conditional logic to notify if velocity drops below thresholds.
—
## Summary
Automating weekly velocity tracking with n8n empowers operations teams with timely, accurate data collected directly from source systems like Jira and centralized in Google Sheets. Integrating Slack notifications closes the loop by keeping stakeholders informed without manual intervention.
This workflow reduces overhead, eliminates manual data compilation errors, and can easily adapt as your team or tools evolve.
—
### Bonus Tip
For enhanced resilience, add error handling nodes to catch API failures and notify your automation engineers via Slack or email. Also, log execution details to a dedicated Google Sheet tab for auditing and troubleshooting.