Manual exports
Sales or transaction data had to be pulled repeatedly before reporting.
Back to case studies
Case study
Looker Studio Sales ETL Automation
The client needed sales and transaction data to appear reliably in Google Looker Studio. I built an AWS Glue ETL job that joins transaction, product, branch, category, manufacturer, and ecommerce order data, then loads a reporting-ready dataset without repeated manual exports.
IndustryRetail and reporting
Company sizeGrowing SME
DepartmentOperations and leadership
Overview
Sales data became reliable enough for recurring business reporting.
The solution automated the movement of raw operational data into a clean reporting structure that Looker Studio could use for dashboards and performance visibility.
The business problem
Reporting depended on manual data extraction, joins, and cleanup.
Sales reporting needed more than one table. Transaction records had to be connected with product, branch, category, manufacturer, pricing, and ecommerce order details before the data became useful for dashboards.
Inconsistent data shape
Raw transaction data was not always ready for clean dashboard reporting.
Delayed dashboards
Looker Studio visibility depended on whether the latest data had been prepared.
Multiple source tables
Transactions needed context from products, listings, branches, categories, manufacturers, and ecommerce orders.
Deleted record filtering
Reports needed to exclude deleted transactions so dashboard numbers stayed cleaner.
Reporting storage
The final dataset needed to be written in a stable format that could support recurring dashboard refreshes.
Challenges
The pipeline needed to be reliable, scheduled, and reporting-ready.
- Data consistencyTransactions needed consistent fields and formats for dashboard use.
- SchedulingData needed to load automatically on a recurring cadence.
- Transformation logicRaw data needed cleaning and reshaping before visualization.
- Dashboard usabilityThe final data structure needed to support useful Looker Studio reports.
- Cross-table joinsThe pipeline needed to combine transactions with product, branch, category, manufacturer, listing, and ecommerce order context.
- Data quality checkThe output needed a basic validation step before writing reporting data.
- Operational reliabilityThe job needed metrics, observability, and controlled concurrency so reporting jobs did not overlap unexpectedly.
Solution
An AWS Glue ETL workflow for sales reporting.
I created an AWS Glue ETL job that reads operational data from the data catalog, joins the relevant business tables, filters out deleted transactions, and produces a single reporting dataset for sales visibility.
The transformed dataset includes transaction identifiers, branch details, product names, category and manufacturer context, listing status, pricing fields, ecommerce delivery fields, and the date the transaction was encoded.
The job writes a compressed Parquet output to reporting storage, with a basic data quality check and Glue observability enabled so the pipeline is easier to monitor and maintain.
System architecture
From operational tables to Looker Studio dashboards.
The architecture connects cataloged source tables, SQL transformation, data quality checks, reporting storage, and Looker Studio consumption.
Source tables
AWS Glue job
SQL joins
Data quality check
Parquet output
Looker Studio
Dashboard
Business results
Reporting became less dependent on manual data preparation.
- Reduced manual exportsRecurring data movement no longer depended on repeated staff action.
- Cleaner dashboardsLooker Studio received data in a more usable reporting format.
- Improved visibilityDecision-makers could review sales activity through a dedicated reporting layer.
- Better reporting cadenceThe managed ETL workflow created a more dependable update rhythm.
- Richer sales contextTransactions were enriched with branch, product, category, manufacturer, price, and ecommerce order details.
- More maintainable pipelineA managed AWS Glue job gave the reporting flow clearer monitoring and operational boundaries.
Technologies
The stack supported reliable reporting operations.
AWS GluePythonSQLData CatalogParquetS3Google Looker StudioData quality checks
Gallery
Real workflow proof from the reporting pipeline.
The screenshots show source data flowing through AWS Glue, SQL transformation, S3 output, and a sanitized Looker Studio dashboard.
AWS Glue job visual
The Glue job connects multiple cataloged source tables, runs the SQL transformation, and writes the reporting output to S3 for dashboard consumption.
Sanitized SQL transform
The transformation joins transaction, product, branch, category, manufacturer, listing, and ecommerce context into one reporting-ready dataset.
Reporting dataset output
The job writes generated Parquet files to reporting storage, creating a repeatable data source for dashboards instead of manual exports.
Sanitized Looker Studio dashboard
The dashboard consumes the automated dataset so decision-makers can inspect sales movement, branch performance, transactions, customers, and margin without rebuilding the data manually.
Why this matters
Dashboards only create value when the data behind them is dependable.
The solution turned repeated reporting preparation into a managed data pipeline that made sales visibility easier to maintain and more useful for dashboard decisions.
Have a workflow that feels too manual?
Book a free discovery call and we will identify whether automation can reduce repetitive work in your business.
Book My Free Discovery Call