[portfolio](https://geonwoo.kim/en/portfolio) › Biztalk

# KakaoTalk sending and template statistics collection server

https://geonwoo.kim/en/portfolio/kakao-stats

Period: 2025.05 — 2025.06

The server covers AlimTalk, FriendTalk and brand messages. The statistics table was denormalized around user IDs to speed up queries.

Sending statistics for over 8 million messages a month

Team: Solo

Role: DB and flow design · Batch scheduler · Aggregation API · Re-collection API · Data reprocessing scheduler

Java 17 · Spring Boot 3.5 · Spring Batch · MyBatis · MySQL

## Statistics that differ by elapsed day

Problem: Read and click statistics from the Kakao API arrive aggregated separately per elapsed day (0, 1, 2). Combining them in one pass produced wrong totals and broke data consistency.

Solution: Every day, the batch pulls all aggregated statistics from the Kakao API. Read and click values are accumulated in three tiers by elapsed days. The batch writes 500 rows at a time to speed up DB writes.

## Duplicate rows on re-collection

Problem: A manual re-collection API, added for cases where the scheduled batch missed data, made it possible to accumulate the same data several times.

Solution: In the denormalized final table, I built a composite key from the main columns, leaving out those that change. An elapsed\_mask column marks elapsed days 0, 1 and 2 as bits 1, 2 and 4. Accumulation checks these bits, so the same elapsed day is never added twice. Collecting the same data again creates no duplicate rows, and the statistics stay consistent.


[Server configuration management and deployment scripts](https://geonwoo.kim/en/portfolio/biztalk-ops-automation) · [AI-Native commerce run by agents](https://geonwoo.kim/en/portfolio/deepeet)