flipside

    Try Quests to earn SOLTry Quests to earn SOL
    Share

    Tracking Realms DAOs

    Shows number of proposals for each DAO.

    Introduction

    Realms provides a platform for builders on Solana to create a DAO, manage their members, vote on proposals, and allocate their treasury.

    > decentralized autonomous organization (DAO) is a community with a shared bank account. Members of the DAO make decisions in a transparent and decentralized fashion, with smart contracts executing these decisions. > > source

    In this page, we can see list of DAOs tracked on Realms in this dashboard, a link to this page and first and last record of realms data used.

    By clicking on Overview, we can access the 2nd page:

    This page has 3 charts and a table:

    The first page of this dashboard:

    The Dashboard is created with Microsoft Power BI.

    You can access the dashboard Here

    Tracking Realms DAOs in MS Power BI

    In this dashboard, we want to know these parameters for some DAOs on Realms:

    • Number of Voters
    • Total Votes
    • Number of Proposals
    • Date of Last Proposal
    • Date of Creation

    The aim of this dashboard is to provide useful information for stakeholders so they can have a self-serving analysis based on it.

    db_img
    db_img
    db_img
    db_img
    db_img
    db_img
    db_img
    db_img
    db_img
    db_img
    db_img
    db_img
    db_img

    Shows number of unique voters for each DAO.

    This chart shows the days between new proposal creation on average.

    This table show some info about each DAO:

    • Number of proposals
    • Number of unique voters
    • Total Votes
    • First vote date
    • Last vote date
    • Last proposal creation date.

    The 3rd page shows DAO stats:

    The elements on DAO Stats Page:

    1: Selecting DAO

    2: Link to Selected DAO on Realms

    3: This section shows info about the selected DAO.

    > Note: If a single proposal had selected from Proposal table (4) , the info of that proposal will shown. If a timeframe had selected from the chart (6), the info about that timeframe will shown. but the first vote date in the last proposal and new proposal submission always reflect values for the whole DAO. > > * The first vote date for the entire DAO considered as DAO creation date on Realms and first vote date in the last proposal considered as last proposal creation date.

    4: Showing all proposals for the selected DAO or timeframe.

    5: If a single proposal had selected, this button activates and links to that proposal on Realms.

    6: This chart shows the number of votes, voters, and proposals for selected DAO by time.

    7: We can select which parameter to show on the chart.

    For example, The Psy Finance DAO is selected and we expand all down one level to see the monthly view. then select April 2022, As we can see the info about April 2022 is shown in the info section, and 4 proposals for that month are shown in the proposal table.

    Queries in Power BI

    We can see that all queries are depend on those two queries from Flipsidecrypto tables.

    Queries Codes:

    Fact - Votes:

    let Source = Json.Document(Web.Contents("")),#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"PROGRAM_NAME", "BLOCK_TIMESTAMP", "TX_ID", "VOTER", "VOTER_ACCOUNT", "VOTER_NFT", "PROPOSAL", "REALMS_ID"}, {"PROGRAM_NAME", "BLOCK_TIMESTAMP", "TX_ID", "VOTER", "VOTER_ACCOUNT", "VOTER_NFT", "PROPOSAL", "REALMS_ID"}),#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"BLOCK_TIMESTAMP", type datetime}}),#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"VOTER_NFT"}),#"Added Custom" = Table.AddColumn(#"Removed Columns", "Time", each [BLOCK_TIMESTAMP]),#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Time", type date}}) in#"Changed Type1"

    Dim DAOs:

    let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZZJk5s4HMW/SldXzS1e2GFueAEPDQ4GTINTqZQAAbIbsYjVn36ATlKdy8Q5zAWQ+AveT0968OXLswFwkj9/elZLx9T50oo5cG5KEuqpaCX0wenNsxenqeztitrR04KqaW0s35nowByYUKhZYWP0pdPrpDFPxX7Lv2AoqrLkekFf4Euv7MfytK4L8vdqVVcggstseud4rG6wJitAyHRCYY7J6i0f75A2+TAGFMWyguAtI8s6j8CwikC+Mo7q5+evn748qxUo4KQ/b/cWp9dpRphA35SvTSAJg1sZ2T5P7p7QH6+RV5jm1r10Y/lmoImrXrwuPpV8JSPZYKqjqUnoFAccpE8pVlj/qrEbVPyXlPfGahaxQlmySqarZYF/C6BasrmfCUwyPCkIAxx+5zgk8c4cTpTagbvm7HwatLbri6oaFtkWFFm2JUNmAXUqV5DK+3n5it37OeuVY3MLLde7bDnq0OoVn9VExH2YCXZoiR8kNQRWC5SBBJJlguq0Caae0YEa4noZ5tmKodcCJayZFcVKLL+WKHHB8BK7Ftl4QQNGWLChxC0kJmIWAQdAvBbEiGXZR9BN25/B7fwN4misloVb1tC3c4hPnexiccPe8SlTNXiuiEa97lPfNeoMbfTbvhrLBRIzdfiacWKb1jfN6JqONhmHP2884bzn3JfeNLCnnS/UAX/QQubXvVMv3hfdkjDLhiwgIPWCXoIM3HMMOjJPAEWL60WIqvDtIT9t/bibqYwc3+BO/jx5aYyX/K332zKXvSRQVNyI7OneHg95WToYXV9tz/cd5RRspjVJbfvUpW5pehUwlyEuhUGAWkjKg5S1XsUd4pTGO/q8TfkHFuUPIfO6/NH4Nm+wB3h+YsxMsAbFG+yflLzBEahRPk2smtWFNzA8rYvhJb5ZmXMx/Aa/ulB0BuvqcRuB5JF54Rl9igya7MNDdyfHTXfMHYMaZO9aK9TBMU4vZ9mLRdTWUWEXuaS0D+Bl3zXNeFNj0aWofsirHzh/0esPQL+AqhBDgshsYuEljq3VNNaE+3VHWV3g0KfBTF/KHW1c5UuzBzqJTV9XxvJt1CDJBaKHX1ycMFPGhPFGUXjP7oPSRrRXNUYooEakhLH8MOr83yG/w6xmRA3W41DNHIOGtjcyHwuerSmcxdqIu+41eFPRUHUmnQmJvS/18zQHgugEZ6srFYE63diO8gpei+gE5KeA32+F4WWTcv1Oy0q+TD/oSvIWVnh5HZUQkodonudlXiUzztj9be5fzcdvdVMFCMPltfgtm7Z33hMEVk02BYiby75DGH7YcOgug82tt1Re6HRKNjqVaXOWSl/qsx2Is6WMYez6a0vZ971y2r1UgyOERG7BEPl1lRhyyOhSr0tbV9/+IuU9MzoYkNGDRYze4HtU8GNWsFEYxzRLhfQYhbQEYilm5hsczQghpCGzpmiOi8YkXYvzLlyQSf1D2bK3zsYM7KTw6Z+sgBVqsqc8frIAwn/+8ePzSkWar3r+TVId+qjkTkuPe7eQeUAMBOUygjfWtw70Dj+wEy35n+PsaDWqeThgdo757uGA6xTWKB7+HETINyIb2E3fSvKBQlZkFBxW9K44yUUr+rlEqETNdrdDbZsPgNhHf+YgPyXNNN+SCkL8yF/J9IACVCAjz1+//gs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DAO = _t, Address = _t, Realms_ID = _t, Icon = _t, URL = _t]),#"Changed Type" = Table.TransformColumnTypes(Source,{{"DAO", type text}, {"Address", type text}, {"Realms_ID", type text}, {"Icon", type text}, {"URL", type text}}) in#"Changed Type"

    first votes:

    let Source = #"Fact - Votes",#"Removed Other Columns" = Table.SelectColumns(Source,{"PROPOSAL", "REALMS_ID", "BLOCK_TIMESTAMP"}),#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"REALMS_ID", "PROPOSAL"}, {{"First_Vote", each List.Min([BLOCK_TIMESTAMP]), type nullable datetime}}) in#"Grouped Rows"

    First and Last Vote - Each proposal:

    let Source = #"Fact - Votes",#"Removed Other Columns" = Table.SelectColumns(Source,{"PROPOSAL", "REALMS_ID", "BLOCK_TIMESTAMP"}),#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"REALMS_ID", "PROPOSAL"}, {{"First_Vote", each List.Max([BLOCK_TIMESTAMP]), type nullable datetime}}),#"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"First_Vote", "Last_vote"}}),#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"PROPOSAL"}, #"first votes", {"PROPOSAL"}, "first votes", JoinKind.LeftOuter),#"Expanded first votes" = Table.ExpandTableColumn(#"Merged Queries", "first votes", {"First_Vote"}, {"First_Vote"}) in#"Expanded first votes"

    Last proposal creation date:

    let Source = #"First and Last Vote - Each proposal",#"Grouped Rows" = Table.Group(Source, {"REALMS_ID"}, {{"Last Proposal creation date", each List.Max([First_Vote]), type nullable datetime}}) in#"Grouped Rows"

    AVG Time diff proposals:

    let Source = #"Fact - Votes",#"Removed Other Columns" = Table.SelectColumns(Source,{"PROPOSAL", "REALMS_ID"}),#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns", {"PROPOSAL"}),#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"REALMS_ID"}, {{"Proposals", each Table.RowCount(_), Int64.Type}}),#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"REALMS_ID"}, #"First and Last Vote - Each proposal", {"REALMS_ID"}, "First and Last Vote - Each proposal", JoinKind.LeftOuter),#"Expanded First and Last Vote - Each proposal" = Table.ExpandTableColumn(#"Merged Queries", "First and Last Vote - Each proposal", {"First_Vote"}, {"First_Vote"}),#"Grouped Rows1" = Table.Group(#"Expanded First and Last Vote - Each proposal", {"REALMS_ID", "Proposals"}, {{"First Vote", each List.Min([First_Vote]), type nullable datetime}}) in#"Grouped Rows1"

    Update time:

    let Source = Json.Document(Web.Contents("")),#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"MIN_TIME", "MIN_BLOCK", "MAX_TIME", "MAX_BLOCK"}, {"MIN_TIME", "MIN_BLOCK", "MAX_TIME", "MAX_BLOCK"}),#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"MIN_TIME", type datetime}, {"MIN_BLOCK", Int64.Type}, {"MAX_TIME", type datetime}, {"MAX_BLOCK", Int64.Type}}) in#"Changed Type"

    Methodology

    Two queries were used from Solana.core.fact_proposal_votes table:

    • The first query extracts all voting activity for selected DAOs. LINK
    • The second query extracts the first and last record of Realms data date. LINK

    These two queries are sent to Power BI for further analysis via Flipsidecrypto API.

    The time is hierarchical in the chart(6). (Yearly, Quarterly, Monthly, and Daily)

    Using the above buttons, we can change the date view as we want:

    1: Drill Up

    2: Turn on Drill down

    3: Expand all down one level in the hierarchy

    Data Model

    Measures:

    All Votes = COUNT('Fact - Votes'[VOTER])

    DAO Button = IF(HASONEVALUE('Dim DAOs'[DAO]),
"View " & SELECTEDVALUE('Dim DAOs'[DAO]) & " DAO on Realms","...")

    DAO Stats = 
