Zenn
🍣

troccoを使ってApp Store Connect APIからDL数をSnowflakeにETLする

2022/01/19に公開
Snowflake
App Store Connect
TROCCO
tech

概要

App Store Connect APIを使ってアプリのDL数をSnowflakeに投入して、Tableauなどで可視化したい！って要望があったのでやってみました。
今回は、APIからDL数を取得して、Snowflakeに投入する所までを書きたいと思います。

App Store Connect

trocco接続情報の作成のための情報

必要な情報は、対象となるアプリの以下の情報

  • Issuer ID
  • Key ID
  • 秘密鍵
    APIキーを作成する際の権限は、「Finance」以上であることが必要とのことです。

秘密鍵の取得方法

https://developer.apple.com/documentation/appstoreconnectapi/creating_api_keys_for_app_store_connect_api

https://appstoreconnect.apple.com/

  • Users and Access　を選択し、Keysタブを選択
  • (+) ボタンをクリックして、API KEYを作成。このとき権限は「Finance」以上にする
    その後
    再度、Users and Accessから、Download API Key で、秘密鍵（Private　　Key）をダウンロードする。
    このダウンロードは一度しかできないので、厳重に管理する 
-----BEGIN PRIVATE KEY-----
................................................................................................................................................................................................
-----END PRIVATE KEY-----

みたいな内容です。


① Issuer Key
② KEY ID
③ 秘密鍵（Download API Key）

【AppStoreConnect】新規DL数を取得

新規DL数の情報を取得するエンドポイントは、https://api.appstoreconnect.apple.com/v1/salesReports

なのでApp Store Connect API側の設定として、これら（IssuerID、KeyID、秘密鍵、エンドポイント）を用意します。
あと、上記のEndpointの必須パラメータとして「vendor_id」があるのでそちらも合わせて用意します。

vendor_idの取得

vendor_idは、https://nsdev.jp/apple/ios/8813.html を参考に
売上概要レポートの画面に出る

troccoの設定

【trocco】転送設定の新規作成

転送元に、App Store Connect API
転送先に、Snowflake

を選択し、「この内容で作成」をクリックします。


設定名と必要とあれば共有するグループを設定します。

【trocco】②転送元App Store Connect APIの設定で「＋接続情報を追加　」をクリックします

  • 名前
  • Issuer ID
  • Key　ID
  • 秘密鍵
    を入力します。

必要に応じて、「共有するグループ」も設定し、
「保存」をクリックします。

取れるパラメータ

https://api.appstoreconnect.apple.com/v1/salesReports　 に必要な情報はこちら
https://developer.apple.com/documentation/appstoreconnectapi/download_sales_and_trends_reports

  • filter[frequency] : DAILY, WEEKLY, MONTHLY, YEARLY
  • filter[reportSubType] : SUMMARY, DETAILED
  • filter[reportType] : SALES, PRE_ORDER, NEWSSTAND, SUBSCRIPTION, SUBSCRIPTION_EVENT, SUBSCRIBER
  • filter[vendorNumber]:https://help.apple.com/app-store-connect/#/dev3a16f3fe0 の「ベンダー番号の表示」を参照

あとは、
filter[reportDate] で日時指定する
↑を指定しない場合は、前日分が集計される（2022年1月19日22時に実行したら、2022年1月28日分が集計された）
なお、filter[frequency]をWEEKLYとした場合は、
https://tech.pepabo.com/2021/03/29/app-store-connect/ にこのように描いてらっしゃいます。

WEEKLYのデータを取るときは注意が必要で、週の日曜日の日付を指定する必要があります。
取得するタイミングも注意が必要で時差を考慮する必要があります。
具体的には、日本標準時（JST）と、太平洋標準時（PST）では、17時間の時差があります。
これに加え、レポートの配信タイミングがPSTの午前8時までに配信されます。
この点に注意をする必要があります。

取れるパラメータの組合

reportType reportSubType frequency version
SALES SUMMARY DAILY, WEEKLY, MONTHLY, YEARLY 1_0
SUBSCRIPTION SUMMARY DAILY 1_2, 1_3
SUBSCRIPTION_EVENT SUMMARY DAILY 1_2, 1_3
SUBSCRIBER DETAILED DAILY 1_2, 1_3
NEWSSTAND DETAILED DAILY, WEEKLY 1_0
PRE_ORDER SUMMARY DAILY, WEEKLY, MONTHLY, YEARLY 1_0

reportType について

reportType 内容 説明URL
SALES 売上概要レポート https://help.apple.com/app-store-connect/#/dev15f9508ca
PRE_ORDER 予約注文レポート https://help.apple.com/app-store-connect/#/dev831e72dd9
NEWSSTAND 雑誌／新聞レポート https://help.apple.com/app-store-connect/#/itc9eefc011c
SUBSCRIPTION サブスクリプションレポート https://help.apple.com/app-store-connect/#/itc5dcdf6693
SUBSCRIPTION_EVENT サブスクリプションのイベントレポート https://help.apple.com/app-store-connect/#/itc0b9b9d5b2
SUBSCRIBER サブスクリプション登録者レポート https://help.apple.com/app-store-connect/#/itcf20f3392e

