← All projects Discuss the project
Project ·
Data, reporting, and conversion automation for education
Client: an international education and media group with multiple brands and enrollment processes
- Google Cloud: Compute Engine, Cloud Functions and Cloud Scheduler
- Google Drive and Google Sheets
- Looker Studio
- Google Analytics
- Measurement Protocol
- SFTP
The problem
Multiple brands produced operational files on an SFTP server that had to feed reporting and analytics. The process needed several update methods, had to run from an allowlisted IP address, and had to match confirmed enrollments against transactions already recorded.
What I did
- I built the process on a virtual machine launched by a Cloud Function and scheduled with Cloud Scheduler. The machine exists because the SFTP required a static IP and Cloud Functions had none; it shuts itself down when finished.
- I implemented CSV ingestion into Drive and Sheets with three update modes: full rewrite, appending new lines, and key-based updates, depending on what each file needed.
- I handled the sheet cell limit, error logging through a purpose-built logger, and the lifecycle of the files in Drive.
- I added the module that matches the coupon identifier against the Google Analytics transaction and sends the confirmed enrollment hit through the Measurement Protocol, inside the configured window.
- I left documentation at three levels: the functional description of the process, the technical one covering the code and its deployment, and a step-by-step runbook for adding a new brand without touching code.
Result
- The process reached production and automated the flow of operational data into reporting and analytics for multiple brands.
- Measurement could tell an application apart from a confirmed enrollment inside site analytics.
- The configuration and documentation supported new folders and brands without redesigning the architecture.
How I approach this problem
Do you have a similar problem?
Describe the starting point, the platforms involved, and the decision you need to unblock.