This code basically calculates a few metrics based on numerators and denominators and then it bins the data into quintiles. This is a common operation that I perform for various data sets. My issue is that I always have to rename the metrics and columns.
I have a feeling that I could create functions to make this more general perhaps. The common operations I am doing are:
- % of total numerator
- delta from a target
- attainment (actual divided by the target)
- impact (the weighted delta from the target, how much a person is increasing or decreasing the total score)
- The quintiles (I have them labeled so the end-user understands what they are. These files are used in Excel for pivoting how the top and bottom employees perform)
I would like to have it more general: input a numerator and denominator. Instead of global variables, perhaps they could be input into a function?
csat_target = 0.75
fcr_target = 0.75
nps_target = 0.25
aht_target = 5.25
csat_weight = 0.30
fcr_weight = 0.30
nps_weight = 0.40
aht_weight = 0.00
df['fcr'] = df['final_hits'] / df['total_hits']
df['csat'] = df['csat_score_weight'] / df['survey_count']
df['nps'] = (df['promoters'] - df['detractors']) / df['survey_count']
df['aht'] = (df['total_time'] / df['total_calls'])
df['goal_attainment'] = ((df['nps'] / nps_target) * nps_weight) + ((df['csat'] / csat_target) * csat_weight) + ((df['fcr'] / fcr_target) * fcr_weight)
df['fcr_volume'] = df['total_hits'] / df['total_hits'].sum()
df['fcr_delta'] = df['fcr'] - fcr_target
df['fcr_impact'] = (df['fcr_delta'] * df['fcr_volume'])
df['survey_volume'] = df['survey_count'] / df['survey_count'].sum()
df['nps_delta'] = nps_target - df['nps']
df['nps_impact'] = df['nps_delta'] * df['survey_volume']
df['overall_nps_impact'] = np.where(df['survey_count'] > 0, df['nps_impact'], 0)
df['csat_delta'] = df['csat'] - csat_target
df['csat_impact'] = -(df['csat_delta'] * df['survey_volume'])
df['overall_csat_impact'] = np.where(df['survey_count'] > 0, df['csat_impact'], 0)
df.replace([np.inf, -np.inf], np.nan, inplace = True)
df['call_volume'] = df['total_calls'] / df['total_calls'].sum()
df['aht_delta'] = df['aht'] - aht_target
df['aht_impact'] = (df['aht_delta'] * df['call_volume'] )
df['fcr_weight'] = np.where(df['total_hits'] > 0, fcr_weight, 0)
df['nps_weight'] = np.where(df['survey_count'] > 0, nps_weight, 0)
df['csat_weight'] = np.where(df['survey_count'] > 0, csat_weight, 0)
df['fcr_dfile'] = pd.qcut(df['fcr_impact'], 5, labels = ["D1", "D2", "D3", "D4", "D5"])
df['nps_dfile'] = pd.qcut(df['overall_nps_impact'] , 5, labels = ["D1", "D2", "D3", "D4", "D5"])
df['csat_dfile'] = pd.qcut(df['overall_csat_impact'] , 5, labels = ["D1", "D2", "D3", "D4", "D5"])
df['aht_impact_dfile'] = pd.qcut(df['aht_impact'], 5, labels = ["D1", "D2", "D3", "D4", "D5"])
df['aht_dfile'] = pd.qcut(df['aht'], 5, labels = ["D1", "D2", "D3", "D4", "D5"])