AI is now part of our day to day life, however it's important to become a prompt pro to maximise the impact of your AI collaboration with GCP and BigQuery. 

​

1. Be Clear and Specific About Your Needs

Start by explaining the problem you want to solve or the code you need. Be explicit about what you're trying to achieve so the AI can provide code that directly addresses your requirements.

Example:

• Less effective: "I need help with SQL."

• More effective: "I need SQL code to extract the path from URLs in GA4 event data using a regex."

​

2. Provide Context

Describe the environment or framework you're working in. This could be the database, programming language, API, or technology stack. The more context you provide, the better the AI can tailor the code. be specific, provide examples and your variable names.

​

GA4 BigQuery Example:

• Context: "I'm analysing GA4 export data in BigQuery. I need to extract the page path level 2 from the page_location parameter field using a BigQuery regex to extract this. An example of this URL is 'www.bigquery.co.uk/biqquery-cookbook-recipe-1&utm_source=xxxx".

Result:

 SELECT
  REGEXP_EXTRACT(page_location, r'www\.bigquery\.co\.uk\/([^?&\/]+)') AS page_path_level_2
FROM
  `your_dataset.your_table`
​

3. Define Inputs and Outputs

Clearly state what inputs the code should work with and what kind of output you expect. This helps guide the AI in designing the right structure and logic.

​

GA4 BigQuery Example:

• Input: "I have GA4 event data with nested fields like event_params. I need a query that extracts the user_id and the first page title each user saw."

​

4. Specify the Structure or Constraints

If the code must follow certain patterns, techniques, or coding styles, mention these upfront. Include constraints like performance optimisations or specific functions you want to use.

​

GA4 BigQuery Example:

• Constraint: "I want to optimise this GA4 query to minimise scanned data by using inline unnesting for event parameters."

​

5. Request Error Handling or Edge Case Considerations

Ask for robust code by explicitly stating that the solution should handle common errors, exceptions, or edge cases.

​

GA4 BigQuery Example:

• Request: "Please ensure the query handles cases where the page_title or page_location is missing or null by overwriting null values with 'unknown' ."

​

6. Break Complex Problems into Steps

For more complex problems, break them down into smaller parts. Ask for help with one part at a time, rather than requesting a large solution all at once.

​

GA4 BigQuery Example:

• Step 1: "Can you write a query to unnest GA4 event parameters to get all page_title and page_location fields?"

• Step 2: "Now, can you modify this to extract the domain and path from page_location using regex?"

​

7. Review and Request Refinements

If the initial code doesn't fully meet your needs, review it and ask for specific improvements or adjustments. Be clear about what needs to be changed or optimised.

​

GA4 BigQuery Example:

• Follow-up Request: "This query works, but can you optimise it further by using LIMIT to reduce scanned data?"

​​

8. Ask for Explanations

If you're unsure about how the code works, you can ask for a line-by-line explanation or a summary of the logic. This is useful for learning purposes or understanding the solution better.

​

Example 

• Explain what this code is doing https://github.com/GoogleCloudPlatform/bigquery-utils/blob/master/udfs/community/bignumber_avg.sqlx

 

9. Provide Examples

When possible, provide sample data or outputs. This helps the AI better visualize your use case and provide more accurate code. But remember to be cautious of what you share as it can be used within the memory of AI. Likewise consider sharing schema of tables to help AI build accurate code. Most of all double check - it doesn't always work accurately even with this level of information.

​

GA4 BigQuery Example:

• Sample Data: "Here’s an example of the event_params table structure: xyz. My table schema is the standard GA4 BigQuery export"

​

10. Set Performance Goals and Always check

If you need the code to be efficient or handle large datasets, mention that explicitly. The AI can then optimize the code or suggest faster alternatives.

​

GA4 BigQuery Example (where ChatGPT goes wrong):

• Request: "I'm working with a large GA4 dataset and need to reduce query cost. Can you optimize this query by limiting the date range?"

 

One common mistake ChatGPT may make is using a direct date comparison on the event_date column for optimizing performance, which is incorrect when querying partitioned GA4 export data. Instead, the correct approach is to use the _TABLE_SUFFIX to filter date partitions.

​

​SELECT user_pseudo_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title

FROM `project.dataset.events_*`

WHERE event_date BETWEEN '20231001' AND '20231010'; -- INCORRECT

​

GA4 BigQuery Example (Correcting ChatGPT):

• Request: "I'm working with a large GA4 tables from the GA4 export and need to reduce query cost. How can I optimise processing cost by limiting to the data ranges I require?"

​

11. Use ChatGPT to Write, Edit, and Review Prompts

ChatGPT can assist not only in providing solutions to coding or query problems but also in helping you craft better prompts to improve its output. This can be particularly useful when you're working on complex tasks, like building BigQuery queries, and need precision in your requests (and to reduce your carbon footprint). 

​

If you’re unsure about how to ask for something, ChatGPT can assist in drafting the initial version of the prompt. You can ask it to create a base prompt, and then you can review and refine it.

​

How to Request:

• Explain the goal: Tell ChatGPT what you're trying to achieve, and ask it to write a prompt.

• Be specific about the task: If you’re working with GA4 data in BigQuery, mention that you need the query structured in a certain way (e.g., including UNNEST, filtering on dates, etc.).

 

Example Request:

• Request: "I’m working with GA4 data in BigQuery and need to extract the total number of events for each user over the last 30 days. Can you help me write a good prompt to generate that query?"

​

12. Get ChatGPT to Edit Your Prompts

If you've written a prompt but feel it could be clearer or more precise, ChatGPT can help you refine it. You can ask it to modify your prompt to ensure it includes all necessary details or structure it better for a more accurate output.

How to Request:

• Submit your existing prompt: Provide ChatGPT with the prompt you’ve written.

• Explain what you need to improve: Indicate where you think the prompt could be clearer or what additional details need to be included.

 

Example Request:

• Your initial prompt: "Write a SQL query for GA4 data to get all user events."

• Request for improvement: "Can you help me refine this prompt? I want the query to unnest the event parameters, filter the results to the last 7 days, and group them by user_pseudo_id."

 

Example Edited Output:

• Refined Prompt Generated: "Can you write a BigQuery SQL query to extract all user events from GA4 data, unnesting the event_params field? The query should filter the data to only include the last 7 days and group the results by user_pseudo_id. Include a count of events per user."

​

13: Get ChatGPT to Review Your Prompts

Before you submit your prompt to generate a code or query, you can ask ChatGPT to review it. ChatGPT can identify any missing details, potential ambiguities, or errors in the prompt that might lead to suboptimal or incorrect code being generated.

 

How to Request:

• Submit the prompt for review: Share your final draft prompt and ask ChatGPT to check if it’s clear or if it might produce the desired output.

• Ask for feedback on improvements: Request feedback on whether you need to add more details or structure the prompt differently.

 

Example Request:

• Your final prompt: "Can you write a BigQuery query to find the top 10 pages visited by users, using GA4 data?"

• Request for review: "Can you review this prompt? Does it provide enough detail for a good query?"

 

Revised Prompt Based on Feedback:

• Revised Prompt: "Can you write a BigQuery query to find the top 10 pages visited by users, based on the number of page_view events? The query should use GA4 data, unnest the event_params field to get the page_title and group results by page_title to rank the top 10 pages."

​

There are many ways to craft and utilise ChatGPT and other AI tech, it's key to remember though that chatGPT is a chatbot and it needs clear and concise information to process, it does have a carbon footprint, and can support you in refining how you work, but always check the output.

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​