Global Metrics Dashboard
This page described the global metrics dashboard. This dashboard pools data directly from Postgres database (via n8n) for all the globally managed instances. It is updated automatically every month to include last month's data via scheduled n8n workflows.
Dashboard Link: https://docs.google.com/spreadsheets/d/1rqGdGK2iBB-ba2dXCWPon0Ntl1-eGcX_lhQNousvKVc/edit#gid=0&fvid=2043055293
Instances added to the Dashboard: Australia, Belgium, Canada, France, Germany, Hungary, Ireland, New Zealand, Spain/Katuma, UK, USA
Tools used: Postgres (connection/access), n8n, Google Sheets, OFN API, Open Exchange API
Notes:
Do not change the country names in google sheet. It is using the names to match the data from n8n to google sheets.
Idea is to keep things simple and have only limited number of metrics which are actually used/required and can be automatically updated every month.
Monthly update time and frequency:
This dashboard uses multiple n8n workflows (one for each metric) to pool the new monthly data. All of the dashboard related workflows will run between 00:00 to 00:30 (GMT) on 10th of every month. Each workflow starts at a different time and only one workflow (ideally) will be running at any time. This was done to avoid any potential memory issue.
Schematic
Division of Information across sheets:
Global Snapshot sheet: Specifically containing summed up data for key metrics. This is basically OFN as a whole (limited by data availability of instances-globally managed instances).
All Instances (globally-managed ones) - Metric sheets: Data for a given metrics split across instances. No. of metrics within a sheet is limited to avoid cluttering and make it easy to use Slicer for filtering months.
Charts: Some are there, more can be added later on
List of Metrics (Monthly metrics only)
Monthly Sales: Global and Instance Level
All the sales data (except the raw data sheet) will be in Euro (converted using the exchange rate on the 10th of that month). Monthly exchange rate is updated using Open Exchange API (via n8n).
Hub Shopfront Sales: Sales for shopfronts with “Sells” field set to “any”
Individual Shopfront Sales: Sales for shopfronts with “Sells” field set to “own”
Monthly Completed Orders: Global and Instance Level
Enterprises
Monthly New signups: Global and Instance Level
Monthly Active Enterprises: Global and Instance Level
Shopfronts (No. of shopfronts making a sale in a given month. Can include test orders.)
Producers (No. of producers making a sale (i.e. their product sells in any shopfront) in a given month. Can include test orders.)
Monthly Unique Shoppers: Global and Instance Level (Includes both registered and guest)
Monthly Avg. Basket Size: Global and Instance Level
Workflow:
Open n8n admin account and search for “Global Dashboard”. There will be 9 workflows. Individual links below.
Orders data: https://n8n.openfoodnetwork.org.uk/workflow/91
Sales data: https://n8n.openfoodnetwork.org.uk/workflow/140
Shopper data: https://n8n.openfoodnetwork.org.uk/workflow/141
New Enterprises / signups: https://n8n.openfoodnetwork.org.uk/workflow/105
Active Producers: https://n8n.openfoodnetwork.org.uk/workflow/143
Active Shopfronts: https://n8n.openfoodnetwork.org.uk/workflow/142
Hub Shopfront sales: https://n8n.openfoodnetwork.org.uk/workflow/145
Individual shopfront sales: https://n8n.openfoodnetwork.org.uk/workflow/144
Exchange rates: https://n8n.openfoodnetwork.org.uk/workflow/102
Instances with expressions in SET module in case of null output
Sometimes a SQL queries can return "null" or doesn't return anything for a particular instance ex. there are no new sign ups for a instance in a some month. (Note: Null output caused errors in last few cycles) Without any output from the postgres module, following modules won't work and the dashboard workflow will break or give error. To avoid this, Postgres module for following instances have been set to "always output data", and the SET module following postgres has an expression to put 0 in the value field in case of null output.
List of Instances with expressions: New Zealand, Ireland, Hungary, Belgium, Canada, Germany, Katuma Spain
Explainer Videos
Global Dashboard Overview:
Global Dashboard n8n workflows:
Last updated
