← All projects Discuss the project
Project ·
Data and reporting automation for an education group
Sector: Education and media
Context: an international education and media group with multiple brands and enrolment processes
- Google Cloud (Compute Engine, Cloud Functions, Cloud Scheduler)
- Google Drive / Google Sheets
- Looker Studio
- Google Analytics
- Measurement Protocol
- SFTP
The problem
Files from different brands arrived on an SFTP server and needed to update reports. Access required a static IP. Confirmed enrolments also needed matching to Google Analytics transactions, so they could be distinguished from applications.
What I did
- I set up a virtual machine, started by a Cloud Function triggered through Cloud Scheduler. In that implementation, the machine provided the static IP and shut itself down after each run.
- I programmed CSV loading into Drive and Sheets. Each file could replace the contents, append rows or update records by key.
- I handled the cell limit, recorded errors with a custom logger and managed the Drive files.
- I matched the coupon identifier to the Google Analytics transaction. A module sent the confirmed enrolment through Measurement Protocol within the configured window.
- I documented the process, code and deployment. I also prepared a guide for adding brands without changing the code.
Result
- Daily file transfers from SFTP to Drive and Sheets ran automatically in production.
- Site analytics distinguished applications from confirmed enrolments.
- New folders and brands could be added through configuration, following the operating guide.
How I approach this problem
Do you have a similar problem?
Tell me what you need to solve and which platforms you use.