Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

first call resolution

Dear community,

I am a handicap when it comes to measures or any dynamic filtering. I like to measure FCR for my organization and I have spent a long time to make this work but I couldn't. 

I tried the solution from this question but it didn't work because my variable (customer_id) isn't constant. 


An exemple of the dataset:

customer_id

contact_date

issue_id

1

2020-05-07

770

2

2020-05-05

772

2

2020-05-05

772

2

2020-05-07

764

3

2020-05-20

759

4

2020-05-14

673

4

2020-05-20

772

5

2020-05-12

741

5

2020-05-15

677

5

2020-05-15

677

6

2020-05-06

637

6

2020-05-15

772

7

2020-05-05

772

7

2020-05-06

746

7

2020-05-07

753

7

2020-05-07

753

8

2020-05-06

755

 


The idea is to calculate the data like this, when the same customer contacts us but for different issue, count = 1.

 

I have other filters besides day by day like the exemple below that I would like to see such as FirstOfMonth, FirstOfWeek, Resolver etc. However, FCR for day, week and month are the most interesting matrics for my team.

 

customer_id

contact_date

issue_id

count

1

2020-05-07

770

1

2

2020-05-05

772

2

2

2020-05-07

764

1

3

2020-05-20

764

1

4

2020-05-14

673

1

4

2020-05-20

772

1

5

2020-05-12

741

1

5

2020-05-15

677

2

6

2020-05-06

637

1

6

2020-05-15

772

1

7

2020-05-05

772

1

7

2020-05-06

746

1

7

2020-05-07

753

2

8

2020-05-06

755

1

 

Then we calculate,

FCR = count(customer_id where count = 1) / count(customer_id)

Using the example above, 

