Dynamic Survey Reporting & Automation

To engineer and deploy a multi-faceted automation system for a leading well-being organization. The primary goal was to completely eliminate manual data handling and report creation for two distinct, high-volume surveys. The solution involved creating two parallel workflows: one for generating instant, visual PDF feedback for users, and another for processing complex data sets to produce detailed, multi-page Google Doc reports

Automation Engine Zapier

Data Input & CRM HubSpot Forms

Data Processing & Logging Google Sheets (including advanced formulas, VLOOKUPs, and conditional logic)

Dynamic Visualization QuickChart.io API

Custom Document Generation CraftMyPDF & Google Docs

Cloud Storage & File Management Google Drive

Automated Communication Gmail

To provide survey respondents with immediate, personalized, and visually engaging feedback in the form of a custom PDF report, delivered directly to their inbox upon submission.

Initiation via HubSpot Form Submission- the workflow is triggered in real-time the moment a user completes and submits the FAW survey through HubSpot

Data Structuring in Google Sheets- the submission data is instantly parsed and logged as a new row in a designated Google Sheet, creating a reliable, organized data repository

Dynamic Chart Generation via QuickChart.io- the system utilizes the newly logged data to make a server-side API call to QuickChart.io. this generates a dynamic bar chart that visually represents the user's scores across key flourishing domains

Custom PDF Templating with CraftMyPDF- the workflow forwards the user's submission data along with the unique URL of the newly generated chart to CraftMyPDF. a pre-designed template is dynamically populated with the user’s name, scores, and the embedded chart image, creating a professional, branded report

Automated Email Delivery with Gmail- the final, generated PDF is attached to a personalized email and sent directly to the respondent's email address, completing the feedback loop within seconds of their submission

To automate the entire reporting lifecycle for a complex baseline survey, handling intricate data calculations and business logic to generate a structured, multi-page analytical report in Google Docs.

Data Ingress from HubSpot - the process commences upon the submission of the comprehensive baseline survey in HubSpot

Advanced Data Processing in Google Sheets - the raw data is sent to a "staging" tab within a master Google Sheet. Here, a system of pre-built formulas (including VLOOKUPs and conditional logic across multiple tabs) automatically performs complex calculations, transposes data, and processes the final quantitative and qualitative results

Calculated Data Retrieval - the Zap deliberately pauses for one minute to ensure all spreadsheet formulas have fully executed. it then uses a "Lookup Spreadsheet Row" action to search the processed data tabs for a unique submission ID and retrieves the complete, calculated dataset

Dynamic Google Doc Generation- the rich, processed data is methodically mapped to a pre-formatted Google Doc template. this populates all sections of the report, including the cover page, dynamic tables, and appendices, creating a new, complete document for each submission

PDF Conversion and Cloud Archiving- the newly created Google Doc is automatically saved to a primary folder in Google Drive for archival purposes. Zapier's integration with Google Drive then generates a PDF version of the document

Final Report Delivery- the generated PDF report is attached to a personalized email and sent to the stakeholder who submitted the form, timely and accurate delivery of the baseline analysis

This dual-workflow automation system successfully transformed the organization's survey and reporting infrastructure

Efficiency & Time Savings- eliminated 100% of the manual administrative overhead previously required for data processing and report creation, saving significant staff hours

Enhanced User Experience- reduced the feedback delivery time for the FAW survey from days to seconds, providing immediate value and engagement for users

Data Accuracy & Consistency- centralized all complex business logic within Google Sheets, removing the risk of human error and ensuring every report is generated with perfect consistency

Scalability- created a robust, hands-free system capable of handling any volume of future survey submissions without requiring human intervention or additional resources