OLD Statistics in uala-main

The application uala-main provides some statistics reachable from /v1/manager/ that are considered "old" since a migration process started with the intention to move them them in uala-statisticsuala-statistics (but that process never ended). All the statistics in uala-main are Mono Venue (they can be computed only for 1 venue at the time).

Most of the statistics provided by TWPro are still computed in uala-main (146/190 EPs). That's for different reasons:

• Move the statistics requires a long effort from BE and FE
• The statistics are quite complex adn bugged
• The Jobs to sync uala-statistics and uala-main aren't stable

The reports can be seen in the Manager section of TWPro, and they provide a lot of different reports, from really generic ones to very specific ones and they can can all be filtered by:

• date
• treatment typology
• with or without quotations
• computed on Appointments or Line Items

And it's even possible to compare different time periods.

!!!warning Delayed Database As defined in UALA infrastructure as a code all the EPs under /v1/manager/ points to the web-b2b-manager workload that reads a Delayed Postgres Replica. That because the statistics performs a lot of heavy Postgres queries, for that reason they can use read from Delayed replica.

That's not a problem for the statistics that reads data till the day before, but it caused some problems to the authentication since now the delay is 1 hour. !!!

!!! Authentication Due to the fact that the Database used is delayed the authentication uses internally uala-auth. That's because the Bearer Token used is stored directly in the User's table and if that change due to a logout the EPs in /v1/manager/ won't be able to recognize the new User Authentication Token. !!!

!!! Known issues

1. The code adn the formulas used are complex, and there is a lot of code repetition. Some sections has been refactored but not all of them and not enough.
2. Not every EP and not every scenario is tested, that's because there are too many edge cases and too many EPs. (the first step of a refactoring should always be to introduce tests based on the old code)
3. Some queries are really slow and complex and they can get killed by Postgres or if the ruby code is slow the processing time could exceed the 20s so ngnix would return a 500 to the client.
4. The definition on the same entity can have different meaning based on the EP where they are computed, so to have a deep understanding you should always check the code and how stuff are computed. !!!

Statistics Tables and Jobs

In order to make the report EPs faster some statistics data are precomputed and saved in statistics tables:

• statistics_appointment_customers
• statistics_appointment_passages
• statistics_customers_frequencies
• statistics_fiscal_checkouts
• statistics_fiscal_customers
• statistics_fiscal_passages

And those tables are populated by different Jobs that run every night.

• StoreStatisticsPassagesJob computes the statistics data related to a customer going to the venue, it's computed for Appointments and for Line Items and it populates statistics_appointment_passages and statistics_fiscal_passages.
• StoreStatisticsCustomersJob computes the statistics data related to a customer, it's computed for Appointments and for Line Items and it populates statistics_appointment_customers and statistics_fiscal_customers.
• StoreStatisticsFrequenciesJob computes the Customer frequencies on how many times a customer went to a depending by date and treatment typology, the frequency is computed using only the Appointments and they are stored in statistics_customers_frequencies.
• StoreProductBenchmarkJob computes the product's benchmarks used in the Stock Reports
• StoreStockRotationDataJob computes the product's rotation index by country in 1 year, it's a data used in the Stock Reports

!!!warning Old records update It's possible that some field values will change for past Appointments, Line Items or Checkouts due to bugs, wrong formulas, introductions of new fields etc. When that happen the Statistics Tables wouldn't know it because they are computed day to day. To keep the data in sync the statistics table records should be recomputed running Maintenance::RunStatisticsJobsTask !!!

Manager Reports

Due to the fact that there are 146 EPs that performs different queries on different data using Ruby code and Postgres queries it's impossible to describe all the Reports or even to remember what they compute and how. Some descriptions were already present in uala-statistics repository wiki and hopefully they can be helpful to understand some definitions and formulas.

Customers Reports

The Customers Reports show the Clients:

• behaviors
• typology
• what treatments and staff members they ask
• frequency
• debts

Customers Typologies

The Customers are classified in different typologies divided by:

