ORGANISING & REPORTING ON UNSTRUCTURED STEAM DATA

I originally created this Excel tool for the indie Steam game called Hero Defense - Haunted Island. An epic game with a new twist on the Tower Defense genre!

The objective was to help structure their Steam impressions and visits data faster, because the current Steam traffic dashboard has its limitations. There is however no reason why you can not use the same tool to help organise other unstructured data in your business. This tool will help save you at least 1 to 2 hours of your weekly reporting time! If you have not heard of Valve or Steam before, it is ok. Just follow along, and I promise that this article will bring you at least one good insight.

Steam is pretty much the biggest PC game distribution platform in the world. That said, I became rather annoyed with the way it gives access to its traffic and sales data. I have searched quite a while for ways to query this data to process and analyse it faster, but at the time of this writing; there seems to be no real API that addresses store impressions, visits and sales data. There is only an API that addresses game usage behaviour. Valve can definitely improve here! There is not one indie out there that would shy away from a company that saves them production/marketing/analytics time and cost. If I have missed anything or if there are better ways to address these challenges, please post it below. I always love to learn from others on how to be more productive! One last note before we get started, the solution was done in Excel, but there is no reason why one can not do it in a scripting language like VBA, Python or Ruby.

My current suggestions to Valve are:

1) Make it easier to export impressions and visits data in the Marketing & Visibility-section within Steamworks. Even a plain CSV export would be great for starters.

2) Make above export as structured as possible. Currently it really takes a long time to order this data if you want to supplement it with sales or wishlist data.

3) Create an API where marketers and analyst of game companies can more easily query and access data, and possibly also combine the marketing and sales data in one stream based on authentication keys.

4) Improve analytics and A/B testing on the Store & Checkout page. Rather than letting developers guess what their conversion rates are from the game page through checkout, give them the availability to at least know which of their visitors went on to the checkout page. This way they can not only test images, text and banners on the store page, but also know who went (at least) one step down the funnel.

Given all 4 points above, I have decided to address #2; the lowest of the 4 hanging fruit. Steam updates impression and visits data usually after 12'o'clock at night; once a day. So, it would not be possible for you to do real-time decision-making based on traffic data, but rather based on a daily view of historic data. To get started, please navigate to your Steamworks traffic breakdown under the 'Marketing & Visibility' - tab.

Rather than selecting a date range, go for one day at a time.

We determined that other than the impression packages (which drove most traffic from the home page to the store page), we wanted to also get a lower-level traffic breakdown for the following pages/features:

* Search (Organic brand power)

* Game Page (Visits to game or store page)

* External Website (External traffic drivers other than Steam)

Click on each of the above, allowing them to open up. Now, copy & right-click paste the 'text' in Excel. It is a bit of a manual process to get all historic daily data, because one can not yet export to CSV, but it will be worth your while once done. View below an example of two separate dates. Note especially how the rows are different from one another. There is no day which has the same output. This is exactly what we want to address. The next step in the process is, to build an index (2) and a matcher (3) which helps you to identify which of the new values matches with your index and thereafter allocate a number to it. You can ignore (1), because it is only a summary of the steps I will describe below. The matcher is made out of a (longggg) nested if-formula to look for which values in your index matches with which values in your unstructured data. Formula: IF(F13=$C$13,1,IF(F13=$C$14,2,IF(F13=$C$15,3,IF(F13=$C$16,4,IF(F13=$C$17,5,IF(F13=$C$18,6,IF (F13=$C$19,7,IF(F13=$C$20,8,IF(F13=$C$21,9,IF(F13=$C$22,10,IF(F13=$C$23,11,IF(F13=$C$24,12,IF (F13=$C$25,13,IF(F13=$C$26,14,IF(F13=$C$27,15,IF(F13=$C$28,16,IF(F13=$C$29,17,IF(F13=$C$30,18, IF(F13=$C$31,19,IF(F13=$C$32,20,IF(F13=$C$33,21,IF(F13=$C$34,22,IF(F13=$C$35,23,IF(F13=$C$36, 24,IF(F13=$C$37,25,IF(F13=$C$38,26,IF(F13=$C$39,27,IF(F13=$C$40,28,IF(F13=$C$41,29,IF(F13=$C$42, 30,IF(F13=$C$43,31,IF(F13=$C$48,32,IF(F13=$C$49,33,IF(F13=$C$50,34,IF(F13=$C$51,35,IF(F13=$C$52, 36,IF(F13=$C$53,37,IF(F13=$C$54,38,IF(F13=$C$55,39,IF(F13=$C$56,40,IF(F13=$C$57,41,IF(F13=$C$58, 42,IF(F13=$C$59,43,IF(F13=$C$60,44,IF(F13=$C$61,45,IF(F13=$C$62,46,IF(F13=$C$63,47,IF(F13=$C$64, 48,IF(F13=$C$65,49,IF(F13=$C$66,50,IF(F13=$C$44,51,IF(F13=$C$45,52,IF(F13=$C$46,53,IF(F13=$C$47, 54,IF(F13=$C$67,55)))))))))))))))))))))))))))))))))))))))))))))))))))))))

The next step is to paste your impressions and visits data into (4). I only choose to focus on these two metrics, but there is no reason why you can not use all the other derived metrics given by Steam. It is important to  know that the formula next to the newly pasted data; trims and changes the cell string values to lowercase before matching happens. As you can see, there are a) gaps in the data set and b) similar strings, which we need to account for before proper matching can happen. Take out all the gaps and rename the second "Search Results" to "search results (sub-search)". I added a conditional format on the "Search Result" to highlight them for me. This makes it much faster to identify where they are.  After this adjustment took place you will see several #REF's appear. You will have to first drag down the formula to view the adjusted end-result. Don't mind the several FALSE values,  it is only because I left out some of the names in the column next to it. Once that is done, copy all values in that block (including the green section) to the open white space. I purposefully only copied half of the block, but you should copy the whole block. Select and paste "Values". The column next to this newly pasted block checks if it matches with the number index in dark orange, and indicates whether the numbers do / don't match. The reason for this is, if I apply sorting ASCENDING (view below) and the index don't match, it will indicate which numbers I will need to add rows between. In this case there is a jump from 6 to 8, so I would leave a row open for 7 (I have not done it here, but you should). After you have done this you can select and copy the newly structured data to a new sheet. The end result would then look something like this.

Having your (impressions and visits) data structured over time is really useful, because it will help you to view what are the underlying trends in your data. Furthermore, you can supplement this data-set with sales data (or wishlist data), which is really one of the most important KPIs for your business. You can also see whether there are ways to influence future sales figures based on your findings.  

Loved this article? or Are you also planning to do a similar analysis project? Send me a tweet or subscribe to my blog called fromsprout. I would love to hear from you!