Using Event Data in Tableau

Learn how to connect PredictHQ data to Tableau and build an example dashboard.

PredictHQ provides a rich repository of event data that can impact key business operations including demand forecasting and strategic planning. This tutorial demonstrates how to seamlessly integrate PredictHQ with Tableau to create dynamic visualizations, offering insights into how events may influence business trends. Follow along as we explore a quick and easy way to load a static event export as well as advice on connecting directly from data warehouses where PredictHQ data is stored.

Use Cases

Demand Forecasting, Dynamic Pricing, Workforce Optimization, Demand Analytics, Inventory Management, Event Visibility

Relevant Industries

Accommodation, Consumer Packaged Goods, Grocery and Supermarkets, Leisure, Travel and Tourism, Marketing and Advertising, Parking, Restaurants, Retail, Transportation and Delivery and Others

Getting Started

This tutorial requires access to both Tableau and PredictHQ.

1. Tableau: The instructions provided are based on Tableau Public, but other Tableau products, such as Tableau Desktop, should operate similarly.

2. PredictHQ: To download a static export of PredictHQ data, a PredictHQ account is required.

Connection with JSON File

Tableau supports various methods for connecting to data sources, including local files and data warehouses. This tutorial focuses on connecting to PredictHQ data via a JSON file.

Connecting via a JSON file in Tableau involves downloading a static snapshot of event data through bulk exporting from PredictHQ's Control Center. It is particularly useful for scenarios where real-time data updates are not essential. Ideal for quick testing or one-off analyses, this approach provides an efficient way to get started with event data in Tableau. This is a good way to try PredictHQ's data for the first time and explore how it can be useful in your business.

A JSON file export contains a structured list of events, much like a CSV export, but with better handling of nested data. While Tableau also supports CSV file connections, accessing nested data, such as Impact Patterns, is more challenging. In contrast, Tableau's native support for JSON files simplifies the integration and manipulation of nested information, making it the preferred method for connecting PredictHQ data.

Export File

Search Events

1. Access Control Center: Log in and navigate to Search Events.

2. Configure Filters: Set relevant filters, such as those for category, date, and location. Once set, click 'Search'.

Example Search

To view all attendance-based events in San Francisco scheduled or predicted to take place in May 2024, use the following URL with pre-configured filters:

https://control.predicthq.com/search/events?category=conferences,expos,concerts,festivals,performing-arts,community,sports&place.scope=5391959&active.gte=2024-05-01&active.lte=2024-05-31&state=active,predicted&sort=phq_attendance,-start

For guidance on finding the most relevant events for your business, see Filtering and Finding Relevant Events. For searches around specific locations or stores, exporting a JSON file from Control Center Saved Locations is recommended.

Export as JSON

1. Export Events: Once the events of interest are displayed on Control Center Search, click 'Export' on the right-hand side followed by 'Export Events Data'. In the dialog box that appears, select the 'JSONL' tab and then click 'Export'.

Export Events dialog box

1. Download Link: Once the export is ready, the dialog box will update with a download link. The link will also be sent via email.

2. Save Export: Download the file and save it to a directory for later use.

For more information on using Control Center Search and exporting files, see Guide to Bulk Export Data via Control Center.

Connect in Tableau

Connect to File

1. Start Tableau: Open Tableau and under 'Connect' select 'JSON file'.

2. Locate File: Navigate to the directory where the export was previously saved. It may be necessary to change the file extension filter from 'JSON Files (*.json)' to 'All Files (*.*)' in order to see and select the JSON lines file. Click 'Open' to load the file.

File extension options

For more information on connecting a local JSON file to Tableau and setting up the data source, see this Tableau article.

Flatten Nested JSON

1. Select Schema Levels: When the file is loaded, the 'Select Schema Levels' dialog box should automatically appear. The schema levels can also be modified by following these instructions. To ensure the data is structured correctly for this tutorial, select the following schema levels or follow these instructions to change the schema levels:

   1. Root Level: Typically named after the JSON file e.g. Events-Export-…

   2. Impact Patterns: Includes data related to impact patterns.

   3. Impacts: Details specific impact values for each day.

   

   Schema levels to select

For more information on schema levels, see this Tableau article.

Update Data Types

1. Check Data Types: Tableau automatically interprets a field's data type but this is not always correct. Review and adjust the data type where necessary by following these instructions, ensuring the following fields are set correctly for this tutorial:

