I am fortunate to share another guest post<\/strong> by Arben Kqiku<\/a>, Digital Analyst at Assura<\/a>.<\/p>\n Last time, Arben graced this blog with a comprehensive love letter to R<\/a> as an example of the power of this programming language.<\/p>\n This time, he\u2019ll add even more fancy tools to the toolkit to help you build a data pipeline<\/strong> in the Google Cloud Platform to join your Google Ads and Google Analytics 4 data together.<\/p>\n \n \n Subscribe to the Simmer newsletter<\/a> to get the latest news and content from Simo Ahava into your email inbox!\n <\/p>\n <\/span><\/p>\n My goal for this article is twofold; first, I want to show my fellow R users that it is possible and fun to use R in a production environment! R is sometimes seen as a second-class citizen compared to Python, but in my experience, R has consistently proven effective in addressing all my data challenges at work.<\/p>\n Second, I want to show my fellow digital analysts the power of complementing GTM and GA4 with additional tools.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Above you can see an architectural overview of what we\u2019ll build today.<\/p>\n As a digital analyst, I often need to combine data from different sources and display it in a dashboard.<\/p>\n For instance, clients run campaigns on platforms like Google Ads<\/strong> and Meta Ads<\/strong>, and they want to understand the impact of each channel or even individual campaigns.<\/p>\n The challenge is that each channel tends to attribute conversions mostly to itself. This isn\u2019t intentional; it\u2019s just that each channel operates independently and can\u2019t see the contributions of others.<\/p>\n To address this, we usually use conversion data from a third-party source, like Google Analytics<\/strong>, and combine it with other data such as impressions, clicks, and cost from the advertising channels. This helps us calculate the cost per conversion for each channel more accurately.<\/p>\n I know multi-touch attribution<\/em> isn\u2019t perfect because it often doesn\u2019t consider view-through conversions. However, it\u2019s the best method we have for day-to-day operations. While Marketing Mix Modeling<\/strong> is an option, it requires a lot of data over a long period. So, for practical purposes, multi-touch attribution remains our best tool. As the saying goes, The pipeline we\u2019ll build today needs to address the following things:<\/p>\n Accessibility<\/strong>: Make sure we can easily get data from different sources, such as Google Ads, Meta Ads, and GA4.<\/p>\n Data integration<\/strong>: Combine data from different sources accurately.<\/p>\n Storage<\/strong>: Create a data warehouse in Google BigQuery<\/strong> for the joined data and make it accessible to data visualization tools.<\/p>\n Maintenance<\/strong>: Find a way to automate these steps without needing manual intervention. That way stakeholders will have access to almost real-time data.<\/p>\n In my previous company, comtogether<\/a>, Casper Crause<\/a> took care of our data engineering needs, and he often did so with Python.<\/p>\n Recently, I needed to automate multiple data pipelines and I had no idea how to do that in R. I thought, well, might as well learn Python. But then, before giving up, I googled one last time The screenshot below comes from Mage. Mage is a data engineering tool that allows you to build your ETL<\/em> (extract, transform, and load) pipelines. What I love about Mage is that it is easy to use, you can visualize your data pipelines and it supports multiple programming languages: SQL, Python.. and R!<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n I love R and I am so thankful that Tommy Dang<\/a> and his team included it in Mage.<\/p>\n Many tools prefer Python since it is more widely adopted for data engineering tasks. Moreover, in a recent workshop on Andreas Kretz\u2019s YouTube channel<\/a>, Tommy mentioned that they are going to include even more programming languages!<\/p>\n In addition to building our pipeline, we\u2019ll use Mage to schedule it, as you can see in the example below.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n You can run Mage on your local machine or in the cloud.<\/p>\n Obviously, if you run it locally, your computer needs to be on all the time, which is not ideal. Therefore, we\u2019ll create a virtual machine (VM) on the Google Cloud Platform<\/a> and run Mage from there.<\/p>\n A virtual machine (VM) on GCP is like a computer in the cloud. It\u2019s not a physical machine you can touch; instead, it\u2019s a powerful, remote computer that you can use to run your software and store your data.<\/p>\n To access the virtual machine from our computer, we\u2019ll use Visual Studio Code<\/a>, which is a lovely, free<\/strong> code editor that supports many programming languages.<\/p>\n To use GCP, you need a payment method. But worry not, as of today, If you have never used GCP, you get a credit of $300. So, go to the Google Cloud Console and create an account: https:\/\/console.cloud.google.com\/welcome<\/a>.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Once you have used your free credits, you need to add a credit card to your account, by going under \u201cBILLING\u201d:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n In order to use GCP, we need a project. Later, everything that we\u2019ll do will be within this project. So, go back to https:\/\/console.cloud.google.com\/welcome<\/a> and create a new project by first clicking on the project selector in the top left.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Then, click on \u201cNEW PROJECT\u201d.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Next, name your project. I called my project <\/p>\n <\/a><\/p>\n<\/div>\n Then simply wait until your project is created. Once you have selected your project, type \u201cvm instances\u201d in the search bar, and select \u201cVM instances\u201d.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n This will lead to the following screen:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n On GCP, to use specific features, you must activate the corresponding APIs. For example, we\u2019ll enable the Google Analytics API later to get data from GA4. To make a virtual machine, we need to enable the Compute Engine API. Afterward, you\u2019ll see this screen, but we won\u2019t create a VM instance just yet\u2026<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n First, we need to create SSH keys<\/strong> that will allow us to access our virtual machine from our computer.<\/p>\n SSH keys are like special keys that help your computer talk securely to another computer, such as a virtual machine.<\/p>\n It\u2019s a way for your computer to prove it\u2019s really you when connecting to the virtual machine. It\u2019s like having a secret handshake between your computer and the virtual machine, making sure they can trust each other without needing to type in a password every time.<\/p>\n We need to create two SSH keys, a private and a public key. Think of SSH keys like a pair of magic keys for your online accounts. You have one key that you keep secret (the private key) and another key that you share with others (the public key).<\/p>\n When you connect to a server or service, you use your private key to prove you are who you say you are. The server then checks this with your public key to make sure it\u2019s really you. This way, even if someone gets your public key, they can\u2019t do anything without the private key, which stays safe on your computer. It\u2019s a bit like having a special lock and key where only your key can open it.<\/p>\n To create your keys, hop to the terminal in your local machine and type the following code:<\/p>\n The end of the code should be your username, in my case Once you enter the code mentioned above, you\u2019ll be prompted to insert your computer\u2019s password, if you have any. Once you add your password, your SSH keys will be created.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Now, go to the directory where your SSH keys can be found. This is where your public private and public SSH keys are located.<\/p>\n Now, type the following code to display the content of your public SSH key in the terminal.<\/p>\n This will show the content of your public SSH key that we will later paste into our VM.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Now, let\u2019s go back to Google Cloud Platform and click on \u201cCREATE INSTANCE\u201d in the VM instances overview.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Give a name to the VM instance and select the region closest to you:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Go to the \u201cBoot disk\u201d section and click on \u201cCHANGE\u201d:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Select the following options.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Under Firewall, select the following options:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n This is important, as otherwise we won\u2019t be able to access Mage by using the IP address of our VM, you\u2019ll understand later what I mean by this.<\/p>\n Under Advanced Options > Security, click on \u201cADD ITEM\u201d. Here is where we\u2019ll add our public SSH key<\/strong>.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Copy the entire SSH public key and paste it.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Finally, click on \u201cCREATE\u201d. It may take some time to create the VM.<\/p>\n Once done, your new VM will appear here. Also, you\u2019ll see that your VM will have an \u201cExternal IP\u201d.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n You can use this \u201cExternal IP\u201d and your SSH private key to connect to this VM. Let\u2019s do this!<\/p>\n Go back to the terminal in your local machine and go to the directory where the SSH keys are located:<\/p>\n Next, type this command:<\/p>\n I\u2019ll break it down to you so you know what to replace:<\/p>\n You\u2019ll likely will be prompted to enter your password again, and also to add the \u201cExternal IP\u201d as a host. Just follow the instructions and you should be able to connect to your VM.<\/p>\n As you can see from the image below, we connected to the VM named <\/p>\n <\/a><\/p>\n<\/div>\n We could do this whole process through the terminal, but it is much more user-friendly to do it through Visual Studio Code. Visual Studio Code is a very powerful code editor. Go to this link: https:\/\/code.visualstudio.com\/download,<\/a> and download Visual Studio Code.<\/p>\n Once you have installed it, go to \u201cExtensions\u201d and install \u201cRemote – SSH\u201d.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n In Visual Studio Code, go the the search bar and type <\/p>\n <\/a><\/p>\n<\/div>\n In the configuration file that will open, you need to enter your details. Essentially, we\u2019re providing the details to connect to our VM.<\/p>\n Now, we still have to go back to the terminal one last time and type this:<\/p>\n Then, type your password when prompted. This basically means that you can use your password when you try to access the VM through Visual Studio Code.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Now, go back to the search bar of Visual Studio Code, type <\/p>\n <\/a><\/p>\n<\/div>\n Among others, it should suggest the host that you just created, click on that host:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n A new window will open. In the search bar, type your password:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Now, click on the \u201cRemote Explorer\u201d icon, and it should show that you connected to your VM:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n On the top right, click this icon to display the terminal below:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Now click on \u201cTERMINAL\u201d. Congratulations, you have accessed your VM through Visual Studio Code!<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n To install mage on GCP, I largely followed this tutorial<\/a>, but I will also explain every step here. First of all, let\u2019s create a directory in our VM<\/strong> for mage:<\/p>\n Now, if you type the following code, you should be able to see the newly created folder:<\/p>\n Then, let\u2019s access the folder:<\/p>\n Now, to install mage, we need to first install Docker<\/strong>.<\/p>\n Docker is a platform for developing, shipping, and running applications. It uses containerization technology to package an application and its dependencies together into a single unit called a \u201ccontainer\u201d.<\/p>\n In the Let\u2019s access the folder that contains the Docker installation:<\/p>\n Let\u2019s modify the file to make it executable:<\/p>\n Then, let\u2019s run it:<\/p>\n Type this to verify that Docker has been installed correctly:<\/p>\n This should show the following message:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Now, let\u2019s go back to the initial directory:<\/p>\n Now, we can finally install mage with this command:<\/p>\n with the command At the end, Now, to access mage through our External IP from GCP, we need to hop back on GCP first, as we need to create a firewall rule<\/strong>.<\/p>\n This is necessary to control and regulate incoming and outgoing traffic to and from your VM on Google Cloud Platform. When you want to access mage through your External IP from GCP, a firewall rule is needed to explicitly allow the traffic to reach your VM.<\/p>\n Browse to Firewall<\/a> in the Google Cloud Platform.<\/p>\n Click on \u201cCREATE FIREWALL RULE\u201d:<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Select the following options and click on \u201cCREATE\u201d:<\/p>\n
\n X<\/span><\/p>\n
\n The Simmer Newsletter<\/strong>\n <\/p>\nGoal of this article<\/h2>\n
![\"Architecture](\"https:\/\/www.simoahava.com\/images\/2023\/11\/Article-mage-digital-analytics.jpg#ZgotmplZ\")
<\/p>\nWhat am I trying to solve?<\/h2>\n
all models are wrong, but some models are useful<\/code>.<\/p>\nWhat are the requirements of the solution we\u2019ll build?<\/h2>\n
The awesome things you\u2019ll learn<\/h2>\n
\n
Mage.ai and R<\/h3>\n
Data Engineering in R<\/code>, and that\u2019s when I stumbled on Mage.ai<\/a>!<\/p>\n![\"mage-ai-example.jpg\"\/](\"https:\/\/www.simoahava.com\/images\/2023\/11\/mage-ai-example.jpg#ZgotmplZ\")
<\/p>\n![\"Mage](\"https:\/\/www.simoahava.com\/images\/2023\/11\/mage-schedule.jpg#ZgotmplZ\")
<\/p>\nGoogle Cloud Platform (GCP) and Visual Studio Code<\/h3>\n
Walkthrough<\/h2>\n
Configure the Google Cloud Platform<\/h3>\n
Billing and costs<\/h4>\n
![\"Google](\"https:\/\/www.simoahava.com\/images\/2023\/11\/google-cloud-credits.jpg#ZgotmplZ\")
<\/p>\n![\"Google](\"https:\/\/www.simoahava.com\/images\/2023\/11\/google-cloud-billing.jpg#ZgotmplZ\")
<\/p>\nCreate a Google Cloud Project<\/h4>\n
![\"Project](\"https:\/\/www.simoahava.com\/images\/2023\/11\/project-selector.jpg#ZgotmplZ\")
<\/p>\n![\"New](\"https:\/\/www.simoahava.com\/images\/2023\/11\/new-project.jpg#ZgotmplZ\")
<\/p>\nmage-ai-test<\/code>.<\/p>\n![\"New](\"https:\/\/www.simoahava.com\/images\/2023\/11\/new-cloud-project.jpg#ZgotmplZ\")
<\/p>\n![\"VM](\"https:\/\/www.simoahava.com\/images\/2023\/11\/vm-instances.jpg#ZgotmplZ\")
<\/p>\n![\"Compute](\"https:\/\/www.simoahava.com\/images\/2023\/11\/compute-engine-api.jpg#ZgotmplZ\")
<\/p>\n![\"Create](\"https:\/\/www.simoahava.com\/images\/2023\/11\/create-vm-instance.jpg#ZgotmplZ\")
<\/p>\nCreate SSH and public keys<\/h3>\n
\n
ssh-keygen -t rsa -f ~\/.ssh\/mage-ai-test -C arbenkqiku<\/code><\/pre>\n<\/div>\narbenkqiku<\/code>. If you don\u2019t know your user name, type whoami<\/code> in the terminal and press enter. This will output your username.<\/p>\n![\"Create](\"https:\/\/www.simoahava.com\/images\/2023\/11\/create-ssh-key.jpg#ZgotmplZ\")
<\/p>\ncd<\/code> stands for \u201cchange directory\u201d:<\/p>\n![\"Public](\"https:\/\/www.simoahava.com\/images\/2023\/11\/public-key.jpg#ZgotmplZ\")
<\/p>\nCreate a VM instance and connect to it by using our SSH keys<\/h3>\n
![\"Create](\"https:\/\/www.simoahava.com\/images\/2023\/11\/create-new-vm-instance.jpg#ZgotmplZ\")
<\/p>\n![\"Name](\"https:\/\/www.simoahava.com\/images\/2023\/11\/name-and-region-of-vm-instance.jpg#ZgotmplZ\")
<\/p>\n![\"Change](\"https:\/\/www.simoahava.com\/images\/2023\/11\/change-boot-disk.jpg#ZgotmplZ\")
<\/p>\n![\"Advanced](\"https:\/\/www.simoahava.com\/images\/2023\/11\/advanced-boot-disk-options.jpg#ZgotmplZ\")
<\/p>\n![\"Firewall](\"https:\/\/www.simoahava.com\/images\/2023\/11\/firewall-options.jpg#ZgotmplZ\")
<\/p>\n![\"Add](\"https:\/\/www.simoahava.com\/images\/2023\/11\/add-public-key-to-vm.jpg#ZgotmplZ\")
<\/p>\n![\"Paste](\"https:\/\/www.simoahava.com\/images\/2023\/11\/paste-ssh-key.jpg#ZgotmplZ\")
<\/p>\n![\"VM](\"https:\/\/www.simoahava.com\/images\/2023\/11\/vm-external-ip.jpg#ZgotmplZ\")
<\/p>\nssh -i name_of_private_key user_name@gcp_vm_instance_external_ip<\/code><\/pre>\n<\/div>\nmage-demo-test<\/code>. And if you recall, in \u201cBoot disk\u201d options, we selected Ubuntu as our operating system!<\/p>\n![\"Remote](\"https:\/\/www.simoahava.com\/images\/2023\/11\/ubuntu-vm-remote.jpg#ZgotmplZ\")
<\/p>\nInstall Visual Studio Code and access the VM<\/h3>\n
![\"Remote](\"https:\/\/www.simoahava.com\/images\/2023\/11\/remote-ssh-code-extension.jpg#ZgotmplZ\")
<\/p>\n><\/code>, and then select the following option:<\/p>\n![\"Open](\"https:\/\/www.simoahava.com\/images\/2023\/11\/open-ssh-config.jpg#ZgotmplZ\")
<\/p>\nHost mage-demo-test # Give a name to your host<\/span>\n HostName 34.65.231.180 # Replace with the External IP address in GCP<\/span>\n User arbenkqiku # Replace this with your user name<\/span>\n IdentityFile \/Users\/arbenkqiku\/.ssh\/mage-ai-test # Path to private SSH key<\/span><\/code><\/pre>\n<\/div>\neval<\/span> $(<\/span>ssh-agent)<\/span>\nssh-add \/Users\/arbenkqiku\/.ssh\/mage-ai-test # Path to private SSH key<\/span><\/code><\/pre>\n<\/div>\n![\"The](\"https:\/\/www.simoahava.com\/images\/2023\/11\/ssh-add-command.jpg#ZgotmplZ\")
<\/p>\n><\/code> and select the following option:<\/p>\n![\"Connect](\"https:\/\/www.simoahava.com\/images\/2023\/11\/code-connect-to-ssh-host.jpg#ZgotmplZ\")
<\/p>\n![\"Choose](\"https:\/\/www.simoahava.com\/images\/2023\/11\/choose-ssh-host.jpg#ZgotmplZ\")
<\/p>\n![\"Enter](\"https:\/\/www.simoahava.com\/images\/2023\/11\/ssh-passphrase.jpg#ZgotmplZ\")
<\/p>\n![\"Remote](\"https:\/\/www.simoahava.com\/images\/2023\/11\/remote-explorer-vm.jpg#ZgotmplZ\")
<\/p>\n![\"Display](\"https:\/\/www.simoahava.com\/images\/2023\/11\/display-terminal-icon.jpg#ZgotmplZ\"){kind=icon}
<\/p>\n![\"access](\"https:\/\/www.simoahava.com\/images\/2023\/11\/access-terminal-success.jpg#ZgotmplZ\")
<\/p>\nInstall Mage<\/h3>\n
mage-demo<\/code> folder, let\u2019s download a GitHub repo that contains the installation for Docker:<\/p>\ngit clone https:\/\/github.com\/MichaelShoemaker\/DockerComposeInstall.git<\/code><\/pre>\n<\/div>\n![\"Hello](\"https:\/\/www.simoahava.com\/images\/2023\/11\/hello-docker.jpg#ZgotmplZ\")
<\/p>\ndocker run -it -p 6789:6789 -v $(<\/span>pwd<\/span>)<\/span>:\/home\/src --restart always mageai\/mageai \/app\/run_app.sh mage start mage-ai-test<\/code><\/pre>\n<\/div>\n--restart always<\/code>, we\u2019re asking the VM to always restart mage whenever the VM is shut down and later restarted.<\/p>\nmage-ai-test<\/code> represents the name of our project.<\/p>\n![\"Create](\"https:\/\/www.simoahava.com\/images\/2023\/11\/create-firewall-rule.jpg#ZgotmplZ\")
<\/p>\n
![\"Test](\"https:\/\/www.simoahava.com\/images\/2023\/11\/mage-test.jpg#ZgotmplZ\")
<\/p>\n![\"New](\"https:\/\/www.simoahava.com\/images\/2023\/11\/new-batch-pipeline.jpg#ZgotmplZ\")
<\/p>\n![\"Files](\"https:\/\/www.simoahava.com\/images\/2023\/11\/files-in-pipeline.jpg#ZgotmplZ\")
<\/p>\n