Tableau dashboard example: Merchant Monitoring

Written by: Jeremy Goh

---

This HackMD article accompanies the Merchant Monitoring Tableau dashboard published on my Tableau Public profile page. I discuss some of the considerations behind this dashboard design and give some context around the information conveyed there.

Business Use-Case

Fraud monitoring of merchants & their customers in a Buy-now-pay-later business model. (Details for those interested can be found below.) The end goal is to catch fraudulent behaviour as early as possible, without having to wait for the installments to reach due-date before realizing something has gone wrong.

We want to track pre-defined early warning indicators on a per merchant / company basis, and colour the text red if either of these alert conditions are met:

• There has been a "significant" change in the indicator compared to the company's historical values (e.g. this week v.s. past 12 weeks). This is applicable to the columns under "Application-date" level.
• Observed values exceeded our target (OKR), set via parameters. This is applicable to the columns under "Due-date" level.

This dashboard serves as a quick overview on merchants which experienced recent, significant shifts in their population / customer behaviour. Companies with a greater number of alerts (hits) will then be assigned a higher priority for a fraud investigation or more in-depth analysis to be conducted.

Details

Dashboard requirements

• Achieve a different alert condition for different columns
• Dynamic alert conditions via parameters (e.g. FPD1 target, calculating FPD1 via Amount or Volume, etc.)
• Using separate Calculated Fields for the "Colour" mark (alert condition) and the "Text" mark (the actual value displayed on the cross-tab).

The default Tableau cross-tab is not sufficiently flexible to support all of these requirements. Thus, we have to build the cross-tab manually.

Significance

In order to achieve true dynamic alert monitoring, we want to avoid having to specify thresholds for every single indicator column. Imagine having 20+ indicator columns: specifying the static alert thresholds via individual Tableau parameters would quickly become unwieldy and ugly.

Instead, wherever possible, we calculate relevant test statistics (e.g. 2-sample t-test for proportions), and compare the test statistic against the 5% significance level (say).

Early warning indicators

The 4 indicators below were chosen to highlight the range of indicator types that this dashboard can support. When comparing against historical values (to detect unusual drifts), we can use test-statistics. Whereas for monitoring key metrics such as default rates or expected loss, we can compare directly to our quarterly targets.

Indicator	Description	Main alert condition *
Downpayment Credit Card Ratio	Ratio of transactions at this company where the downpayment is paid via credit card. Value shown in the cross-tab is the 2-sample t-test test statistic, comparing the current week's proportions v.s. the last 12 weeks' proportions.	Test statistic $\ge$ 1.96
Avg Basket Size	Mean merchandise value per transaction at this company. Value shown in the cross-tab is the 2-sample t-test test statistic, comparing the current week's avg basket size against that of the last 12 weeks.	Test statistic $\ge$ 1.96
FPD1, Past 7 Days	Ratio of transactions with days past due $\ge$ 1 (default rate) as of "now". This ratio calculation can be performed via Amount or by Transaction volume (user can choose via parameter).	FPD1 $\ge$ FPD1 target
Additional Loss	Monetary loss over that acceptable by our FPD1 target, $(\text{FPD1}-\text{FPD1 target})\times \text{Due amount}$.	Loss $\ge$ Add'l loss target

$\ast$ Note: the actual alert conditions will include, in addition to these, conditions on transaction counts that must be satisfied for validity of significance testing.

Limitations

1. Maintainability of dashboard is not great. E.g. adding new feature indicators into the dashboard requires finessing with the alignment, width, height etc. of text objects in the dashboard view.
2. Number of rows in the cross-tab is equal to the number of distinct companies under consideration. You can consider restricting this to only companies which have hits greater than a certain threshold.
3. Number of columns will scale up with the number of features being monitored.

End

Concluding thoughts

In business-reporting scenarios, it is important to keep dashboards 1. easy-to-understand (for the target audience), 2. to-the-point, and most importantly, 3. easy-to-maintain.

In cases where there is an overload of information (case in point), I suggest maintaining an external document (like this one but more thorough) that explains the entire dashboard logic, assumptions for statistical tests employed, etc. And link that document as a caption on your Tableau dashboard.

References

• Ideas adapted from https://playfairdata.com/3-ways-to-create-charming-crosstabs-in-tableau/. Specifically the "PLACEHOLDER" trick to simulate individual "columns" in the cross-tab.

Disclaimer

All data used in this dashboard is fake / randomly generated, thus there might be logical inconsistencies in the numerical values.

In actual use, the data extract is refreshed daily from our database instead.