[STORY]: Create new email notifications for 'Follow + Note' #2962
Comments
|
|
|
Two leading designs thus far, one exploring pulling in the user avatar + comment visual component into the email |
|
Additional comment: View My Grants button links to interested tab |
ekhoffman
changed the title
|
Drafting some scratch for implementing these notes as a digest... Data RetrievalQuerying for grant activityThe following query returns both new follows and note revision content for grants since a given SELECT
g.grant_id AS grant_id,
g.title AS grant_title,
u.id AS user_id,
u.name AS user_name,
u.email AS user_email,
a.name AS agency_name,
activity.activity_at,
activity.activity_type,
activity.text_content AS note_text
FROM (
SELECT
gf.id,
gf.grant_id,
gf.user_id,
gf.created_at AS activity_at,
'follow' AS activity_type,
null AS text_content
FROM grant_followers gf
UNION ALL
SELECT
rev.id,
gn.grant_id,
gn.user_id,
rev.created_at AS activity_at,
'note' AS activity_type,
rev.text AS text_content
FROM grant_notes gn
LEFT JOIN LATERAL (
SELECT
r.id,
r.grant_note_id,
r.created_at,
r.text
FROM grant_notes_revisions r
WHERE r.grant_note_id = gn.id
ORDER BY r.created_at DESC
LIMIT 1
) AS rev ON rev.grant_note_id = gn.id
) activity
JOIN users u ON u.id = activity.user_id
JOIN agencies a ON a.id = u.agency_id
JOIN grants g on g.grant_id = activity.grant_id
WHERE
activity.activity_at > :digestPeriodStart
AND activity.activity_at <= :digestPeriodEnd
ORDER BY
g.grant_id DESC,
-- Followers first, then notes (remove for grant activity in chronological order, regardless of type):
array_position(array['follow', 'note'], activity.activity_type) ASC,
activity.activity_at ASC;Example result set:
This query retrieves all new grant collaboration activity (new follows and new note revisions), across all organizations (tenants). However, it only represents user data about users who performed some activity (i.e. became a new follower and/or created or revised a note on a grant); it does not provide all users who are following a grant. In other words, the query in the previous section provides results that can be used to build the content of a digest email, but not to determine all users who should receive that digest. Although this query may be useful for analytical purposes, it must be adjusted in order to be made suitable for our email digest use-case. Querying for digest recipientsIn order to determine every recipient for a 24-hour digest, we need to locate every grant with at least one new follower and/or at least one new or revised note. Although the query in the previous section satisfies that need, we also need to determine which users are following those grants – all followers of a grant (not just new followers) will receive a digest email as long as some of the follower and/or activity pertains to users within the same organization. This can be achieved by modifying the SELECT
DISTINCT recipient_followers.user_id AS recipient_user_id
FROM (
SELECT
gf.id,
gf.grant_id,
gf.user_id,
gf.created_at AS activity_at,
'follow' AS activity_type,
null AS text_content
FROM grant_followers gf
UNION ALL
SELECT
rev.id,
gn.grant_id,
gn.user_id,
rev.created_at AS activity_at,
'note' AS activity_type,
rev.text AS text_content
FROM grant_notes gn
LEFT JOIN LATERAL (
SELECT
r.id,
r.grant_note_id,
r.created_at,
r.text
FROM grant_notes_revisions r
WHERE r.grant_note_id = gn.id
ORDER BY r.created_at DESC
LIMIT 1
) AS rev ON rev.grant_note_id = gn.id
) activity
-- Limit activity to grants with (current) followers:
JOIN grant_followers recipient_followers ON recipient_followers.grant_id = activity.grant_id
-- Incorporate `users` table data for users responsible for the activity
JOIN users activity_users ON activity_users.id = activity.user_id
-- Incorporate `users` table data for recipient followers
JOIN users recipient_users ON recipient_users.id = recipient_followers.user_id
-- (No need to JOIN on `agencies` or `grants` tables because their data is only used for email bodies)
WHERE
activity.activity_at > :digestPeriodStart
AND activity.activity_at <= :digestPeriodEnd
-- Only consider actions taken by users in the same organization as the recipient:
AND recipient_users.tenant_id = activity_users.tenant_id
-- Exclude rows where the recipient user is the one taking the action,
-- to ensure that users only receive a digest if OTHER users took action:
AND recipient_followers.user_id != activity.user_id;The results of this query are a single column of unique recipient user IDs, which can be iterated over in a fan-out scenario so that individual digest emails can be constructed and sent in parallel. Querying for a single recipient's email dataIn this scenario, a single recipient user ID is a known value, having been determined as described in "Querying for digest recipients". Now we need to build the contents of the digest email that will be sent to this single recipient. Once again, our query is primarily concerned with grant activity that occurred within a start/end timestamp threshold. This time however, we need to select all the values that will be used in the body of the email, as well as filter results in the following ways:
SELECT
g.grant_id AS grant_id,
g.title AS grant_title,
activity_users.id AS user_id,
activity_users.name AS user_name,
activity_users.email AS user_email,
activity_users_agencies.name AS agency_name,
activity.activity_at,
activity.activity_type,
activity.text_content AS note_text
FROM (
SELECT
gf.id,
gf.grant_id,
gf.user_id,
gf.created_at AS activity_at,
'follow' AS activity_type,
null AS text_content
FROM grant_followers gf
UNION ALL
SELECT
rev.id,
gn.grant_id,
gn.user_id,
rev.created_at AS activity_at,
'note' AS activity_type,
rev.text AS text_content
FROM grant_notes gn
LEFT JOIN LATERAL (
SELECT
r.id,
r.grant_note_id,
r.created_at,
r.text
FROM grant_notes_revisions r
WHERE r.grant_note_id = gn.id
ORDER BY r.created_at DESC
LIMIT 1
) AS rev ON rev.grant_note_id = gn.id
) activity
-- Limit activity to grants for which the recipient user is a follower (note the additional condition):
JOIN grant_followers recipient_followers ON recipient_followers.grant_id = activity.grant_id
-- (This could alternatively go in the WHERE clause:)
AND recipient_followers.user_id = :recipientUserId
-- Incorporate `users` table data for users responsible for the activity:
JOIN users activity_users ON activity_users.id = activity.user_id
-- Incorporate `users` table data for the recipient follower:
JOIN users recipient_users ON recipient_users.id = recipient_followers.user_id
-- Additional JOINs for data selected for use in the email's content:
JOIN grants g on g.grant_id = activity.grant_id
JOIN agencies activity_users_agencies ON activity_users_agencies.id = activity_users.agency_id
WHERE
activity.activity_at > :digestPeriodStart
AND activity.activity_at <= :digestPeriodEnd
-- Limit to activity where the user performing the activity belongs to the same organization:
AND activity_users.tenant_id = recipient_users.tenant_id
ORDER BY
-- Somewhat arbitrary, but ensures rows with the same `grant_id` occur consecutively:
g.grant_id DESC,
-- Followers first, then notes (remove for grant activity in chronological order, regardless of type):
array_position(array['follow', 'note'], activity.activity_type) ASC,
-- Activity of the same type is ordered oldest to most-recent:
activity.activity_at ASC;The |
|
@ClaireValdivia Just want to confirm whether or not the email digest a user receives will contain their own follow/note activity (assuming it is not the only activity for that day) |
|
@greg-adams no, the user should not see their own follow/note activity in the digest. |
Full User Story
As an organization, we would like to foster more collaboration between our users to allow for the sharing of knowledge and resources to empower them to more efficiently and effectively apply for the grants discovered on our platform.
As a grantseeker, I want to be able to easily share grants I’ve discovered with my team.
As a grantseeker, I want to be able to easily find collaborators who may be able to help me when preparing and applying for grants.
As a grantseeker, I want to be able to quickly see what grants my team members are interested in.
As an admin, I want to be able to easily surface and resolve issues and blockers my team is running into.
Why is this issue important?
In order to help build the collaborative spirit we're going for, we need to have a prompt to pull people back to the platform when people they may be interested in supporting or receiving support from mark interest in the same grant. This will help bring users back to the platform in a timely manner to collaborate on previously marked grants.
Action Plan
Engineering Tasks
Acceptance Criteria
Designs
Trigger
A daily digest of follow & notes activity will send each morning to a user who:
General email specifications
Create new email notification for 'Grant Activity'
- From: USDR Federal Grant Finder ([email protected])
- Email Subject: New activity in your grants
- Preview Text: See recent activity from grants you follow
General Email Content
- Header should match Grants Digest header (i.e. USDR Logo with "Federal Grant Finder" underneath)
- Email Title: New activity in your grants
- Subtitle: Today's date will display under the title
- Footer: Should display the text "Change notification preferences" - this text should link to the /my-profile page of grant finder
- Body: - Each grant with activity should be displayed in its own box. There is no maximum of grants that should display. We will want to ensure that we are able to monitor frequency of the email size exceeding what can load (i.e. so we can understand how frequently users will not be able to see full content due to the email being too long)
Content within each grant box
- The grey header of the box should display the name of the grant - this should truncate after two lines with an ellipses at the end (see third grant in design as an example of truncation). The name of the grant should also link to the relevant grant details page in Grant Finder.
- Below the grant title, new note activity should display first if there is any. If there are no new or edited notes, then no note section should display. If there are no new followers, then no follow section should display. If neither new/edited notes or followers exist, the grant should not appear in the email digest.
- At the bottom of the grants box, the "View Grant" button should display and link to grant details page
Notes section:
Followers section
My Profile
The text was updated successfully, but these errors were encountered: