About MarketingDAO → Source

The MarketingDAO allocates funding to NEAR ecosystem marketing initiatives seeking grants under $10K USD. The Marketing DAO is made up of globally distributed marketing professionals, creatives and NEAR enthusiasts. The Marketing DAO began during the summer of 2021 to empower active members of the NEAR Community to use marketing activities to further the overall growth and expansion of the NEAR ecosystem. The origins of the Marketing DAO date back to the original Sputnik DAO. We were among the first to migrate to AstroDAO when it launched and to this day are amongst the most active DAOs. The MarketingDAO has, over the course of its time in operation, incrementally assumed responsibility for:

• Monitoring the Forum for proposals and Community comments related to the Marketing DAO

• Reviewing proposals from Community members (guilds, DAOs, entrepreneurs, creatives and service providers) in depth to understand and evaluate their strength and value to the NEAR Community and its overall growth

• Asking questions and giving feedback with the goal and intention of allocating funds to active members of the Community aiming to deploy high-quality marketing activities to grow and expand the NEAR ecosystem

• Providing clear explanations for decisions

• Offering additional advice, support and strategic feedback via the Forum, individual meetings and other direct communications as needed to support Community members in the success of their proposals

• Tracking the review process and council decisions regarding proposals with transparency

  \

In addition to the above activities, we regularly request and review reports detailing metrics and analytics, as well as other supporting documentation (receipts, cost estimates, etc.) to ensure responsible allocation of Community funds.

MarketingDAO Twitter

✍️ Description of Work

---

The NEAR MarketingDAO is in need of a grants tracking dashboard to better monitor the outcome of grant requests from their treasury. This dashboard analyzes proposals and funding for MarketingDAO grant program. This dashboard tracks the funds sent on the blockchain and examines the following criteria:

• Number of Proposals
  • Total Number of Proposals Added
  • Daily Number of Proposals Added
  • Grant Proposal Result
  • Grant Proposal Votes
  • Number of Grants Proposals Approved
• Council Member
  • Most Active Council Member
  • Council Member and Number of Vote
  • Council Member Join Date
• Funds

  • Total Value Locked

  • Amount of Funds Disbursed Over Time

  • Amount of funds disbursed by Receiver (Target Wallets)

  • Top Receiver (Target Wallets) by Amount of Funds Disbursed

• Project Name(s)

🧠 Methodology

---

To deal with this bounty, we use the schema near.core and the tables fact_receipts and fact_actions_events_function_call.

To obtain each of the different parts, we explain its queries separately.

Number of Proposals:

To obtain the transactions related to adding a proposal, we obtain the list of transactions related to add_proposal by using the fact_receipts table:

select * from near.core.fact_receipts where receiver_id = 'marketing.sputnik-dao.near'
      	and actions:receipt:Action:actions[0]:FunctionCall:method_name = 'add_proposal'

Then we count the total number of transactions related to add_proposal → count(distinct tx_hash) as "Number of Proposal"

Grant Proposal Vote and their Results

To determine the result of a proposal based on the votes given, using the fact_receipts table, according to the following query, we get the list of transactions related to the vote:

select tx_hash, actions:predecessor_id as voter from near.core.fact_receipts where receiver_id = 'marketing.sputnik-dao.near'
      	and actions:receipt:Action:actions[0]:FunctionCall:method_name = 'act_proposal' and status_value:Failure is null 

Then, by using the fact_actions_events_function_call table and the args:action command, we get the type of each vote (Approve or Reject) and by using the args:id command, we get the proposal id, then we count the number of Approve and Reject votes for each proposal, and then the result of each We determine the proposal based on these votes.

select tx_hash, block_timestamp, args:id as proposal_id, args:action as vote_action, voter
  		from near.core.fact_actions_events_function_call join marketing_dao_proposals_votes using(tx_hash) where args:action is not null

Using the following two queries, we determine whether a proposal is approved or rejected:

winning_proposals as (
	select concat('Proposal #', proposal_id) as "Proposal", 'Approved ✅' as "Proposal Result","Number of Approve Vote", "Number of Reject Vote"
  		from governance_votes_counter
  		where "Number of Approve Vote" >= "Number of Reject Vote"
  		order by "Number of Approve Vote" desc 
)

losser_proposals as (
	select concat('Proposal #', proposal_id) as "Proposal", 'Rejected ❌' as "Proposal Result", "Number of Approve Vote", "Number of Reject Vote"
  		from governance_votes_counter
  		where "Number of Approve Vote" < "Number of Reject Vote"
  		order by "Number of Approve Vote" desc 
)

Council Member

To obtain the list of currently active Council Members, using the fact_receipts table and the following query, we obtain the list of transactions related to the request to be added to the Council:

select tx_hash from near.core.fact_receipts where receiver_id = 'marketing.sputnik-dao.near' and actions:receipt:Action:actions[0]:FunctionCall:method_name = 'add_proposal'