Field	Correct Data Type
End Local	Date & Time
Date Local	Date
Start Local	Date & Time
State	String (Geographic Role: None)

For more information on data types, see this Tableau article.

Dashboard with Event Data

This section will guide you through creating a simple dashboard in Tableau, featuring a time series chart of daily event impact derived from Impact Patterns and a table listing relevant events. PredictHQ data is connected via a JSON file.

Worksheets

Chart

1. New Worksheet: Open a new worksheet and name it 'Time Series'.

2. Set Filters: Use filters to refine the data for events of interest only. Drag the following fields to the Filters shelf:

Folder	Field	Dialog Box
Event-Export-...	State	Filter [State] Under 'General' and 'Select from list', check active and predicted. Click 'OK'. Notes This filter is necessary unless these states have already been filtered via Control Center Search.
Event-Export-...	Category	Filter [Category] Under 'General' and 'Select from list', check community, concerts, conferences, expos, festivals, performing-arts, sports. Click 'OK'. Notes This filter is necessary unless these categories have already been filtered via Control Center Search.
Impact Patterns	Vertical	Filter [Vertical] Under 'General' and 'Select from list', check an industry e.g. accommodation. Click 'OK'. Note Vertical is the industry vertical associated with the impact pattern. This tutorial focuses on event day impact, which is the same for all industries. Choose any available industry if yours is not available.
Impacts	Date Local	Filter Field [Date Local] Select 'Range of Dates' then click 'Next'. Filter [Date Local] Set the minimum and maximum dates to '01/05/2024' and '31/05/2024', respectively. Click 'OK'. Notes Date Local is the date in the local time zone.
Impacts	Position	Filter [Position] Under 'General' and 'Select from list', check event_day. Click 'OK'. Notes Position categorizes Date Local in relation to when the event takes place, such as before, during, or after the event. While this tutorial focuses on the impact during event days, exploring impacts on other days is also encouraged.

For more information on PredictHQ event fields, see Events.

1. Apply Filters Globally: Apply the above filters to 'all worksheets using this data source' by right-clicking each field in the Filters shelf and following these instructions. This prevents the need to repeat configurations across multiple worksheets, ensuring consistency in data.

2. Create Chart:

   1. Drag Value from 'Source Measures' to the Row shelf.

   2. Drag Date Local from 'Impacts' to the Column shelf. Then right-click on the Date Local pill and select the 'Exact Date' format.

   3. Update the y-axis title to 'Daily Event Day Impact' by following these instructions.

3. Chart Preview:

Time Series worksheet

Table

1. New Worksheet: Open a new worksheet and call it 'Event Info'.

2. Create Table:

   1. Add all relevant fields to the Row shelf and ensure they are all formatted as 'Discrete' to produce the correct table. This formatting change should turn all the pills blue. For this tutorial, the following fields are considered:

Folder	Field	Notes
Impacts	Date Local	Right-click on the pill and select the 'Exact Date' and 'Discrete' formats.
Event-Export-...	Id	This is the ID of the event.
Event-Export-...	Category	This is the event category.
Event-Export-...	Start Local	This is the start date of the event in the local time zone. Right-click on each of the pills and select the 'Exact Date' and 'Discrete' formats.
Event-Export-...	End Local	This is the end date of the event iin the local time zone. Right-click on each of the pills and select the 'Exact Date' and 'Discrete' formats.
Event-Export-...	Timezone	The local time zone.
Source Measures	Phq Attendance	This is the predicted attendance for an event. Right-click on the pill and select the 'Discrete' format.
Source Measures	Value	This is the daily impact for an event. For this tutorial, only impact on event days is considered. Sort by descending `Value` by following these instructions.

For more information on PredictHQ event fields, see Events.

1. Table Preview:

Event Info worksheet

Dashboard

1. New Dashboard: Open a new dashboard.

   1. Set the size of the dashboard to 'Automatic' by following these instructions to ensure the dashboard adjusts to fit the screen it's being viewed on.

2. Add Worksheets: From the Sheets list, drag the Time Series sheet anywhere in the dashboard and then 'Event Info' to the right. Resize the layout containers as needed.

3. Set Filters: Use the Time Series worksheet as an interactive filter by following these instructions. This allows you to click on specific dates in the chart to dynamically filter the events displayed in the table.

4. Dashboard Preview:

Dashboard

