JEBRA DASH

Reference

Page 1 - Sales

Revenue 

● Explanation: This represents the total revenue generated from sales transactions,

● Calculation: Total revenue = Sum of line totals - total account balance

● Chart: A large number showing sales excluding account balances.

● Filters: None

Refunds 

● Explanation: This represents the total refunds from sales transactions,

● Calculation: Total refunds = (Sum of line totals - total account balance) * -1

● Chart: A large number showing refunds excluding account balances.

● Filters: Order_Status = Refunded

Credits / Memberships / Gift cards / Penalty fees / Clothing & accessories / Food & Beverages

● Pie charts showing revenue from product types, excluding account balances.

● Calculation: Credit revenue = line totals - total account balance

● Chart: A large number and a pie chart that breaks down the revenue into two categories: ‘Credits’ vs. ‘Other’.

● Filters: Only revenue from products classified as "Credits" is included.

Buyers

● Explanation: This chart compares the first-times buyers with repeat buyers over a line chart

● Calculation: Count_Distinct (Customer_ID)

● Chart: A line chart which is broken down by dimension “First Purchase?” comparing first buyers with repeat buyers for the selected time period

● Filters: None

Sales summary

● A table showing Product Type Categories as the dimension with the following metrics displayed as bar charts:

○ Revenue (as sum of line totals excluding purchases using gift cards)

○ Percentage of Revenue: Shows the revenue as a percentage of the total (calculated as a percentage of the revenue figure above).

○ Percentage of Number of Sales: Shows the percentage of completed sales relative to the total number of sales. Refunds are excluded

● Filters: None

Page 2- Growth

Month-on-month (%) revenue change by product category

● Table with product category as rows and Month-year as columns.

● metric is MoM % change in revenue for

● Filters: None

Monthly revenue and month-on-month change

● Explanation Monthly revenue excluding refunds and excluding purchases made using account balance compared monthly

● Bar chart shows monthly revenue for the selected year (quarter) , Line shows the MoM% change.

● Filters: None

Repeat buyers (by month) and month-on-month change

● Explanation Repeat Buyers are calculated using formula: SUM(IF(Customer_Frequency > 1, 1, 0))

● Bar chart shows monthly repeat buyers for the selected year (quarter) , Line shows the MoM% change.

● Filters: None

First-time buyers (by month) and month-on-month change

● Explanation First-time Buyers are calculated using formula: SUM(IF(Customer_Frequency < 2, 1, 0))

● Bar chart shows monthly first-time buyers for the selected year (quarter) , Line shows the MoM% change.

● Filters: None

Page 3 - Conversion

Customers that become members (%)

● Large number shows the % of customers to converted to memberships

● Bar chart shows a breakdown by the quarter of customers becoming members.

● Filters: None

Days until membership conversion

● Large number shows the average number of days between a customer’s first transaction and their first membership purchase for all customers who have made their first purchase.

● Bar chart shows a breakdown by the quarter when a customer made their first purchase.

● Filters: Customers who converted to membership & exclude negative values (days to convert)

Visits until membership conversion

● Large number shows the average number of visits between a customer’s first transaction and their first membership purchase for all customers who have made their first purchase.

● Bar chart shows a breakdown by the quarter when a customer made their first purchase.

● Filters: Customers who converted to membership & exclude negative values (days to convert)

Purchases made by winbacks

.

● Bar table shows a % of purchases made in each product after the winback.

● Filters: Last transaction type is credits or memberships. Only includes winbacks.

Winbacks

● Visit Winback is defined as follows:

○ WHEN Days between last two visits < 30 then "Active customer"

○ WHEN Days between last two visits >= 30 AND Days between last two visits < 45 THEN "30-44 days" …

○ WHEN Days between last two visits >= 45 AND Days between last two visits < 60 THEN "45-59 days"

○ WHEN Days between last two visits >= 60 AND Days between last two visits < 75 THEN "60-74 days"

○ WHEN Days between last two visits >= 75 AND Days between last two visits < 89 THEN "75-89 days"

○ WHEN Days between last two visits >= 90 THEN "90 days or more"

● Bar chart shows % of inactive customers in each category.

● Filters: Exclude active customers

● Date control by customer’s last visit.

Credit conversion, Membership conversion, No conversion.

The percentage of customers who made their first purchase during the selected period and:

● Only purchased credits packages

● Purchased at least one membership package

● Made no further purchases

● Filters : First transaction is memberships OR credits

Journey after first purchase

● Table with heatmap showing first purchases and conversion from that first purchase to Credits, Memberships and No Conversions.

● Filters : First transaction is memberships OR credits

Page 4 - Customers

Avg number visit per customer

● Average number of visits defined as total visits ÷ total number of customers

● Filters: Excluding 0 visits

% of customers who visit only once

● Number of customers who have only visited once as a percentage of the total number of customers.

● Filters: None

Total customers who visited 1 to 20 times

● Bar chart showing number of customers who visited 1-20 times

● Filters: Excluding 0 visits

Average lifetime value

● Average spend per customer defined as total amount spent ÷ total number of customers

