Hey Folks!
Ever felt swamped by data? Let’s cut through the noise. We’re using Node.js for ETL to prepare data for Looker Studio, a tool that turns data into interactive visuals. This ensures your data is primed for both analysis and presentation.
This is an easy way to integrate Knack, Google Cloud Functions, Google BigQuery, and Looker Studio:
1. Define Your Goals: Know what you want your report to achieve. In our case, we’re focusing on examining commissions for sales agents.
2. Locate Your Data: We’re pulling from Knack’s object_4 table. It’s got everything from transaction IDs to seller names.
3. Set Up Your Data Warehouse: We’re using Google BigQuery. One table, that’s it. Simple and fast.
4. ETL Time: We’re coding our ETL functions in NodeJS. It’s all about extracting, transforming, and loading.
5. Visualize in Looker Studio: This is where your data comes alive. Looker Studio makes it easy to create interactive reports.
Don’t want to do this manually every time? Google Cloud Scheduler is your friend. Set it up once, and it’ll run your tasks automatically.
We use these functions:
main.js: This function kicks off the ETL process and coordinates data flow.
getData.js: Connects to Knack’s API to pull raw data for the ETL process.
format.js: Transforms the raw data to make it ready for analysis.
upload.js: Loads the transformed data into your chosen storage for easy querying.
Dividing tasks in this way improves code clarity and facilitates troubleshooting at each ETL phase. Let us know if you want us to share these files!
We’ve also created a handy library if you want to extract data from Knack using typescript to write your ETL functions in TypeScript. knack-queries - npm.
And there you have it!