We now offer five prebuilt AdWords reports to give you a head start on ad analytics.
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:
ACCOUNT_STATS: Account-level statistics.
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
AD_STATS: Ad-level statistics
We offer a version that can be grouped by ExternalCustomerID called AD_GROUP_STATS
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
AUDIENCE_STATS: Audience-level statistics
CAMPAIGN_STATS: Campaign-level statistics
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
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.
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.
Launch any Fivetran connector instantly.