IN THIS ARTICLE YOU WILL
Introduction
Convert.com sends valuable experimentation data to Google Analytics 4 (GA4) through the experience_impression event. This article demonstrates how to leverage Looker to query this data and create insightful reports comparing different test variations.
Understanding the Data Structure
Convert.com's experience_impression event contains a crucial parameter:
-
Event:
experience_impression -
Event Parameter:
exp_variant_string -
Format:
CONV-{experiment_id}-{variation_id} -
Example:
CONV-100018331-100099919-
Experiment ID: 100018331
-
Variation ID: 100099919
-
Setting Up Your Looker Query
Step 1: Basic Event Filter
Start by filtering for the experience impression events in your Looker explore:
-- Base filter for Convert.com events
WHERE event_name = 'experience_impression'
AND EXISTS(SELECT 1 FROM UNNEST(event_params) WHERE key = 'exp_variant_string')
Step 2: Extracting Experiment and Variation IDs
Create calculated fields to parse the exp_variant_string:
-- Extract Experiment ID
REGEXP_EXTRACT(
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = 'exp_variant_string'),
r'CONV-(\d+)-\d+'
) AS experiment_id
-- Extract Variation ID
REGEXP_EXTRACT(
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = 'exp_variant_string'),
r'CONV-\d+-(\d+)'
) AS variation_id
Step 3: Complete Query Structure
Here's a comprehensive query for analyzing two variations:
SELECT
-- Time dimensions
PARSE_DATE('%Y%m%d', event_date) as date,
-- Extract experiment details
REGEXP_EXTRACT(
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = 'exp_variant_string'),
r'CONV-(\d+)-\d+'
) AS experiment_id,
REGEXP_EXTRACT(
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = 'exp_variant_string'),
r'CONV-\d+-(\d+)'
) AS variation_id,
-- Create variation labels (adjust variation IDs to match your experiments)
CASE
WHEN REGEXP_EXTRACT(
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = 'exp_variant_string'),
r'CONV-\d+-(\d+)'
) = '100099919' THEN 'Control'
WHEN REGEXP_EXTRACT(
(SELECT value.string_value
FROM UNNEST(event_params)
WHERE key = 'exp_variant_string'),
r'CONV-\d+-(\d+)'
) = '100099920' THEN 'Variation A'
ELSE 'Other'
END AS variation_label,
-- Metrics
COUNT(*) as impressions,
COUNT(DISTINCT user_pseudo_id) as unique_users,
-- User properties
user_pseudo_id,
device.category as device_category, -- May need adjustment based on your schema
geo.country as country -- May need adjustment based on your schema
FROM `your_project.your_dataset.events_*` -- Replace with your actual table path
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND event_name = 'experience_impression'
AND EXISTS(
SELECT 1 FROM UNNEST(event_params)
WHERE key = 'exp_variant_string'
)
GROUP BY 1,2,3,4,5,6,7
ORDER BY date DESC
Creating a Two-Variation Comparison Report
Report Structure in Looker
Dimensions to Include:
-
Date (for trend analysis)
-
Variation Label
-
Device Category (verify field structure)
-
Geographic data (verify field structure)
Metrics to Track:
-
Total Impressions
-
Unique Users Exposed
-
Impression Rate by Device
-
Geographic Distribution
Sample Looker Explore Configuration
LookML Example:
dimension: variation_label {
type: string
sql:
CASE
WHEN ${variation_id} = '100099919' THEN 'Control'
WHEN ${variation_id} = '100099920' THEN 'Variation A'
ELSE 'Other'
END ;;
}
measure: total_impressions {
type: count
label: "Experience Impressions"
}
measure: unique_users_exposed {
type: count_distinct
sql: ${user_pseudo_id} ;;
label: "Unique Users Exposed"
}
measure: avg_impressions_per_user {
type: number
sql: ${total_impressions} / NULLIF(${unique_users_exposed}, 0) ;;
value_format: "#,##0.00"
}
Advanced Analysis Techniques
1. Conversion Tracking
Link impression data with conversion events:
WITH impressions AS (
-- Your impression query here
),
conversions AS (
SELECT
user_pseudo_id,
event_timestamp,
event_name as conversion_event
FROM `your_project.your_dataset.events_*`
WHERE event_name IN ('purchase', 'sign_up', 'custom_conversion')
)
SELECT
i.*,
CASE WHEN c.user_pseudo_id IS NOT NULL THEN 1 ELSE 0 END as converted
FROM impressions i
LEFT JOIN conversions c
ON i.user_pseudo_id = c.user_pseudo_id
AND c.event_timestamp > i.event_timestamp
2. Time-Based Analysis
Create cohort analysis by impression date:
SELECT
date,
variation_label,
COUNT(*) as daily_impressions,
SUM(COUNT(*)) OVER (
PARTITION BY variation_label
ORDER BY date
ROWS UNBOUNDED PRECEDING
) as cumulative_impressions
FROM your_impression_data
GROUP BY 1,2
ORDER BY 1,2
Best Practices for Looker Reports
-
Data Validation:
Run:
SELECT * FROM `your_project.your_dataset.events_*` WHERE event_name = 'experience_impression' LIMIT 10
-
Table Structure Verification:
-
Device info fields
-
Geographic fields
-
User properties
-
-
Data Freshness: Use scheduled refreshes
-
Filtering Options: Date range, experiment, device, geography
-
Visualization:
-
Line charts = Trends
-
Bar charts = Comparisons
-
Tables = Details
-
-
Performance Optimization:
-
Use date partitioning
-
Aggregate tables
-
Persistent derived tables (PDTs)
-
Implementation Checklist
- [ ] Verify your GA4 BigQuery export table naming convention
- [ ] Test SQL queries against your actual data structure
- [ ] Confirm device and geographic field structures in your schema
- [ ] Adjust LookML syntax based on your Looker instance configuration
- [ ] Validate that your conversion event names match your tracking setup
- [ ] Test dashboard performance with realistic date ranges
Example Dashboard Layout
Conclusion
By following this approach, you can effectively query and analyze Convert.com's experience impression data in Looker. This setup enables:
-
Monitoring experiment exposure
-
Behavioral insights across variations
-
Conversion tracking
-
Stakeholder reporting
Always validate schema compatibility before production deployment. This methodology is a strong foundation for advanced experimentation analysis.