Direct data access

Spacetime provides direct data access to analytics data via Google BigQuery authorized views. This approach allows you to connect any BI tool (Looker, Power BI, Tableau, etc.) directly to BigQuery or pull data on demand via ad-hoc queries without needing to copy or sync data between environments. Data is always fresh and access to specific properties is platform-enforced at the authentication level.

A service account and key are provided by the Flip.to team for direct data access.

Data Access

Dataset: flipto-external-data.shared_views

View	Description
Lookup & Reference
properties_lookup	Property names and identifiers
companies_lookup	Company names and metadata
companies_properties_associations	Company-to-property mapping
property_hosts	Hostnames (domains) associated with each property
Transactions
transactions	Individual transaction records (booking details, revenue, room nights)
Traffic & Engagement Metrics
traffic_metrics_root	Daily user/engagement metrics by property and dimensions (HLL)
Transaction & Reservation Metrics (Attribution-Weighted)
transaction_metrics_root	Transaction metrics by purchase date with multi-touch attribution
reservation_metrics_root	Transaction metrics by reservation/arrival date with attribution
transaction_reservation_metrics_root	Cross-date: purchase date rows with reservation date as secondary dimension
reservation_transaction_metrics_root	Cross-date: reservation date rows with purchase date as secondary dimension
Demand & Search Metrics
demand_metrics_root	Search demand / availability check user counts (HLL)
search_metrics_root	Booking engine search user counts (HLL)
demand_search_metrics_root	Cross-date: demand date rows with search date as secondary dimension
search_demand_metrics_root	Cross-date: search date rows with demand date as secondary dimension
Media & Advertising
media_ad_accounts	Ad account metadata (Google, Meta, Microsoft, TikTok)
media_ads	Individual ad creative details
media_ad_metrics	Ad platform reported metrics (impressions, clicks, spend)
media_ad_click_ids	Click ID mapping for ad click attribution

Attribution models

Five different attribution models are supported out of the box:

Model	Description
first_touch	100% credit to the first marketing touchpoint
last_touch	100% credit to the last touchpoint before purchase
linear	Equal credit across all touchpoints
positional	40% first, 40% last, 20% split across middle
time_decay	Exponential decay with 7-day half-life

Granularity

Daily data is available. The examples show weekly aggregation, but you can choose any granularity:

Granularity	Approach
Daily	Use granular_date directly
Weekly	DATE_TRUNC(granular_date, WEEK(MONDAY))
Monthly	DATE_TRUNC(granular_date, MONTH)

Understanding array_source (Transaction Views)

Transaction and reservation views contain an array_source column that controls which dimensions are populated on each row. Each transaction appears multiple times — once per array source — so you must always filter to the one you need, or you will double-count.

array_source	Dimensions populated	Use case
traffic_sources	Channel, source, medium, campaign, ad network, ad campaign	Channel or campaign performance reports
nonpositional	Country, region, city, device class, room type, booking window	Geographic, device, or room type breakdowns
landing_pages	Email send UUID	Email campaign attribution

For example, a single $200 transaction with 2 marketing touchpoints and a device type will appear as rows in both traffic_sources (attributed across the 2 touchpoints) and nonpositional (with the device dimension). Querying without an array_source filter would count that transaction multiple times.

Unless you specifically need geo, device, room type, or email breakdowns, use array_source = 'traffic_sources'.

---

Initial setup & first query

Infrastructure

1. You'll need your own Google Cloud project with billing enabled. If you don't have one, you can create one at console.cloud.google.com.

A billing account must be linked to the project — BigQuery will not run queries without one.

1. Grant the Flip.to service account permission to run queries in their project. A GCP admin runs this in their project’s IAM console or via gcloud:

gcloud projects add-iam-policy-binding YOUR-PROJECT-ID-HERE --member="serviceAccount:
YOUR-SERVICE-ACCOUNT-EMAIL-HERE
" --role="roles/bigquery.jobUser"

1. This grants the service account permission to execute queries billed to your project.

2. Receive the service account key file from Flip.to. This is a JSON file used to authenticate. Flip.to will provide it securely.

Connecting a BI Tool

When configuring a BI tool (Power BI, Looker, Tableau, etc.) or a BigQuery client library:

Setting	Value
Authentication	Service account JSON key file (provided by Flip.to)
Billing / Execution Project	Your GCP project ID
Dataset to query	flipto-external-data.shared_views

