Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.18 Telecom: Churn, ARPU and Data Usage

By Last updated:

Example first. Ravina analyses a fictional mobile operator's sample of subscribers and wants churn, ARPU and data use in one view.

Telecom project — in 30 seconds

What: A Telecom tracker: one Table tblSubs, the calculated column Usage, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Telecom report: Churn %, Average ARPU, Total Data (GB) are the numbers a manager reads first.

Tiny example: From 6 rows: Churn % = 33.3%, Average ARPU = ₹372.33, Total Data (GB) = 188.

First look at the data (before → after):

Telecom project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

SubID Usage
S-701 Heavy
S-702 Normal
S-703 Heavy
S-704 Heavy
S-705 Normal
S-706 Normal

Expected result — KPI cells (expected results)

KPI Formula Result
Churn % =COUNTIF(tblSubs[Churned],"Yes")/ROWS(tblSubs[Churned]) 33.3%
Average ARPU =AVERAGE(tblSubs[ARPU]) ₹372.33
Total Data (GB) =SUM(tblSubs[DataGB]) 188
Before table and formula as text

Before — Table tblSubs (fictional sample rows)

SubID City Plan ARPU DataGB Churned
S-701 Pune Postpaid 499 42 No
S-702 Pune Prepaid 239 18 Yes
S-703 Nashik Prepaid 299 35 No
S-704 Nagpur Postpaid 699 60 No
S-705 Solapur Prepaid 199 9 Yes
S-706 Kolhapur Prepaid 299 24 No

Formula / click path

Usage: =IF([@DataGB]>=30,"Heavy","Normal")
Churn %: =COUNTIF(tblSubs[Churned],"Yes")/ROWS(tblSubs[Churned])
Average ARPU: =AVERAGE(tblSubs[ARPU])
Total Data (GB): =SUM(tblSubs[DataGB])

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblSubs in Table Design › Properties › Table Name.
  2. Add the column Usage with =IF([@DataGB]>=30,"Heavy","Normal"); the Table fills it down.
  3. On a KPI sheet type =COUNTIF(tblSubs[Churned],"Yes")/ROWS(tblSubs[Churned]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and ARPU in Values (Average).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Plan.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a subscriber sheet: a Heavy/Normal usage flag (30 GB or more is Heavy), churn % in the sample, average ARPU, total data, average ARPU by city, a chart and a Plan slicer.

Calculated column formulas:

Column Formula
Usage =IF([@DataGB]>=30,"Heavy","Normal")

KPI cells (check your numbers):

KPI Formula Result
Churn % =COUNTIF(tblSubs[Churned],"Yes")/ROWS(tblSubs[Churned]) 33.3%
Average ARPU =AVERAGE(tblSubs[ARPU]) ₹372.33
Total Data (GB) =SUM(tblSubs[DataGB]) 188

PivotTable (expected):

City Average of ARPU
Kolhapur ₹299.00
Nagpur ₹699.00
Nashik ₹299.00
Pune ₹369.00
Solapur ₹199.00
Grand Total ₹372.33

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Plan and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Telecom_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Related ready-made dashboard: the Telecom dashboard guide and its workbook Telecom_Dashboard.xlsx show a finished version of this kind of report.

Practice task (try it before you open the answer):

  1. Which city has the highest average of ARPU?
  2. Filter the Table on City = Nagpur (Data › Filter): what is Churn % for Nagpur alone?
  3. Add the Clustered Column PivotChart and the Plan slicer to your Dashboard sheet.
Answer

Nagpur (₹699.00). For Nagpur only (1 row): Churn % = 0.0%. The chart shows 5 bars, one per city.

Ravindra Bagale's Tip

Show Average ARPU next to the rupee total. A percentage or ratio without the size behind it can make a small city look like a big problem.

Loading practice tasks...

  1. Total data gb (Easy): Add up Data GB in D2:D11.
  2. Average monthly bill (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Prepaid (Easy): How many rows have Plan "Prepaid"?
  4. Largest data gb (Easy): The largest value in D2:D11.
  5. Data GB for Prepaid (Medium): Total of D2:D11 where B is "Prepaid".
  6. Prepaid in Paris (Medium): Total of D2:D11 where B is "Prepaid" and C is "Paris".
  7. Best in Prepaid (Medium): Largest E2:E11 value where B is "Prepaid".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Priya Kulkarni (Medium): The Data GB for "Priya Kulkarni" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Prepaid (Hard): Share of D2:D11 that is "Prepaid", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →