Use BigQuery as a source — pulling events for reporting

Create Kameleoon goals that pull conversion data from BigQuery on a scheduled basis to measure experiment results using your warehouse data.

Data warehouse integrations are available as a premium add-on for the Web Experimentation and Feature Experimentation module. Contact your Customer Success Manager for more information.

With the BigQuery integration, you can display BigQuery metrics as KPIs on Kameleoon’s Results pages. Kameleoon uses your BigQuery instance as a data source for goal conversions, consistently updating data upon goal creation.

Key benefits:

• Allows for the definition and regular updating of specific data-driven goals, simplifying the decision-making process and ensuring campaigns align with objectives.
• Use BigQuery metrics as key performance indicators, providing a comprehensive view of campaign performance.

Once you’ve activated BigQuery for a specific project, you can use it to create goals in Kameleoon. These goals use conversions data from your BigQuery database.

To create a goal using BigQuery, follow the Data warehouse tracking flow described in Create a goal, and choose BigQuery from the Datawarehouse integration dropdown. Step 1 then asks for these BigQuery-specific fields:

• Frequency: Define how often you want Kameleoon to update the goal data.
• BigQuery project ID: Enter your BigQuery project ID to direct the data to the correct location.
• Region: Select the appropriate region from the provided list.
• Query: Define the SQL query to retrieve the necessary data from BigQuery.

Once Step 1 is complete, configure Step 2, Advanced settings, if needed, then save your goal.

Query format

The query must adhere to a specific format:

SELECT visitor_id, conversion_timestamp FROM your_events_table

Where visitor_id is the column representing the unique ID of your visitors, and conversion_timestamp is a column that represents the exact time at which the conversion took place. In BigQuery, the conversion_timestamp column must be a Timestamp type column.
If you want to associate a revenue to each conversion, the query should adhere to an alternate format:

SELECT visitor_id, conversion_timestamp, revenue FROM your_events_table

Where revenue is a column containing the revenue for each conversion.

For more complex queries you can adhere to this format by formulating a sub-query as such:

SELECT visitor_id, conversion_timestamp, revenue FROM ( {your_original_query} ) AS subquery

Your query runs every hour in your BigQuery warehouse, with an added WITH clause that filters by timestamps. Keep in mind that although Kameleoon collects conversions hourly, it only merges them into your experiment results once per day.

Run your query before ingestion

Before saving your ingestion task, you can test your query directly in Kameleoon. Testing allows you to:

• Verify the connection in real time.
• Confirm your credentials and access rights are correct, which helps detect issues immediately, without having to wait for the first data import.
• Validate your data’s structure and accessibility.

You can run a simple query to ensure Kameleoon can access the right databases and tables. You can also download a small sample of the dataset to confirm that the schema, permissions, and contents match your expectations.

Using your BigQuery goal

With the goal created, you can incorporate it into your Kameleoon campaigns. When setting up an experiment or personalization, you’ll have the option to select this goal in the configuration tab, letting you track and analyze specific conversions from your BigQuery database.

To learn how to set up a goal in an experiment, see Set up a goal in a campaign.