Reporting for Instances

OFN UK has three years funding, and as of Feb 17 we will need to report back to our funders and include updates on our target growth figures. The three main things we need to report on are:

  • Monthly orders

  • Total registered shoppers

  • Number of sellers (which we are defining as hubs and producers with a shopfront)

I have found the first two easy enough to do - for orders I pull an order cycle payment or distribution total into a CSV and just check the number of lines, and for shoppers I do the same but with a mailing list report. But Iā€™m finding no easy way to track active and inactive enterprises.

Currently Iā€™m using the same order cycle report as for the monthly orders, grouping them by hub and just counting how many hubs have received orders that month. This is fine whilst we are small but will quickly become cumbersome as we grow!

It would be great if Superadmin was able to pull a ā€˜sales by enterpriseā€™ report that would show the total sales by enterprise that month, which could further be split according to whether they have a shop front or are selling through other hubs. This would also help us quickly identify inactive hubs and either contact them to see if they need support or identify them as needing closed in the future.

Iā€™d be interested to hear how other instances are monitoring their hubs and enterprises at the moment, and what sort of reporting they think would be helpful for them in the future?

I feel like you could make the enterprise accounts functionality do something like this. Not sure if youā€™re using it or not, but if you ran the monthly invoice generator you could then do the report against that hub. @sstead would this be right?

Accounts and Billing seems to manage part of this from what I understand.

In our case, since we arenā€™t charging users turning over under Ā£5k/year these users arenā€™t showing up in accounts and billing, though we do want to capture this for our funding reporting.

I think from our perspective it would be easiest to add another super-admin report to capture this data in a clean way.
Unless @sstead or @oeoeaio have some other suggestions?

A new, specialised report would be an option, but you can currently get most of the info you need from existing reports, with a bit of excel adjusting.

Each month we download the entire monthā€™s ā€˜order cycle customer totals reportā€™ as superadmin (you may need to download this in 1 week chunks if there is too much data). This is a very data rich report and with a little tweeking you can find out most things you want to know. You can create a pivot table which quickly tells you which shops had orders, how many orders each shop received, how many customers ordered in the month, and what the total turnover was. This is a little fiddly, but if you have 5 metrics that you report on, it wouldnā€™t take that long to get the numbers you need. Thereā€™s an example below, Iā€™ve changed the totals and names for privacy:

. We create this pivot table each month, but you could also have a running annual spreadsheet which gives you year to date information. Each month I check which shops are active, and if someone who was previously active is no longer receiving orders, I might touch base to ask why, and see if they need help.

For the third dot point- the superadmin ā€œusers and enterprisesā€ report lists all enterprise profiles on the instance. You can filter out those which are invisible or have an unconfirmed email address, and from that you can filter out those which sell none, own or any to find out whoā€™s profile only, whoā€™s a producer shop and whoā€™s a hub shop. This can be a bit misleading though because someone might sign up and choose to have a shopfront, but never actually run an order cycle, so the order cycle customer totals report is a better indication of who is running an active shopfront.

The accounts and billing function works on a monthly basis and will create ā€˜ordersā€™ for each enterprise that needs to be billed. If you only bill enterprises who reach a certain level of sales, those with lower turnovers wonā€™t be captured, so looking at ā€˜order cycle customer totalsā€™ is a better way to see all activity.

1 Like

Thanks for the detailed response @sstead!

I have realised that different reports I am pulling for the whole UK instance are giving me different totals,and am wondering if anyone has an idea why? @sstead @Kirsten @MyriamBoure @Kirsten

When I try to find out our total sales for the month of January, I have pulled it three ways, all of which are giving me different totals;

  • Sales Total (2017/01/01 - 2017/02/01) - Ā£8015.30

  • Order cycle management payment methods (2017/01/01 00:00 - 2017/02/01 00:00, sum total of individual orders) - Ā£7644.29

  • Order cycle customer totals (2017/01/01 00:00 - 2017/02/01 00:00, sum of order totals) - Ā£7578.94)

Can anyone figure out why these would be different? As the sales total report doesnā€™t ask you to input a time, just a date, I thought it was perhaps giving me those dates inclusive and including sales from the 1st February, but if I take it back a day itā€™s giving me Ā£7687.76, which still doesnā€™t match up with the other two reports!

As weā€™re moving into become financially self-supporting, itā€™s going to become increasingly important that we can produce accurate reports as a national instance so Iā€™d like to try and figure it out before it becomes too urgent!

Can it be linked to shipping fees, or payment method fees? Maybe issues with fee recalculation if modification were made in orders? Just guessingā€¦ Iā€™m not using those for now, our users donā€™t use those reports for the moment so never investigatedā€¦

The Sales Total report breaks down the total by products, fees and combined, and the total products before fees doesnā€™t corrrespond to either the payment methods or customer totals either. If it is the case that fees arenā€™t being included, it must be one particular fee and not all of them.

Iā€™ve not really dug into the reports in OFN yet - but that is on my todo with some testing next week - so this is something Iā€™ll look at too. Iā€™m wondering - (hypothesis) - might it be that fees that are in the shop front (enterprise fees) are included in some of these totals, but fees that are added at checkout are not? just brainstorming here.

Can it be linked to inventories @mags ? It seems the system doesnā€™t integrate the inventory prices everywhere, see https://github.com/openfoodfoundation/openfoodnetwork/issues/1446ā€¦

We are having issues with inconsistencies in UK national sales reporting. I think this may have been triggered by deploying v 1.10. Sorry I donā€™t have much detail on the inconsistency but @Sara might be able to add more detail.