Skip to main content
How-to

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_date is 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​

AccountViewChangelog
Single accountultracart_dw.uc_analytics_sessionsultracart_dw_streaming.uc_analytics_session_streaming
Parent accountultracart_dw_linked.uc_analytics_sessionsultracart_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​

Was this page helpful?