Google Ads (AdWords) Connector Now Comes With Prebuilt Reports

By Charles Wang

You have data, but you may not know what to do with it. In response to some of our customers’ questions about the Google Ads connector, we decided to create five prebuilt reports that can plug directly into Looker AdWords blocks. They are:

1. ACCOUNT_STATS: Account-level statistics.
   1. ACCOUNT_HOURLY_STATS makes it easy to sort these statistics by recurring units of time like the hour of the day, the day of the week, the month of the year, or quarter
2. AD_STATS: Ad-level statistics
   1. We offer a version that can be grouped by ExternalCustomerID called AD_GROUP_STATS
   2. AD_GROUP_HOURLY_STATS makes it easy to sort these statistics by recurring units of time like the hour of the day, the day of the week, the month of the year, or quarter
3. AUDIENCE_STATS: Audience-level statistics
4. CAMPAIGN_STATS: Campaign-level statistics
   1. CAMPAIGN_HOURLY_STATS makes it easy to sort these statistics by recurring units of time like the hour of the day, the day of the week, the month of the year, or quarter
5. KEYWORD_STATS: Keyword-level statistics

These prebuilt reports can be generated at setup:

Closer up:

Note that since each connector corresponds to a table, you will need to create a connector for each report.

A Taste of What You Can Do

Suppose you wanted to find the best time of the day to purchase ads. The AD_GROUP_STATS model offers some simple (if naive) ways to see how well different ads perform by different units of time.

I’ve covered how to write Looker Blocks before, and the rest of this post will assume working knowledge of the subject. In development mode, you will write the following view. Your sql_table_name should, of course, reflect the schema and table you’re reading from:

view: ad_group_hourly {
  sql_table_name: adwords.ad_group_hourly;;

  dimension_group: date {
    description: ""
    type: time
    timeframes: [date, week, month, quarter, year]
    sql:  ${TABLE}.date ;;
  }

  dimension: ad_network_type_1 {
    description: "First level network type"
    type: string
    sql:  ${TABLE}.ad_network_type_1 ;;
  }

  dimension: ad_network_type_2 {
    description: "Second level network type"
    type: string
    sql:  ${TABLE}.ad_network_type_2 ;;
  }

  dimension: click_type {
    description: "Click type"
    type: string
    sql:  ${TABLE}.click_type ;;
  }

  dimension: day_of_week {
    description: "Day of the week"
    type: string
    sql:  ${TABLE}.day_of_week ;;
  }

  dimension: day_of_week_order {
    description: "Day of the week order"
    type: string
    sql:  CASE  WHEN ${TABLE}.day_of_week = "Sunday" THEN 0
                WHEN ${TABLE}.day_of_week = "Monday" THEN 1
                WHEN ${TABLE}.day_of_week = "Tuesday" THEN 2
                WHEN ${TABLE}.day_of_week = "Wednesday" THEN 3
                WHEN ${TABLE}.day_of_week = "Thursday" THEN 4
                WHEN ${TABLE}.day_of_week = "Friday" THEN 5
                ELSE 6
                END;;
  }

  dimension: device {
    description: "Device"
    type: string
    sql:  ${TABLE}.device ;;
  }

  dimension: hour_of_day {
    description: "Hour of the day"
    type: number
    sql:  ${TABLE}.hour_of_day ;;
  }

  dimension: month_of_year {
    description: "Month of the year"
    type: string
    sql:  ${TABLE}.month_of_year ;;
  }

  dimension: month_of_year_order {
    description: "Month of the year order"
    type: number
    sql:  CASE  WHEN ${TABLE}.month_of_year = "January" THEN 1
                WHEN ${TABLE}.month_of_year = "February" THEN 2
                WHEN ${TABLE}.month_of_year = "March" THEN 3
                WHEN ${TABLE}.month_of_year = "April" THEN 4
                WHEN ${TABLE}.month_of_year = "May" THEN 5
                WHEN ${TABLE}.month_of_year = "June" THEN 6
                WHEN ${TABLE}.month_of_year = "July" THEN 7
                WHEN ${TABLE}.month_of_year = "August" THEN 8
                WHEN ${TABLE}.month_of_year = "September" THEN 9
                WHEN ${TABLE}.month_of_year = "October" THEN 10
                WHEN ${TABLE}.month_of_year = "November" THEN 11
          ELSE 12
          END

    ;;
  }

  measure: clicks {
    description: "Total Clicks"
    type: sum
    sql: ${TABLE}.clicks ;;
  }

  measure: cost {
    description: "Total Cost"
    type: sum
    sql: ${TABLE}.cost ;;
  }

  measure: conversions {
    description: "Total Conversions"
    type:  sum
    sql: ${TABLE}.conversions ;;
  }

  measure: impressions {
    description: "Total Impressions"
    type:  sum
    sql: ${TABLE}.impressions ;;
  }

  measure: interactions {
    description: "Total Interactions"
    type:  sum
    sql: ${TABLE}.interactions ;;
  }

  measure: engagement {
    description: "Interactions per impression"
    type: number
    sql:  ${interactions} / ${impressions} ;;

  }

  measure: click_through_rate {
    description: "Click through rate"
    type: number
    sql:  ${clicks} / ${impressions} ;;
    
  }

  measure: conversion_rate{
    description: "Conversion rate"
    type: number
    sql:  ${conversions} / ${clicks} ;;
    
  }

  measure: cost_per_click {
    description: "Cost per click"
    type: number
    sql:  ${cost} / ${clicks} ;;
  }

  measure: cost_per_conversion {
    description: "Cost per conversion"
    type: number
    sql:  ${cost} / ${conversions} ;;
  }
  }

Add the following explore to your model:

explore: ad_group_hourly {
  group_label: "Ad Group"
}

Once saved, you can begin constructing visualizations via the Explore menu. Here is the LookML for a chart showing click-through rates and conversion rates by time of day:

- name: click_through_and_conversion_rates
  title: Click Through and Conversion Rates
  model: fivetran
  explore: ad_group_hourly
  type: looker_column
  fields: [ad_group_hourly.hour_of_day, ad_group_hourly.click_through_rate,
    ad_group_hourly.conversion_rate]
  sorts: [ad_group_hourly.hour_of_day]
  limit: 1000
  query_timezone: America/Los_Angeles
  series_types: {}

This is just one of countless ways to use the data from these reports. Happy analyzing!

Learn more about what Fivetran (and Looker) can do for you here.