.. meta:: :description: Analytics Dashboard in Plytech manager UI: configurable filters and aggregated views of merchant, project and gate processing performance. Analytics Dashboard ################### .. role:: ex .. role:: code Overview ======== .. _analytics_dashboard_general_filters: General Filters =============== :code:`Starting with` - starting date for reports :code:`Show by` - period starting from the :code:`Starting with` date during which the data will be processed :code:`Display currency` - currency used in reports :code:`Tx type` - transaction type used in reports. For example: sale, transfer, etc. :code:`Status` - transaction type used in reports Criteria ======== Dashboard Panels ================ Customer Lifetime Value (Periodic Percent Active Users) ------------------------------------------------------- Overview ^^^^^^^^ Customer Lifetime Value (CLV) is a per-customer metric that represents the total monetary value generated by a customer over a :ref:`selected time period` .. _analytics_dashboard_clv_filters: Filters ^^^^^^^ :code:`Grouping type` - specifies how metrics are aggregated and displayed in the reports. All calculations are performed independently for each group under the same filters and time range. Possible values: Manager, Merchant, Endpoint :code:`Total cost of marketing campaign` - defines the total marketing spend for the selected filters and period Customer Lifetime Value Graph ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Customer Lifetime Value (CLV) is a per-customer metric that represents the total monetary value generated by a customer over a :ref:`selected time period` .. Link: https://code.clubber.me/git/analytics-dashboard/analytics-dashboard-sql/-/blob/main/request/clv/clv.sql?ref_type=heads .. image:: ../_static/images/tools/clv_historical_all.png :width: 750 :align: center :alt: CLV historical all .. image:: ../_static/images/tools/clv_predictive_all.png :width: 750 :align: center :alt: CLV predictive all .. _analytics_dashboard_clv_clv: Customer Lifetime Value *********************** Customer Lifetime Value (CLV) is the average total value of transactions generated by a payer over the selected period, expressed in the :ref:`selected reporting currency`. Calculations: 1. For each payer, CLV is calculated as the sum of all :ref:`selected transactions`, converted to the :ref:`selected in filters currency`: .. math:: Payer\_CLV = \sum_{i = 1}^{N}(amount_i \cdot convert\_rate) where :math:`N` is the number of selected transactions for the payer 2. For each payer determines the month of their first selected transaction 3. Each payer CLV groups by first month of selected transactions 4. For each month :math:`p`, the CLV value is the rounded average of :math:`Payer\_CLV` across all payers whose first transaction month equals :math:`p`: .. math:: CLV(p) = \operatorname{round}(\frac{1}{N_p} \cdot \sum_{i = 1}^{N}Payer\_CLV(p)_i, 4) where :math:`N_p` is the number of all Payer CLV, where the first month's value is the same as the selected month Average CLV per month ********************* Average CLV per Month represents the average transaction value generated per payer per active month within the selected period, expressed in the :ref:`selected reporting currency`. Calculations: 1. For each payer, CLV is calculated as the sum of all :ref:`selected transactions`, converted to the :ref:`selected in filters currency`: .. math:: Payer\_CLV = \sum_{i = 1}^{N}amount \cdot convert\_rate where :math:`N` is the number of selected transactions for the payer 2. For each payer determines the month of their first and last selected transaction 3. Each payer CLV groups by first month of selected transactions 4. For each month :math:`p`, the average CLV per month value calculates as rounded average of :math:`Payer\_CLV` across all payers whose first transaction month equals :math:`p` divided by the difference between the first and last month: .. math:: CLV\_per\_month(p) = \operatorname{round}(\frac{1}{N_p} \cdot \sum_{i = 1}^{N_p}\frac{Payer\_CLV(p)_i}{last\_month_i-first\_month_i + 1}, 4) where :math:`N_p` is the number of all Payer CLV, where the first month's value is the same as the selected month Clients count ************* Client count is the number of all unique payers for the period defined in :ref:`general filters` .. _analytics_dashboard_clv_cac: Customer Acquisition Cost ************************* Customer Acquisition Cost (CAC) is the average marketing spend required to acquire one payer within the selected period. Calculations: .. math:: CAC = \frac{total\_marketing\_cost}{total\_payers} where :math:`total\_marketing\_cost` is the defined in :ref:`CLV filters` total cost of marketing campaign, and :math:`total\_payers` is the number of all unique payers for the period defined in :ref:`general filters` .. _analytics_dashboard_clv_average_lifetime: Average Lifetime **************** Average Lifetime represents the average number of months a payer remains active, calculated for payers that match the :ref:`selected grouping criteria` (e.g., merchant, manager, or endpoint). A payer is considered active from the month of their first selected transaction through the month of their last selected transaction Calculations: 1. For each payer determines the month of their first and last selected transaction 2. Each payer groups by first month of selected transactions 3. For a payer :math:`i` in cohort month :math:`p` (where :math:`first\_month_i = p`), lifetime is calculated as: .. math:: LT_{p, i} = last\_month_{p, i} - first\_month_{p, i} + 1 4. Average Lifetime for specific month (:math:`p`) calculates as rounded average lifetimes across all payers in that cohort: .. math:: AVG\_LT(p) = \operatorname{round}(\frac{1}{N_p} \cdot \sum_{i = 1}^{N_p}LT_{p, i}, 4) where :math:`N_p` is the number of payers whose first month's value is the same as the selected month Historical Lifetime ******************* Historical Lifetime is the :ref:`Average Lifetime` for all available groups (by grouping type) defined in :ref:`CLV filters` .. _analytics_dashboard_clv_predictive_lifetime: Predictive Lifetime ******************* Predictive Lifetime estimates the expected number of months a client will remain active based on the observed month-over-month retention rate Calculations: 1. Total active clients in current and previous months is number of clients which was active in previous month and still active in current month .. math:: total\_active\_in\_current\_and\_prev\_months(p) = \sum_{i = 1}^{N_p}active\_in\_current\_and\_previous\_month_{p, i} where :math:`N_p` is the number of clients considered for period :math:`p` 2. Retention Rate is the share of clients from the previous month who remain active in the current month .. math:: RR(p) = \begin{cases} \frac{total\_active\_in\_current\_and\_prev\_months_p}{previous\_month\_clients_p}, & previous\_month\_clients_p \neq 0\\ 0, & previous\_month\_clients_p = 0 \end{cases} where :math:`previous\_month\_clients_p` is the total number of active clients in month :math:`p-1` for the selected cohort and filters. Predictive Lifetime for specific period calculates as follows: .. math:: Predictive\_LT(p) = \begin{cases} \frac{1}{1 - RR_p}, & RR_p \neq 1\\ NULL, & RR_p = 1 \end{cases} Predictive CLV ************** Predictive Lifetime estimates the expected average Customer Lifetime Value for a client in period :math:`p` based on the :ref:`Predictive Lifetime`, :ref:`Average Order Value` and average transactions frequency Calculations: 1. Computes the average number or transactions per period: .. math:: T = \frac{N_t}{N_p} where :math:`N_t` is the total number of selected transactions, and :math:`N_p` is number of periods 2. For period :math:`p`, Predictive CLV is calculated as: .. math:: Predictive\_CLV(p) = \begin{cases} \frac{T \cdot AOV_p \cdot Predictive\_LT_p}{total\_clients\_in\_previous\_month_p}, & total\_clients\_in\_previous\_month_p \neq 0\\ 0, & total\_clients\_in\_previous\_month_p = 0 \end{cases} where :math:`total\_clients\_in\_previous\_month_p` is number of unique clients in month :math:`p-1`, :math:`AOV_p` is :ref:`average order value` for specified period .. _analytics_dashboard_clv_average_order_value: Average Order Value Graph ^^^^^^^^^^^^^^^^^^^^^^^^^ .. Link: https://code.clubber.me/git/analytics-dashboard/analytics-dashboard-sql/-/blob/main/request/clv/clv_aov.sql?ref_type=heads .. image:: ../_static/images/tools/average_order_value.png :width: 750 :align: center :alt: Average Order Value Graph Average Order Value (AOV) is the average transaction amount for a specific period, expressed in the :ref:`selected reporting currency`. Calculations: Average order value for specific period :math:`p` calculates as rounded average of all transactions, converted to the :ref:`selected in filters currency`, for this period: .. math:: AOV(p) = \operatorname{round}(\frac{1}{N_p} \cdot \sum_{i=1}^{N_p}amount_i \cdot convert\_rate, 2) where :math:`N_p` - is the number of all transactions for the period :math:`p` Average Time to Second Order Graph ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. Link: https://code.clubber.me/git/analytics-dashboard/analytics-dashboard-sql/-/blob/main/request/clv/clv_atso.sql?ref_type=heads Average time to second order - average time between first and second orders .. image:: ../_static/images/tools/average_time_to_second_order_graph.png :width: 750 :align: center :alt: Average Time to Second Order Graph Calculations: Finds out transactions related to each payer (differentiated by card PAN) for the specified period of time: 1) :math:`pan_1` - :math:`T_1, T_2, T_3...T_i` 2) :math:`pan_2` - :math:`T_1, T_2, T_3...T_i` 3) ... where :math:`T_1, T_2, T_3...T_i` - date and time when the payer with :math:`pan_1` made payment For each payer calculates the number of days between first (:math:`T_1`) and second (:math:`T_2`) transactions: .. math:: date\_diff_{i} = \operatorname{days}(T_2 - T_1) ATSO for specific period calculates as rounded average of date diff for all payers for this period: .. math:: ATSO(p) = \operatorname{round}(\frac{1}{N_p} \cdot \sum_{i=1}^{N_p}date\_diff_{i}, 2) where :math:`N_p` - is the number of all collected date_diffs for period (:math:`p`) Average Time to Next Order Graph ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. Link: https://code.clubber.me/git/analytics-dashboard/analytics-dashboard-sql/-/blob/main/request/clv/clv_atno.sql?ref_type=heads Average time to next order - average time between orders .. image:: ../_static/images/tools/average_time_to_next_order.png :width: 750 :align: center :alt: Average Time to Next Order Graph Calculations: Finds out transactions related to each payer (differentiated by card PAN) for the specified period of time: 1) :math:`pan_1` - :math:`T_1, T_2, T_3...T_i` 2) :math:`pan_2` - :math:`T_1, T_2, T_3...T_i` 3) ... where :math:`T_1, T_2, T_3...T_i` - date and time when the payer with :math:`pan_1` made payment For each :math:`T_k` finds a previous transaction made by the same payer and calculates the number of days between them: .. math:: date\_diff_{pan_i, k} = \operatorname{days}(T_k - T_{k-1}) ATNO for specific period calculates as rounded average of all date diffs for all payers for this period: .. math:: ATNO(p) = \operatorname{round}(\frac{1}{N_p} \cdot \sum_{i=1}^{N_p}\frac{1}{N_{pan_i}} \cdot \sum_{k=1}^{N_{pan_i}}date\_diff_{pan_i, k},2) where :math:`N_p` - is the number of all payers for period (:math:`p`), and :math:`N_{pan_i}` - is the number of all date diffs for specific payer .. _analytics_dashboard_clv_roi: Return on Investment Bar Chart ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Return on Investment (ROI) expresses the relative return generated per acquired payer, measured against :ref:`Customer Acquisition Cost (CAC)`. Values are reported as percentages Calculations: For each period :math:`p`, ROI is computed from the period CLV value :math:`CLV_p` and the corresponding CAC: .. math:: ROI(p) = \begin{cases} \frac{CLV_p - CAC}{CAC} \cdot 100, & CAC \neq 0\\ NULL, & CAC = 0 \end{cases} Customer Segmentation CLV Based Segment Chart ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. Link: https://code.clubber.me/git/analytics-dashboard/analytics-dashboard-sql/-/blob/main/request/clv/clv_segmentation.sql?ref_type=heads .. image:: ../_static/images/tools/customer_segmentation_clv_based_1.png :width: 750 :align: center :alt: Customer Segmentation CLV Based 1 .. image:: ../_static/images/tools/customer_segmentation_clv_based_2.png :width: 750 :align: center :alt: Customer Segmentation CLV Based 2 Shows the number of clients in each CLV bucket, providing a distribution of customers by their :ref:`Customer Lifetime Value (CLV)` CLV bucket is a proportional interval of the CLV range used for segmentation. The bucket boundaries are derived from the robust CLV range between the 2nd and 98th percentiles (:math:`lmin` and :math:`lmax`) and split into :math:`bucket\_count` equal-width intervals (with a minimum bucket width of 1). Each client is assigned to a bucket based on their CLV value 1. For each payer calculates CLV as sum of all selected transactions: .. math:: Payer\_CLV = \sum_{i = 1}^{N}amount_i \cdot convert\_rate where :math:`N` - number of all payer transactions 2. Computes bucket bounds (robust range) from CLV distribution: :math:`lmin` = 2nd percentile (0.02 quantile) of all CLV values :math:`lmax` = 98th percentile (0.98 quantile) of all CLV values 3. Interval calculates as difference between :math:`lmax` and :math:`lmin` divided by :math:`bucket\_count`. If obtained value less than 1, interval will be 1: .. math:: interval = \operatorname{max}(\frac{lmax - lmin}{bucket\_count}, 1) 4. For each bucket defines its :math:`interval\_start` and :math:`interval\_end`: .. math:: interval\_start_i = (i - 1) \cdot interval + lmin .. math:: interval\_end_i = \begin{cases} i \cdot interval + lmin, & i \neq bucket\_count\\ lmax, & i = bucket\_count \end{cases} where :math:`i` - bucket number 5. Each customer is assigned to a corresponding bucket based on their CLV, after which the number of customers in the ranges of each bucket is calculated. 6. Percent clients - is the ratio of customers in the current segment to the total number of customers .. math:: Percent\_Clients_i = \frac{number\_of\_clients\_in\_segment_i}{total\_clients} where :math:`i` - bucket number Top 10 Table ^^^^^^^^^^^^ Top 10 entities within the selected grouping type by its :ref:`CLV` Table shows entity's name, :ref:`ROI` and CLV growth .. math:: growth = (\frac{CLV_{cur}}{CLV_{prev}} - 1 ) \cdot 100 where :math:`CLV_{cur}` - CLV for current month, and :math:`CLV_{prev}` - CLV for previous month N-month (Retention Curve) ---------------------------------------------- .. TODO: replace with N-Month??? Overview ^^^^^^^^ N-month retention shows the percentage of customers who made at least one payment in the N-th calendar month after their first payment month .. admonition:: Example If second-month retention is 50%, it means that 50% of customers whose first payment occurred in the first month made at least one payment in the second month :code:`Active clients` — customers from the first payment month who made at least one payment in month :math:`N`. All customers in the first payment month are considered active (by definition) A customer from the first payment month who made payments in earlier months but did not make a payment in month :math:`N` is a :code:`Dormant client` A :code:`Dormant client` who makes a payment in month :math:`N` is a :code:`Reactivated client`. :code:`New clients` — customers who made at least one payment in month :math:`N` but did not make any payment in the first payment month. :code:`Total clients` — the number of unique payers within the period defined in :ref:`general filters`. :code:`First month` is defined by the :code:`Starting with` filter in :ref:`general filters`. Filters ^^^^^^^ :code:`Grouping type` - specifies how metrics are aggregated and displayed in the reports. All calculations are performed independently for each group under the same filters and time range. Possible values: Manager, Merchant, Endpoint :code:`Method` - method of indicators calculation: * Periodic - client considered as dormant in the first month if there were no payments. * Retrospective - client considered as dormant after :math:`churn_period` months during which there have been no payments :code:`Churn period` - filter of retrospective analysis. The number of months that must pass before a client is considered as :code:`Dormant` N-month Retention Graph ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Periodic ******** Periodic N-month retention shows the percentage of customers who made at least one payment in the N-th calendar month after their payment in first month Calculations: 1. Selects all clients who made at least one payment within the chosen reporting period 2. For each client, computes :math:`min\_period` — the month of the client’s first payment within the selected period. If :math:`min\_period` equals the cohort start month, mark the client with the :code:`first month purchase` flag .. math:: client\_fpd\_flag_i = \begin{cases} true, & min\_period = cohort\_period\_start\\ false, & min\_period \neq cohort\_period\_start \end{cases} 3. Each client who made purchase in specific month :math:`p` marks with :code:`retention` flag for this month 4. For month :math:`p`, a client is considered :code:`Active` if: * client has :code:`first month purchase` flag * client has :code:`retention` flag for month :math:`p` * if current month is not the first month of cohort period (:math:`p > cohort\_start\_period`), client made at least one transaction in previous month :math:`p-1` 5. For month :math:`p`, a client is considered :code:`Reactivated` if: * client has :code:`first month purchase` flag * client has :code:`retention` flag * if current month is not the first month of cohort period (:math:`p > cohort\_start\_period`), client didn't make transactions in previous month :math:`p-1` 6. Retention for period calculates as follows: .. math:: Ret_p = \frac{active\_clients_p + reactivated\_clients_p}{total\_clients\_in\_the\_first\_month} Retrospective ************* Retrospective N-month retention shows the percentage of customers who made at least one payment in the N-th calendar month or in the :math:`N-th - churn\_period\_window` month after their payment in first month Calculations: 1. Selects all clients who made at least one payment within the chosen reporting period 2. For each client, computes :math:`min\_period` — the month of the client’s first payment within the selected period. If :math:`min\_period` equals the cohort start month, mark the client with the :code:`first month purchase` flag .. math:: client\_fpd\_flag_i = \begin{cases} true, & min\_period = cohort\_period\_start\\ false, & min\_period \neq cohort\_period\_start \end{cases} 3. Each client who made purchase in current :math:`p` month or :math:`p - churn\_period\_window` months marks with :code:`retention` flag for this month 4. For month :math:`p`, a client is considered :code:`Active` if: * client has :code:`first month purchase` flag * client has :code:`retention` flag for month :math:`p` * if current month is not the first month of cohort period (:math:`p > cohort\_start\_period`), client made at least one transaction in previous month :math:`p-1` 5. For month :math:`p`, a client is considered :code:`Reactivated` if: * client has :code:`first month purchase` flag * client has :code:`retention` flag * if current month is not the first month of cohort period (:math:`p > cohort\_start\_period`), client didn't make transactions in previous month :math:`p-1` 6. Retention for period calculates as follows: .. math:: Ret_p = \frac{active\_clients_p + reactivated\_clients_p}{total\_clients\_in\_the\_first\_month} .. _analytics_dashboard_n_day_users: Users Waterfall Chart ^^^^^^^^^^^^^^^^^^^^^ Chart shows number of clients per category in selected month :math:`p`: :code:`New`, :code:`Active`, :code:`Reactivated`, :code:`Dormant` Percent on chart is share of clients in specific category in selected month relative to the total number of clients for the whole period Quick Ratio Graph ^^^^^^^^^^^^^^^^^ Quick Ratio measures growth relative to churn risk. It is defined as the ratio of :code:`New` and :code:`Reactivated` clients to :code:`Dormant` clients for month :math:`p`. Calculations: .. math:: QR_p = \begin{cases} \frac{new\_clients_p + reactivated\_clients_p}{dormant\_clients_p}, & dormant\_clients_p \neq 0\\ 0, & dormant\_clients_p = 0 \end{cases} Average Revenue per User Graph ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Average Revenue per User (ARPU) Graph shows average revenue per client by category (:code:`New`, :code:`Active`, :code:`Reactivated`) Calculations: For each category :math:`c` (:code:`New`, :code:`Active`, :code:`Reactivated`): 1. Counts number of unique clients for specific month :math:`p` 2. Counts amount of all transactions for specific month :math:`p` 3. Average Revenue per User for specific month (:math:`p`) for specific category (:math:`c`) calculates as follows: .. math:: ARPU_{p, c} = \frac{transaction\_amount_{p, c}}{clients\_count_{p, c}} Total amount - amount of all transactions for specific month .. note:: Some clients may not fit into any category. For example :code:`New` clients from previous month won't be in any category in current month .. math:: Total\_amount_p \neq amount\_new_p + amount\_active_p + amount\_reactivated_p Repeat Customer Rate Graph ^^^^^^^^^^^^^^^^^^^^^^^^^^ Repeat customer rate (RCR) - is the share of customers who made two or more transactions during the selected period :math:`p`. Calculations: 1. Counts number of unique customers who made 2 or more transactions for specific period :math:`p` 2. Counts total number of unique customers for specific period :math:`p` 3. Repeat customer rate calculates as follows: .. math:: RCR_p = \frac{repeat\_clients_p}{total\_clients_p} Repeat Customers Rate Count-to-Amount Ratio Bar ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Repeat Customers Rate Count-to-Amount Ratio Bar shows correlation of single-purchase customers and their purchases and multi-purchase customers and their purchases for whole selected period of time The Count-to-Amount Ratio bar chart compares single-purchase and multi-purchase customers across the entire selected period, showing both: * the share of customers in each group * the share of transaction amount generated by each group. :code:`Single-purchase customers` - customers with exactly one transaction in the selected period. :code:`Multi-purchase customers` - customers with two or more transactions in the selected period. Calculations: 1. For each group (single- and multi- purchase customers) counts: * number of unique customers * total transaction amount 2. Counts total number of customers: .. math:: total\_number\_of\_customers = number\_of\_single\_purchase\_customers + number\_of\_multi\_purchase\_customers 3. Ratio customers count calculates as follows: .. math:: RCC_{single} = \frac{number\_of\_single\_purchase\_customers}{total\_number\_of\_customers} .. math:: RCC_{multi} = \frac{number\_of\_multi\_purchase\_customers}{total\_number\_of\_customers} 4. Ratio spent amount calculates as follows: .. math:: RSA_{single} = \frac{amount\_of\_single\_purchase\_customers\_transactions}{total\_transaction\_amount} .. math:: RSA_{multi} = \frac{amount\_of\_multi\_purchase\_customers\_transactions}{total\_transaction\_amount} Top 10 Table ^^^^^^^^^^^^ Top 10 entities within the selected grouping type by its :ref:`Active percent` Table shows entity's name, :ref:`Active percent` and Active clients growth .. math:: growth = (\frac{Active\_percent_{cur}}{Active\_percent_{prev}} - 1 ) \cdot 100 where :math:`Active\_percent_{cur}` - percent of active clients in current month, and :math:`Active\_percent_{prev}` - percent of active clients in previous month COHORT (For the Period) ----------------------- Overview ^^^^^^^^ Filters ^^^^^^^ Cohort Table ^^^^^^^^^^^^ Templates ========= .. Доработки .. .. CLV: .. 1. Добавить знак % к ROI .. 2. Поправить график Customer Segmentation CLV based. Ведра должны быть одинакового размера .. .. N-DAY: .. 1. Изменить формулу подсчета retention для retrospective (обсудили с Кириллом) .. 2. Добавить отображение fpd_active на UI как active customers для графиков N-day retention и Users .. 3. Поправить отображение growth на UI в Top10 by .. .. COHORT