Spaces:
Sleeping
Sleeping
File size: 4,593 Bytes
0555d94 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 |
## A/B Normalization SQL Prompt Guide (For Model Inference)
This guide outlines how to instruct a model to write Snowflake SQL for normalized A/B test comparisons using the ANALYTICS.ADCODE_DA.AB_GRANULAR table.
---
## Goal
ALWAYS Generate a SQL query that:
- Filters the table by a given ab_key and a specific date
- Aggregates total impressions and revenue per ab_value
- Normalizes impressions and revenue by dividing each by its allocation percentage
- Selects the control group per segment as the ab_value with the highest number of impressions
- Computes each variant’s performance ratio to control for both normalized revenue and impressions
- Filters out low-volume rows (e.g., where total_imps < 10000)
---
Dimensions
## The model must support dynamically adding or removing dimensions based on user input.
Supported dimensions include:
- device
- browser_clean
- format
- topadunitname
- refresh_num
- lazy
## When dimensions are included in the user’s request, they must also be:
- Included in the SELECT and GROUP BY clauses
- Added to the PARTITION BY clause when determining the control
- Used in the JOIN condition when aligning experimental rows with their corresponding control group
---
## Logic Flow
1. Filter the data for the specific AB test key and date.
2. Group by the selected dimensions and ab_value.
3. Aggregate total impressions and total revenue.
4. Normalize:
- normalized_imps = total_imps / percentage
- normalized_rev = total_rev / percentage
5. Determine control per segment:
- Use ROW_NUMBER() partitioned by the selected dimensions (e.g., device, browser_clean)
- Order by total_imps DESC
- Select the first row as the control
6. Join the normalized data back to the control data using the same dimensions
7. Compute ratios to control:
- (normalized_rev / control_rev) - 1
- (normalized_imps / control_imps) - 1
8. Sort by revenue ratio to control, descending
---
## SQL Syntax Considerations
- Use CAST(PERCENTAGE AS FLOAT) before division
- Use HAVING SUM(IMPS) >= 10000 to filter out low-volume variants
- Ensure consistent aliasing (e.g., use n for normalized data, c for control)
- Always match segment dimensions exactly in the JOIN (e.g., n.device = c.device)
## Output Expectation
A single Snowflake SQL query that:
- Adheres to the logic above
- Dynamically includes any dimensions mentioned in the request
- Is clean, readable, and production-ready
## Important
- Please ensure that the 'View Data Analysis & Insights' highlights the revenue and impression lift for the experiment values and flags outliers, if any.
- **Default** If a user does not request specific dimensions, please portray topline or aggregate data, please only include the 'AB_KEY' and 'AB_VALUE' and aggregate the data over that timeframe
- Always filter out low-volume rows HAVING SUM(IMPS) >= 10000
- Always ensure you normalize the data so that there are columns portraying the lift % for the metrics (i.e imps, rev, etc.).
## Example query
WITH base_data AS (
SELECT
AB_VALUE,
DEVICE,
BROWSER_CLEAN,
CAST(PERCENTAGE AS FLOAT) AS percentage,
SUM(IMPS) AS total_imps,
SUM(REV) AS total_rev
FROM ANALYTICS.ADCODE_DA.AB_GRANULAR
WHERE AB_KEY = 'cnftp'
AND DATE = '2025-04-01'
GROUP BY AB_VALUE, DEVICE, BROWSER_CLEAN, PERCENTAGE
HAVING SUM(IMPS) >= 10000
),
normalized AS (
SELECT
AB_VALUE,
DEVICE,
BROWSER_CLEAN,
total_imps,
total_rev,
percentage,
(total_imps / percentage) AS norm_imps,
(total_rev / percentage) AS norm_rev
FROM base_data
),
ranked_controls AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY DEVICE, BROWSER_CLEAN
ORDER BY total_imps DESC
) AS rn
FROM normalized
),
control AS (
SELECT
DEVICE,
BROWSER_CLEAN,
AB_VALUE AS control_ab_value,
norm_imps AS control_norm_imps,
norm_rev AS control_norm_rev
FROM ranked_controls
WHERE rn = 1
)
SELECT
n.AB_VALUE,
n.DEVICE,
n.BROWSER_CLEAN,
n.total_imps,
n.total_rev,
n.percentage,
ROUND(n.norm_imps, 2) AS norm_imps,
ROUND(n.norm_rev, 2) AS norm_rev,
ROUND(n.norm_imps / c.control_norm_imps, 4) - 1 AS norm_imps_ratio_to_control,
ROUND(n.norm_rev / c.control_norm_rev, 4) - 1 AS norm_rev_ratio_to_control
FROM normalized n
JOIN control c
ON n.DEVICE = c.DEVICE
AND n.BROWSER_CLEAN = c.BROWSER_CLEAN
ORDER BY n.DEVICE, n.BROWSER_CLEAN, norm_rev_ratio_to_control DESC;
|