Gitlab hero border pattern left svg Gitlab hero border pattern right svg

Periscope Data

On this page


Periscope dbt docs

Accessing Periscope

Everyone at GitLab has View-only access to Periscope. Log in using Okta. If you need elevated access, such as Editor permissions to create your own charts, create an access request. Also see the user roles section

Periscope Resources

Self-Serve Analysis in Periscope

The goal of this section is to empower the reader to build their own Periscope dashboards that answer their questions about GitLab data. The examples given at the end are specific to the Product organization but are generalizable to any other team at GitLab.

Periscope Basics

The first step to building your own Periscope dashboard is checking that you have the correct permissions.

After logging in to Periscope using Okta, you should see a New Chart button in the top right corner. If you don’t see anything, you only have View-only access and you should follow the instructions above to gain Editor access.

Once you can see New Chart, you can start creating your own dashboards! Find Dashboards on the left side nav bar and click the + icon to build one. Make sure to give it a name in order to keep our directory organized.

Once your dashboard is built and named, you can start adding charts by clicking New Chart in the top right. Now you’re ready to start writing queries.

Finding the Right Data Sources

The next step to answering your data question is finding the relevant table(s) to query from. This requires knowing some background about our Snowflake data warehouse and the data sources which feed into it. There are 3 general types of data that we store in Snowflake: External, Internal Frontend, and Internal Backend.

External Data

External data is all of the data generated by third-party software we use at GitLab, but don’t store the production data ourselves. These sources include Salesforce, Zuora, Netsuite, Greenhouse, and BambooHR. We load this data into our data warehouse using APIs.

Internal Backend Data

GitLab.com is a Ruby on Rails app using a Postgres database on the backend. Each time a user on GitLab.com creates a new MR, issue, comment, milestone, etc., they create a new row in the database. The data team has written custom ELT to sync these Postgres tables into our data warehouse where they’re scrubbed for PII and made available to analyze.

For self-managed instances, we try to get weekly anonymized summaries of these backend databases using usage ping.

Internal Frontend Data

Additionally, we’ve enabled a tool called Snowplow to track frontend interactions on gitlab.com. Snowplow has automatic page view tracking as well as form and link-click tracking. Snowplow sends metadata along with every event, including information about the user’s session and browser.

Note: Snowplow is also capable of capturing backend events but at the moment we’re primarily using it for javascript (frontend) tracking.

What’s the difference between frontend and backend data? Backend data is data that’s already being preserved in the application database because it serves some purpose for the application (MRs, issues, pipelines). In contrast, the primary purpose of frontend tracking is analytics.

dbt Documentation

Our dbt Docs site lists all of the tables available for querying in snowflake. Many of these are documented at both the table and column level, making it a great starting point for writing a query.

Examples

Question 1: How many GitLab.com users visit the preferences page every day? We can use internal frontend data to answer this question since we're asking about page views. We can query Snowplow page views with like this:

SELECT
  DATE_TRUNC('day', page_view_start)::DATE AS period,
  COUNT(DISTINCT user_snowplow_domain_id) AS "Count Users"
FROM analytics.snowplow_page_views
WHERE page_url LIKE '%/profile/preferences%'
  AND page_view_start > DATEADD(day, -7, CURRENT_DATE) -- Last 7 days
GROUP BY 1
ORDER BY 1

Running this query in Periscope’s SQL editor will output a table in the chart section below the query. From there, Periscope offers you a variety of options for visualizing your data. A great way to learn about building charts is to watch this 10-minute Data Onboarding video from Periscope.

Question 2: How many GitLab.com users create a merge request every month? We can use internal backend data to answer this question since merge requests are stored in GitLab.com's backend database.

SELECT
  DATE_TRUNC('month', merge_request_created_at)::DATE AS period,
  COUNT(DISTINCT author_id) AS "Count Users"
FROM analytics.gitlab_dotcom_merge_requests_xf
WHERE merge_request_created_at BETWEEN '2019-01-01' AND '2019-06-01'
  AND merge_request_created_at < DATE_TRUNC('month', CURRENT_DATE) -- Don't show current month