For more information on creating dashboards, see this Tableau article.

Next steps

There are several ways to expand the functionality and relevance of this dashboard to your specific business needs:

1. Incorporate Business Metrics: Integrate key metrics such as sales figures or units sold to gain a comprehensive view of how events influence business performance. Linking event impacts with sales data can help uncover high-level trends and support data-driven decision-making.

2. Explore Impact Patterns: While this tutorial focuses on the immediate impacts of events, exploring additional impact patterns could yield deeper insights. For example, examining patterns leading up to and after events might reveal extended influences on consumer behavior or operational demands.

3. Customize Visualizations: Enhance the dashboard with more complex visualizations like heat maps or geographic visualizations. These can offer spatial insights into where events have the most impact, assisting in regional marketing strategies and resource allocation.

4. Up-to-date Event Data: Ensure the dashboard reflects the most current data by considering connections like Snowflake or Amazon Data Exchange. These methods (see examples below) offer real-time updates which are essential for accommodating the dynamic nature of event data.

Other Connection Methods

While this tutorial primarily focuses on connecting via a JSON file, other connection methods are available for those needing real-time updates or integration with other data services

Snowflake

This connection method involves accessing PredictHQ data through Snowflake’s Secure Data Share and connecting it to Tableau.

Snowflake Data Share

1. Setup: Coordinate with your Snowflake administrator to set up a Data Share with PredictHQ.

2. Database: Create a database from the Data Share and ensure that the necessary permissions are granted to users.

For more information on receiving PredictHQ data via Snowflake, see this guide.

Connect to Snowflake in Tableau

1. Connection Details: Before connecting, gather all necessary information including:

   1. Login credentials for Snowflake authentication

   2. Server, Warehouse, Database and Schema information to set up the data source.

2. Start Tableau: Open Tableau and connect to Snowflake by following these instructions.

For more information on connecting to Snowflake in Tableau and setting up the data source, see this Tableau article.

Filter for Relevant Events in Tableau

1. Configure SQL Query: Use Tableau’s custom SQL query to manage how data is brought in for subsequent analyses. For PredictHQ data, this typically involves flattening nested JSON, converting data types, and applying filters, such as category, date, and location, to filter for relevant events. See this Power BI tutorial for an example of how this query might be structured.

For more information on connecting to a custom SQL query, see this Tableau article.

Amazon S3

This connection method involves accessing PredictHQ data through AWS Data Exchange and connecting it to Tableau.

AWS Data Exchange

1. Setup: Subscribe to PredictHQ data on AWS Data Exchange

2. Amazon S3: Copy the data to your specified S3 bucket. Ensure that your AWS IAM user or role has read permissions for this bucket.

For more information on receiving PredictHQ data via AWS Data Exchange, see this guide.

Connect to Amazon S3 in Tableau

1. Connection Details: Before connecting, gather all necessary information including:

   1. AWS IAM access key for the S3 bucket.

   2. The name and AWS region of the S3 bucket.

2. Start Tableau: Open Tableau and connect to the S3 bucket by following these instructions.

For more information on connecting to Amazon S3 in Tableau and setting up the data source, see this Tableau article.

Filter for Relevant Events in Tableau

1. Configure SQL Query: Use Tableau’s custom SQL query to manage how data is brought in for subsequent analyses. For PredictHQ data, this typically involves flattening nested JSON, converting data types, and applying filters, such as category, date, and location, to filter for relevant events. See this Power BI tutorial for an example of how this query might be structured.

For more information on connecting to a custom SQL query, see this Tableau article.

More Common Connectors

Some other connectors commonly used with PredictHQ data include the following:

• Google BigQuery

• Azure SQL Database

• Amazon Redshift

For more information on loading PredictHQ data into data warehouses, see this guide which provides an example using Google BigQuery.

See this article for all connectors supported by Tableau. Once PredictHQ data is connected to a data warehouse, techniques similar to those described in this guide can be applied for querying data from these sources.

Conclusion

This tutorial has demonstrated how to connect PredictHQ data to Tableau and build a functional dashboard. This is just one way to harness the power of event data. Continue to refine and expand your dashboard to align with your business needs, maximizing the insights available and thereby enhancing strategic decision-making processes.

Resources for Download

6MB

Events-Export-San-Francisco-from-20240501-to-20240531.jsonl

JSON file

460KB

PredictHQ Example Dashboard.twbx

Tableau workbook