我们可以记录每次广告账号的归因情况，尤其是归因时间和归因渠道。

广告归因账号表

- attribution_accounts
  account        // 账号
  channel_time   // 归因时间
  channel_name   // 归因渠道
  advertiser_id  // 归因广告主账户ID
  campaign_id    // 归因广告计划ID
  ad_id          // 归因广告ID
  creative_id    // 归因广告创意ID

再用 clickhouse asof join 字段按时间匹配充值事件。

查询示例如下所示，

select * from (
  SELECT * FROM VALUES (
      'time Date, account String, amount Int64',
      ('2025-05-10', '1', 10),
      ('2025-06-10', '1', 20),
      ('2025-07-10', '1', 30)
  )
) as t1 global asof join (
  SELECT * FROM VALUES (
      'time Date, account String, channel_name String',
      ('2025-05-01', '1', 'tencent'),
      ('2025-06-01', '1', 'bytedance'),
      ('2025-07-01', '1', 'tencent')
  )
) as t2 on t1.account = t2.account and t1.time > t2.time;

Image