Campaign Stat Calculation

Overview

Once a campaign has been sent, the system has to report how many people it reached, how many read and clicked, and which link got clicked the most.

This job is the consumer that computes those numbers from the tracking_log table and writes the results back onto the campaign table, so the CMS can display them immediately without re-aggregating on every query.

The calculation can be triggered two ways: (1) an hourly cron that sweeps campaigns flagged dirty in Redis, and (2) a direct publish to the queue when a specific campaign needs recalculating on demand.

Business Flow

Scanner side (cron on the cron-scheduler profile)

1. Every hour, on the 0 * * * * cron, CampaignStatScannerService scans Redis for keys shaped like CAMPAIGN_STAT_DIRTY:<campaignId> — these keys get set by the tracking path every time a click or read comes in
2. It extracts the campaign id from the key name and publishes one job per campaign onto the calculate_campaign_stat queue
3. If no dirty keys are found at all, it skips that round entirely, to avoid wasting a database query for nothing

Consumer side (profile main)

1. It receives a payload with campaignId and date fields, both optional. If campaignId is omitted, it recalculates every campaign with status sent that's still within its tracking period, which defaults to 90 days from start_date
2. calculateCampaignStats computes aggregate numbers from tracking_log:
   • totalRecipient — the recipient count; for broadcast campaigns this uses the follower count from the LINE Insight API
   • totalReach — the count of distinct line_uid values with any activity
   • totalActivity — the total activity count
   • totalUniqueClick — the count of distinct line_uid values with action_type = click
   • totalFirstClick — the count of first clicks for events where type is uri or postback
   • Every number filters on line_uid being non-null and not equal to -
3. summarizeCampaignStats rolls up per-link and per-image numbers into the template_tracking column, split by type (flex, tappableImage, image, video), joined against rich_message to identify what each link index actually is
   • Read counts come from asset-type events; click counts come from uri and postback events
4. It writes all the results back onto the campaign table, both the stat columns and template_tracking

Key Files & Functions

• internal/campaign/campaign.go
  • Service.ProcessCalculateCampaignStat(ctx, payload) — the consumer's entry point
  • calculateCampaignStats() and getLineFollowerCount()
  • The constant defaultCampaignTrackingPeriodDays is 90
  • The CampaignStatPayload struct, with CampaignID and Date fields
• internal/campaign/stats.go
  • countUniqueLineUID(), countActivity(), countFirstClick()
  • summarizeCampaignStats(), queryClicks(), queryAssetReads()
• internal/campaign/consumer.go — Consumer.HandleCalculateCampaignStat
• internal/cronscheduler/campaign_stat_scanner.go — CampaignStatScannerService.Run(ctx) and the constant campaignDirtyKeyPrefix
• Queue: consumes calculate_campaign_stat on profile main, published by the cron on profile cron-scheduler

Connections to Other Services

• The tracking_log table — the raw data source for everything, using columns campaign_id, line_uid, action_type, type, rich_message_index, and created_date
• The campaign table — where results are written, and rich_message — read to map indexes back to their links
• Redis — the CAMPAIGN_STAT_DIRTY:* keys mark which campaigns have new data to compute
• LINE API — GET /v2/bot/insight/followers to get totalRecipient for broadcast campaigns
• Consumer of the results — cms-api-go's campaign-management domain, which renders the campaign report page