Syncing Analytics Sessions
Copy storefront visits into your own system and keep them current with one query per run,
instead of reading traffic through the storefront or the REST API. Each row of
uc_analytics_sessions is one visit: who the visitor was (client_id), when it happened, the
UTM parameters it arrived with, and the order it produced, if any.
The pattern is the same change data capture used for orders in Extracting Order History, with an extra first step to keep the view scan small, and the differences covered below.
Run the change query
A run takes two queries. The first asks the changelog for the oldest partition any changed session lives in, and the second reads only from that partition forward:
const { UltraCartBigQuery, toBigQueryDatetime } = require('@ultracart/bigquery-sdk');
const ucbq = new UltraCartBigQuery({ merchantId: 'ACME' }); // <- your merchant ID
const startedAt = new Date().toISOString(); // the next run's watermark
const since = await loadWatermark(); // <- your store: the last run's start time
const params = { mid: 'ACME', since: toBigQueryDatetime(since) }; // <- your merchant ID
// 1. The oldest partition among changed sessions. A full changelog scan costs a few MB.
let fromPartition = null;
for await (const row of ucbq.query(
`SELECT MIN(partition_date) AS from_partition
FROM ultracart_dw_streaming.uc_analytics_session_streaming
WHERE merchant_id = @mid AND RecordTime > @since`,
{ params },
)) {
fromPartition = row.from_partition;
}
// 2. Current state of the changed sessions, read only from the partitions they can be in.
if (fromPartition) {
const sql = `
SELECT client_session_oid, client_id, session_dts, first_seen_dts, last_seen_dts,
order_id, customer_profile_oid, new_visitor, new_customer, utms
FROM ultracart_dw.uc_analytics_sessions
WHERE merchant_id = @mid
AND partition_date >= @from_partition
AND client_session_oid IN (
SELECT client_session_oid
FROM ultracart_dw_streaming.uc_analytics_session_streaming
WHERE merchant_id = @mid AND RecordTime > @since)`;
for await (const session of ucbq.query(sql, {
params: { ...params, from_partition: fromPartition },
})) {
await upsertSession(session); // <- your write, idempotent on client_session_oid
}
}
await saveWatermark(startedAt); // <- your store; only after every write commits
The watermark rules from the orders guide apply unchanged: capture the start time before
querying, save it only after the writes commit, and make the write idempotent on
client_session_oid.
A runnable version is in the package repository as
analytics-sessions-cdc.js.
How sessions differ from orders
No SDK model. The REST API has no analytics session resource, so there is no model class to
pass. Leave out model and query() yields plain objects with the same normalization: ISO 8601
dates with a trailing Z, numbers as Number, and primitive arrays flattened. Plain objects
keep every column you select, including *_hash columns such as email_hash, which a hydrated
model would drop.
Read the oldest changed partition first. The sessions view is very large, and the view side is nearly all of a run's cost. The changelog side is cheap: scanning it with no partition filter costs a few megabytes. That is why the job runs two queries.
partition_dateis the week the session started, on both the view and the changelog. A session can change long after that week, for example when an order or a customer record is attached late. A fixed window such as "the last 14 days" would silently miss those changes. Taking the oldest changed partition from the changelog bounds the view exactly instead.- Pass the bound as a query parameter. BigQuery prunes partitions on a parameter, but not on a subquery inside the filter.
On one measured account, a typical run was estimated at about 0.03 GB in total. A run that had to reach a session from three months back was estimated at about 0.4 GB, and only that run paid for it.
Select columns explicitly. hits holds every page view within the session and dominates
the row size. Add it to the column list only when you need page-level detail, and check the
cost with dryRun() first.
Datasets
| Account | View | Changelog |
|---|---|---|
| Single account | ultracart_dw.uc_analytics_sessions | ultracart_dw_streaming.uc_analytics_session_streaming |
| Parent account | ultracart_dw_linked.uc_analytics_sessions | ultracart_dw_linked_streaming.uc_analytics_session_streaming |
The ultracart_dw and _low views carry only email_hash and sms_hash. Point the view side at
_medium or _high if your access level allows it and you need the readable values. The
changelog dataset does not change with the access level, because the subquery reads only IDs
from it.
Next
- Extracting Order History for the watermark reasoning and delete handling.
- Marketing attribution sample queries for reporting SQL against the same table.
- API reference for
query()anddryRun()options.