Hey everyone, I know this is a bit off-topic from pure ETL tools, but I just finished setting up a custom reporting dashboard for our project data, and it lives inside Basecamp.
I needed a way to visualize task completion rates and milestone dependencies across projects. Since all our project management happens in Basecamp, I built a pipeline to get the data out and into something I could chart.
My (probably over-engineered) setup:
* Used Airbyte to extract data from the Basecamp API.
* Loaded it into a BigQuery staging table.
* Wrote a dbt model to clean and aggregate the data.
* Used Looker Studio to connect to BigQuery and build the dashboard.
Here's a snippet of the core dbt model that calculates weekly completion:
```sql
-- basecamp_tasks_weekly.sql
with task_updates as (
select
project_id,
date_trunc(updated_at, week) as week,
countif(completed) as tasks_completed
from {{ ref('stg_basecamp_todos') }}
group by 1, 2
)
select
week,
project_id,
sum(tasks_completed) over (
partition by project_id
order by week
) as cumulative_completed
from task_updates
```
It works, but I'm nervous about maintaining it. Has anyone else tried building custom analytics on top of Basecamp data? I'm wondering if I should have used a different approach for the orchestration part.