Overview: Tables Schemas

Credentials

Wisdom by default does NOT provide you with usernames and passwords cooresponding with connections to your PostgreSQL database. This is because for security purposes the password is only displayed once. When you are ready to recieve this password, reach out to the Wisdom tean. For various purposes there are a few different user names created.

Full Org Access Connection Details + Credentials:

PropertyValue
host${orgId}.sql.wisdom-dashboards.com
port5432
databaseorg_${orgId}
username
password(Ask)

Project Level Data Access Connection Details + Credentials:

PropertyValue
host${orgId}.sql.wisdom-dashboards.com
port5432
databaseproject_${project_Id}
username
password(Ask)

Various Groups:

Group RolesPurpose
customer_role__read_only_accessCustomer Read Only
customer_role__read_only_rls_on_projectCustomer Read Only - Data limited to specific project
wisdom_role__read_only_accessWisdom Admin role
masterWisdom Admin role

Terminal / Command Line Connections

psql \n\t \\ -h ${ORG_ID}.sql.wisdom-dashboards.com \n\t \\ -d ${DATABASE} \n\t \\ -U ${USER_NAME} \n\t \\ -p 5432

Context

Wisdom collects terrabytes of data. We've found that frequently we cannot build reports fast enough- there is just too much information to explore. We wanted to expose functionality that our own team has been able to use for quite some time- that is direct SQL access. For paid enterprise accounts, you'll be able to batch run SQL queries for your own data across 17 different tables.



Default database seach_path:

show search_path;
> atomic, public, rollup, team, enrichment, trained, normalized_vendor, admin, beta, alpha

Schemas

Note that there is a proposal to make use of a sandbox schema for customer write access. To prevent future conflicts, this schema will not be added to the default search_path;

Show Schemas:

select nspname from pg_catalog.pg_namespace;

List of Schemas

Schemas
admin
alpha
app
atomic
beta
community
community_types
enrichment
normalized_vendor
rollup
sandbox
team
trained

List of Tables

Tables with Schema
atomic.company
atomic.employee
atomic.event
atomic.mail_inbox
atomic.person
atomic.session
enrichment.domain_rank
enrichment.person_enrichment
enrichment.spam_email_service
rollup.rollup_daily_project_metrics
rollup.rollup_weekly_project_metrics
rollup.rollup_weekly_user_page_activity
team.gallery_session_view
team.person_note
team.social_share
team.social_share_comment
team.social_share_like
team.user_sync

Data Syncing SaaS Vendors

Note: Requires setup of vendor secret API keys (auth tokens). Since this functionality is relatively new, you should reach out to your assigned Wisdom account manager to discuss getting started with normalized vendor data.

CategoryVendor NameSQL Schema
Supportintercomvendor_intercom
NPSsatismetervendor_satismeter
Revenuestripevendor_stripe
NPSwootricvendor_wootric
revenuechargebeevendor_chargebee
sales CRMcloseiovendor_closeio
marketinggoogle_analyicsvendor_google_analyics
marketinggoogle_analyics_360vendor_google_analyics_360
sales CRMhubspotvendor_hubspot
ticketsjiravendor_jira
marketingmailchimpvendor_mailchimp
marketingmarketovendor_marketo
analyticsmixpanelvendor_mixpanel
sales CRMoutreachvendor_outreach
dev opspagerdutyvendor_pagerduty
sales CRMpardotvendor_pardot
sales CRMpipedrivevendor_pipedrive
sales CRMsalesforcevendor_salesforce
emailsendgridvendor_sendgrid
NPSuservoicevendor_uservoice
ticketszendeskvendor_zendesk
chatzendesk_chatvendor_zendesk_chat

Tables Reserved for Administrative and for Future Use

SchemaNameNote
adminpg_user_authorization
alphaaggregate_mouse_position
alphaaggregate_scroll_abandonment
alphaaggregate_scroll_position
alphaurl_category
publicknex_migration
publicknex_migration_lock

Function, Extensions, and Listen/Notify

Triggers

On record insert into table person, a trigger will broadcast a notification identified as project_${projectId}. This will help you implement real-time data processing within your application, for example, new user signup notifications.

Functions

NOTE: See Community Reports for how to use and run community report functions.

  • community.report();
  • community.update_reports();

Installed Extensions (\dx)

NameVersionSchemaDescription
aws_commons1.0adminCommon data types across AWS services
aws_s31.0adminAWS S3 extension for importing data from S3
hll2.11publictype for storing hyperloglog data
pg_stat_statements1.6publictrack execution statistics of all SQL statements executed
plpgsql1.0pg_catalogPL/pgSQL procedural language
tablefunc1.0publicfunctions that manipulate whole tables, including crosstab
tsm_system_rows1.0publicTABLESAMPLE method which accepts number of rows as a limit
Last updated on by John Gracey