Activity Data Examples

This is a collection of examples to inspire and guide you when analyzing your CARTO Activity Data, so that you can create your own analysis and KPIs in your tool of choice.

• Example using a BI Tool: beginner-oriented example, where you can start surfacing insights from CARTO easily without coding, using a tool like Looker Studio.

• Examples using SQL: advanced examples, where we show SQL snippets and patterns that highlight how to get the most from your CARTO Activity Data.

Requirements

Example using a BI Tool

We can easily analyze CARTO Activity Data using Business Intelligence (BI) tools such as Looker, Tableau, PowerBI or Thoughtspot. For the following example, we'll use Looker Studio, but the steps should be similar in any other BI tool.

Uploading the data to Looker Studio

Open a new dashboard in Looker Studio and click on "Add Data" > "File Upload". Select your files and wait until they've been uploaded and processed.

Click on "Add to Report" and you should already see a basic placeholder chart with the number of events per day.

Creating a maps created over time chart

To analyze the number of maps created, we need to isolate the events with type = MapCreated. There are several ways to do this in Looker Studio, but an easy way is to create a filter like this:

Once you create the filter, it can be used in any chart, for example a Time Series Chart with the following configuration:

• Dimensions: ts

• Metrics: Record count

And that's it! The time series chart already showcases the amount of maps created per day, for the Activity Data that you exported. You can play with different options to create aggregations by week, day, etc.

Creating a usage quota by user chart

Understanding which user consumes the most usage quota is an important KPI for organizations rolling out CARTO to different teams, and it's quite easy to obtain from the CARTO Activity Data.

First, we need to create a calculated field to understand the final usage quota. This is done by multiplying the amount of requests by their relative usage_quota_weight, as seen in the Activity Data reference.

Once we have that field, we can create a simple horizontal bar chart using the following configuration:

• Dimensions: user

• Metrics: final_usage_quota_consumed

Simple, right? We can tell already who is consuming the most usage quota.

Examples using SQL

In most cases you will be adding the CARTO Activity Data files (CSV or Parquet) to your company data catalog or data lake. Typically these files are stored in SQL-compatible databases and data warehouses, such as Google BigQuery or Snowflake.

The schemas of the CARTO Activity Data tables are very simple and are fully compatible with SQL databases, so let's build a few examples.

SQL: Maps created per user over time

This query returns a very simple table with the amount of maps that each user has created over time. You could join the userid with the User List table to obtain the actual email of each userid.

SELECT 
  date(ts) as date, 
  JSON_EXTRACT_SCALAR(data, '$.userId') as userid,
  COUNT(*) AS created_maps
FROM  `carto-monitoring-gcp.cloud_native_metrics.raw_events` 
WHERE type = 'MapCreated' 
  AND date(ts) = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
GROUP BY date, userid

SQL: Workflow components used by each workflow and user

This query is centered around monitoring the use of Workflow components. It returns a list of the components that were used by each user and workflow execution over time. You could later aggregate this data to find out what components are used most frequently, or which users are using a specific component.

SELECT 
    date,
    userid, 
    ARRAY_AGG(STRUCT(component AS key, workflowId as value) IGNORE NULLS) AS executed_workflowid_by_component
FROM (
    SELECT DISTINCT 
        DATE(ts) as date,
        JSON_VALUE(data,'$.userId') as userid,
        JSON_VALUE(data,'$.workflowId') as workflowId,
        JSON_VALUE(components,'$.name') AS component
    FROM `carto-monitoring-gcp.cloud_native_metrics.raw_events`,
    UNNEST(JSON_EXTRACT_ARRAY(data,'$.components')) AS components
    WHERE DATE(ts) = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
        AND type='WorkflowExecutionComplete'
    )
GROUP BY 1,2