Page 1 of 1

PvP Finder

Posted: 2021.04.13 17:00
by Arin Mara
PvP Finder

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.

The Solution

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 :P
  • we aren't sure if this is worth the effort :D

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:

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 :)

Re: PvP Finder

Posted: 2021.04.13 17:56
by Selara Trondinvale
Thanks for making this!

One question: It looks like the PVP sheet is pretty temporal in nature, in that the value of the intel will quickly degrade — what was true about a system's activity 60 minutes ago may not be true anymore.

Given that, do you think it would make sense to gradually deprecate items so that they die or are no longer counted in the "Find Prey" activity after a certain amount of time?

Re: PvP Finder

Posted: 2021.04.14 14:34
by Arin Mara
My pleasure! :)

That feature could be automated, but I predict the effort required would be many times greater then the convenience gained.
How would you deprecate a data point? :) Would you turn it to 0 after some time? How would you track the age of a data point? I'm open to any and all suggestions :)

Re: PvP Finder

Posted: 2021.04.16 13:56
by Kimeemaru
I used the sheet yesterday, during my second Nullbear hunt, it was my first time as a navigator.

First of all, what a great help, the top systems on the list make it very easy to find and navigate to some juicy nullbears.

On the Find Prey tab it lists npc deltas to sort the list. Another option could be to add the ADM of the contellation or an average of 5 jumps, like with the delta. I'm not completely sure how useful the ADM is for this sheet to be honest. It has no bearing on the amount of ratters in a system, though I would guess a high ADM means more miners / haulers. Also juicy I would imagine.

Re: PvP Finder

Posted: 2021.04.17 10:48
by Arin Mara
Hmm... yea, I'll consult Yto and Marek on this.

Then there also the problem of sourcing data. I can see DOTLAN has an Active Defense Multiplier overlay, but I can't see where they source their data.
It is not under or ... or perhaps it is but I can't find it :D

Re: PvP Finder

Posted: 2021.04.19 10:20
by Kimeemaru
I took a quick look and I believe it’s the vulnerability_occupancy_level parameter in the /sovereignty/structures endpoint.

I checked it for 30004867 (random pick) which is F-ZBO0 in Tenerifis. Currently listed as 2.6 ADM in ESI and dotlan.

Re: PvP Finder

Posted: 2021.04.19 16:34
by Arin Mara
You are absolutely right! I'll write it down.
I've consulted Marek and it seems the benefit is disproportionately small compared to the effort required to implement the Active Defense Multiplier columns.

Besides, the siren that is the Wiki calls to me :P Whats that Wiki? A capsuleer can't complete the 13th Sisters of EVE Level 1 Blood Soaked Stars Epic Arc Mission because the Item and System names are out of date? Well... :P

Re: PvP Finder

Posted: 2021.04.21 23:42
by Selara Trondinvale
Arin Mara wrote: 2021.04.14 14:34 How would you deprecate a data point? :) Would you turn it to 0 after some time? How would you track the age of a data point? I'm open to any and all suggestions :)
The simplest way to do it is that you add a created_at field which is populated when the row shows up for the first time (or you just ask others to add it when they add to the sheet). If it's more than some fixed time after the report, say two hours, you don't include it since it's stale.

Re: PvP Finder

Posted: 2021.04.22 12:27
by Arin Mara
UUuuuu, alright, I'll try to play around with the functions and see what comes out of it :)
Thank you very much!

Re: PvP Finder

Posted: 2021.04.30 13:13
by Johan IV Hilanen
Sorry for the huuuuggge delay in posting on here. Arin, this spreadsheet is amazing. The technical aspects fly right over my head and as you noted above, when I initially saw it I was not sure what it was going to do.

However, once we got going and once the sheet was populated, the true potential really came out. The data is useful, helps to focus on the areas most likely to find content and mean the 'navigators' have the ability to plan in advance while the scout works. This is invaluable for a good roaming fleet. It is clearly very useful on the Nullbear fleet where the wormhole connections can be so varied and I am sure there are other applications too.

I look forward to continuing to navigate with this and to see how you can improve or add to this in the future. Great job!

Re: PvP Finder

Posted: 2021.05.01 19:19
by Arin Mara
Aaaaaw! :)

Thank you so much Johan :)