Activating Your Data Warehouse for Intelligent Experiences 

Google BigQuery stands as a cornerstone for many organizations, serving as a powerful, scalable data warehouse for storing vast amounts of historical user interaction data, detailed user profiles, rich item catalogs, and critical business metrics. While BigQuery excels at analytics and batch processing, the challenge often lies in activating this wealth of data to drive real-time personalized experiences like recommendations and search ranking.

How do you leverage the deep historical insights locked within your BigQuery tables to predict what a user wants right now? How do you use your carefully curated item metadata and user segments from BigQuery to power sophisticated AI models without building complex, fragile ETL pipelines and ML infrastructure? This is where Shaped's dedicated BigQuery connector provides a seamless solution.

Shaped is an AI-native relevance platform designed to connect directly to your data warehouse, ingest relevant data, train state-of-the-art machine learning models, and serve personalized recommendations and search rankings via simple APIs. This post outlines the value of connecting BigQuery to Shaped and provides a step-by-step guide to setting up the integration.

Why Connect BigQuery to Shaped? Leveraging Your Data Asset

Connecting your BigQuery warehouse directly to Shaped allows you to transform your historical data and curated catalogs into powerful drivers for personalization and insight generation across various use cases:

• Data-Rich Recommendations: Utilize the comprehensive data in BigQuery to fuel highly relevant suggestions:
  
  • Leverage Long-Term History: Generate recommendations based on deep historical interaction patterns stored in BigQuery, complementing real-time signals.
  • Catalog-Aware Recommendations: Incorporate rich item metadata (attributes, categories, descriptions) directly from your BigQuery catalog tables.
  • User Profile Personalization: Utilize user segments, demographics, or calculated attributes stored in BigQuery to tailor recommendations.
  • "Similar Item" based on Rich Attributes: Find related items based on detailed metadata alongside behavioral signals.
  • Cold-Start Mitigation: Use historical data and item attributes from BigQuery to provide better recommendations for new users or items with sparse interaction data.
