Data Studio insurance dashboards for brokers and agencies

Which numbers does an insurance broker actually report each month?

For most brokers and agencies it comes down to four questions: how many members or policies each scheme gained and lost, why they left, what is coming up for renewal, and what commission each insurer owes. The answers live in different places, which is why month end takes so long.

The insurance dashboard we built in Data Studio for a broker focuses on membership and churn per client scheme. It shows members joined, exited and retained, churn rate by client and by customer type, and a filterable member list so an account manager can see exactly who left a scheme and when.

Case study: how did an Australian brokerage get from hours to minutes?

An Australian insurance brokerage received messy .xls files from multiple insurers, each in its own layout. Someone on the team spent four to five hours rebuilding the report every cycle, and reporting accuracy sat at about 75%.

We automated the pipeline. Files now go to a Google Drive folder, are mapped into a central Google Sheet, have the brokerage's business rules applied, and feed the Data Studio report directly. Report time fell to under 5 minutes, accuracy rose to 99%, and visibility across insurers went from limited to real time, with filters.

How do you turn insurer bordereaux into one clean table?

By mapping every insurer's layout to one standard set of columns before anything reaches a chart. Bordereaux and statements arrive as .xls or .xlsx with merged header rows, totals mixed into data rows and dates stored as text. A Data Studio blend cannot fix that; it can join at most five sources and only on exact matches.

We build a mapping table per insurer that says which column means policy number, member ID, start date, end date, premium and commission. When an insurer changes its template, your team updates one row in the mapping sheet instead of calling us. Larger books, or ones with several years of history, go into BigQuery so the report stays quick.

• Standard columns: insurer, scheme, client, member or policy ID, customer type, start, end, status, premium, commission.
• Rules applied once: how a lapsed policy is counted, what makes a member retained, which date defines an exit.
• Exceptions sheet: rows that fail a rule are listed for review, not silently dropped.

How is churn per client scheme calculated in an insurance dashboard?

Churn rate is members exited in the period divided by members at the start of the period, calculated per scheme. The hard part is agreeing what counts as an exit. A member who moves between two of your schemes, or a policy cancelled and reissued on the same day, should not inflate churn.

We encode those rules in calculated fields, typically a CASE statement over status and dates, and show the definition on the page. Breaking churn by customer type then shows whether a scheme is losing its individual members, its family members or a whole employer group.

Can renewals and commission sit in the same report?

Yes, if the policy admin export carries renewal dates and the insurer statements carry commission lines. We add a renewals page that lists policies due in the next 30, 60 and 90 days by account manager, and a commission page that reconciles what each insurer paid against what the policy data says was due.

Commission reconciliation is where brokers can find missing money. When the expected and received figures sit side by side by insurer and month, an unpaid or short-paid line is visible without anyone building a pivot table. Clawbacks on cancelled policies get their own column, so a negative month is explained rather than queried.

Who should see member-level data?

Only the people who need it. The member list is useful to account managers and less appropriate for a board pack, so summary pages show counts and rates while the member-level page is shared with a smaller group. Where client schemes are run by different account teams, the data source can filter rows by the viewer's email so each team sees only its own schemes.

We set data source credentials deliberately, so viewers see the report without access to the underlying Drive folder, and we keep ownership on a shared account. Scheduled delivery can email a PDF summary to principals each month. As a data studio agency working with brokers, we follow your own privacy and data handling policies; we do not give legal or compliance advice.