https://linen.dev logo
Join Slack
Powered by
dumb question: I want to add custom dimensions to ...
# announcements
k
dumb question: I want to add custom dimensions to a Google Analytics workflow. Using the Salesforce tutorial as a guide, I need to add a custom extractor, model, and transform. Any other steps to prepare for?
y
Hi @kyle! You don't have to add a custom extractor; Google Analytics is fully supported by Meltano: https://www.meltano.com/docs/plugins.html#google-analytics You just need to provide a custom "reports.josn" to it by using the 
GOOGLE_ANALYTICS_API_REPORTS
env var so that you can define custom reports. You can check [tap-google-analytics documentation](https://gitlab.com/meltano/tap-google-analytics) for more details
You have to then add custom Transforms and Models for the data that you bring by following the [Adding Custom Transformations and Models](https://www.meltano.com/docs/tutorial.html#advanced-adding-custom-transformations-and-models) tutorialo
As an example, I have a custom setup for fetching events from GA through Meltano: Just set the 
GOOGLE_ANALYTICS_API_REPORTS
var to a file that you are going to use: * .env*
Copy code
export FLASK_ENV=development
export SQLITE_DATABASE=meltano

export PG_DATABASE=warehouse
export PG_PASSWORD=YOUR_PASSWORD
export PG_USERNAME=YOUR_USERNAME
export PG_ADDRESS=localhost
export PG_PORT=5432

export PG_SCHEMA='tap_google_analytics'

export GOOGLE_ANALYTICS_API_CLIENT_SECRETS="client_secrets.json"
export GOOGLE_ANALYTICS_API_VIEW_ID="YOUR VIEW ID"
export GOOGLE_ANALYTICS_API_REPORTS="cli_reports.json"
export GOOGLE_ANALYTICS_API_START_DATE="2019-02-01T00:00:00Z"
# export GOOGLE_ANALYTICS_API_END_DATE="2019-06-01T00:00:00Z"
Then define your custom reports there (check the documentation of the tap for info on restrictions, etc): cli_reports.json
Copy code
[   
  { "name" : "events",
    "dimensions" : 
    [
      "ga:date",
      "ga:week",
      "ga:eventCategory",
      "ga:eventAction",
      "ga:eventLabel"
    ],
    "metrics" : 
    [
      "ga:totalEvents",
      "ga:uniqueEvents",
      "ga:sessionsWithEvent",
      "ga:eventsPerSessionWithEvent",
      "ga:users",
      "ga:sessions"
    ]
  }
]
Add a custom transform: /transform/models/my_meltano_project/google_analytics_events.sql
Copy code
with source as (

    select * from {{var('schema')}}.events

),

renamed as (

    select
        -- dimensions
        ga_date as report_date,

        substring(ga_date from 1 for 4) as report_year,
        substring(ga_date from 5 for 2) as report_month,
        ga_week as report_week,
        substring(ga_date from 7 for 2) as report_day,

        ga_event_category as event_category,
        ga_event_action as event_action,
        ga_event_label as event_label,

        -- metrics
        ga_total_events as total_events,
        ga_unique_events as unique_events,
        ga_sessions_with_event as sessions_with_event,
        round(ga_events_per_session_with_event, 2) as events_per_session_with_event,
        ga_sessions as sessions,
        ga_users as users

    from source

)
And finally a custom m5o table and model: model/google_analytics_events.table.m5o
Copy code
{
  version = 1
  sql_table_name = google_analytics_events
  name = google_analytics_events
  columns {
    report_date {
      primary_key = true
      hidden = false
      label = Date
      description = Full Date
      type = string
      sql = "{{table}}.report_date"
    }
    report_year {
      label = Year
      description = Year
      type = string
      sql = "{{table}}.report_year"
    }
    report_month {
      label = Month
      description = Month
      type = string
      sql = "{{table}}.report_month"
    }
    report_week {
      label = Week
      description = Week
      type = string
      sql = "{{table}}.report_week"
    }
    report_day {
      label = Day
      description = Day
      type = string
      sql = "{{table}}.report_day"
    }
    event_category {
      primary_key = true
      hidden = false
      label = Event Category
      description = Event Category
      type = string
      sql = "{{table}}.event_category"
    }
    event_action {
      primary_key = true
      hidden = false
      label = Event Action
      description = Event Action
      type = string
      sql = "{{table}}.event_action"
    }
    event_label {
      primary_key = true
      hidden = false
      label = Event Label
      description = Event Label
      type = string
      sql = "{{table}}.event_label"
    }
  }
  aggregates {
    total_events {
      label = Total Events
      description = Total Events
      type = sum
      sql = "{{table}}.total_events"
    }
    total_unique_events {
      label = Total Unique Events
      description = Total Unique Events
      type = sum
      sql = "{{table}}.unique_events"
    }
    total_sessions_with_event {
      label = Total Sessions with Event
      description = Total Sessions with Event
      type = sum
      sql = "{{table}}.sessions_with_event"
    }
    avg_events_per_session_with_event {
      label = Average Events per Session with Event
      description = Average Events per Session with Event
      type = avg
      sql = "{{table}}.events_per_session_with_event"
    }
    total_sessions {
      label = Total Sessions
      description = Total Sessions
      type = sum
      sql = "{{table}}.sessions"
    }
    total_users {
      label = Total Users
      description = Total Users
      type = sum
      sql = "{{table}}.users"
    }
  }
}
model/google_analytics_cli.topic.m5o
Copy code
{
  version = 1
  name = google_analytics_cli
  connection = postgres_db
  label = Google Analytics - Meltano CLI 
  designs {
    google_analytics_events {
      label = CLI Events
      from = google_analytics_events
      description = Website Overview Stats
    }
  }
}
You'll find everything you need in the links I posted in my first two comments
k
thanks @yannis_roussos! I was able to get your code to run (needed to add 
select * from renamed
to the bottom of the .sql)
I'm trying to add a custom dimension which is populated in our Google analytics by a third party (Clearbit Reveal)
I'm getting errors running the etl with the custom dimension in the report json
Copy code
CRITICAL 'ga:dimension3'
Traceback (most recent call last):
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/bin/tap-google-analytics", line 11, in <module>
    load_entry_point('tap-google-analytics==0.1.0', 'console_scripts', 'tap-google-analytics')()
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/lib/python3.7/site-packages/singer/utils.py", line 225, in wrapped
    return fnc(*args, **kwargs)
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/lib/python3.7/site-packages/tap_google_analytics/__init__.py", line 177, in main
    catalog = discover(args.config)
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/lib/python3.7/site-packages/tap_google_analytics/__init__.py", line 43, in discover
    return reports_helper.generate_catalog()
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/lib/python3.7/site-packages/tap_google_analytics/reports_helper.py", line 85, in generate_catalog
    data_type = self.lookup_data_type('dimension', dimension)
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/lib/python3.7/site-packages/tap_google_analytics/reports_helper.py", line 155, in lookup_data_type
    attr_type = self.valid_dimensions[attribute]
KeyError: 'ga:dimension3'
[MainThread][WARNING][2019-07-29 15:56:48,051] before_invoke has failed: Command /home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/bin/tap-google-analytics ['/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/bin/tap-google-analytics', '--config', '/home/kyle/projects/misc/meltanoexp/carbon/.meltano/run/tap-google-analytics/tap.config.json'] returned 1
[MainThread][WARNING][2019-07-29 15:56:48,051] Could not select stream, catalog file is missing.
INFO URL being requested: GET <https://www.googleapis.com/discovery/v1/apis/analyticsreporting/v4/rest>
INFO URL being requested: GET <https://www.googleapis.com/discovery/v1/apis/analytics/v3/rest>
INFO URL being requested: GET <https://www.googleapis.com/analytics/v3/metadata/ga/columns?alt=json>
INFO Attempting refresh to obtain initial access_token
INFO Refreshing access_token
CRITICAL 'ga:dimension3'
Traceback (most recent call last):
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/bin/tap-google-analytics", line 11, in <module>
    load_entry_point('tap-google-analytics==0.1.0', 'console_scripts', 'tap-google-analytics')()
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/lib/python3.7/site-packages/singer/utils.py", line 225, in wrapped
    return fnc(*args, **kwargs)
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/lib/python3.7/site-packages/tap_google_analytics/__init__.py", line 184, in main
    catalog = discover(args.config)
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/lib/python3.7/site-packages/tap_google_analytics/__init__.py", line 43, in discover
    return reports_helper.generate_catalog()
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/lib/python3.7/site-packages/tap_google_analytics/reports_helper.py", line 85, in generate_catalog
    data_type = self.lookup_data_type('dimension', dimension)
  File "/home/kyle/projects/misc/meltanoexp/carbon/.meltano/extractors/tap-google-analytics/venv/lib/python3.7/site-packages/tap_google_analytics/reports_helper.py", line 155, in lookup_data_type
    attr_type = self.valid_dimensions[attribute]
KeyError: 'ga:dimension3'
[MainThread][ERROR][2019-07-29 15:56:48,913] Subprocesses didn't exit cleanly: tap(1), target(0)
cli_reports.json
Copy code
[   
  { "name" : "events",
    "dimensions" : 
    [
      "ga:dimension3",
      "ga:date",
      "ga:week",
      "ga:eventCategory",
      "ga:eventAction",
      "ga:eventLabel"
    ],
    "metrics" : 
    [
      "ga:totalEvents",
      "ga:uniqueEvents",
      "ga:sessionsWithEvent",
      "ga:eventsPerSessionWithEvent",
      "ga:users",
      "ga:sessions"
    ]
  }
]
All I can find so far is this from the stich forum: https://community.stitchdata.com/t/custom-dimensions-in-google-analytics/100/2
@yannis_roussos I think I found the issue. The 
tap-google-analytics
pings the metadata api to see value dimesions. But the Metadata response only includes the structure for custom dimension ids
from GA Metadata api response
Copy code
{
  "id": "ga:dimensionXX",
  "kind": "analytics#column",
  "attributes": {
    "type": "DIMENSION",
    "dataType": "STRING",
    "group": "Custom Variables or Columns",
    "status": "PUBLIC",
    "uiName": "Custom Dimension XX",
    "description": "The value of the requested custom dimension, where XX refers to the number or index of the custom dimension.",
    "minTemplateIndex": "1",
    "maxTemplateIndex": "20",
    "premiumMinTemplateIndex": "1",
    "premiumMaxTemplateIndex": "200",
    "allowedInSegments": "true",
    "addedInApiVersion": "3"
  }
}
if I put 
ga:dimensionXX
verbatim into my reports.json, I don't get errors 
reports_helper.py
but do get a 400 error from the GA API
I'll file a bug that custom dimensions aren't supported in tap-google-analytics. Unfortunately, I'm not strong enough to suggest PR to fix, but hopefully this helps narrow down
y
Amazing work @kyle! Thank you so much 🙂 You were one step away from submitting your first MR. I just added support for custom metrics also. The problem was fixed with more details provided in the issue submitted above by @kyle
8 Views
Open in Slack
PreviousNext
Powered by