BigQuery and Snowflake

Get Punchline's numbers into your warehouse every day, next to the rest of your data, with no API code to maintain. Each morning Punchline writes the previous UTC day into four tables in a BigQuery dataset or Snowflake schema you choose.

Set it up in Site settings → Warehouse export. You need to be an owner or admin of the site.

What's written

Four tables, one schema everywhere (the same as exports):

Table	One row per
punchline_traffic_daily	Day × page × visitor type × agent and source
punchline_conversions_daily	Day × goal × who converted × channel × landing page
punchline_events_daily	Day × custom event × who sent it
punchline_agent_sessions	Agent or crawler session, with its pages, rules and evidence

The same privacy rules apply as for the API: groups with fewer than 5 people's page views are withheld, and people's sessions are never exported. Agent sessions are exported in full.

In Snowflake, table and column names are upper case (PUNCHLINE_TRAFFIC_DAILY.SESSIONS).

When it runs

• Every day at about 08:00 UTC, for the previous UTC day.
• Each day replaces that day's rows (BigQuery replaces the day's partition; Snowflake deletes the day, then inserts), so a rerun never duplicates anything.
• Missed days are caught up, up to 7 days back. If a run fails, the error shows next to the destination, and the next run starts from the first missing day.
• Sync now writes the missing days immediately. Test checks the connection without writing.
• Pause stops the daily run without removing the destination.

BigQuery

Punchline writes with its own service account, which you give access to one dataset only. No key or password is exchanged.

1. In BigQuery, create a dataset for Punchline (for example punchline), in the location you want.
2. Open the dataset → Sharing → Permissions → Add principal. Add the service account shown in Punchline's Warehouse export card, with the role BigQuery Data Editor. Grant it on the dataset, not the project, so Punchline can't see anything else.
3. Open the dataset → Edit details → Labels → Add label. Set the key to punchline-site and the value to your site ID (shown in the Warehouse export card). Every Punchline customer shares datasets with the same service account, so the label is how Punchline knows the dataset is yours: only someone who can edit the dataset can set it, and Punchline never sets labels itself.
4. In Punchline, enter the project ID and dataset ID and press Connect BigQuery. Punchline checks it can see the dataset and that the label matches before saving, and checks the label again before every daily sync. If the label is removed or changed, syncing stops and the card shows why.

Tables are partitioned by day, so filter on day to keep queries cheap. Load jobs run in Punchline's project. You pay BigQuery's usual rates for storing the tables and for the queries you run on them.

Snowflake

Punchline signs in as a service user with a key pair it creates for your destination. You run one script to create that user.

1. In Punchline, press Add Snowflake and enter:
   • Account identifier, like myorg-myaccount (from Snowflake's account menu → View account details).
   • User to create (default PUNCHLINE), Database, Schema (default PUNCHLINE), Warehouse (a small one is plenty), and optionally a Role (default PUNCHLINE_WRITER).
2. Punchline shows the setup SQL. Run it once in a Snowflake worksheet as a role that can create users and grant privileges, such as SECURITYADMIN. It creates the role and the service user, grants the role use of the warehouse and database and permission to create tables in the schema, and sets Punchline's public key on the user.
3. Press Test, then Sync now.

Loading runs on the warehouse you named, for a few seconds a day, and Snowflake bills that time as usual. An X-Small warehouse with auto-suspend is plenty.

The private key is generated by Punchline, stored encrypted, and never shown or sent anywhere except to sign Snowflake logins. To revoke access, drop the user or unset its public key in Snowflake, then remove the destination.

The script looks like this, with your names:

-- Run as a role that can create users and grant privileges (e.g. SECURITYADMIN).
CREATE ROLE IF NOT EXISTS PUNCHLINE_WRITER;
GRANT USAGE ON WAREHOUSE LOADING_WH TO ROLE PUNCHLINE_WRITER;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE PUNCHLINE_WRITER;
GRANT USAGE, CREATE TABLE ON SCHEMA ANALYTICS.PUNCHLINE TO ROLE PUNCHLINE_WRITER;
CREATE USER IF NOT EXISTS PUNCHLINE TYPE = SERVICE DEFAULT_ROLE = PUNCHLINE_WRITER DEFAULT_WAREHOUSE = LOADING_WH;
ALTER USER PUNCHLINE SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqh…';
GRANT ROLE PUNCHLINE_WRITER TO USER PUNCHLINE;

Example queries

Agent share by week (BigQuery):

SELECT
  DATE_TRUNC(day, WEEK(MONDAY)) AS week,
  SUM(IF(visitor_type IN ('agent', 'crawler'), pageviews, 0)) / SUM(pageviews) AS agent_share
FROM punchline.punchline_traffic_daily
WHERE day >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 WEEK)
GROUP BY week
ORDER BY week;

