---
# System prepended metadata

title: 'T133198: Revisit withheld merchant'
tags: [Wish]

---

###### tags: `Wish`

# T133198: Revisit withheld merchant
*Joanna Jiang
2019-06-25*

---

## 1. Refund rate by type

> $Refund \ Rate = \frac {Refunded \ GMV} {Total \ GMV}$. [Click to see query](https://console.treasuredata.com/app/queries/editor?queryId=1199581)

```sql
select
a.refund_type,
a.refund_gmv/b.gmv as refund_ratio
from
  (select
  merchant_id,
  b.titled_name as refund_type,
  sum(COALESCE(gmv,0)) as refund_gmv
  from merch_merchanttransaction as a
  left join dim_refund_reasons as b on a.refund_reason_category=b.refund_reason_category
  where
  merchant_id='57ac6fb11226ef29ed12b8e6'
  group by 1,2
  order by refund_gmv DESC) a
join
  (select
  merchant_id,
  sum(gmv) as gmv
  from merch_merchanttransaction
  group by 1) b 
on a.merchant_id=b.merchant_id
where refund_type is not null
```
> The result shows:


| refund_type | refund_ratio |
| -------- | -------- |
|Shipping Taking Too Long	|0.0886|
|User Cancelled Order	|0.0368|
|Suspected Fraud|	0.0223|
|Item Does Not Match Listing	|0.0180|
|Item Does Not Work As Described|	0.0099|
|Item Is Damaged|	0.0064|
|Item Marked Delivered But Did Not Arrive|	0.0056|
|User Placed Order By Mistake|	0.0054|
|Item Returned To Sender|	0.0048|
|Item Does Not Fit|	0.0018|
|Item Is Counterfeit|	0.0009|
|Received Wrong Item|	0.0009|
|Merchant Out Of Stock|	0.0008|
|Incomplete Order|	0.0006|
|Merchant Sent To Wrong Address|	0.0005|
|Consumer Fraud|	0.00007|
## 2. Missleading/fake tracking reviews ration

> Find out the top 10 destination contries for this merchant regarding GMV. [Click to see query](https://console.treasuredata.com/app/queries/editor?queryId=1197498)
```sql
select dest_country, sum(gmv) as total_gmv
from merch_merchanttransaction as mmt
where merchant_id='57ac6fb11226ef29ed12b8e6'
group by dest_country
order by total_gmv desc
limit 10
```
They turn to be GB(en), US(en), BR(portuguese), FR(french), DE(german), IT(italian), ES(spanish), NL(dutch), CH(german, french and italian), MX(spanish).

> Search relating keyword in comment. [Click to see query](https://console.treasuredata.com/app/queries/editor?queryId=1199566)


```sql
select 
  t_1.merchant_id, 
  t_1.misleading_feedback_cnt/t_2.comments_cnt as misleading_ratio,
  t_1.fake_feedback_cnt/t_2.comments_cnt as fake_ratio,
  t_1.fraud_feedback_cnt/t_2.comments_cnt as fraud_ratio,
  t_1.scam_feedback_cnt/t_2.comments_cnt as scam_ratio
from 
  (
  select
  merchant_id,
  sum(
    case when 
    (
    LOWER(comment) like '%misleading%' --english
    or LOWER(comment) like '%mislead%' --english
    or LOWER(comment) like '%misled%'--english
    or LOWER(comment) like '%enganosa%' --portuguese
    or LOWER(comment) like '%fallacieuse%' --french
    or LOWER(comment) like '%trompé%' --french
    or LOWER(comment) like '%irreführen%' --german
    or LOWER(comment) like '%fuorviare%' --italian
    or LOWER(comment) like '%engañar%' --spanish
    or LOWER(comment) like '%misleiden%' --dutch
    ) then 1 else 0 end) as misleading_feedback_cnt,
  sum(
    case when 
    (
    LOWER(comment) like '%fake%' --english
    or LOWER(comment) like '%falsa%' --portuguese
    or LOWER(comment) like '%faux%' --french
    or LOWER(comment) like '%Fälschung%' --german
    or LOWER(comment) like '%falso%' --italian
    or LOWER(comment) like '%falso%'--spanish
    or LOWER(comment) like '%nep%' --dutch
    ) then 1 else 0 end) as fake_feedback_cnt,
  sum(
    case when 
    (
    LOWER(comment) like '%scam%' --english
    or LOWER(comment) like '%golpe%' --portuguese
    or LOWER(comment) like '%arnaque%' --french
    or LOWER(comment) like '%Betrug%' --german
    or LOWER(comment) like '%truffa%' --italian
    or LOWER(comment) like '%estafa%'--spanish
    or LOWER(comment) like '%oplichterij%' --dutch
    ) then 1 else 0 end) as scam_feedback_cnt,
  sum(
    case when 
    (
    LOWER(comment) like '%fraud%' --english
    or LOWER(comment) like '%fraude%' --portuguese
    or LOWER(comment) like '%fraude%' --french
    or LOWER(comment) like '%Betrug%' --german
    or LOWER(comment) like '%frode%' --italian
    or LOWER(comment) like '%fraude%'--spanish
    or LOWER(comment) like '%bedrog%' --dutch
    ) then 1 else 0 end) as fraud_feedback_cnt
    from merchant_feedback_rating
    where merchant_id='57ac6fb11226ef29ed12b8e6'
    and TD_Time_range(time, '2016-08-11')
    group by 1
    ) as t_1
join
    (select
    merchant_id,
    count(comment) as comments_cnt
    from merchant_feedback_rating
    where merchant_id='57ac6fb11226ef29ed12b8e6'
    and TD_Time_range(time, '2016-08-11')
    and comment is not null 
    group by 1) as t_2
on t_1.merchant_id=t_2.merchant_id
```

> The ratios in this result are:

| misleading_ratio| fake_ratio | fraud_ratio | scam_ratio |
| -------- | -------- | -------- | - |
| 0    | 0.00780  | 0   | 0.00060 |


## 3. Ratings Ratio
> Calculate rating ratios in 5-star scale for this merchant. [Click to see query](https://console.treasuredata.com/app/queries/editor?queryId=1197241)
```sql
select
a.rating_star,
a.records/b.total_records as rating_ratio
from
  (select
  mfr.merchant_id,
  mfr.rating as rating_star,
  count(mfr.rating) as records
  from merchant_feedback_rating mfr
  where
  merchant_id='57ac6fb11226ef29ed12b8e6'
  and TD_Time_range(time, '2016-08-11')
  group by 1,2
  order by rating_star desc) a
join
  (select
  merchant_id,
  count(rating) as total_records
  from merchant_feedback_rating
  where TD_Time_range(time, '2016-08-11')
  group by 1) b 
on a.merchant_id=b.merchant_id
```
> Result shows as:


| rating_star | rating_ratio|
| -------- | -------- | -------- |
|1	|0.0766|
|2	|0.0458|
|3	|0.1104|
|4	|0.1775|
|5	|0.5897|



## 4. Account Balance
> Calculate confirmed balance and pending balance for this merchant. [Click to see query](https://console.treasuredata.com/app/queries/editor?queryId=1197132)

```sql
select 
a.merchant_id,
a.currency,
sum (case when a.balance_type=1 then a.balance else 0 end) as confirmed_balance,
sum (case when a.balance_type=2 then a.balance else 0 end) as pending_balance
from merch_account_balance_20190624074726 as a
where a.merchant_id='57ac6fb11226ef29ed12b8e6'
group by 1, 2
```

> Result shows as:

| merchant_id | currency | confirmed_balance | pending_balance|
| -------- | -------- | -------- |---|
|57ac6fb11226ef29ed12b8e6|	USD|	13091.429920002527	|0.0|


## 5. Fine History
> Find out fined amount and reversed fine amount for this merchant. [Click to see query](https://console.treasuredata.com/app/queries/editor?queryId=1197146)

```sql
select
  -- a.merchant_id,
  b.name as fine_type,
  sum(case when a.action=0 then COALESCE(a.amount,0) else 0 end) as fine_amount,
  sum(case when a.action=1 then coalesce(a.amount,0) else 0 end) as fine_reversed
from merchant_fine_audit as a
left join dim_merchant_fine_types as b 
on a.type=b.fine_type
where merchant_id='57ac6fb11226ef29ed12b8e6'
group by 1
order by fine_amount desc
```
> Result shows as:

|           fine_type                    |        fine_amount            |         fine_reversed           | 
|-------------------------------|--------------------|--------------------| 
| FAKE_TRACKING                 | 184441.0          | 173971.0 | 
| LEGAL_SETTLEMENT              | 75000.0            | 0.0                | 
| LATE_CONFIRMED_FULFILLMENT    | 12336.4 | 3978.2 | 
| PAID_PLACEMENT_IMPRESSION_FEE | 8433.4  | 0.0                | 
| REPEAT_IP_INFRINGEMENT        | 500.0              | 0.0                | 
| IP_INFRINGEMENT               | 390.0              | 10.0               | 
| PROMOTED_PRODUCT_DISABLE_FINE | 150.0              | 0.0                | 
| PROHIBITED_PRODUCT_FINE       | 120.0              | 0.0                | 
| MISLEADING_LISTING            | 100.0              | 0.0                | 
| INFRACTION                    | 50.0               | 0.0                | 
| MERCHANT_CANCELLATION         | 32.0               | 0.0                | 
| MERCHANT_INCENTIVE            | 19.0 | 0.0                | 
| PAID_PLACEMENT_ENROLLMENT_FEE | 9.5                | 0.0                | 
| QUALITY_REFUND_FINE           | 5.0                | 5.0                | 
| SE_CASH_BACK                  | 2.9 | 0.0                | 


## 6. AOV Changes (Monthly)

> $AOV =\frac{total\ gmv}{number\ of\ orders}$.  [Click to see query](https://console.treasuredata.com/app/queries/editor?queryId=1199658)
```sql
select TD_TIME_FORMAT(TD_DATE_TRUNC('month', cast(order_time as BIGINT), 'America/Los_Angeles'), 'yyyy-MM-dd', 'America/Los_Angeles') AS order_month,
sum(mmt.gmv)/count(distinct m_transaction_id) as AOV
from merch_merchanttransaction_dump as mmt
where mmt.merchant_id='57ac6fb11226ef29ed12b8e6'
and order_time > 1529651778
group by 1
```
> Result shows as:


| order_month| aov (monthly)|
|------------|--------------------| 
| 2018-12-01 | 29.268724460638207 | 
| 2018-11-01 | 34.60895665811946  | 
| 2018-10-01 | 31.921843846638996 | 
| 2018-09-01 | 25.40263658814998  | 
| 2018-08-01 | 19.68501932248473  | 
| 2018-07-01 | 19.27852293857941  | 
| 2018-06-01 | 19.49389443332202  | 
| 2018-05-01 | 20.75860331853231  | 
| 2018-04-01 | 21.06698128328485  | 
| 2018-03-01 | 19.910345203948744 | 
| 2018-02-01 | 20.848074283308655 | 
| 2018-01-01 | 22.054052815527776 | 
| 2017-12-01 | 23.934194815464508 | 
| 2017-11-01 | 25.765522649309492 | 
| 2017-10-01 | 25.84926935821249  | 
| 2017-09-01 | 27.19147716662688  | 
| 2017-08-01 | 21.5004484651015   | 
| 2017-07-01 | 20.94164583317555  | 
| 2017-06-01 | 20.560839391523793 | 
| 2017-05-01 | 20.0245792320259   | 
| 2017-04-01 | 18.590909417498352 | 
| 2017-03-01 | 20.397345499776716 | 
| 2017-02-01 | 19.150302738447984 | 
| 2017-01-01 | 23.002432505556403 | 
| 2016-12-01 | 20.607280729675292 | 
| 2016-11-01 | 18.02374956013975  | 
| 2016-10-01 | 10.657052929827708 | 
| 2016-09-01 | 22.627306867213477 | 
| 2016-08-01 | 15.360422009513492 | 



