Open Collective Queries


#1

Activity

Active collectives per month

SELECT date_trunc('month', "createdAt") as "month", COUNT(DISTINCT("GroupId")) from "Transactions"
GROUP BY "month" ORDER BY "month"

Collectives active after 3 months, per monthly cohort

For all the collectives created in a given month, how many of them are still active after 3 months?

with "groupsCreated" as (
SELECT g."createdAt" as "createdAt", g.slug, min(t."createdAt") as "firstTransaction", max(t."createdAt") as "latestTransaction", EXTRACT(day from (min(t."createdAt") - g."createdAt")) as "daysToFirstTransaction",

EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) as "activeDays",

CASE 
    WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 90 THEN 1
    WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 90 THEN 0
    WHEN min(t."createdAt") IS NULL THEN 0
END as "active"

FROM "Groups" g LEFT JOIN "Transactions" t ON g.id=t."GroupId"
GROUP BY g.id
)
SELECT DATE_TRUNC('month', "createdAt") as month, count(*) as "totalCollectivesCreated", SUM(active) as "totalActiveAfter90days",  (SUM(active)*100/count(*)) AS "percentage"
FROM "groupsCreated" GROUP BY month ORDER BY month DESC

Monthly subscriptions active after 3 months, per monthly cohort

For all the monthly subscriptions that started in a given month, how many are still active after 3 months?

WITH "activeSubscriptions" as (
SELECT s."createdAt" as "createdAt", min(t."createdAt") as "firstTransaction", max(t."createdAt") as "latestTransaction", 

EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) as "activeDays",

CASE 
    WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) >= 90 THEN 1
    WHEN EXTRACT(day from (max(t."createdAt") - min(t."createdAt"))) < 90 THEN 0
END as "active"

FROM "Transactions" t LEFT JOIN "Donations" d ON d.id=t."DonationId" LEFT JOIN "Subscriptions" s ON s.id=d."SubscriptionId" 
WHERE s.interval = 'month'
GROUP BY s.id
)

SELECT DATE_TRUNC('month', "createdAt") as month, count(*) as "totalSubscriptionsCreated", SUM(active) as "totalActiveAfter90days",  (SUM(active)*100/count(*)) AS "percentage" FROM "activeSubscriptions" GROUP BY month ORDER BY month DESC

Groups sorted most amount raised in a particular month

select 
	max(g.currency) as currency,
	sum(t.amount)/100 as "amount", 
	g.slug

from "Transactions" t
inner join "Groups" g on g.id = t."GroupId"
where 
	t.type='DONATION' and 
	t."createdAt" between '2017/04/01' and '2017/04/30' and /* change to set correct month */
	t."PaymentMethodId" is not null 
group by g.slug
order by amount DESC
limit 20

Groups sorted with highest amount of expenses filed in a particular month

select 
	max(g.currency) as currency,
	sum(t.amount)/100 as "amount", 
	g.slug

from "Transactions" t
inner join "Groups" g on g.id = t."GroupId"
where 
	t.type='EXPENSE' and 
	t."createdAt" between '2017/04/1' and '2017/04/30' /* change to set month */
group by g.slug
order by amount
limit 20

Groups with total amounts raised per month

select 
	to_char(t."createdAt", 'YYYY-mm') as "month",
	sum(t.amount)/100 as amount,
	max(t.currency) as currency,
	g.slug as "group"

from "Transactions" t
inner join "Groups" g on g.id = t."GroupId"

where 
	amount > 0 and 
	"GroupId" != 1 and
	t."platformFeeInTxnCurrency" > 0

group by g.slug, "month"
order by g.slug

Revenue calcs

Revenue and transaction splits by month with all currencies converted to USD

Note: the exchange rates are from March 11, 2016

