###### 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)
:::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:
:::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:
+ 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:
:::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:
:::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:
:::
Sign in with Wallet
Connect another wallet