The PvP Finder is a spreadsheet constructed in collaboration with Marek Holmberg that helps Scouts, DOTLAN Navigators and other intelligence gatherers find PvP target rich areas.
Yto Itinen and Marek Holmberg organize a Nullbear Fleet every Thursday. The Fleet is made up of agile long range Frigates equipped with scanning equipment. Pilots go into a Drifter Wormhole and scan down Cosmic Signatures inside it. Half of those Signatures are Wormholes leading to Null Security Systems. A Scout then searches for inattentive Null Security pilots killing pirates. They are assisted by a Navigator whose job it is to look at DOTLAN and point the Scout towards Systems with lots of activity. Once a target is tackled by a Scout, the rest of the Fleet pounces on them, returns to the Drifter Wormhole and begins the find-tackle-kill cycle anew.
I joined the Nullbear Fleet because I wanted to see a Drifter Wormhole, but stayed for the company, lighthearted atmosphere and scanning.
The Problem written by Marek Holmberg
Our original modus operandi was to have the Fleet scan down Cosmic Signatures, have a look at the Wormhole information and put them into a shared bookmark folder, marking any Null Security connections as viable hunting paths. We would then check the first Wormhole on the list, the Scout would enter it, tell the Navigators the exit system, Navigators would then look up the Region on DOTLAN and relay a good hunting route to the Scout, provided the Region had any suitable targets in the first place. Once we are done with that Wormholes we move on the next one, usually in alphabetical order.
While that whole process was relatively quick and easy, we ended up spending precious time on Systems/Regions which didn’t look that promising on DOTLAN and which we never would have entered, had we known beforehand to where the Wormholes lead.
The first step towards a solution was to scan down the Cosmic Signatures and then also take a peek at the other side and relay the destination Systems to the Navigators so they could identify the best hunting grounds from the getgo. We ended up flooding the Navigators with a bunch of destination Systems, they had to compare 2 dozen browser tabs of DOTLAN and it was just very messy overall. The idea was right, but we needed a quick and easy way to structure all that intel for meaningful comparison and access.
Marek and I met five times to come up with a solution and concluded:
- a Sheet is easier to use and make then a Google Survey
- Bookmarks should be a sufficient input into the Sheet
- the Sheet should automatically find the System's Region, generate a DOTLAN link, fetch DOTLAN NPC kills and delta
- columns should be sortable (that's more difficult then you imagine due to how Sheets update and compute cells!)
- NPC delta is more indicative of player activity then NPC kills
- NPC kills for a single System is a useless metric
- peek NPC kills in a Constellation gives too many false negatives
- peek NPC kills within 5 jumps is an exceptionally useful and difficult to compute metric! So that's what I did next
- we aren't sure if this is worth the effort
Finding all Systems within five jumps of each System was the most difficult part because there are:
- 8285 Systems
- 13756 Stargate pairs
However, after analyzing the requirements more thoroughly, I was able to:
- reduce the 8285 Systems to only 645 Null Security Systems containing Jove Observatories
- reduce the 13756 Stargate pairs to only 8464 Null-Null Security Stargate pairs
Next, I couldn't get GESI to pull data from EVE ESI without authenticating so I wrote an email to Blacksmoke16, the maker of GESI, and:
- Blacksmoke16 said I had to authenticate to let CCP know who was using their API
- Blacksmoke16 offered to help me find a solution and invited me to their Discord
- in Discord, Blacksmoke16 and I talked about what GESI can do and what problem I was trying to solve
- they told me how I could authenticate and set all the scope permissions to NONE
- I praised Blacksmoke16 for being helpful and speedy
Still, I couldn't find a way to fetch Systems (NPC) kills for the previous and current hour to compute the NPC delta (as seen on DOTLAN). I decided against using GESI and pulled data from EVE ESI /universe/system_kills/ manually. This means that the spreadsheet would require human input once every hour. That's acceptable considering I'd be able to calculate the NPC delta.
To get the freshest data and synchronize the spreadsheet with DOTLAN I had to discover when the Systems (NPC) kills get updated: its on the 50th minute mark of every hour.
After I gathered all the data I used every spreadsheet tip and trick I knew to:
- construct sortable column formulas
- find data by a key
- apply a function to each member of the array
- find the maximum value
To construct and operate the spreadsheet I use:
- Google Sheets
- EVE ESI and EVE SDE
- Arataka Research Consortium's Jove Observatory list
- Python and a graph analysis library
As with my Total Loyalty Point Offer Comparator, it is my strong belief that digital tools and knowledge should be available to the largest possible audience.
The PvP Finder was tested live during the 08-04-21 Nullbear Hunt. After a short lived confusion, apprehension and doubts about its complexity, outward appearance and usefulness it received rave reviews.
Although the spreadsheet was designed with the Nullbear Fleet in mind, large parts of it can be repurposed for Null Security and Low Security roams, for analyzing traffic patterns, activity density or any other creative purpose.
Feel free to contact Arin Mara via EVE email or firstname.lastname@example.org.