with conversions as (select
    date_trunc('month', t."createdAt") as "givenMonth",

    /* deal with currency */
    CASE 
        WHEN (t.currency = 'USD') THEN t.amount / 1
        WHEN (t.currency = 'EUR') THEN t.amount / 0.9
        WHEN (t.currency = 'MXN') THEN t.amount / 17.7
        WHEN (t.currency = 'AUD') THEN t.amount / 13.2
        WHEN (t.currency = 'CAD') THEN t.amount / 1.3
        WHEN (t.currency = 'INR') THEN t.amount / 66.97 
        WHEN (t.currency = 'SEK') THEN t.amount / 8.34
        WHEN (t.currency = 'GBP') THEN t.amount / 0.71 
        ELSE 0 
    END AS "amountInUSD",
    CASE 
        WHEN (t.currency = 'USD') THEN t."platformFeeInTxnCurrency" / 1
        WHEN (t.currency = 'EUR') THEN t."platformFeeInTxnCurrency" / 0.9
        WHEN (t.currency = 'MXN') THEN t."platformFeeInTxnCurrency" / 17.7
        WHEN (t.currency = 'AUD') THEN t."platformFeeInTxnCurrency" / 1.32
        WHEN (t.currency = 'CAD') THEN t."platformFeeInTxnCurrency" / 1.30
        WHEN (t.currency = 'INR') THEN t."platformFeeInTxnCurrency" / 66.97 
        WHEN (t.currency = 'SEK') THEN t."platformFeeInTxnCurrency" / 8.34
        WHEN (t.currency = 'GBP') THEN t."platformFeeInTxnCurrency" / 0.71 
        ELSE 0 
    END AS "platformFeeInUSD",

    /*
    Generate donations categories
    - added-funds (manually added funds - we didn't get a platform fee)
    // for rest of these we charge a fee
    - recurringMonthlyNew (new monthly subscription in this month)
    - recurringMonthlyOld (carryover monthly subscription in this month)
    - recurringAnnualNew (new annual subscription this month)
    - recurringAnnualOld (carryover annual subscription renewed this month)
    - one-time (one-time donations)

    */

    CASE
        WHEN 
        	t.amount > 0 AND 
        	(t."platformFeeInTxnCurrency" = 0 OR t."platformFeeInTxnCurrency" IS NULL) 
        THEN 1
        ELSE 0
    END AS addedFunds,


    CASE 
        WHEN t.amount > 0 AND 
        d."SubscriptionId" is NULL AND 
        (t."platformFeeInTxnCurrency" is not null AND t."platformFeeInTxnCurrency" != 0)
        THEN 1
        ELSE 0
    END AS oneTimeDonations,

    CASE 
        WHEN 
        	t.amount > 0 AND 
        	(t."platformFeeInTxnCurrency" IS NOT NULL AND t."platformFeeInTxnCurrency" != 0) AND 
        	d."SubscriptionId" is NOT NULL AND s."interval" like 'month%'
        THEN 1
        ELSE 0
    END AS recurringMonthlyTotal,

    CASE 
        WHEN 
        	t.amount > 0 AND 
        	(t."platformFeeInTxnCurrency" IS NOT NULL AND t."platformFeeInTxnCurrency" != 0) AND 
        	d."SubscriptionId" is NOT NULL AND s."interval" like 'month%' AND 
        	date_trunc('month', t."createdAt") = date_trunc('month', s."activatedAt")
        THEN 1
        ELSE 0
    END AS recurringMonthlyNew,

    CASE 
        WHEN 
        	t.amount > 0 AND 
        	(t."platformFeeInTxnCurrency" IS NOT NULL AND t."platformFeeInTxnCurrency" != 0) AND 
        	d."SubscriptionId" is NOT NULL AND s."interval" like 'month%' AND 
        	date_trunc('month', t."createdAt") > date_trunc('month', s."activatedAt")
        THEN 1
        ELSE 0
    END AS recurringMonthlyOld,

    CASE 
        WHEN 
        	t.amount > 0 AND 
        	(t."platformFeeInTxnCurrency" IS NOT NULL AND t."platformFeeInTxnCurrency" != 0) AND 
        	d."SubscriptionId" is NOT NULL AND s."interval" like 'year%'
        THEN 1
        ELSE 0
    END AS recurringAnnuallyTotal,

    CASE 
        WHEN 
        	t.amount > 0 AND 
        	(t."platformFeeInTxnCurrency" IS NOT NULL AND t."platformFeeInTxnCurrency" != 0) AND 
        	d."SubscriptionId" is NOT NULL AND s."interval" like 'year%' AND 
        	date_trunc('month', t."createdAt") = date_trunc('month', s."activatedAt")
        THEN 1
        ELSE 0
    END AS recurringAnnuallyNew,

    CASE 
        WHEN 
        	t.amount > 0 AND 
        	(t."platformFeeInTxnCurrency" IS NOT NULL AND t."platformFeeInTxnCurrency" != 0) AND 
        	d."SubscriptionId" is NOT NULL AND s."interval" like 'year%' AND 
        	date_trunc('month', t."createdAt") > date_trunc('month', s."activatedAt")
        THEN 1
        ELSE 0
    END AS recurringAnnuallyOld,

    /*
    Generate expenses categories
    - total (all expenses recorded)
    - manual (submitted but no money exchanged from us)
    - paypal (paid through paypal)
    */

    CASE
        WHEN 
        	t.amount < 0 
        THEN 1
        ELSE 0
    END AS totalExpensesRecorded,

    CASE
        WHEN 
        	t.amount < 0 AND 
        	t."PaymentMethodId" IS NULL 
        THEN 1
        ELSE 0
    END AS manualExpenses,

    CASE
        WHEN 
        	t.amount < 0 AND 
        	t."PaymentMethodId" IS NOT NULL 
        THEN 1
        ELSE 0
    END AS paypalExpenses,

    CASE 
        WHEN t.type = 'DONATION' THEN 'donation'
        WHEN t.type IS NULL THEN 'addfund'
        WHEN t.type = 'EXPENSE' THEN 'expense'
        WHEN t.type = 'payment' THEN 'donation'
        else 'unknown'
    END AS type
    FROM "Transactions" t
    LEFT JOIN "Donations" d on t."DonationId" = d.id
    LEFT JOIN "Subscriptions" s on d."SubscriptionId" = s.id
    WHERE 
        t."deletedAt" IS NULL AND 
        t."createdAt" BETWEEN '2016/01/01' AND '2019/01/01' AND 
        d."deletedAt" IS NULL AND
        s."deletedAt" IS NULL)

