GA360 Standard

Devices

The device a session ran on: category, browser and its version, operating system and its version, one row per combination, as the GA 360 export records them. Sessions point here through deviceHash, a FARM_FINGERPRINT of the five values. The table has no date, so a hash repeats once per run (once per day for daily runs). Count distinct hashes. In your own SQL over these tables, deduplicate it before joining, or rows multiply; OWOX Data Marts reports collapse joined fields per source row.

Fields

Event Info

The category, action and label behind event hits, one row per combination, as the site sent them. Events point here through their pageHash column, which despite its name holds this table's eventHash, a FARM_FINGERPRINT of the three values. Each run adds the combinations of its own window. The table has no date, so a hash repeats once per run (once per day for daily runs). Count distinct hashes. In your own SQL over these tables, deduplicate it before joining, or rows multiply; OWOX Data Marts reports collapse joined fields per source row.

Fields

Events

Every event hit of the GA 360 export, one row each, tied to the session it fell in. Join to Sessions on date and sessionId together. Despite its name, pageHash holds the event hash: it joins Event Info, which carries the event's category, action and label. hitId identifies an event: a rerun of the same window gives the same value, a different window does not, and Pageviews numbers its hits separately. time is a timestamp in milliseconds since 1970-01-01 UTC; date is the day in the property's time zone.

Fields

Relationships

Geo

Where a session came from: country, region and city, one row per combination, as the GA 360 export records them. Sessions point here through geoHash, a FARM_FINGERPRINT of the three values. The table has no date, so a hash repeats once per run (once per day for daily runs). Count distinct hashes. In your own SQL over these tables, deduplicate it before joining, or rows multiply; OWOX Data Marts reports collapse joined fields per source row.

Fields

Pages

The pages behind page views: host name, path and title, one row per combination. The path is cut at the first ?, # or ;, so query strings and anchors are dropped. The title is part of the key, so one path with two titles is two pages. Page views point here through pageHash, a FARM_FINGERPRINT of host, path and title. Each run adds the pages of its own window. The table has no date, so a hash repeats once per run (once per day for daily runs). Count distinct hashes. In your own SQL over these tables, deduplicate it before joining, or rows multiply; OWOX Data Marts reports collapse joined fields per source row.

Fields

Pageviews

Every page view hit of the GA 360 export, one row each, tied to the session it fell in. Join to Sessions on date and sessionId together, and to Pages on pageHash for the host, path and title: the table holds no page path itself. hitId identifies a page view: a rerun of the same window gives the same value, a different window does not, and Events numbers its hits separately. time is a timestamp in milliseconds since 1970-01-01 UTC; date is the day in the property's time zone.

Fields

Relationships

Sessions

One row per session of the GA 360 export, with its landing page, engagement, purchases and the keys to its location, device, traffic source and user. A row is identified by date together with sessionId; page views, events and purchases join on both. visitStartTime is in seconds since 1970, and timeOnSite is in whole seconds with the fraction cut off, so a one-hit session shows 0. Purchases are deduplicated the same way as in Transactions. transactionRevenue is in millionths, as Google's export stores it: divide by 1,000,000. cost, impressions and clicks are always 0, slots for ad cost blended in later.

Fields

Relationships

Traffic Sources

The source, medium, campaign, keyword and ad content Google Analytics attributed a session to, one row per combination. They are read as they are from the export's trafficSource record, such as google / organic, (direct) / (none), or the UTM tags of a campaign. Sessions point here through trafficSourceHash, a FARM_FINGERPRINT of the five values. The table has no date, so a hash repeats once per run (once per day for daily runs). Count distinct hashes. In your own SQL over these tables, deduplicate it before joining, or rows multiply; OWOX Data Marts reports collapse joined fields per source row.

Fields

Transactions

One row per purchase, deduplicated by transaction ID: when the export records the same ID more than once within the deduplication window (7 days in the runbook's setup), only the earliest is kept, so there is at most one row per ID per deduplication window. transactionRevenue is in millionths, as Google's export stores it: divide by 1,000,000. status is always empty and touchpoint is always WEB; both are slots. Join to Sessions on date and sessionId for the source, device and location behind each order. The purchase counts on Sessions are deduplicated the same way.

Fields

Relationships

Users

The visitors behind sessions, one row per client ID: the identifier Google Analytics gives a browser, not a person. The model builds a user from clientId alone; the other identifier columns are always empty, slots for other sources. Where the export carries no clientId, as in Google's public sample, every session lands on one user hash and this table holds one row per run. Sessions point here through userHash. The table has no date, so a hash repeats once per run (once per day for daily runs). Count distinct hashes. In your own SQL over these tables, deduplicate it before joining, or rows multiply; OWOX Data Marts reports collapse joined fields per source row.

Fields