PvP Finder

For questions regarding the mechanics of exploding other people.
Forum rules
This forum can be viewed by the public.
Post Reply
User avatar
Arin Mara
Member
Member
Posts: 262
Joined: 2020.07.10 13:53

PvP Finder

Post by Arin Mara »

PvP Finder
Image

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.

Background

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

Summary

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 maraarin@gmail.com. :)
User avatar
Selara Trondinvale
Member
Member
Posts: 19
Joined: 2021.02.27 03:58

Re: PvP Finder

Post 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?
User avatar
Arin Mara
Member
Member
Posts: 262
Joined: 2020.07.10 13:53

Re: PvP Finder

Post 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 :)
User avatar
Kimeemaru
Member
Member
Posts: 16
Joined: 2021.03.08 20:26

Re: PvP Finder

Post 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.
User avatar
Arin Mara
Member
Member
Posts: 262
Joined: 2020.07.10 13:53

Re: PvP Finder

Post 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 https://esi.evetech.net/ui/#/Sovereignty or https://esi.evetech.net/ui/#/Universe ... or perhaps it is but I can't find it :D
User avatar
Kimeemaru
Member
Member
Posts: 16
Joined: 2021.03.08 20:26

Re: PvP Finder

Post 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.
User avatar
Arin Mara
Member
Member
Posts: 262
Joined: 2020.07.10 13:53

Re: PvP Finder

Post 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
User avatar
Selara Trondinvale
Member
Member
Posts: 19
Joined: 2021.02.27 03:58

Re: PvP Finder

Post 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.
User avatar
Arin Mara
Member
Member
Posts: 262
Joined: 2020.07.10 13:53

Re: PvP Finder

Post 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!
User avatar
Johan IV Hilanen
Member
Member
Posts: 13
Joined: 2019.08.10 12:58

Re: PvP Finder

Post 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!
User avatar
Arin Mara
Member
Member
Posts: 262
Joined: 2020.07.10 13:53

Re: PvP Finder

Post by Arin Mara »

Aaaaaw! :)

Thank you so much Johan :)
User avatar
Arin Mara
Member
Member
Posts: 262
Joined: 2020.07.10 13:53

Re: PvP Finder

Post by Arin Mara »

During todays Yto-Marek Nullbear's I've discovered that the NPC kill data update happens at the 10th minute mark rather then at the 50th minute mark as was previous. Additionally, DOTLAN's maps now update at the 5th minute mark rather then at the 50th minute mark. The winds have changed. The Sheet will raise the sail and change course...

I'll consult the Master and Commander about how to resolve the following conundrum:
20:05 - data updates
20:10 - DOTLAN updates with 19:05 data (data is 65 minutes out-of-date!)
21:05 - data updates
21:10 - DOTLAN updates with 20:05 data (data is 65 minutes out-of-date!)
22:05 - data updates
22:10 - DOTLAN updates with 21:05 data (data is 65 minutes out-of-date!)
... when do I update the data with fresh data? If I update it at the 5th minute mark, then the Sheet will be out of sync with DOTLAN for 65 minutes. If I update the Sheet at the 10th minute mark, then we'll act upon data that is 65 minutes out-of-date. At what point should I update the Sheet? Hmmm...
User avatar
Arin Mara
Member
Member
Posts: 262
Joined: 2020.07.10 13:53

Re: PvP Finder

Post by Arin Mara »