/* End temporary table */

SELECT 
    to_char("givenMonth", 'YYYY-mm') as "month", 

    /* donations */
    (SUM("amountInUSD" * recurringMonthlyTotal + 
    	"amountInUSD" * recurringAnnuallyTotal + 
    	"amountInUSD" * oneTimeDonations + 
    	"amountInUSD" * addedFunds)/100)::DECIMAL(10,0)::money 
    	AS "totalMoneyBroughtIntoPlatformInUSD", 
    
    (SUM("amountInUSD" * recurringMonthlyTotal + 
    	"amountInUSD" * recurringAnnuallyTotal + 
    	"amountInUSD" * oneTimeDonations)/100)::DECIMAL(10,0)::money 
    	AS "totalDonationsMadeOnPlatformInUSD",
    	
    (SUM("platformFeeInUSD")/100)::DECIMAL(10,0)::money AS "OCFeeInUSD",
    
    (SUM("amountInUSD" * recurringMonthlyTotal)/100)::DECIMAL(10,0)::money AS "recurringMonthlyTotalDonationsInUSD",
    (SUM("amountInUSD" * recurringMonthlyOld)/100)::DECIMAL(10,0)::money AS "recurringMonthlyOldDonationsInUSD",
    (SUM("amountInUSD" * recurringMonthlyNew)/100)::DECIMAL(10,0)::money AS "recurringMonthlyNewDonationsInUSD",
    (SUM("amountInUSD" * recurringAnnuallyTotal)/100)::DECIMAL(10,0)::money AS "recurringAnnualDonationsInUSD",
    (SUM("amountInUSD" * recurringAnnuallyOld)/100)::DECIMAL(10,0)::money AS "recurringAnnuallyOldDonationsInUSD",
    (SUM("amountInUSD" * recurringAnnuallyNew)/100)::DECIMAL(10,0)::money AS "recurringAnnuallyNewDonationsInUSD",
    (SUM("amountInUSD" * oneTimeDonations)/100)::DECIMAL(10,0)::money AS "oneTimeDonationsInUSD",
    (SUM("amountInUSD" * addedFunds)/100):: DECIMAL(10,0)::money AS "addedFundsInUSD",

    /* expenses */
    (SUM("amountInUSD" * totalExpensesRecorded)/100)::DECIMAL(10,0)::money AS "expensesPaidInUSD",
    (SUM("amountInUSD" * manualExpenses)/100)::DECIMAL(10,0)::money AS "manualExpensesInUSD",
    (SUM("amountInUSD" * paypalExpenses)/100)::DECIMAL(10,0)::money AS "paypalExpensesInUSD",

    /* counts of transactions */
    COUNT(*) AS "numTransactions",
    SUM(recurringMonthlyTotal + recurringAnnuallyTotal + oneTimeDonations + addedFunds) AS "numMoneyBroughtInEntries",
    SUM(recurringMonthlyTotal + recurringAnnuallyTotal + oneTimeDonations) AS "numDonationMadeOnPlatformEntries",
    
    SUM(recurringMonthlyTotal) as "numRecurringMonthlyTotalDonations",
    SUM(recurringMonthlyOld) as "numRecurringMonthlyOldDonations",
    SUM(recurringMonthlyNew) as "numRecurringMonthlyNewDonations",
    SUM(recurringAnnuallyTotal) as "numRecurringAnnualDonations",
    SUM(addedFunds) as "numAddedFunds",
    SUM(totalExpensesRecorded) as "numExpensesPaid"

