Core, tracking data schema. 3rd party tracking-system (NO ADTRIBA's PIXEL)
Core, Tracking App data schema.
Core, UMM data. (Self-delivery)
Core, TV data.
Sphere, Schema for data self-delivery
1 - Core, tracking data schema. 3rd party tracking-system (No Adtriba's Pixel)
Field | Example | Additional Information |
event_id | a0f65918-6b38-11ed-a534-a365a1dfe954 | unique id of an event |
event_timestamp | 2022-10-21 14:04:25 | UTC timestamp of an event, if not UTC it should match the project timezone |
event_action | pageview OR conversion OR track | used to distinguish the events within one file/dataset |
event_name | ProductAddedToCart | used for micro conversions, value provided when |
ip | 217.233.36.0 | OPTIONAL, 217.233.36.55, replace last octet 55 with 0 |
ip_hashed | d9e6cf191ad6377fe5894820dc782594ed035b6e188848bae1dec912bb3e1d1c | OPTIONAL, SHA256(217.233.36.55) |
http_location | the location of the client at the moment after a click/page load | |
http_referrer | the location of the client at the moment after a click/page load | |
platform_device_id | dad29c40-6c4c-11ed-a9c9-4bdc85395049 | identifier of the client/device (e.g. browser) |
platform_user_id | 2b91027ddcd3869f3ccf1cf72d76fcad322803c0360b950f6c5479d6bca68c94 | OPTIONAL but recommended identifier of the user, always hashed, hash needs to be stable/immutable |
conversion_id | AIWIEWOOWAEY7JAH | unique id of a conversion / transaction / order value provided when event_action == conversion |
conversion_value | 4999.85 | value of the conversion/transaction/order value provided when event_action == conversion |
conversion_currency | EUR | currency of the conversion/transaction/order value provided when event_action == conversion |
2 - Core, tracking APP data schema.
Field | Type | Comment |
log_id | string | Unique identifier of the row |
tracker_id | string | df613gkl |
store | string |
|
timezone | string |
|
country | string |
|
language | string |
|
gps_adid | string |
|
idfa | string | We resolve cookie_id from here |
idfv | string | or here |
activity | string |
|
event_name | string |
|
conversion_id | Option[String] |
|
revenue_float | Option[Double] |
|
currency | Option[String] |
|
tracker | string |
|
network_name | Option[string] | source |
campaign_name | Option[string] |
|
gclid | String |
|
impression_based | Option[int] |
|
received_at | String |
|
received_date | String |
|
user_id | String |
|
3 - Core, UMM data (self-delivery)
Valid formats: .csv / .json / .parquet
3.1 Media Data
Destination
s3://{{data-exchange-import}}/umm/MEDIA/upload_date={upload_date}/
Schema
Column Name | Type | Example | Comments |
ds | date | 2022-01-01 | YYYY-MM-DD |
country | string | DE | level 2 alpha iso country code, e.g DE, ES, IT, PL... |
impressions | long | 20000 |
|
costs | double | 20.99 |
|
media_feature | string |
| |
website_purchases | long | 5 | optional - self attributed conversions (from the ad platforms) on web |
mobile_app_purchases | long | 10 | optional - self attributed conversions (from the ad platforms) on mobile |
3.2 Control Data
Destination
s3://{{data-exchange-import}}/umm/REDEEMED_VOUCHERS/upload_date={upload_date}/
Schema
Column Name | Type | Example |
ds | date | 2022-01-01 |
country | string | DE |
ctrl_var_name | string | pricing |
ctrl_var_value | double | 7.00 |
3.3 Control Data
Destination
s3://{{data-exchange-import}}/umm/REDEEMED_VOUCHERS/upload_date={upload_date}/Schema
Column Name | Type | Example |
date | date | 2022-01-01 |
order_id | string | abcd1234 |
country | string | DE |
voucher_prefix | string | optional |
voucher_type | string | optional |
This file explains how to send data for Self-delivery and using Funnel.io:
4 - TV data
For the upload the TV data, please follow this already existing post in our help-center:
5 - Sphere, Data self-delivery
tracking and Ad_platforms tables base schema required to onboard a customer (NOT using Funnel.io) onto Sphere
5.1 - Tracking
Column name | Data type | Notes |
date | date | YYYY-MM-DD format |
country | string | ISO 3166-1 alpha-2 code (DE, US. ...) |
brand | string | Brand name |
conversion event | string | Can be used to segment the metrics by conversion event. Will default to Transaction if it’s NULL. |
device type | string | Web / Mobile (can be NULL) |
data source name | string | Can be NULL in this table |
Sphere Source | string | See detailed explanation* |
is organic | boolean | Indicates if visits are organic or marketing related |
currency | string | ISO 4217 code (EUR, USD, ...) |
visits | long | One, and only one of: sessions, visits, or pageviews |
conversions | long | Total number of conversions in a day (see detailed explanation**) |
revenue | double | Total revenue in a day (see detailed explanation ***) |
Aggregated data by day.
5.2 - Ad platforms
Column Name | Data Type | Notes |
date | date | YYY-MM-DD format |
country | string | ISO 3166-1 alpha-2 code (DE, US...) |
brand | string | Brand name |
conversion event | string | Can be used to segment the metrics by conversion event. Will default to Transaction if it’s NULL.. |
device type | string | Web / Mobile (can be NULL) |
data source name | string | Source of the data (facebookads, google analytics, criteo..) |
sphere source | string | See detailed explanation* |
currency | string | ISO 4217 code (EUR, USD...) |
cost | double |
|
clicks | long |
|
impressions | long |
|
Sphere Source*
This will be the column used for the marketing mix model. It is an aggregation determined by you, but we recommend it to be a combination of platform and tactic, e.g. facebook-prospecting, adwords-nonbrand. For the organic traffic, it is best if it just resolves to the source (e.g. direct, organic).
Conversions**
When we aggregate the data per day, this should resolve to the total number of conversions on that day. If you provide us with google analytics data, this contains Lastclick attributed data in the same row as the source (e.g. email, 250, organic 300..., etc.) If you pull conversions and revenue from a separate location, you can put them in separate rows but leave the sphere source on NULL.
Revenue***
It follows the same logic as the conversions.
In this files you can have more information: