How to pull and understand data from a tiered Department setup to obtain KPIs like (i) Missed calls, (ii) Abandoned calls and (iii) Wait Times (of which Ring Duration is an analogue to).


Overview

  1. Export Group Statistics CSV from Dialpad
  2. Paste rows 6-9 containing formulae from previous spreadsheet
  3. Copy numbers to your Report


Steps

  1. Go to Dialpad Analytics. Adjust date range as needed.
    1. Calgary  (https://go.amestile.com/cgy-simring-analytics)
    2. Winnipeg  (https://go.amestile.com/wpg-simring-analytics)
  2. Export CSV > Group Statistics > click Send report.
  3. Wait for email to arrive. Open the CSV file.
  4. Either:
    1. Highlight, copy and paste previous exported report's rows 6-9, which contain formulae (copy from Dialpad_SimRing_WPG_2025.08.01-31.xlsx), to new report.
    2. Copy and paste each formula into their respective cells, from the table below.
  5. (optional) 'Save As' file as XLSX.


Formulae

Cell Formula
D6 =MAX(D2:D5)&" "&SUBSTITUTE(D1, "_", " ")
D7 =D2-F6-G6-H6 &" calls captured"
D8 =TEXT(1 - (VALUE(LEFT(H8,FIND("%",H8)-1))/100), "0.0%") & " captured"
F6 =MAX(F2:F5)
F7 =TEXT(ROUND(F6 / VALUE(TEXTJOIN("", TRUE, IF(ISNUMBER(--MID($D$6, ROW(INDIRECT("1:" & LEN($D$6))), 1)), MID($D$6, ROW(INDIRECT("1:" & LEN($D$6))), 1), ""))), 4), "0.0%") &" "&SUBSTITUTE(F1, "_", " ")
G6 =MIN(G2:G5)
G7 =TEXT(ROUND(G6 / VALUE(TEXTJOIN("", TRUE, IF(ISNUMBER(--MID($D$6, ROW(INDIRECT("1:" & LEN($D$6))), 1)), MID($D$6, ROW(INDIRECT("1:" & LEN($D$6))), 1), ""))), 4), "0.0%") &" "&SUBSTITUTE(G1, "_", " ")
H6 =SUM(H2:H5)
H7 =TEXT(ROUND(H6 / VALUE(TEXTJOIN("", TRUE, IF(ISNUMBER(--MID($D$6, ROW(INDIRECT("1:" & LEN($D$6))), 1)), MID($D$6, ROW(INDIRECT("1:" & LEN($D$6))), 1), ""))), 4), "0.0%") &" "&SUBSTITUTE(H1, "_", " ")
H8 =TEXT(VALUE(LEFT(F7,FIND("%",F7)-1))/100 + VALUE(LEFT(G7,FIND("%",G7)-1))/100 + VALUE(LEFT(H7,FIND("%",H7)-1))/100, "0.0%") & " unanswered"
U6

=LET(

    WeightedAvg, (($D$2*U2 + $D$3*(U2+U3) + $D$4*(U2+U3+U4) + $D$5*(U2+U3+U4+U5)) / $D$2),

    IF(INT(WeightedAvg/60)>=1, INT(WeightedAvg/60)&"h ", "") &

    IF(INT(MOD(WeightedAvg,60))>=1, INT(MOD(WeightedAvg,60))&"m ", "") &

    ROUND(MOD(WeightedAvg,1)*60, 0)&"s "&

    IF(U1="asa", "average speed to answer", UPPER(LEFT(SUBSTITUTE(U1, "_", " "),1)) & LOWER(MID(SUBSTITUTE(U1, "_", " "),2,LEN(U1))))

)
AC6 =SUM(AC2:AC5)
AC8 =TEXT(SUM(AC2:AC5)/D2,"0.0%") & " answered"
AT9

Weighted ring duration

Tier 1 calls: all experience D2

Tier 2 calls: experience D2 + D3

Tier 3: D2 + D3 + D4

Tier 4: full path
AU6

=LET(

    WeightedAvg, (($D$2*AU2 + $D$3*(AU2+AU3) + $D$4*(AU2+AU3+AU4) + $D$5*(AU2+AU3+AU4+AU5)) / $D$2),

    IF(INT(WeightedAvg/60)>=1, INT(WeightedAvg/60)&"h ", "") &

    IF(INT(MOD(WeightedAvg,60))>=1, INT(MOD(WeightedAvg,60))&"m ", "") &

    ROUND(MOD(WeightedAvg,1)*60, 0)&"s "&

    UPPER(LEFT(SUBSTITUTE(AU1, "_", " "),1)) &

    LOWER(MID(SUBSTITUTE(AU1, "_", " "),2,LEN(AU1)))

)
AW6 =LET(

    WeightedAvg, (($D$2*AW2 + $D$3*(AW2+AW3) + $D$4*(AW2+AW3+AW4) + $D$5*(AW2+AW3+AW4+AW5)) / $D$2),

    IF(INT(WeightedAvg/60)>=1, INT(WeightedAvg/60)&"h ", "") &

    IF(INT(MOD(WeightedAvg,60))>=1, INT(MOD(WeightedAvg,60))&"m ", "") &

    ROUND(MOD(WeightedAvg,1)*60, 0)&"s "&

    UPPER(LEFT(SUBSTITUTE(AW1, "_", " "),1)) &

    LOWER(MID(SUBSTITUTE(AW1, "_", " "),2,LEN(AW1)))

)