現役社内SEゆゆのExcelVBA覚書

現役社内SEがExcel VBAを中心に覚書として書いていくブログです。

【MWS API】ASINからFBA手数料を取得しよう【Excel VBA】

商品情報取得の中で、一番需要があると思われるFBA出品手数料の取得について解説していきます。


FBA出品手数料は、下記のAmazon FBA料金シミュレーターでみなさん計算していると思います。
Fulfillment by Amazon Revenue Calculator
これは1商品ずつASINを入力して計算するもので、非常に手間がかかる作業です。


しかし、これから解説するGetMyFeesEstimateを使用すれば、
ワンクリックで複数のASINの手数料を取得することができます!


ただし、今までの商品情報取得のものより難しい内容になっています。

実際FBA手数料の取得に関する記事は、ネットで調べてもなかなかヒットしませんでした・・・

また、詳しく解説している記事もなかったので、頑張って詳しく解説したいと思います。

FBA出品手数料を取得する前に

FBA手数料を取得するためには、商品価格が必須です。
こちらの記事でまずは価格を取得しておきましょう。
www.yumeigunshi.com www.yumeigunshi.com

ASINからFBA出品手数料を取得するコード

以前の接続確認のExcelシートに下記を追加してください。
www.yumeigunshi.com

Excelシート

f:id:yumeigunshi444:20191230153206p:plain
ASINシート
f:id:yumeigunshi444:20191230153238p:plain
取得結果を貼り付ける「Result」シート

VBAコード

Option Explicit

Const MAX_COLUMNS As Integer = 8    '最大列数

Private Sub BTN_実行_Click()
    Dim xml As Object
    Dim dic As Object
    
    Dim lstRow As Long
    Dim asinRow As Long
    Dim resultRow As Long
    Dim result() As Variant
    Dim asinList As Collection
    
    Dim asin As String
    Dim price As String
    Dim shohinObj As Object
    Dim itemObj As Object
    Dim wkObj As Object
    Dim i As Integer, j As Integer
    Dim param As String
    Dim request As String
    Dim wk As Variant
    
    Dim asinSort() As String
    Dim strSort As String
    strSort = "1,10,11,12,13,14,15,16,17,18,19,2,20,3,4,5,6,7,8,9"
    asinSort = Split(strSort, ",")
    
    request = "FeesEstimateRequestList.FeesEstimateRequest."
    
    '最終行取得
    lstRow = Cells(Rows.Count, 1).End(xlUp).Row
    If lstRow = 1 Then
        MsgBox ("ASINが入力されていません。")
        Exit Sub
    End If
    
    '初期処理
    Set dic = CreateObject("Scripting.Dictionary")
    
    asinRow = 2     '開始行数
    
    'Result配列の宣言
    ReDim result(1 To lstRow - 1, 1 To MAX_COLUMNS)
    
    While asinRow <= lstRow
        Set asinList = New Collection
        With Sheets("ASIN")
            ' ASINを取り出す
            j = 0
            Do While j < 10
                ' 最終行の場合、処理終了
                If asinRow > lstRow Then
                    Exit Do
                End If
                ' 情報をセット
                asin = CStr(.Cells(asinRow, 1).Value)
                price = CStr(.Cells(asinRow, 2).Value)
                If price = "" Or IsNumeric(price) = False Then
                    price = "-"
                End If
                
                If dic.exists(asin) = False Then    '未処理のASINを処理
                    Set wk = New Collection
                    wk.Add asin, "ASIN"
                    wk.Add asinRow - 1, "ROWS"
                    asinList.Add wk, CStr(asinList.Count + 1)
                    
                    '処理済ASINを格納
                    dic.Add asin, asinRow
                    j = j + 1
                End If
                
                '配列初期化
                result(asinRow - 1, 1) = asin   'ASIN
                result(asinRow - 1, 2) = price  '価格
                result(asinRow - 1, 3) = "-"    '出品手数料
                result(asinRow - 1, 4) = "-"    '出品手数料(率)
                result(asinRow - 1, 5) = "-"    '成約料
                result(asinRow - 1, 6) = "-"    'FBA手数料
                result(asinRow - 1, 7) = "-"    'FBA手数料合計
                result(asinRow - 1, 8) = "-"    '例外
                ' 次行へ
                asinRow = asinRow + 1
            Loop
        End With
        
        param = ""
        ' ASINを1つずつ取り出す
        For i = 0 To UBound(asinSort) - 1
            j = 1
            For Each wk In asinList
                If j = CLng(asinSort(i)) Then
                    resultRow = wk("ROWS")
                    price = result(resultRow, 2)
                    
                    '価格未設定の場合
                    If price = "-" Then
                        price = "0"
                    End If
                    
                    param = param & "&" & request & j & ".IdType=ASIN" _
                            & "&" & request & j & ".IdValue=" & wk("ASIN") _
                            & "&" & request & j & ".Identifier=" & j _
                            & "&" & request & j & ".IsAmazonFulfilled=true" _
                            & "&" & request & j & ".MarketplaceId=A1VC38T7YXB528" _
                            & "&" & request & j & ".PriceToEstimateFees.ListingPrice.Amount=" & price _
                            & "&" & request & j & ".PriceToEstimateFees.ListingPrice.CurrencyCode=JPY"
                    Exit For
                End If
                j = j + 1
            Next
        Next
        
        ' リクエストを作成
        param = MakeParam(param)
        
        Set xml = getXMLPost("https://mws.amazonservices.jp/Products/2011-10-01", param)
        '情報取得
        Call getMyFeesEstimateForId(xml, result, asinList)
        
        '1秒のウェイト
        Sleep 1000
    Wend
   
    'Resultへセット
    With Sheets("Result")
        'クリア
        resultRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Range(.Range("A2").Address, .Cells(resultRow, MAX_COLUMNS).Address) = ""
        '貼り付け
        .Range(.Range("A2").Address, .Cells(lstRow, MAX_COLUMNS).Address) = result
    End With
    
    MsgBox "出力完了"
End Sub


'パラメータ作成
Function MakeParam(asinList As String) As String
    Dim param As String
    Dim paramToSign As String
    Dim sellerId As String
    Dim accessKey As String
    Dim seacretKey As String
    Dim endPoint As String
    Dim timeStamp As String
    Dim apiVersion As String
    Dim sign As String
    Dim url As String
    
    'パラメータの取得
    With Sheets("MWS")
        sellerId = .Range("B1").Value
        accessKey = .Range("B2").Value
        seacretKey = .Range("B3").Value
    End With
    endPoint = "mws.amazonservices.jp"
    timeStamp = Format(CDate(DateAdd("h", -9, Now)), "yyyy-mm-dd") & "T" & Format(CDate(DateAdd("h", -9, Time)), "hh%3AMM%3Ass") & "Z"
    apiVersion = "2011-10-01"
    
    ' リクエストを作成
    param = "AWSAccessKeyId=" & accessKey _
            & "&Action=GetMyFeesEstimate" _
            & asinList _
            & "&SellerId=" & sellerId _
            & "&SignatureMethod=HmacSHA256" _
            & "&SignatureVersion=2" _
            & "&Timestamp=" & timeStamp _
            & "&Version=" & apiVersion
            
    paramToSign = "POST" & vbLf & endPoint & vbLf & "/Products/2011-10-01" & vbLf & param
    sign = GetSign(seacretKey, paramToSign)
    
    param = param & "&Signature=" & sign

    MakeParam = param
End Function

