M
Data · AI · Automation

Insurance Agency Analytics — AMS360 + Zoho CRM

Power BI report mock · Sample data
Prepared by MyData Insights
Insurance Agency Analytics.pbix
Date
FY 2026 YTD
Branch
All (4)
Producer
All (12)
Line of Business
All (9)
Written Premium YTD
$42.8M
▲ 9.4% vs LY
Commission Revenue
$5.61M
▲ 7.1% vs LY
Policies in Force
18,742
▲ 612 net
Retention Rate
91.3%
▼ 0.8 pts
New Business WP
$6.9M
▲ 14%
Policies / Client
1.62
cross-sell depth
Written Premium by Month — New vs Renewal⤢ ⋯
Premium by Line of Business⤢ ⋯
Top Carriers by Written Premium⤢ ⋯
CarrierWPShareLoss Ratio
Travelers$8.9M54%
The Hartford$6.4M49%
Chubb$5.1M63%
Nationwide$4.3M51%
Liberty Mutual$3.6M72%
Producer Book — Top 5⤢ ⋯
ProducerBook WPRetentionNew Biz
K. Alvarez$9.1M94%$1.8M
D. Cho$7.4M92%$1.3M
M. Okafor$6.8M88%$1.1M
R. Singh$5.2M91%$0.9M
T. Nguyen$4.0M84%$0.7M
New Business WP
$6.9M
▲ 14%
Policies Bound YTD
2,184
▲ 187
Hit / Close Ratio
34.6%
▲ 2.1 pts
Avg Days to Bind
11.4
▼ 1.9 days
Avg New Premium
$3,160
per policy
Quotes Outstanding
1,142
in pipeline
New Business WP by Producer⤢ ⋯
New vs Renewal Premium by Month⤢ ⋯
Quote-to-Bind Funnel by Line of Business (Zoho quotes → AMS360 bound)⤢ ⋯
Line of BusinessQuotesBoundHit RatioBound WPAvg Days
Commercial Auto61224139.4%$1.9M9.2
General Liability54820337.0%$1.4M12.8
Commercial Property43012829.8%$1.6M15.1
Workers' Comp38814236.6%$1.1M10.4
Personal Lines1,02047046.1%$0.9M6.1
Retention Rate
91.3%
▼ 0.8 pts
Premium Retention
93.1%
▲ 0.4 pts
Renewing < 90 days
1,946
$8.2M WP
At-Risk Premium
$1.4M
217 policies
Lost Policies YTD
1,631
$3.0M WP
Saves YTD
308
$1.1M WP
Retention Rate by Line of Business⤢ ⋯
Cancellation / Non-Renewal Reasons⤢ ⋯
Renewal Pipeline — Next 90 Days (At-Risk First)⤢ ⋯
ClientLineRenewal DatePremiumRate ChgRiskProducer
Cedar Logistics LLCComm. Auto24-Jul-2026$84,200+18%HighK. Alvarez
Harbourpoint MfgProperty02-Aug-2026$61,500+22%HighD. Cho
Vela Foods IncWorkers' Comp11-Aug-2026$47,900+9%MedM. Okafor
Northline RetailGen. Liability19-Aug-2026$28,400+4%MedR. Singh
Aster Dental GroupPackage28-Aug-2026$19,600−2%LowT. Nguyen
Commission Revenue
$5.61M
▲ 7.1%
Avg Commission Rate
13.1%
of WP
Contingency / Bonus
$486K
▲ 11%
Producer Payout
$1.74M
31% of comm.
Direct : Agency Bill
68:32
by premium
Payout Variances
2
flagged
Commission Revenue by Carrier⤢ ⋯
Commission by Line of Business⤢ ⋯
Producer Payout Reconciliation — Earned vs Paid⤢ ⋯
ProducerEarned Comm.Split %Payout DuePaidVariance
K. Alvarez$412,00035%$144,200$144,200$0
D. Cho$338,00035%$118,300$116,900−$1,400
M. Okafor$291,00030%$87,300$87,300$0
R. Singh$228,00030%$68,400$71,100+$2,700
T. Nguyen$176,00028%$49,280$49,280$0
Open Pipeline
$4.2M
premium
Weighted Pipeline
$1.61M
stage-adj.
Open Deals
438
▲ 52
Win Rate
31.8%
▲ 1.4 pts
Cross-Sell Targets
2,910
1-policy clients
Avg Deal Size
$9,590
premium
Pipeline by Stage (Zoho CRM Deals)⤢ ⋯
Won Premium by Lead Source⤢ ⋯
Cross-Sell Worklist — Existing Clients, One Policy Only⤢ ⋯
ClientCurrent LineCurrent PremiumSuggested Cross-SellEst. PremiumOwner
Cedar Logistics LLCComm. Auto$84,200Workers' Comp~$38,000K. Alvarez
Harbourpoint MfgProperty$61,500General Liability~$22,000D. Cho
Vela Foods IncWorkers' Comp$47,900Commercial Auto~$31,000M. Okafor
Aster Dental GroupPackage$19,600Cyber Liability~$6,500T. Nguyen
‹ ›
Design note
Report Design · Annotated

Dashboard Wireframes

Page-by-page layout intent for the Power BI report, before visuals are styled. Each block notes the visual type, the source system and the measure behind it.

