Many teams assume building custom attribution models requires specialized, expensive SaaS tooling. While those platforms have their place, I've found you can create remarkably sophisticated, first-party attribution logic directly from your GA4 BigQuery event stream. This is particularly powerful for dissecting search ad performance (Google Ads, Microsoft Ads) beyond the last-click default.
The core concept leverages the `session_id` and user `pseudo_id` stitched together in the export. You can trace a user's path across multiple touchpoints—including various paid search clicks—before a conversion. This allows you to assign fractional credit based on rules you define, such as position-based, time-decay, or even data-driven weightings.
A basic starting query to map sessions to campaigns might look like this:
```sql
WITH session_source_data AS (
SELECT
CONCAT(user_pseudo_id, '.', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS session_id,
TIMESTAMP_MICROS(event_timestamp) AS session_start_time,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign,
event_name
FROM `your_project.your_dataset.events_*`
WHERE event_name = 'session_start'
),
conversion_data AS (
SELECT
CONCAT(user_pseudo_id, '.', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS conversion_session_id,
TIMESTAMP_MICROS(event_timestamp) AS conversion_time,
event_name
FROM `your_project.your_dataset.events_*`
WHERE event_name IN ('purchase', 'generate_lead') -- Your key conversions
)
SELECT
c.conversion_session_id,
s.campaign,
s.source,
s.medium,
s.session_start_time
FROM conversion_data c
JOIN session_source_data s ON c.conversion_session_id = s.session_id
WHERE s.medium = 'cpc' AND s.source LIKE '%google%'
ORDER BY c.conversion_session_id, s.session_start_time;
```
This gives you the foundation—a list of all paid search sessions that culminated in a conversion. The real work begins in your transformation layer (e.g., dbt, Python). Here, you can:
* **Window functions** to sequence touchpoints for each user journey.
* **Apply your attribution logic** (e.g., 40% to the first click, 30% to the last, 30% distributed linearly).
* **Join back to cost data** from the ad platforms via the campaign ID to calculate true ROI per channel/tactic.
Key considerations from a deployment perspective:
* **Latency:** GA4 exports can have a 24-48 hour delay. This is for strategic analysis, not real-time bidding.
* **Data freshness:** Ensure your pipeline accounts for the streaming nature of the BigQuery export and deduplication.
* **Cost:** Querying the raw event table repeatedly is expensive. Materialize your core user journey table daily.
* **Compliance:** You own the data transformation logic, which can be audited for regulatory needs.
This approach isn't for every team—it requires solid SQL and pipeline skills. But for those with the capability, it unlocks a level of transparency and control over search attribution that black-box models simply cannot match. I'm curious if others have built similar systems and what specific logic you found most valuable for search campaigns.
- Mike
Mike