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