# Sku drilldown - group by client and sku # Restore previous behavior without empty-DataFrame fallback or headers if df_filtered.empty: print("No data available for the selected filters.") else: drilldown = df_filtered.groupby(['client_name', 'sku']).agg( total_orders=('order_id', 'count'), Success=('status', lambda x: (x == 'SUCCESS').sum()), Pending=('status', lambda x: (x == 'PENDING').sum()), Processing=('status', lambda x: (x == 'PROCESSING').sum()), Failed=('status', lambda x: (x == 'FAILED').sum()), Rejected=('status', lambda x: (x == 'REJECTED').sum()) ).reset_index() drilldown = drilldown.sort_values(by=['client_name', 'sku'], ascending=[True, True]) drilldown

# Response-code drilldown grouped by client and sku (using canonical categories) if df_filtered.empty: print("No data available for the selected filters.") else: # Build aggregation dict with canonical categories agg_dict = {'total_orders': ('order_id', 'count')} for category in RESPONSE_CODE_COLUMN_ORDER: agg_dict[category] = ('response_category', lambda x, c=category: (x == c).sum()) drilldown_codes = df_filtered.groupby(['client_name', 'sku']).agg(**agg_dict).reset_index() # Ensure column order: client_name, sku, total_orders, then response categories in order column_order = ['client_name', 'sku', 'total_orders'] + RESPONSE_CODE_COLUMN_ORDER drilldown_codes = drilldown_codes[column_order] drilldown_codes = drilldown_codes.sort_values(by=['client_name', 'sku'], ascending=[True, True]) drilldown_codes

# Response-code drilldown grouped by SKU only (without client_name) if df_filtered.empty: print("No data available for the selected filters.") else: # Build aggregation dict with canonical categories agg_dict = {'total_orders': ('order_id', 'count')} for category in RESPONSE_CODE_COLUMN_ORDER: agg_dict[category] = ('response_category', lambda x, c=category: (x == c).sum()) drilldown_codes_by_sku = df_filtered.groupby(['sku']).agg(**agg_dict).reset_index() # Ensure column order: sku, total_orders, then response categories in order column_order = ['sku', 'total_orders'] + RESPONSE_CODE_COLUMN_ORDER drilldown_codes_by_sku = drilldown_codes_by_sku[column_order] drilldown_codes_by_sku = drilldown_codes_by_sku.sort_values(by=['total_orders'], ascending=False) drilldown_codes_by_sku

total_batches = len(arts_receive_valid) total_consumers = arts_receive_valid['consumer_count'].sum() unique_orgs = arts_receive_valid['organization_id'].nunique() unique_skus = arts_receive_valid['sku'].nunique() display(HTML(f""" <h2>ARTS Receive Workflow - Metrics</h2> <div style="display: flex; gap: 20px; flex-wrap: wrap;"> <div style="background: #f0f4f8; padding: 20px; border-radius: 10px; min-width: 150px; text-align: center;"> <div style="font-size: 32px; font-weight: bold; color: #1a365d;">{total_batches:,}</div> <div style="color: #4a5568;">Total Batches</div> </div> <div style="background: #e6fffa; padding: 20px; border-radius: 10px; min-width: 150px; text-align: center;"> <div style="font-size: 32px; font-weight: bold; color: #234e52;">{total_consumers:,}</div> <div style="color: #4a5568;">Total Consumers</div> </div> <div style="background: #f0f4f8; padding: 20px; border-radius: 10px; min-width: 150px; text-align: center;"> <div style="font-size: 32px; font-weight: bold; color: #1a365d;">{unique_orgs}</div> <div style="color: #4a5568;">Unique Orgs</div> </div> <div style="background: #f0f4f8; padding: 20px; border-radius: 10px; min-width: 150px; text-align: center;"> <div style="font-size: 32px; font-weight: bold; color: #1a365d;">{unique_skus}</div> <div style="color: #4a5568;">Unique SKUs</div> </div> </div> """))

display(HTML("<h2>ARTS Receive - Batch History</h2>")) if arts_receive_valid.empty: print("No data available.") else: batch_table = arts_receive_valid[['time', 'client_name', 'sku', 'subscription_id', 'consumer_count']].copy() # Apply client filter if set if arts_org_filter != 'All': batch_table = batch_table[batch_table['client_name'] == arts_org_filter] # Sort: desc by timestamp, then asc by client name batch_table = batch_table.sort_values( by=['time', 'client_name'], ascending=[False, True] ) batch_table['time'] = batch_table['time'].dt.strftime('%Y-%m-%d %H:%M:%S') batch_table = batch_table.rename(columns={ 'time': 'Batch Timestamp', 'client_name': 'Client Name', 'sku': 'SKU', 'subscription_id': 'Subscription ID', 'consumer_count': 'Consumer Count' }) batch_table

daily_receives = arts_receive_valid.groupby('date').agg( batch_count=('time', 'count'), consumer_count=('consumer_count', 'sum') ).reset_index() fig, ax1 = plt.subplots(figsize=(12, 5)) ax1.bar(daily_receives['date'], daily_receives['consumer_count'], color='#9f7aea', alpha=0.7) ax1.set_xlabel('Date') ax1.set_ylabel('Consumer Count', color='#9f7aea') ax1.tick_params(axis='y', labelcolor='#9f7aea') ax2 = ax1.twinx() ax2.plot(daily_receives['date'], daily_receives['batch_count'], color='#ed8936', linewidth=2, marker='o', markersize=4) ax2.set_ylabel('Batch Count', color='#ed8936') ax2.tick_params(axis='y', labelcolor='#ed8936') plt.title('ARTS Receive - Daily Consumer Volume & Batch Count') plt.xticks(rotation=45) fig.tight_layout() plt.show()