The execution project setting is critical as it tells BigQuery which project owns the query and where to create jobs. It must be set to your project, as the service account does not have job creation permission on the flipto-external-data project so all requests will fail unless this is configured properly!

Running your first query

To verify everything works, run this in the BigQuery console from your project or from a BI tool:

SELECT property_uuid, name
FROM `flipto-external-data.shared_views.properties_lookup`

If this returns property names, the setup is complete. If not, here are some common errors and how to fix them.

Error	Cause	Fix
Access Denied: User does not have bigquery.jobs.create permission	Service account not granted bigquery.jobUser in your project	Your admin runs the gcloud IAM command above
Access Denied: Table flipto-external-data.shared_views.properties_lookup	Dataset read access not yet granted by Flip.to	Flip.to runs the GRANT dataViewer command
Empty results (0 rows)	service_account_id not set on your company record	Flip.to updates companies_lookup

---

Example query

All queries should include a date filter and a property filter for best performance. Examples below use a one-month window.

Daily Traffic + Revenue By Property / Channel / Source / Medium / Ad Campaign

DECLARE date_from DATE DEFAULT '2026-02-01';
DECLARE date_to DATE DEFAULT '2026-02-28';
DECLARE properties ARRAY<STRING> DEFAULT ['YOUR-PROPERTY-UUID-HERE'];

WITH ad_names AS (
  SELECT
    campaign.campaign_id                AS ad_campaign,
    ANY_VALUE(campaign.name)            AS campaign_name,
    ad_group.ad_group_id,
    ANY_VALUE(ad_group.name)            AS ad_group_name,
    ad_id,
    ANY_VALUE(name)                     AS ad_name
  FROM `flipto-external-data.shared_views.media_ads`
  GROUP BY campaign.campaign_id, ad_group.ad_group_id, ad_id
),

traffic AS (
  SELECT
    granular_date AS day,
    property_uuid,
    default_channel_group,
    NULLIF(NULLIF(utm_source, 'xxxx'), 'unknown')     AS utm_source,
    NULLIF(NULLIF(utm_medium, 'xxxx'), 'unknown')     AS utm_medium,
    NULLIF(NULLIF(ad_campaign, 'xxxx'), 'unknown')    AS ad_campaign,
    NULLIF(NULLIF(ad_group_id, 'xxxx'), 'unknown')    AS ad_group_id,
    NULLIF(NULLIF(ad_id, 'xxxx'), 'unknown')          AS ad_id,
    HLL_COUNT.MERGE_PARTIAL(metrics.total_hll)    AS visitors_hll,
    HLL_COUNT.MERGE_PARTIAL(metrics.is_engine_hll) AS planners_hll,
    HLL_COUNT.MERGE_PARTIAL(metrics.is_booked_hll) AS booked_guests_hll
  FROM `flipto-external-data.shared_views.traffic_metrics_root`
  WHERE granular_date BETWEEN date_from AND date_to
    AND entity_uuid IN UNNEST(properties)
  GROUP BY ALL
),

txn AS (
  SELECT
    transaction_date AS day,
    property_uuid,
    default_channel_group,
    NULLIF(NULLIF(utm_source, 'xxxx'), 'unknown')     AS utm_source,
    NULLIF(NULLIF(utm_medium, 'xxxx'), 'unknown')     AS utm_medium,
    NULLIF(NULLIF(ad_campaign, 'xxxx'), 'unknown')    AS ad_campaign,
    NULLIF(NULLIF(ad_group_id, 'xxxx'), 'unknown')    AS ad_group_id,
    NULLIF(NULLIF(ad_id, 'xxxx'), 'unknown')          AS ad_id,
    SUM(first_touch.transactions) AS first_touch_transactions,
    SUM(first_touch.revenue)      AS first_touch_revenue,
    SUM(last_touch.transactions)  AS last_touch_transactions,
    SUM(last_touch.revenue)       AS last_touch_revenue
  FROM `flipto-external-data.shared_views.transaction_metrics_root`
  WHERE transaction_date BETWEEN date_from AND date_to
    AND property_uuid IN UNNEST(properties)
    AND array_source = 'traffic_sources'
  GROUP BY ALL
)

