Producer sales total after the Order Cycle closes

[div-yansh-1]

Delivery Stage

• Initial proposal
• Agree implementation proposal
• Estimating
• Funding Confirmed
• Delivered

1. What is the need / problem?

At the end of every order cycle, Hub manager wants an email with a list of producer who had sales in a given order cycle (preferably in form of CSV).

2. Which type of users does this problem affect (and how many, if known)?

Hub Managers

3. What tools does this involve?

Email, CSV

4. Links to connected discussions

https://openfoodnetwork.slack.com/archives/C04U02X3806/p1679964247999209

5. Implementation Proposal

To use a n8n workflow which automatically triggers when an order cycle closes for the hub (using polling with Postgres and function nodes). Then using a SQL query (via the Postgres node in n8n), we will get the list of producers sales and info for that order cycle. After which, the data will be converted into CSV file and will be emailed to the hub manager.

Fields in CSV:

• Order Cycle Name and Id
• Supplier Enterprise Name
• Supplier Enterprise Id
• Supplier Enterprise "Notifications" email address
• Number of orders that include the supplier for the hub's order cycle
• Sum of Quantity*price for items sold in that order cycle

Enterprise Id and OC id are very easy to add in the workflow. So, no extra time is needed for that.

Whats Polling (in context of this issue): Basically, n8n will keep on checking at fixed intervals (every hour - can be reduced if needed), if a new order cycle has closed for the specified enterprise. It's similar to Zapier trigger.

Notes:

• The workflow will be made/developed in USA's n8n account and will be documented in the API handbook.
• Polling interval will be 1 hr initially (i.e. there can be a time gap of up to 1 hr between order cycle closing and the email being sent) - This can be reduced if needed. Polling can be replaced with OFN Order cycle close webhook once it's ready.
• This workflow could be adapted for other hubs by replacing the enterprise id used in SQL query and the email id of the hub manager.
• Email subject and body can be anything (text) (USA can propose what they want email subject and contents to be)
  - Need to connect a Gmail account in USA's n8n account to send emails. To do this, I would need access to the email account which would be sending the email.

6. Estimates and funding partners

10-12 hrs

7. Link to Workflows, User Guide etc

[div-yansh-1]

Hi @lauriewayne , We can do this in n8n using polling, which means the workflow will trigger when order cycle closes.
It will get a list of Producers (Name, Email, and Enterprise ID - let me know if you want some other fields also) who had sold an item in the closed order cycle and email it to the hub manager as a CSV file.

To do this, I would need to know the name and enterprise id of the hub, email address for sending the email to, and content for the email (optional).

I don't think there is an existing gmail connection in OFN USA's n8n account, so I would need to set this up first. For this, I would need access to OFN USA gmail account or any other email account you want to send the email from.

To do this (development, testing, and documentation), I would need 5-7 hours. Once finished, you can easily replicate the workflow for other hubs (steps will be in the documentation).

[lauriewayne]

Thank you! I'll get the hub id (there might be a few who want it right now) and Gmail setup. Should be able to get back to you by this time tomorrow.

[div-yansh-1]

@lauriewayne @lin-d-hop can you have a look at the implementation proposal and let me know if its okay, so that I move forward on this issue.

[lauriewayne]

Thank you @div-yansh-1 ! The fields in the csv that are wanted from the hub manager are:

• Order Cycle Name
• Supplier Enterprise Name
• Supplier Enterprise "Notifications" email address
• Number of orders that include the supplier for the hub's order cycle
• Dollar Amount due to Supplier for that order cycle ($)

There are two new fields in the list above that will increase the time estimate, and I think this is a complete list. Sorry for the scope creep! If these are not yet supported by the API, we can let the user know and proceed with just the first three items.

The emails should come from support-usa@openfoodnetwork.net. The hub that is wanting this is OFN UID 1342 (South Cumberland Farmer’s Market) but for testing/signoff purposes please use OFN UID 2 (OFN USA Demo Hub).

[div-yansh-1]

@lauriewayne
Re. the fields:
Order Cycle Name: Can be added (would have to tweak my plan, as my proposed SQL query will only give OC id). Basically, I would have to run a mini query in parallel and merge the data with the main query.
Supplier Enterprise Name: Already included
Supplier Enterprise "Notifications" email address: I can get the email address associated with the producers (but it won't be the Notifications email address) using my existing query. To get notifications email address, I would have to run another mini query to get notifications emails for a producer (using enterprise roles table). Basically, it can be done.
Number of orders that include the supplier for the hub's order cycle: Can be added
Dollar Amount due to Supplier for that order cycle ($): So, I can get the sum of item total (quantity*price of products sold) for the producers, but it won't include the adjustment totals for the order. Reason being, adjustment is applied at order level, whereas to get item totals we need to go line item level.

Yep, it will increase the time estimate by few hours. Overall it should be around 10 - 12 hrs.

[lauriewayne]

Thank you @div-yansh-1!

• The order cycle name would be important, thank you (since hub managers don't use ID to keep track of order cycles)
• Which email is associated with the producer in your current design? In the enterprise settings we have the public email address, the owner, the notifications address, and the managers.
• I think the adjustments happen at the hub level and do not affect the amount the producers get, so quantity times price seems ok (probably need a sanity check here). The hub manager wants to know what to pay each producer/supplier for what they sold during the order cycle, and quantity times price would get them there, correct? In the case where that number is different because of order additions or cancellations after, the manager would have to figure that our separately.
• Is there a significant overhead with polling? If not, 10 minutes seems like it would be good.
• Other hubs will probably also want this integration, so wondering if it can be built from the start to include more than one hub (and what the performance hit would be).

Thank you for your patience! It is so appreciated as we figure out this process!

[lauriewayne]

Hi @div-yansh-1 just checking in to see the status on this one. Are you waiting on me at all?

[div-yansh-1]

@lauriewayne
Yes, we can add order cycle name.
By default it's the public email address. I can get the notifications email address if we want that.
Polling: I would say lets start with 1 hr and we can reduce it to 10 mins if needed.
Quantity *price seems to be working.
Adding too many hubs in 1 workflow might lead to some memory issue with polling. So, I would avoid that. My preference is to setup 1 workflow per hub requesting this. We can easily adapt this integration for other hubs
(by duplicating the workflow and changing few settings).

[div-yansh-1]

@lauriewayne can you check the updated implementation proposal and let me know if its okay. Then I can start working on this.

[lauriewayne]

I think it is okay but need to get the sanity check on the implications of price*qty. I believe we would want the notifications email (in many cases this is the same as the public email from a practical standpoint, but it seems like a good practice to use the notifications email, since I think that's what it was set up for).
One workflow per hub would totally make sense.

I will follow up with the user and get back to you ASAP - thank you!!

[lauriewayne]

Hi @div-yansh-1, I checked with the user, and sadly, they have figured out some other way to get what they need and this project is no longer a priority for them. 😭, so we would not have an actual "customer" for this one and it makes sense to wait . Very happy to contribute to development funds to compensate for the hard work you have already done figuring it out!
@lin-d-hop, process wise, in order to avoid scenarios like this, does it make sense to have some specific minimum amount of funding before asking for work to be done on figuring out what would be involved in creating a complete requested solution?

[lin-d-hop]

Hi @lauriewayne
On big pieces of work we usually do an estimate for an estimate. For smaller pieces, it is often just cost of sales and part of why we mark up dev time 4-6 times.
If you would like to update your business model to ask for an 'exploration contribution' in cases that you think this might happen, that would make sense in my mind. Though I feel like this is your call as 'wooing' users is an art.