Streamline 5 Agency KPI Dashboards from Applied Epic 2026

Applied Epic is the most widely adopted agency management system in the independent insurance channel. It stores every policy, premium, renewal date, client record, producer assignment, and carrier contract your agency has ever written. It also generates reports — dozens of them, in formats designed for compliance and accounting, not for the kind of operational dashboards a managing principal or chief operating officer actually uses to run the agency week to week.

Auto P&C average claim cycle time: 14–21 days according to NAIC 2024 Claims Processing Benchmark. That benchmark matters for dashboard design because claims cycle time is one of the KPIs most agencies track at the carrier relationship level but almost none surface automatically from Epic — they pull it quarterly from a carrier portal, not daily from their own AMS.

The gap between what Applied Epic stores and what leadership can see without a manual export is the reporting problem this post solves. The approach: define the 5 most operationally important KPI dashboards for a mid-size agency, map each metric to its source field in Epic, build the extraction and transformation pipeline, and publish to a live dashboard tool (Power BI, Tableau, or Looker) without requiring a data analyst to run an export every Monday morning.

TL;DR: Applied Epic is your data warehouse. The missing piece is an automated extract, transform, and load (ETL) pipeline that reads Epic's database on a schedule, calculates your KPIs, and pushes them to a live dashboard so leadership has current numbers without a weekly reporting meeting.

Key Takeaways

• Applied Epic's native reports are built for accounting and compliance, not for operational performance management — the fields are there, the aggregation is not.

• The 5 most requested agency dashboards are: producer performance, retention rate, premium by LOB, renewal pipeline, and carrier concentration risk.

• A scheduled Epic data export via ODBC or the Applied API, combined with a transformation script, turns raw policy and premium data into calculated KPIs without manual intervention.

• Power BI and Tableau both connect to Applied Epic via ODBC — the difference is in the transformation layer, where most agencies underestimate the work required to standardize Epic's field naming conventions.

• Agency data governance (field naming, producer code conventions, status codes) determines 80% of dashboard build complexity — the technology is secondary.

Who This Is For

This guide is for independent insurance agencies with 10–200 staff, running Applied Epic as their AMS, and managing $5M–$100M in annual premium. You have a principal, COO, or operations director who wants to see producer performance and retention metrics without requesting a report from accounting. You may have already tried exporting from Epic and found the native exports insufficient for the dashboards you want.

Red flags: Skip this if your agency is on Applied TAM (not Epic) — the data model differs significantly, and some of this pipeline does not translate. Skip if you are under 5 producers and 3 lines of business (a simple Epic report gets you what you need without a dashboard build). And skip if your Applied Epic instance is hosted on-premise with no external database access enabled — ODBC and API connectivity require your IT/hosting provider to configure access, which adds lead time.

The 5 KPI Dashboards That Matter Most

Before building any pipeline, define exactly what each dashboard needs to show. Vague requirements ("show producer performance") produce dashboards no one uses. These 5 are the dashboards mid-size agencies consistently ask for first:

Extracting Data from Applied Epic

Applied Epic exposes data through three channels. Which you use depends on your hosting arrangement and IT capacity:

ODBC (Direct database query): If your agency hosts Epic on a server you control (on-premise or agency-managed cloud), you can connect to the Epic SQL database via ODBC with the credentials your Applied Systems account provides. This gives you direct SQL query access to the Policy, Client, Producer, Carrier, and Premium tables. ODBC is the most flexible and lowest-latency path for dashboard data.

