Log analysis

Backend log fields (PAI-Rec)

Each structured backend log record contains the following fields:

Frontend behavioral log fields

Frontend behavioral logs typically come from client-side instrumentation or reporting. Each record contains the following fields:

Impression and click ratio

What it measures: Each channel's share of total impressions and clicks across all channels.

Formula: impression ratio = channel impressions / total impressions across all channels x 100%

In multi-channel recall, the same item can be returned by more than one channel simultaneously, so the sum of impression ratios across all channels can exceed 100%. This is expected behavior. CTR calculations are not affected.

-- Impression and click ratio per retrieval channel
WITH recall_stats AS (
  SELECT
    recall_name,
    SUM(is_expose) AS expose_cnt,
    SUM(is_click)  AS click_cnt
  FROM merged_data  -- Result from the join step
  GROUP BY recall_name
),
total_stats AS (
  SELECT
    SUM(expose_cnt) AS total_expose,
    SUM(click_cnt)  AS total_click
  FROM recall_stats
)
SELECT
  r.recall_name,
  r.expose_cnt,
  r.click_cnt,
  ROUND(r.expose_cnt * 100.0 / t.total_expose, 2) AS expose_ratio_pct,
  ROUND(r.click_cnt  * 100.0 / t.total_click,  2) AS click_ratio_pct
FROM recall_stats r
CROSS JOIN total_stats t
ORDER BY r.expose_cnt DESC;

What to do with the result: A high impression ratio means the channel dominates the recommendation surface. Pair this metric with CTR (see below) to decide whether that dominance is earning its place.

Exclusive rate

What it measures: The share of a channel's impressions and clicks that come from items no other channel returned in the same request — a proxy for the channel's unique contribution.

Formula: exclusive impression rate = exclusive impressions / total impressions for the channel x 100%. An item is "exclusive" when recall_count = 1 (only one channel returned it for that request).

-- Exclusive rate per retrieval channel
-- Step 1: Count how many channels returned each (request, item) pair
WITH item_recall_count AS (
  SELECT
    request_id,
    item_id,
    COUNT(DISTINCT recall_name) AS recall_count
  FROM rec_with_recall
  GROUP BY request_id, item_id
),
-- Step 2: Join behaviors and mark items returned by only one channel
merged_with_exclusive AS (
  SELECT
    r.recall_name,
    r.request_id,
    r.item_id,
    b.is_expose,
    b.is_click,
    CASE WHEN c.recall_count = 1 THEN 1 ELSE 0 END AS is_exclusive
  FROM rec_with_recall r
  JOIN behavior_with_type b
    ON r.request_id = b.request_id AND r.item_id = b.item_id
  JOIN item_recall_count c
    ON r.request_id = c.request_id AND r.item_id = c.item_id
)
-- Step 3: Calculate exclusive impression and click rates
SELECT
  recall_name,
  SUM(CASE WHEN is_expose = 1 AND is_exclusive = 1 THEN 1 ELSE 0 END) AS exclusive_expose,
  SUM(is_expose) AS total_expose,
  ROUND(
    SUM(CASE WHEN is_expose = 1 AND is_exclusive = 1 THEN 1 ELSE 0 END) * 100.0 /
    NULLIF(SUM(is_expose), 0), 2
  ) AS expose_exclusive_rate_pct,
  SUM(CASE WHEN is_click = 1 AND is_exclusive = 1 THEN 1 ELSE 0 END) AS exclusive_click,
  SUM(is_click) AS total_click,
  ROUND(
    SUM(CASE WHEN is_click = 1 AND is_exclusive = 1 THEN 1 ELSE 0 END) * 100.0 /
    NULLIF(SUM(is_click), 0), 2
  ) AS click_exclusive_rate_pct
FROM merged_with_exclusive
GROUP BY recall_name
ORDER BY total_expose DESC;

What to do with the result: A high exclusive rate means the channel surfaces content other channels miss. Even a channel with a low impression ratio can be worth keeping if its exclusive rate is high — it may be the only path to certain long-tail items.

Click-through rate (CTR)

What it measures: The quality of a channel's recommendations.

Formula: CTR = clicks / impressions x 100%. NULLIF(SUM(is_expose), 0) prevents division by zero when a channel has no impressions.

-- CTR per retrieval channel
SELECT
  recall_name,
  SUM(is_expose) AS expose_cnt,
  SUM(is_click)  AS click_cnt,
  ROUND(
    SUM(is_click) * 100.0 / NULLIF(SUM(is_expose), 0), 2
  ) AS ctr_pct  -- Unit: %
FROM merged_data
GROUP BY recall_name
ORDER BY ctr_pct DESC;

What to do with the result: A high CTR indicates users find the channel's items relevant. A channel can be valuable even with a low impression ratio if its CTR is high — increase its weight or item quota to give it more surface area.

Why does the sum of impression ratios exceed 100%?

In multi-channel recall, the same item can be returned by multiple channels simultaneously. For example, item_1001 might appear in both savana_in and u2i. Because each channel claims that impression, the ratios stack up beyond 100%. This is by design and does not affect CTR calculations.

Why is CTR NULL or 0?

Check for these causes in order:

1. The channel has no recorded click events in the time window — verify that valid is_click = 1 rows exist in merged_data.

2. Behavioral logs are delayed and click events have not arrived yet — re-run the query after the expected reporting delay.

3. The join on request_id or item_id failed due to a type mismatch or partition filter — inspect field types, partition conditions, and the time window used in the join.

Why is exclusive rate high but CTR low?

The channel likely focuses on long-tail or cold-start items such as new products or niche content that are unique but not yet proven popular. Before disabling it, evaluate with additional metrics — CVR, time on site, and diversity — to determine whether it serves users who are harder to reach with standard signals.