FCR = 11/14 = 0.78 (*in general, FCR is different if we plot it day by day or month by month

I need a few measures to calculate the numerator and denominador for calculation.

 

Could anyone help me? Thanks in advance!

Best regards,

Steven

  • Anonymous's avatar
    Anonymous
    6 years ago
    // T is the first dataset (not the second, aggregated).
    // There must be a Date table in the model
    // marked as Date table that joins to
    // T on [contact_date] and the filtering is
    // one-way. There also has to be a Customer dimension
    // in the model that joins on CustomerId to
    // T on [customer_id]. All columns of the fact table
    // T must be hidden and slicing is only allowed via
    // dimensions. If you want to slice by Issue, 
    // you have to create a dimension Issue and join
    // to T on [issue_id]. In the code below I've assumed
    // that you won't be slicing by issues.
    // Then you can create measures:
    
    // This, for any slicing, will tell you
    // how many rows are visible in the current
    // context, which means "number of calls."
    [# Calls] = COUNTROWS( T )
    
    // This will tell you the percentage of
    // customers visible in the current context
    // that got their issue resolved in the first
    // call.
    [First Call Resolution %] =
    var __existingCustIssueTable =
    	SUMMARIZE(
    		T,
    		Customer[CustomerID],
    		T[issue_id]
    	)
    var __custIssueWithOneCallResolution =
    	SUMX(
    		__existingCustIssueTable,
    		( [# Calls] = 1 ) * 1
    	)
    var __custIssueTotal =
    	COUNTROWS(
    		__existingCustIssueTable
    	)
    RETURN	
    	DIVIDE(
    		__custIssueWithOneCallResolution,
    		__custIssueTotal
    	)

    If you decide to introduce a separate Issue dimension, you'll need to adjust the code, especially what's under SUMMARIZE. Here's the code after the required changes for this scenario:

    [First Call Resolution %] =
    var __existingCustIssueTable =
    	SUMMARIZE(
    		T,
    		Customer[CustomerID],
    		Issue[IssueID]
    	)
    var __custIssueWithOneCallResolution =
    	SUMX(
    		__existingCustIssueTable,
    		( [# Calls] = 1 ) * 1
    	)
    var __custIssueTotal =
    	COUNTROWS(
    		__existingCustIssueTable
    	)
    RETURN	
    	DIVIDE(
    		__custIssueWithOneCallResolution,
    		__custIssueTotal
    	)

    By the way, your specification of the problem is a bit misleading. You cannot just count customers in the current context. You have to calculate the pairs "customer-issue" to get the correct percentage.

     

    Best

    D

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    // T is the first dataset (not the second, aggregated).
    // There must be a Date table in the model
    // marked as Date table that joins to
    // T on [contact_date] and the filtering is
    // one-way. There also has to be a Customer dimension
    // in the model that joins on CustomerId to
    // T on [customer_id]. All columns of the fact table
    // T must be hidden and slicing is only allowed via
    // dimensions. If you want to slice by Issue, 
    // you have to create a dimension Issue and join
    // to T on [issue_id]. In the code below I've assumed
    // that you won't be slicing by issues.
    // Then you can create measures:
    
    // This, for any slicing, will tell you
    // how many rows are visible in the current
    // context, which means "number of calls."
    [# Calls] = COUNTROWS( T )
    
    // This will tell you the percentage of
    // customers visible in the current context
    // that got their issue resolved in the first
    // call.
    [First Call Resolution %] =
    var __existingCustIssueTable =
    	SUMMARIZE(
    		T,
    		Customer[CustomerID],
    		T[issue_id]
    	)
    var __custIssueWithOneCallResolution =
    	SUMX(
    		__existingCustIssueTable,
    		( [# Calls] = 1 ) * 1
    	)
    var __custIssueTotal =
    	COUNTROWS(
    		__existingCustIssueTable
    	)
    RETURN	
    	DIVIDE(
    		__custIssueWithOneCallResolution,
    		__custIssueTotal
    	)

    If you decide to introduce a separate Issue dimension, you'll need to adjust the code, especially what's under SUMMARIZE. Here's the code after the required changes for this scenario:

    [First Call Resolution %] =
    var __existingCustIssueTable =
    	SUMMARIZE(
    		T,
    		Customer[CustomerID],
    		Issue[IssueID]
    	)
    var __custIssueWithOneCallResolution =
    	SUMX(
    		__existingCustIssueTable,
    		( [# Calls] = 1 ) * 1
    	)
    var __custIssueTotal =
    	COUNTROWS(
    		__existingCustIssueTable
    	)
    RETURN	
    	DIVIDE(
    		__custIssueWithOneCallResolution,
    		__custIssueTotal
    	)

    By the way, your specification of the problem is a bit misleading. You cannot just count customers in the current context. You have to calculate the pairs "customer-issue" to get the correct percentage.

     

    Best

    D

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Anonymous,

       

      I modified your code and the result is what I got from my SQL. When I was solving the problem I wasn't exactly sure how I could measure this.

      Thanks for your help! 

      Best regards,

      Steven

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Greg_Deckler, I will try it and get back to you. Thanks for responding!

  • Hello,

     

    I need to measure First Contact Resolution (FCR), with more conditions than the original post. But I'm having trouble doing this with DAX.

     

    Example of the dataset:

     

    I highlighted the last 4 lines because the contacts happened in the same day, so they need a different treatment.

     

    I have to calculate FCR in two different ways:

     

    1. If the same customer doesn't contact again 3 days after the first contact it's a first contact resolution.

    In this first calculation, we only consider the customer and the contact date.

    Some conditions:

    - If the customer contacts more than one time in the "first day", we only count as one.

    - If we have a contact within 3 days after the first contact, and no contact after that second contact, this second contact is not a first contact resolution.

     

    The result I expect:

     

    As you can see, I only count one contact for the customers H and I.

     

    1. If the same customer doesn't contact again 3 days after the first contact with a contact from the same type, we consider it a first contact resolution.

    Some conditions:

    - If the customer contacts more than one time in the "first day" with the same contact type, we only count as one.

    - If we have a contact from the same type within 3 days after the first contact, and no contact from the same type after that second contact, this second contact is not a first contact resolution.

     

    The result I expect:

     

    In this example, customer I counts with only one contact.

    I also need it to work with slicers. Here's an example with the type dimension in the slicer and the type 1 selected:

     

     

    Thanks for all the help.

    Best regards.

    Jorge