[Proposal] Creation and Maintenance of Dune Analytics Dashboards

Abstract

Creation and maintenance of Dune Analytics Dashboards.

About yourself - please share a little about yourself and your relevant experience. Links to past work and experience is particularly helpful!

I am currently pursuing a masters degree in Business Information Systems and lockdown has enabled me to dive deep into the abyss of Defi. My focus has been on the investment side of things till now, but since finding PICKLE I have chosen to contribute and try to progress this project with my skills and knowledge.

My previous work experiences are related to data analysis and project management in an international software development project for the treasury of a $100+ billion revenue company. To be transparent, my official position there was “Intern”, but I was basically the only one in the project who had the knowledge to tie all of the strings together and was frequently in contact with all kinds of stakeholders in the project. Due to severe restrictions in choice of databases and methods that I could use (Company IT guidelines -I am sure some of you can relate) many of the systems were quite old and outdated, but most of them ran on some format of SQL Databases, perfectly preparing me for the job of querying data on Dune now.

I can gladly confirm my work experience with someone in particular, I do not feel comfortable in sharing this publicly.

Anyways, the Dashboard I have already built should be proof enough already that I can do what I describe in the following.

Solution & motivation

Completed Work

I built a staking dashboard on Dune.Analytics to track the allocation of Pickles in the Uniswap $ETH/$PICKLE Pool, staked $PICKLE in the staking contract and $PICKLE which are not staked in either of them (“freesupply”).

In Progress

I am already working on creating a dashboard that surfaces the data relating to the PJars and total value locked with historical data and denomination in USD. Over time this will most likely incorporate more things, but that’s my main focus for now.

Motivation

Making data publicly available is hugely important for any defi project as it creates transparency and creates an opportunity for stakeholders to analyse projects more in depth. Dune.Analytics is shaping up to become the go-to data source as it is the most open and customizable source of data for Ethereum projects available right now. Having this dashboard(and future dashboards) allows us to surface the data needed to progress our protocol and reevaluate our decisions.

It also allows investors to take a deeper look into the Pickle Ecosystem and base their decisions on these.

You might have also seen this google sheet and the graphs which stem from there at some point. I also intend to keep this up-to-date and eventually incorporate more data into to make calculations of P/E, reasonable valuation and so forth possible.

Further thoughts on this proposal

Dune isn’t exactly the best platform for surfacing data yet and I am sure that people like @jintao or Zippo (that guy from sushiswap who built all the dashboards) are probably going to come up with more elegant and visually more appealing versions of dashboards, but I think having a unbiased dashboard on Dune that is able to be verified by any third party due to the open nature of dune is an important and a worthwhile pursuit.

Deliverables & timeline

Staking Dashboard - already delivered

Total Value Locked Dashboards Completion- up to 1 Weeks from time of proposal (23.10.2020)

Cost

Since there is no frame of reference this is quite hard for me to judge.

As I see it, with the amount of work I have put in and will be putting in the future I am contributing valuable work for the protocol that is in the hands of the community with its inception(dune queries are always public). I will also include proper documentation once this proposal passes as to allow others in the future to be able to work with my queries.

I am requesting 8 ETH for my continued work on Dune Analytics Dashboards.
I worked quite a lot of Hours on these topics in the last 2 weeks and will probably do so for the next 2 weeks.

I have no Idea if this a fair or justified amount, I am basing this on my monthly salary if I would have chosen to work instead of continuing my studies. I gotta pay taxes on these and intend to do so, so I’ll be left with about 5 ETH in the end.
If anyone thinks this is to high or to low I am happy to hear feedback on this and reiterate on this.

On this amount I will work until I have finished what I intend to do as described above and request further funding if I see fit or anyone else in the community sees fit.

Closing

I am truly a believer in this Team and most of all this community and would be excited to be a “Pickle Private first class” (private first class is the next military rank, would be funny and fitting if we use this).

I excel at giving others the data they need to make the right decisions and aim to fulfill this role in this project.
It would be an honor to be the first pickle private first class.

Best 0xboxer / Florian

Creation and maintenance of Dune.Analytics Dashboards
  • Approve Proposal
  • Reject Proposal

0 voters

7 Likes

18/21 votes (86%) in favor of this CAP. Looks good!

Is this work to become the sole property of Pickle Finance, or will it adapted for other projects as well?

*edit
To clarify, if this open-source can’t anyone duplicate it, negating it value to some degree?

