I think its important to take a look at whether or not Crucible is really making inroads into the players lost during the massive clusterfuck that was Incarna. Afterall, it wasn’t very long ago that blogs like this were being written. I think this is an interesting comment in it:
One last thing: this chart seems to prove what a stagnant state EVE is in at the moment: other than the +60D peak after Incursion, the 30 day rolling average number of players logged into EVE has not changed from 30,000 in 27 months.
I took the liberty of poking the eve-offline data and putting it into a more useful form. There’s some massaging of the data – namely I filtered out any row with less than 10,000 players and duplicated the previous day for any missing days. The goal here was to eliminate momentary “blips” in the chart because Chribba’s site went down, or because Eve had a particularly long patch. Those simply aren’t interesting data points for me here.
I’m also notoriously terrible at Excel wizardry, so I did most of this in a database – Oracle in particular. Mostly because it was handy, but also because it gave me the opportunity to learn more about Oracle’s analytic functions. I came from a PostgreSQL background, and so far at this job I haven’t really had a lot of reason to play with them. Maybe its not having to do a ton of manual BI requests, or maybe its just because people want the raw data to work their own Excel Wizardry. Dunno, but at least some small hole in my knowledge is filled.
For what its worth, this is the basic form of the query:
select
to_char(logtime, 'YYYY-MM-DD HH24:MI:SS') AS "date",
raw_pl AS "raw_max_players",
pl AS "adjusted_max_players",
trunc(avg(pl) over (order by logtime rows between 7-1 preceding and current row)) AS "7 day",
trunc(avg(pl) over (order by logtime rows between 30-1 preceding and current row)) AS "30 day",
trunc(avg(pl) over (order by logtime rows between 60-1 preceding and current row)) AS "60 day",
trunc(avg(pl) over (order by logtime rows between 90-1 preceding and current row)) AS "90 day",
trunc(avg(pl) over (order by logtime rows between 120-1 preceding and current row)) AS "120 day",
trunc(avg(pl) over (order by logtime rows between 180-1 preceding and current row)) AS "180 day",
trunc(avg(pl) over (order by logtime rows between 365-1 preceding and current row)) AS "365 day"
from (
select
t.day AS logtime,
max(pl) AS raw_pl,
last_value(max(pl) ignore nulls) over (order by t.day) AS pl
from days_table t
left outer join all_tz p on t.day = p.logtime
where t.day between (select min(logtime) from all_tz) and (select max(logtime) from all_tz)
group by
t.day
) x
order by
to_char(logtime, 'YYYY-MM-DD HH24:MI:SS')
These are the basic forms of the other tables and views:
create table pl (
logtime date,
pl number
);
create or replace view all_tz as
select trunc(logtime) as logtime, max(pl) as pl
from pl
where pl >= 10000
group by trunc(logtime)
;
create or replace view us_tz as
select trunc(logtime) as logtime, max(pl) as pl
from pl
where pl >= 10000
and to_char(logtime, 'HH24:MI:SS') != '00:00:00'
and cast(to_char(logtime, 'HH24') as number) between 0 and 6
group by trunc(logtime)
;
I remembered one of the things I wanted done the first time around was to split the data and see just how Eve fared in any given timezone. Now, I admit that I’m mostly interested in this because I play at a slightly odd hour and I find it frustrating to not find anyone to shoot at. So I took the time to filter the data based on Swearte’s suggestion on Twitter:
- US TZ: 00:00 to 06:00 Eve Time
- AUS TZ: 10:00 to 15:00 Eve Time
- EU TZ: 15:00 to 00:00 Eve Time
If you look carefully at the example timezone view, you can see some peculiar 00:00:00 filtering. I filtered out rows taken exactly at midnight because so much of the data set had the time portion stripped off – and thus I don’t feel that it was directly attributable to any particular timezone. Though looking at the way the EU TZ converges so heavily with the 60 day moving average, I could be persuaded to call it EU TZ.
Here’s the a graph of what I found (courtesy of Google Docs save chart image) – and its very encouraging for my timezone. From this perspective, it seems that Incarna was a total disaster and Crucible was a smashing success. There are some fascnating things in the numbers – like for example the US and EU TZ appear to be growing at a very similar rate, but the AUS TZ is taking off like a house afire.
The EU TZ had its all time 60 day rolling average high of 45.5k in Feb 2011, and by Dec 2011 it had dropped to 37.6k – down a hair over 17%. However, its been rising steadily and is back up to 41.8k – either an 11% improvement over the recent slump or only 8% down from the all time high.
The US TZ had its all time high of 33.1k in Feb 2011, and by October it plunged to 25.3k where it stayed until December. Since then, its been taking off fairly well and is back up to 29.6k. This means that the US TZ was down almost 24%, but has grown 17% since December for a net loss of 10.5%.
The AUS TZ had its all time high of 29.3k in May 2011 and was down to 22.7k by October – a 23.5% loss. By Dec 18th, the AUS TZ was hitting record highs and is now at 32.8. Crucible has seen almost 45% growth of the AUS TZ since the Incarna Slump – which is over 10% more than its ever been. This is fantastic news for me, because it means I will soon have more people to shoot when I’m awake.
So I guess at this point there’s nothing to do but congratulate CCP on their hit expansion.
The raw data is available on my blog here:
Filed under: Databases, Eve, Gaming, Software Development, Crucible, Databases, Eve Offline, Eve Online, max players, Oracle, PCU
Failing In So Many Ways 