IF(HASONEVALUE('Fact - Votes'[PROPOSAL]),

"Proposal ID: " & SELECTEDVALUE('Fact - Votes'[PROPOSAL]) & UNICHAR(10) &
"Number of Voters: " & [Voters] & UNICHAR(10) &
"Number of Votes: " & 'Measures-1'[All Votes] & UNICHAR(10) &
"First Vote Date: " & [First Vote] & UNICHAR(10) &
"Last Vote Date: " & [Last Vote] & UNICHAR(10) 
,
"DAO: " & SELECTEDVALUE('Dim DAOs'[DAO]) & UNICHAR(10) &
"Number of Proposals: " & [proposals] & UNICHAR(10) &
"Number of Voters: " & [Voters] & UNICHAR(10) &
"Number of Votes: " & 'Measures-1'[All Votes] & UNICHAR(10) &
"First Vote date: " & [First Vote] & UNICHAR(10) &
"Last Vote date: " & [Last Vote] & UNICHAR(10) &
"First vote date in the last proposal : " & [Last proposal] & UNICHAR(10) &
"* Submitted a new proposal every " & Round(SELECTEDVALUE('AVG Time diff proposals'[AVG Time Diff]),1) & " days (On Average)"
)

    DAO URL = IF(HASONEVALUE('Dim DAOs'[DAO]),
SELECTEDVALUE('Dim DAOs'[URL]) , "")

    First Vote = MIN('Fact - Votes'[BLOCK_TIMESTAMP])

    ICON = SELECTEDVALUE('Dim DAOs'[Icon])

    Last proposal = LOOKUPVALUE(
    'Last proposal creation date'[Last Proposal creation date],
    'Last proposal creation date'[REALMS_ID],
    SELECTEDVALUE('Dim DAOs'[Realms_ID]))

    Last Vote = Max('Fact - Votes'[BLOCK_TIMESTAMP])

    Proposal Button = IF(HASONEVALUE('Fact - Votes'[PROPOSAL]),
"View Proposal on Realms","...")

    Proposal URL = IF(HASONEVALUE('Fact - Votes'[PROPOSAL]),
SELECTEDVALUE('Dim DAOs'[URL]) & "/proposal/" & SELECTEDVALUE('Fact - Votes'[PROPOSAL]),"")

    proposals = DISTINCTCOUNT('Fact - Votes'[PROPOSAL])

    Record time = 
"Last Record: Block #" & MAX('Update time'[MAX_BLOCK]) & " - TimeStamp: " & MAX('Update time'[MAX_TIME]) & UNICHAR(10) &
"First Record: Block #" & MAX('Update time'[MIN_BLOCK]) & " - TimeStamp: " & MAX('Update time'[MIN_TIME])

    Voters = DISTINCTCOUNT('Fact - Votes'[VOTER])

    In this dashboard, all successful voting transactions are considered for analysis. If a voter votes multiple times (change or cancel a vote), all transactions are considered as a vote. but on the proposal page on Realms, we can see the final result. Also If a proposal had at least one vote, it is considered a proposal for that DAO even if that proposal has been deleted.

    This Dashboard is set to daily update.

    Loading...