'結果XMLから最低価格を取得
Sub getMyFeesEstimateForId(xml As Object, result() As Variant, asinList As Collection)
    Dim getProObj As Object
    Dim shohinObj As Object
    Dim fbaObj As Object
    Dim wkObj As Object
    Dim strAsin As String
    Dim price As String
    Dim fbaGokei As Double
    Dim hanbaiFee As Double
    Dim hanbaiFeeRitsu As Double
    Dim kihonFee As Double
    Dim haisoFee As Double
    Dim feeType As String
    Dim reigai As String
    
    Dim resultRow As Long
    Dim num As Double
    Dim wk As Collection
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    
    Set getProObj = xml.SelectNodes("GetMyFeesEstimateResponse/GetMyFeesEstimateResult/FeesEstimateResultList/FeesEstimateResult")

    ' 取得したProductオブジェクトを順次取り出し
    For k = 0 To getProObj.length - 1
        '初期化
        strAsin = ""
        fbaGokei = 0
        hanbaiFee = 0
        hanbaiFeeRitsu = 0
        kihonFee = 0
        haisoFee = 0
        feeType = ""
        reigai = "-"
        '行数取得
        Set wk = asinList(CStr(k + 1))
        strAsin = wk("ASIN")
        resultRow = wk("ROWS")
        
        price = result(resultRow, 2)
        
        'ASIN 取得
        '商品情報
        Set shohinObj = getProObj(k).SelectNodes("FeesEstimateIdentifier")
        If shohinObj.length <> 0 Then
            ' ASINを取得
            Set wkObj = shohinObj(0).SelectSingleNode("IdValue")
            If Not wkObj Is Nothing Then
                strAsin = wkObj.text
            End If
        End If
        
        ' ASINが取得できていれば処理を行う。
        If strAsin <> "" And price <> "-" Then
            
            'エラーの場合
            Set wkObj = getProObj(k).SelectNodes("Error/Message")
            If wkObj.length <> 0 Then
                reigai = wkObj(0).text
            Else
                ' FBA手数料合計を取得
                Set wkObj = getProObj(k).SelectSingleNode("FeesEstimate/TotalFeesEstimate/Amount")
                If Not wkObj Is Nothing Then
                    fbaGokei = wkObj.text
                End If
                
                '手数料の内訳を取得
                Set fbaObj = getProObj(k).SelectNodes("FeesEstimate/FeeDetailList/FeeDetail")
                If fbaObj.length > 0 Then
                    For i = 0 To fbaObj.length - 1
                        '手数料取得
                        Set wkObj = fbaObj(i).SelectSingleNode("FinalFee/Amount")
                        If Not wkObj Is Nothing Then
                            num = wkObj.text
                        End If
                        '手数料タイプを取得
                        Set wkObj = fbaObj(i).SelectSingleNode("FeeType")
                        If Not wkObj Is Nothing Then
                            feeType = wkObj.text
                        End If
                        
                        '金額のセット
                        If feeType = "ReferralFee" Then      '販売手数料
                            hanbaiFee = hanbaiFee + num
                        ElseIf feeType = "VariableClosingFee" Or feeType = "PerItemFee" Then    'カテゴリー成約料 or 基本成約料
                            kihonFee = kihonFee + num
                        ElseIf feeType = "FBAFees" Then             '配送手数料
                            haisoFee = num
                        End If
                        
                    Next i
                    
                    '手数料(%)を算出
                    If hanbaiFee > 0 Then
                        hanbaiFeeRitsu = hanbaiFee / CLng(price)
                        hanbaiFeeRitsu = Round(hanbaiFeeRitsu, 2)
                    End If
                End If
            End If
            
            '情報のセット
            If fbaGokei <> 0 Then
                result(resultRow, 3) = Round(hanbaiFee)
                result(resultRow, 4) = hanbaiFeeRitsu
                result(resultRow, 5) = Round(kihonFee, 0)
                result(resultRow, 6) = Round(haisoFee, 0)
                result(resultRow, 7) = Round(fbaGokei, 0)
            End If
            result(resultRow, 8) = reigai
        End If
    Next
   
End Sub

実行結果

f:id:yumeigunshi444:20191230153358p:plain

FBA手数料について

コードの解説の前にFBA手数料について説明します。

FBA料金シミュレーターに1個目のASINを入力して計算してみます。

f:id:yumeigunshi444:20191230153955p:plain
B00KBROZXA

ここで見るべきは「FBA発送の場合」の出品手数料,FBA(Amazonから出荷)手数料の2つになります。
この2つの手数料の合計がFBA手数料となります。

