Openfoodnetwork: Analytics > Setup User Engagement / Acquisition KPIS with Metabase

[jaycmb]

Description

As an instance manager / PM I would like to track a basic set of user engagement/acquisition KPIs using Metabase

This issue includes the setup of these KPIs

• [x] Number of New Shoppers
• [x] Number of New Enterprises
• [x] Number of Active Shoppers
• [x] Number of Active Enterprises

Acceptance Criteria

• [ ] Query is created in Metabase, Title “Number of New Shoppers/New Enterprises..”, Description “This query returns the Number of New Shoppers / Enterprises...per specified time period”
• [ ] User can specify time duration of interest, e.g. “last month”, Jan 1st 2020-March 31st 2020"
• [ ] Metric is included in “Global Metrics - Test for FR Instance"
• [ ] Query can be reused for any instance of choice

Since those queries should eventually help to measure more complex KPIs, like

• Purchase Frequency: Buyer Segmentation, what % of users buying once, buying occasionally (tbd) and super users who are buying regularly
• Sales Frequency: Seller segmentation, what % of shops selling once, selling occasionally (tbd) and super users who are buying regularly

So possibly having an option to specify the number of sales or purchases.

[sauloperez]

I'm currently working on these on FR's replica in https://data.openfoodnetwork.org.uk/dashboard/9.

[jaycmb]

I added them to the Global Dashboard - OFN Test https://data.openfoodnetwork.org.uk/dashboard/17

[sauloperez]

I defined the _Number of shoppers_ at https://data.openfoodnetwork.org.uk/question/153. Shoppers are those users who placed an order. I then joined that with the users table so that in the dashboard we can filter that by users.confirmed_at. This means that said user confirmed his account.

Likewise, the _Number of active shoppers_ it's just the same definition of _Number of shoppers_ but filtering it by spree_orders.completed_at so we get those users that placed one in the time window specified by the dashboard.

However, I see rather large numbers...

[sauloperez]

We changed things a bit after discussing with @jaycmb . Those definitions above do not take into account signups even if they didn't place an order. Also, we should include that did not register but checked out as guest.

Therefore, I simplified Number of shoppers to simply the users that confirmed the signup but aren't enterprise owners. In this context, it makes no sense to speak about guests.

Number of active shoppers then becomes the count of distinct customers who placed at least an order. Guest and non-guest orders both include always a customer, so this takes them both into account. If the user is logged in when checking out we will then have a user associated with the order, that's the only difference.

These two metrics are also included in https://data.openfoodnetwork.org.uk/dashboard/17.

[sauloperez]

As for _Number of New Enterprises_ we'll need to revisit it to possibly consider:

• enterprises that have no turn over because they sell through a re-seller
• Profile-only enterprises that have no products

So far it only considers distributors.

[jaycmb]

Re

Number of active shoppers then becomes the count of distinct customers who placed at least an order. Guest and non-guest orders both include always a customer, so this takes them both into account.

That would be more "Number of Customers" -> I am going to rename that query, which is useful too!

For definition of Active Shoppers it would be better keep guest orders separate.
The reason for this is to get a clear picture when it comes to user segmentation and purchase behaviour over time (retention, purchase frequency..)

We want to understand the shares of

• inactive: users who signed up (and then never placed an order or haven´t done within a tbd timeframe, like 6 months)

• active : shopper has placed at least one order within specified /filtered timeframe
  -> These we can segment in the next step into single-time, occasional and frequent buyers

[sauloperez]

Alright then, if we define _shopper_ as a non-guest user, the Number of active shoppers is the count of distinct users who placed at least an order.

Likewise, the Number of inactive shoppers is the count of confirmed users who have no orders yet.

[sauloperez]

Further clarification for my comment above.

As I pointed out in https://github.com/openfoodfoundation/openfoodnetwork/issues/6222#issuecomment-715373504.

Guest and non-guest orders both include always a customer, so this takes them both into account. If the user is logged in when checking out we will then have a user associated with the order, that's the only difference.

all orders have a customer. That enables us to store an address and an email if they are checking out as guests. Orders that have a user imply that a user record was persisted as a result of a signup, aka. non-guests.

:warning: the customers table was introduced back in Feb 24, 2015 and the existing order records back then weren't updated to be associated with a customer. That'll be an issue for AU, at least.

[jaycmb]

Summarizing definitions as just discussed with @sauloperez

• Number of shoppers Count of distinct users

• Number of active shoppers: Count of distinct users that placed at least 1 order -> Exclude guest shoppers, because they are in customers

• Number of Customers: count of customers (registered users & guests) that placed at least one order

• Number of Enterprises: count of all enterprise users (distributors, re-sellers, profile-only...)

Number of active Enterprises: we agreed we cannot define "active enterprises", as for example there´s no meaningful way to measure profile-only enterprise users to be active, we decided to split in

• Number of (active) Distributors: count of distributors, that had at least 1 sale (adding "active", because we use this metric per time frame and some distributors did not have a sale within the last 6 months etc)

• Regarding Number of Non-Distributors: grouping enterprises that have no turn over because they sell through a re-seller, profile-only enterprises that have no product

[sauloperez]

All done!