Unveiling the Power of ChatGPT in Excel: Streamlining Customer Verbatim Analysis
In the ever-evolving landscape of AI applications, the integration of ChatGPT within Excel takes on a new dimension, where calls to the Azure OpenAI service API are orchestrated via queries scripted in the M language. This language operates within the Excel Power Query add-in, facilitating the seamless execution of AI-powered analyses. It’s crucial to note that while this implementation enhances the analysis of simulated customer verbatims, considerations surrounding the authentication key’s visibility within a workbook query are paramount.
The code for the various Power Query functions and an example Excel workbook are available in this GitHub repository.
Understanding the Landscape
Before diving into the technicalities, it’s crucial to understand the landscape we’re navigating. Customer verbatims, although simulated by ChatGPT, mimic the essence of real interactions. These texts can range from feedback, issues, suggestions, or inquiries, forming a substantial dataset for analysis.
Prompt for dataset generation :
CONTOSO is a fictitious brand used by Microsoft for its database demonstrations.
Choose ten products from the CONTOSO Adventure Works range.
For each of these products, generate 5 customer reviews (in English) by varying the sentiment: negative, neutral, positive.
Format this last response in the form of a table with 3 columns: Product | Verbatim | Sentiment and add a 4th column with a score from 1 to 10 corresponding to the Net Promoter Score. Add a fifth column containing an English first name, varying the gender and origin.
All cells must be filled in. Do not add any other words outside the table.
Structuring the Excel Workbook
Rest assured, the utilization of the Azure OpenAI service API through M language in Excel’s Power Query provides a bridge between cutting-edge AI capabilities and the practicality of Excel. Nonetheless, the accessibility of the authentication key within a workbook query presents a security concern that demands careful handling. This creates a necessity to address security measures without compromising the integration’s functionality for verbatim analysis.
Here is the code of an M function performing a call to the Azure OpenAI service API.
We use several input parameters in the function, including the customer verbatim. The other function parameters determine the response generated by the model. Several sub-queries or Power Query parameters provide elements that can be modified: main prompt (the “system message”), API key, model deployment, and so on.
Unsupervised and Supervised Categorization
The categorization process begins by allowing ChatGPT to autonomously invent categories — a preliminary unsupervised approach. This stage empowers the AI to identify potential themes or groupings within the verbatims. Subsequently, the user intervenes to specify the categories they intend to retain. Here, the Language Learning Model (LLM) within ChatGPT comes into play. It functions to determine which of the user-specified categories best applies to each verbatim, executing a supervised approach without the explicit training of a classification model. This hybrid method combines the ingenuity of AI-driven unsupervised categorization with user-guided refinement, streamlining the verbatim analysis process within Excel.
Sentiment Analysis
Leverage ChatGPT’s sentiment analysis tools to gauge the emotional tone of verbatims. Classify them as positive, negative, or neutral. This categorization aids in understanding customer sentiment and overall satisfaction levels.
Here, we introduce a few examples using the technique known as few-shot learning.
The M function then expands to incorporate these examples into the system message.
Summarizing Verbatims and Identifying Action Levers
Create a summary sheet that highlights the key points from the verbatims. Use ChatGPT to distill lengthy texts into concise summaries. This will aid in quickly grasping the essence of customer feedback. Identify action levers or recurring issues that demand attention.
Prompt for summary :
Your role is to summarize a set of customer reviews for CONTOSO Adventure Works products. Please provide a concise and informative summary of the key points and sentiments expressed in the reviews. The summary must include a sentence for each category {categories}. The summary should end with a list of concrete and original actions to improve the areas of dissatisfaction.
Result :
Overall, customers expressed satisfaction with the quality and durability of CONTOSO Adventure Works products. They appreciated the wide range of products available and the excellent customer service provided. However, some customers faced issues with sizing and found certain products to be overpriced.
Quality: Customers praised the high-quality materials and craftsmanship of the products, which contributed to their durability and longevity.
Durability: Many customers were impressed with the long-lasting nature of the products, even after extensive use in various outdoor activities.
Product Range: The wide variety of products offered by CONTOSO Adventure Works was appreciated by customers, catering to different needs and preferences.
Customer Service: The company received positive feedback for its responsive and helpful customer service, which enhanced the overall shopping experience.
Sizing: Some customers encountered difficulties with sizing, finding that certain products did not fit as expected or were inconsistent across different items.
Pricing: A few customers felt that some products were overpriced compared to similar offerings from competitors.
To address these areas of dissatisfaction, CONTOSO Adventure Works could consider the following actions:
1. Review and improve the sizing guide to ensure consistency and accuracy across all products.
2. Offer a more comprehensive and user-friendly exchange/return policy for customers who face sizing issues.
3. Conduct regular market research to ensure competitive pricing and consider offering occasional discounts or promotions to attract price-sensitive customers.
Automatic Response Generation for Dissatisfied Customers
Employ ChatGPT to generate automatic responses for dissatisfied customers. These responses should address their concerns, provide solutions, and offer a personalized touch. Craft these responses based on the analysis and categorized issues.
We leverage the context from other cells in the row to personalize the response.
Prompt for response generation :
Your role is to draft a response to a customer review for a CONTOSO Adventure Works product. You need to craft a thoughtful and helpful response. Address any concerns or issues raised by the customer.
Response example :
Subject: Assistance with Your CONTOSO Adventure Works Hiking Boots
Dear Henk,
Thank you for purchasing our CONTOSO Adventure Works Hiking Boots. I understand that you may have some concerns or issues with the product, and I am here to help.
Firstly, I would like to rephrase your request to ensure I fully understand your concerns. Please provide more details about the specific issues you are experiencing with your hiking boots, such as fit, comfort, or durability.
As a representative of CONTOSO Adventure Works, I am committed to finding a solution that meets your needs. Based on the information you provide, I will be able to suggest appropriate next steps, such as exchanging the boots for a different size or model, or offering advice on how to improve their performance.
Please be assured that our customer support team is available to assist you further. You can reach us via email at support@contosoadventureworks.com or by phone at 1–800–123–456
Implementation and Iteration
Once the framework is set, implement it on a subset of verbatims. Evaluate the results, refine the categorization models, sentiment analysis, and summary generation. Iteratively improve the promps for more accurate and insightful outcomes.
Remember, prompt engineering essentially involves expressing a clear need in natural language.
Ethical Considerations
While harnessing the power of AI within customer verbatim analysis, ethical considerations hold utmost importance. Ensure the anonymization of data, respect user privacy, and handle the simulated verbatims responsibly.
Conclusion
The integration of ChatGPT within Excel not only revolutionizes customer verbatim analysis but also simplifies the process for non-developer users. This user-friendly approach enables the handling of data volumes that might otherwise be inaccessible within traditional information systems. However, a crucial aspect demanding attention is the management of billing associated with API calls. It’s imperative to exercise control over these calls, considering that each time a request is updated, it restarts the requests for all rows in the table. This nuanced balance between accessibility and cost control is key to optimizing the utilization of this powerful tool within Excel for customer verbatim analysis.
Disclaimer: This post is a collaborative effort between ChatGPT and the author. While ChatGPT provided the structural framework and content suggestions, the author directed the writing process through iterations of prompts and guidance. The information presented amalgamates the AI’s capabilities and the author’s expertise, aiming to provide a comprehensive guide for leveraging ChatGPT within Excel for customer verbatim analysis.