FROM conversions 
GROUP BY "givenMonth"
ORDER BY "givenMonth"

Monthly gross revenue, split by currency

select 
	"currency",
	sum("amount") as "origAmount",
	date_trunc('month', "createdAt") as "givenMonth"
 from "Transactions"
 where "type" = 'DONATION' and "PaymentMethodId" is NOT NULL
 GROUP BY "currency", "givenMonth"

Monthly gross revenue, split by recurring vs one-time

select 
    "currency",
    sum("amount") as "origAmount",
    date_trunc('month', "createdAt") as "givenMonth",
    case 
    	when "SubscriptionId" is Null THEN false
    	else true
    end as "recurring"
 from "Transactions"
 where "type" = 'DONATION' and "PaymentMethodId" is NOT NULL
 GROUP BY "currency", "givenMonth", "recurring"

Fee-related calcs

Fees by group by month for all donations.

Note: it doesn’t include expenses (yet).

select 
	t."GroupId",
	g."name",
	t."txnCurrency",
	sum(t."amount") as "origAmount",
	date_trunc('month', t."createdAt") as "givenMonth",
	cast(sum(t."platformFeeInTxnCurrency") as FLOAT)/100 as "platformFee",
	cast(sum(t."hostFeeInTxnCurrency") AS FLOAT)/100 as "hostFee",
	cast(sum(t."paymentProcessorFeeInTxnCurrency") AS FLOAT)/100 as "stripeFee",
	cast(sum(t."amountInTxnCurrency") as FLOAT)/100 as "totalDonationsInTxnCurrency",
	count(t."id") as "numDonations"
from "Transactions" t
LEFT JOIN "Groups" g on t."GroupId" = g."id"
where "type" = 'DONATION' and "txnCurrency" IS NOT NULL and "PaymentMethodId" is NOT NULL
GROUP By t."GroupId", g."name", t."txnCurrency", "givenMonth"
ORDER BY t."GroupId"

Active collectives in a month and how much they paid in fees.

You’ll need to update the month start and end dates to run it for a particular month and update %name for different organizatons.

select 
    t."GroupId",
    g."name",
    sum(t."amount") as "origAmount",
    cast(sum(t."platformFeeInTxnCurrency") as FLOAT)/100 as "platformFee",
    cast(sum(t."hostFeeInTxnCurrency") AS FLOAT)/100 as "hostFee",
    cast(sum(t."paymentProcessorFeeInTxnCurrency") AS FLOAT)/100 as "stripeFee",
    cast(sum(t."amountInTxnCurrency") as FLOAT)/100 as "totalDonationsInTxnCurrency",
    count(t."id") as "numDonations"
