Customer Retention Analysis

Build an interactive web report analyzing monthly customer purchase patterns, enabling data-driven retention strategies without manual Excel work.

What You'll Learn

  • Convert Excel to web report with i-AUD Designer
  • Understand V/D/P sheet structure for data management
  • Define variables and configure SQL data retrieval
  • Configure interactive search filters

~30 min

Intermediate

6 Steps

1

Convert Excel to Web Report

Download sample file and set up the web report environment

Download the sample Excel file from Learning Experience Samples.

Sample Excel file download location

Navigate to [Menu] → [Tool] to open i-AUD Designer.

i-AUD Designer launch via Menu Tool
  1. Drag and drop the Excel file into the Designer workspace
Drag and drop Excel file

Configure report settings:

  1. Set Data > LimitofBinding: 5000 to handle large datasets
  2. Configure Docking: Left, Right, Bottom
  3. Set Border > Line Type: None for clean presentation

Tip: Setting LimitofBinding to 5000 ensures smooth performance when working with large customer datasets (default is 1,000).

Configure LimitofBinding to 5000
Set border Line Type to None
2

Database Connection (Variables)

Set up the V/D/P sheet structure for data management

Configure the three-sheet structure that separates concerns:

  1. V Sheet V_Retention — the display/visualization layer
V Sheet (V_Retention) setup
  1. D Sheet D1 — raw data storage and retrieval
D Sheet (D1) setup
  1. P Sheet P1 — parameter variables (e.g., YearMonth for date filtering)
P Sheet (P1) setup

Review the Excel sheet naming convention to understand the V/D/P structure.

Sheet naming convention chart

Tip: The V/D/P structure separates concerns: V for visualization, D for data, and P for parameters. This makes reports maintainable and flexible.

3

Define Variables & Retrieve Data

Create named variables and configure SQL data retrieval

Open Name Manager and define the YearMonth variable in the P sheet. Enter values like "2025-06" with a leading apostrophe to preserve text format.

Define YearMonth variable in Name Manager

Identify the columns required to create the V sheet for the retention analysis display.

Columns required for V sheet

Configure SQL data retrieval:

  1. Navigate to i-MATRIX > DB Bot > Dataset Manager
  2. Add your database connection and configure the SQL query
  1. Write the SQL query with the date filter: WHERE date = :VS_YM
  2. Set output to D1 sheet as Table format

Tip: The :VS_YM syntax binds the variable to the SQL query, enabling dynamic date-based filtering at runtime.

SQL query with VS_YM variable

Apply the Purchase Cycle formula to calculate customer retention metrics and auto-fill the results.

Purchase Cycle formula application
4

Connect Data to V Sheet

Link retrieved data to the visualization sheet with formulas

Use aggregation formulas to connect data from the D sheet to the V sheet:

  1. Apply COUNTIFS formula to calculate purchase counts per customer segment
  2. Apply SUMIFS formula to aggregate revenue data by category
COUNTIFS and SUMIFS formula setup
  1. Create a dynamic title formula that updates based on the selected date period
  2. Hide unnecessary rows and save the file

Tip: COUNTIFS and SUMIFS are powerful for aggregating customer data across multiple criteria, making retention analysis straightforward.

Dynamic title and completed V sheet
5

Configure Search Filter

Add interactive UI elements for filtering by time period

Add a Period Label using UI Bot > Label. Customize font and color in Style properties.

Add Period Label via UI Bot

Add a Month Calendar via UI Bot > Calendar > Month:

  1. Set the Name property to VS_YM (must match the variable name)
  2. Configure DataFormat: yyyyMM → yyyy-MM
Configure Month Calendar with VS_YM

Add a Refresh Button via UI Bot > Button. Apply BoxStyle for quick formatting.

Add Refresh Button

Configure button event using Process Bot:

  1. Connect button click → Refresh Module → MXGrid component

Tip: The Month Calendar linked to VS_YM lets users quickly switch between months without typing dates manually.

Process Bot event configuration
6

Save Your Report

Save and verify your completed retention analysis report

Save your report to your personal folder:

  1. Click Save and select My Folder as the destination
Save to My Folder

Verify your saved report:

  1. Navigate to [Menu] → [Individual]
  2. Click the Search button to refresh the list
  3. Test the interactive filters by changing the month and clicking Refresh
Verify report in Individual menu