Upgrade to Pro — share decks privately, control downloads, hide ads and more …

アプリストアからのレポート集計自動化 / App Report Automation

cc4966
November 22, 2019

アプリストアからのレポート集計自動化 / App Report Automation

アプリのダウンロード数や売上集計を自動化するためにやったこと(大変だったこと)
・App Store Connectからのレポート
・Google Play Consoleからのレポート
pixiv App Night - 2019/11/22 https://pixiv.connpass.com/event/153642/

cc4966

November 22, 2019
Tweet

More Decks by cc4966

Other Decks in Programming

Transcript

  1. • Google Play΍App Store͔ΒͷϨϙʔτूܭࣗಈԽ • ΍Γ͔ͨͬͨ͜ͱ • Google Play Consoleͷ࿩

    • App Store Connectͷ࿩ • ΍ͬͨ͜ͱ • ࢀߟϦϯΫू  Ξ΢τϥΠϯ
  2. • ࣗಈԽʹखΛ͚ͭΔલʹ஌͍ͬͯͨ৘ใ • Google PlayͷϨϙʔτ͸Google Cloud Storageʹશ෦஝ੵ͞ΕΔ • App StoreͷϨϙʔτ͸App

    Store Connect APIͰऔಘͰ͖Δ • ͔͜͜ΒόονͰσʔλΛྲྀ͠ࠐΈଓ͚Ε͹Φοέʔ  ΍Γ͔ͨͬͨ͜ͱ
  3. • ͦ΋ͦ΋Ͳ͏͍͏਺஋͕औΕΔΜ͚ͩͬʁ • Google Play ConsoleͱϨϙʔτ • App Store ConnectͱϨϙʔτ

    • ؒʹ߹Θͳ͔ͬͨͷͰը໘͸શ෦ݸਓϓϩμΫτͷ΋ͷͰ͢  ຊ୊ʹೖΔલʹ
  4. • Google Play Console͸ϒϥ΢βͳΒ1࣌ؒ͝ͱͷ਺஋ΛݟΕΔ • ϦϐʔλʔͳͲϒϥ΢βͷΈ͔Β͔͠खʹೖΒͳ͍৘ใ΋͋Δ • App Store Connect͸UTCͱPST͕͋ΔͷͰ1೔Λ8࣌ؒͱ16࣌ؒʹ෼཭Մೳ

    • AppΞφϦςΟΫε͸ϒϥ΢βͷΈ͔Β͔͠खʹೖΒͳ͍৘ใ͕ͨ͘͞Μ • ࣗಈԽՄೳͳൣғͰ͸1೔୯Ґ (PST) ͷ౷ܭ৘ใ͔͠खʹೖΒͳ͍ • PST: ଠฏ༸ඪ४࣌ -0800  ൵͍͠ϙΠϯτ
  5. • σʔλ͸͢΂ͯBigQueryʹอ؅͢ΔΑ͏ʹόονΛ૊Μͩ • ʮGoogle ͕ఏڙ͢ΔαʔόʔϨεͰεέʔϥϏϦςΟʹ༏Εͨاۀ޲͚ σʔλ΢ΣΞϋ΢εʯ • Looker͔ΒݟΕΔΑ͏ʹܨ͗ࠐΜͩ • ʮϦΞϧλΠϜͷϏδωε෼ੳΛ؆୯ʹ୳ࡧɺ෼ੳɺڞ༗Ͱ͖ΔϏδωε

    ΠϯςϦδΣϯειϑτ΢ΣΞͰɺσʔλ෼ੳϓϥοτϑΥʔϜʯ • iOSͷAppϢχοτ਺ͱAndroidͷ৽نϢʔβʔ֫ಘ਺  ΍ͬͨ͜ͱ • https://inside.pixiv.blog/jaggy/7353 • https://www.slideshare.net/KOTAROTAKAHASHI1/350-170250961
  6. • Google Play ConsoleͷϨϙʔτ • ࣮͸·ͩ΍ͬͯͳ͍ʢ2୒Ͱ·ͩະఆʣ • Google Cloud StorageͷCSVΛBigQueryʹΞοϓϩʔυ͢Δ

    • BigQuery Data Transfer Service for Google PlayΛ࢖͏ (25υϧ/ΞϓϦ݄) • ݄࣍Ϩϙʔτ͚ͩͲਵ࣌ߋ৽͞ΕΔ • ৽نϢʔβʔ֫ಘ਺͸ installs_xxx.yyy.zzzzzz_YYYYMM_overview.csv ͷ Daily User Installs ΛΈ͍ͯ·͢  ΍ͬͨ͜ͱ
  7. • Google Play ConsoleͷϨϙʔτ • Google Cloud StorageͷϨϙʔτϑΝΠϧ΁ͷΞΫηεݖ • Google

    Play Console > ઃఆ > σϕϩούʔΞΧ΢ϯτ > Ϣʔβʔͱݖݶ • ͜͜ͰϑΝΠφϯεݖݶΛ෇༩ • αʔϏεΞΧ΢ϯτʹ΋෇༩Ͱ͖Δ  ݖݶ·ΘΓ
  8. • App Store ConnectͷϨϙʔτ • App Store Connect API͔ΒϨϙʔτΛऔಘͯ͠Ξοϓϩʔυ •

    App Store Connect > ϢʔβͱΞΫηε > ΩʔʢAdminҎ্ͷݖݶʣ • ʮΞΫηεΛϦΫΤετʯ͢ΔʢҰ౓͚ͩಉҙ͕ඞཁʣ • ʮAPIΩʔΛੜ੒ʯ͢Δ • ΞΫηε͸Financeʢࢧ෷৘ใ͕ෆཁͳΒSales͓ΑͼReportsͰྑ͍ʣ  ΍ͬͨ͜ͱ
  9. • App Store Connect > ച্ͱτϨϯυ > Ϣχοτ਺ • λΠϜκʔϯͰPSTʹͯ͠શظؒΛબ୒

    • ର৅ͷΞϓϦͰϑΟϧλ • ϨϙʔτΛμ΢ϯϩʔυ • ͜ΕΛ܁Γฦ͢ͱશΞϓϦͷ1೥Ҏ্લͷAppϢχοτ਺΋खʹೖΔ  1೥Ҏ্લͷAppϢχοτ਺
  10. • App Store Connect > ച্ͱτϨϯυ > Ϣχοτ਺ • λΠϜκʔϯͰPSTʹͯ͠શظؒΛબ୒

    • ର৅ͷΞϓϦͰϑΟϧλ • ϨϙʔτΛμ΢ϯϩʔυ • ͜ΕΛ܁Γฦ͢ͱશΞϓϦͷ1೥Ҏ্લͷAppϢχοτ਺΋खʹೖΔ • Ͱ΋େมͳͷͰͳ͔ͥ௨৴ݟͯͨΒશظؒʹͨ͠ͱ͖ʹඞͣ߱ͬͯ͘Δ ͪΐ͏Ͳ1೥ΑΓલͷશΞϓϦͷ೔࣍AppϢχοτ਺Λ࢖͍·ͨ͠  1೥Ҏ্લͷAppϢχοτ਺
  11. • App Store Connect > ࢧ෷ͱࡒ຿ใࠂ > ্ͷ೥݄ΛΫϦοΫ > Լ෦ͷձܭΧ

    ϨϯμʔΛදࣔ • ௚ۙ3೥͸5ि, 4ि, 4िͷ܁Γฦ͠ • 2019೥11݄͸2020೥ͷPeriod 2  AppleձܭΧϨϯμʔ
  12. • The date of the report you wish to download

    based on the Apple Fiscal Calendar. The date is specified in the YYYY-MM format. • YYYY-MM  ࡒ຿Ϩϙʔτ
  13. • filter[reportDate] [string] (Required) The date of the report you

    wish to download based on the Apple Fiscal Calendar. The date is specified in the YYYY-MM format. • YYYY-MM • AppleձܭΧϨϯμʔͷ೥౓ͱPeriodΛࢦఆ͢Δ • 2019೥11݄෼͸2019-11Ͱ͸ͳ͘2020-02ͱͳΔ  ࡒ຿Ϩϙʔτ
  14. • filter[regionCode] [string] (Required) You can download consolidated or separate

    financial reports per territory. For a complete list of possible values, see Financial Report Regions and Currencies.  ࡒ຿Ϩϙʔτ
  15. • filter[regionCode] [string] (Required) You can download consolidated or separate

    financial reports per territory. For a complete list of possible values, see Financial Report Regions and Currencies.  ࡒ຿Ϩϙʔτ
  16. • filter[regionCode] [string] (Required) You can download consolidated or separate

    financial reports per territory. For a complete list of possible values, see Financial Report Regions and Currencies. • 46ͷϦʔδϣϯ͕͋ΔͷͰɺ46ճAPIΛୟ͘ඞཁ͕͋Δ • https://help.apple.com/app-store-connect/#/dev360aba524 • App಺՝ۚϓϩμΫτ͸શੈքʹ഑৴͞ΕΔ • ҠಈઌͰ΋ΞϓϦ͸࢖͑ΔͷͰؾΛ෇͚·͠ΐ͏  ࡒ຿Ϩϙʔτ
  17. • filter[regionCode] [string] (Required) You can download consolidated or separate

    financial reports per territory. For a complete list of possible values, see Financial Report Regions and Currencies. • 46ͷϦʔδϣϯ͕͋ΔͷͰɺ46ճAPIΛୟ͘ඞཁ͕͋Δ • ྲྀੴʹେมͩΑͶɺͱ͍͏͜ͱͰZZΛࢦఆ͢ΔͱશϦʔδϣϯΛ·ͱΊͨ ΋ͷ͕औಘͰ͖ΔΑ͏ʹͳ͍ͬͯΔ • ͨͩ͠2018೥2݄෼͔Β • ͦΕҎલ͸46ճୟ͔ͳ͍ͱ͍͚ͳ͍ʢ৔߹ʹΑͬͯ͸खಈָ͕ʣ  ࡒ຿Ϩϙʔτ
  18. • ࡒ຿Ϩϙʔτͱࢧ෷ϨϙʔτΛJOIN͢Δ • SELECT * FROM ӳޠࡒ຿Ϩϙʔτ JOIN ೔ຊޠࢧ෷Ϩϙʔτ ON

    ...? • App Store Connectͷ೔ຊޠ/ӳޠͷϔϧϓΛۦ࢖͢Δඞཁ͕͋Δ • ӳޠࡒ຿Ϩϙʔτʹ͸ࠃ·ͨ͸஍ҬͷISOίʔυɺࢧ෷௨՟͕͋Δ • ೔ຊޠࢧ෷Ϩϙʔτʹ͸೔ຊޠͷϦʔδϣϯίʔυɺࢧ෷௨՟ • ࡒ຿ϨϙʔτʹϦʔδϣϯίʔυೖΕΔͷ͸46ճAPIୟ͚͹Ͱ͖Δ͚Ͳ……  ࡒ຿Ϩϙʔτͱࢧ෷Ϩϙʔτ
  19.  -- աڈλΠ͕WWʹؚ·Ε͍ͯΔ͜ͱ͕͋ͬͨͷͰ௥Ճ WITH fixed_financial_report_regions_and_currencies AS ( SELECT * FROM

    `financial_report_regions_and_currencies` UNION ALL SELECT 'Rest of World', 'USD', 'WW', 'Thailand' ), fixed_financial_report_regions_and_currencies_in_japanese AS ( SELECT * FROM `financial_report_regions_and_currencies_in_japanese` UNION ALL SELECT 'ͦͷଞͷ஍Ҭ', 'USD', 'WW', 'λΠ' )
  20.  WITH fixed_app_store_territories AS ( -- 2ͭͷදͰදه༳Ε͕͋ΔͷͰͦΕΛٵऩ͢Δ SELECT * FROM

    `app_store_territories` UNION ALL SELECT 'CO', 'Columbia' UNION ALL SELECT 'KR', 'Korea' UNION ALL SELECT 'TZ', 'United Republic of Tanzania' UNION ALL SELECT 'LC', 'Saint Lucia' UNION ALL SELECT 'BN', 'Brunei Darussalam' UNION ALL SELECT 'KR', 'Republic of Korea' UNION ALL SELECT 'LA', 'People’s Democratic Republic of Lao' UNION ALL SELECT 'MK', 'The Former Yugoslav Republic of Macedonia' )
  21. • ࡒ຿Ϩϙʔτͱࢧ෷ϨϙʔτΛJOIN͢Δ • ӳޠࡒ຿Ϩϙʔτʹ͸ࠃ·ͨ͸஍ҬͷISOίʔυɺࢧ෷௨՟͕͋Δ • ೔ຊޠࢧ෷Ϩϙʔτʹ͸೔ຊޠͷϦʔδϣϯ໊ɺࢧ෷௨՟ • ISOίʔυɺࢧ෷௨՟ → Ϧʔδϣϯίʔυɺࢧ෷௨՟

    • Ϧʔδϣϯίʔυɺࢧ෷௨՟ → ೔ຊޠͷϦʔδϣϯ໊ɺࢧ෷௨՟ • ࢧ෷௨՟͕ඞཁͳͷ͸1ͭͷISOίʔυ͕ෳ਺ͷϦʔδϣϯʹؚ·ΕΔͷͰ  ࡒ຿Ϩϙʔτͱࢧ෷Ϩϙʔτ
  22. • AndroidΞϓϦɺiOSΞϓϦͰ΄΅ಉ౳ͷࢦඪͱͯ͠೔࣍৽نϢʔβʔ֫ಘ ਺ (Android) ɺ೔࣍AppϢχοτ਺ (iOS) ͕ࣗಈͰूܭ͞ΕͯΞϓϦ͝ͱʹ ֬ೝͰ͖ΔΑ͏ʹͳͬͨʢAndroid͸ͳΔ༧ఆʣ • AndroidΞϓϦͰͷΞϓϦɾΞΠςϜผͷച্ɾऩӹܭࢉ͕ࣗಈԽ͢Δ༧ఆ

    • iOSΞϓϦͰͷΞϓϦɾΞΠςϜผͷച্ɾऩӹܭࢉ͕൒ࣗಈԽ͞Εͨ • աڈ෼΋ಉ༷ʹ֬ೝͰ͖ΔΑ͏ʹͳͬͨ • ৽͍͠؍఺Ͱ਺஋Λݟ͍ͨ৔߹΋ΫΤϦΛॻ͖׵͑Δ͚ͩ  Ͱ͖ΔΑ͏ʹͳͬͨ͜ͱ
  23. • https://support.google.com/googleplay/android-developer/answer/ 6135870 • https://cloud.google.com/bigquery/docs/play-transfer • https://developer.apple.com/jp/app-store-connect/api/ • https://help.apple.com/app-store-connect/ •

    https://help.apple.com/app-store-connect/en.lproj/static.html • https://qiita.com/rsahara/items/c9d3743d926dd29a89e8 • https://blog.japantaxi.co.jp/2018/12/05/2159  ࢀߟϦϯΫू