Metadocumentation
open-collective
open-collective
  • Welcome
  • About
    • Introduction
    • Terminology
    • Company
    • Pricing
    • Community Guidelines
    • Values
    • Team
    • Investors
    • Contributing
  • Product
    • Features
    • Roadmap
    • Comparison
    • User Profile
    • Currencies
    • Log-in System
  • Collectives
    • FAQ
    • Creating a Collective
    • Quick Start Guide
    • Customize Collective
    • Change Core Contributors
    • Tiers & Goals
    • Add Fiscal Host
    • Change Fiscal Host
    • Transparent Budget
    • Expense Policy
    • Approving Expenses
    • Updates & Comms
    • Events
    • Funding Options
    • Data Export
    • Buttons & Banners
    • Integrations
    • Zero Collective Balance
    • Closing a Collective
  • Financial Contributors
    • FAQ
    • Payments
    • Website Badge
    • Organizations
      • FAQ
      • Bulk Transfers
      • Gift Cards
      • Sustainer Resources
    • Collectives
      • Collective to Collective donations
  • Expenses & Getting Paid
    • FAQ
    • Submitting Expenses
    • Expense Comments
    • Edit an Expense
    • Tax Information
  • Fiscal Hosts
    • FAQ
    • Becoming a Fiscal Host
    • Create a Fiscal Host
    • Fiscal Host Settings
    • Invoices
    • Payouts
    • Host Dashboard
    • Add Funds Manually
    • Refunds
    • Host Fees
    • Local Tax Support
    • Agreement Templates
    • Open Source Collective
  • Contributing
    • Design
      • Design Workflow
      • Design Contribution Guidelines
    • Development
      • Contribution Guide
      • Best Practice Guidelines
      • Bounties
      • API
        • Members
        • Users / Emails
        • Collectives
        • Events
      • README Integration
      • Architecture
      • Postgres Database
      • PayPal
      • Post-Donation Redirect
      • Custom Tweets
      • Manual Reporting
      • Github Permissions
      • Translations
      • Testing with Cypress
      • Collective's locations
    • Documentation
      • Style guide
      • Suggesting changes
    • Translation
  • Internal
    • Scope
    • Team Retreats
    • Brussels Summer Team Retreat
    • Host Admin Manual
    • Newsletter
    • Support
    • Issue Labels
    • DNS Troubleshooting
    • Developer Guidelines
    • Architecture
    • Testing
    • Queries
      • Gift Cards
      • Transactions
      • Ops
      • Collectives
      • Hosts
      • Analytics
      • Emails
    • Projects
      • Maintainerati Berlin 2019
      • Season of Docs 2019
    • The Open Collective Way
      • Values
      • Community Guidelines
      • Core Contributors Guidelines
      • Core Contributors: Communication
      • Core Contributors: Expenses
      • Core contributors: Leave
      • Core Contributors: Compensation
Powered by GitBook
On this page
  • Get annual budget and bunch of expenses stats per collective
  • WARNING: Don't run in production. Can take a while.
  • Get backercount and core contributors
  • Count of how many orders have updated paymentMethod after a failed charge
  1. Internal
  2. Queries

Analytics

Get annual budget and bunch of expenses stats per collective

WARNING: Don't run in production. Can take a while.

