AB
1
Steam Market Study v1.4
2
3
To use this "template" you'll need to copy it on your own drive (File -> Make a copy)
4
How to use :

1/ Add games to the "Steam game (Database)" tab, you just need to add the steam AppID into the blue cell, all the fields should autocomplete (to remove a game, delete the id). AppId can be found in the game store page url (numerical value after app/)

2/ Add games that are already in your game database through the "name" combobox into the "Market study(play with data)" tab, all the fields should autocomplete.This is separated from the database to be able to manipulate data without triggering a re-import through the api, as well as play with a subsets of the games (because filtering them would also trigger a re-import)

3/ You can analyze your data into the "market study (play with data)" tab, by adding your own fields at the righmost colum, filter data, hide column etc... and pluging some nice Charts onto those data into the "Market study (analysis) tab"

n.b: you can change the values used to evaluate number of copies and estimated revenue in the _Sales estimations (config) tab
n.b2: ! some fields are "grouped", you can see the reviews per lang (and more!) by opening the group ("+" icon) above "total reviews" header
n.b3: !! Remember that estimations for # of sales and "Estimated lifetime raw revenue", are very vague estimations, based on partial informations and a lot of assumptions. Expect the margin of error to be quite high and use these number carefully
5
6
7
8
9
10
11
12
13
14
15
16
17
Credits, rights and thanks
18
19
Created by (Gsheet template and json script Helper)Julien Cotret, Producer @ Alt Shift
20
Inspired byThe template shared by Sรฉbastien Benard under CC BY-SA 4.0 license. Inspired by the general idea, copied the sales estimation method and number format formula
21
LibrariesUsing ImportJSON by Brad Jasper and Trevor Lohrbeer
Project Page: https://github.com/bradjasper/ImportJSON
Copyright: (c) 2017-2019 by Brad Jasper
(c) 2012-2017 by Trevor Lohrbeer
License: GNU General Public License, version 3 (GPL-3.0)
http://www.opensource.org/licenses/gpl-3.0.html
22
23
Using Steam store Api (unofficial) documentation https://wiki.teamfortress.com/wiki/User:RJackson/StorefrontAPI
24
Using SteamSpy Apihttps://steamspy.com/api.php
25
26
LicenceThis document and attached scripts are released under CC BY-SA 4.0 license. Feel free to use and modify it!
You just need to keep the same license if you re-share it or a modified version of it
27
28
29
Future evolutions
30
31
Check random errors happening while pulling data from api (only happend when a lot of people did it simultaneously)
32
Using api requiring key/oauth to get more data from apis like isThereAnyDeal (https://itad.docs.apiary.io/) or paid data services. Could help extend the template to non steam games (as most store don't share public data that easily)
33
Might want to consider dropping ImportJson library and use https://developers.google.com/apps-script/guides/services/external, should be simpler to manipulate/extract data and use oauth
34
Improve the revenue formula by including the real base prices for major country through api, and weight them againt the % of review in said countries (or deducing it through lang). Some game make 70% of their units in china, which is much cheaper per unit, so the difference could be huge
35
Should provide a way to compare game by taking into account their release date (like comparing only the #units of the first two years)
36
Should improve the #units formula for old games, as the units per review factor will vary a lot over their lifetime
37
Improve the market study (analysis) tab with more insightful charts and summary (like median and quantile values)
38
Look into the refresh rate of the data imported, not sure how gsheets handles it for now but it seems overkill, might be a problem with huge dataset, might be a good idea to have a "last update" date somewhere and avoid refreshing data more than once a day (or less).
39
Add more meta informations/comment about each column header