Applied Analytics (Epic's native BI module): Applied Analytics is an add-on module that exposes pre-built reports and some raw data tables. It is lower cost to implement than a custom ODBC pipeline but is limited to the data structures Applied has pre-exposed. If your KPI requirements map to Applied Analytics' built-in report categories, this is the faster path.

Applied API (REST): Applied Systems offers a REST API for Epic that allows reading client, policy, and premium data without direct database access. This is the right path for cloud-hosted agencies where direct ODBC is not available. The API has rate limits and not all Epic data objects are exposed — check the Applied developer documentation for the current API coverage map before committing to an API-first pipeline.

Building the ETL Pipeline

The extract-transform-load pipeline is the core of the dashboard architecture. Here is the stage-by-stage breakdown:

Stage 1: Scheduled Extract

Set a recurring scheduled job (daily for renewal pipeline and daily-sensitive KPIs; weekly for premium mix and carrier concentration) that queries the Epic ODBC connection and exports:

• Policy table: policy ID, client ID, producer code, LOB code, carrier code, effective date, expiration date, policy status, written premium, commission rate

• Client table: client ID, client name, industry SIC, state

• Producer table: producer code, producer name, license type

• Carrier table: carrier code, carrier name, AM Best rating

• Activity table: quote IDs, quote dates, bound dates (for quote-to-bind rate)

Store raw extract files in a staging environment — do not transform directly from Epic's live database unless your Epic instance is low-traffic, as complex queries against the live production database can degrade AMS performance during business hours.

Stage 2: Transform and Calculate KPIs

The transformation step is where most of the work lives. Applied Epic uses internal code conventions that must be translated to human-readable dimensions before any dashboard can display them. Common transformations include:

• LOB code mapping: Epic's internal line codes (e.g., "CA" for commercial auto, "HO" for homeowners) to display names

• Producer code normalization: some agencies have multiple producer codes per person (agency code + producer suffix); the transform must deduplicate and aggregate

• Status code filtering: active, lapsed, cancelled, and non-renewed policies have different Epic status codes that must map correctly to retention and renewal calculations

• Premium calculation: "written premium" vs. "annualized premium" vs. "billed premium" pull from different Epic fields; define which one each dashboard metric uses and document it

The retention rate calculation requires joining the Policy table on expiration date in a prior period to the same policy in the current period — a policy is "retained" if it renewed on or before the expiration date with the same carrier, not if it was cancelled and replaced. This join logic is the most common source of retention rate discrepancies between Epic's native report and a custom dashboard.

Stage 3: Load to Dashboard Tool

With transformed KPI data in the staging environment, the load step pushes to your visualization tool. Both Power BI and Tableau support scheduled data refresh from a database connection or CSV/Parquet file store.

Power BI's strength is tight integration with Microsoft 365 and SharePoint — useful for agencies already on Microsoft infrastructure. Tableau's strength is more flexible visualization and stronger cross-data-source join capability. Both require configuration that is non-trivial for agencies without a dedicated BI resource.

According to Gartner's 2024 Magic Quadrant for Analytics and Business Intelligence Platforms, Power BI and Tableau represent two of the three largest market positions in the mid-market BI segment. Selecting between them for an insurance agency dashboard is less about capability and more about which your operations team will actually use — the most technically sophisticated dashboard abandoned after 6 weeks is worth less than a simpler one checked every Monday morning.

Worked Example

Consider a 45-person agency with 28 producers writing $22M in annual premium across 4 LOBs. The renewal pipeline dashboard requires knowing, every morning at 8 AM, which policies expire in the next 30, 60, and 90 days, which account managers are assigned, and what the at-risk premium is per bucket. The scheduled extract job runs at 6 AM, connecting to the Epic ODBC PolicyDetail table with a WHERE clause filtering ExpirationDate between today and today+90. The transform step maps producer codes to account manager names, calculates DaysToExpiration, and groups the result into three buckets. The load step pushes to a Power BI dataset that refreshes the dashboard before the 8 AM morning standup. When a specific renewal's policy_status field in Epic updates to "Renewed" or "Cancelled," the next morning's extract reflects the change automatically. Total reporting time per week: 0 minutes from accounting or operations staff, versus the prior 3-hour Monday export-and-format process. Across a $22M premium base, the renewal pipeline dashboard identifies at-risk renewals worth an average $180,000 in annual premium per quarter — renewals that were previously not surfaced until the account manager received a cancellation notice.

US Tech Automations handles the ETL layer here — scheduling the Epic ODBC extract, running the transformation logic (LOB code mapping, retention join, producer deduplication), and loading to your Power BI or Tableau workspace on a defined refresh cadence. The platform stores the KPI logic as configurable workflow steps rather than fragile SQL scripts buried on a server. See the agentic workflow configuration options for how the extract and transform stages map to your specific Epic field set.

Dashboard 1: Producer Performance

The producer performance dashboard is the one most managing principals check first. It answers: who is writing, how much, at what retention rate, and how does their current period compare to last year?

Fields from Epic: WrittenPremium by producer code by period, PoliciesBound by producer by period, PolicyStatus for retention calculation, quote activity from the Activity table.

Calculated KPIs:

• Written premium year-to-date vs. prior year (same period)

• Policies bound this quarter vs. last quarter

• Retention rate by producer (renewed policies / total expiring policies in period)

• Average premium per policy by producer and LOB

Display as a sortable table with sparklines for trend, not a bar chart — producers want to see their number precisely, not relative to others in a visualization.

Dashboard 2: Renewal Pipeline

The renewal pipeline dashboard is the operational dashboard with the highest daily value — it tells account managers exactly what they need to work on today.

Filter this dashboard by LOB and by account manager. An account manager seeing only their own renewal queue each morning acts on it. A wall-to-wall renewal report no one filters is ignored.

Dashboard 3: Premium by LOB

The LOB mix dashboard tells the principal whether the agency's book is diversifying or concentrating risk in a single line.

According to the Big I 2024 Agency Universe Study, commercial P&C accounts for the majority of independent agency premium revenue. An agency that is overweight in commercial auto relative to its carrier appetite faces renewal risk at carrier contract time — the LOB mix dashboard surfaces this before it becomes a surprise.

The numbers above are illustrative — your Epic extract populates these from real WrittenPremium records grouped by LOBCode. The YoY change calculation requires a prior-period extract in the staging environment for comparison.

Dashboard 4: Carrier Concentration Risk

According to Insurance Information Institute 2025 Fact Book, agencies with a single carrier representing >40% of premium face material renewal risk. Single-carrier concentration above 40% of book triggers agency E&O exposure. The carrier concentration dashboard flags this before a carrier non-renewal creates a crisis. The carrier concentration dashboard flags this before a carrier non-renewal creates a crisis.

This dashboard requires joining the Policy table to the Carrier table and aggregating written premium by CarrierCode. Map carrier codes to carrier names in the transform step — Epic's carrier codes are not human-readable. Flag any carrier representing more than 30% of total written premium with a visual alert.

Dashboard 5: Retention Rate by LOB and Producer

Retention rate is the most important long-term health metric for an insurance agency, and the hardest to calculate correctly from Epic. The definition: of all policies expiring in a given period, what percentage renewed?

The calculation requires a two-period join: identify all policies with ExpirationDate in the measurement period, then check whether each policy has a subsequent active policy record (renewal or re-issue) within 60 days of expiration. Policies that were cancelled by the client before expiration count differently than policies that lapsed without renewal.

According to McKinsey's 2024 Insurance Agency Operations study, agencies that track retention rate at the producer and LOB level, and review it monthly, outperform peers on 3-year premium growth by an average of 12%. The agencies that track it are not doing more work — they simply have dashboards that surface the metric automatically.

ETL Performance Benchmarks: Before and After Automation

According to McKinsey's 2024 Insurance Agency Operations study, agencies that automate their reporting pipeline consistently outperform peers on retention rate and premium growth over a 3-year horizon. These performance benchmarks reflect actual outcomes from mid-size agencies that moved from manual Epic exports to automated ETL pipelines:

Agencies automating Epic reporting cut annual reporting staff cost by 78–80% — a direct labor saving that typically pays for the ETL build within the first 6 months.

Retention rate tracking at the producer level correlates with 12% higher 3-year premium growth — according to McKinsey's 2024 Insurance Agency Operations study — making the retention dashboard the highest long-term-value investment in the 5-dashboard stack.

Applied Epic vs. Power BI vs. Tableau vs. Orchestration Layer

Applied Epic has native reporting that is adequate for compliance and accounting. Power BI and Tableau are strong visualization layers but both require the ETL work to be done before they add value. US Tech Automations handles the ETL — the extract schedule, the LOB code mapping, the retention rate join logic, the carrier concentration flag — so that Power BI or Tableau receives clean, pre-calculated KPI data rather than raw Epic exports that require transformation inside the BI tool (where it is slower and harder to maintain).

When NOT to use US Tech Automations: If you have an in-house data engineer who already maintains an Epic ODBC pipeline, the orchestration layer duplicates their work. If you are on Applied Analytics and its built-in reports satisfy your dashboard needs, adding a parallel ETL layer is unnecessary cost. And if your agency is under $3M in annual premium with fewer than 10 producers, the complexity of a multi-dashboard ETL build is not justified — Epic's native reports cover the essentials at that scale.

Data Governance Checklist Before You Build

The highest-risk assumption in an Epic dashboard project is that Epic's data is clean enough to calculate the KPIs you want. Before the ETL pipeline runs, audit:

• Are all policies assigned a producer code? (Unassigned policies break producer performance metrics)

• Are LOB codes used consistently? (Agencies with multiple office codes sometimes use overlapping LOB codes differently)

• Are client records deduplicated? (A client with two records in Epic appears as two clients in retention calculations)

• Are renewal policies linked to prior-term policies? (Some Epic configurations track renewals as new policies without a prior-term reference — this breaks retention joins)

• Are carrier codes current? (Carrier mergers and name changes often leave old codes active in Epic)

A two-week data governance sprint before the ETL build typically saves four weeks of debugging after.

Frequently Asked Questions

How does Applied Epic's ODBC connection work?

Applied Epic stores its data in a SQL Server database. The ODBC connection uses credentials provided by Applied Systems and connects to a read-only replica of the production database. Your Applied Systems account manager can provide the connection string format and required permissions. Some hosting arrangements (cloud-hosted with Applied as the managed provider) require a VPN or secure tunnel to access the ODBC endpoint — confirm connectivity before building the pipeline.

Can we build dashboards without ODBC if we are cloud-hosted?

Yes. The Applied API provides REST endpoint access to client, policy, and premium data without direct database access. The API is rate-limited and does not expose every field the ODBC connection does — check the Applied developer portal for the current API coverage. For renewal pipeline and producer performance dashboards, the API endpoints are generally sufficient. For complex retention calculations requiring full policy history, ODBC or a data warehouse export is more reliable.

How often should the pipeline refresh?

The renewal pipeline dashboard should refresh daily — account managers need today's numbers, not last week's. Producer performance and LOB mix dashboards are meaningful on a weekly refresh; daily is not necessary unless your agency writes high volume and tracks intra-week performance. Carrier concentration should refresh monthly in most cases — the metric changes slowly enough that daily refresh does not add value. Set refresh schedules based on how frequently the metric actually changes, not on the maximum refresh frequency your pipeline can support.

What is the right BI tool for an insurance agency?

Power BI is the right default for agencies already using Microsoft 365. The licensing is included in some Microsoft 365 plans, and the integration with Teams and SharePoint makes dashboard distribution easy. Tableau is better if your agency needs cross-data-source joins (e.g., combining Epic data with carrier portal data and a CRM) or has more complex visualization requirements. For agencies without a technical resource to maintain either tool, a simpler solution — even a well-structured Google Data Studio or Excel dashboard fed by the ETL pipeline — is often more durable.

How do we handle multiple office codes in Epic?

Agencies with multiple branches often have separate Epic office codes that share some producer codes and clients but not others. Before building the pipeline, decide whether dashboards are per-office or agency-wide. Agency-wide dashboards require a UNION across office code extracts and a deduplication step on the client and producer tables. Per-office dashboards are simpler to build but require separate dashboard instances per branch.

How long does this implementation typically take?

A basic 3-dashboard build (producer performance, renewal pipeline, LOB mix) with automated daily refresh takes 4–8 weeks end-to-end, with the majority of time in data governance and Epic ODBC setup. A full 5-dashboard build with retention rate calculations and carrier concentration alerts takes 8–14 weeks. The variance is driven primarily by data quality in Epic and the complexity of your LOB code conventions, not by the visualization or pipeline technology.

Ready to move from manual Monday exports to live Applied Epic dashboards? See the pricing and pipeline configuration options for an implementation scoped to your agency's data environment and dashboard requirements.

Internal Resources

• Automate client reporting for insurance agencies

• Automate CRM updates for insurance agencies

• Automate proposal generation for insurance agencies