Executive Summary:
A digital media analytics platform relied on manual Power BI reporting as mobile usage data grew. Rapyder moved its DAX logic into a serverless AWS data pipeline using S3, Glue, Delta Lake, Athena, Lambda and API Gateway. Incremental processing reduced repeated historical work, while API-delivered metrics were validated against existing Power BI outputs.
About Customer:
The customer helps brands plan, activate and measure digital media and e-commerce campaigns. Its platform provides consumer insights, audience activation and panel surveys for CPG, gaming, OTT, BFSI and mobile phone brands, using a privacy-first approach to consumer behaviour analysis.
Problem Statement:
The platform’s metric logic sat in Power BI DAX formulas and relied on manual reporting. Growing mobile usage data made repeated historical processing inefficient, while fragmented definitions made installs, monthly active users and engagement hard to compare across apps and regions. Brand clients needed timely, consistent insights in a privacy-first environment.
Industry:
Industries, Media & Entertainment
Offering:
Offerings, Data Analytics
Business Challenges:
- Privacy-first measurement: As traditional consumer tracking became less effective, the platform needed a privacy-first framework based on aggregated app usage data.
- Dashboard-bound metrics: Metric logic lived in manual Power BI DAX formulas, making it difficult to scale reporting as the business and its brand client base grew.
- Growing event volume: Increasing volumes of granular mobile usage events made it inefficient to reprocess historical data whenever new data arrived.
- Inconsistent definitions: Metrics were fragmented across apps, categories and geographies, with no standard way to calculate installs, monthly active users and engagement across filters.
- Dashboard delivery: Brand clients expected low-latency, dashboard-ready insights that the manual reporting approach struggled to deliver at scale.
Solution Implemented:
- Partitioned S3 data lake: Stored mobile app usage CSV data in Amazon S3, partitioned by application, date and device attributes.
- Schema discovery: Used AWS Glue Crawlers to detect schema changes and maintain the Glue Data Catalog across raw and curated datasets.
- Incremental ETL: Built PySpark-based AWS Glue jobs to normalise timestamps, derive sessions and calculate weighted metrics. Job bookmarks limited routine processing to newly ingested data.
- Curated Delta Lake storage: Wrote curated output to Amazon S3 in Delta Lake format to support ACID transactions and consistent incremental updates.
- DAX-to-SQL conversion: Converted existing Power BI DAX formulas into Amazon Athena SQL and validated the resulting metrics against the legacy dashboard outputs.
- Dashboard API layer: Used AWS Lambda to build parameterised Athena queries for Month, State, NCCS, Gender, Category and App filters. Amazon API Gateway exposed secure REST endpoints for dashboard consumption.
- Controlled rollout: Deployed and validated the architecture in Development and Production environments for a controlled rollout.
Technology Stack:
- Amazon S3
- AWS Glue
- Amazon Athena
- AWS Lambda
- Amazon API Gateway
Business Benefits:
- App onboarding without pipeline redesign – The serverless S3, Glue, Athena and Lambda architecture supported new app data without rebuilding the core pipeline for each app.
- Less repeated historical processing – Glue job bookmarks and partitioned data supported incremental ETL, avoiding a full historical scan during routine processing.
- Interactive dashboard delivery – Pre-aggregated datasets and optimised Athena SQL supported interactive access to the platform’s metrics.
- Consistent reporting – Automated ETL, centralised metadata and monitored query execution across Development and Production helped keep metrics reliable and reproducible.
- Lower query scan overhead – Partition pruning, columnar data storage in Delta Lake and pay-per-use AWS services reduced unnecessary data scanning and helped control query costs.
- Validated metric parity – Converted Athena SQL queries were checked against existing Power BI DAX outputs to validate that the migrated calculations matched the legacy metrics.
Still relying on manual Power BI reporting?
Automate reporting with a serverless AWS data pipeline.
Key Takeaways:
- Reusable metric logic – Moving the existing DAX calculations into Athena SQL made the metric layer available through APIs, beyond an individual dashboard.
- Incremental processing – Glue bookmarks and partitioned data limited repeated historical processing as new mobile usage data arrived.
- Output validation – Comparing migrated SQL results with legacy Power BI outputs helped preserve the metric definitions business users relied on.
Frequently Asked Questions
In this platform, metric logic sat inside Power BI and depended on manual reporting. Converting the existing DAX calculations into Amazon Athena SQL made those definitions available through APIs to other dashboards and applications. The team validated the SQL results against the legacy Power BI outputs.
Growing mobile usage data made repeated historical scans inefficient. AWS Glue job bookmarks tracked processing progress, while partitioned data allowed routine ETL runs to focus on newly ingested data. This reduced the need to recompute the full historical dataset for each update.
The platform needed curated metrics to remain consistent as new data arrived. Delta Lake added an ACID transaction layer to curated data in Amazon S3, supporting reliable incremental writes and updates. It formed the storage layer for the refined analytics datasets.
Brand dashboards needed consistent metrics across different audience and app filters. AWS Lambda built parameterised Amazon Athena queries from the requested filter values, and Amazon API Gateway served the results through REST endpoints. Curated datasets and optimised SQL supported interactive dashboard use.