Page 1 — Executive Summary
Audience: Principal / Agency owner · Refresh: nightly · Slicers: date, branch, producer
Card
Written Premium
SUM(FactPolicyTxn[WrittenPremium])
Card
Commission Rev
SUM(FactCommission[Amount])
Card
Policies in Force
DISTINCTCOUNT active
Card
Retention %
Renewed ÷ Eligible
Line + ColumnWritten Premium trend — new vs renewal, 12 months
DonutPremium by Line of Business
TableTop carriers — WP, share, loss ratio
TableProducer book — WP, retention, new biz
Design note: Card visuals carry prior-year comparison. Loss-ratio cells conditionally formatted (green <55%, amber 55–70%, red >70%). Every visual respects the branch/producer slicer so a branch manager sees only their book.
Page 2 — Production & New Business
Audience: Sales manager · Cross-source: Zoho quotes joined to AMS360 bound policies
Card
New Business WP
Card
Policies Bound
Card
Hit Ratio
Card
Days to Bind
BarNew business WP by producer
Stacked ColumnNew vs renewal premium by month
MatrixQuote-to-bind funnel by line — quotes, bound, hit ratio, avg days
Cross-source join: Zoho Deal matched to AMS360 Policy on ClientKey + effective date (±14 days). Unmatched quotes flagged for CRM hygiene — a data-quality by-product the agency gets for free.
Page 3 — Retention & Renewals
Audience: Producers / account managers · Drives a 90-day at-risk worklist
Card
Retention %
Card
Premium Retention
Card
Renewing <90d
Card
At-Risk Premium
LineRetention rate by line of business
BarCancellation reasons (reason codes)
Table + flagsRenewal pipeline next 90 days, at-risk first
Leading indicator: at-risk logic = premium change >10% OR open loss/claim activity OR reduced contact in Zoho. Table exports via a Power Automate flow that opens a task in Zoho for each producer.
Pages 4 & 5 — Commissions & Sales Pipeline
Audience: Finance (commissions) · Sales leadership (pipeline / cross-sell)
BarCommission by carrier
BarCommission by line of business
FunnelZoho pipeline by stage
MatrixProducer payout reconciliation — earned vs paid, variance flag
TableCross-sell worklist — one-policy clients + next-best line
Row-level security: a producer role sees only their own commission and pipeline rows; principals and finance see all. Enforced in the semantic model via a UserProducer mapping table.
Solution Architecture

Source-to-Insight Data Flow

Medallion architecture on Microsoft Fabric / Azure. AMS360 and Zoho CRM land raw in Bronze, are conformed and joined in Silver, and served as a star-schema Gold semantic model to Power BI, with Power Automate closing the loop back into Zoho.

SOURCES INGEST BRONZE (raw) SILVER (conformed) GOLD + SERVE AMS360WSAPI (SOAP) /Data Lake API (OData) Zoho CRMBulk Read API v6OAuth 2.0 Reference dataCarrier / LOB / GL map Azure DataFactoryScheduled pipelinesIncremental / CDCFabric DataPipelines OneLakeLakehouseraw_ams360.*raw_zoho.*Delta Parquetfull history,as-landed ConformedCleansed & typedClient de-dupeZoho↔AMS360client match keyBusiness rules(dbt / notebooks) Star SchemaFacts + DimensionsDirect Lakesemantic modelDAX + RLS Power BIReports & app Power Automatealerts → Zoho tasks Closed loop — at-risk & cross-sell tasks written back to Zoho CRM Cross-cuttingMicrosoft Entra ID auth · Purview lineage & catalog · Key Vault secrets · CI/CD (Fabric deployment pipelines) · data-quality tests on each loadRefresh: AMS360 nightly incremental + Zoho every 4 hours · Monitoring & failure alerts to Teams
Source / serveIngestBronzeSilverGoldAutomation loop
Why this shape: a medallion lakehouse keeps a full, replayable history of both systems (Bronze), isolates the messy client-matching and business rules in one governed layer (Silver), and serves Power BI over Direct Lake with no import refresh bottleneck (Gold). The same Silver client key powers both the cross-sell list and the retention worklist.
Semantic Layer

Star-Schema Data Model

Gold-layer dimensional model. Two fact tables (policy transactions and commissions) surrounded by conformed dimensions shared across every report page. Zoho pipeline attaches through the same Client and Date dimensions.

FactPolicyTransactionPK PolicyTxnKeyFK ClientKey, PolicyKeyFK CarrierKey, ProducerKeyFK LOBKey, DateKeyWrittenPremium, TxnTypeEffDate, ExpDate, Status FactCommissionPK CommissionKeyFK PolicyTxnKey, CarrierKeyFK ProducerKey, DateKeyCommissionAmt, RateBillType, ProducerSplit DimDatePK DateKeyDate, Month, Qtr, FYIsRenewalMonth DimClientPK ClientKeyName, Segment, IndustryAMS360_CustId · Zoho_AccId DimCarrierPK CarrierKeyCarrier, Group, AMBestContingencyTier DimProducerPK ProducerKeyProducer, Branch, TeamSplitDefault DimLineOfBusinessPK LOBKeyLOB, Group (Comm/Personal)RiskCategory DimPolicyPK PolicyKeyPolicyNo, Type, BillMethodOrigEffDate FactPipeline (Zoho)PK DealKey · FK ClientKeyFK ProducerKey, DateKeyStage, PremiumValueProbability, LeadSourceQuoteDate, ExpectedClose ● = one-to-many
Fact (AMS360)Fact (Zoho pipeline)Conformed dimensionActive relationship
The join that unlocks it: DimClient carries both AMS360_CustId and Zoho_AccId, resolved in the Silver layer. That single conformed key is what lets a Zoho deal, an AMS360 policy and a commission line all describe the same customer — and it is what makes cross-sell, true hit ratio and unified retention possible.