# KPI report documentation # 📊 Users Metrics ### 1. **Active Users** - **Definition**: The number of **unique users** who completed at least one **successful rental** in the given time period. **Calculation**: ```sql SELECT b.name AS branch_name, b.id AS branch_id, r.guser_id, DATE_TRUNC('day', r.start_time) AS activity_day, MAX(DATE_TRUNC('day', r.start_time)) AS last_activity_day FROM rentals r LEFT JOIN branches b ON r.branch_id = b.id GROUP BY r.guser_id, activity_day, b.name, b.id ORDER BY activity_day, b.name ``` First, activity of each user is calculated per day, from the rentals tables, which is available in snowflake sharing layer. Next, based on activity table count of users_ids per day, branch, tenant is calculated. **Notes**: - Time dimension used- start time from rentals table. - User activity table is based on the number on the rentals table and takes the latest rental of each user per that day. Daily active users are calculated afterwards based on user_activity table. ### 2. **Number of Rentals** - **Definition**: The **total number of rentals** across all users during the selected time frame. **Calculation**: ```sql SELECT DATE_TRUNC('hour', start_time) AS ride_hour, b.name as branch_name, b.id as branch_id, COUNT(*) as number_of_rentals FROM rentals r LEFT JOIN branches b ON r.branch_id = b.id GROUP BY ride_hour, branch_name, b.id ORDER BY ride_hour, branch_name, branch_id; ``` Data is taken from the rentals table, count of all rows is selected and grouped by date/hour, tenant_id, branch. **Notes**: - Time dimension used- start time from rentals table. - All rentals are taken. ### 3. **Average Rides per Active User** - **Definition**: The **average number of completed rides** per active user. - **Calculation**: The calculation is performed in Qliksense BI tool. It is calculated as sum of all rentals divided by sum of daily active users. If there were no active users, the calculation will return 0. - **Notes**: - This value may be a decimal (e.g., 2.3 rides per user). - Division by zero is handled (e.g., return NULL or 0 when no active users). - This calculation is performed inside the Qliksense data visualisation tool. # 📈 Marketing Metrics ### 4. **New User Acquisition** - **Definition**: The number of users who **registered for the first time** during the selected period. - **Calculation**: ```sql SELECT DATE_TRUNC('hour', g.created_at) AS hour_created, g.home_billable_branch_id, COUNT(*) AS new_users FROM gusers g LEFT JOIN branches b ON g.home_billable_branch_id = b.id WHERE g.home_billable_branch_id IS NOT NULL GROUP BY hour_created, g.home_billable_branch_id ORDER BY hour_created, g.home_billable_branch_id; ``` New rows from table gusers are counted by tenant, date/hour of creation, as well as home_billable_branch. Note, home_billable_branch is different from rental branches on which a rental has happened and is a unique branch to which user is assigned. - **Notes**: - Registration date should be the **first recorded** creation time for the user. - Time dimension used- created_at field from the gusers table. - Only users with present home_billable_branch_id are taken into account. - All user KPIs are grouped by home billable branch, not the rental branch. ### 5. **Conversion Rate** - **Definition**: The **percentage of new users** who complete their **first rental** on the **same day** as registration. - **Calculation**: ```sql SELECT guser_id, DATE_TRUNC('hour', MIN(start_time)) AS first_ride_hour FROM rentals GROUP BY guser_id ``` - First, the first rental time for each user is calculated, using the rentals table and start_time. This calculation is performed in snowflake. - Next, the number of converted users is divided by number of registered users in that day to get the conversion rate. This calculation is performed in Qliksense BI tool. - **Notes**: - Requires accurate linkage between user account creation and their first rental. - All user KPIs are grouped by home billable branch, not the rental branch. ### 6. **Promotion Redemption Rate** - **Definition**: The absolute number of **promotional offers** that were **redeemed** during the time period. - **Calculation**: ```sql select g.home_billable_branch_id as branch_id, DATE_TRUNC('day', rv.created_at) as created_date, count(distinct rv.id) as number_of_vouchers_used from rentals_vouchers rv left join vouchers v on rv.voucher_id = v.id left join gusers g on g.id = rv.guser_id where g.id is not null group by g.home_billable_branch_id, created_date; ``` Vouchers that were used together with rentals are counted, grouped by creation date, home billable branch of user and branch. Only rental vouchers attributed to a certain user are taken into account. - **Notes**: - All user KPIs are grouped by home billable branch, not the rental branch. - Time dimension - rentals_vouchers table, created_at time, the time of rental where a voucher was used. # **🚲 Fleet Metrics** ### 7. **Vehicle Utilisation Rate** - **Definition**: The **percentage of time** that **non-retired vehicles** were **actively rented** during the selected time frame. - **Calculation**: ```sql SELECT v.id AS vehicle_id, vc.name AS vehicle_category, b.id AS branch_id, DATEADD(day, gen.offset, DATE(r.start_time)) AS rental_date, CASE --Single day rental WHEN DATE(r.start_time) = DATE(r.end_time) THEN DATEDIFF(second, r.start_time, r.end_time) / 3600.0 --First day of the rental (multiple day rental) WHEN DATEADD(day, gen.offset, DATE(r.start_time)) = DATE(r.start_time) THEN DATEDIFF(second, r.start_time, LEAST(r.end_time, DATEADD(day, 1, DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', r.start_time))))) / 3600.0 -- Last day of the rental (multiple day rental) WHEN DATEADD(day, gen.offset, DATE(r.start_time)) = DATE(r.end_time) THEN DATEDIFF(second, DATE_TRUNC('day', CONVERT_TIMEZONE('UTC', r.end_time)), r.end_time) / 3600.0 -- Middle day of the rental (multiple day rental) ELSE 24.0 END AS booked_hours, DATE_TRUNC('month', DATEADD(day, gen.offset, DATE(r.start_time))) AS rental_month, CASE WHEN DAYOFWEEK(DATEADD(day, gen.offset, DATE(r.start_time))) IN (1, 7) THEN 'Weekend' ELSE 'Weekday' END AS day_type FROM rentals r JOIN vehicles v ON r.vehicle_id = v.id JOIN vehicle_categories vc ON v.category_id = vc.id JOIN branches b ON r.branch_id = b.id JOIN LATERAL ( SELECT seq4() AS offset FROM TABLE(GENERATOR(ROWCOUNT => 31)) -- Assumes max rental duration of 31 days ) gen WHERE DATEADD(day, gen.offset, DATE(r.start_time)) <= DATE(r.end_time) AND r.type IN (0, 1) AND r.start_time >= DATE_TRUNC('YEAR', DATEADD(YEAR, -2, CURRENT_DATE())) order by rental_date desc; ``` - In order to calculate rental durations a case when logic is used. If it was a single day rental, the difference between start_time and end_time is calculated. Same logic is used for start_date and end_date of a multi-day rental. Otherwise, if it was a middle day for a rental, 24 hours of utilisation will be used. - Weekdays are additionally categorised into weekday and weekend. - Later these rental duration are cross joined with each non-retired vehicle (see definition below) for each day and utilisation is calculated as percentage of time during the day when a specific vehicle was rented. - The final KPI is calculated as sum of booked hours divided by the sum of total possible hours. This calculation is performed in Qliksense - **Notes**: - **Rental duration** includes only the actual ride time (not idle or reserved time). - This KPI is calculated for the last 365 days. - Must exclude retired, defleeted, disassociated vehicles. - Assumes maximum rental duration of 31 days ### 8. **Average Rental Duration** - **Definition**: The **average length of time** (e.g., in minutes) for each completed rental. The length of a rental is calculated as difference between the end_time and start_time of a rental. - **Calculation**: ```sql SELECT DATE_TRUNC('hour', start_time) AS trip_hour, branch_id, COUNT(*) AS number_of_trips_for_duration, SUM(DATEDIFF('minute', start_time, end_time)) AS total_trip_minutes FROM rentals WHERE end_time IS NOT NULL AND DATEDIFF('minute', start_time, end_time) BETWEEN 0 AND 1440 GROUP BY 1, 2 ``` - First, the sum of difference is calculated between the start_time and end_time of a rental, grouped by tenant, branch, hour/date. - **Notes**: - Only rides shorter than 1440 minutes of 24 hours are taken, because this KPI calculated average rental duration per day. Rentals with duration of more than 24 hours are considered as outliers in this case. - The final calculation is performed in Qliksense, where the sum of total trip time is divided by the sum of number of rentals. - Only rides with available end_time are taken. ### 9. **Rental Revenue per Vehicle (Non-Retired)** - **Definition**: The **total gross rental revenue** divided by the number of **non-retired vehicles** in the fleet during the time frame. - **Calculation**: ```sql SELECT DATE_TRUNC('hour', r.start_time) AS revenue_hour, b.name AS branch_name, b.id AS branch_id, COALESCE(SUM(TO_NUMBER(price_v3:"totalGross")), 0) AS revenue_gross, COALESCE(SUM(TO_NUMBER(price_v3:"totalNet")), 0) AS revenue_net FROM rentals r LEFT JOIN branches b ON b.id = r.branch_id WHERE r.state = 3 -- state is "Ended" AND r.type <> 2 -- type is not "service" GROUP BY revenue_hour, branch_name, b.id ORDER BY revenue_hour DESC, branch_name, b.id ``` - First, rental revenue is calculated using column price_v3 in table rentals, subfields totalGross and totalNet are used. This revenue is summed by branch, hour. - Next, vehicle periods are calculated. What is taken into account: date when a module was assigned to a vehicle, closest date when a vehicle was either unassigned from a module or retired. Number of defleeted vehicles during that day is also taken into account. If a vehicle was not retired during a day, then it is taken into account. Non-retired means that a vehicle was not: - Defleeted - Retired - Disassociated from a module. Next, vehicle counts are historized (calculated) for each date. This means that only vehicles where the association with a module was before the calculation date AND there was no disassociation/retirement will be taken into account. The final calculation is performed dynamically in Qliksense as sum of revenue_gross divided by the number of vehicles. If there were no vehicles, the calculation will return 0. - **Notes**: - Rental revenue is taken into account. This is different from booking revenue, invoiced revenue or paid revenue. - Rentals with type 2 (service) are excluded. - Only rentals with state ended are taken.