Suspicious of my pen and paper data recording method I constructed a program to automatically query the ESI NPC Kills every minute and then inform me if the data has been updated. Here are the filtered results:
Spoiler
2021-05-12 08:16:39.171 | INFO | __main__:main:42 - update?=False
2021-05-12 08:17:48.945 | INFO | __main__:main:42 - update?=True
2021-05-12 08:18:56.451 | INFO | __main__:main:42 - update?=False
2021-05-12 10:17:20.198 | INFO | __main__:main:42 - update?=False
2021-05-12 10:18:26.185 | INFO | __main__:main:42 - update?=True
2021-05-12 10:19:31.611 | INFO | __main__:main:42 - update?=False
2021-05-12 13:17:01.440 | INFO | __main__:main:42 - update?=False
2021-05-12 13:18:06.346 | INFO | __main__:main:42 - update?=True
2021-05-12 13:19:11.461 | INFO | __main__:main:42 - update?=False
2021-05-12 14:16:33.874 | INFO | __main__:main:42 - update?=False
2021-05-12 14:17:38.745 | INFO | __main__:main:42 - update?=True
2021-05-12 14:18:43.872 | INFO | __main__:main:42 - update?=False
2021-05-12 15:16:46.494 | INFO | __main__:main:42 - update?=False
2021-05-12 15:17:51.839 | INFO | __main__:main:42 - update?=True
2021-05-12 15:18:57.592 | INFO | __main__:main:42 - update?=False
2021-05-12 16:16:31.689 | INFO | __main__:main:42 - update?=False
2021-05-12 16:17:37.289 | INFO | __main__:main:42 - update?=True
2021-05-12 16:18:43.548 | INFO | __main__:main:42 - update?=False
2021-05-12 17:17:12.448 | INFO | __main__:main:42 - update?=False
2021-05-12 17:18:17.807 | INFO | __main__:main:42 - update?=True
2021-05-12 17:19:23.146 | INFO | __main__:main:42 - update?=False
2021-05-12 18:16:44.250 | INFO | __main__:main:42 - update?=False
2021-05-12 18:17:50.455 | INFO | __main__:main:42 - update?=True
2021-05-12 18:18:56.595 | INFO | __main__:main:42 - update?=False
2021-05-12 19:16:40.780 | INFO | __main__:main:42 - update?=False
2021-05-12 19:17:47.029 | INFO | __main__:main:42 - update?=True
2021-05-12 19:18:52.789 | INFO | __main__:main:42 - update?=False
Turn out that my previous 50th and 10th minute mark estimations were both wrong. The new data can be fetched as early as the 20th minute mark. Unfortunately, DOTLAN still updates at the 50th minute mark. If I update the Sheet only when DOTLAN updates, the data will be 90 minutes old. In consultation with Marek, I will update the Sheet as soon as possible and Marek will instruct the Navigators to disregard DOTLAN's numbers and trust the Sheet instead (a tough but necessary ask).
User avatar
Arin Mara
Member
Member
Posts: 262
Joined: 2020.07.10 13:53

Re: PvP Finder

Post by Arin Mara »

During the latest Nullbear Dark Mode was mentioned. Kimeemaru, Mike, Johan, Clemence, and anyone else, let me hear what you'd like to see :)

Here are a few prompts:
  • What would the Dark Mode look like? Do I just invert the colors?
  • How dark should Dark Mode be?
  • Should there only be Dark Mode or should I keep the Light Mode too?
  • Which Sheet tabs should be Dark? Only "Find pray!" or another one as well?
  • Can you show me an example of a Dark Mode that you like? Can you show me an example of a Dark Mode that you dislike?
User avatar
Kimeemaru
Member
Member
Posts: 16
Joined: 2021.03.08 20:26

Re: PvP Finder

Post by Kimeemaru »

A good dark mode color scheme has quite a few guidelines that should be followed. A good dark mode example would be discord. A bad one would be dotlan, in my eyes. While discord has good contrasts in both light(eeeww) and dark mode, only dotlans light mode has good contrasts.

Usually a good rule is to avoid pure black and fully saturated colors. Another one is that the more in the back an element is the darker it is, in front it gets lighter. It’s all a game of contrasts really. It’s only a spreadsheet, not a complicated responsive website but it can be an annoying endeavor either way.

Now, that’s quite a ramble while I actually wanted to suggest something totally different, which lets any navigator choose their theme without you having to do anything for now. People can install an addon or extension for their browser. For Firefox that can be Dark Reader. Chrome probably also HD a similar addon. My fear is that this won’t work for google sheets, this needs to be tested(not at the pc atm).

https://addons.mozilla.org/en-US/firefo ... arkreader/
Post Reply