# 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. {% hint style="info" %} A service account and key are provided by the Flip.to team for direct data access. {% endhint %} ### 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 | {% hint style="info" %} 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'.** {% endhint %} *** ## 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](https://console.cloud.google.com). {% hint style="info" %} A billing account must be linked to the project — BigQuery will not run queries without one. {% endhint %} 2. 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: ```console gcloud projects add-iam-policy-binding YOUR-PROJECT-ID-HERE --member="serviceAccount: YOUR-SERVICE-ACCOUNT-EMAIL-HERE " --role="roles/bigquery.jobUser" ``` 3. This grants the service account permission to execute queries billed to your project. 4. 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 | {% hint style="warning" %} 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! {% endhint %} ### Running your first query To verify everything works, run this in the BigQuery console from your project or from a BI tool: ```sql 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 ```sql DECLARE date_from DATE DEFAULT '2026-02-01'; DECLARE date_to DATE DEFAULT '2026-02-28'; DECLARE properties ARRAY 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 {% hint style="info" %} HLL Data is binary and not represented in the samples below. {% endhint %} ### 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 | | 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 | | ------------------------- | ---------- | ------------------ | ------------------------------------ | ---------------------- | ------------------ | | | true | Your property name | f7416e5b-f68f-486f-9079-666b793bd79b | true | 2 | ### 4. property\_hosts
hostnameproperty_uuid
yourdomain.comf7416e5b-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 |