README
¶
Track Package - BigQuery Analytics
This package provides helpers for tracking visits, events, and marketing touchpoints in BigQuery.
TouchPoints and BigQuery JSON Columns
Overview
The touchpoints table uses BigQuery's native JSON type for the Payload column. This enables powerful SQL queries using dot-notation to access nested fields:
-- Query UTM parameters directly
SELECT
Payload.utm_source,
Payload.utm_campaign,
Payload.utm_medium,
COUNT(*) as hits
FROM `demeterics.touchpoints.touchpoints`
WHERE DATE(Time) = CURRENT_DATE()
GROUP BY 1, 2, 3
-- Filter by nested fields
SELECT * FROM `demeterics.touchpoints.touchpoints`
WHERE Payload.utm_medium = "cpc"
AND Payload.utm_campaign LIKE "%spring%"
Critical Implementation Details
BigQuery JSON Type Requirements
BigQuery's streaming insert API has specific requirements for JSON columns:
| Approach | Works? | Example |
|---|---|---|
| Parsed Go map | YES | "Payload": map[string]interface{}{"utm_source": "google"} |
| JSON string | NO | "Payload": "{\"utm_source\": \"google\"}" |
The touchPointInsertRequest() function handles this automatically by parsing PayloadJSON (a string) into a map[string]interface{} before insertion.
How It Works
-
Application code creates a
TouchPointEventwithPayloadJSONas a JSON string:event := &track.TouchPointEvent{ Category: "landing", Action: "view", PayloadJSON: `{"utm_source": "google", "utm_campaign": "spring2025"}`, } -
touchPointInsertRequest()parses the JSON string into a Go map:var payloadMap map[string]interface{} json.Unmarshal([]byte(tp.PayloadJSON), &payloadMap) // payloadMap = map[string]interface{}{"utm_source": "google", "utm_campaign": "spring2025"} -
BigQuery streaming API receives the map and serializes it as JSON internally.
-
SQL queries can now use dot-notation:
SELECT Payload.utm_source
Table Schema
The touchpoints table is created with:
CREATE TABLE touchpoints (
Time TIMESTAMP, -- Partitioned by day
Category STRING, -- Event category (e.g., 'landing', 'campaign')
Action STRING, -- Event action (e.g., 'view', 'cta_click')
Label STRING, -- Optional event label
Referer STRING, -- HTTP Referer header
Path STRING, -- Request path
Host STRING, -- HTTP host header
RemoteAddr STRING, -- Client IP address
UserAgent STRING, -- User-Agent header
Payload JSON -- Queryable JSON payload
)
PARTITION BY DATE(Time)
Common Payload Fields
The Payload JSON column typically contains:
| Field | Description | Example |
|---|---|---|
utm_source |
Traffic source | "google", "newsletter" |
utm_medium |
Marketing medium | "cpc", "email", "organic" |
utm_campaign |
Campaign name | "spring2025", "black_friday" |
utm_term |
Paid search keyword | "ai analytics" |
utm_content |
Ad content variant | "banner_a", "text_link" |
page_title |
Page title | "Pricing - Demeterics" |
button_text |
CTA button clicked | "Get Started" |
Error Handling
If PayloadJSON is empty or contains invalid JSON:
- An empty map
{}is inserted (row is not rejected) - A warning is logged for debugging
- The other fields (Category, Action, etc.) are still recorded
Recreating the Table
If you need to recreate the table with the correct schema:
# 1. Delete existing table (only if empty or data can be lost)
bq rm -f demeterics:touchpoints.touchpoints
# 2. Create with correct JSON schema
bq mk --table \
--time_partitioning_field=Time \
--time_partitioning_type=DAY \
--description="Marketing touch point events with queryable JSON payload" \
demeterics:touchpoints.touchpoints \
'Time:TIMESTAMP,Category:STRING,Action:STRING,Label:STRING,Referer:STRING,Path:STRING,Host:STRING,RemoteAddr:STRING,UserAgent:STRING,Payload:JSON'
Or let the code auto-create it via createTouchpointsTableInBigQuery().
Testing Queries
After inserting data, verify the JSON column works:
-- Check recent touchpoints with payload fields
SELECT
Time,
Category,
Action,
Payload,
JSON_VALUE(Payload, '$.utm_source') as utm_source_alt, -- Alternative syntax
Payload.utm_source as utm_source_dot -- Dot notation
FROM `demeterics.touchpoints.touchpoints`
ORDER BY Time DESC
LIMIT 10
Version History
- v1.21.0: Fixed BigQuery JSON column handling - Payload is now parsed from JSON string to map before streaming insert. Added comprehensive documentation.
- v1.20.0: Changed Payload to STRING type (incorrect approach).
- v1.19.0: Initial touchpoints implementation with JSON type but string value (broken).
Documentation
¶
Overview ¶
Package track contains analytics helpers.
This file implements AdWords click tracking. The handler stores the query parameters provided by Google Ads in BigQuery before redirecting the visitor to the landing page.
Package track defines helpers used for pixel tracking and BigQuery storage.
Dataset variables hold the BigQuery project and dataset names for visits, events and AdWords tracking. Each variable defaults to a sensible name but can be overridden via the corresponding environment variable, allowing the runtime configuration to differ from the source defaults.
onePixelPNG contains a transparent 1×1 PNG used as the response for tracking requests.
Package track contains analytics helpers for recording visits and events.
Package track provides helpers for analytics collection. This file contains HTTP handlers used by the tracking service. Handlers are provided to create daily BigQuery tables, serve the tracking pixel and record outbound clicks.
Package track contains analytics helpers for recording visits and events. This file defines the structures used to store visit details and robot hits in Datastore and BigQuery.
Index ¶
- func AdWordsTrackingHandler(w http.ResponseWriter, r *http.Request)
- func ClickHandler(w http.ResponseWriter, r *http.Request)
- func ConfigureTouchpoints(projectID, datasetID string)
- func CreateTodayClicksTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
- func CreateTodayEventsTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
- func CreateTodayVisitsTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
- func CreateTomorrowClicksTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
- func CreateTomorrowEventsTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
- func CreateTomorrowVisitsTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
- func StoreClickInBigQuery(c context.Context, click *Click) error
- func StoreEventInBigQuery(c context.Context, v *Visit) error
- func StoreTouchPointInBigQuery(c context.Context, e *TouchPointEvent) error
- func StoreVisitInBigQuery(c context.Context, v *Visit) error
- func TrackEvent(w http.ResponseWriter, r *http.Request, cookie string)
- func TrackEventDetails(w http.ResponseWriter, r *http.Request, cookie, category, action, label string, ...)
- func TrackHandler(w http.ResponseWriter, r *http.Request)
- func TrackRobots(r *http.Request)
- func TrackTouchPoint(r *http.Request, category, action, label string, payload map[string]any)
- func TrackTouchPointWithUser(r *http.Request, category, action, label string, userID int64, email string, ...)
- func TrackVisit(w http.ResponseWriter, r *http.Request, cookie string)
- type Click
- type RobotPage
- type TouchPointEvent
- type Visit
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func AdWordsTrackingHandler ¶
func AdWordsTrackingHandler(w http.ResponseWriter, r *http.Request)
AdWordsTrackingHandler collects detailed AdWords click information from the request's query parameters and stores it in BigQuery. Expected parameters include `url` for the landing page as well as `k` (keyword), `cm` (campaign ID) and other standard Google Ads values. After recording the click, the handler redirects the user to the validated `url` parameter.
func ClickHandler ¶
func ClickHandler(w http.ResponseWriter, r *http.Request)
func ConfigureTouchpoints ¶ added in v1.17.0
func ConfigureTouchpoints(projectID, datasetID string)
ConfigureTouchpoints overrides the default BigQuery project and dataset used for touch point events. Empty values are ignored, allowing callers to update only one of the parameters.
Typical usage at application startup:
track.ConfigureTouchpoints("my-project", "marketing_touchpoints")
func CreateTodayClicksTableInBigQueryHandler ¶
func CreateTodayClicksTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
CreateTodayClicksTableInBigQueryHandler sets up today's clicks table in BigQuery. It is typically called by a cron job or an administrator. Errors while creating the table are sent as HTTP 500 responses.
func CreateTodayEventsTableInBigQueryHandler ¶
func CreateTodayEventsTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
func CreateTodayVisitsTableInBigQueryHandler ¶
func CreateTodayVisitsTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
func CreateTomorrowClicksTableInBigQueryHandler ¶
func CreateTomorrowClicksTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
CreateTomorrowClicksTableInBigQueryHandler creates the BigQuery table for tomorrow's clicks data. It behaves like CreateTodayClicksTableInBigQueryHandler but uses tomorrow's date when calling createClicksTableInBigQuery.
func CreateTomorrowEventsTableInBigQueryHandler ¶
func CreateTomorrowEventsTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
func CreateTomorrowVisitsTableInBigQueryHandler ¶
func CreateTomorrowVisitsTableInBigQueryHandler(w http.ResponseWriter, r *http.Request)
func StoreClickInBigQuery ¶
StoreClickInBigQuery streams an AdWords click record to BigQuery. If the daily table for today does not exist, insertWithTableCreation will create it before retrying the insert. Any error from BigQuery or table creation is returned to the caller.
func StoreEventInBigQuery ¶
StoreEventInBigQuery streams an Event visit to BigQuery. The dataset and table are automatically created if necessary and the insert retried once.
func StoreTouchPointInBigQuery ¶ added in v1.17.0
func StoreTouchPointInBigQuery(c context.Context, e *TouchPointEvent) error
StoreTouchPointInBigQuery streams a TouchPointEvent to BigQuery. The dataset and partitioned table are created on demand if they do not already exist. The table is partitioned by day on the Time field.
func TrackEvent ¶
func TrackEvent(w http.ResponseWriter, r *http.Request, cookie string)
func TrackEventDetails ¶
func TrackEventDetails(w http.ResponseWriter, r *http.Request, cookie, category, action, label string, value float64)
TrackEventDetails records a custom event. The work runs asynchronously in a goroutine so it does not delay the HTTP response. A context derived from the request is passed to App Engine services used inside the goroutine.
func TrackHandler ¶
func TrackHandler(w http.ResponseWriter, r *http.Request)
func TrackRobots ¶
func TrackTouchPoint ¶ added in v1.17.0
TrackTouchPoint records a marketing touch point event for a visitor. It is designed for use from HTTP handlers serving marketing or landing pages and focuses on capturing referral traffic and campaign parameters.
The function runs asynchronously in a goroutine so that BigQuery streaming latency does not impact the HTTP response time. The provided payload map is JSON-encoded and stored in the Payload column together with standard fields such as category, action, label, referer and request path.
For authenticated user tracking, use TrackTouchPointWithUser instead.
func TrackTouchPointWithUser ¶ added in v1.24.0
func TrackTouchPointWithUser(r *http.Request, category, action, label string, userID int64, email string, payload map[string]any)
TrackTouchPointWithUser records a touch point event with authenticated user context. Use this for tracking authenticated user actions in the application.
Parameters:
- r: HTTP request (used to extract headers, path, etc.)
- category: High-level category (e.g., "purchase", "app", "ai_chat", "feedback")
- action: What happened (e.g., "view", "checkout_start", "create", "submit")
- label: Optional label for additional context (e.g., page name, agent ID)
- userID: Datastore user ID (0 for anonymous visitors)
- email: User email (empty for anonymous visitors)
- payload: Additional key-value data to include (e.g., credits, amount, agent_id)
Example:
track.TrackTouchPointWithUser(r, "purchase", "checkout_start", "credits_5000",
session.UserID, session.Email, map[string]any{"credits": 5000, "amount": 50.00})
func TrackVisit ¶
func TrackVisit(w http.ResponseWriter, r *http.Request, cookie string)
Types ¶
type Click ¶
type Click struct {
Time time.Time `json:"time,omitempty"`
RedirectUrl string `json:"redirectUrl,omitempty"`
Query string `json:"query,omitempty"`
Campaignid string `json:"campaignid,omitempty"`
Adgroupid string `json:"adgroupid,omitempty"`
Feeditemid string `json:"feeditemid,omitempty"`
Targetid string `json:"targetid,omitempty"`
Loc_Physical_Ms string `json:"loc_physical_ms,omitempty"`
Loc_Interest_Ms string `json:"loc_interest_ms,omitempty"`
Matchtype string `json:"matchtype,omitempty"`
Network string `json:"network,omitempty"`
Device string `json:"device,omitempty"`
Devicemodel string `json:"devicemodel,omitempty"`
Creative string `json:"creative,omitempty"`
Keyword string `json:"keyword,omitempty"`
Placement string `json:"placement,omitempty"`
Target string `json:"target,omitempty"`
Param1 string `json:"param1,omitempty"`
Param2 string `json:"param2,omitempty"`
Random string `json:"random,omitempty"`
Aceid string `json:"aceid,omitempty"`
Adposition string `json:"adposition,omitempty"`
Ignore string `json:"ignore,omitempty"`
Lpurl string `json:"lpurl,omitempty"`
Cookie string `json:"cookie,omitempty"`
Referer string `json:"referer,omitempty"`
Host string `json:"host,omitempty"`
RemoteAddr string `json:"remoteAddr,omitempty"`
InstanceId string `json:"instanceId,omitempty"`
VersionId string `json:"versionId,omitempty"`
Scheme string `json:"scheme,omitempty"`
Country string `json:"country,omitempty"`
Region string `json:"region,omitempty"`
City string `json:"city,omitempty"`
Lat float64 `json:"lat,omitempty"`
Lon float64 `json:"lon,omitempty"`
AcceptLanguage string `json:"acceptLanguage,omitempty"`
UserAgent string `json:"userAgent,omitempty"`
IsMobile bool `json:"isMobile,omitempty"`
IsBot bool `json:"isBot,omitempty"`
MozillaVersion string `json:"mozillaVersion,omitempty"`
Platform string `json:"platform,omitempty"`
OS string `json:"os,omitempty"`
EngineName string `json:"engineName,omitempty"`
EngineVersion string `json:"engineVersion,omitempty"`
BrowserName string `json:"browserName,omitempty"`
BrowserVersion string `json:"browserVersion,omitempty"`
}
type RobotPage ¶
type RobotPage struct {
// Time records when the robot accessed the page.
Time time.Time `json:"time,omitempty"`
// Name is an optional identifier for the robot event.
Name string `json:"name,omitempty"`
// URL is the full request URL visited by the robot.
URL string `json:"url,omitempty"`
// URI is the request URI visited by the robot.
URI string `json:"uri,omitempty"`
// Host is the host that served the request.
Host string `json:"host,omitempty"`
// RemoteAddr is the robot's IP address.
RemoteAddr string `json:"remoteAddr,omitempty"`
// UserAgent contains the robot's user agent string.
UserAgent string `json:"userAgent,omitempty"`
// Country is the robot's country based on geolocation.
Country string `json:"country,omitempty"`
// Region is the robot's region based on geolocation.
Region string `json:"region,omitempty"`
// City is the robot's city based on geolocation.
City string `json:"city,omitempty"`
// BotName is the name of the bot parsed from the user agent.
BotName string `json:"botName,omitempty"`
// BotVersion is the bot version parsed from the user agent.
BotVersion string `json:"botVersion,omitempty"`
}
type TouchPointEvent ¶ added in v1.17.0
type TouchPointEvent struct {
// Time is when the touch point occurred.
Time time.Time `json:"time,omitempty"`
// Category groups touch points by high-level category (for example "landing" or "campaign").
Category string `json:"category,omitempty"`
// Action describes what happened (for example "view" or "cta_click").
Action string `json:"action,omitempty"`
// Label provides an optional label for the touch point.
Label string `json:"label,omitempty"`
// UserID is the authenticated user's Datastore ID (0 for anonymous visitors).
UserID int64 `json:"userId,omitempty"`
// Email is the authenticated user's email address (empty for anonymous visitors).
Email string `json:"email,omitempty"`
// Referer holds the HTTP Referer header for the request.
Referer string `json:"referer,omitempty"`
// Path is the HTTP request path (for example "/pricing").
Path string `json:"path,omitempty"`
// Host is the HTTP host serving the request.
Host string `json:"host,omitempty"`
// RemoteAddr is the client IP address.
RemoteAddr string `json:"remoteAddr,omitempty"`
// UserAgent captures the full user agent string for the visitor.
UserAgent string `json:"userAgent,omitempty"`
// Country is the visitor country derived from AppEngine geolocation headers.
Country string `json:"country,omitempty"`
// Region is the visitor region derived from AppEngine geolocation headers.
Region string `json:"region,omitempty"`
// City is the visitor city derived from AppEngine geolocation headers.
City string `json:"city,omitempty"`
// PayloadJSON stores a JSON-encoded payload with arbitrary event fields.
PayloadJSON string `json:"payloadJson,omitempty"`
}
TouchPointEvent captures a marketing touch point for web visitors. It records standard event metadata (category, action, label) plus request context and a JSON-encoded payload for event specific fields such as UTM parameters.
type Visit ¶
type Visit struct {
// DatastoreKey holds the datastore entity key when a visit is stored.
DatastoreKey *datastore.Key `json:"datastoreKey" datastore:"-"`
// Cookie uniquely identifies the visitor across requests.
Cookie string `json:"cookie,omitempty"`
// Session tracks consecutive page views within the same visit.
Session string `json:"session,omitempty"`
// URI is the request URI that was visited.
URI string `json:"uri,omitempty"`
// Referer records the Referer header from the request.
Referer string `json:"referer,omitempty"`
// Time is when the visit occurred.
Time time.Time `json:"time,omitempty"`
// Host is the HTTP host serving the request.
Host string `json:"host,omitempty"`
// RemoteAddr is the IP address of the visitor.
RemoteAddr string `json:"remoteAddr,omitempty"`
// InstanceId identifies the App Engine instance serving the visit.
InstanceId string `json:"instanceId,omitempty"`
// VersionId is the App Engine version serving the visit.
VersionId string `json:"versionId,omitempty"`
// Scheme is the HTTP scheme used (http or https).
Scheme string `json:"scheme,omitempty"`
// Country is the visitor country derived from geolocation.
Country string `json:"country,omitempty"`
// Region is the visitor region derived from geolocation.
Region string `json:"region,omitempty"`
// City is the visitor city derived from geolocation.
City string `json:"city,omitempty"`
// Lat is the latitude of the visitor city.
Lat float64 `json:"lat,omitempty"`
// Lon is the longitude of the visitor city.
Lon float64 `json:"lon,omitempty"`
// AcceptLanguage records the visitor's Accept-Language header.
AcceptLanguage string `json:"acceptLanguage,omitempty"`
// UserAgent captures the full user agent string.
UserAgent string `json:"userAgent,omitempty"`
// IsMobile reports whether the user agent is mobile.
IsMobile bool `json:"isMobile,omitempty"`
// IsBot indicates whether the visit came from a bot.
IsBot bool `json:"isBot,omitempty"`
// MozillaVersion is the Mozilla version extracted from the user agent.
MozillaVersion string `json:"mozillaVersion,omitempty"`
// Platform identifies the device platform in the user agent.
Platform string `json:"platform,omitempty"`
// OS is the operating system reported by the user agent.
OS string `json:"os,omitempty"`
// EngineName names the rendering engine used by the browser.
EngineName string `json:"engineName,omitempty"`
// EngineVersion is the version of the rendering engine.
EngineVersion string `json:"engineVersion,omitempty"`
// BrowserName is the browser name parsed from the user agent.
BrowserName string `json:"browserName,omitempty"`
// BrowserVersion is the browser version parsed from the user agent.
BrowserVersion string `json:"browserVersion,omitempty"`
// Category categorizes an analytics event when tracking actions.
Category string `json:"category,omitempty"`
// Action describes the user action that triggered the event.
Action string `json:"action,omitempty"`
// Label provides an optional label for the tracked event.
Label string `json:"label,omitempty"`
// Value stores a numeric value associated with the event.
Value float64 `json:"value,omitempty"`
}
Source Files