Subchapter 103.37

Models Customer Analytics

references/models-customer-analytics.mdMarkdown15 KBView on GitHub

Customer analytics accounts and feature requests

Customer analytics tracks accounts, their owners and custom properties, and the feature requests linked to those accounts.

Source of truth for account ownership questions (“who is the CSM of X?”, “which accounts does Y own?”): answer them from these tables, not from warehouse CRM columns (, , …). Warehouse copies of ownership fields can lag behind reassignments made in PostHog.

Models Customer Analytics · Querying PostHog Data · PostHog/skills · Skills Docs

salesforce.*

hubspot.*

Column	Type	Nullable	Description
`id`	UUID	NOT NULL	Account UUID.
`team_id`	Integer	NOT NULL
`external_id`	String	NULL	Identifier of the account in the source system.
`name`	String	NOT NULL	Display name of the account.
`properties`	JSON	NOT NULL	JSON map of account properties; the CRM id columns below are extracted from this.
`stripe_customer_id`	String	NOT NULL
`hubspot_deal_id`	String	NOT NULL
`billing_id`	String	NOT NULL
`sfdc_id`	String	NOT NULL
`zendesk_id`	String	NOT NULL
`created_by_id`	Integer	NULL	User who created the account record.
`created_at`	DateTime	NOT NULL	When the account record was created.
`updated_at`	DateTime	NULL	When the account record was last updated.
`churned_at`	DateTime	NULL	When the account churned; NULL if it has not churned.
`ignored_at`	DateTime	NULL	When Track Rules ignored the account; NULL if tracked.

Column	Type	Nullable	Description
`id`	UUID	NOT NULL	Relationship definition UUID.
`team_id`	Integer	NOT NULL
`name`	String	NOT NULL	Human-readable name of the relationship; unique within the team.
`description`	String	NULL	What this relationship means.
`is_single_holder`	Integer	NOT NULL	1 if only one user can hold this relationship per account at a time, 0 otherwise.
`created_by_id`	Integer	NULL	PostHog user who created the definition.
`created_at`	DateTime	NOT NULL	When the definition was created.
`updated_at`	DateTime	NULL	When the definition was last updated.

Column	Type	Nullable	Description
`id`	UUID	NOT NULL	Relationship assignment UUID.
`team_id`	Integer	NOT NULL
`definition_id`	UUID	NOT NULL	Relationship definition this assignment is for; join to `system.account_relationship_definitions.id`.
`account_id`	UUID	NOT NULL	Account the assignment belongs to; join to `system.accounts.id`.
`user_id`	Integer	NULL	Assigned PostHog user id.
`created_by_id`	Integer	NULL	PostHog user who made the assignment.
`started_at`	DateTime	NOT NULL	When the assignment became effective.
`ended_at`	DateTime	NULL	When the assignment ended; NULL while active.
`created_at`	DateTime	NOT NULL	When the assignment row was created.

Column	Type	Nullable	Description
`id`	UUID	NOT NULL	Feature request UUID.
`team_id`	Integer	NOT NULL
`title`	String	NOT NULL	Customer-facing request title.
`description`	String	NOT NULL	Customer-facing description in Markdown.
`status`	String	NOT NULL	Current lifecycle status: ‘requested’, ‘planned’, ‘completed’, ‘wont_fix’, or ‘duplicate’.
`priority`	String	NULL	Manual priority: ‘high’, ‘medium’, ‘low’, or NULL.
`archived_at`	DateTime	NULL	When the request was archived. NULL while active.
`archived_by_id`	Integer	NULL	PostHog user who archived the request.
`version`	Integer	NOT NULL	Version required for optimistic concurrency on mutations.
`created_by_id`	Integer	NULL	PostHog user who created the request.
`updated_by_id`	Integer	NULL	PostHog user who last updated the request.
`created_at`	DateTime	NOT NULL	When the request was created.
`updated_at`	DateTime	NOT NULL	When the request was last updated.

Column	Type	Nullable	Description
`id`	UUID	NOT NULL	Feature request account link UUID.
`team_id`	Integer	NOT NULL
`feature_request_id`	UUID	NOT NULL	Feature request this link belongs to. Join to `system.feature_requests.id`.
`account_id`	UUID	NOT NULL	Affected account. Join to `system.accounts.id`.
`created_at`	DateTime	NOT NULL	When the account was first linked.
`updated_at`	DateTime	NULL	When the account link was last changed.

Column	Type	Nullable	Description
`id`	UUID	NOT NULL	Evidence UUID.
`team_id`	Integer	NOT NULL
`account_link_id`	UUID	NOT NULL	Request and account pair this evidence supports. Join to `system.feature_request_account_links.id`.
`summary`	String	NOT NULL	Internal summary of the request evidence.
`customer_quote`	String	NOT NULL	Customer quote kept with this evidence item.
`source`	String	NOT NULL	Free-form name of the evidence source.
`source_url`	String	NOT NULL	HTTP or HTTPS link to the source, or an empty string.
`requested_on`	Date	NULL	Date the account made the request, or NULL when unknown.
`image_ids`	Array	NOT NULL	Uploaded image UUIDs attached to this evidence item, in display order.
`created_by_id`	Integer	NULL	PostHog user who added the evidence.
`updated_by_id`	Integer	NULL	PostHog user who last updated the evidence.
`created_at`	DateTime	NOT NULL	When the evidence was added.
`updated_at`	DateTime	NOT NULL	When the evidence was last updated.

Column	Type	Nullable	Description
`id`	UUID	NOT NULL	Product area UUID.
`team_id`	Integer	NOT NULL
`name`	String	NOT NULL	Team-maintained product area name.
`display_order`	Integer	NOT NULL	Position in product area selectors. Lower values appear first.
`is_active`	Integer	NOT NULL	1 if editors can select this area for new requests, 0 otherwise.
`created_by_id`	Integer	NULL	PostHog user who created the product area.
`updated_by_id`	Integer	NULL	PostHog user who last updated the product area.
`created_at`	DateTime	NOT NULL	When the product area was created.
`updated_at`	DateTime	NOT NULL	When the product area was last updated.

Column	Type	Nullable	Description
`id`	UUID	NOT NULL	Feature request product area link UUID.
`team_id`	Integer	NOT NULL
`feature_request_id`	UUID	NOT NULL	Feature request. Join to `system.feature_requests.id`.
`product_area_id`	UUID	NOT NULL	Product area. Join to `system.feature_request_product_areas.id`.
`created_at`	DateTime	NOT NULL	When the product area was linked.

Column	Type	Nullable	Description
`id`	UUID	NOT NULL	Feature request history entry UUID.
`team_id`	Integer	NOT NULL
`feature_request_id`	UUID	NOT NULL	Feature request that changed. Join to `system.feature_requests.id`.
`changed_fields`	Array	NOT NULL	Names of the fields changed in this save. Before and after values are not exposed.
`is_initial`	Integer	NOT NULL	1 if this entry records the request’s initial values, 0 otherwise.
`source`	String	NOT NULL	System that recorded the change.
`actor_id`	Integer	NULL	PostHog user who changed the request.
`changed_at`	DateTime	NOT NULL	When the request changed.

SELECT r.id, r.title, r.status, r.priority, a.id AS account_id, a.name AS account_name
FROM system.feature_requests r
JOIN system.feature_request_account_links l ON l.feature_request_id = r.id
JOIN system.accounts a ON a.id = l.account_id
WHERE r.archived_at IS NULL
ORDER BY r.updated_at DESC

SELECT r.title, a.name AS account_name, count(e.id) AS evidence_count
FROM system.feature_requests r
JOIN system.feature_request_account_links l ON l.feature_request_id = r.id
JOIN system.accounts a ON a.id = l.account_id
LEFT JOIN system.feature_request_evidence e ON e.account_link_id = l.id
GROUP BY r.id, r.title, a.id, a.name
ORDER BY evidence_count DESC

SELECT r.id, r.title, r.status, p.name AS product_area
FROM system.feature_requests r
JOIN system.feature_request_product_area_links l ON l.feature_request_id = r.id
JOIN system.feature_request_product_areas p ON p.id = l.product_area_id
WHERE p.name ILIKE '%analytics%'
ORDER BY r.updated_at DESC

Column	Type	Nullable	Description
`id`	UUID	NOT NULL	Custom property definition UUID.
`team_id`	Integer	NOT NULL
`name`	String	NOT NULL	Human-readable name of the custom property; unique within the team.
`description`	String	NULL	Optional description of what the property represents.
`display_type`	String	NOT NULL	How the property is interpreted and rendered: ‘text’, ‘number’, ‘currency’, ‘percent’, ‘date’, ‘datetime’, ‘boolean’, ‘select’ (allowed options stored on the definition), or ‘link’.
`is_big_number`	Integer	NOT NULL	1 if large numeric values are abbreviated (e.g. 10,000 -> 10K), 0 otherwise.
`created_by_id`	Integer	NULL	User who created the definition.
`created_at`	DateTime	NOT NULL	When the definition was created.
`updated_at`	DateTime	NULL	When the definition was last updated.

SELECT a.name, d.name AS relationship, r.user_id, r.started_at
FROM system.account_relationships r
JOIN system.account_relationship_definitions d ON d.id = r.definition_id
JOIN system.accounts a ON a.id = r.account_id
WHERE a.name ILIKE '%acme%'
  AND d.name = 'CSM'
  AND r.ended_at IS NULL

SELECT a.name, d.name AS relationship
FROM system.account_relationships r
JOIN system.account_relationship_definitions d ON d.id = r.definition_id
JOIN system.accounts a ON a.id = r.account_id
WHERE r.user_id = 12345 AND r.ended_at IS NULL
ORDER BY a.name

SELECT d.name AS relationship, r.user_id, r.started_at, r.ended_at
FROM system.account_relationships r
JOIN system.account_relationship_definitions d ON d.id = r.definition_id
WHERE r.account_id = '0192f000-0000-7000-8000-000000000000'
ORDER BY r.started_at DESC

Models Customer Analytics

Customer analytics accounts and feature requests

Models Customer Analytics

Customer analytics accounts and feature requests

Account (`system.accounts`)

Columns

Account relationships (`system.account_relationship_definitions`, `system.account_relationships`)

`system.account_relationship_definitions` columns

`system.account_relationships` columns

Important notes

Feature requests

`system.feature_requests` columns

`system.feature_request_account_links` columns

`system.feature_request_evidence` columns

Product area tables

`system.feature_request_product_areas` columns

`system.feature_request_product_area_links` columns

`system.feature_request_history` columns

Feature request query patterns

Custom properties (`system.custom_property_definitions`)

Columns

Important notes

Reading per-account values (`system.accounts.custom_properties`)

Common query patterns