Step-by-step: Creating a custom activity report that Granola's builder can't handle.

Granola's built-in reporting is excellent for standard velocity, burndown, and capacity overviews. However, when our product leadership requested a highly specific, cross-project view of "design debt" tickets—categorized by a custom field and weighted by story points over the last six rolling weeks—I found the native builder lacking the necessary flexibility for complex joins and window functions.

This post details the analytical approach and the SQL necessary to build such a report externally, which can then be scheduled and visualized in a connected BI tool like Looker. The core challenge was aggregating story points for specific tickets across multiple projects while calculating a rolling window average, a pattern not currently supported in Granola's UI.

**Step 1: Extracting the Raw Data from Granola's Data Warehouse**

Assuming you have direct access to the underlying data warehouse (e.g., Snowflake, BigQuery), the first step is to create a robust extraction query that pulls the necessary entities. We need tickets, their custom fields, and historical data from the `issues` table.

```sql
WITH design_debt_tickets AS (
SELECT
i.id,
i.project_id,
i.created_date,
i.resolution_date,
i.story_points,
-- Assuming a custom field is stored as a key in a JSON variant:
custom_fields:design_debt_category::STRING as debt_category,
DATE_TRUNC('week', i.created_date) AS ticket_week
FROM granola.issues i
WHERE i.issue_type = 'Story'
AND i.story_points IS NOT NULL
AND debt_category IS NOT NULL
AND i.created_date >= DATEADD(week, -12, CURRENT_DATE())
)
```

**Step 2: Implementing the Rolling Window Calculation**

The builder cannot easily perform a rolling sum and average over a dynamic window. This requires a self-join or a window function.

```sql
, weekly_aggregation AS (
SELECT
ticket_week,
project_id,
debt_category,
COUNT(id) AS ticket_count,
SUM(story_points) AS total_points
FROM design_debt_tickets
GROUP BY 1,2,3
)
, rolling_calculation AS (
SELECT
ticket_week,
project_id,
debt_category,
total_points,
SUM(total_points) OVER (
PARTITION BY project_id, debt_category
ORDER BY ticket_week
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
) AS rolling_6wk_points,
AVG(total_points) OVER (
PARTITION BY project_id, debt_category
ORDER BY ticket_week
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
) AS rolling_6wk_avg_points
FROM weekly_aggregation
)
SELECT * FROM rolling_calculation
ORDER BY ticket_week DESC, project_id, debt_category;
```

**Step 3: Operationalizing the Data Product**

This query forms the core of a dbt model, which can be materialized as a table or incremental model. From there, it can be:
* Connected to Looker/Tableau/Power BI for a dynamic dashboard.
* Scheduled via dbt Cloud or Airflow to refresh daily.
* Augmented with additional dimensions from other Granola tables (e.g., `sprints`, `users`).

The key limitations we circumvented were:
* The inability to define complex common table expressions (CTEs) in the builder.
* The lack of support for advanced window functions (`ROWS BETWEEN` clause).
* The need for a reusable data asset that could be joined to other business data outside Granola.

This pattern is applicable to any complex, multi-project metric requiring time-series analysis and custom categorization. While Granola's builder is perfect for 80% of reporting needs, the remaining 20% often requires a direct, code-based approach to the underlying data.

- dan