Chatting with Your Google Analytics 4 Data: Step-by-Step Python Tutorial

Key Takeaways

in today's video we'll be connecting llms to GAO data using Lang chain this allows us to build chat interfaces to marketing data helping marketers generate insights I'm going to do that by assembling chains that interact with Google biery I'm first going to connect to the GAO data using Lang chains B Lotus then I'm going to formulate the functions that fetches the schema information for the gfo data I'll then feed the schemas into a SQL chain needed to extract the data and finally I'm going to formulate the full chain that allows us to chat with the data using basic chain composition I'll share the code and a link to the data below the video let's get started so I'm going to be using the ga4 e-commerce public data set I'll put a link to this data below the video and if you click the data set link you will open up the bory public data sets in your bigy console and here you can find the ga4 obfuscated sample data and if you click on events you can see that each day of data is stored in a separate date petitioned table I also have my own G4 data in here which is data that is streamed from a web property using ga4 the connection from G4 to biery and here I'll have two tables I'll have an events table like the one we just saw and then an events table with intraday data so I'm not going to cover how to set up the ga4 bitrate connection there are plenty of tutorials out there that will cover that it's very easy to do but you will need a service account key to access the data and I'll put a link to a tutorial on how to do that below this video all right so let's build the cop notebook I have my EnV file with the API keys I have my gbq key. Json which is the bit cray service account key and then I'm PIV installing a bunch of libraries python. envs you get the Epi Keys Lang chain Omi and anthropic the connectors to Lang chain SQL Alchemy and Google Cloud biery so the first thing I'm going to do is I'm going to connect to Google biery I'm going to use my service account key gbq key. Json to do that and the Lang chain community library has a bit query loader that I'm going to use in this video and in order to fetch data with the bit cray loader you need to use OS to set the Google application credentials and then I'm going to write a function that allows me to take a query and fetch the data using the big cray loader and return it as documents that can be used in Lang chain chains and the service account key gbq key. Json allows me to fetch any data within my project rabit promotion so I'm just going to Define two variables table one and table two specifying the two tables I want to query and one table is the public G4 data set I showed you and the other one is my own gfo data set and to keep it simple I'm just going to focus on one table and one date I'm not going to query across all the different dates now let me first use the get docks function to fetch some data from the public data set I'm going to count events and group by the events and if I call the get dock function with this query as an argument you can see that I get a nice list of documents that can be fed to a l chain chain and I can of course do the same thing for table one my own dat set all right so far so good this is the functionality that is needed to feed the chain with insights next up I'm going to import biery from Google Cloud I'm going to import service account from Google or to and I do this just because I want to be able to use the big query client in order to be able to run my own queries now if I run the query from before with the Google bitrate client you can see that I can get the results as a pandas data frame and this is nice to have when you're building the chains and you want to troubleshoot now we need the schemas we need the detailed ga4 schema information because we want to feed that to the chain and this part is a little bit painful because you actually need to know how to extract this scheme information using the Google bitre client I've written two functions here that allows me to extract this information one builds the schemas and the other one fetches the schemas the second one uses the first one to fetch the schema information and we will fetch the scheme information for both the tables the events and the events intraday but you only need to write them once for your data set and then you can reuse them for every chain you built and the get schema function is going to need the full data set ID the project and the data set and we need that because we want to feed the LM as much information as possible on the structure of the tables and if I fetch the schemas for full data set ID one the first data set you can see that I get table identifiers and I get a detailed schema description so I get the schema for the daily table and the intraday table and if I fetch the scheme information for the second data set I'm only going to get the daily table as there is no intraday table in the public data set all right so let's set up the language models I'm going to load the environment variables because I need the API keys for open Ai and anthropic and I usually try out both gbt 4 and CLA three Opus to see which one performs better next up I'm going to import string output passer and runnable pass through which is needed to set up the chain and the first prompt I'm going to need is the SQL prompt So based on the G4 bit schema write a SQL query that will answer the user's questions and then we'll inject the schema and the question from the user and this gives me everything I need to set up the first chain the secret response so I'm going to formulate a new function called get schema that uses my fetch schemas function and the full data set ID to fetch the schema information and this will be injected into the prom template using the runnable pass through and then I'm assembling the chain using the runnable pass through the prompt the language model in this case dpt4 and the string output passer that ensures that I get a string in return and now we can test the Chain by asking the llm to list the top five events and here you can see that the chain returns a clean SQL response that can be run using any connector to the database so I'm just going to use the gbq client to run the SQL code and return it as a pandas data frame okay so this is already useful this was the first part of the full chain now we have the text to clean SQL part covered let me just try to run this with the public data set and see what we get get and here we also get the SQL code that can be run using the Google B client now the last part is fairly simple we just need to use the SQL chain and the Google bit crate loader to formulate the full chain so I'm going to formulate a final template and this prompt says that based on the table schema below the question and the SQL query and the SQL response write a natural language response and then we're going to inject schema question query and response and the final chain is going to look like the SQL chain except that we will use the runnable pass through to inject the SQL response and the schema and the output of the SQL query using the get docs function and then the final chain will have three components the runnable pass through the final prompt and the language model and if we run this and ask for the top five events we'll get an answer back in natural language and you can see here that the language model gives us the top five events in the big public data set so the final part of the chain here doesn't add much value in its current form but now you have the full pipeline that allows you to build chat interfaces to G4 data all right that's it for now if you enjoyed this video like And subscribe thanks for watching