Human-Readable ◉‿◉

Next Better Iteration: Building a Chat Moderation Filter with Looker

If we wait for perfection in business - or the exact right thing to do - we get nothing done and become a bottleneck.

In this blog post, I report steps taken to address pain points for a customer support team in need of new tools designed to facilitate more efficient workflow processes while educating and delighting customers. I discuss the process of introducing new SOPs, automation, and a Looker dashboard.

The objective is to demonstrate how placing emphasis on automation/continuous improvement processes can unlock value for the business.

Software Tools:

Example: Chat Moderation Filter Tool using Looker

Background:

The product team has launched a new chat feature designed to facilitate communication between two parties within a technology marketplace (B2B business model). Early signs signal product fit, however, the lag in training/education has left a gap for the customer support team to respond to as new issues arise.

Internal reporting has flagged numerous cases whereby users falsely assume Company X is actively monitoring chat between company A or B. Due to the nature of the business, urgent unanswered requests on behalf of either party have resulted in a poor outcome for the customer (payer). Timely behavioral intervention reduces the risk of customer churn and repeated instances of said behavior.

Problem:

Scaling up the business has increased demands on the chat moderation aspect of the customer support role. The product team has limited resourcing in the short-to-medium term to build product 2.0 with improved tooling for internal use.

Proposed solution:

  1. Build an operational Looker dashboard to filter/screen out irrelevant chat notifications

  2. Introduce a revised workflow within the current scope of work functions

Key Findings:

1/ Information Gathering – Consulting Team Members

No detail is too small. To understand the whole scope of the problem consultation with team members should give rise to a narrative that addresses the 5W1H.

2/ Information Gathering – Technical Requirements

Main Findings:

3/ Descriptive Analyis

Using descriptive analytics we can drill down on data from the last 8 months dating back to the initial launch of the product.

Data fields (2):

- round(mean(total messages sent per day),2)
- Last message sent date

Filters(3):

- User type is not equal to system – chat messages auto-generated by product 
- Chat Messaging First Message Sent Day of Week - is not Saturday AND is not Sunday)
- Chat Messaging Last Message Sent Date – is in the past 30 days

The line chart shows engagement increased significantly between January and May 22' and then plateaued thereafter. Furthermore, a consistent trend is observed for weekly seasonality whereby weekdays (regular business hours) remain higher versus weekends.

Filtering weekends, dates = 30 days, and NOT computer-generated messages, the average daily messages count is 126 (range 82-162).

3/ Looker Dashboard

The first order of operations is to determine what types of visualization(s) will be most conducive to informing chat moderation decision-making while screening out FA.

A table chart will do the trick. Recall that consultations resulted in the identification of 7 data fields that provide all the relevant info to make an informed decision. Let's add those fields to the table as new columns and then determine if there are new table calculations that further assist the decision-making process.

How about the element of time passing? Let's include a field that keeps track of the amount of time the sender is left unread by the recipient. The calculation is as follows:

diff_minutes(${dim_work_order.last_client_chat_message_sent_time}, ${dim_work_order.last_service_partner_chat_message_sent_time})

Lastly, let's also add a simple calculation that assigns the value '1' to each chat conversation. This field can serve as a trigger should the team prefer single or multiple-value scheduling alerts (see the section below).

Filters:

The 'chat-msg content' field is applied as a filter to screen keywords/phrases that might cue staff that timely urgent intervention is needed (e.g. "please help!" or "this is an emergency...").

Instead of guessing the exact keywords, phrasing, or sentence, we can leverage filter string patterns that introduce more flexibility into the filter. Looker filter expression (aka. SQL Regex function) 'matching(advanced) --> %insert_string% identifies patterns from the entire conversation.

Other filters might include said time passing since the last 'urgent' unread msg over a given time period and exclusion of chat messages auto-generated by a bot or program.

Alerts and Schedules:

Alerts are set on query-based or 'Look-linked tiles' on dashboards and apply to at least one numeric measure or table calculation in the tile's visualization. Single-value alerts and time-series alerts can be configured to the end-users preference.

We can anticipate said keyword filter going through many iterations before the dashboard is ready to replace the current process. For this reason, scheduled Slack reminders that map the data refresh rate will help ensure that staff review the most recent chat history (e.g. 8:20am; 10:40am; 1:00pm...).

If using single value alerts (as opposed to timer) create a new field using table calculations and then assigned conditional scheduling that triggers alerts when X value is shown in the dashboard.

value=1 so that all conversations (rows) in the table that meet the filter criteria are flagged in a timely manner.

Looker Dashboard:

Here is a screenshot of the Looker view applied to a dashboard. Note, that black rectangles are annotated to prevent visibility of customer data. As an aside, if you choose a similar approach for your team, consider the impact of ordering fields in such as way that is most likely to lead to a decision the fastest.

5/ Proposed Customer Support Workflow Process

The two diagrams below illustrate the proposed changes to the existing workflow process.

4/ Calculating Efficiency Gains

Self-reported values provided by the customer support team and calculated average daily chat count will serve as inputs to determine efficiency gains at the current inbound volume (last 30 days).

Inputs:

Formula:

Daily Chat Total * probability of FA/INV = total per chat type

Total per chat type * time allocated per msg = total moderation time

FA Calculations:

126 * 0.9 = 113.4 --> 113.4 * 0.5 = 56.7 mins

INV Calculations:

126 * 0.1 = 12.6 --> 12.6 * 2 = 25.2 mins

Dashboard Performance Goals:

A. reduce review of FA by 90%

56.7 - (56.7* 0.9) = 5.67 review mins/day

B. Increase speed of review for INV by 20%

25.2 - (25.2 * 0.2) = 20.16 review mins/day

Total efficiency gains:

((5.67 + 20.16) / (56.5 + 25.2) - 1) = 68.4%*

*higher efficiency returns on larger N of chat replies

Conclusion

In conclusion, there are observed gaps in customer support that (for the time being) can not be resolved through productization due to company resource constraints and prioritization. Existing data infrastructure and automation tools are available to streamline implementation. Customer support team members are anticipated to benefit from reduced context switching which may result in higher quality work, less burnout, and opportunities to reallocate labor to the completion of higher value tasks within the department.

Strengths, Weaknesses, and Limitations:

#BI Tools #data analysis #looker #software automation