SELECT
  COALESCE(tr.day, tx.day)                                           AS day,
  p.name                                                              AS hotel,
  COALESCE(tr.default_channel_group, tx.default_channel_group)        AS channel,
  COALESCE(tr.utm_source, tx.utm_source)                              AS source,
  COALESCE(tr.utm_medium, tx.utm_medium)                              AS medium,
  COALESCE(an.campaign_name, tr.ad_campaign, tx.ad_campaign)          AS campaign,
  COALESCE(an.ad_group_name, tr.ad_group_id, tx.ad_group_id)         AS ad_group,
  HLL_COUNT.EXTRACT(tr.visitors_hll)                                  AS visitors,
  HLL_COUNT.EXTRACT(tr.planners_hll)                                  AS planners,
  HLL_COUNT.EXTRACT(tr.booked_guests_hll)                             AS booked_guests,
  ROUND(tx.first_touch_transactions, 2)                               AS first_touch_transactions,
  ROUND(tx.first_touch_revenue, 2)                                    AS first_touch_revenue,
  ROUND(tx.last_touch_transactions, 2)                                AS last_touch_transactions,
  ROUND(tx.last_touch_revenue, 2)                                     AS last_touch_revenue
FROM traffic tr
FULL OUTER JOIN txn tx
  ON  tr.day = tx.day
  AND tr.property_uuid = tx.property_uuid
  AND tr.default_channel_group = tx.default_channel_group
  AND IFNULL(tr.utm_source, '') = IFNULL(tx.utm_source, '')
  AND IFNULL(tr.utm_medium, '') = IFNULL(tx.utm_medium, '')
  AND IFNULL(tr.ad_campaign, '') = IFNULL(tx.ad_campaign, '')
  AND IFNULL(tr.ad_group_id, '') = IFNULL(tx.ad_group_id, '')
  AND IFNULL(tr.ad_id, '') = IFNULL(tx.ad_id, '')
LEFT JOIN ad_names an
  ON  an.ad_campaign = COALESCE(tr.ad_campaign, tx.ad_campaign)
  AND an.ad_group_id = COALESCE(tr.ad_group_id, tx.ad_group_id)
  AND an.ad_id = COALESCE(tr.ad_id, tx.ad_id)
LEFT JOIN `flipto-external-data.shared_views.properties_lookup` p
  ON p.property_uuid = COALESCE(tr.property_uuid, tx.property_uuid)
ORDER BY day, last_touch_revenue DESC NULLS LAST, channel, source, medium, campaign, ad_group

Best Practices

1. Always include a date filter — data is partitioned by date. Without a date filter, the query scans all historical data and costs more.

2. Add a literal property filter when possible — If querying a specific property, add AND entity_uuid = 'uuid-here' (or property_uuid for transaction views). This enables cluster pruning, improves query performance and can reduce data scanned.

3. Aggregate by week or month — Daily granularity is available, but weekly/monthly aggregation reduces result set size for BI dashboards.

4. HLL metrics must be merged, not summed — User counts use HyperLogLog sketches for accurate cross-dimension deduplication. Always use HLL_COUNT.MERGE() when aggregating, never SUM.

Sample Data

HLL Data is binary and not represented in the samples below.

1. companies_lookup

service_account_id	company_name	company_uuid	company_type_id	url	is_active
Your service acount email	Your company name	594cabdb-a86f-4b74-beb5-0d09bb7a9358	1	https://www.example.com	true

2. companies_properties_associations

property_uuid	company_uuid
36a49ed0-8e63-4f25-b6f6-a7423a1a5e59	594cabdb-a86f-4b74-beb5-0d09bb7a9358

3. properties_lookup

website_url	is_active	name	property_uuid	is_spacetime_enabled	property_type_id
https://www.example.com	true	Your property name	f7416e5b-f68f-486f-9079-666b793bd79b	true	2

4. property_hosts

hostname	property_uuid
yourdomain.com	f7416e5b-f68f-486f-9079-666b793bd79b

5. transactions

purchase_tstamp	transaction_id	amount_in_cents	currency	nights	arrival_date	departure_date	room_type	rate_plan_name	property_uuid	booking_engine	geo_country	geo_city	device_class
2025-11-13 21:07:50	d275b263-a960-4ac4-9eec-54c73c495991	41644	usd	2	2026-04-10	2026-04-12	superior room 2 double	best available rate	f7416e5b-f68f-486f-9079-666b793bd79b	ihotelier	es	barcelona	desktop

