Query CRM Analytics Data with the Query API

Execute Analytics Queries Programmatically

You can use a REST API to query analytics datasets using either SAQL or SQL query statements. The query endpoint is wave/query and takes a POST request body using a SAQL Query Input to execute the query.

Example SAQL Query Request

For SAQL queries executed using REST API, you must have both the dataset ID and the dataset version ID to specify the dataset you want to query. Using the name of the dataset in the query returns an error. The only required attribute for the request is query.

1{
2    "query" : "q = load \"<datasetId>/<datasetVersionId>\"; q = group q by 'Status'; q = foreach q generate 'Status' as 'Status', count() as 'count'; q = limit q 20;",
3    "queryLanguage" : "SAQL"
4}

Example SAQL Query Response

The response results include metadata and records. It also returns the original query and the response time. If there are warnings or errors, warnings is included. For the SAQL query executed in the previous example, here’s the response:

1{
2  "action" : "query",
3  "responseId" : "4l-kl6BTnH4ay9-qbx2Re-",
4  "results" : {
5    "metadata" : [ {
6      "lineage" : {
7        "type" : "foreach",
8        "projections" : [ {
9          "field" : {
10            "id" : "q.Status",
11            "type" : "string"
12          },
13          "inputs" : [ {
14            "id" : "q.Status"
15          } ]
16        }, {
17          "field" : {
18            "id" : "q.count",
19            "type" : "numeric"
20          }
21        } ],
22        "input" : {
23          "type" : "group",
24          "groups" : [ {
25            "id" : "q.Status"
26          } ]
27        }
28      },
29      "queryLanguage" : "SAQL"
30    } ],
31    "records" : [ {
32      "Status" : "Closed",
33      "count" : 7
34    }, {
35      "Status" : "Open",
36      "count" : 6
37    } ]
38  },
39  "query" : "q = load \"<datasetId>/<datasetVersionId>\"; q = group q by 'Status'; q = foreach q generate 'Status' as 'Status', count() as 'count'; q = limit q 20;",
40  "responseTime" : 3,
41  "warnings" : [
42    {
43      "code" : "001",
44      "message" : "Limit exceeded"
45    },
46    {
47      "code" : "002",
48      "message" : "Another warning..."
49    }
50  ]
51}

Example SQL Request Body

The default language for query execution is SAQL. To use SQL, include the attribute "queryLanguage" : "SQL" in your request. For SQL queries, you can use the dataset name. Here’s the same query request in SQL.

1{
2  "query" : "SELECT Status, COUNT(*) as StatusCount FROM \"<name>\" GROUP BY Status LIMIT 20;", 
3  "queryLanguage": "SQL"
4}

If you forget to specify "queryLanguage" : "SQL", the request returns a syntax error.

Note

Example SQL Query Response

The response records look the same as the SAQL response, but the metadata is different. The columns key includes the name and type of projections in the query.

1{ 
2  "action" : "query", 
3  "responseId" : "4l-mtW26NZ4OYu-qbx3GN-", 
4  "results" : { 
5    "metadata" : [ 
6      { 
7        "columns" : [ 
8          { 
9            "columnLabel" : "Status", 
10            "columnType" : "varchar" 
11          }, 
12          { 
13            "columnLabel" : "StatusCount", 
14            "columnType" : "numeric" 
15          } 
16        ], 
17        "queryLanguage" : "SQL" 
18      } 
19    ], 
20    "records" : [ 
21      { 
22        "Status" : "Closed", 
23        "StatusCount" : 7 
24      }, 
25      { 
26        "Status" : "Open", 
27        "StatusCount" : 6 
28      } 
29    ] 
30  }, 
31  "query" : "SELECT Status, COUNT(*) as StatusCount FROM \"Cases1\" GROUP BY Status LIMIT 20;", 
32  "responseTime" : 9 
33}

Example SQL Metadata: Group By Query With an Aggregation

The query projects the aggregation avg(Sales) as AvgSales, which returns numerical data. In the metadata, the corresponding column type is returned as numeric.

1"metadata": [
2  "columns": [
3    {
4      "columnLabel": "City",
5      "columnType": "varchar"
6    },
7    {
8      "columnLabel": "AvgSales",
9      "columnType": "numeric"
10    }
11  ],
12  "queryLanguage": "SQL"
13]

Example SQL Query and Response Body: Extract Date Parts from a Date Field

This example returns the year, month, and day from the CloseDate field as numerical values. The request features the timezone attribute, which is attribute is optional and can only be used if timezone is enabled for the org.

1{
2  "query" : "SELECT EXTRACT(YEAR FROM CloseDate) as year, EXTRACT(MONTH FROM CloseDate) as month, EXTRACT(DAY FROM CloseDate) as day From "OpportunityFiscalEM"
3  "queryLanguage" : "SQL",
4  "timezone" : "America/Los_Angeles"
5}

1"metadata": [
2  "columns": [
3    {
4      "columnLabel": "year",
5      "columnType": "numeric"
6    },
7    {
8      "columnLabel": "month",
9      "columnType": "numeric"
10    },
11    {
12      "columnLabel": "day",
13      "columnType": "numeric"
14    }
15  ],
16  "timezone": "America/Los_Angeles",
17  "queryLanguage": "SQL"
18]

Example SQL Query and Response Body: Project Date Field of Type DateTime

This query returns date information as timestamps.

1{
2  "query" :  "SELECT CloseDate From "OpportunityFiscalEM",
3  "queryLanguage" : "SQL"
4}

1"metadata": [
2  "columns": [
3    {
4      "columnLabel": "CloseDate",
5      "columnType": "timestamp"
6    }
7  ],
8  "queryLanguage": "SQL"
9]

Query Metadata Details

Clients can parse the queries to figure out what dimensions and groups are used, but this can be expensive. So, in most cases, the query response contains a metadata section, which provides grouping and column information. The metadata section, if present, is found in the results key in the query response payload. The metadata section is structured with columns and groups keys:

1"metadata":{
2  "columns" : [
3    {
4      "name" : "dim name",
5      "type" : "String"
6    }
7  ],
8  "groups" : [
9    "name",
10    "destination"
11  ]
12}

The columns key includes the name and type of the projections of the query, and the groups key contains a list of groups used in the query.

• The metadata is added when the query is successful. If the query fails to run, if there’s a syntax error, or if the authorization callback fails then the metadata isn’t added to the results.
• The value set in a column name is the alias given to the projection and not the name of the dimension.
• A list of the groups used in the query is returned in the groups key, provided the query isn’t considered complex—where the group name returned is nondeterministic (the name of the group is used in multiple streams of the query). This is the case when the query uses cogroup or union. In such cases, the groups key is empty.

Note