# Ga4 Bigquery Ml Query

> >

- **Type:** Skill
- **Install:** `agentstack add skill-adswerve-ga4-bigquery-agent-skill-ga4-bigquery-ml-query`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [adswerve](https://agentstack.voostack.com/s/adswerve)
- **Installs:** 0
- **Category:** [Data & Analytics](https://agentstack.voostack.com/c/data-and-analytics)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [adswerve](https://github.com/adswerve)
- **Source:** https://github.com/adswerve/ga4-bigquery-agent-skill/tree/main/ga4-bigquery-ml-query

## Install

```sh
agentstack add skill-adswerve-ga4-bigquery-agent-skill-ga4-bigquery-ml-query
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# BigQuery ML Dataset Creation Skill

You are an expert at building ML-ready datasets in BigQuery using SQL. You correctly structure lookback/lookahead windows, prevent data leakage, create deterministic splits, and produce datasets that feed directly into training or inference pipelines.

## Required Context

Before writing a dataset query, confirm:
1. **ML objective** — what is predicted (purchase probability, future revenue, churn)
2. **Observation grain** — entity + moment (per-session, per-user per-week)
3. **Label definition** — positive outcome + future window duration
4. **Source tables** — BQ project, dataset, table names
5. **Window sizes** — lookback and lookahead durations

If unclear, ask before writing.

## Core Architecture

```
┌──────────────────────────────────────────────────┐
│  1. PARAMETERS   (windows, dates, mode)          │
│  2. ENTITY POOL  (eligible population filter)    │
│  3. BASE LAYER   (pre-aggregated raw data)       │
│  4. LABELS       (target from lookahead window)  │
│  5. FEATURES     (join + engineer features)      │
│  6. OUTPUT       (split + materialize)           │
└──────────────────────────────────────────────────┘
```

→ [references/dataset-architecture.md](./references/dataset-architecture.md)

## Lookback & Lookahead Windows

| Window | Purpose |
|--------|---------|
| **Lookback** | Gather features from historical behavior before observation point |
| **Lookahead** | Observe the outcome after observation point |

**CRITICAL**: Source data range must extend BOTH directions:

```sql
WHERE event_date BETWEEN date_start - LOOKBACK_DAYS AND date_end + LOOKAHEAD_DAYS
-- NOT just date_start to date_end (truncates edge users → systematic bias)
```

→ [references/dataset-architecture.md](./references/dataset-architecture.md)

## Entity Pool

Filters eligible entities — removes noise, ensures data quality:

```sql
user_pool AS (
  SELECT user_id, MAX(activity_date) as last_activity_date
  FROM source_table
  WHERE activity_date BETWEEN date_start - LOOKBACK_DAYS AND date_end
  GROUP BY user_id
  HAVING
    last_activity_date BETWEEN date_start AND date_end
    AND COUNT(*) > threshold
)
```

Typical criteria: minimum sessions/events, activity within range, exclude bots, business filters (e.g. prior purchase for LTV).

## Snapshot Approach (Multiple Observation Points)

For user-level predictions, use multiple snapshot dates (e.g. every Monday) instead of a single cutoff — captures every lifecycle phase:

```sql
SET snapshot_dates = ARRAY(
  SELECT d FROM UNNEST(GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 DAY)) d
  WHERE EXTRACT(DAYOFWEEK FROM d) = 2
);
```

**Correlated rows mitigation**: Sample one per user (`QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY FARM_FINGERPRINT(user_id || '/' || date)) = 1`) or cap at N snapshots.

→ [references/dataset-architecture.md](./references/dataset-architecture.md)

## Data Leakage Prevention

| Rule | Implementation |
|------|----------------|
| Features: lookback only | `WHERE date BETWEEN snapshot - LOOKBACK AND snapshot` |
| Label: lookahead only | `WHERE date BETWEEN snapshot + 1 AND snapshot + LOOKAHEAD` |
| Training: full lookahead | `WHERE date 0, ts, NULL)) OVER (...), HOUR), sentinel)` |
| Time-windowed agg | `SUM(IF(DATE_DIFF(d, date, DAY) BETWEEN 0 AND 28, x, 0))` |
| Site content feature | `COUNTIF(event_name='page_view' AND LOWER(page_location) LIKE '%pattern%')` |
| Geo/traffic one-hot | `MAX(IF(geo.region = 'California', 1, 0))` |
| Event params extraction | `MAX(IFNULL((SELECT ep.value.int_value FROM UNNEST(event_params) ep WHERE ep.key='key'), 0))` |
| Ratio/intensity | `SAFE_DIVIDE(SUM(x), SUM(y))` |
| Derived score | `(pageviews + engagements + scrolls) as engagement_score` |

→ [references/feature-engineering.md](./references/feature-engineering.md)

## Deterministic Split

```sql
CASE
  WHEN MOD(ABS(FARM_FINGERPRINT(user_id)), 100) < 70 THEN 'TRAIN'
  WHEN MOD(ABS(FARM_FINGERPRINT(user_id)), 100) < 90 THEN 'EVAL'
  ELSE 'TEST'
END as data_split
```

Deterministic, entity-level, reproducible, no random seed.

→ [references/training-and-inference.md](./references/training-and-inference.md)

## Training vs Inference

| Aspect | Training | Inference |
|--------|----------|-----------|
| Label | Included | Excluded |
| data_split | Included | Not needed |
| Identifiers | Excluded from features | Included |
| Snapshots | Multiple (weekly) | Single (latest) |
| Expiration | 7-90 days | 1 day |

→ [references/training-and-inference.md](./references/training-and-inference.md)

## Observation Grain

- **Session-level** (one row per session): Real-time propensity, next-action. Each session is the observation point.
- **User-level snapshots** (one row per user per date): LTV, churn, periodic scoring.

Both follow: pool → base → label → features → output.

## GA4-Specific Patterns

- Entity: `user_pseudo_id` (or `user_id` if set)
- Session key: `user_pseudo_id || '/' || event_date || '/' || ga_session_id`
- Cost control: `_TABLE_SUFFIX` filtering
- Labels: purchase in next N days, premium subscription, revenue
- Site content features via `page_location` LIKE/REGEXP
- Traffic source via `session_traffic_source_last_click.cross_channel_campaign.medium`
- Geo/device/platform one-hot encoding
- Event params: `engaged_session_event`, `percent_scrolled`, custom params
- External enrichment: holiday proximity, user lookup tables

→ [references/ga4-examples.md](./references/ga4-examples.md)

## Key Warnings

1. **Extend source read range**: `date_start - LOOKBACK_DAYS` to `date_end + LOOKAHEAD_DAYS`
2. **Split on entity ID**, never on rows
3. **Filter training to full lookahead**: `WHERE date <= date_end - LOOKAHEAD_DAYS`
4. **SAFE_DIVIDE** for ratios, **IFNULL** for missing values
5. **Exclude identifiers/timestamps** from training features
6. **Correlated snapshots**: sample or cap per user
7. **Conditional labels**: define business-specific conversion precisely (e.g. purchase WHERE program='AUTO' AND product NOT LIKE '%accessory%')

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [adswerve](https://github.com/adswerve)
- **Source:** [adswerve/ga4-bigquery-agent-skill](https://github.com/adswerve/ga4-bigquery-agent-skill)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-adswerve-ga4-bigquery-agent-skill-ga4-bigquery-ml-query
- Seller: https://agentstack.voostack.com/s/adswerve
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.