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.
2. 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
3. AD_STATS: Ad-level statistics
4. We offer a version that can be grouped by ExternalCustomerID called AD_GROUP_STATS
5. 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
6. AUDIENCE_STATS: Audience-level statistics
7. CAMPAIGN_STATS: Campaign-level statistics
8. 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
9. 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.