A Look Under the Hood: Calculating Aggregated Chargebacks in PQL

Over a year ago, we introduced The Pagos Query Language (PQL) as an internal capability that enables Pagos engineers to encapsulate the complexity of querying payment data. This domain-specific language provides a concise expression of metrics and filters which then empowers you to drill down into your data. Both the data visualizations in Peacock and the data triggers in Canary use PQL and therefore can be filtered in the same ways. Filtering data is at the core of the value Pagos provides to our customers. When you can effortlessly filter your payments data, you can hone in on segments of transactions or customers that require additional attention.

In this blog post, we will explore how we express the The Inner Workings of Filters in Pagos Query Language syntax and specifically how we actually calculate chargeback metrics behind the scenes. For additional context, check out our Understanding Your Refund and Chargeback Volume Using Peacock blog post, where we demonstrate how chargeback visualization and filtering look on the frontend in our Peacock Service Panel.

Querying for Chargebacks

To anchor our perspective, let’s examine how your chargeback rate and count appear in Peacock. In the Chargebacks dashboard, the Chargeback Rate and Count sub-chart under Aggregated Chargebacks demonstrates your chargeback rate and total chargeback count in a set time period:

For you to see this chart on the front end, we use PQL on the back end. The PQL representation of the query to retrieve the data displayed in this Peacock chart is:

PQL#1# stream='pagos.ai'                start='2023-07-07 00:00:00'                end='2022-07-13 23:59:59'                over='days'                metrics=[chargeback_rate, chargeback_count]

For each day shown as a bar on the chart, chargeback_rate is the number of customers issuing a chargeback (some banks call this “disputing a charge”) divided by the total number of transactions processed for all hours in that day.

Filtering Chargeback Data

If you ever see an increase in your chargeback rate, you’re going to want to know why, so you can adjust your payments stack or processes appropriately to get that number back down. In order to really answer that question of why, you’ll need to drill down into smaller and smaller pools of data to identify a root cause. Said another way, you’ll want to isolate smaller and smaller summations of chargebacks and transactions to find out what's driving the rate increase. 

For example, let’s say you’ve theorized that your overall chargeback rates have increased only because rates went up in one particular card issuing country. If you could see which country that might be, you’d know exactly where to target any efforts to curb chargeback volume. To do so, you take a look at the Map of Issuing Country Chargeback Rate sub-chart under the Chargebacks by Issuing Country chart in the Chargebacks dashboard:

Now let’s take a look at what we do on our side of things to generate a chargeback chart with such granular detail. We wrote the PQL for this chart by simply adding a “group_by” statement.

PQL#1# stream='pagos.ai'                start='2022-07-01 00:00:00'                end='2022-07-06 23:59:59'                over='days'                metrics=[chargeback_rate, chargeback_count]                group_by=[issuer_country]

Here, we isolate a subset of both transactions and chargebacks for each chargeback rate calculation, where they all belong to one issuer country. 

You might assume that the overall chargeback rate (shown in the first example above) is just the sum of all chargeback_rates from each issuer_country, but it’s a bit more complex than that. To get a little technical, we are careful to ensure numerical stability when dealing with IEEE-754 floating point numbers because an average of an average is not an average any more. Instead, we calculate overall chargeback rate by using integer accumulators for both the numerator and denominator over each filter option. This method allows us to go deeper and maintain accuracy.  

If you thought the increase in chargeback rate was somewhere in Northern America, you might filter the Chargebacks by Issuing Country chart to see only transactions made with cards issued in the United States, Canada, and Mexico. We’d calculate those country-specific chargeback rates for you with the following formula and PQL:

PQL#1# stream='pagos.ai'            start='2022-07-01 00:00:00'            end='2022-07-06 23:59:59'            over='days'            metrics=[chargeback_rate, chargeback_count]            group_by=[processor > issuer_country(CA,US,MX) > currency]

Note we use IBAN alpha-2 country codes to make the issuer_country selection.

The Power of PQL

Concise PQL queries enable us to make filter selections at the most granular level of detail. In the examples above, we were able to drill down from the overall chargeback rate to much more specific and comparable subsets, enabling you to find the root cause of a spike in chargeback rate quickly. This is only one small example of what you can do with PQL and Pagos filters in general. It has never been this easy to view and filter all of your payments data all in one place; that’s the power of Pagos.

Ready to get started? Pagos provides a range of microservices, each designed to provide you with the data, insights, and tools you need to improve your business efficiency and generate additional revenue. With Peacock by Pagos, you can not only visualize your comprehensive payments data, but filter those visualizations to hone in on the aspects of your stack where there might be issues to address or successes to replicate. Canary, our data anomaly detection service, allows you to not only customize acceptable thresholds for specific payment metrics, but to filter down the data sets monitored to only those segments you're most concerned about.

At the time of publication, PQL is something we’re only using internally, behind the scenes. In a future release, we hope to simplify and augment our filtering options by bringing PQL to our client-facing products. Subscribe to the Pagos Blog for announcements down the line on how to access PQL directly from each of Pagos’ products.