● Large number shows the lifetime value ($)

● Bar chart shows the quarterly average LTV

● Filters: First purchases >365 days. Excluding 0 visits.

Average length of stay

● The difference, on average, between customers’ first transaction date and last transaction date.

● Large number shows the average length value

● Bar charts shows the quarterly average length of stay

● Filters: First purchases >365 days. Excluding 0 visits.

Average lifetime value

● A heatmap showing the following performance metrics by quarter:

○ Number of new customers, defined as customers who have made their first transaction in that quarter

○ LTV is the average spend by customers who joined that quarter

○ Month 1 to Other months shows the average spend per month since joining.

● Filters: Exclude negative values (days to convert)

Page 5- Memberships

Attrition this month

● Attrition: Memberships that have been cancelled or have ended Memberships ÷ Active memberships at the beginning of the month

● Large Number shows the average attrition for the current month

● Grey bars shows monthly average attrition(%) for last 12 months

● Orange line shows the MoM change (%) for attrition.

Active members this month

● Active: Memberships that have a status = active in Mariantek dataset.

● Large Number shows the active members for the current month

● Grey bars shows monthly active members for last 12 months

● Green line shows the MoM change (%) for active members.

Net members this month

● Net members: Sum of total members gained minus total members lost

● Large Number shows the net members for the current month

● Grey bars shows monthly net members for last 12 months

● Green line shows the MoM change (%) for active members.

Members gained and lost

● Table that breaks down members by month-year showing:

○ Gained: memberships purchased that month

○ Lost: memberships cancelled or ended that month

○ Net: Gained - Lost

○ Attrition: Lost ÷ Active memberships at the beginning of the month

● Filters: None

Page 6- Teachers

● Teachers

○ Equation: COUNT_DISTINCT(Worker_ID)

○ Filters: None

● Attendees per class (on average)

○ Equation: AVG(Total Attendees)

○ Filters: None

● Utilization (%)

○ Equation: SUM(Attendees)/Sum(Capacity)

○ Filters: None

● Substitution (%)

○ Equation: (COUNT_DISTINCT(IF(Is_Substitute = TRUE, Class_ID, NULL)) / COUNT_DISTINCT(Class_ID))

○ Filters: None

Instructor work history

● A table showing historical data of workers showing their name, first class date, tenure and total class taught and then the following metrics displayed as bar charts:

○ Avg. attendees

○ Avg. Utilization (%)

○ Subbed-in: total numbers of classes where the instructor worked was subbed-in

● Filters: None

Classes that were subbed

● A table showing instructors and a bar chart indicating how many classes they were subbed in the selected time period

Performance as a substitute teacher

● A heatmap showing the following performance metrics per instructor:

○ Classes taught as a non-sub, defined as instructors who were not a substitute

○ Classes taught as a sub, so instructors who were substituted for a class

○ Avg. utilization % as a non-sub, shows average utlization for the instructor for his classes taught as a non-sub

○ Avg. utilization % as a sub, shows average utlization for the instructor for his classes taught as a sub

○ Filters: Exclude teachers who have never been subbed

Page 7- Classes

● Check-ins per day

○ Equation: sum(Attendees)/DATE_DIFF(max(Class_Date), MIN(Class_Date))

○ Filters: None

● Check-ins per class

○ Equation: AVG(Attendees)

○ Filters: None

● Utilization (%)

○ Equation: SUM(Total Attendees)/SUM(Total Capacity)

○ Filters: None

● No. shows per location

○ Equation: SUM(Total No Shows)/COUNT_DISTINCT(Locations)

○ Filters: None

● Graced cancels + graced no shows per day

○ Equation: SUM(Total_Cancellations + Total_No_Shows - Penalty_Cancellations - Penalty_No_Shows) / DATE_DIFF(max(Class_Date), MIN(Class_Date))

○ Filters: None

● Graced no shows (%)

○ Equation: SUM(Total No Shows - Penalty No Shows)/SUM(Total No Shows)

○ Filters: None

● Graced cancels (%)

○ Equation: SUM(Total Cancellations - Penalty Cancellations)/SUM(Total Cancellations)

○ Filters: None

Attendance and utilization trends

● Chart showing quarterly data for the last 3 years

● Grey bars shows utlization (%)

● Green line shows the average attendees per class

Graced no shows and cancellation trends

● Chart showing quarterly data for the last 3 years

● Grey bars shows late cancels and no shows per class

● Orange line shows graced no shows (%)

● Red line shows graced cancels (%)

Schedule performance

● Pivot table with heatmap showing how the following KPIs vary by days and time:

○ Utilization

○ Avg. check-ins per class

Page 8- Data Exports

Pricing options

● Pivot table with heatmap showing how the total orders for each product varies per quarter

● Filter: is either a credit or membership

Sales by product

● Table showing the following metrics as bar for each Product (with its category):

○ Revenue (excl. refunds)

○ Revenue (excl. refunds & taxes)

○ Taxes

○ Refunds (when order status is “refunded” or “partially refunded”)

Sales by customer

● Table chart showing the same metrics as above chart with in-depth customer details alongside.