How to integrate Birdie + Bigquery

With the BigQuery connector, Birdie can import data straight from your enterprise data warehouse. Once a day, Birdie checks for new records in your table by running a query to filter by a date column that should identify when the row was added to the table.

Requirements

Dedicated table for Birdie integration.
- The table should preferably partitioned by a date column that identifies when the row was added to the table.
- The daily query uses the last import date and the current date as the interval for checking for new rows.
- See docs on creating date partitioned tables.
Birdie integration requires a service account key for authentication with Bigquery Data Viewer and Bigquery Job User access to the project/table.
- See docs on creating a service account.
- See docs on creating a service account key.
- See docs on BigQuery prefined IAM roles for more information on the Bigquery Data Viewer and BigQuery Job User roles we suggested.

Parameters

Kind: The kind of data you're trying to import. This defines what schema Birdie expects when reading rows from the table. See the schema section for more information. Supported values are:
- Review
- NPS
- CSAT
- Support Ticket
- Social Media Post
- Issue
Credentials: A JSON Service account key.
Dataset: The name of the dataset the table belongs to (without the Project ID), e.g gold_layer.
Table: The name of the table to query the data from, e.g support_tickets
Project ID: The unique ID the table and dataset belongs to.
- Optional. Defaults to the Project ID the service account belongs to.
Partition Column: The name of the date column to use for checking for new records. We suggest using the date partition column.
- Optional. Defaults to posted_at
Start date: Date filter to apply to the partition column.

BigQuery Schemas

Each row of the table must fit within one of the following schemas. The schema must match the kind selected when configuring the parameters.

See the official BigQuery docs for more information on the supported types.

Feedbacks // Review

Column Name	Type	Required	Description
feedback_id	`STRING`	✅	Unique identifier for each review.
text	`STRING`	✅	Text posted by user
posted_at	`DATE` or `TIMESTAMP`	✅	The date the record was posted.
author_id	`STRING`		Identifier for the author of the the record.
account_id	`STRING`		Identifier for the account the record belongs to.
language	`STRING`		Language of the record as BCP 47 code.
title	`STRING`		The title of the feedback given by the author.
rating	`FLOAT`	✅	A rating or score of the feedback.
category	`STRING`		The category the review belongs to.
owner	`STRING`	✅	One of: `Owner, Competitor`.

Feedbacks // NPS and CSAT

Column Name	Type	Required	Description
feedback_id	`STRING`	✅	Unique identifier for each answer.
text	`STRING`		Text posted by user
posted_at	`DATE` or `TIMESTAMP`	✅	The date the record was posted.
author_id	`STRING`		Identifier for the author of the the record.
account_id	`STRING`		Identifier for the account the record belongs to.
language	`STRING`		Language of the record as BCP 47 code.
title	`STRING`		The title of the survey.
rating	`FLOAT`	✅	A rating or score of the feedback.
author_name	`STRING`		The name of the author.

Conversation // Support Ticket

Column Name	Type	Required	Description
conversation_id	`STRING`	✅	Unique identifier for each conversation.
message_id	`STRING`	✅	Unique identifier for each message.
author_id	`STRING`		Identifier for the author of the message.
account_id	`STRING`		Identifier for the account the message belongs to.
text	`STRING`	✅	Text of the message.
posted_at	`DATE` or `TIMESTAMP`	✅	The time the message was sent.
language	`STRING`		Language of the message as BCP 47 code.
subject	`STRING`		Subject of the ticket.
status	`STRING`		Status of the ticket, e.g `open`.
priority	`STRING`		Priority assigned to the ticket.
channel	`STRING`		Source channel of the ticket, e.g `web`.
tags	`REPEATED STRING`		Array of tags applied to the ticket.
author_type	`STRING`	✅	One of: `Internal Person, User, Bot`.
author_name	`STRING`		The name of the author of the message.

Conversation // Issue

Column Name	Type	Required	Description
conversation_id	`STRING`	✅	Unique identifier for each conversation.
message_id	`STRING`	✅	Unique identifier for each message.
author_id	`STRING`		Identifier for the author of the message.
account_id	`STRING`		Identifier for the account the message belongs to.
text	`STRING`	✅	Text of the message.
posted_at	`DATE` or `TIMESTAMP`	✅	The time the message was sent.
language	`STRING`		Language of the message as BCP 47 code.
project_id	`STRING`		Project identifier.
project_name	`STRING`		Project Name.
title	`STRING`		Issue title.
status	`STRING`		Issue status.
author_name	`STRING`		The name of the author of the message.

Conversation // Social Media Post

Column Name	Type	Required	Description
conversation_id	`STRING`	✅	Unique identifier for each conversation.
message_id	`STRING`	✅	Unique identifier for each message.
author_id	`STRING`		Identifier for the author of the message.
account_id	`STRING`		Identifier for the account the message belongs to.
text	`STRING`	✅	Text of the message.
posted_at	`DATE` or `TIMESTAMP`	✅	The time the message was sent.
language	`STRING`		Language of the message as BCP 47 code.
title	`STRING`		Title of the post.
owner	`STRING`		One of: `Owner, Competitor`.
category	`STRING`		The category the post was under, e.g a subreddit name.
url	`STRING`		URL of the post.
channel	`STRING`		Source channel of the post, e.g `facebook`.
tags	`REPEATED STRING`		Array of tags applied to the post.
author_type	`STRING`	✅	One of: `Internal Person, User, Bot`.
author_name	`STRING`		The name of the author of the message.
upvotes	`INTEGER`		The number of upvotes the message has.

Custom Fields

Any columns that don't fit under the previously listed schemas may become custom fields.

The name of the column in BigQuery must be configured as the key/source of the custom field inside the Birdie App.