Start from the questions
Reporting projects fail when they start from the data. We start from the owner: "What five questions do you ask yourself every month?" Typical answers: which sources produce paying customers, how fast do we respond, where do leads drop out, how healthy is the pipeline, and how many customers come back. Every report we build answers one of those. Anything else waits.
What to export and how
A nightly job pulls four objects from the GoHighLevel API: contacts (with custom fields and tags), opportunities (with stage history where available), appointments (with status), and conversation summaries (first response time per contact). Each lands in its own sheet or table with a snapshot date. Google Sheets works up to a few tens of thousands of rows; beyond that a small warehouse like BigQuery and Looker Studio on top. The job uses the webhook and API habits from Webhooks 101.
A tiny data model
Four tables joined on contact ID and opportunity ID. The most important field is first-touch source, stored permanently on the contact when it was created and never overwritten, so a customer who later clicked a retargeting ad is still attributed to the campaign that found them. Revenue comes from won opportunities, with refunds as negative rows from the payment system when available.
Append a nightly snapshot instead of replacing the table. That is what lets you answer "how did the pipeline look on the first of last month?", which the live CRM cannot.
The five reports
- Source to revenue: leads, bookings, wins and revenue by first-touch source, monthly. The marketing budget conversation in one chart.
- Response time: median first response, by weekday and hour. Shows exactly when leads wait.
- Booking funnel: lead to booked to attended to won, with drop-off at each step.
- Pipeline health: value by stage and age, with stale deals highlighted, mirroring the pipeline automations.
- Retention: customers with a second purchase or appointment within twelve months, by cohort.
Keeping it honest
Once a month, totals in the report are compared with totals in GoHighLevel: contacts created, opportunities won, appointments completed. Any gap is investigated and explained on the report itself. Numbers people trust get used; numbers that were wrong once get ignored forever.
Key takeaways
- Write the five questions the owner actually asks before building anything.
- Export contacts, opportunities, appointments and conversations nightly into a spreadsheet or warehouse.
- One table per object, joined on IDs, with the first-touch source stored permanently.
- Five reports cover most businesses: source to revenue, response time, booking funnel, pipeline health, retention.
- Reconcile totals against GoHighLevel monthly so trust in the numbers holds.