ここでさらに手数料の内訳をみてみましょう。

出品手数料の内訳

f:id:yumeigunshi444:20191230154334p:plain 出品手数料の内訳には、"販売手数料"と"カテゴリー別成約料"があります。
これはResultシートのC~E列になります。

それぞれ何なのかAmazonから引用させていただくと、

販売手数料の支払いは、各商品の売上毎に発生します。
販売手数料は商品が属するカテゴリーによって異なり、各商品の価格から割合で計算されます。
カテゴリーによっては、最低販売手数料があります。


カテゴリー別成約料(VCF)は各メディア商品に適用されます。
VCFの支払いはすべての出品者が対象になります。


販売手数料は、「各商品の価格から割合で計算される」とあるので、
販売手数料率を算出しています。
価格によって手数料が変動してしまうので、
販売手数料率を出すことによって再計算しやすくなります。


カテゴリー別成約料はメディア商品(CD, DVD等)のみに課せられる手数料です。


2つ目のASINのB01L9J68YWはおもちゃなので、0円になっていますね。


FBA手数料の内訳

f:id:yumeigunshi444:20191230155654p:plain
FBA(Amazonから出荷)手数料の内訳
FBA手数料の内訳は、"月額保管手数料","配送代行手数料"となっています。
これはResultシートのF列に該当します。


ここで注意点なのですが、MWS APIでは月額保管手数料を取得することができません。


なのでFBA料金シミュレーターの手数料合計と、
MWS APIで取得した手数料合計は月額保管手数料分だけずれてしまいます。

F列に260円でなく257円と出力されているのはそれが理由です。


FBA手数料の説明だけでもまあまあ複雑ですよね・・・
それではこの知識を前提として、
FBA手数料の取得について解説していきます。

GetMyFeesEstimateの解説

今回はリクエストパラメーター、レスポンスで返ってくる情報量も多いので、頑張っていきましょう・・・!

スロットリング

f:id:yumeigunshi444:20191230161120p:plain カート価格と同様です。
1度のリクエストで10個のASINを指定しましょう。

リクエストパラメーター

f:id:yumeigunshi444:20191230161444p:plain 今までリクエストパラメーターは多くても2つでしたが、
FBA手数料を取得する場合は6つも必要になります。


内容的には難しくないので、1つ1つ解説していきますね。


リクエストを作成しているコード

    param = param & "&" & request & j & ".IdType=ASIN" _
            & "&" & request & j & ".IdValue=" & wk("ASIN") _
            & "&" & request & j & ".Identifier=" & j _
            & "&" & request & j & ".IsAmazonFulfilled=true" _
            & "&" & request & j & ".MarketplaceId=A1VC38T7YXB528" _
            & "&" & request & j & ".PriceToEstimateFees.ListingPrice.Amount=" & price _
            & "&" & request & j & ".PriceToEstimateFees.ListingPrice.CurrencyCode=JPY"

まずは指定している値について、上から順番に説明します。

IdType:ASINかSKUどちらを指定するか。今回はASINで指定したいので"ASIN"。
IdValue:IdTypeで指定した形式で値を指定する。ASINを指定しているので”B00KBROZXA”等を入れます。
Identifier:リスト内で被らなければOK。よく分からないので私は1~10を入れています。
IsAmazonFulfilled:FBA出品かどうか。FBAを利用している場合、"true"を入れます。
MarketPlaceId:これは今までと同じパラメーターですね。固定で"A1VC38T7YXB528"を入れます。
PriceToEstimateFees.ListingPrice.Amount:商品価格を指定します。
PriceToEstimateFees.ListingPrice.CurrencyCode:国を指定します。日本なので"JPY"を指定。


VBAコードだと変数を結合していて分かりにくいので、
文字列にするとこうなります。


