We send a newsletter once a month to our wider community of users and supporters. This is done via Mailchimp.
Newsletter Process
Signups
People can subscribe to the newsletter using the form at the bottom of the homepage.
Import Recipients
Users who create new platform accounts and opt-in to the newsletter need to be added manually. A developer with database access needs to export the data. Then use Mailchimp's 'import contacts' function to add them to the main mailing list.
Content
Content can come from a range of sources.
Examples:
Announcing new features or improvements
Tweets about Open Collective or a specific Collective
Blog posts by Open Collective
Blog posts and articles by other people
Media like podcasts or videos of talks
Exciting partnerships with sponsors
Tips and ideas for Collective fundraising
New noteworthy Collectives that joined
The general style is brief and straightforward, with opportunities to click through for more info.
Leaderboard
The monthly leaderboard is a popular section of the newsletter, showing top backers & sponsors, top Collectives by new backers, and top new Collectives by donations. To get this data, use the following queries, and then put the data in the team Google Drive.
Top backers
with res as (SELECT CONCAT('https://opencollective.com/', max(backer.slug)) as backer, sum(amount) / 100 as "amount", max(t.currency) as currency, string_agg(DISTINCT c.slug, ', ') AS "collectives supported", max(backer."twitterHandle") as twitter, max(backer.description) as description, max(backer.website) as website
FROM "Transactions" t
LEFT JOIN "Collectives" backer ON backer.id = t."FromCollectiveId"
LEFT JOIN "Collectives" c ON c.id = t."CollectiveId"
WHERE t."createdAt" > '2019-01-01'
AND t."createdAt" < '2019-02-01'
AND t.type='CREDIT'
AND t."platformFeeInHostCurrency" < 0
AND t."deletedAt" IS NULL
GROUP BY t."FromCollectiveId"
ORDER BY "amount" DESC)
SELECT row_number() over(order by "amount" DESC) as "#", * from res
Top collectives by number of new backers
SELECT max(c.slug) as collective, max(c."createdAt") as "createdAt", count(*) as "totalNewBackers", max(c.website) as website, max(c."twitterHandle") as twitter, max(c.description) as description
FROM "Members" m
LEFT JOIN "Collectives" c ON m."CollectiveId" = c.id
WHERE m."createdAt" > '2019-01-01'
AND m."createdAt" < '2019-02-01'
AND m.role='BACKER'
GROUP BY "CollectiveId"
ORDER BY "totalNewBackers" DESC
Top new collectives by amount received
SELECT sum(amount) / 100 as "totalAmount", max(t.currency) as currency, max(c.slug) as collective, max(c.website) as website, max(c."twitterHandle") as twitter, max(c.description) as description
FROM "Transactions" t
LEFT JOIN "Collectives" c ON c.id = t."CollectiveId"
WHERE t."createdAt" > '2019-01-01'
AND c."createdAt" > '2019-01-01'
AND t."createdAt" < '2019-02-01'
AND c."createdAt" < '2019-02-01'
AND t.type='CREDIT'
AND t."platformFeeInHostCurrency" < 0
GROUP BY t."CollectiveId"
ORDER BY "totalAmount" DESC
List of transactions
SELECT
t."createdAt", c.slug as "collective slug", t.type as "transaction type", t.amount, t.currency,
fc.slug as "from slug", fc.type as "from type", t.description, e.category as "expense category", h.slug as "host slug",
t."hostCurrency", t."hostCurrencyFxRate",
pm.service as "payment processor", pm.type as "payment method type",
t."paymentProcessorFeeInHostCurrency", t."hostFeeInHostCurrency", t."platformFeeInHostCurrency"
FROM "Transactions" t
LEFT JOIN "Collectives" fc ON fc.id=t."FromCollectiveId"
LEFT JOIN "Collectives" c ON c.id=t."CollectiveId"
LEFT JOIN "Collectives" h ON h.id=t."HostCollectiveId"
LEFT JOIN "PaymentMethods" pm ON pm.id=t."PaymentMethodId"
LEFT JOIN "Expenses" e ON e.id=t."ExpenseId"
WHERE t."createdAt" >= '2019-01-01' AND t."createdAt" < '2019-02-01'
AND t."deletedAt" IS NULL
ORDER BY t.id ASC