📈
MetricsDAO
  • The DAO for Web3 Analytics
  • MetricsDAO Ecosystem
    • Labor Markets Docs
    • Metrics App Docs
    • Metrics App Walkthrough
    • xMETRIC
    • xMETRIC Masters
  • NETWORKS
    • Quickstart: Make a Network
    • Networks 101
    • Network List
  • Operations
    • Constitution
    • Team Structure
      • Governor and Genesis Councils
      • Treasury
    • Tooling and Processes
    • Code of Conduct
    • Partners
    • Brand Assets
    • Directory
  • Education
    • Education Overview
    • MetricsDAO Courses
    • 🧑‍🏫Web3 Analytics 101: Async Course
    • 🧑‍🏫Web3 Analytics 101: Live Course
      • Segment 0 - Live Course
    • 📚Workshops
      • 2022-10-12 Open-Source Data Analytics via Dune Spellbook (Prerequisites)
      • 2022-04-21+ Interactive Data Visualization with Python
      • 2022-03-30+ Subgrounds Workshop Series
      • 2022-03-11 Data Curation with dbt
      • 2022-02-10 Exploring Terra Data with Flipside Crypto
      • 2022-01-27 Dune Analytics and NFTs
  • Analyst Resources
    • Analytics Challenge FAQ
    • Tips for an Impactful Analytics Dashboard
      • Dos
        • 1. Do: Decide on the broad type of dashboard
        • 2. Do: Get clear on your audience(s)
        • 3. Do: Define and deliver on your purpose
        • 4. Do: Communicate your Big Idea
        • 5. Do: Establish a logical structure
        • 6. Do: Define key metrics
        • 7. Do: Maximize the signal-to-noise ratio
        • 8. Do: Use best practices for visuals
        • 9. Do: Ask for feedback
      • Don'ts
        • 10. Don’t: Disregard your audience’s knowledge
        • 11. Don’t: Go too technical in the methodology
        • 12. Don’t: Overwhelm with charts
        • 13. Don’t: Overwhelm with text
        • 14. Don’t: Submit without proofreading
      • Extras
      • Closing Thoughts
      • Resources
    • Evaluation Criteria
    • Analyst Resources
  • Reviewer Resources
    • Reviewer Basics
    • Providing Effective Feedback
  • Extras
    • Experiments
    • Useful Links
Powered by GitBook
On this page
  • Recording
  • Resources
  • Follow Along with Starter Queries!
  • 0 - Exploring the Data
  • 1 - Collateral Transactions
  • 2 - Transaction Volume
  • 3 - User Behavior
  • Final Product - the Dashboard

Was this helpful?

  1. Education
  2. Workshops

2022-02-10 Exploring Terra Data with Flipside Crypto

Mentor's Workshop #2

Previous2022-03-11 Data Curation with dbtNext2022-01-27 Dune Analytics and NFTs

Last updated 3 years ago

Was this helpful?

Recording

Resources

We've created a new page under the Mentor's Circle GitBook! Resources we find helpful and that are referenced in the MetricsDAO Workshops will be added to an ever-expanding repository.

Follow Along with Starter Queries!

Feel free to Copy & Edit and make them your own!

0 - Exploring the Data

1 - Collateral Transactions

Using this query, we take a high level view of the collateral transaction volume happening with Anchor Protocol.

We're using a few SQL techniques here to aggregate data and look at trends across time. As mentioned in the workshop, the initial goal is to take a look at general trends so transaction volume, simply by count of collateral adds or withdrawals, each day is where we are going to start.

Additional tables from the workshop:

2 - Transaction Volume

Additional tables:

3 - User Behavior

High level volume is a useful starting point, but transaction count and USD volume flows only tell us so much. What are users doing and how is that changing across time? We can compute some powerful summary statistics using the same exact techiques.

Outliers should be investigated, and we can do a couple things about those. First, let's figure out why average bETH is spiking by finding the top transactions, by amount_usd.

Additional tables:

Final Product - the Dashboard

Key points about the final product, describe the data! What insights do you see? What was interesting along the course of the investigation. Write-ups do not need to be long, by any means, but telling the story of the data is very important.

Start by running a simple SELECT statement so we can see the data in the anchor.collateral table. When using SELECT * from a table for this purpose, always use a .

To do that we need to combine all data from a day into one number, so we use to flatten all timestamps from throughout the day into one, common, date. Then, we use the count() to simply count up the number of records associated with that day.

When using an aggregate function, we need to tell SQL what the "groups" are. In this case, we are going to the date that was calculated above, with date_trunc().

Finally, let's order the data by day using . Flipside's charting tool will generally automatically do this, but it's good practice.

using

using

Using the techniques learned above, let's determine the gross volume of collateral, in USD, for these transactions. This utilizes the aggregate function.

The amount_usd column is a computed value by the Flipside team. When working with these, always check for missing values. . From this query we see that there are a few dates with significant missing data. Accounting for missing data is beyond the scope of this workshop, but checking the quality and completeness of the data tables is essential to good analysis. This should be noted in our final dashboard.

Replace count() or sum() with in the aggregation, and we start to see trends emerge.

We use order by to specify which column and desc to ensure the highest numbers are ordered at the top. As with above, we need to exclude null values using . Again, set a limit! We only need the top few results.

is another aggregate function that we can use to interpret user behavior. Averages tend to be skewed as the continues to grow, so this provides us with valuable insight into what the more "average" user is doing.

📚
Analyst Resources
MDAO Workshop - Anchor Collateral Starter Query 0
LIMIT
MDAO Workshop - Anchor Collateral Starter Query 1
date_trunc()
aggregation function
group by
order by
Data Check
distinct
Gross Collateral Transaction Events
Collateral Transaction Events, bLUNA
where
Collateral Transaction Events, bETH
MDAO Workshop - Anchor Collateral Starter Query 2
sum()
Checking for Null Values in amount_usd
MDAO Workshop - Anchor Collateral Volume - Gross by event
MDAO Workshop - Anchor Collateral Volume - Gross by Collateral
MDAO Workshop - Anchor Collateral Starter Query 3
avg()
Query Here
where amount_usd is not null
median()
max()
MDAO Workshop - Anchor Average Transaction, by Collateral
Average Transaction, excl Outliers
Median Transaction Size
Median Transaction, by Collateral
Flipside Crypto
Final dashboard using our queries from above.
Logo