How SmartNews powers real-time ad performance dashboards with ClickHouse

SmartNews has transformed its ad performance analytics by migrating from a batch-processing system using Amazon Redshift to a real-time streaming architecture powered by ClickHouse, significantly improving query response times for advertisers on Japan's leading news app. This shift allows SmartNews to handle approximately 3 million queries and 60,000 data inserts per hour, with most queries completing in under 100 milliseconds. The transition involved replacing a multi-step batch process with a streamlined pipeline where ad delivery data is streamed through Kafka and processed by Flink before being ingested into ClickHouse. This new system allows advertisers to access real-time metrics such as budget spend and click-through rates almost instantaneously. SmartNews has also scaled its infrastructure by migrating from AWS EC2 to Kubernetes, enhancing flexibility and scalability. The use of ClickHouse’s uniqCombined function allows SmartNews to efficiently estimate ad reach by counting unique users through a compressed intermediate state, maintaining accuracy even as the number of unique users scales. This investment in ClickHouse has not only improved performance and simplicity but also provided a flexible foundation for future growth and innovation in their advertising platform.