BigQuery data sharing

Whimful publishes a daily pageview stats table directly into your GCP project via BigQuery Analytics Hub. Storage stays on Whimful's side; queries you run are billed to your project.

How to subscribe

1. Your account admin enables sharing in account settings and shares the listing link with your engineering team.
2. Open the listing link in a browser signed in to your GCP project. Pick a destination dataset name.
3. Confirm. The linked dataset appears in your project under BigQuery.

Table reference — whimful_daily_pageview_stats

One row per (date, dimension combo) per day. Partitioned by event_date, clustered by host, path.

Example queries

Daily pageviews, last 30 days

SELECT event_date, SUM(pageviews) AS pageviews
FROM `<your-project>.<linked-dataset>.whimful_daily_pageview_stats`
WHERE event_date >= CURRENT_DATE() - 30
GROUP BY event_date
ORDER BY event_date;

Unique users per day (HLL)

SELECT event_date, HLL_COUNT.MERGE(users_hll) AS unique_users
FROM `<your-project>.<linked-dataset>.whimful_daily_pageview_stats`
WHERE event_date >= CURRENT_DATE() - 30
GROUP BY event_date
ORDER BY event_date;

By country, last 7 days

SELECT geo.region_code AS country,
       HLL_COUNT.MERGE(users_hll)    AS users,
       HLL_COUNT.MERGE(sessions_hll) AS sessions,
       SUM(pageviews)                AS pageviews
FROM `<your-project>.<linked-dataset>.whimful_daily_pageview_stats`
WHERE event_date >= CURRENT_DATE() - 7
GROUP BY country
ORDER BY users DESC;

p75 Web Vitals by page (KLL)

SELECT path,
       KLL_QUANTILES.MERGE_POINT_DOUBLE(cwv_metrics.lcp_kll, 0.75) AS lcp_p75_ms,
       KLL_QUANTILES.MERGE_POINT_DOUBLE(cwv_metrics.inp_kll, 0.75) AS inp_p75_ms,
       KLL_QUANTILES.MERGE_POINT_DOUBLE(cwv_metrics.cls_kll, 0.75) AS cls_p75,
       SUM(pageviews) AS pageviews
FROM `<your-project>.<linked-dataset>.whimful_daily_pageview_stats`
WHERE event_date >= CURRENT_DATE() - 7
GROUP BY path
HAVING pageviews >= 100
ORDER BY pageviews DESC
LIMIT 50;

UTM attribution with median active time on page

SELECT utm.source, utm.medium, utm.campaign,
       HLL_COUNT.MERGE(sessions_hll) AS sessions,
       KLL_QUANTILES.MERGE_POINT_DOUBLE(active_visit_duration_kll, 0.5) AS median_active_ms
FROM `<your-project>.<linked-dataset>.whimful_daily_pageview_stats`
WHERE event_date >= CURRENT_DATE() - 30
  AND utm.source IS NOT NULL
GROUP BY utm.source, utm.medium, utm.campaign
ORDER BY sessions DESC;

Exclude bot, VPN, and data-center traffic

SELECT event_date,
       SUM(pageviews)             AS pageviews,
       HLL_COUNT.MERGE(users_hll) AS unique_users
FROM `<your-project>.<linked-dataset>.whimful_daily_pageview_stats`
WHERE event_date >= CURRENT_DATE() - 30
  AND bot_name IS NULL
  AND COALESCE(bot_score, 0) < 4
  AND NOT COALESCE(is_vpn, FALSE)
  AND NOT COALESCE(is_data_center, FALSE)
GROUP BY event_date
ORDER BY event_date;

HLL and KLL primer

• HLL sketches let you count uniques at any rollup. Always use HLL_COUNT.MERGE(column); never SUM or COUNT on the BYTES column directly.
• KLL sketches store the full distribution. KLL_QUANTILES.MERGE_POINT_DOUBLE(col, q) returns the value at quantile q (e.g. 0.75 for p75). The standard convention for Web Vitals is p75.
• Both sketch types are mergeable: pre-aggregate, save the sketches, merge them again later. Approximate but bounded: HLL ≈ 1.5% error, KLL bounded relative error.

Gotchas

• Data lands at T+1, around 02:00 UTC. Yesterday's data appears around 02:00 UTC today.
• The most recent two days are always re-materialised, so values for those dates can shift slightly as late Web Vitals arrive.
• Path query strings are sanitised. Sensitive params (email, token, password, auth, session, key, secret) are stripped before storage.
• All timestamps and partitions are UTC.
• The bot_name column is a best-effort heuristic. It catches three patterns: the standard (compatible; <name>/<version>) Mozilla clause (Googlebot, bingbot, GPTBot, OAI-SearchBot, …), bots that announce a contact URL or email right after their identifier (facebookexternalhit, meta-externalads, AdsBot-Google, Applebot, ClaudeBot, …), and as a final fallback any token ending in "bot", "crawler", or "spider". For a stricter filter, combine it with bot_score < 4 (IP reputation), NOT is_vpn, and NOT is_data_center.
• The IP-classification columns (bot_score, is_vpn, is_data_center) are NULL for IPs we have not seen frequently enough to classify yet. Use COALESCE when filtering so unclassified traffic is not silently dropped.
• Storage cost is on Whimful; query bytes are billed to your project. Use partition filters (event_date >= …) to keep query cost low.