【trocco】②転送元App Store Connect APIの設定

API Endpoint : https://api.appstoreconnect.apple.com/v1/salesReports
入力ファイル形式： CSV/TSV ※検証まち
カスタム変数：

  • targatDate : 文字列　: 2022-01-15
    クエリパラメータ：
  • filter[reportType] : SALES
  • filter[reportSubType] : SUMMARY
  • filter[frequency] : DAILY
  • filter[version] : 1_0
  • filter[vendorNumber] : （VENDOR_IDを入力）
  • filter[reportDate] : targetDate

【trocco】③　転送先Snowflakeの設定

適宜設定する
Snowflakeの設定する上で注意点としては、すべて「大文字」で行うこと
→　DB名、スキーマ名、テーブル名、WAREHOUSE名
小文字で書くと　Snowflake上で　SELECT * FROM "hoge" みたいに、" でくくらなければならなくなるので

テーブル名は、「APP_STORE_CONNECT_DL_SUMMARY_DAILY」としておきます。

転送モードは、「追記（INSERT）」

【trocco】 Step2 データプレビュー・詳細設定

プレビューが成功すると

と、スキーマプレビューと詳細設定が表示されます。

詳細設定

カラム名を大文字へ


Snowflakeに入れる場合、カラム名は　すべて大文字　にしておくほうが良いです。
※これは、小文字入りだと　　Select　　"Privider" from hoge; のように""でくくらなければならなくなるので

非常に面倒ですが、カラム名を大文字のみ（スペースは、_）　にします

転送時刻カラム設定

カラムを追加する　に変更します
転送するカラム名も大文字にしておきます（私は、INSERT_TIMESTAMP　としています）

ワークフローを使って過去分を全部入れる

新規ワークフロー作成

troccoの左メニュー ワークフロー をクリック
＋新規ワークフロー作成 をクリック

ワークフロー名：AppStoreConnect過去分一括登録
その他はとりあえずデフォルトで問題なし
共有グループは必要に応じて設定

フロー編集

trocco転送ジョブ　の横の + をクリックし、「App　Store　　Connect　API→Snowflake　SALES_SUMMARY_DAILY」　を追加

カスタム変数でループ実行　に　チェック
ループの種類：文字列展開でループ
対象カスタム変数：targetDate ( 転送元：App　Store Connect API )
1 : 2021-12-21
ループ回数を追加 をクリックしてループ変数を追加
2 : 2021-12-22
・・・・　以下必要な分だけ追加

※文字列の生成などをもっとうまくやる方法もあるかもだけど、ちょっといい方法が見当たらなかった
※Excelなどでパラメータに入れる文字列を生成して、一列まとめてコピペすると自動的にループが追加されるという裏技もある

ワークフローを使って、前日分を毎日自動で取り込むように設定する

前日分を集計する場合は、
パラメータ：filter[reportDate] の指定を削除する
カスタム変数の targetDate を削除

した転送設定を別途作成し、新たにワークフローを作成する。
今回はカスタム変数の設定はせず、新たに作った（filter[reportDate]を削除）を定期実行するようにワークフローを組む

画面下部のスケジュール設定 の横の + をクリックし、
毎日15時00分　
で登録をする

AppStoreConnectのDAILY集計は、UTFで計算されるみたいなので、前日分を集計するには、午前9時以降が良さそう（時差9時間なので）
余裕を見て15時00分（JST）で定期実行させてみようと思う

【追記】　前日分を10時（JST）で実行したところ

"status" : "404",[0x85]    "code" : "NOT_FOUND",[0x9f]    "title" : "The request expected results but none were found",[0xe1]    "detail" : "Report is not available yet. Daily reports for the Americas are available by 5 am Pacific Time; Japan, Australia, and New Zealand by 5 am Japan Standard Time; and 5 am Central European Time for all other territories."[0x1cb]  } ][0x1d1]}

このようなエラーが。

レポートはまだ利用できません。南北アメリカの日報は太平洋時間の午前5時までに入手できます。日本、オーストラリア、ニュージーランドの日本標準時の午前5時まで。他のすべての地域では、中央ヨーロッパ時間の午前5時です。

午前5時・・・・ってことは、9時間時差があるから14時。余裕を見て15時に実行してみます

【追記】
filter[reportDate]を削除し、1時間毎に起動してみたところ、
日本時間の21時起動分が成功しました。もう一日様子を見てみるつもりです

参考

https://zenn.dev/tatsumi0000/articles/570395ae258dc0
https://tech.pepabo.com/2021/03/29/app-store-connect/

Discussion