> For the complete documentation index, see [llms.txt](https://docs.predicthq.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.predicthq.com/integrations/integration-guides/loading-event-data-into-a-data-warehouse.md). # Loading event data into a data warehouse Take PredictHQ API data and load it into a data warehouse, in this example, GCP BigQuery. This guide outlines the process for integrating PredictHQ's Events data into your data lake. You may want to get data from our APIs and store it in your data lake. To support this, we have created this guide to help you with the integration process We use Google Cloud Platform (GCP) as our primary example, but the methods for structuring the data table and making PredictHQ Events API calls can be applied to various data management systems. This guide assumes you have a basic understanding of GCP or similar data warehousing solutions and the necessary permissions to use them. This guide covers Events API data only, loaded via the API - the do-it-yourself path. For production, [managed delivery](/integrations/third-party-integrations.md) (Snowflake, ADX, or SFTP) keeps your store updated with nothing to build. And model features should come from the [Features API](/api/features/get-features.md), not from aggregating warehouse events - see [Which API should I use?](/getting-started/core-concepts/which-api-should-i-use.md) If you are using Snowflake or AWS Data Exchange (ADX) for your data lake PredictHQ integrates with those products. See [Receive data via Snowflake](https://docs.predicthq.com/integrations/third-party-integrations/snowflake) and [Receive data via AWS Data Exchange](https://docs.predicthq.com/integrations/third-party-integrations/aws-data-exchange) for details. ## Overview This guide details the data structure of our Events API within a data warehouse environment and outlines two methodologies for creating an Events data table in GCP. Throughout this guide, we will use the fictional example from our [Filtering Guide](/getting-started/guides/events-api-guides/filtering-and-finding-relevant-events.md), "Tom’s Pizzeria". This scenario will give us a practical illustration of the methods described and demonstrate how to tailor API queries to specific business needs. For an understanding of how we selected the parameters for this example, refer to the [Filtering Guide](/getting-started/guides/events-api-guides/filtering-and-finding-relevant-events.md). **Requirements**: * Access to PredictHQ Data * JSONL: Requires a PredictHQ account. [Sign up for a PredictHQ account](https://predicthq.com/signup) if you don’t already have one. * API: An [API Access Token](/api/overview/authenticating.md) is necessary for accessing the data programmatically. * GCP permissions: * Ensure you have "BigQuery Data Owner" and "BigQuery User" permissions over the BigQuery environment. * A GCP BigQuery Service Account and its corresponding JSON key are required.[ See the GCP documentation](https://cloud.google.com/iam/docs/service-account-overview) for details. ## Scenario: Tom’s Pizzeria In this guide, we'll explore a hypothetical use case for “Tom’s Pizzeria”, a chain of restaurants with locations throughout the US headquartered in Seattle, Washington. Tom is interested in understanding how local events might influence his business operations and customer flow. Tom’s inventory system, website, and tools run off GCP. Tom wants to use event data in his staffing and inventory management systems to help anticipate the demand caused by events. He wants to show upcoming events near stores to his staff. To do this he needs to download events into his data lake. For comprehensive details on selecting appropriate filters for this scenario, refer to our [Filtering Guide](/getting-started/guides/events-api-guides/filtering-and-finding-relevant-events.md). This guide will help us understand which events could impact Tom's business and how to configure our data queries accordingly. For our load into GCP, we want to bring through a larger number of events and then filter down to a specific pizzeria location using BigQuery once loaded. See [Querying the loaded data](#querying-the-loaded-data) section for more. Tom's Data Parameters: * **Attended Categories**: community, conferences, concerts, expos, festivals, performing-arts, sports. * **Date Range**: Events active within the range June 1, 2024, to June 30, 2024. * **PHQ Rank**: Events with a rank greater than 30 indicate a significant likelihood of impacting local traffic and attendance. * **Event Status**: Both ‘active’ and ‘predicted’ events to ensure a comprehensive overview. * **Location**: Bring through all of Seattle first, and Tom can filter for his locations once it’s in BigQuery using our [Predicted Impact Area API](https://docs.predicthq.com/api/impact-area/get-impact-area). For the purposes of this guide, we have limited the example load to a single city for Tom to filter on. You can bring through as much data as you have access to or require when doing an actual load. We find with data warehouse customers they may pull down all data they have access to into their data warehouse and then query it for relevant locations and data from their applications. ## Data load methods There are several methods available for integrating PredictHQ data with GCP BigQuery or other data warehouse solutions. This guide outlines two primary approaches, both compatible with each other’s data structure. Regardless of the method chosen for the initial data load, ongoing updates require API code. **JSONL File Upload Method**: This method provides a straightforward, code-free approach to data upload, by exporting data from PredictHQ’s [WebApp](https://control.predicthq.com/search/events). We recommend using JSONL uploads for the initial population of your data lake, especially in cases where there is a substantial volume of data, such as multiple years of historical data. Manage subsequent updates through API calls to keep the data current. **API connection method**: Connect to the PredictHQ Events API to download the latest data from the API into your data lake. This guide takes you through using the Python API connection method for GCP but similar steps would apply to other programming languages. Events data is dynamic with events changing all the time as events are canceled, postponed, shift location, or have other details change. Also, new events are being announced all the time. We recommend having a process that updates your data on a regular frequency, such as daily. This guide will walk you through the initial data load, providing you with the tools and understanding necessary to create a robust connection to GCP. You'll learn how to structure your data effectively within your warehouse, setting the stage for potential automation and regular updates that you can implement as needed. ## Table data structure Regardless of the method chosen for initial data creation and loading, the table structure remains consistent. This ensures that both methods are interchangeable, delivering data in a uniform format. The following table details the data structure: | Field Name | Datatype | Mode | | ----------------------------------- | --------- | -------- | | id | STRING | REQUIRED | | parent\_event | JSON | NULLABLE | | title | STRING | NULLABLE | | alternate\_titles | JSON | NULLABLE | | description | STRING | NULLABLE | | category | STRING | NULLABLE | | labels | JSON | NULLABLE | | phq\_labels | JSON | NULLABLE | | rank | INTEGER | NULLABLE | | local\_rank | INTEGER | NULLABLE | | phq\_attendance | INTEGER | NULLABLE | | entities | JSON | NULLABLE | | duration | INTEGER | NULLABLE | | start | TIMESTAMP | NULLABLE | | start\_local | TIMESTAMP | NULLABLE | | end | TIMESTAMP | NULLABLE | | end\_local | TIMESTAMP | NULLABLE | | predicted\_end | TIMESTAMP | NULLABLE | | predicted\_end\_local | TIMESTAMP | NULLABLE | | updated | TIMESTAMP | NULLABLE | | first\_seen | TIMESTAMP | NULLABLE | | timezone | STRING | NULLABLE | | location | JSON | NULLABLE | | geo | JSON | NULLABLE | | scope | STRING | NULLABLE | | country | STRING | NULLABLE | | place\_hierarchies | JSON | NULLABLE | | state | STRING | NULLABLE | | private | BOOLEAN | NULLABLE | | impact\_patterns | JSON | NULLABLE | | predicted\_event\_spend | INTEGER | NULLABLE | | predicted\_event\_spend\_industries | JSON | NULLABLE | ## JSONL file upload method We recommend this method for large data uploads, as it efficiently manages the transfer of large volumes of data better than direct API calls. ### Search Events in the WebApp and export JSONL To locate and export the relevant event data into a JSONL file, we utilize the PredictHQ [WebApp Search](https://control.predicthq.com/search/events). This tool allows for precise querying of events based on specific criteria, ensuring that you retrieve only the most relevant information for your needs. You can download everything you have access to, but most integrations are better served by scoping the export to the locations that matter - and production deployments by [managed delivery](/integrations/third-party-integrations.md) rather than manual exports. In the context of our example [Scenario](#scenario-toms-pizzeria) for Tom's Pizzeria, they would be downloading events for all of the US and then querying for specific locations. Many customers may bulk load all the data they have access to by exporting it all and then importing it into their data lake. In this example, we’ll download events only for Seattle. To do that, we searched for Seattle in the WebApp for the relevant period, status, and attended categories.
The WebApp Search page filtered to Seattle events, ready to export

WebApp Search for Seattle ready for Export

To export the events: 1. In the WebApp Search, set your filters and run the search. 2. Click **Export**. 3. Choose the **JSONL** file format. You can then upload the JSONL file directly to your BigQuery setup, as detailed in the [Create a table via JSONL upload](#create-a-table-via-jsonl-upload). ### Create a table via JSONL upload To set up a BigQuery table with a JSONL file, you need the necessary permissions on GCP. Before beginning, ensure you are clear about which dataset will host the data. Here are the steps to create the table once you have found and highlighted the dataset in GCP BigQuery: 1. **Click Create table**: In the dataset you wish to create the table in, click the **hamburger menu**, and then select **Create table**.
The BigQuery dataset menu open with the Create table option highlighted

Select destination dataset and use the hamburger menu to create table

2. **Select the File Location**: Select the JSONL export that you have downloaded somewhere on your computer. 3. **Name the Table**: Give the table about to be created a name that suits
The BigQuery Create table form with the file source, destination dataset, and table name filled in

table upload example details. Replace with your own dataset and table name

4. **Manually Define Schema**: Specify the schema details manually. Define each column accurately, and make sure the datatype and column names precisely match those in the [Table data structure](#table-data-structure). Any discrepancies in spelling or datatype lead to errors during the upload process. While you have flexibility to modify the schema by adding or removing columns based on your specific data requirements, this guide focuses on the recommended fields we suggest including.
The BigQuery Create table form with the schema fields defined manually

Follow our Table data structure and check for spelling

5. **Advanced options**: Expand the **Advanced options** and tick the **Unknown values** checkbox. This setting allows the system to gracefully handle missing information in specific columns of some records, ensuring that rows with incomplete data are not rejected or throw errors during the upload process. 6. **Create the Table**: To finalize the creation, click **Create table**.
The BigQuery Advanced options section with the Unknown values checkbox ticked

tick Unknown values and you're ready to create

This method allows initializing your BigQuery table with a JSONL dataset suitable for bulk data uploads. However, it does not support ongoing data refreshes. See the [Keep Event Data Updated](#keep-event-data-updated) section for advice on setting up a regularly updated table after initialization. ## API connection method This method works well for smaller datasets as initial upload and must be used for continuous updates to the table as recommended in our [Keep Event Data Updated](#keep-event-data-updated) section. ### Table creation code To establish the required data structure in BigQuery, you can utilize the following Python script. This script explicitly defines the columns and data types as laid out in our [Table data structure](#table-data-structure), configuring them precisely as needed for your BigQuery table. Before executing this script, ensure you have the following prerequisites: * **SERVICE\_ACCOUNT\_JSON**: This is your service account JSON key, which is typically stored in a secure file. If your organization uses a different method to handle service account keys, modify the code accordingly. * **dataset\_id**: Specify whether this is a new dataset or an existing one in which you want to place this table. * **table\_id**: Determine a name for your new PredictHQ data table in BigQuery. Manage this table creation code separately from other data processing scripts to maintain clarity and ease of updates. Use the following script: {% code lineNumbers="true" fullWidth="true" %} ```python from google.cloud import bigquery #Set Service Account Key connection SERVICE_ACCOUNT_JSON='~/file_location/service_account_key.json' # Set BigQuery variables dataset_id = "[dataset_name]" table_id = "[table_name]" # Initialize the BigQuery client client = bigquery.Client.from_service_account_json(SERVICE_ACCOUNT_JSON) # Manually create schema schema = [ bigquery.SchemaField("id", "STRING", mode="REQUIRED"), bigquery.SchemaField("parent_event", "JSON"), bigquery.SchemaField("title", "STRING"), bigquery.SchemaField("alternate_titles", "JSON"), bigquery.SchemaField("description", "STRING"), bigquery.SchemaField("category", "STRING"), bigquery.SchemaField("labels", "JSON"), bigquery.SchemaField("phq_labels", "JSON"), bigquery.SchemaField("rank", "INTEGER"), bigquery.SchemaField("local_rank", "INTEGER"), bigquery.SchemaField("phq_attendance", "INTEGER"), bigquery.SchemaField("entities", "JSON"), bigquery.SchemaField("duration", "INTEGER"), bigquery.SchemaField("start", "TIMESTAMP"), bigquery.SchemaField("start_local", "TIMESTAMP"), bigquery.SchemaField("end", "TIMESTAMP"), bigquery.SchemaField("end_local", "TIMESTAMP"), bigquery.SchemaField("predicted_end", "TIMESTAMP"), bigquery.SchemaField("predicted_end_local", "TIMESTAMP"), bigquery.SchemaField("updated", "TIMESTAMP"), bigquery.SchemaField("first_seen", "TIMESTAMP"), bigquery.SchemaField("timezone", "STRING"), bigquery.SchemaField("location", "JSON"), bigquery.SchemaField("geo", "JSON"), bigquery.SchemaField("scope", "STRING"), bigquery.SchemaField("country", "STRING"), bigquery.SchemaField("place_hierarchies", "JSON"), bigquery.SchemaField("state", "STRING"), bigquery.SchemaField("private", "BOOLEAN"), bigquery.SchemaField("impact_patterns", "JSON"), bigquery.SchemaField("predicted_event_spend", "INTEGER"), bigquery.SchemaField("predicted_event_spend_industries", "JSON") ] #basic create code with no error handling dataset_ref = client.dataset(dataset_id) table_ref = dataset_ref.table(table_id) table = bigquery.Table(table_ref, schema=schema) table = client.create_table(table) # Make an API request to create the table. print("Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)) ``` {% endcode %} This script sets up the initial table structure within BigQuery, providing a foundation for subsequent data loads. ### Extract from Events API This section outlines the process of querying the PredictHQ Events API using Python, using the example for Tom's Pizzeria. This approach ensures the extracted data is directly relevant to Tom’s operational needs. The [Scenario](#scenario-toms-pizzeria) section of this guide explains the methodology and rationale behind the data extraction parameters used below. Before initiating the script, ensure you have configured the following prerequisites: * **phq\_access\_token**: Insert your PredictHQ access token here. For enhanced security, consider referencing the token indirectly if you prefer not to hard-code it into your script. * **SERVICE\_ACCOUNT\_JSON**: This is your Google Cloud Platform service account JSON key, which should be securely stored. Adjust the access method in the script if your organization's practices differ. * **dataset\_id**: Designate whether this is a new or existing dataset in which you plan to house the PredictHQ data. * **table\_id**: Assign a name to your new PredictHQ data table in BigQuery. * **params**: Modify these parameters as needed to align with the data you intend to extract from PredictHQ. This Python script fetches the necessary data from the Events API. This method loops through the paginated response from the API and pulls all results and columns. The [next section](#transform-api-responses) covers transforming this data before we push for upload.\ For more details on any of the parameters we’ve used in the script that follows, see our [Events API](https://docs.predicthq.com/api/events/search-events) documentation, keeping in mind our [Scenario](#scenario-toms-pizzeria) to pull attended results in Seattle for Tom. Use the following script: {% code lineNumbers="true" fullWidth="true" %} ```python import time import requests import json from google.cloud import bigquery base_url = "https://api.predicthq.com/v1/events" access_token = "[phq_phq_access_token]" dataset_id = "[dataset_name]" table_id = "[table_name]" SERVICE_ACCOUNT_JSON='~/file_location/service_account_key.json' headers = { "Authorization": f"Bearer {access_token}", "Accept": "application/json" } params = { # adjust these parameters to correspond to the data you want to pull down "categories": "community,conferences,concerts,expos,festivals,performing-arts,sports", "place.scope": "5809844", #Seattle geonames ID "active.gte": "2024-06-01", "active.lte": "2024-06-30", "state": "active,predicted", "limit": 500 # Maximum number of events per page } # Initialize the BigQuery client client = bigquery.Client.from_service_account_json(SERVICE_ACCOUNT_JSON) def fetch_all_pages(base_url, headers, params): print("calling Events API") results = [] next_url = base_url # Start with the base URL while next_url: response = requests.get(next_url, headers=headers, params=params) params = None # After the first request, prevent re-sending initial parameters if response.status_code == 200: data = response.json() results.extend(data.get('results', [])) next_url = data.get('next') # Update the next_url from the 'next' field in the response else: print(f"Failed to fetch data: {response.status_code}, Message: {response.text}") break print("total events fetched", len(results)) return results events_data = fetch_all_pages(base_url, headers, params) ``` {% endcode %} ### Transform API responses To ensure seamless compatibility with the BigQuery table structure, we run a transformation function applied to the data retrieved from the Events API in the previous step. The function performs essential adjustments, including: * Handling null or empty responses to maintain data integrity. * Restricting output to only the columns defined in our table structure ensures consistency and relevance. * Formatting complex fields to JSON, suitable for BigQuery ingestion. To transform the data after extraction, append this transformation code to the extraction code from the previous step. This code readies the data for loading into BigQuery: {% code lineNumbers="true" fullWidth="true" %} ```python def prepare_data_for_bigquery(events_data): schema_fields = set([ 'id', 'parent_event', 'title', 'alternate_titles', 'description', 'category', 'labels', 'phq_labels', 'rank', 'local_rank', 'phq_attendance', 'entities', 'duration', 'start', 'start_local', 'end', 'end_local', 'updated', 'predicted_end', 'predicted_end_local', 'first_seen', 'timezone', 'location', 'geo', 'scope', 'country', 'place_hierarchies', 'state', 'private', 'impact_patterns', 'predicted_event_spend', 'predicted_event_spend_industries' ]) # Fields that are to be in JSON format json_fields = {'phq_labels', 'entities', 'geo', 'location', 'impact_patterns', 'predicted_event_spend_industries', 'labels', 'place_hierarchies', 'parent_event', 'alternate_titles'} cleaned_events = [] for event in events_data: filtered_event = {} for key in schema_fields: if key in event and event[key] is not None: # If key is in json_fields, serialize it as JSON string if key in json_fields: # Ensure the data is properly structured as a JSON string filtered_event[key] = json.dumps(event[key]) else: filtered_event[key] = event[key] else: # Provide default None for missing fields, or an empty JSON structure as appropriate filtered_event[key] = 'null' if key in json_fields else None cleaned_events.append(filtered_event) return cleaned_events transformed_events_data = prepare_data_for_bigquery(events_data) ``` {% endcode %} ### Load data into the table Once your script has extracted the data from the API and transformed it to meet our [schema requirements](#table-data-structure), the next step involves loading this data into the previously established BigQuery table. This process utilizes Python code integrated with the BigQuery API to load the data. Below is the code block that you need to append to the end of the extraction and transformation script. It includes a basic retry mechanism to handle occasional upload failures, which is common in network-related operations. However, depending on your requirements for reliability and data integrity, you might consider implementing a more advanced retry logic. Append the following code: {% code lineNumbers="true" fullWidth="true" %} ```python def insert_data_with_retry(table_ref, data, max_attempts=3): for attempt in range(max_attempts): errors = client.insert_rows_json(table_ref, data) if not errors: print("Data inserted successfully.") return else: print(f"Attempt {attempt + 1} failed with errors: {errors}") time.sleep(2) # Wait for 2 seconds before the next retry print("Failed to insert data after several attempts.") table_ref = client.dataset(dataset_id).table(table_id) #Run insert data with retry insert_data_with_retry(table_ref, transformed_events_data) ``` {% endcode %} With this step completed, the script has loaded the data from the PredictHQ Events API into your BigQuery table, and the data is ready for analytical querying. This setup initially caters to a single load of data; however, to maintain the relevance and timeliness of your data, consider adapting this script to periodically update the dataset based on changes reflected in the "updated" timestamp column of the source data. See the [Keep event data updated](#keep-event-data-updated) section on updating your data. The full code that follows combines all three code parts into one executable:
Full Code {% code lineNumbers="true" fullWidth="true" %} ```python import time import requests import json from google.cloud import bigquery bse_url = "https://api.predicthq.com/v1/events" access_token = "[phq_phq_access_token]" dataset_id = "[dataset_name]" table_id = "[table_name]" SERVICE_ACCOUNT_JSON='~/file_location/service_account_key.json' headers = { "Authorization": f"Bearer {access_token}", "Accept": "application/json" } params = { # adjust these parameters to correspond to the data you want to pull down "categories": "community,conferences,concerts,expos,festivals,performing-arts,sports", "place.scope": "5809844", #Seattle geonames ID "active.gte": "2024-06-01", "active.lte": "2024-06-30", "state": "active,predicted", "limit": 500 # Maximum number of events per page } # Initialize the BigQuery client client = bigquery.Client.from_service_account_json(SERVICE_ACCOUNT_JSON) def fetch_all_pages(base_url, headers, params): print("calling Events API") results = [] next_url = base_url # Start with the base URL while next_url: response = requests.get(next_url, headers=headers, params=params) params = None # After the first request, prevent re-sending initial parameters if response.status_code == 200: data = response.json() results.extend(data.get('results', [])) next_url = data.get('next') # Update the next_url from the 'next' field in the response else: print(f"Failed to fetch data: {response.status_code}, Message: {response.text}") break print("total events fetched", len(results)) return results events_data = fetch_all_pages(base_url, headers, params) def prepare_data_for_bigquery(events_data): schema_fields = set([ 'id', 'parent_event', 'title', 'alternate_titles', 'description', 'category', 'labels', 'phq_labels', 'rank', 'local_rank', 'phq_attendance', 'entities', 'duration', 'start', 'start_local', 'end', 'end_local', 'updated', 'predicted_end', 'predicted_end_local', 'first_seen', 'timezone', 'location', 'geo', 'scope', 'country', 'place_hierarchies', 'state', 'private', 'impact_patterns', 'predicted_event_spend', 'predicted_event_spend_industries' ]) # Fields that are to be in JSON format json_fields = {'phq_labels', 'entities', 'geo', 'location', 'impact_patterns', 'predicted_event_spend_industries', 'labels', 'place_hierarchies', 'parent_event', 'alternate_titles'} cleaned_events = [] for event in events_data: filtered_event = {} for key in schema_fields: if key in event and event[key] is not None: # If key is in json_fields, serialize it as JSON string if key in json_fields: # Ensure the data is properly structured as a JSON string filtered_event[key] = json.dumps(event[key]) else: filtered_event[key] = event[key] else: # Provide default None for missing fields, or an empty JSON structure as appropriate filtered_event[key] = 'null' if key in json_fields else None cleaned_events.append(filtered_event) return cleaned_events transformed_events_data = prepare_data_for_bigquery(events_data) def insert_data_with_retry(table_ref, data, max_attempts=3): for attempt in range(max_attempts): errors = client.insert_rows_json(table_ref, data) if not errors: print("Data inserted successfully.") return else: print(f"Attempt {attempt + 1} failed with errors: {errors}") time.sleep(2) # Wait for 2 seconds before the next retry print("Failed to insert data after several attempts.") table_ref = client.dataset(dataset_id).table(table_id) #Run insert data with retry insert_data_with_retry(table_ref, transformed_events_data) ``` {% endcode %}
## Keep event data updated Event data is dynamic and events can change frequently. This happens when events are canceled, posted, or have details updated. Also, PredictHQ’s pipeline is constantly fetching new events so new future events are always being added and can be downloaded via the API. To keep your data updated see [Keep data updated via API](https://docs.predicthq.com/integrations/integration-guides/keep-data-updated-via-api). Use a similar code to the [API connection method code](#api-connection-method) using the ‘updated’ parameter to filter for recently changed events. This extracts all new events and updates to events. Check for events updated since your last table update using the ‘updated’ timestamp column. You will need to code for updating and replacing the data in BigQuery according to your preferred data update standards, but the structure will be the same as outlined earlier. We recommend running a daily update process (such as a cron job) that calls the PredictHQ API and updates the data in your data lake. ## Querying the loaded data Once the data is successfully loaded into BigQuery, you can begin querying it to derive insights relevant to your use case. This section provides an example of a BigQuery SQL query tailored to Tom’s scenario as outlined earlier. While the initial data load might have utilized specific filters via the WebApp or API parameters, it's often useful to perform additional queries directly within BigQuery. This capability is particularly valuable if you have loaded a broader dataset and need to perform dynamic or complex filtering post-load. Some common fields to query are: * **category:** filter down to the relevant categories most likely impacting your [industry](https://docs.predicthq.com/getting-started/guides/industry-specific-event-filters) * **start and end:** filter on start and end dates being in a specified time period. You might be looking at events in the next week or month or longer. * **rank:** is commonly used to filter out smaller events. Filter where rank is equal to or greater than a specific field to filter out smaller events. * **geo:** This field contains geojson data (see the [GeoJSON overview](https://docs.predicthq.com/getting-started/guides/geolocation-guides/overview#geojson) for more details) on the location event. For attended events, this field typically holds the latitude and longitude of the point at which the event is occurring. It can also hold [polygon](https://docs.predicthq.com/getting-started/guides/geolocation-guides/working-with-polygons) information for events that cover a wide area like marathons or severe weather events. For marathons, the polygon shows the route of the marathon. Query on this field to find all events in an area like a radius. See the [Filtering Guide](/getting-started/guides/events-api-guides/filtering-and-finding-relevant-events.md) for more examples. The SQL example later in this section shows how to query these fields in the database. Below is a sample BigQuery SQL query that aligns with the parameters specified for our example. This query filters events based on the categories, date range, event rank, and geographical proximity to Tom’s location. You use this type of query to find all events within a specified radius of a business location. For example a restaurant, hotel, store, parking garage, or any other business location. Once you have the data loaded into GCP you will want to find how events are impacting your locations. Use the type of query in the earlier example for each location to get all the events around that location: {% code lineNumbers="true" fullWidth="true" %} ```sql SELECT * FROM `predicthq_dataset_test.phq_api_json_format` WHERE category IN ('concerts','conferences','festivals','performing-arts') AND `start_local` >= '2024-06-01' --Change this to the date range you want to display events for - e.g. the next 30 days AND `end_local` <= '2024-06-30' AND rank >= 30 AND ST_Distance( ST_GeogPoint( CAST(JSON_EXTRACT_SCALAR(geo, '$.geometry.coordinates[0]') AS FLOAT64), CAST(JSON_EXTRACT_SCALAR(geo, '$.geometry.coordinates[1]') AS FLOAT64)), ST_GeogPoint(-122.33, 47.60) --Tom's store location in our example ) <= 2400 -- Approximately 1.48 miles in meters --When implementing this use the Predicted Impact Area API to find the area for your location AND state IN ('active', 'predicted'); ``` {% endcode %} This query retrieves records that meet all the specified criteria, allowing Tom to identify events that could potentially influence the operations and traffic at this pizzeria in Seattle. Modify the earlier query to fit the specific fields and data types of your table if they differ from this example, and fill your latitude and longitude for your locations using our [Predicted Impact Area API](https://docs.predicthq.com/api/impact-area/get-impact-area) to determine the optimal area for each location. Visually, this type of query allows you to pull all the events in a radius, as shown in the Radius Map image that follows:
A map showing a circular radius around a business location with the surrounding events plotted inside it

Radius Map example from our website

A common example is customers often look at events occurring in the next one to three months and may display this information in their application, in a BI tool, or in other types of products and tools. A common approach to doing this can be to have a table with a list of your business locations with latitude and longitude for each. For each, call the [Predicted Impact Area API](https://docs.predicthq.com/api/impact-area/get-impact-area) to determine the Predicted Impact Area and then look up your store locations in the table. For example you may have a table of locations like the following:
locationlattitudelongituderadiusradius_unitdate_startdate_end
store1-chicago41.8131-87.65864.11mi2023-07-012023-12-31
Hyde Park51.50736-0.164112.06mi2024-01-012024-03-31
store10-new-yor40.73061-73.93524............
You can look up location details from this table (to find the lat/long for a store for example) and then use it to find events with SQL like that shown in the earlier example. See our [Snowflake data science guide](https://docs.predicthq.com/integrations/third-party-integrations/snowflake/snowflake-data-science-guide) for an example of doing this in Snowflake. Using BigQuery for these queries ensures that you leverage powerful, scalable SQL analytics over large datasets, enabling responsive decision-making based on the latest event data available in your system.\\ ## Utilizing PredictHQ data in your data warehouse Having integrated PredictHQ's rich events data into your data warehouse, the opportunities to leverage this data are extensive. By now, you've successfully set up your data structure within Google Cloud Platform's BigQuery and have a solid understanding of the JSONL file Upload or the API Connection methods. Here’s how you can maximize the value of PredictHQ data within your organization: #### 1. Cross-referencing with internal datasets Enhance the granularity and relevance of your internal analytics by cross-referencing PredictHQ events data with your own datasets. For instance, you can correlate sales data with event occurrences to analyze the impact of local events on sales performance. This cross-analysis can be crucial for demand forecasting and strategic planning. Customers sometimes use fields like [Placekey ](https://docs.predicthq.com/getting-started/guides/geolocation-guides/join-events-using-placekey)to join events data and other location data. #### 2. Demand analysis and forecasting PredictHQ data can significantly enhance your demand forecasting models, especially for businesses that are impacted by local events, such as retail, hospitality, and transportation. By understanding when significant events are happening, you can better predict and prepare for attendance surges or declines. You can use Event data from your data warehouse in your demand forecast to improve forecast accuracy. See our [Snowflake data science guide](https://docs.predicthq.com/integrations/third-party-integrations/snowflake/snowflake-data-science-guide) for an example of how you can implement ML features for demand forecasting in a data warehouse. Although that example shows how to do this in Snowflake, a similar approach applies to other data warehouses. See also [Improving demand forecasting models with event features](/getting-started/guides/features-api-guides/improving-demand-forecasting-models-with-event-features.md). #### 3. Building tailored reports Use the data within BigQuery to create detailed reports and dashboards that monitor the effects of events on your business operations. These reports can provide actionable insights to business users across your organization, from marketing teams planning campaigns around major events to supply chain management preparing for increased activity. See [Use Events Data in Power BI](/integrations/third-party-integrations/using-event-data-in-power-bi.md) for an example of building reports in Power BI. You can connect Power BI or other BI tools to your database to build dashboards and reports. See also [How to Connect PowerBI to Google BigQuery](https://learn.microsoft.com/en-us/power-query/connectors/google-bigquery). #### 4. Enhancing customer experience Inform your customers about local events that might impact their experience with your service or product. For example, a transportation company could provide passengers with real-time updates about events that might affect travel times or service availability. #### 5. Event-driven marketing Plan and execute marketing campaigns that align with upcoming events to capitalize on increased foot traffic or digital engagement. This targeted approach can improve marketing ROI by reaching audiences when they are most receptive. By tapping into the detailed and predictive insights provided by PredictHQ, your business can not only anticipate the impact of external events but also strategize proactively to harness potential opportunities or mitigate risks. Whether through enhancing predictive analytics, refining customer engagement strategies, or driving operational efficiencies, PredictHQ's events data serves as a powerful tool in your data-driven decision-making arsenal. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.predicthq.com/integrations/integration-guides/loading-event-data-into-a-data-warehouse.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.