Creating a Custom Payment Recovery Performance Report

Admin: Corporate Admin
Page: Reports > Custom Reports
URL: [CLIENTID].corpadmin.directscale.com/Corporate/Reports/Custom
Permission: ManageCustomReports() or ViewCustomReports()

After setting up your FlexPay payment recovery integration, you can create a custom report that provides a glimpse into how FlexPay performs while filtering out the refunds that don’t count.

While initially, this report will not have much data to display, over time, as FlexPay does its magic, you’ll see the benefit. With the report, you can evaluate your payment recovery integration and determine if it provides the expected benefits.

To create this report, use the SQL Report Builder:

  1. Click + New Report button and select SQL Report Builder.

    New Report > SQL Report Builder

    The SQL Report Builder page opens. This page features the same functionality as the SQL Manager.

    Sql Report Builder page
  2. Enter the SQL query in the Query Box.

    SELECT COUNT(R1.recordnumber) AS OrdersRecovered,
           SUM(P1.Amount) AS TotalAmountRecovered,
           AVG(P1.Amount) AS AverageRecoveryValue
    FROM CRM_PaymentRecovery_RecommendationInfo R1
    JOIN ORD_Payments P1 ON P1.OrderNumber = R1.OrderNumber
    AND P1.PayType = 'Charge'
    AND P1.PaymentStatus = 'Paid'
    WHERE NOT EXISTS
        (SELECT R2.OrderNumber
         FROM CRM_PaymentRecovery_RecommendationInfo R2
         JOIN ORD_Payments P2 ON P2.OrderNumber = R2.OrderNumber
         AND P2.PayType = 'Credit'
         AND P2.PaymentStatus = 'Paid'
         WHERE R2.OrderNumber = R1.OrderNumber )

    The queried data will look back to the beginning of time. If you need a more narrow view, you can modify the query to show the time constraint you need (for example, only the current month).

  3. Click Preview. The Preview Report panel allows you to test your query and ensure it pulls in the intended data. Click the X to close the panel.

  4. Click Save. The Save Report pop-up opens.

  5. In the pop-up, enter a Report Name—for example, “Overall Numbers for Payment Recovery”.

  6. Click Save to confirm.

The report adds to the list of available reports accessible to you and other users.

Was this article helpful?
0 out of 0 found this helpful
Previous Next

Comments

0 comments

Please sign in to leave a comment.