Article date

Model raw Google Ads Data and build visualisation

Overview:

📊 Outcome: Visualized Google Ads Spend

🕑 Estimated Time: ~20min

🗒 Structure:

  1. Create a model

  2. Add raw data and rename columns 
  3. Change data field type
  4. Normalize data

     

  5. Create an output table and commit your model
  6. Visualize your data

🏷 Model:

Bildschirmfoto_2021-09-15_um_11.57.07.png

Create a model

  1. Click on the Model icon to navigate to the Modeling workspace
  2. Create a new model by clicking on Add...
  3. Select UI Model and give your model a name (e.g. "Peter's first Google Ads model)

🎉🥳 You've created a model! Now you cans tart model data!

Add raw data and rename columns

  1. To start modeling data you would need to first add data to the model. You can do this by clicking on the INPUT node and drag it into the modeling canvas.
  2. Click on the INPUT node and select the table ad_group_ad_report if you are using your own Google Ads data OR select the table "Beginner's Journey Google Ads Data" if you are using the preloaded data.
  3. Add a FIELDS node to drop and rename columns. We will now change the name of the cost column to spend and the day column to date.

Change data field type

  1. If you are looking at the column date, you see that it is formatted as a text/string column. In order to transform this column into a date column we will need to use the DATE PARSER node. Add the DATE PARSER to the canvas and connect the node to the prior added FIELDS node. 
  2. Now set the DATE PARSER node as followed in order to "parse out the date":Bildschirmfoto_2021-08-15_um_18.15.27__1_.png

Normalize "Spend" column and create an output table

  1. Now let's have a look on the spend column. You will notice that it's a quite high value. ℹ️ Google Ads is displaying spend & revenue values in one-millionth USD, therefore we have to divide it by 1,000,000 to normalize it to one USD. In Y42 we can do this with the FUNCTIONS node. As done before with the FIELDS & DATE PARSER node, please also add the FUNCTIONS node to your model.
  2. Now divide the column spend by 1.000.000 just by typing in following function into the editor:

Bildschirmfoto_2022-09-01_um_18.01.04.png

You have done basic steps to normalize your Google Ads data. You can save the model now by adding an OUTPUT node and committing the model. Just drag an OUTPUT node to the canvas, give your output table the name "Daily Ad spend", select all columns you want to have in your table and commit your model afterwards.  

🎉🥳 You've now created your first table with a model! 

 

Visualize your normalized Google Ads data

Now you can start to visualize your data.

  1. Navigate to the Visualisation workspace by clicking on the Visualisation icon and create a new dashboard. 
  2. Add a widget and select the "Daily Ad spend" table as an input
  3. Drag and drop the columns into the fields:
    - Breakdown by: Date of date
    - Summarize by: Sum of spend

Bildschirmfoto_2022-09-01_um_18.27.36.png

Articles in this section

Was this article helpful?
1 out of 1 found this helpful