Portfolio Dashboard
- Sean De Souza
Consolidated version: https://app.powerbi.com/groups/ffafc884-3e5b-4097-8f2c-47ab8c25dae3/reports/2e32eec5-be11-4a1c-b281-4e7fd639cc5b/ReportSection?experience=power-bi
Glossary:
AKA = Also known as
Metric = The number that you see
Dimension = The category by which you filter or slice (on axis or legend) the metric by
Column = When the data is just being shown in a data table as an attribute and is therefore neither a metric or dimension
Other Notes:
We haven’t included when a metric is visible through a tooltip for now
There are many cross-references to things in other rows of the table (particularly when capitalized) so please search for the name of something in the rest of the document if not clear what is being described somewhere. You may find it has it’s own dedicated row.
We have tried to organise the table so that any metric referenced in the formula of a metric should have it’s own row earlier on in the table (and not further down)
Name | Item Type | Description | Pages | Linked Metric/Dimension | Source Tables | Comments | |
|---|---|---|---|---|---|---|---|
| 1 | All Accounts | Metric | The count of accounts which have been installed. We do not include the Pre-portfolio where an install hasn’t happened yet. See the Portfolio Category dimension below for the breakdown of accounts. Note that Account is different from Customer. I as a customer could have multiple accounts if I have multiple metered devices (e.g. a BPower50 and a Nuovopay smartphone). Could also have 2 of the same type of metered device -one for my office and one for home. Though 97% of customers have just one account. | Active Portfolio | Region, Current ARPU Range, TV Customer, Enable Status, Subsidy, CU Type, ESF Only Customers, Activity Month, Portfolio Category |
|
|
| 2 | Contracted Portfolio Size | Metric | The same as All Accounts but filtered to just Contracted Portfolio. This means accounts that have an active contract. There are also variants of this for the Churned and Paid Portfolio. See the Portfolio Category dimension for wider context. | Overview, Active Portfolio | Region, Current ARPU Range, TV Customer, Enable Status, Subsidy, CU Type, ESF Only Customers, Activity Month, Portfolio at Risk |
|
|
| 3 | Active Portfolio Size | Metric | The same as Contracted Portfolio but further filtered to exclude the Defaulted accounts. Accounts default after a certain number of consecutive days expired - which varies between Opcos. See the Portfolio Category dimension for wider context. | Active Portfolio | Region, Current ARPU Range, TV Customer, Enable Status, Subsidy, CU Type, ESF Only Customers, Activity Month |
|
|
| 4 | Defaulters | Metric | The count of the Contracted Portfolio with the Portfolio Category of Defaulted. Aka the delta between Contracted and Active Portfolio in a given month | Active Portfolio |
|
| Mostly an intermediate metric for calculating other ones |
| 5 | % Portfolio in Default | Metric | The count of Defaulters divided by the Contracted Portfolio Size | Overview, Active Portfolio, By Region, Portfolio Outflow |
|
|
|
| 6 | Delta in Defaulters | Metric | This is the difference in count of defaulters between end of previous month and the current reporting month | Active Portfolio |
|
| Mostly an intermediate metric for calculating other ones |
| 7 | Repo (count) | Metric | The number of accounts that got repossessed in a particular month | Active Portfolio, Portfolio Outflow |
|
| Mostly an intermediate metric for calculating other ones |
| 8 | Write Off (count) | Metric | The number of accounts that got written off in a particular month | Active Portfolio, Portfolio Outflow |
|
| Mostly an intermediate metric for calculating other ones |
| 9 | Default Rate | Metric | This define the rate at which accounts in the Active Portfolio are moving into the Defaulted Portfolio Category. We take the Delta in Defaulters and add the Repo and Write Off counts for that month. Then we divide that by the Active Portfolio at end of previous month. We only include the active portfolio in the denominator because those are the only ones that can move into default. We add the reposessions because otherwise a lot of the delta in defaulters would just be people who had been repossessed. We only want to include those that returned to active so that we don't make the default rate look better by reposessing more customers. See Portfolio Category dimension for wider context. | Overview, Active Portfolio, Portfolio Outflow | Region, Current ARPU Range, TV Customer, Enable Status, Subsidy, CU Type, ESF Only Customers, Activity Month |
|
|
| 10 | Churn Rate | Metric | This defines the rate at which accounts in the Contracted Portfolio are moving into the Churned Portfolio. See Portfolio Category dimension for wider context. This is the sum of Repo (count) and Write Off (count) then divided by the Contracted Portfolio Size for a given month. | Overview, Active Portfolio, By Region, Portfolio Outflow |
|
|
|
| 11 | Churn Rate (90-days) | Metric | The same as Churn rate but calculated just for current moment and shows the last 90 days only. This means it is the count of repos and write offs which happened in the last 90 days and then divided by the Portfolio size 90 days ago. In practice we use the snapshot of Portfolio size at the last month end closest to 90 days ago. | Overview |
|
|
|
| 12 | Write Off Rate | Metric | Write Off count divided by Contracted Portfolio size in a particular month |
|
|
|
|
| 13 | Repo Rate | Metric | Repo count divided by Contracted Portfolio size in a particular month |
|
|
|
|
| 14 | Recovery Rate | Metric | The number of repossessions divided by the total number of churn. It is not handling for the fact that some repossessions may have been partial ones (and some may even be actually all lost products but it just wasn’t marked by the user as a write off). |
|
|
|
|
| 15 | Completions (count) | Metric | The number of accounts that moved from Contracted Portfolio to Finished state. This means any accounts who had their last contract move from active to finished in that month (excluding ones who got opted out). See Portfolio Category dimension for more context. | Active Portfolio, Portfolio Outflow |
|
|
|
| 16 | Opt Outs (count) | Metric | The same as Completions count but only accounts when their last contract moved from active to finished via the Opt Out process (which is where you get a bonus for the remaining amount so it appears like you paid off the rest so the device unlocks but actually the contract is getting cancelled (having the remaining amoutn written off). See Portfolio Category dimension for more context. | Active Portfolio, Portfolio Outflow |
|
| Aka “# Opted Outs” |
| 17 | Uncompletions (count) | Metric | The number of accounts that moved from Paid category back into Contracted Portfolio. This will happen when they do a new sale order. | Active Portfolio, Portfolio Outflow |
|
|
|
| 18 | Completion Rate | Metric | Count of Completions divided by Contracted Portfolio size for the month | Active Portfolio, Portfolio Outflow |
|
|
|
| 19 | Opt Out Rate | Metric | Count of Opt Outs divided by Contracted Portfolio size for the month | Active Portfolio, Portfolio Outflow |
|
| Aka “Opted Outs” |
| 20 | Uncompletions Rate | Metric | Count of Uncompletions divided by Contracted Portfolio size for the month | Active Portfolio, Portfolio Outflow |
|
|
|
| 21 | Net Completions (count) | Metric | Count of Completions plus Opt Outs minus Uncompletions | Active Portfolio |
|
|
|
| 22 | Net Completion Rate | Metric | Net Completions count divided by the Contracted Portfolio size for the month | Overview, Active Portfolio, Portfolio Outflow |
|
|
|
| 23 | Net Completion Rate (30-days) | Metric | The same as Net Completion Rate but only including in the numerator actions that happened over the last 30 days instead of a particular month. Similar to how Churn Rate (90 days) works. | Overview |
|
|
|
| 24 | % DP only Defaulters | Metric |
| Defaulters |
|
|
|
| 25 | PAR-120 Rate | Metric |
|
|
|
|
|
| 26 | Notifications (count) | Metric |
|
|
|
|
|
| 27 | Cohort Size | Metric | Total number of accounts that were installed in that installation month. This number (for a particular cohort row) would be the same as if you filtered on the Overview page for particular installation month and then looked at the overall Total on the bottom graph excluding Upfront (All Accounts Count) | Churn Cohorts |
|
| This should stay the same for all months but we do have some remaining bugs which result in small variances from month to month |
| 28 | Non-Completed Accounts | Metric | Cohort Size minus accounts that have completed in that cohort (cumulative at that point in time) | Churn Cohorts |
|
|
|
| 29 | Completions | Metric | Count of accounts that have completed in that cohort. This number (for a particular cohort row) would be the same as if you filtered on the Overview page for particular installation month and then looked at the count of Completions and Opt Outs on the bottom graph (All Accounts Count). Different from the “Completions (count)” metric in that Opt Outs have been merged in | Churn Cohorts |
|
|
|
| 30 | Completions Rate | Metric | Completions divided by Cohort Size. Percentage of that cohort that have completed (or opted out) | Churn Cohorts |
|
|
|
| 31 | Repo | Metric | Same as “Repo (count)” but for a specific cohort cumulative at that point in time | Churn Cohorts |
|
|
|
| 32 | Written Off | Metric | Same as “Write Off (count)” but for a specific cohort cumulative at that point in time | Churn Cohorts |
|
|
|
| 33 | Churned | Metric | Sum of Repo and Written Off | Churn Cohorts |
|
|
|
| 34 | Repo Rate | Metric | Repo divided by Non-Completed Accounts (this means that completed are excluded from the denominator and so the percentage will be larger compared to if completions had been counted). | Churn Cohorts |
|
|
|
| 35 | Write Off Rate | Metric | Written Off divided by Non-Completed Accounts (this means that completed are excluded from the denominator and so the percentage will be larger compared to if completions had been counted). | Churn Cohorts |
|
|
|
| 36 | Churn Rate (excl. Completions) | Metric | Churned divided by Non-Completed Accounts (this means that completed are excluded from the denominator and so the percentage will be larger compared to if completions had been counted). | Churn Cohorts |
|
|
|
| 37 | Churn Rate | Metric | The same as Churn Rate but using Cohort Size as the denominator instead of Non-Completed Accounts. This means it will be lower percentage than Churn Rate (excl. Completions) because the denominator would be bigger | Churn Cohorts |
|
|
|
| 38 | Portfolio in Default (Cohorts) | Metric | The percentage is the subset of the customers (in a particular installation month) who have been installed and haven’t yet churned who are in default at the end of a given month. So for example - if there were 100 accounts installed on that month and by the end of the fifth month since install we have 60 normal, 10 late, 20 defaulted and 10 churned - this would mean we should see (20)/(100-10) = 22% in the matrix cell. The cohort size to the left should be the original total count of customers (100 in the above example). The cells of each month should only be excluding the customers in the denominator who had churned at that point. So if in the sixth month in above example there are now 15 customers churned and still 20 defaulted - we’d get a % of 20/85 = 24%. The fifth month cell is still going to be 22% though. | Default Cohorts |
|
|
|
| 39 | Activity Month | Dimension | This is controlled by the timeline filter at the bottom of the page. The particular date that it controls depends on the metric being used but generally it is simply the date that the event happened. Normally only available at month level and not lower. You can also drill up to year on graphs which are using this for the X axis though some metrics don’t necessarily support aggregation to year. | All | All | Various (see metrics) |
|
| 40 | Portfolio Category | Dimension | This is a calculated field built in the data warehouse which combines different status (like payment status) coming from Pulse with other properties. There is a multi-layer hierarchy which follows the below structure. 
| Overview, Active Portfolio, Customer Lookup | All Accounts, Contracted Portfolio, Active Portfolio |
| Not officially using this name anywhere currently. You can also see them in the Active Portfolio table as separate metrics. To inclulde opt out churn etc soon |
| 41 | Portfolio at Risk | Dimension | When an account expires there is a counter which increments each day on Pulse so you see the total consecutive days late. This is then grouped into different ranges of days (e.g. 0-7 days). This attribute is calculated for the last day of the month being shown (as it would change over the course of the month). Accounts that have no days expired are marked as Normal. | Portfolio At Risk | Contracted Portfolio |
|
|
| 42 | CU Type | Dimension |
|
|
|
|
|
| 43 | Region | Dimension | Includes Shop and Opco levels |
|
|
|
|
| 44 | Current ARPU Range | Dimension |
|
|
|
|
|
| 45 | TV Customer | Dimension | If the account contains a contract that has a product labelled as TV then this is marked as “Has TV”. Otherwise it is “No TV”. This is identifying TV based on the name or category of the product containing the text TV. | Overview, By Region, Active Portfolio, Portfolio at Risk, Portfolio Outflow, Defaulters, Default Cohorts, Churn Cohorts, Portfolio Splits, Customer Lookup |
|
|
|
| 46 | Enable Status | Dimension | This attribute only exists for Smart Solar devices (Bpower, Teide) because we own the IoT platform and can therefore get back from the device the status. In theory this correlates with the payment status (Normal=Enabled, Late/Defaulted=Disabled) but if the device is in a pending enable/disable state or error then it won’t match up | Overview, By Region, Active Portfolio, Portfolio at Risk, Portfolio Outflow, Defaulters, Portfolio Splits, Customer Lookup |
|
|
|
| 47 | Subsidy | Dimension |
| Overview, By Region, Active Portfolio, Portfolio at Risk, Portfolio Outflow, Defaulters, Portfolio Splits |
|
|
|
| 48 | ESF Only Customer | Dimension | If the account only has an ESF contract remaining active then “Yes”, else “No”. We identify ESF contract based on having the ESF product tag or having ESF in the name. There are complexities around excluding products/packages which have things like “ESF upgrade” in the name which are not the same as pure ESF. | Overview, By Region, Active Portfolio, Portfolio at Risk, Portfolio Outflow, Portfolio Splits |
|
|
|
| 49 | Campaign Name | Dimension |
|
|
|
|
|
| 50 | Notification Name | Dimension |
|
|
|
|
|
| 51 | Language | Dimension |
|
|
|
|
|
| 52 | Delivery State | Dimension |
|
|
|
|
|
| 53 | Customer ID | Column |
| Customer Lookup |
|
|
|
| 54 | Territory |
|
|
|
|
|
|
| 55 | End Date |
|
|
|
|
|
|
| 56 | Downpayment Date |
|
|
|
|
|
|
| 57 | Downpayment |
|
|
|
|
|
|
| 58 | Sign Up Sales Agent |
|
|
|
|
|
|
| 59 | Sign Up Sales Agent Code |
|
|
|
|
|
|
| 60 | Install Technician |
|
|
|
|
|
|
| 61 | Install Technician Code |
|
|
|
|
|
|
| 62 | Upgrade Technician |
|
|
|
|
|
|
| 63 | Upgrade Technician Code |
|
|
|
|
|
|
| 64 | Customer Status |
|
|
|
|
|
|
| 65 | Customer |
|
|
|
|
|
|
| 66 |
|
|
|
|
|
|
|
| 67 |
|
|
|
|
|
|
|
| 68 |
|
|
|
|
|
|
|
| 69 |
|
|
|
|
|
|
|
Related content
BBOXX