from "Transactions" t
LEFT JOIN "Groups" g on t."GroupId" = g."id"
where 
g."slug" like 'wwcode%' and 
t."createdAt" between '2016/05/01' and '2016/05/31'
GROUP By t."GroupId", g."name"
ORDER BY t."GroupId"

Other

Get members and hosts of a group

This is the query to get members and hosts per group

SELECT 
        ug."UserId" as id,
        u."firstName" as "firstName",
        u."lastName" as "lastName",
        u.email as email,
        ug.role as role,
        g.slug as groupSlug,
        g.name as groupName,
        g.id as groupId
      FROM "UserGroups" ug
      LEFT JOIN "Users" u ON u.id = ug."UserId"
      LEFT JOIN "Groups" g on g.id = ug."GroupId"
      where ug."role" != 'BACKER';

Get Backers (name, email, totalDonations, lastDonation) of a collective

    WITH constants AS (
      SELECT id FROM "Groups" WHERE slug='chsf'
    ), total_donations AS (
      SELECT
        max("UserId") as "UserId",
        SUM(amount) as amount,
        max("createdAt") as "lastDonationAt"
      FROM "Transactions" t
      WHERE t."GroupId" IN (SELECT id FROM constants) AND t.amount >= 0
      GROUP BY "UserId"
    )

    SELECT
      ug."UserId" as id,
      ug."createdAt" as "createdAt",
      td."lastDonationAt" as "lastDonation",
      u."firstName" as "firstName",
      u."lastName" as "lastName",
      u.email as email,
      ug.role as role,
      u.avatar as avatar,
      u.website as website,
      u."twitterHandle" as "twitterHandle",
      td.amount as "totalDonations"
    FROM "UserGroups" ug
    LEFT JOIN "Users" u ON u.id = ug."UserId"
    LEFT JOIN total_donations td ON td."UserId" = ug."UserId"
    WHERE ug."GroupId" IN (SELECT id FROM constants)
    AND ug."deletedAt" IS NULL
    ORDER BY "totalDonations" DESC, ug."createdAt" ASC

Amount we are holding for collectives at any given moment

Change UserId to other hosts to find out for anyone else.

select  
	cast(COALESCE(sum(t."netAmountInGroupCurrency"), 0) as float)/100 as "hostBalance"
from "UserGroups" ug
left join "Transactions" t on t."GroupId" = ug."GroupId"

where (ug."UserId" = 40 or ug."UserId" = 772)
	and ug.role like 'HOST' 
	and ug."GroupId" not in (1, 7, 34) /* remove opencollective, tipbox and ispcwa (because it's negative) */
	and t."deletedAt" is null

Estimate how many subscriptions are marked active but haven’t had a transaction in last 30 days

with recentdonations as (
    SELECT distinct("DonationId")

    FROM "Transactions" 

    WHERE "createdAt" between '2016/10/29' and '2016/11/30'
        and "DonationId" is not null and "deletedAt" is null
)

select 
    d.id as "DonationId",
    d."UserId" as "UserId",
    d."GroupId" as "GroupId",
    d.amount as "Amount",
    d."createdAt" as "DonationCreatedAt",
    d.currency as currency
from "Donations" d

left join "Subscriptions" s on d."SubscriptionId" = s.id

where 
    d.id not in (select "DonationId" from recentdonations)
and d."deletedAt" is null
and s."deletedAt" is null
and s."isActive" = true 
and d."isProcessed" = true

order by currency;

Manually paying an expense

WARNING: Don’t run in production, unless you know what you are doing

This query takes an expense that’s approved with paymentMethod = ‘manual’ and approves it by adding a row in Transactions table. You’ll still need to mark the status of expense as PAID

INSERT INTO "Transactions" (type, description, amount, currency, status, "createdAt", "updatedAt", "GroupId", "UserId", "netAmountInGroupCurrency", "ExpenseId")
Select 'EXPENSE', e.title, e.amount/-100, e.currency, 'REIMBURSED', '2016-12-07 00:00:00.86-05', '2016-12-07 00:00:00.86-05', e."GroupId", e."UserId", e.amount*-1, e.id from "Expenses" e
where e.id = [expense_ID]
and e.status = 'APPROVED'
and e."payoutMethod" = 'manual'