• Enhanced Search Relevance: Improve search results by incorporating warehouse insights:
  
  • Attribute-Based Filtering & Faceting: Easily use item attributes synced from BigQuery for powerful filtering in search results (via Shaped's APIs).
  • Offline Metric Optimization: Train models that learn from historical conversion data or business metrics stored in BigQuery to optimize search ranking.
  • Enriching Search with Metadata: Ensure search models have access to the latest, most accurate item attributes directly from your source of truth in BigQuery.
• Deeper Analytics & Insights: Connect warehouse data to powerful ML models for analysis:
  
  • Historical Trend Analysis: Train models on specific time windows from BigQuery to understand how user behavior or item relevance has evolved.
  • Attribute Importance: Understand which item attributes from your BigQuery tables are most predictive of user engagement.
  • Offline Evaluation: Use historical interaction datasets from BigQuery to evaluate the potential performance of different personalization strategies before deploying them live.
• Simplified Data Flow: Avoid building and maintaining complex export/import jobs or reverse ETL processes. Shaped's connector handles the data synchronization directly from BigQuery.
• Scheduled Training: Automatically retrain models on a schedule as new data lands in your BigQuery tables, ensuring models stay fresh without manual intervention.

How it Works: The BigQuery Dataset Connector

Shaped's BigQuery connector works by securely accessing your specified BigQuery tables using a dedicated Google Cloud Platform (GCP) service account granted read-only permissions. You configure the connection within Shaped, defining which table, columns, and filters to use. Shaped then periodically syncs data from BigQuery based on a timestamp column you specify, ensuring that the models are trained on up-to-date information without requiring real-time streaming infrastructure between BigQuery and Shaped.

Connecting BigQuery to Shaped

Setting up the connection involves granting Shaped read access and configuring the dataset in Shaped.

Step 1: Grant Shaped Read-Only Permissions in GCP

To allow Shaped's secure service account to read data from your BigQuery project, you need to grant it specific IAM roles.

1. Contact Shaped: Reach out to the Shaped team (via your support channel or sales contact) to obtain the specific email address of Shaped's dedicated GCP service account (<OUR_SERVICE_ACCOUNT>).‍
2. Install gcloud CLI: Ensure you have the gcloud command-line tool installed and configured for your GCP project.‍
3. Grant Roles: Execute the following gcloud commands, replacing <YOUR_PROJECT> with your GCP project ID and <OUR_SERVICE_ACCOUNT> with the email provided by Shaped. These commands grant the necessary read-only permissions:

1 # Allows viewing data in tables
2 gcloud projects add-iam-policy-binding <YOUR_PROJECT> \
3    --member='serviceAccount:<OUR_SERVICE_ACCOUNT>' \
4    --role='roles/bigquery.dataViewer'
5 
6 # Allows running BigQuery jobs (like export/read jobs)
7 gcloud projects add-iam-policy-binding <YOUR_PROJECT> \
8    --member='serviceAccount:<OUR_SERVICE_ACCOUNT>' \
9    --role='roles/bigquery.jobUser'
10
11 # Allows reading data via the BigQuery Storage Read API for efficiency
12 gcloud projects add-iam-policy-binding <YOUR_PROJECT> \
13    --member='serviceAccount:<OUR_SERVICE_ACCOUNT>' \
14    --role='roles/bigquery.readSessionUser'

These roles ensure Shaped can only read data and cannot make any modifications to your BigQuery resources.

Step 2: Configure the Shaped Dataset (YAML)

Next, define the connection parameters in a YAML configuration file. This file tells Shaped what data to sync from BigQuery.

Create a YAML file (e.g., bq_dataset.yaml):

1 name: your_bigquery_dataset # Choose a descriptive name for your Shaped dataset
2 
3 # --- Required Fields ---
4 
5 schema_type: BIGQUERY # Specifies the connector type
6 
7 # Fully qualified BigQuery table name: project.dataset.table
8 # NOTE: If your project ID contains special characters like hyphens,
9 # enclose the project ID in backticks AND double quotes.
10 table: "`your-gcp-project`.your_bq_dataset.your_bq_table"
11 
12 columns: ["user_id", "item_id", "event_type", "timestamp", "item_category", "item_price"]
13 
14 # The column in your BigQuery table containing a timestamp or datetime.
15 # Shaped uses this to perform efficient incremental syncs after the initial load.
16 datetime_key: "timestamp"
17 
18 # --- Optional Fields ---
19 
20 # start_datetime: "2023-01-01T00:00:00Z"
21 # filters: ["country = 'US'", "event_type IN ('purchase', 'view')"]
22 # unique_keys: ["user_id", "item_id", "timestamp"]
23 # batch_size: 100000

Key Configuration Points:

• table:
• Must be the fully qualified name (project.dataset.table). Pay attention to quoting if your project ID has special characters.
• columns: Select only the columns needed for your personalization models (user IDs, item IDs, timestamps, event types, relevant metadata).
• datetime_key:
• Crucial for efficient incremental updates. Choose a column that reliably indicates when a row was created or last updated (e.g., updated_at, created_at, event_timestamp).

Step 3: Create the Dataset in Shaped

Use the Shaped CLI to create the dataset using the YAML file you just configured:

1 shaped create-dataset --file bq_dataset.yaml

Shaped will validate the configuration and attempt to connect to your BigQuery table using the granted permissions. You can monitor the dataset's status (syncing progress, potential errors) on the Shaped Dashboard or via the CLI (shaped view-dataset --dataset-name your_bigquery_dataset).

What Happens Next? Syncing, Training, Serving

Once the connection is successfully established:

1. Initial Sync: Shaped performs an initial full sync of the data from your specified BigQuery table, respecting any start_datetime or filters you configured. This may take time depending on table size.‍
2. Incremental Syncs:
3. After the initial load, Shaped periodically checks for new or updated rows in your BigQuery table based on the datetime_key and syncs only the changes.
4. Model Training: As data syncs, Shaped uses it to train its powerful AI models tailored for search ranking and recommendations. You can configure training schedules within Shaped.‍
5. API Serving: Once models are trained, you can query Shaped's APIs to get personalized results based on the insights derived from your BigQuery data.‍
6. Ongoing Retraining: Shaped automatically retrains models based on the latest synced data according to your schedule, keeping personalization fresh.

Conclusion: Activate Your BigQuery Data Powerhouse

Your BigQuery data warehouse is a rich source of information critical for deep personalization. Shaped's BigQuery connector provides a secure, efficient bridge to activate this data, allowing you to leverage state-of-the-art AI for recommendations and search without building complex custom ML infrastructure. By connecting BigQuery to Shaped, you can transform historical data and curated catalogs into dynamic, personalized experiences that drive engagement and business value.

Ready to unlock the personalization potential hidden in your BigQuery data?

‍Request a demo of Shaped today to see it in action with your specific use case. Or, start exploring immediately with our free trial sandbox.