---
# System prepended metadata

title: 'T135217: Potential Financial Theft/Money Laundering Merchant'
tags: [Wish]

---

###### tags: `Wish`

# T135217: Potential Financial Theft/Money Laundering Merchant

## Table of Contents
[TOC]

## 0. History of Chargebacks
[Chargebacks Link](https://merchant.wish.com/transaction/5ce63383fdb8567ca648cfe8)

![](https://i.imgur.com/F3Zn2Vq.png)
:::danger
For this merchant, some transactions in May 2019 has been filed chargebacks. It shows the evidence of financial theft conduct.

:::

## 1. Declined Order Rate
[sql](https://console.treasuredata.com/app/queries/editor?queryId=1238224)
```sql
select
  t_1.merchant_id,
  t_1.declined_cnt/t_2.order_cnt as declined_order_rate
from
  (select 
    a.merchant_id,
    sum(case when a.state='4' then 1 else 0 end) as declined_cnt
  from commerce_transaction_snapshot_20190705060511 as a
  where a.merchant_id = '5abafc5dabef95631a642ce3'
  group by 1) as t_1
join
  (select
    b.merchant_id,
    count(b.state) as order_cnt
  from commerce_transaction_snapshot_20190705060511 as b
  where b.merchant_id = '5abafc5dabef95631a642ce3'
  group by 1) as t_2
```

| merchant_id | declined_order_rate|
| -------- | -------- | -------- |
| 5abafc5dabef95631a642ce3 | 0.4210 | 

:::danger
For this merchant, the declined order rate is over 40%, which is very high so that it demostrates a high chance that this merchant is doing money laundrying.
:::


## 2. User Blocked Rate
[sql](https://console.treasuredata.com/app/queries/editor?queryId=1242634)
```sql
select
    count(distinct case when a.state=1 then a.user_id else null end)/count(distinct a.user_id)  as blocked_user_rate
from(
    select t_1.user_id,td_last(t_2.state, t_2.restriction_seq) as state
    from commerce_transaction_snapshot_20190708060511 as t_1
    left join risk_datamart.user_restrictions_history as t_2
    on t_1.user_id = t_2.user_id
    where t_1.merchant_id = '5abafc5dabef95631a642ce3'
    group by 1) as a
```

Output:
|blocked_user_rate|
|---|
|0.7075|

:::danger
Among all users of this merchant, over 70% of them are blocked. 

</br>
A financial theft/money laudering merchant is very likely to get this high blocked rate due to incomplete stolen financial information. 

:::


## 3. User Blocked Reason
[sql](https://console.treasuredata.com/app/queries/editor?queryId=1245312)
```sql
with reason_table as
  (select 
      reason_type,
      count(distinct case when state=1 then user_id else null end) as reason_cnt
  from(
    select t_1.user_id, td_last(t_2.state, t_2.restriction_seq) as state,td_last(t_2.reason_type, t_2.restriction_seq) as reason_type
    from sweeper.commerce_transaction_snapshot_20190708060511 as t_1
    left join risk_datamart.user_restrictions_history as t_2
    on t_1.user_id = t_2.user_id
    where t_1.merchant_id = '5abafc5dabef95631a642ce3'
    group by 1) as a
  group by 1
  order by reason_cnt desc),
  
total_blocked_user as
  (select sum(reason_cnt) as total_blocked_cnt
  from reason_table)
  
select 
  reason_table.reason_type,
  reason_table.reason_cnt,
  reason_table.reason_cnt / total_blocked_user.total_blocked_cnt as ratio
from reason_table, total_blocked_user
order by reason_cnt desc, ratio desc
```
Output:
| reason_type      | reason_cnt | ratio   | 
|------------------|------------|---------| 
| Riskified        | 25         | 0.33333 | 
| MinFraud         | 24         | 0.32    | 
| Signifyd         | 10         | 0.1333  | 
| Braintree        | 10         | 0.1333  | 
| Chargeback       | 4          | 0.0533  | 
| Adyen            | 1          | 0.0133  | 
| Admin - ATO      | 1          | 0.0133  | 
| Unblock: Step Up | 0          | 0.0     | 
|     NULL         | 0          | 0.0     |

:::danger
Of all the blocking reasons, 'Riskified Review Declined', 'MinFraud Risk Score above threshouds', 'Signifyd Guarantee Declined', '
Fraud detected by Braintree' are among the the top 4 reasons, which make up 92% of all the declinations.

</br>
The result aligns with the high user blocked rate of this merchant.
:::

## 4. Tracking Number Trail
[sql](https://console.treasuredata.com/app/queries/editor?queryId=1249216)

```sql
select
  a.tracking_id,
  a.city as user_shipping_city,
  a.dest_city as dilivery_city,
  t_2.pick_up_city
from master_order_tracking_infos as a
join 
  (select
    b.tracking_id,
    TD_FIRST(b.city, b.timestamp) as pick_up_city
  from master_order_tracking_infos as a 
  left join checkpoint_data as b
  on a.tracking_id = b.tracking_id
  where a.merchant_id = '5abafc5dabef95631a642ce3'
  and a.tracking_created_time > unix_timestamp('2018-08-23 00:00:00')
  and unix_timestamp(b.timestamp) > unix_timestamp('2018-08-23 00:00:00')
  and b.tag='InTransit'
  group by 1) as t_2
on a.tracking_id = t_2.tracking_id
where a.tracking_created_time > UNIX_TIMESTAMP('2018-08-23 00:00:00')
```
Output Partial Screenshot:
![](https://i.imgur.com/GAu17Xc.png)

:::warning
Among all the tracking ids of this merchant, the package pick up locations are all from Hongkong. And customers' shipping city align well with dilivery city, which are mostly based in Japan.

</br>
The location distribution of customers are suspecious and a financial theft/money laundring merchant can also have this pattern through fake tracking id purchasing.

:::

## 5. User IP Address Distribution
+ Group by ip_address and ip_country. [sql](https://console.treasuredata.com/app/queries/editor?queryId=1245432)
```sql
select 
  ip_address, 
  ip_country, 
  count(distinct user_id) as same_ip_users
from commerce_transaction_snapshot_20190708060511 as a
where a.merchant_id = '5abafc5dabef95631a642ce3'
group by 1, 2
order by same_ip_users desc
```
Output Partial Screenshot:

![](https://i.imgur.com/w8a4cqs.png)

+ Group by first 3 parts of ip_address and ip_country. [sql](https://console.treasuredata.com/app/queries/editor?queryId=1245383)
```sql
select
  SUBSTRING_INDEX(ip_address, '.', 3) as first3parts_ip, 
  ip_country,
  count(distinct user_id) as same_ip_users
from commerce_transaction_snapshot_20190708060511 as a
where a.merchant_id = '5abafc5dabef95631a642ce3'
group by 1, 2
order by same_ip_users desc
```

Output Partial Screenshot:
![](https://i.imgur.com/Is7rK7A.png)

:::warning
It returns 114 ip_address and only 5 out of them are duplicated, regardless of how exactly the ip_address is specified.

</br>

It shows no apparent evidence that same ip_address are used by multiple users. But it's reasonable to suspect the distribution of customers, which are all from Japan.
:::

## 6. G/I trend
[sql](https://console.treasuredata.com/app/queries/editor?queryId=1249262)

```sql
select 
  a.date,
  COALESCE(sum(a.gmv),0)/COALESCE(sum(a.impressions),0) as g_i
from daily_commerce_product_metrics as a
left join 
  (select product_id, merchant_id
  from merch_merchanttransaction
  where merchant_id = '5abafc5dabef95631a642ce3' 
  group by 1, 2) as b
on a.product_id = b.product_id
where b.merchant_id is not null
and date>'2018-08-23' 
group by 1
order by date desc
```
Output Partial Screenshot:
![](https://i.imgur.com/up5wPFf.png)

:::danger
The gmv/impression ratio is extremly high whereas the 95th percentile of G/I ratio typically only ranges from 0.002 to 0.01, i.e., RMP ranges from 2 to 10.

</br>
Note that for some dates G/I value are zeros, that's due to zero transaction 
or zero approved transaction on that day resulted by Wish refund action. See below for orders from 2019-06-30 to 2019-07-07:


![](https://i.imgur.com/xKqN8Bh.png)

:::