Pages each AI company read most in the last 30 days (Snowflake):

SELECT operator, f.value::string AS path, COUNT(*) AS sessions
FROM PUNCHLINE.PUNCHLINE_AGENT_SESSIONS, LATERAL FLATTEN(input => PARSE_JSON(PATHS)) f
WHERE DAY >= DATEADD(day, -30, CURRENT_DATE())
GROUP BY 1, 2
ORDER BY sessions DESC
LIMIT 50;

Conversions by people sent from AI assistants, joined to your own revenue table (BigQuery):

SELECT c.day, SUM(c.conversions) AS ai_referred_conversions, ANY_VALUE(r.revenue) AS revenue
FROM punchline.punchline_conversions_daily c
LEFT JOIN finance.daily_revenue r USING (day)
WHERE c.visitor_type = 'ai_referred' AND c.goal = 'Purchase'
GROUP BY c.day
ORDER BY c.day;

Columns

Generated from the export's own table definitions.

punchline_traffic_daily

Sessions and page views per day, page, visitor type and source. Groups with fewer than 5 people's page views are withheld.

Column	BigQuery type	Snowflake type	Description
day	DATE	DATE	UTC day
path	STRING	VARCHAR	Page path
visitor_type	STRING	VARCHAR	human, ai_referred, agent or crawler (at the likely confidence)
actor_class	STRING	VARCHAR	Punchline's label for the visitor
operator	STRING	VARCHAR	The company behind an agent or crawler
tier	STRING	VARCHAR	How sure the label is: verified, declared, inferred_high, inferred_low, none
channel	STRING	VARCHAR	How the visit arrived
referrer_host	STRING	VARCHAR	Referring site
utm_source	STRING	VARCHAR	utm_source
utm_medium	STRING	VARCHAR	utm_medium
utm_campaign	STRING	VARCHAR	utm_campaign
sessions	INT64	NUMBER(38,0)	Sessions that started on this page
pageviews	INT64	NUMBER(38,0)	Page views

punchline_conversions_daily

Goal conversions per day, credited to how the session arrived.

Column	BigQuery type	Snowflake type	Description
day	DATE	DATE	UTC day
goal	STRING	VARCHAR	Goal name
visitor_type	STRING	VARCHAR	Who converted
channel	STRING	VARCHAR	How the session arrived
landing_path	STRING	VARCHAR	First page of the session
conversions	INT64	NUMBER(38,0)	Sessions that completed the goal

punchline_events_daily

Custom events per day.

Column	BigQuery type	Snowflake type	Description
day	DATE	DATE	UTC day
event_name	STRING	VARCHAR	Event name
visitor_type	STRING	VARCHAR	Who sent it (null for server events not tied to a visit)
events	INT64	NUMBER(38,0)	Events
event_value	FLOAT64	FLOAT	Sum of event values

punchline_agent_sessions

Every agent and crawler session, with the rules and evidence behind its label. People's sessions are never exported.

Column	BigQuery type	Snowflake type	Description
day	DATE	DATE	UTC day
session_id	STRING	VARCHAR	Session ID
started_at	TIMESTAMP	TIMESTAMP_TZ	First request
ended_at	TIMESTAMP	TIMESTAMP_TZ	Last request
actor_class	STRING	VARCHAR	Label
operator	STRING	VARCHAR	Operator
tier	STRING	VARCHAR	Confidence tier
pageviews	INT64	NUMBER(38,0)	Requests in the session
paths	JSON	VARCHAR (JSON text)	Paths in request order (up to 200)
rule_ids	JSON	VARCHAR (JSON text)	Classifier rules that fired
evidence	JSON	VARCHAR (JSON text)	The evidence behind the label

Other ways to get data out

• POST /v1/exports: a CSV or Parquet file of any query, up to 100,000 rows, when you need it.
• The API and the query cookbook, for pulling exactly the numbers you want from your own code or a BI tool.