• Frequency:

  • new: given an interval, all customers whose first passage happened during that interval
  • recurrent: given an interval, all customers whose first passage happened before that interval and also have a passage during that interval
  • lost: given an interval, all customers whose last visit + (average_frequency * 1.5) should happen in given interval (but it doesn't)
  • dead: given an interval, all customers whose last visit + (average_frequency * 2.5) should happen in given interval (but it doesn't)
  • recovered: ex lost or dead customer who have an appointment during given interval
  • active: new, recurrent and recovered customers If a customer is dead, he cannot be lost as well. Same is for the other typologies. Only active can occur together with new or recurrent.

  !!!warning Frequencies definitions mismatch The fields fields is_alive, is_lost and is_dead in statistics_fiscal_customers, customer_frequencies, statistics_appointment_customers aren't the client typologies, they are only used to compute the recovered customer's group and they are independent from the time interval.

  At the moment the fields is_alive, is_lost and is_dead aren't even computed because the operation required is too slow. !!!

• Loyalty:

  • new: new customers returning within 90 days (first appointment within 90 days, second appointment in the given interval)
  • recurrent: customers with at least one appointment during given interval and at least another during previous 90 days

• Treatment groups:

  • A clients is in a treatment group if has done at least a treatment in that group. The fallback treatment typology is hairdresser

Customers Reports Definitions

• Loyalty Percentages:

  • new customers percentage:

    (number customers returning within 90 days, first appointment within 90 days, second appointment in the given interval) / number of customers with second appointment in the given interval

  • old customers percentage:

    (number of customers with at least 2 appointments, one in the previous 90 days and another in the given interval) / number of customers with more than 2 appointment, one in the given interval

• Customers Frequency:

  The client's average frequency could be found in different EPs in the customers reports, but it isn't always the same because it could be computed based on different tables ( statistics_appointment_customers, statistics_fiscal_customers ) and even computed at runtime (using the function _customers_average_frequency)

  The general formula used in the Jobs or at runtime to compute the customer's average frequency is:

  ((max_appointment - min_appointment) / (count_appointments - 1)).ceil

• Customers Debts:

  All the debts amount are saved in the checkouts table and when the client pays a part of the debt the payed amount is saved in the field debt_cash_draws. In the table debts is saved if a debt is open or closed but to compute the reports it's required to check also if the debt_cash_draws amounts are equal to debt amount.

Customers Filters

In the Customers Report the venue owner could compute the statistics only on certain customers. They could be filtered by:

• age interval
• sex
• profession
• group (defined by the client's tags table)
• amount spent in the interval with or without a receipt (more or less then X)
• treatment typology performed or not, ever or in the last X days
• specific treatment performed or not, ever or in the last X days
• amount of appointments in the interval is less then X but there is al least 1
• amount of appointments in the interval is more then X
• did't have an appointment in the last X days
• had an appointment in the last X days
• the discounted card used

Those filters are the same used for the Newsletters and they aren't related to other classification used in the reports.

Appointments Reports

The Appointments reports are separated in 2 pages: ==- Appointments Recap in Agenda

It considers three different time slots: today, tomorrow and next 7 days (called this_week). Comparison can be done with appointment performed the same days in the last week or in the last year. So for example if chosen interval is today and it's Wednesday the comparison can be done with last Wednesday or with the Wdnesday of the same week from last year. ==-

==- Appointment Reports inside Company report

• Passages classification:
  • booked: passages with an appointment created before the day of passage (at least the day before)
  • not booked: passages without appointment (this only in case of fiscal data) or with an appointment created the very same day of passage
  • deleted after check-in: These are retrieved through CustomerCartItem, checking if related appointment is null. Created because the user delete an appointment after checked in.
  • appointments with no checkout: appointments with no checkout, appointments in the status checkout without a real checkout (our jobs assign those state to them)
• Where the appointments are booked is defined based on the ApiKeys explained here ==-

Stock Reports

The Stock Report is to provide and estimations and to suggest how the products should be managed (what product order, stop order or sell more). The indexes are inspired by http://www.marchegianionline.net/appro/appro_212.htm

!!!warning The FE sometimes computes some index from the array length or performing other computations.

Sometimes the values are computed on a yearly basis instead of the given interval, for example when the rotation index of the venues is confronted with the rotation index of the country, it's computed in a year. !!!

Definitions

• Products: average number of products in stock given an interval of days
• References: single occurrences of every venue product
• No movements references: References with no transactions during selected interval
• Dead references: References with no transactions during one year before start of selected interval and end of selected interval. Estimated loss of dead products is calculated with corresponding inward transaction expense amount
• Products to be order: Products with stock quantity lesser then minimum stock required
• Venue rotation index: Index stored in VenueRotationIndex and computed every night
• Minimum stock days: Field saved in VenueProduct
• Products in stock in a specific day:

  stock value from VenueProduct model - inner transactions happened after report day + outer transactions happened after report day

• stock value from VenueProduct model:

  The amount of product in stock today, if we wanna know the amount of product in stock in the past we have to subtract the product acquired since that date and add the product sold after that date.

• A product has a correct rotation if:

  product rotation index is > 0.9 * average rotation index (benchmark)

• A product should be used better if:

  rotation < average rotation index (benchmark)

• Product is under stock if:

  stock < suggested quantity

• Product should be ordered now if:

  stock < min quantity

• Suggested quantity:

  (number of products sold per day * min stock)

• Number of Products sold per day:

  - The average of references sold per day if > 0
  - otherwise if it's a product bought to be sold is computed as the average sold items in the last 3 months
  - otherwise if it's a product bought for internal use is computed as the average usage (as sold or as used internally) in the last 3 months

• Rotation duration in an interval of days:

  num days in the interval / rotation index

• Average stock amount:

  SUM(product's stock for each day) / num days

• The products that should be optimized are:

  products to order + products not to order (they are signed as to not use in the last year) + under stock (they should be optimized because there are to few of them)
  

• Average products in stock (in stock-report EP)

  total products / number of references

• Average stock

  (stock_layout + initial_stock_value) / number_of_days)

• venue_product_rotation_index:

  sum outward transactions / average_stock

• Products with hight margin:

  selling_price_cents - buying_price_cents in the highest percentile (> 0.9)

• Products with low margin:

  selling_price_cents - buying_price_cents in the lowest percentile (< 0.5)

• Estimated revenues for a product category:

  AVG( product prices) * quantity of products belonging to that category 

Stock Reports Handling Section

• day, week or month aggregation in the Handling graph: is based on the days in the queried period.
• Product activity: A product is considered more or less active based on the the number of transactions performed on it without considering the quantity involved in the transaction.
• Handling graph's price for sold products: is defined using the Line Items price instead of the price saved in the transactions, because in that way you can consider the discounts. Meanwhile in all the other reports the price for sold products is the catalog price (without the discounts).
• Abnormal movements index: is the number of transaction for each kind considered outliers (if usually the transaction inward are 10 and one is 10.000 and usually the transaction outward are 2 and one is 100.000, those are 2 outliers)

!!!warning In the Handling graph there are 2 curves for each transaction type even when any comparison is performed. Probably that's due to some FE computations. !!!

Inventory Reports

That report is computed watching the warehouse status in a specific day. The last selling price is used to define if the product is bought to be sold or for internal use:

• if the last selling price is 0 the product is considered for internal use
• if the last selling price is greater then 0 the product is considered bought to be sold