Gitcoin Data Sources

tags: Info

Up to date by June 2021

Author: Danilo Lessa Bernardineli (BlockScience)

Required fields

• Contribution details: contributions graph
• IP addresses: retrievable from IP address vector thread
• Wallets: contributions graph

Metabase Queries

Contributions Graph

select 
    dashboard_profile.sybil_score,
    grants_contribution.*
from grants_contribution
INNER JOIN grants_subscription on grants_contribution.subscription_id = grants_subscription.id
INNER JOIN grants_grant on grants_grant.id = grants_subscription.grant_id
INNER JOIN dashboard_profile on dashboard_profile.id = grants_subscription.contributor_profile_id
where grants_contribution.created_on BETWEEN '2021-03-10 00:00:00' AND '2021-03-26 00:00:00'

IP Address Vector Threat List

Provides a IP -> user map of "duplicate" users

select
ip_address,
    CONCAT(
    location_data->'city',
    location_data->'country_name',
    location_data->'continent_name'
    ) as _where,
count(distinct profile_id) as num_user_visiting,
count(distinct grants_subscription.contributor_profile_id) as num_users_grants_contribution,
array_agg(distinct handle) as users,
array_agg(distinct email) as emails
from dashboard_useraction
inner join dashboard_profile on dashboard_profile.id = dashboard_useraction.profile_id
left join grants_subscription on grants_subscription.contributor_profile_id = dashboard_profile.id
where dashboard_useraction.created_on > '2021-04-01' and action = 'Visit'
group by ip_address,     CONCAT(
    location_data->'city',
    location_data->'country_name',
    location_data->'continent_name'
    )

order by count(distinct profile_id) desc
limit 10

Colluer Contribution IDs

Possible reference for getting verification status

select 
/* 
distinct grants_contribution.id
*/
count(distinct grants_contribution.id) as contributions,
count(distinct grants_subscription.contributor_profile_id) as profiles,
    avg(sybil_score) as avg_sybil_score,
    round(count(distinct CASE WHEN is_brightid_verified then grants_subscription.contributor_profile_id else null END)/ 1.0 / count(distinct grants_subscription.contributor_profile_id), 3) as brightid_verified_users_pct,
    round(count(distinct CASE WHEN is_idena_verified then grants_subscription.contributor_profile_id else null END)/  1.0 / count(distinct grants_subscription.contributor_profile_id), 3) as idena_verified_users_pct,
    round(count(distinct CASE WHEN is_twitter_verified then grants_subscription.contributor_profile_id else null END)/  1.0 / count(distinct grants_subscription.contributor_profile_id), 3) as twitter_verified_users_pct,
    round(count(distinct CASE WHEN is_google_verified then grants_subscription.contributor_profile_id else null END)/ 1.0 /  count(distinct grants_subscription.contributor_profile_id), 3) as google_verified_users_pct,
    round(count(distinct CASE WHEN is_poap_verified then grants_subscription.contributor_profile_id else null END)/  1.0 / count(distinct grants_subscription.contributor_profile_id), 3) as poap_verified_users_pct,
    round(count(distinct CASE WHEN sms_verification then grants_subscription.contributor_profile_id else null END)/  1.0 / count(distinct grants_subscription.contributor_profile_id), 3) as sms_verified_users_pct

from (
select
ip_address,
count(distinct grants_subscription.contributor_profile_id) as num_users_grants_contribution
from dashboard_useraction
inner join dashboard_profile on dashboard_profile.id = dashboard_useraction.profile_id
left join grants_subscription on grants_subscription.contributor_profile_id = dashboard_profile.id
where dashboard_useraction.created_on > '2021-03-10' and action = 'Visit'
group by ip_address
having count(distinct grants_subscription.contributor_profile_id) > 2
) t1 
INNER JOIN dashboard_useraction on t1.ip_address = dashboard_useraction.ip_address
inner join grants_subscription on grants_subscription.contributor_profile_id = dashboard_useraction.profile_id
inner join grants_contribution on grants_contribution.subscription_id = grants_subscription.id
inner join dashboard_profile on dashboard_profile.id = dashboard_useraction.profile_id
where grants_contribution.created_on > '2021-03-20' and action = 'Visit'
limit 10