with "monthlySubscriptions" as
    (SELECT
      t."GroupId", COALESCE(SUM(t.amount * 12),0) as total
      FROM "Subscriptions" s
      LEFT JOIN "Donations" d ON s.id = d."SubscriptionId"
      LEFT JOIN "Transactions" t
      ON (s.id = d."SubscriptionId"
        AND t.id = (SELECT MAX(id) from "Transactions" t where t."DonationId" = d.id))
      WHERE t.amount > 0
        AND t."deletedAt" IS NULL
        AND s.interval = 'month'
        AND s."isActive" IS TRUE
        AND s."deletedAt" IS NULL
      GROUP BY t."GroupId"),

      "yearlyAndOneTimeSubscriptions" as
    (SELECT
      t."GroupId", COALESCE(SUM(t.amount),0) as total FROM "Transactions" t
      LEFT JOIN "Donations" d ON t."DonationId" = d.id
      LEFT JOIN "Subscriptions" s ON d."SubscriptionId" = s.id
      WHERE t.amount > 0
        AND t."deletedAt" IS NULL
        AND t."createdAt" > (current_date - INTERVAL '12 months') 
        AND ((s.interval = 'year' AND s."isActive" IS TRUE AND s."deletedAt" IS NULL) OR s.interval IS NULL)
      GROUP BY t."GroupId"),

    "inActiveSubscriptions" as
    (SELECT
      t."GroupId", COALESCE(SUM(t.amount),0) as total FROM "Transactions" t
      LEFT JOIN "Donations" d on t."DonationId" = d.id
      LEFT JOIN "Subscriptions" s ON d."SubscriptionId" = s.id
      WHERE t.amount > 0
        AND t."deletedAt" IS NULL
        AND t."createdAt" > (current_date - INTERVAL '12 months')
        AND s.interval = 'month' AND s."isActive" IS FALSE AND s."deletedAt" IS NULL
      GROUP BY t."GroupId"),

    "expensesData" as 
        (select e."GroupId", e.status, count(*) as "countExpenses", sum(amount) as "totalExpenses", max("createdAt") as "lastExpenseDate" from "Expenses" e
        WHERE "deletedAt" is null
        GROUP BY "GroupId", status
        ORDER BY "GroupId")

SELECT 
    id as "groupId", 
    slug, 
    COALESCE(ms.total,0)/100 + COALESCE(ys.total,0)/100 + COALESCE(ias.total,0)/100 as "annualBudget",
    currency, 
    ed.status as "expenseStatus",
    ed."lastExpenseDate",
    ed."countExpenses", 
    ed."totalExpenses"/100 as "sumOfExpenses"

     from "Groups" g
LEFT JOIN "monthlySubscriptions" ms on ms."GroupId" = g.id
LEFT JOIN "yearlyAndOneTimeSubscriptions" ys on ys."GroupId" = g.id
LEFT JOIN "inActiveSubscriptions" ias on ias."GroupId" = g.id
LEFT JOIN "expensesData" ed on ed."GroupId" = g.id
ORDER BY g.id

Get backercount and core contributors

with "backers" as 
(select "GroupId", count(*) as "backerCount" from "UserGroups"
    where role = 'BACKER'
    GROUP BY "GroupId")

select 
    g.id as "groupId", 
    g.name as "groupName",
    g.slug as "groupSlug",
    u.id as "userId",
    u."firstName" as "userFirstName", 
    u."lastName" as "userLastName", 
    u.email as "userEmail", 
    u.username as "username", 
    u.website as "userWebsite",
    u."twitterHandle" as "userTwitter",
    ug.role,
    b."backerCount"
from "UserGroups" ug
LEFT JOIN "Groups" g on ug."GroupId" = g.id
LEFT JOIN "Users" u on ug."UserId" = u.id
LEFT JOIN "backers" b on ug."GroupId" = b."GroupId"
WHERE ug.role = 'MEMBER' OR ug.role = 'HOST'
ORDER BY g.id

Count of how many orders have updated paymentMethod after a failed charge

with subs as 
(select distinct(id) as id, max("chargeRetryCount") as "chargeRetryCount" from "SubscriptionHistories" sh where "deletedAt" is null and "chargeRetryCount" > 0 group by id)

select oh.id, max("FromCollectiveId") as "FromCollectiveId", max("SubscriptionId"), max("chargeRetryCount") as "subId" 
from "OrderHistories" oh 
left join subs s on oh."SubscriptionId" = s.id
where "chargeRetryCount" > 0
group by oh.id having count("PaymentMethodId") > 1
PreviousHostsNextEmails

Last updated 5 years ago