&FeesEstimateRequestList.FeesEstimateRequest.1.IdType=ASIN
&FeesEstimateRequestList.FeesEstimateRequest.1.IdValue=B00KBROZXA
&FeesEstimateRequestList.FeesEstimateRequest.1.Identifier=1
&FeesEstimateRequestList.FeesEstimateRequest.1.IsAmazonFulfilled=true
&FeesEstimateRequestList.FeesEstimateRequest.1.MarketplaceId=A1VC38T7YXB528
&FeesEstimateRequestList.FeesEstimateRequest.1.PriceToEstimateFees.ListingPrice.Amount=3003
&FeesEstimateRequestList.FeesEstimateRequest.1.PriceToEstimateFees.ListingPrice.CurrencyCode=JPY

これで1つのリクエストパラメーターになります。
2つめのASINの場合、"FeesEstimateRequest.1"の1の部分を2にする必要があります。

これを最大10回繰り返し、10個のASINを1つのリクエストで送信します。

VBAコード

それでは手数料を取得していきます。

いつものようにB00KBROZXAの結果をスクラッチパッドで表示し、解説していきます。
f:id:yumeigunshi444:20191230190358p:plain いつもより長いですね・・・w

あと取得の仕方もいつもと少し違います。


FeeDetailのタグが複数あり、それぞれの手数料が出力されています。
何の手数料なのかはFeeTypeで判断します。


しかし肝心のFeeTypeの内容が公式ドキュメントにすら載ってなかったので、
下記表を参考にしてください。

FeeType 手数料
ReferralFee 販売手数料
VariableClosingFee カテゴリー成約料
PerItemFee 基本成約料
FBAFees FBA手数料の合計
FBAWeightHandling + FBAPickAndPack
FBAPickAndPack 配送代行手数料
FBAWeightHandling 使用しない(常に0)

ちなみに、基本成約料は小口出品のみ課される手数料だと思われます。
大口の人は常に0円だと思います。


この表とスクラッチパッドを見比べてみると・・・
ReferralFee(販売手数料)が450円。
VariableClosingFee(カテゴリー成約料)が140円。
FBAPickAndPack(配送代行手数料)が257円。
このようになりますね。


これを取得しているコードが下記となります。

    ' FBA手数料合計を取得
    Set wkObj = getProObj(k).SelectSingleNode("FeesEstimate/TotalFeesEstimate/Amount")
    If Not wkObj Is Nothing Then
        fbaGokei = wkObj.text
    End If
    
    '手数料の内訳を取得
    Set fbaObj = getProObj(k).SelectNodes("FeesEstimate/FeeDetailList/FeeDetail")
    If fbaObj.length > 0 Then
        For i = 0 To fbaObj.length - 1
            '手数料取得
            Set wkObj = fbaObj(i).SelectSingleNode("FinalFee/Amount")
            If Not wkObj Is Nothing Then
                num = wkObj.text
            End If
            '手数料タイプを取得
            Set wkObj = fbaObj(i).SelectSingleNode("FeeType")
            If Not wkObj Is Nothing Then
                feeType = wkObj.text
            End If
            
            '金額のセット
            If feeType = "ReferralFee" Then      '販売手数料
                hanbaiFee = hanbaiFee + num
            ElseIf feeType = "VariableClosingFee" Or feeType = "PerItemFee" Then    'カテゴリー成約料 or 基本成約料
                kihonFee = kihonFee + num
            ElseIf feeType = "FBAFees" Then             '配送手数料
                haisoFee = num
            End If
            
        Next i
        
        '手数料(%)を算出
        If hanbaiFee > 0 Then
            hanbaiFeeRitsu = hanbaiFee / CLng(price)
            hanbaiFeeRitsu = Round(hanbaiFeeRitsu, 2)
        End If
    End If
End If

実行結果
f:id:yumeigunshi444:20191230193053p:plain

まとめ

いかがでしたでしょうか?


ここまでくるとプログラミングの知識がないと難しいかもしれません・・・


しかしこれで商品情報取得についての記事はおしまいです!

今後は自動出品や、競合の取得方法などを書いていこうかなーと思っていますが、
恐らくこれよりも難しいので記事にする意味あるのかなと・・・w


それではこれで商品情報に関しては最終回となります!

おつかれさまでした!