The challenge

This social media agency ran on a custom CRM. It fit their process well, but reporting didn’t. The only way to get numbers out was a monthly Excel export the developer had hacked together. That meant:

• Hours of manual work, lots of copy/paste, and plenty of errors

• Gaps in the data because some relationships couldn’t be exported

• Decisions made on last month’s numbers, not this week’s

As they grew, this got worse. Every new question needed a fresh pull. Different teams used different definitions for the same KPI. Adding other sources (ads, billing, support) felt too hard, so they didn’t.

What we built

We kept the CRM as-is and built the analytics around it.

• Reliable pipeline

• A daily batch load from the CRM into BigQuery. It’s simple, predictable, and cheap to run. We added basic monitoring to Slack so failures are obvious and easy to fix.

• A warehouse that makes sense with a trustworthy transformation layer

• Raw → staging → intermediate → marts. Clean models for accounts, contacts, deals, and activities. We rebuilt the missing links the Excel export couldn’t handle. KPIs live in the models, not in someone’s spreadsheet.

• dbt Core on Cloud Run. Versioned, tested, and documented. If something changes in the CRM, we see it, and tests catch bad data before it hits a dashboard.

• Self-service, so the agency can develop and research on its own

• Clear docs, short trainings, and a steady metrics layer. Most questions now get answered in the dashboards. We stay available for small tweaks and ops.

Why this works

• It’s cost-effective. BigQuery + Cloud Run keeps idle costs low. Daily loads are enough for their decisions; we can speed up later if needed.

• It scales. Adding Meta Ads, Google Ads, billing, or support follows the same pattern. No rebuild.

• It’s consistent. One place for metrics. No more “which number is right?”

• It’s future-proof. With clean models, they can try LLM-based querying later without creating chaos.

What changed

• Freshness: data updates daily before the team starts

• Time: monthly reporting went from a long afternoon to under an hour

• Coverage: all core CRM entities are modeled with proper relationships

• Adoption: most reporting now happens without a data person in the loop