[Tool] OSS - Tool to plan Steamgifts giveaways (v27.09.2017)

Open

OSS (Ouinx2's SpreadSheet)

Tool to plan giveaways on Steamgifts

Hi!

I decided to share my work with you as a contribution to the community! It could be useful for some of you (let me know).

At first I only wanted to understand my Steamgifts level and get an idea of the real price of my donations. Discrepancies with my CL displayed on Steamgifts and on SGTOOLS, as well as the plethora of games bundle have made the task more arduous. Finally I have included many parameters in the OSS but they will not be useful for everyone. Free to each to use these tools according to its way of proceeding. However, many hours were needed to create it. I'm not a developer, it's been years since I used a spreadsheet and most of the functions used were unknown to me. But I had fun creating it and I just had to do an extra job before submission (formatting & translation). It still can be improved, It will be or not…

Have fun.

Note: Sorry for my english and the translated help text inside the file. Original work is in french. ¯\_(ツ)_/¯

If you like this tool, you can thank me by adding me to your Steamgifts Whitelist. Ouinx2

What are we talking about?

The OSS (Ouinx2's SpreadSheet) is a statistical tool (and strategic, hence its name) used to plan giveaways on Steamgifts. It was originally developed for me but you are free to use it according to your own way of creating giveaways. The OSS will allow you to:

reproduce your Contributor Level (CL) on the fly, partially or totally
have an overview and detailed view of your giveaways
Analyze your past giveaways to understand your CL and find why it has changed. (eg by re-adjusting the MSRP value of the games according to market fluctuations)
Virtually inject a CV gain or loss for analysis and visualize changes on the fly
plan your next giveaways by simulating the gain they will bring you or according to the remaining to rise to the next level ...
estimate the loss generated by the switch of a "no-bundle" game to a "bundle" game
analyze in detail the real cost of your donations
estimate the performance of your donations according to their costs, the number of copies given and the type of game
have an overview of the distribution of your donations according to the type of giveaways
...

The OSS includes various branches and the OSCCGB (Ouinx's Simple CV Calculator for Games Bundle) - Couldn't find more stupid name - . It’s a quick and simple tool for bundle evaluation.

What does it look like ?

Check below the screenshot of what the OSS looks like. You can also compare with the one of my profile to verify the accuracy of the calculations.

The files

It works perfectly with Excel 2016 and probably with 2007, 2010 and 2013 versions. The 97-2003 version is likely to cause problems.
I have reworked it for (full) OpenOffice and (partially) Google Sheets compatibilities.

V27092017	Excel	OpenOffice
International	OSS 27092017 Int ¤	OSS 12092017 Int ODS
Française	OSS 27092017 Fr ¤	OSS 12092017 Fr ODS

¤ Main table extended to 1000 rows

I allow its distribution only if the file has not been modified.

Changelog

01.09.2017 :  
¤ Final version for french submission
¤ Calculated values arent rounded except for display
¤ Value Steam (USD) = US Market Value (USD) = "Full Value"
¤ Reduced Value = 15% Value Steam (USD)
¤ Iterative Malus (10%) applied from the 6th copy given.
¤ "No value" type implemented
¤ Type of giveaways: Everyone (E), Invite (I), Whitelist (W), Steam Groups (SG), W + SG (WSG)
¤ Step [Lower limit- Upper limit]
     0 [0-0,01] / 1 [0,01-25] / 2 [25-50] / 3 [50-100] / 4 [100-250]
     5 [250-500] / 6 [500-1000] / 7 [1000-2000] / 8 [2000-3000 ] / 9 [3000-5000] / 10 [5000-1E+28 ]

02.09.2017 :
¤ Add Overall total ($) to "Stats"

04.09.2017 :
¤ Add Steam Value (Bundle)→CV to "Quick Tools"
¤ Add "Steam Value - 0,01" column in "Buffer"
¤ Translation for international purpose

06.09.2017 :
¤ Formula simplification for OpenOffice compatibility (CL calculation)

08.09.2017 :
 ¤ Google Sheets compatibility

12.09.2017 :
 ¤ Minor corrections for full OpenOffice compatibility

13.09.2017 :
 ¤ Issue when extending the main table resolved by providing a 1000 rows ready to go table. If you need more, ask me directly
 ¤ Cell protection prevents links from working → Unprotected some cells

27.09.2017 :
 ¤ Change CV subtotal formula for accurate result
 ¤ Minor change to buffer sheet

Important

From 13092017 version, the file is provided with 1000 empty rows. If you need more, ask me directly (got an issue when extending the main table, not yet resolved).

I do not provide tutorial on spreadsheet use, however, it is better to handle purple information movements by copying / deleting only the values rather than by performing a full copy-paste or by moving cells and so on. : If you move cells you move the formula and its values, formatting, conditional formatting etc., which upsets the whole table. Prefer to copy / erase values (only values) or enter the data one by one.

Check screenshot below

To paste into Excel :
Select a purple cell → right click → Paste Options: Values (A) to paste only the values (without the formulas, without the formatting etc.)
¤ Note: If asked for another copy/paste, use Keep Source formatting (A) in the paste options
¤ Note: To delete data, use Clear content (B)
To paste into OpenOffice :
Select a purple cell → right click → Paste Special… (A’) → Text & Numbers (only).
¤ Note : If asked for another copy/paste, use Unformatted text in the paste options
¤ Note : To erase date, use Delete Contents… (B’) → Text & Numbers (only).

Bonus: How to scrap data?

If you already did a lot of giveaways, I provide an extra work to help you fill in the main sheet: a small tutorial to scrap your data (game name, related points and copies given if more than one). There are probably other ways but at least I show you one as example.
Check screenshots below

First, you’ll need a Web Scraper. It's a program able to analyze a web page and automatically extract information from it.
I propose OutWit Hub.
The light version, free, is sufficient to half-automate the task. Once installed, you will be able to extract data from a Steamgifts profile. Enter the URL in the address bar at the top of the (1) program.
Then, you’ll create a scraper:
~~Either you follow the instructions below to understand how it works, or you~~ import (2) the following xml file: Steamgifts scraper.xml (Modified 13/09/2017)
At first I wanted to explain how to create the scraper but I’m not happy with the result so just use the file provided, it’s easier.
Select the newly imported scraper (3) and Execute (4). Result should be similar to the screenshot.
- If the "empty" columns dont appear and the game names contain not wanted characters, make sure that Clean Text and Keep Order (5) are checked, Empty (6) the result sheet and Execute (4) again. (go back to “Scrapers” in the “automators” section)
- If needed, hide any columns that is not desired to get a result similar to the example : Click Edit (7) to customize the view. The black and bold names are for visible data. The yellow ones are for hidden data. → Right click on black and bold names that aren’t “Game name, Value, Empty 1, Empty 2, Empty 3, Empty 4, Quantity given” and then Hide field. (8)
- If needed, move columns with a drag and drop. Validate (7).
All that is left is to select all the extracted data in the right HTML formatting table and copy them. You can use the left array if you prefer but you’ll have to hide unwanted columns (9).
You will notice, if you have more than 25 giveaways, that the scraper only considers the first page. Once the scraper is activated, simply navigate to the next page of your profile (upper part of the screen (10)) so that it is automatically scrapped . If the empty option at the bottom right is set to Empty on Demand (11), the data is kept between page loads. Otherwise, if set to Auto-empty, the result table will empty before retrieving the new data. The Light version of Outweb Hub allows up to 100 results (ie 4 profile pages). So, you’ll have to empty the table to continue the extraction after 4 pages.
How to integrate the data in the OSS ?
- Once copied into memory simply paste them into the spreadsheet ... yes but ... not a simple paste . It is recommended to paste only the values without overwriting the formatting. It is not a question of aesthetics but of functionality. This precaution is valid for any data movement within the OSS. See the upper section called Important on how to paste/delete content.
- I recommend to paste recovered data into the buffer sheet before moving them to the main sheet. Why? Because the values recovered by the scraper are equal to the points needed to enter a contest. These points actually correspond to the MSRP (USD) rounded to the unit. As Steamgifts system use the true value of the game, the recovered values must be rectified. Most of the time you just have to remove $ 0.01 (ie a 2P game is worth 1,99$). That’s why there is a column on the right of the buffer sheet to automate the process. Copy and paste these corrected values into the Steam Value (USD) column.
Finally, select and copy the data from the buffer to the main table.