Session 23: Looker Studio for Executive Reporting

1. Executive Reporting vs. Operational Dashboards

The most common mistake in insurance dashboard design is building an operational dashboard and calling it an executive dashboard. The two serve fundamentally different purposes, audiences, and decision contexts. An executive dashboard that shows the "claims processed per adjuster" chart is not an executive dashboard — it is an operational dashboard that an executive will ignore. Understanding the difference is the first step in building a dashboard that actually gets used by senior leadership.

2. Looker Studio Interface & Setup

Looker Studio (formerly Google Data Studio) is a free, browser-based data visualisation tool that integrates with the Google ecosystem — Sheets, BigQuery, Analytics, and 800+ other data sources through connectors. For Indian insurers, Looker Studio is particularly useful for: (a) building lightweight executive dashboards that can be shared easily, (b) incorporating Google Sheets data that the finance team already maintains, (c) embedding in Google Sites for intranet access, and (d) creating scheduled email PDF snapshots for board reporting.

2.1 Setting Up the Data Source

Step 1 — Prepare the data in Google Sheets: The executive dashboard data should be aggregated to the monthly level before importing to Looker Studio. Create a Google Sheet with the following structure (one sheet per data category):

Sheet 1: 'kpi_monthly'
Columns: month_date, kpi_name, kpi_value, target_value, prior_year_value
Data:
  Jan 2024, GWP Growth (%), 12.5, 15.0, 10.2
  Jan 2024, Combined Ratio (%), 101.2, 98.0, 103.5
  ... (one row per KPI per month)

Sheet 2: 'product_performance'
Columns: month_date, product_line, gwp_cr, loss_ratio, expense_ratio, combined_ratio, growth_yoy
Data:
  Jan 2024, Motor, 45.2, 72.5, 28.0, 100.5, 8.5
  Jan 2024, Health, 28.6, 68.2, 22.5, 90.7, 22.3
  ... (one row per product per month)

Sheet 3: 'channel_performance'
Columns: month_date, channel, new_premium_cr, retention_rate, cac, ltv_cac
Data:
  Jan 2024, Agent, 18.5, 0.82, 3200, 4.2
  ... (one row per channel per month)

Sheet 4: 'market_share'
Columns: quarter, product_line, our_gwp_cr, total_market_gwp_cr
Data:
  Q1 2024, Motor, 145.2, 5200
  ... (one row per product per quarter)

Step 2 — Connect Looker Studio to Google Sheets: In Looker Studio, click "Create" → "Data Source" → Select "Google Sheets" → Choose the spreadsheet and worksheet. Set the data refresh to "On" with a frequency of "Daily" (executive dashboards need daily data freshness, not real-time).

Step 3 — Configure fields: For each data source, verify that: date fields are recognised as "Date" type, numeric fields as "Number" or "Currency" as appropriate, and percentage fields as "Number" with a "%" format. Create calculated fields for derived metrics:

Calculated Field Examples (Looker Studio formula syntax):

-- Variance vs. Target (absolute)
Variance vs Target = kpi_value − target_value

-- Variance vs. Target (%)  :
Var % vs Target = (kpi_value − target_value) / ABS(target_value) * 100

-- Status Indicator:
Status = CASE
           WHEN kpi_value >= target_value THEN "On Track"
           WHEN kpi_value >= target_value * 0.90 THEN "At Risk"
           ELSE "Off Track"
         END

-- Year-to-Date (aggregate):
YTD Value = RUNNING_SUM(kpi_value)

-- Year-over-Year Growth (%)  :
YoY Growth % = (kpi_value − prior_year_value) / ABS(prior_year_value) * 100

3. Executive KPIs for Insurance

Not every metric is an executive KPI. An executive dashboard should contain exactly the metrics that the CEO and board use to assess the strategic health of the business. In insurance, seven KPIs cover the essential dimensions of business performance. An executive dashboard that has more than 7 KPIs is not an executive dashboard — it is a list of metrics without strategic focus.

3.1 The Seven Essential Insurance Executive KPIs

4. Page 1 — Performance Overview

Page 1 answers the question: "Are we on track to achieve our strategic objectives this year?" It is the page the CEO looks at first — and most of the time, it is the only page they look at. Every element on this page must serve this single question.

4.1 Page Layout

LOOKER STUDIO EXECUTIVE DASHBOARD — PAGE 1: PERFORMANCE OVERVIEW

┌─────────────────────────────────────────────────────────────────────┐
│ PERFORMANCE OVERVIEW  FY 2026            [Date Range: ▼] [Compare: ▼]
├───────────┬───────────┬───────────┬───────────┬───────────┬──────────┤
│ GWP Growth│ Combined  │ Claim     │ Persist-  │ Expense   │ Solvency │
│ ▲ 14.2%   │ Ratio     │ Settlement│ ency      │ Ratio     │ Ratio    │
│ vs plan:  │ 98.6      │ Ratio     │ 78%       │ 26.5%     │ 2.3      │
│ +1.2%     │ vs plan:  │ 94.5%     │ vs plan:  │ vs plan:  │ vs plan: │
│ ▲ vs PY:  │ ▲ -2.1pts │ vs plan:  │ +2%       │ -0.5pts   │ -0.1     │
│ +3.5%     │ (improv)  │ -0.5%     │ (improve)  │ (improv)  │ (stable) │
│ GREEN      │ AMBER     │ GREEN     │ GREEN      │ GREEN      │ GREEN    │
├───────────┴───────────┴───────────┴───────────┴───────────┴──────────┤
│ I N S T I T U T I O N A L   D E T A I L                              │
│ (Optional expanded view when CEO clicks "show details")             │
│                                                                     │
│ Combined Ratio Trend (Last 24 Months)                               │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │   ┌────┐    ┌────┐    ┌────┐    ┌────┐    ┌────┐              │ │
│ │   │    │▄▄▄▄│    │    │    │▄▄▄▄│    │▄▄▄▄│    │              │ │
│ │▄▄▄│    │    │▄▄▄▄│▄▄▄▄│    │    │▄▄▄▄│    │    │▄▄▄▄│▄▄▄▄│▄▄▄▄│ │
│ │   J F M A M J J A S O N D J F M A M J J A S O N D              │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ ▲ Loss Ratio: 72.5%     Expense Ratio: 26.1%     Target: 98.0%    │
│                                                                     │
│ Key Insight: Combined ratio improved 2.1 pts vs. prior year,        │
│ driven by expense reduction. Loss ratio stable. Monitor motor TP    │
│ adequacy — pricing pool update expected in Q3.                      │
└─────────────────────────────────────────────────────────────────────┘

BOTTOM ROW (expandable/collapsible):

┌──────────────┬─────────────────────┬──────────────────────┐
│ KPI Trend    │ Top 5 Products      │ Exception Flags       │
│ Indicators   │ by GWP + Combined   │ (Red = needs action)  │
│ (Mini-spark  │ Ratio — bar/line)   │ ─────────────────     │
│ lines for    │                     │ • Motor TP CR=108 ⚠  │
│ all 7 KPIs)  │ Motor  45%  CR=100  │ • Health persistency  │
│              │ Health 29%  CR=91   │   dropped 4% in 2 mo  │
│              │ Property 12% CR=95  │ ─────────────────     │
│              │ Crop 8% CR=110      │ • IT expenses +15%    │
│              │ Travel 6% CR=72     │   vs plan (cloud cost)│
└──────────────┴─────────────────────┴──────────────────────┘

4.2 Building Page 1 in Looker Studio — Step by Step

1. Scorecards (top row): Add 6 scorecard charts, one for each KPI. Configure: "Metric" = kpi_value, "Comparison" = target_value (shows variance). Enable "Show comparison" and "Compact numbers." Set conditional formatting: green if kpi_value ≥ target_value, amber if ≥ 90% of target, red if below. Add a trend arrow (up/down/flat) as a sparkline at the bottom of each scorecard using a separate time series chart overlaid on the scorecard.
2. Key insight text box: Below the scorecards, add a text box that dynamically shows the "Key Insight" — the most important KPI movement this month. This should be updated monthly by the analyst. In Looker Studio, you can use a "Text" element with a calculated field that picks the KPI with the largest month-over-month change. For example: "Combined ratio improved 2.1 pts — best performance in 18 months. Driven by expense ratio reduction." This single text box makes the dashboard an "executive summary" rather than a data display.
3. Combined Ratio trend chart: Add a time series chart. Dimension: month_date. Metric: combined_ratio. Add reference lines at target (98) and threshold (100). Add a second metric: loss_ratio, plotted on the same Y-axis but with a different style (dashed line). The two lines together tell the story: combined_ratio = loss_ratio + expense_ratio. The gap between them is the expense ratio — executives can see at a glance whether CR movements are driven by claims or expenses.
4. KPI sparklines row: Add 7 mini time series charts (one per KPI) showing the 24-month trend. These are critical for executive decision-making — a KPI that is "green" this month but has been trending downward for 6 months is more concerning than a "red" KPI that is improving. Configure: height = 80px, hide axes, hide legend. Add a trend line (linear) for each sparkline.
5. Top 5 products table: Add a table with: product_line, gwp_cr (formatted as currency), growth_yoy (with conditional green/red), combined_ratio (with conditional formatting: green if < 95, amber if 95–105, red if > 105). Sort by gwp_cr descending. Limit to 5 rows. This table tells the CEO: "which products are generating our premium, and are they profitable?"
6. Exception flags: Add a table filtered to show only metrics where kpi_value is outside the acceptable range. This is the "red flags" section — the CEO's attention should be drawn here first. If the table is empty (all KPIs within range), display a green "All KPIs on track" message. This table is where the executive should START their review, not the scorecards.

