This report allows you to analyze monthly customer purchase activity with ease.

Identify trends among first-time and returning customers each month to develop effective marketing strategies.

Connect directly to your database and view updated results on the web — no need to recreate.

Step 1. Convert an Excel file to a web report using i-AUD Designer

• • Download the sample Excel file from the Learning Experience Samples.
    
    
    

  • From the EPA main screen, go to [Menu] > [Tool] and launch i-AUD Designer.
    
    
    

  • Drag and drop the saved Excel file into the i-AUD Designer window.
    
    
    

  • The Excel report now appears in the web view, preserving the original layout.

  • When database data is connected, the number of records displayed on the web screen is limited to 1,000 by default. Change this limit to 5,000 as follows:
    Data > LimitofBinding : 5000
    
    
    
    

  • Ensure the report automatically resizes to fit the web browser window.

  • In the Properties pane on the right, check Docking: Left, Right, Bottom.

  • To remove the border from the report on the designer screen, set the Line Type property under Border to ‘None’.
    

Step 2. Connect to the database (Using Variables)

• • Let’s connect the database so the report data updates automatically — no need to create a new report every month.

  • Right-click on the report area, then select Design.

    
    

  • Let’ start by understanding the role of each sheet in this report

    • Sheet V : V_Retention
      When multiple sheets exist in Excel, the sheet to be displayed on the web must follow the format ‘V_(letters)’ or ‘V(number)’.
      
      
      

    • Sheet D : D1
      This sheet allows you to enter data or retrieve it by connecting to a database.
      
      
      

    • Sheet P : P1
      Create variables to be used when querying data. You can define cell values as variables by using Excel’s ‘Name Manager’ feature.
      In this exercise, we’ll use ‘YearMonth’ as a variable to view the customer purchase status by month.
      
      
      

      The naming convention for Excel sheet is as follows:

      
      

Step 3. Define variables and use them to retrieve database data

• • Use Excel’s ‘Name Manager’ to define the ‘YearMonth’ variable in the P sheet.

  • Variables can be used as filter conditions on the web. When a filter condition is set, only the data matching the variable value will be retrieved.

  • The variable value is the cell where you enter the value to filter, and you can enter any value.
    Example) '2025-06   ※ Add a single quote (’) at the beginning to ensure the value is recognized as text, preventing automatic date formatting.
    
    
    

  • Use the variable to get necessary data for the V sheet into the D sheet.
    

  • First, identify the columns required to create the V sheet.
    
    
    

  • Use an SQL query statement to load the necessary data from the database into the D1 sheet.

  • From the top ribbon menu, click i-MATRIX > DB Bot > Dataset Manager.

① Click '+' to connect to the database.
② Search for a table to retrieve.
③ From the list of table columns on the right, double-click the columns required to the V sheet. This will generate the SQL query statement.
    In the WHERE clause, use the variable defined in the P sheet as the filter condition.
    To substitute a variable name with its value in SQL, prefix the variable name with a colon(:).
    WHERE date = :VS_YM
④ Select a location to output the data. Click the icon to choose the cell where the data will be output.
   * Output type : Table
   * Output location : 'D1'!$A$1
⑤ Save the SQL query statement and the configured settings.
⑥ Click the 'Close' button.

• • The database connection is complete.
  • [Data Derivation Method] We will calculate the ‘Purchase Cycle’, necessary for V sheet, using the ‘Repurchase Interval’ values.
    ※ For more details on Data Derivation Method, refer to here.
    
    
  • Assign the ‘Purchase Cycle’ from ‘first’ to ‘fifth’ based on the ‘Repurchase Interval’, and enter the formula using Excel’s IF function.
    =IF(D2="First-Time Purchase","-",IF(E2<=1,"first",IF(AND(E2>=2,E2<=6),"second",IF(AND(E2>=7,E2<=14),"third",IF(AND(E2>=15,E2<=30),"fourth","fifth")))))
  • After entering the formula in row 2, click 'Run' from the i-MATRIX ribbon menu, and the formula will automatically fill down to cover the area with DB data.
    Even if the number of records retrieved each month changes, the formula applied to the derived column will automatically be applied to all rows.
    
    
    
  • The setup to connect data to the V sheet is complete.

Step 4. Connect data to the V sheet

• • Let’s connect the data retrieved in the D sheet to the V sheet, which will be displayed on the web screen.

  • Use the COUNTIFS function to calculate ‘Purchase Count’, and use the SUMIFS function to calculate ‘Revenue’.
    
    
    
    

  • Hide any unnecessary rows on the screen (e.g., Sum of Category).
    
    

  • Now, data entry is now complete. Next, we will use the variable to automatically update the report title based on the selected month.
  • Enter the following formula in the area for the report title.
    ="Customer Purchase Analysis — " & TEXT(DATE(LEFT(VS_YM, 4), RIGHT(VS_YM, 2), 1), "mmmm") & ", " & LEFT(VS_YM, 4)
    
    
    

  • The report setup is complete. Click 'Save' in the i-MATRIX ribbon menu or press Ctrl+S to save, then return to the AUD Designer screen.

Step 5. Configure the Search filter

• • Next, add and configure the ‘YearMonth’ filter to view the Customer Retention Analysis report by month on the web.
  • Place a ‘Period’ label and a calendar so that users can select the year and month to filter, and add a ‘Refresh’ button to refresh data when clicked.
    
    
  • From the UI Bot menu at the top, click Label and place it on the designer. In the Properties pane, change the Text property to ‘Period’, or press F2 on your keyboard. You can also customize the label’s font, color, and other style properties in Style.
    
    
    
    
  • Next, add the calendar.
  • From UI Bot > Calendar, click Month and place it next to the label.
  • Set the calendar’s Name property to VS_YM, the same variable you defined in the Excel’s ‘Name Manager’. This ensures that the value selected in the calendar is used as the filter condition.
    * Optionally, set InitDate to define the default value when the report is first opened.
    * Ensure that DataFormat matches both the DB data and the variable values defined in Excel. (yyyyMM → yyyy-MM)
    
    
    
    
  • Finally, add the ‘Refresh’ button to the designer.
  • From the UI Bot menu at the top, click Button and place it in the desired location on the designer.
  • Similar to the ‘Period’ label, change the button’s Text property to ‘Refresh’, and adjust its formatting in Style.
    You can use BoxStyle to quickly apply a saved style to the button.
    
    
    
    
  • Set up an event so that when the ‘Refresh’ button is clicked, the report data is refreshed using the value from the YearMonth variable.
  • Click Process Bot at the top. In the Objects list, double-click Button with a name ‘Button’; in the Activities list, double-click 'Refresh'.

  • In the Refresh module details, select the MXGrid component to refresh, and drag a connection line from the Button to Refresh Module.

    (Note: Dragging and dropping the Excel file onto the web screen automatically creates the MXGrid component.)

    

  • Save and close the Process Bot.
  • The report setup is now complete.

Step 6. Save your report

• • Save the completed report to My Folder.
    
    
    
  • From the EPA main screen, go to [Menu] > [Individual] in the left-hand sidebar.
  • Click the Search(Magnifying glass icon) button to refresh the report list and confirm your saved report.
  • Change the year and month, and click the 'Search' button to see the data updated.