How can I manage subscription reminders in foresight

[Patrick Keogh]

Hi,

I am new to foresight and  I have a business problem I'd like to solve.

We are a non-profit and run Google Workspace for our members.

Members pay an annual membership fee, but right now it is a manual process to check who has paid and send reminders to those who have not.

I'd like to add "Renewal Date" to the user record in Workspace and then:

• Send the user an email reminder to pay.
• Provide a method for the treasurer to easily update the records of members who have paid using a "Data uploaded" trigger.
• Suspend users who have not paid after (say) two months from their renewal date.

The problem I have is that foresight seems to have lots of fields to do with employees that I could use but none of them are date fields.

So is foresight extensible in being able to handle additional fields in the user record?

Thanks in advance!

[Patrick Keogh]

As an alternative, if I reuse a field like Cost Center as the renewal date field, can foresight convert this string to a date to do date arithmetic

[Jason Huang]

Hello,

It's doable with Foresight in a different approach than you described. Instead of storing the Renewal Date data in Google Admin, it's better to store it in a Google Sheet, which is much easier to manage the membership renewal. Let me explain how it works. 

1. You maintain a Google Sheet. See this membership template. 
   1. The sheet All Members is the master sheet you need to maintain the Member Email, Firstname and Renewal Date. You can use Foresight's List users action to export all your members.
   2. The Expiring Members sheet and the Suspend Members sheet are mapped sheets with formulas. 
   3. The Expiring Members sheet filters the members from the All Members sheet if their Renewal Date is 7 days from today. The list automatically updates daily.
   4. The Suspend Members sheet filters the members from the  All Members sheet if their Renewal Date is 60 days before today. The list automatically updates daily.
2. Publish the Expiring Members sheet and the Suspend Members sheet as CSV files using the Publish to web feature.
3. Now you set up 3 rules
   1. Rule 1: Payment reminder email rule. Data uploaded trigger => Email action. 
   2. Rule 2: Suspend user rule. Data uploaded trigger => Suspend user action. 
   3. Rule 3: Daily job rule. Recurring times trigger => Upload data to rule action and  Recurring times trigger =>  Upload data to rule action. In the 2 Upload data to rule actions, put the published CSV links in the CSV file URL field, respectively. This rule executes every day, pulls the members from the Expiring Members sheet and sends them to the Rule 1; It pulls the members from the Suspend Members sheet, and sends them to Rule 2. 
4. Then every day, the latest member lists of Expiring Members would receive an email. The member lists of the Suspend Members would have their Google Workspace user accounts suspended.
5. The treasurer just need to update the Renewal Date column of the All Members sheet monthly for the members who have paid. 

Would this solution work for you?

Thanks.

-Jason

[Patrick Keogh]

That potentially sounds good. So (don't you hate it when the user broadens the requirements?) thinking about new registrations...

We use a Google Form to collect new member data. So could that Forms data flow to the All Members sheet and then use Foresight to update the Google Admin data including Group and OU membership?

[Jason Huang]

Yes, each form submission would send the form data to the spreadsheet as a new row. You can create a separate sheet New Members with formulas to extract yesterday's registrations, e.g. form submission timestamp >= yesterday AND form submission timestamp < today. This sheet records all new users with the user provided data. For the formula to auto update, you also need to change the File > Settings > Calculation > Recalculation to On change and every minute. 

Then in Foresight, you create the 4th rule, bulk create users. Data upload trigger => Generate password action => Create user action. Create yet another 5th rule, auto add new users to groups: User created trigger => Add group member action. 

Then in the Rule 3 daily job in the last email, you add another Upload data to rule action to pull this sheet as a CSV file and upload it to Rule 4. 

Therefore, each day Foresight would streamline creating users and assigning the users to specific groups based on the registration in the past day. No coding is required. 

Just a little teaser. In future versions of Foresight, we will integrate with Google Forms so that the user registration can be done in real-time. That is, as soon as a user submits a Google Form, Foresight creates a new user in your domain and assigns the new user to specific groups. 

We love solving problems. Any time! :D

Thanks for the feedback.

[Patrick Keogh]

Thank you for the superb support.