6. Dashboard Storytelling

A dashboard that displays data without telling a story is a report, not a decision-support tool. Dashboard storytelling is the practice of structuring the visual elements, annotations, and layout to guide the viewer through a logical narrative: Here is the context → Here is what changed → Here is why it matters → Here is what we should do.

6.1 The Executive Dashboard Story Framework

Every executive dashboard should tell a four-part story. The layout should follow this narrative structure — not the structure of the data source:

1. The Headline (Top row — scorecards): The 7 KPI scorecards with green/amber/red status. An executive should be able to glance at this row and answer: "Is the business healthy or not?" If all 7 are green, the story is "we are on track." If any are red, the exception flags section takes over.
2. The Evidence (Middle — trend charts): The trend charts that support the headline. If the Combined Ratio is amber, the Combined Ratio trend chart should be the first chart the executive sees — showing whether the amber is a temporary blip (you can ignore it) or part of a deteriorating trend (you must address it).
3. The Context (Lower — composition and comparison): The portfolio composition and market context that explains WHY the headline and evidence look the way they do. The Product Profitability Matrix and Market Share chart provide this context.
4. The Call to Action (Bottom — exception flags, recommendations): The actionable output of the dashboard — what needs to be done. The exception flag table lists the specific issues requiring management attention. Each exception should have a recommended action and an owner.

6.2 Strategic Annotation — The "Why" Layer

The most valuable addition to an executive dashboard is not a chart — it is an annotation that explains WHY a KPI moved. A chart showing "Combined ratio improved from 100.5 to 98.6" is useful. A chart with the annotation "Combined ratio improved 1.9 pts — expense ratio down 1.2 pts (IT modernisation savings beginning to flow) + loss ratio stable" is transformative. The annotation turns data into insight.

In Looker Studio, annotations can be added as:

• Text boxes (recommended for monthly executive dashboards): The analyst writes a paragraph summarising the month's key movements. This is placed at the top of the dashboard, directly below the scorecards. It should take 30 seconds to read and cover: what happened, why, and what action is recommended.
• Chart-specific annotations (for recurring trends): Use a separate data source with annotation events (date, label, colour) and add it as a line chart layer on top of the main time series. This is more technically complex but produces a cleaner result for recurring annotations (e.g., marking monsoon season on claims charts, or marking regulatory change implementation dates).
• Drill-through comments (for deep dive): Use the "Report comments" feature in Looker Studio to allow executives to comment on specific charts. The CEO can ask: "What is driving the motor TP combined ratio increase?" The analytics team can respond: "Reserve strengthening of ₹12 Cr due to pool deficit — temporary, not structural." This creates a dialogue layer on top of the data.
🌎
Real World: A large Indian life insurer replaced its static quarterly board pack (68 PowerPoint slides, taking 2 weeks to prepare) with a 2-page Looker Studio dashboard. The change was not about technology — it was about storytelling. The 68 slides had presented every possible metric but told no coherent story. The 2-page dashboard presented exactly 7 KPIs with a "key insight" text box and an "exception flags" table at the bottom. The board discussion changed: instead of spending 75% of meeting time understanding the numbers and 25% debating strategy, the ratio flipped to 25% understanding and 75% debating. The chairman told the analytics team: "This is the best report we have ever received. Not because the numbers are different — they are the same numbers — but because now I understand what I need to act on." That is the goal of dashboard storytelling: not to present data, but to enable decisions.