​
Table of Contents

  1. BigQuery Setup
    1.1 Project and Billing
    1.2 Enabling APIs and Permissions
    1.3 Creating a Dataset and Table
    1.4 Schema and Table Expiration
  2. Octanist Setup

​
1. BigQuery Setup

​
1.1 Project and Billing

Before setting up Google BigQuery for Octanist, you need a BigQuery environment with billing enabled. Don’t worry, the cost is typically between €0–€1 per month for storing data.
  1. Go to Google BigQuery.
  2. Sign in with the Google account you want to associate with the BigQuery environment. We recommend using a client-owned account or one that the client can always access.
  3. Create or select a project. Use a clear and concise name, you can’t change it later.
  4. Select the appropriate organization (usually your business or your client’s), then click Create.
Create BigQuery Project Search for Billing and open it. You must set up a billing account to enable and use specific APIs required by Google BigQuery. Next, search for BigQuery API, open it, and click Enable. A green checkmark will confirm activation. Enable BigQuery API

​
1.2 Enabling APIs and Permissions

Now, search for IAM & Admin, and click Grant Access. Grant Access Add relevant users (e.g., your team, your agency’s email, your client). Assign the Owner or BigQuery Admin role so they can manage the lead data and perform analysis. Add Roles Return to the search bar, search for BigQuery, and click it to open the BigQuery Studio. Open BigQuery Studio

​
1.3 Creating a Dataset and Table

  1. In BigQuery Studio, click the three dots next to your project name and select Create dataset.
Create Dataset
  1. Enter a Dataset ID (e.g., octanist_dataset). This cannot be changed later.
  2. Choose a Region close to your business (e.g., a region containing “europe” or the “EU” multi-region).
Dataset ID and Region Once the dataset is created:
  1. Click the three dots next to the dataset name and choose Create table.
Create Table
  1. In the Create Table menu:
    • Select Empty table.
    • Ensure the project and dataset are filled in correctly.
    • Enter a Table Name (e.g., export_bigquery_integration or exported_leads_octanist).
  2. Scroll down to Partitioning Settings:
    • Set Partition by ingestion time
    • Choose By day
This keeps the dataset manageable and cost-efficient.
  1. Scroll up to Schema, switch to Edit as text, and paste the schema below:

[
  {
    "name": "id",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Lead unique identifier"
  },
  {
    "name": "organizationId",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Organization ID that owns this lead"
  },
  {
    "name": "name",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Lead name"
  },
  {
    "name": "email",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Lead email address"
  },
  {
    "name": "phone",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Lead phone number"
  },
  {
    "name": "custom",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Custom field data"
  },
  {
    "name": "gclid",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Google Click ID"
  },
  {
    "name": "fbc",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Facebook Click ID"
  },
  {
    "name": "fbp",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Facebook Browser ID"
  },
  {
    "name": "ga4cid",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Google Analytics 4 Client ID"
  },
  {
    "name": "ga4sid",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Google Analytics 4 Session ID"
  },
  {
    "name": "li_fat_id",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "LinkedIn First Party Ad Tracking ID"
  },
  {
    "name": "msclkid",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Microsoft Click ID"
  },
  {
    "name": "path",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Website path where lead was generated"
  },
  {
    "name": "website",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Website domain"
  },
  {
    "name": "ad_storage",
    "type": "BOOLEAN",
    "mode": "NULLABLE",
    "description": "Google Consent Mode - Ad Storage"
  },
  {
    "name": "ad_user_data",
    "type": "BOOLEAN",
    "mode": "NULLABLE",
    "description": "Google Consent Mode - Ad User Data"
  },
  {
    "name": "ad_personalization",
    "type": "BOOLEAN",
    "mode": "NULLABLE",
    "description": "Google Consent Mode - Ad Personalization"
  },
  {
    "name": "analytics_storage",
    "type": "BOOLEAN",
    "mode": "NULLABLE",
    "description": "Google Consent Mode - Analytics Storage"
  },
  {
    "name": "status",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Lead status: new, open, win, lost, expired, archived"
  },
  {
    "name": "note",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Notes about the lead"
  },
  {
    "name": "utm_source",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "UTM source parameter"
  },
  {
    "name": "utm_medium",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "UTM medium parameter"
  },
  {
    "name": "utm_campaign",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "UTM campaign parameter"
  },
  {
    "name": "value",
    "type": "FLOAT",
    "mode": "NULLABLE",
    "description": "Lead value"
  },
  {
    "name": "currency",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Currency code (ISO 4217)"
  },
  {
    "name": "conversionName",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Conversion name/type"
  },
  {
    "name": "createdAt",
    "type": "TIMESTAMP",
    "mode": "NULLABLE",
    "description": "When the lead was originally created"
  },
  {
    "name": "updatedAt",
    "type": "TIMESTAMP",
    "mode": "NULLABLE",
    "description": "When the lead was last updated"
  },
  {
    "name": "labelId",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Associated label ID"
  },
  {
    "name": "leadOfTheMonth",
    "type": "INTEGER",
    "mode": "NULLABLE",
    "description": "Lead of the month counter"
  },
  {
    "name": "qualifiedAt",
    "type": "TIMESTAMP",
    "mode": "NULLABLE",
    "description": "When the lead was qualified"
  },
  {
    "name": "event_type",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Event type: win, qualified, lost"
  },
  {
    "name": "event_timestamp",
    "type": "TIMESTAMP",
    "mode": "NULLABLE",
    "description": "When the event was sent to BigQuery"
  }
]
After this is set up, click Create Table. The settings should look as follows: Table Settings Lastly, before switching back to Octanist, click on the dataset (the square icon with four dots), then click Edit Details in the top right.

Uncheck Enable table expiration. Make sure this option is disabled, otherwise, your data will be deleted after 60 days. Disable Table Expiry

​
Octanist

Now head back to Octanist and create a new outgoing integration: Create Integration
  1. Select Google BigQuery.
  2. Enter a clear and concise name for the integration.
  3. Under Google Connection, click Connect and log in using the Google account with the necessary BigQuery permissions (as set in the IAM & Admin section above).
Note: A second window may open during the authentication process. If it does, complete the setup in that window, then close it. This is related to Google Authentication and will be improved in future updates.
  1. Enter your:
    • Project name
    • Dataset ID
    • Table name
The final setup should look like this: Octanist Setup Once configured, you can start qualifying your leads — these will be automatically sent to BigQuery.

​
The following lead statuses will be sent to BigQuery:

  • Won Leads
  • Qualified Leads
  • Lost Leads
Each lead includes a timestamp, allowing you to calculate the time it takes for a lead to convert to a sale. If you’d like to export historical leads, we can provide that. Just send your request to support@octanist.com.