You are certainly right in the observation that anyone is able to copy my work with very low transactional costs.
I am deploying SQL code in DUNE that is open for anyone to read, so if let’s say Dracula Protocol wants to make similar dashboards their most efficient way of doing this would be to take my queries and modify them for their own needs. This can quite easily be done, but needs to be adjusted in some ways for their own needs.
I myself certainly learned to build these dashboards by looking at what other people did and how they solved certain problems. It basically is an open Library as can be found in many other applications where coding is involved. Our whole Protocol is Open Source and so will be our database infrastructure on Dune.Analytics.
I actually think it’s a neat feature and would be happy to see my code used elsewhere as it progresses the whole of Dune Analytics, which in turn enables me to use implementations of someone else’s code snippets.

1 Like

Yeah, I see what you are saying.

My only thought was that it might make more sense to do it with a view to adapting it to work for all projects (like liquidity.vision) and use advertising for revenue. Probably over complicating things though.

@0xBoxer congrats on having your proposal passed! And thank you for your work on this so far.

The team would like to propose that you finish all of the stated deliverables before the reward is paid out so that we can help iron out any details if necessary. But we like what we see so far :slight_smile: Does that sound reasonable to you?

This does indeed sound reasonable. I’ll keep updating this thread as I progress in my work.

#1 I am revamping most of my queries to a more elegant format. My initial queries were quite a “brute force” approach, this now limits me quite a bit so I have to compose them in new formats to allow further data extraction.

#2 I am also working on the integration of pjar 0.88 and a graph showing all Jars at once.

#3 Pjar 0 queries went online yesterday.

4 Likes

I was quite busy with other stuff this week, but I just completed the rewriting of all my queries.

The Dashboard is now fed from a single query that feeds from all transactions that happen inside the
Pickle ecosystem. I also track all Jars at once now which is pretty convenient to see TVL.
The queries are IMO as clean as possible and should be easier to maintain in the future.

Next up is withdrawals and deposits.
I posted some messages in #feedback on discord for feedback on this, would be cool if some of you can take a look.

Also if anyone wants certain features feel free to request them in here.

4 Likes

I like this!! Really clean way to see our TVL breakdown at a high level.

This is awesome, love the page that shows you the jar specific trends too.

Is it possible to add Staking and Pickle-ETH to the same page, including those in the total TVL? Actually not sure whether the app.pickle.finance TVL shown includes staking or not?

One thought on further breakdowns - would be interesting to show the breakdown of # of stakers in each jar and size of the larger ones? Would be interesting (potentially useful?) to know what proportion comes from 1-2 whales vs lots of smaller stakers?

Additional request: Can you track $ profits, by type (performance fee vs withdrawal fee), and by Jar?

2 Likes

After facing some issues yesterday with the change of all smart contracts regarding the strategies I solved those issues and added Total deposits/withdrawals and individual deposits/withdrawals.


The numbers for this week are not 100% accurate since the strategy migration forces me to exclude a bit of data for the migration period, but as far as I can see they still add up and this shouldn’t be a concern.

Next up is profits generated by the strategies and following this also $profits from withdrawals and fees as @chimaera suggested above.

This seems like a good idea, I’ll add the Pickle Power Pool in the next few days. I don’t think it would be honest to include the Staking contract.

This as well are good ideas, I’ll work on that in the Future.

3 Likes

@chimaera Great adds. I’m especially interested in the breakdown between performance and withdrawal fees as that’s a hard metric to manually calculate.

1 Like

Mate, this is great work! I would also like to see as @chimaera pointed out earlier the composition of each jar i.e how many wallets make up a jar to see how distributed or concentrated the value is. It also is a health metric as it is difficult to loose hundreds of wallets at once compared to loosing 2 or 3 big wallets in a short period with the most value. I guess as we have a more distributed jar contribution it will build resilience and so to easily visualise this would be a great feature!

Additional feature request: Now that Swaps have been enabled natively within Pickle would be cool to see any mass migration of TVL within the pickle, i guess in events like lets say UNI rewards are stopped. It will be nice to see how much of the value actually does stay within the system and not leave!

Did you see @0xBoxer 's draft earlier?

https://duneanalytics.com/0xBoxer/sample_1

1 Like

As most of you will have seen already I released a bunch of updates to the queries today.
This is for documentation.

Updates include:
-Total Withdrawals and deposits
-breakdown of withdrawals and deposits per PJar
-Total yield generated by harvests
-the corresponding fees/profits for the community treasury which relate to the before mentioned datapoints

I’ll not incldue screenshots here since it’s way better to look at the actual dashboard with updated dune UI.

I’ll keep improving these queries and start working on the features requested.
I will also start working on documentation, some of the queries reached 1000+ lines already, for full transparency and for someone else to be able to verify my code that seems like a worthwhile pursuit.

As always, if u see room for improvement in any way, be it layout or number formats and whatnot, I’ll happily see what I can do about those.

Thanks for all the positive feedback you guys are giving me, it makes working on this all the more
enjoyable.

3 Likes