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

Exporting Activity Data

Before following the exemples, you'll need to export your Activity Data. Learn how to export it with the following resources:

• Exporting Activity Data via CARTO Workspace

• Exporting Activity Data via API

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.

Different tools use different rules for CSV validation. If Looker Studio shows errors with the CSV upload, you can try to fix the errors or contact our Support team for assistance.

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.

Next step: maps created per user over time

You already know from these steps how to analyze Activity Data based on the type of events. But each event contains meaningful information in the payload column (JSON format), such as the user that triggered the event. For a complete reference of event types and payloads, check Activity Data Reference.

While there are ways to analyze the payload column directly in a BI tool we recommend processing the payload column using SQL for further flexibility, as shown in this example.

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.

Understanding who is who

The CARTO Activity Data tracks users with IDs, so that the data can remain anonymous when removing the link to the user. This happens, for example, when a user is deleted.

To translate user IDs into actual user emails you can use the complementary user list table in your Activity Data export.

Additionally, remember that public means that CARTO can't tell who the end user is, such as an unauthenticated visitor in a public map, or a custom application using your own login.

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.

The following examples use the BigQuery SQL dialect, please consider the SQL dialect and functions available in your own database.

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

The first user created 3 maps on July 15th, while the other two only created one map each

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

The first user executed a workflow with 3 components, while the second user executed a workflow with 10 components