GROUP BY 1
ORDER BY 1

This shows that about 110K users create a merge request every month.

Official Badge for a Dashboard

Some dashboards in Periscope will include an Official Badge (similar to Twitter's Verified Checkmark). Periscope Verified Checkmark

That means these analyses have been reviewed by the data team for query accuracy. Dashboards without the verified checkmark are not necessarily inaccurate; they just haven't been reviewed by the data team. Only members of the Data role can add or remove the Official Badge.

Spaces

We have two Periscope spaces:

They connect to the data warehouse with different users- periscope and periscope_sensitive respectively.

Most work is present in the GitLab space, though some extremely sensitive analyses will be limited to GitLab sensitive. Examples of this may include analyses involving contractor and employee compensation and unanonymized interviewing data.

Spaces are organized with tags. Tags should map to function (Product, Marketing, Sales, etc) and subfunction (Create, Secure, Field Marketing, EMEA). Tags should loosely match issue labels (no prioritization). Tags are free. Make it as easy as possible for people to find the information they're looking for. At this time, tags cannot be deleted or renamed.

Pushing Dashboards into Slack Automatically

Many folks will have some cadence on which they want to see dashboards; for example, Product wants an update on opportunities lost of product reasons every week. Where it is best that this info is piped into Slack on a regular cadence, you can take advantage of Slack's native /remind to print the URL. If it does not appear that the dashboard is autorefreshing, please ping a Periscope admin to update the refresh schedule.

User Roles

There are three user roles (Access Levels) in Periscope: admin, SQL, and View Only.

The current status of Periscope licenses can be found in the analytics project.

Updating Users for Periscope

let jq = document.createElement("script");
jq.src = "https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js";
jq.onload = function() {
  //your code here
};
document.body.appendChild(jq);
  • To get list of all users from a list visiable in the DOM, run this in the console, replacing the class name with the actual label.
$('div.list_class_namer_replace_this').map(function(i, el) {
  return $(el).text()}
).toArray()

Administrators

These users have the ability to provision new users, change permissions, and edit database connections. (Typical admin things)

Resource: Onboarding Admins

Editor access

The users have the ability to write SQL queries against the analytics and analytics_staging schemas of the analytics database that underlie charts and dashboards. They can also create or utilize SQL snippets to make this easier. There are a limited number of SQL access licenses, so at this time we aim to limit teams to one per Director-led team. It will be up to the Director to decide on the best candidate on her/his team to have SQL access.

View-only users

These users can consume all existing dashboards. They can change filters on dashboards. Finally, they can take advantage of the Drill Down functionality to dig into dashboards.

Functional Group Users

We have additional roles for further subdividing the Editor role. Certain charts should not be able to be edited by anyone. For example, the Finance KPIs dashboard should only be able to be edited by members of the Data and Finance roles.

Notes for when provisioning users

All users have View-only access privileges via Okta.

To upgrade a user, in the Periscope UI, navigate to the Roles and Policies section. Then add the user to the relevant group (Admin/Editor) and their functional group (e.g. Marketing, Product, etc.).

Users will inherit the highest access from any group they are in. This is why all functions are by default View-only.

Dashboard Creation and Review Workflow

This section details the workflow of how to push a dashboard to "production" in Periscope. Currently, there is no functionality to have a MR-first workflow. This workflow is intended to ensure a high level of quality of all dashboards within the company. A dashboard is ready for production when the visuals, SQL, Python, and UX of the dashboard have been peer reviewed by a member of the Data Team and meet the standards detailed in the handbook.

  1. Create a dashboard with WIP: as the name and add it to the WIP topic
  2. Utilize the documentation of dbt and the warehouse to build your queries and charts
  3. Once the dashboard is ready for review, create an MR in the data team project using the Periscope Dashboard Review template.
  4. Follow the instructions on the template
  5. Assign the template to a member of the data team for review
  6. Once all feedback has been given and applied, the data team member will update the text tile in the upper right corner detailing who created and reviewed the dashboard, when it was last updated, and cross-link relevant issues (See Data Analysis Process for more details)
  7. The data team member reviewer will:
    • Rename the dashboard to remove the WIP: label
    • Remove the dashboard from the WIP topic
    • Add the Approval Badge to the dashboard
    • Merge the MR if they have permissions or assign it to someone with merge rights
      • MR's can also be closed if there are no meaningful changes

Embedding Periscope Charts in the Handbook

The business unit, not the data team, is responsible for embedding these charts in the handbook. Periscope has great embed docs and chat support through the app.

Embedding Dashboards

It is quite easy to embed a whole dashboard in the handbook. To embed a dashboard, you must first make it an Externally Shared Dashboard. Then, you can add ?embed=true to the URL string to make it an embed link. Plug the URL into the following:

<iframe class="dashboard-embed" src="https://app.periscopedata.com/shared/string-of-numbers-here?embed=true" height="700"> </iframe>

We aim to make sure that the dashboard does not require scroll within the handbook, so you will need to adjust the height value appropraitely. There is no way to do that programmatically. Embedding just one chart requires assistance from the data team to get the API Key.

Tips and Tricks

Having a Dashboard that only presents the correct fiscal quarter information

You may want a dashboard that only filters to the current fiscal quarter or the next fiscal quarter. Periscope's off-the-shelf date filters cannot accomodate for custom fiscal years.

In your analysis, add the following: (update the [datevalue] with the date you're looking to have filtered)

LEFT JOIN analytics.date_details on current_date = date_actual
WHERE [datevalue] < last_day_of_fiscal_quarter
AND [datevalue] > first_day_of_fiscal_quarter

Filter out current month in dashboard queries

In most cases, you need to filter out the current month from your query and only report on completed months. The current month is incomplete and showing these numbers can be misleading. Please use the below statement in your dashboard query to filter out current month.

WHERE <month_column> < date_trunc('month', CURRENT_DATE)

Working with Date Range Filters

When you have an aggregated date that you want to use as a filter on a dashboard, you have to use the aggregated period as the date range start and one day less than the end of the aggregation as the date range end value. Your date range start value can be mapped to your date period.

DRS

For the date range end, you need to create an additional column in your query to automatically calculate the end date based on the value selected in your aggregation filter. If we've been using sfdc_opportunity_xf.close_date as the date we care about, here is an example: dateadd(day,-1,dateadd([aggregation],1,[sfdc_opportunity_xf.close_date:aggregation])) as date_period_end Then add the mapping for the date range end.

DRE

Avoid unclear Visualizations

Pie charts are universally seen as a poor method of visualizing data. Read this blog post as a primer on why not to use pie charts.

High-Quality Image Exports

When exporting static charts out of Periscope, use the built-in export functionality instead of taking a screenshot. Exporting produces a higher-quality image with a transparent background. To export an image out of Persicope, select More Options in the top-right corner of any chart and then select Download Image.

Download Image

Periscope Housekeeping

Periscope operates as our Business Intelligence Tool and our Single Source of Truth. As our SSOT, Periscope requires us to maintain a very high level of cleanliness, tidiness, and accuracy.

It also requires that the dashboards created and/or approved by the data team are accurate and informative. It also requires some periodic maintenance from the data team members.

Main Principles:

Auto-archival of unused dashboards

At the moment, for all dashboards, the auto-archival feature is enabled. That means that if a dashboard is not viewed for more than 45 days, it will be automatically archived. An archived dashboard is not deleted and can be unarchived.

Monthly Periscope Cleanup

Entropy is a natural but avoidable state of a Business Intelligence Tool. In order to act against this tendency, the Data Team operates periodic Maintenance Operations in our Periscope space.

Every month, a Data Team member will take care of the maintenance. This could be proactively claimed one week before the end of each month during one of the Data Ops meetings.

The maintenance task has to be completed in the first week of every month. To do so, you have to open a new issue in the data team project and select the Periscope Cleanup Issue templace. This template will give a list of tasks to complete. Once all of them are completed, you can close the issue.