it’s hard for a user to find the info he wants in the existing reports (lots of noise, infos spread among multiple reports)
Some info people need isn’t available on existing reports (ex: delivery place)
There is a complexity overhead in meeting info needs (hard to adapt to users requests, today we need to create each time new reports or add columns to existing ones each time there is a new need, which is not easy)
Who does it impact
Fred (if he needs reports on his products, how they are sold, etc.), Mary and Shannon (for reports on their sales, orders, delivery, packing, etc.), Manel (for meta-reports about instance use). So basically all users apart Jane (final customer).
What is the current impact of this problem
Today hubs need to ask reports modification or new reports if they want a data that is not yet in the reports, and we are not able to do it. So basically they have to do with what is there, and try to find workaround to follow other data they need.
It’s hard to measure the performance and evolutions for hubs and instances as data are not easily accessible.
What is the benefit in focusing on this
It will allow much more flexibility for our users in their activity monitoring, as well as for instance managers.
It will make it easier for hubs to organize their operations with the appropritate data they need.
I’ve had a report improvement request from a user and I’m not sure what to tell him about the status of report improvements, and whether his particular request will get captured in the next reports improvements?
What he needs:
In the Customer Addresses report he needs the customer’s billing address to be split into 3 columns (address, city, postcode). Currently the report only gives you the address in a single column with the address and suburb (postcode not included). He needs this so he can import data into Quickbooks.
@sstead we want to take this as an example of us responding in ‘bespoke’ way to meet needs of aus users where things unlikely to get through global pipe.
can you tell @maikel who this is and if there is any particular date setting to start from or they just want all customers? He can whip up a csv in less than 10 mins and send to them in good faith
Then if you can also find out:
how often do they anticipate wanting this e.g. every month, sporadically, every time they have a new customer?
how important is responsivity e.g. can they wait 24 hours or need instantaneously
There would be a range of ways we could provide this which we could work out a cost for (minimal), but want to make sure we don’t overspec
It’s Josh, he needs it once off to import all customers into Quickbooks (entire date range) and each week to add any new customers.
He can get the information he needs across 2 reports - he can use OC customer totals (for address in the format he needs) and the Customer Address report (for phone number, which is not in OC cutomer totals).
Thanks @lin_d_hop - I figured as much. So it seems that basically what we have here is that every instance that has ‘local dev’ is basically fudging this to provide what they need for their users while waiting for longer-term global process.
We in Aus have the same immediate need and business model opportunity to respond to this, and would like to do so in most effective way to:
a) provide immediate relief / satisfaction to our users that makes them excited about starting to pay for OFN
b) ensures that what we do in short-term is a step towards:
awesome, flexible, customised reporting solution for ofn users
reporting solution that can also be used outside ofn e.g. possibly read data from ofn and/or other tools
c) is easy to apply and use by other instances in the short term, possibly able to be rolled out also to those who don’t have local ‘friendly’ dev to play with things for them
So we’d love to understand pros/cons of the approaches you’ve taken in other places please It would be really helpful if you could each summarise your instance-level reporting / custom user solutions at a very high-level below to help us work out which is best way forward . .
@lin_d_hop could you document (even just a few dot points here):
how you are doing this with Zapier
estimate of complexity / cost for setting up for a user, including 1st one you do it for and if it gets easier / cheaper for later ones or not really . .
other reporting use cases (e.g. not just the quickbooks solution) - I think maybe you are also send stuff to google sheets, I assume as custom reports? and so people can then customise their own?
@sauloperez similarly could you reply post here with high level non-dev explanation of Katuma Reports, including:
why you did this
what it does
what it means for your users - are you running just one set of your own Katuma reports (that replaces standard ofn reports page) or are you running / customising different reports for different users? If the former, can it also do the latter?
how easy for other instances to set-up and adapt etc?
@CLFC I know you have documented elsewhere, but could you also please post a high level summary here of how you are doing customised reporting so it’s all in the same place? thanks!
Last but far from least, we are fresh from lots of exciting conversations with @MyriamBoure about DFC, apis and options for doing things in ways that are easy to share also outside OFN (for us this is most pertinent for our partner CERES), which raises the question of experimenting with reporting in some kind of separate web app or component that can hit up ofn in the first instance, but potentially also draw data from other things down the track . .
As one of those instances that doesn’t have a friendly dev, I’m so THRILLED to see this itemized - and look forward to whatever we can come up with. Thanks @Kirsten. I think there is a parallel situation re: how instances are using various integrations to help users with shopfront marketing. For ex., I note in the UK bus model that you folks are offering integration with mailchimp as a service for an additional fee. Is this correct @lin_d_hop. ? I wonder if - in parallel to what Kirsten outlined above - if we should do a similar process to gather together the pros/cons of approaches instances have taken to help shops with marketing. Anyone else doing things in this regard?
Yep, thanks @Kirsten for summarizing and follow up on that ! I feel a bit desperate in France as well to move forward, we have many users asking that… if already implemented in UK maybe super short term solution is Zappier, second is document our API and third is adapt our API to DFC (and then we can adapt anytool we want to connect to DFC and it can talk to OFN).
So adding up on Kirsten’s request @lin_d_hop if you could share and leverage the global community with your efficient solution that would be awesome @Matt-Yorkley had open some time ago that issue in GH : https://github.com/openfoodfoundation/ofn-install/issues/230 but it would be great to agree on the path and if we agree it is first step prioritize and do the stuff (and train local team so we can then use it easily to generate custom reports for our users !)
What I have set up, and @tschumilas has been using for her hubs, are a couple of scripts. One that she has been using to create packing lists/labels - and one that CLFC would use to generate our producer invoices to work around our 5% producer fee model.
The packing list/labels script starts with the OFN packing report, exported as a .csv file, and uploads it to a (currently my own) server. The PHP script then imports that CSV data to a mySQL database, giving each row an order cycle ID, and generates two pages/reports - a customer packing report and a supplier packing report.
The customer packing report creates labels for each producer, broken down to one label per product per customer. After the labels, it creates a packing list of all products for each customer. When printed, or saved as a PDF, these will end up one per page. (Using CSS page breaks in the print job.)
The producer report is essentially the same, only sorted by producer instead of customer. The packing list that follows gives a page for each producer, listing all of the products they need to fulfill their orders. Just item/quantity. IE: I need 3 loaves of sliced bread this OC. Or 7 turnips. But this one does not identify who gets how many. it’s just a ‘quantities at a glance’ report.
I think I mentioned somewhere but cant remember where. Adding here:
This could be an opportunity to setup some kind of ETL in OFN. It’s an intermediate (and very very common) solution between zapier and api.
You setup something like https://www.talend.com/ to extract data from ofn’s production database into a separate “BI database” (this is really easy to do, just a little effort to maintain as the operational database changes). then you connect your reporting tool to this database. This is like the zapier solution but a lot better, more scalable and easier to manage. This “reporting tool” can still be a api+web components app (or some 3rd party) that renders reports on top of this “BI database”.
With talend (or similar tool) and this “BI database” we can transform the OFN data to make it friendlier for reporting and also add data from other systems. And if we build an API on top of this DB, this would be our DATA API. I think this approach is a lot easier than expecting to have an OFN API that allows for good reporting.
The zapier approach (external reporting tool connecting to live operational database) has my no-go
I like that @luisramos0 ! I would love to discuss that with @sylvain as well in term of long term strategy to enable to share data with various web-components on a later stage. I guess the data and API needs to meet the W3C standards, but with the semantic bus then which is a semantic web ETL we could transform non semantic data into semantic data https://www.virtual-assembly.org/semantic-bus/ and hithub is https://github.com/assemblee-virtuelle/Semantic-Bus. It is an ETL actually but maybe Talend on a first step is more adapted to our need, you know better. @lin_d_hop you where very much in favor of Zapier, what do you think of Luis’suggestion ?
I like the idea of additional BI database. It certainly doesn’t stop us using zapier and gives more options.
It also is a step toward exploring the architecture needed to facilitate single global instance functionality …
any estimate of how hard this new BI database with Talend or something is?
any idea / assessment of possible existing reporting tools we might use?
Would we then connect Talend(orwhatever) to Zapier to do integrations with xero, quickbooks etc? or we’d do it direct from this new database? Think i might be a bit lost!!
I would argue that writing an integration with Zapier will give us more bang for our buck short term on integrations. One integration into Zapier for 1000s of integrated apps. Rather than loads of bespoke connectors between apps to integrate.
This wouldn’t necessarily be the solution forever, loads of reasons that we might switch to focusing on individual connectors. But integrating with Zapier properly first would mean we can cheaply try out loads of different integrations and find the ones that stick and create value.
In terms of effort to create a BI database I’d want to hear from other devs. I’d say the setup is pretty easy, a few days of dev work to create the database and setup some talend processes to copy the data. I think the biggest cost is that after that we need to maintain the processes: when the OFN DB changes, we need to update the talend processes for the BI DB to be in sync.
We could have a single global BI database that takes data from all instances (not sure this would create data protection issues but if we want to go “single instance” it would be the same problem).
I think this BI DB is something very good and useful but I am not sure it is the right decision to go for it now. Maybe better to push for zapier integrations using the API, because that will push the API dev forward.
From my perspective, any approach is ok as long as we dont have integrations like zapier reading from the live DB directly as we have right now.
regarding reporting tools, I know quite a lot of large comercial reporting tools (I have experience with MicroStrategy, IBM Cognos, etc). I’ve also seen Tableau and Looker being used, they are both commercial and market leaders in BI.
Googling for open source tools the ones that appear in most lists are:
metabase - looks like this one is full open source, not a community edition like the others
It would be really helpful if you could each summarise your instance-level reporting / custom user solutions at a very high-level below to help us work out which is best way forward . .