Model: GW - Retention

The GW - Retention model compares policy counts and premium amounts from each accounting month to the same month in the prior year. Analysts can use the model to visualize policy and premium retention over twelve months. They can view retention by month, state, product, and renewal business.

Source tables



The GW- Retention model is built from a fact table and multiple dimension tables, as shown in the diagram.

Fact:
  • efr_fact_retention

Dimensions:

The two dimensions are build from the same source dimension table, efr_dim_policyinfo. Joining the table to two different foreign keys in the fact table creates one dimension with current policy information and another dimension with prior policy information. The following table lists the model dimension and the columns that create the join.
Model dimension efr_fact_retention column efr_dim_policyinfo column
efr_dim_policyinfo_current fk_currentpolicyinfo_skey skey
efr_dim_policyinfo_prior fk_priorpolicyinfo_skey skey

From each table, Guidewire selected specific columns to include in the model. They’re typical columns used in reporting and analysis. For details about each column in the model, see the Data dictionaries for Explore models.

Current and prior information

To compare data from year to year, the model uses current and prior periods. The current period is any accounting period month, such as March 2024. The prior period is the same month in the prior year, such as March 2023. The model includes policy attributes and measures for both current and prior periods. For example:
  • Attributes
    • Current year new/renewal flag
    • Prior year new/renewal flag
    • Current year product
    • Prior year product
  • Measures
    • Current year policy count
    • Prior year policy count
    • Current year inception-to-date annualized written premium
    • Prior year inception-to-date annualized written premium

Policy and premium retention formulas

The model uses the current and prior measures to calculate policy and premium retention across different attributes. For example:
  • Overall retention
  • New business retention (New business from the prior year that was renewed in the current year)
  • Retention by product
  • Retention by state
Note: Formulas are created on the model in Explore. They aren’t part of the source tables in Explore or datasets Data Studio.

Related information