6. traffic_metrics_root

granular_date	entity_uuid	channel_collection	default_channel_group	ad_network	utm_source	utm_medium	utm_campaign	geo_country	geo_city	device_class	property_uuid	room_type	booking_window_week
2026-03-01	f7416e5b-f68f-486f-9079-666b793bd79b	Organic	Direct	Organic	NULL	NULL	NULL	us	NULL	desktop	f7416e5b-f68f-486f-9079-666b793bd79b	NULL	0

7. demand_metrics_root

reservation_date	property_uuid	channel_collection	default_channel_group	ad_network	utm_source	utm_medium	geo_country	geo_city	device_class	room_type	booking_window_week
2026-03-01	f7416e5b-f68f-486f-9079-666b793bd79b	NULL	NULL	unknown	NULL	NULL	mx	venustiano carranza	desktop	Deluxe Room 1 King	0

8. search_metrics_root

	property_uuid	channel_collection	default_channel_group	ad_network	utm_source	utm_medium	geo_country	geo_city	device_class	room_type	booking_window_week
2026-03-01	f7416e5b-f68f-486f-9079-666b793bd79b	Paid	Paid Metasearch	Trivago	trivago	meta	us	new york	desktop	NULL	0

9. transaction_metrics_root

transaction_date	property_uuid	array_source	channel_collection	default_channel_group	ad_network	utm_source	utm_medium	geo_country	geo_city	device_class	room_type	booking_window_week
2026-03-01	f7416e5b-f68f-486f-9079-666b793bd79b	traffic_sources	Paid	Cross-network	Google	unknown	unknown	mx	cuauhtémoc	desktop	superior room 2 double	0

10. reservation_metrics_root

reservation_date	property_uuid	array_source	channel_collection	default_channel_group	ad_network	geo_country	geo_city	device_class	room_type	booking_window_week
2026-03-01	f7416e5b-f68f-486f-9079-666b793bd79b	nonpositional	NULL	NULL	NULL	mx	guadalajara	desktop	superior room 1 king	0

11. transaction_reservation_metrics_root

transaction_date	reservation_date	property_uuid	array_source	channel_collection	default_channel_group	ad_network	geo_country	geo_city	device_class	room_type
2026-03-01	2026-03-16	f7416e5b-f68f-486f-9079-666b793bd79b	traffic_sources	Paid	Paid Search	Google	mx	ecatepec	phone	superior room 2 double

12. reservation_transaction_metrics_root

reservation_date	transaction_date	property_uuid	array_source	channel_collection	default_channel_group	ad_network	geo_country	geo_city	device_class	room_type
2026-03-01	2026-02-04	f7416e5b-f68f-486f-9079-666b793bd79b	nonpositional	NULL	NULL	NULL	mx	tlajomulco de zúñiga	desktop	accessible room

13. demand_search_metrics_root

reservation_date	search_date	property_uuid	channel_collection	default_channel_group	ad_network	geo_country	geo_city	device_class
2026-03-01	2025-12-09	f7416e5b-f68f-486f-9079-666b793bd79b	NULL	NULL	unknown	us	san diego	desktop

14. search_demand_metrics_root

search_date	reservation_date	property_uuid	channel_collection	default_channel_group	ad_network	geo_country	geo_city	device_class
2026-03-01	2026-04-06	f7416e5b-f68f-486f-9079-666b793bd79b	Paid	Paid Metasearch	Trivago	mx	saltillo	phone

15. media_ad_accounts

media_ad_account_uuid	name	account_id	media_ad_account_type_id	company_uuid	property_uuid	start_date	is_allow_properties_access
ca0c909b-dd45-4680-8296-ba7636338784	Tiktok ad account	7890123456	4	594cabdb-a86f-4b74-beb5-0d09bb7a9358	f7416e5b-f68f-486f-9079-666b793bd79b	2025-04-17	false

16. media_ads

ad_id	media_ad_account_uuid	name	campaign_name	campaign_id	campaign_status	ad_group_name	ad_group_id
5647382910	ca0c909b-dd45-4680-8296-ba7636338784	Ad Name	Ad Campaign Name	0987654321	Active	Ad Group Name	1234567890

17. media_ad_metrics

ad_id	date	total_cost	clicks	impressions	unique_clicks	conversions	landing_page_views
5647382910	2026-03-02	12.34	12	12345	14	NULL	NULL