Project Title:
“IDP Plus Insights: Automating Document Processing and Enabling Natural Language Data Querying”
Primary Objective: The client wanted a solution to:
The client required a solution that could scale across departments, manage diverse data types, and support real-time querying capabilities.
Solution Overview
Our team developed an end-to-end solution in Make.com to address each of the client’s needs. This solution involved:
- Automating Document Processing and Structuring (IDP): Using a combination of OCR, data parsing, and structuring in Airtable.
- Adding a Natural Language Query Layer: Integrating ChatGPT to allow users to ask questions and retrieve insights from the data using a webhook.
- Data Visualization: Connecting the structured data in Airtable with Power BI for real-time reporting and visualization.
The solution was designed to be user-friendly, efficient, and capable of handling large volumes of data from multiple sources.
Solution Components and Workflow
1. Document Processing and Consolidation
- Google Drive – Watch Files: The process begins when new files are added to specific folders in Google Drive. This acts as the primary trigger, automatically initiating the workflow.
- Router: We set up a router to classify files based on type or folder location, enabling specialized processing for each file type.
- Data Processing Routes:
- Handwritten Notes (OCR Processing):
- Google Cloud Vision: We applied OCR to extract text from scanned handwritten documents.
- Airtable: The extracted text was saved into Airtable, providing structured, searchable data.
Excel Spreadsheets:
- Microsoft Excel: The system retrieved rows from Excel files and prepared them for consolidation.
- Airtable: Data from Excel sheets was stored in a structured format within Airtable.
SAP CSV Exports:
- CSV Parsing: SAP data, exported as CSV files, was parsed to extract the necessary fields.
- Airtable: Parsed SAP data was structured and stored in Airtable.
Microsoft Access Data:
- CSV Parsing: Similarly, Access data was exported as CSV files and parsed for essential fields.
- Airtable: Structured Access data was saved to Airtable, ensuring uniformity with other data sources.
–Error Notifications: The workflow included notifications via Email and Slack to alert team members of any processing errors, ensuring quick response and minimal downtime.
2. Natural Language Data Query with ChatGPT
- Webhook – Custom Webhook: We set up a webhook endpoint in Make.com to receive natural language questions from users.
- Airtable – Search Records: Based on the user’s query, this module searched Airtable for relevant records. For example, if the user asked, “Show me last month’s sales,” it filtered Airtable records by date.
- HTTP Request to ChatGPT: The question, along with any retrieved data from Airtable, was sent to ChatGPT using the OpenAI API. ChatGPT processed the query and formulated a response, making the data accessible in a conversational manner.
- JSON Parsing: We parsed ChatGPT’s response to format it for readability.
- Webhook Response: The answer from ChatGPT was returned to the user through the webhook, providing a seamless query-response experience.
3. Power BI Integration for Data Analytics
- Data Export to Power BI: Although Make.com doesn’t natively integrate with Power BI, we used Airtable as an intermediary. The structured data in Airtable was connected to Power BI via periodic CSV exports, enabling the client’s team to visualize and track key metrics.
- Real-Time Reporting and Dashboards: With Power BI, the client could create dashboards for deeper insights, trend analysis, and performance tracking based on the consolidated data in Airtable.
Key Benefits and Results
- Increased Efficiency: The automated IDP workflow reduced manual data entry by over 70%, allowing team members to focus on higher-value tasks.
- Enhanced Data Accessibility: With natural language querying, non-technical team members could now retrieve insights from data without needing SQL skills or complex filtering.
- Scalability and Flexibility: The solution was designed to scale, allowing the client to add new data sources and processing routes as needed.
- Real-Time Insights: Power BI dashboards enabled the client’s management to track KPIs and trends in real time, helping them make data-driven decisions faster.
- Reduced Errors: Automated error notifications minimized downtime and ensured data accuracy.
Conclusion and Future Enhancements
The IDP Plus Insights solution successfully automated the client’s document processing workflow, streamlined data consolidation, and enabled interactive data querying. This comprehensive system provided the client with a scalable, easy-to-use platform for managing and analyzing complex data from diverse sources.
Future Enhancements:
- Deeper Integration with Power BI: Implementing a more seamless data synchronization option for Airtable and Power BI to eliminate manual exports.
- Advanced Analytics: Expanding ChatGPT’s role to generate more complex insights, such as predictive analytics based on historical data.
- Additional Data Sources: Adding routes for new data types as the client’s needs grow.
This case study exemplifies how advanced automation, combined with conversational AI, can transform data processing and access, making information more accessible, interactive, and valuable for business decision-making.