Then, using the fact_actions_events_function_call table and the command args:proposal:kind:AddMemberToRole:member_id, we obtain the list of transactions related to AddMember, and using the command args:proposal:kind:RemoveMemberFromRole:member_id, we obtain the list of transactions related to RemoveMember then we get the list of members who have not been removed from the council or their last add request was after their removal.

Total Value Locked

To calculate TVL, we obtain the values of Deposit Fund and Withdraw Fund by using the fact_receipts table and the two queries below, then we subtract the total amount of Deposit from the total amount of Withdrawal to obtain TVL.

Deposit

with deposit_found as (
	select block_timestamp,tx_hash,actions:predecessor_id as predecessor_id,
  		case 
  			when actions:receipt:Action:actions[0]:FunctionCall:deposit is not null then actions:receipt:Action:actions[0]:FunctionCall:deposit
  			when actions:receipt:Action:actions[2]:Transfer:deposit is not null then actions:receipt:Action:actions[2]:Transfer:deposit
  			else actions:receipt:Action:actions[0]:Transfer:deposit
  		end as deposit 
    from near.core.fact_receipts where receiver_id = 'marketing.sputnik-dao.near'
),

Withdraw

withdraw_fund as (
	select block_timestamp,tx_hash,receiver_id,
  		actions:receipt:Action:actions[0]:Transfer:deposit as withdraw from near.core.fact_receipts where actions:predecessor_id = 'marketing.sputnik-dao.near'
),

Amount of Funds Disbursed

To obtain the Amount of Funds Disbursed, we first use the fact_receipts table and the following query to obtain the list of transactions related to add_proposal:

select tx_hash from	near.core.fact_receipts where receiver_id = 'marketing.sputnik-dao.near'
      	and actions:receipt:Action:actions[0]:FunctionCall:method_name = 'add_proposal'

Then, using the fact_actions_events_function_call table and the command args:proposal:kind:Transfer:receiver_id, the address of the recipient of the Fund, and using the command args:proposal:kind:Transfer:amount, we get the amount of Fund received:

select block_timestamp, args:proposal:kind:Transfer:receiver_id AS address, args:proposal:kind:Transfer:amount AS amount 
  		from near.core.fact_actions_events_function_call where tx_hash in (select tx_hash from marketing_dao_add_proposals) and args:proposal:kind:Transfer:receiver_id is not null 
  		and args:proposal:kind:Transfer:amount is not null 

1️⃣ → Review and analysis of Proposals, Grant Proposals, Proposals Vote and Proposals Result

---

✅ Observations

---

In the charts above, you can see the Proposals and their votes and the result of each Proposal, as is clear:

• The total number of Proposals is 614, of which 606 have been voted and the rest have expired.
• For this 606 Proposal, 1864 votes have been registered, which were voted by 9 unique voters.
• The average vote for each proposal is 3 votes
• The highest number of votes was related to Feb 21, 2020 and Sep 26, 2022.
• Out of 606 proposals, 492 have been approved and 114 have been rejected.
• 81% of the votes were Approve and 19% were Reject.

2️⃣ → Review and analysis of Council Member

---

✅ Observations

---

In the charts above, you can see the active members of the council along with their votes and the date of joining the council. It is clear that:

• The total number of active members of the Council are 5, which include:
  • whendacha.near with 533 votes
  • cryptocredit.near with 406 votes
  • alejandro.near with 376 votes
  • so608.near with 197 votes
  • klint.near with 64 votes

3️⃣ → Review and analysis of Total Value Locked and Amount of Funds Disbursed

---

✅ Observations

---

In the charts above, you can see Amount of Funds Disbursed and Total Value Locked. It is clear that:

• The total amount of TVL is 2059 NEAR which is equal to 5060 USD.
• The total Amount of Funds Disbursed is 75.7K NEAR received by 83 unique addresses, the average amount of funds received per wallet is 913 NEAR.
• The highest amount of funds received is related to Dec 6, 2021 and Aug 22, 2022.

✔️ Final Conclusion

---

In this dashboard we analyzes and review proposals and funding for MarketingDAO grant program. We obtained the following results:

• The total number of Proposals is 614, of which 606 have been voted and the rest have expired.

• Out of 606 proposals, 492 have been approved and 114 have been rejected.

• 81% of the votes were Approve and 19% were Reject.

• The total number of active members of the Council are 5, which include:

  • whendacha.near with 533 votes
  • cryptocredit.near with 406 votes
  • alejandro.near with 376 votes
  • so608.near with 197 votes
  • klint.near with 64 votes

• The total amount of TVL is 2059 NEAR which is equal to 5060 USD.

• The total Amount of Funds Disbursed is 75.7K NEAR received by 83 unique addresses, the average amount of funds received per wallet is 913 NEAR.