Title: Querying DPL
Author: staff
Published: August 1, 2022
Last modified: June 1, 2026

---

 1. [Data Pipeline](https://docs.parse.ly/data-pipeline/)
 2. Querying DPL

#  Querying DPL

Parse.ly’s [Data Pipeline](https://docs.parse.ly/data-pipeline/) starts with a standard
set of metrics and events that are worth instrumenting about many kinds of content
and audience.

## Event types

Parse.ly’s [tracking integration](https://docs.parse.ly/parsely-integration/) supports
a number of standard event types out of the box, which help for measuring standard
websites that include text and video content.

The type of an event is determined by the value of the `action` field in each event.
A standard JavaScript integration primarily generates `pageview` and `heartbeat`
events initially. This table lists Parse.ly’s standard events and their meanings.

| name | description | 
| `pageview` | View of a URL; JavaScript tracker auto-fires this on load. | 
| `heartbeat` | Indicates page activity; auto-fires every few seconds. | 
| `videostart` | Video asset has started playing. | 
| `vheartbeat` | Video watching activity; auto-fires every few seconds. | 
| `conversion` | Conversion action has taken place. | 
| _custom_ | Aside from the above reserved names, any name may be used for custom events. |

## Metrics

A metric is a count, unique count, or sum of specific or filtered events.

The following metrics are described by SQL queries that compute each metric from
a table containing all the raw data.

### Page views

    ```lang-php
    SELECT COUNT(action) as views
    FROM parsely.rawdata
    WHERE action = 'pageview';
    ```

### Unique visitors

    ```lang-php
    SELECT COUNT(DISTINCT visitor_site_id) as visitors
    FROM parsely.rawdata;
    ```

### Unique sessions

    ```lang-php
    SELECT COUNT(DISTINCT session) as sessions
    FROM (
      SELECT CONCAT(visitor_site_id, '_', session_id) as session
      FROM parsely.rawdata
    );
    ```

### Engaged time

    ```lang-php
    SELECT SUM(engaged_time_inc) as time_in_seconds
    FROM parsely.rawdata
    WHERE action = 'heartbeat';
    ```

### Conversions

    ```lang-php
    SELECT COUNT(action) as conversions
    FROM parsely.rawdata
    WHERE action = 'conversion';
    ```

### Video starts

    ```lang-php
    SELECT COUNT(action) as videostarts
    FROM parsely.rawdata
    WHERE action = 'videostart';
    ```

### Unique video viewers

    ```lang-php
    SELECT COUNT(DISTINCT parsely_visitor_id) as viewers
    FROM parsely.rawdata
    WHERE action = 'videostart';
    ```

### Video watch time

    ```lang-php
    SELECT SUM(engaged_time_inc) as time_in_seconds
    FROM parsely.rawdata
    WHERE action = 'vheartbeat';
    ```

### Other actions

    ```lang-php
    SELECT action, COUNT(action) as num_actions
    FROM parsely.rawdata
    WHERE action != 'pageview'
    GROUP BY 1
    ORDER BY 2 DESC;
    ```

This query counts all event types other than `pageview` and sorts by the ones with
the highest frequency.

## Channels

Channels, found in the `channel` fields in the Data Pipeline, are places where user
interactions occur. Parse.ly-defined channels include:

 * `apln-rta` (Apple News Real Time)
 * `amp` (Accelerated Mobile Pages)
 * `website` (Webpage traffic)
 * `native-ios` (iOS app traffic)
 * `native-android` (Android app traffic)

To filter specific channels, use the following queries:

Include only Apple News real-time data:

    ```lang-php
    SELECT
    <fields>
    FROM parsely_rawdata
    WHERE channel = 'apln-rta';
    ```

Exclude mobile app traffic:

    ```lang-php
    SELECT
    <fields>
    FROM parsely_rawdata
    WHERE channel not in ('native-ios','native-android');
    ```

Apple News real-time events include extra data from Apple News. See [How to query Data Pipeline for Apple News Real-time data](https://docs.parse.ly/how-to-query-data-pipeline-for-apple-news-real-time-data/)
for more details on the extra data available!

## Mobile distributed content

Parse.ly has support for [Google Accelerated Mobile Pages (AMP)](https://docs.parse.ly/google-amp/)
as described elsewhere in the integration docs. Raw events complying with the standard
event types appear normally on these channels. AMP traffic is distinguished by a
special query parameter whereas IA traffic is distinguished with a special referring
URL.

## Native mobile apps

Parse.ly has [mobile SDKs for iOS and Android](https://docs.parse.ly/mobile-sdk/).
At the present time, these SDKs only support the `pageview` event, which should 
be registered against “URLs” in the app that correspond to pages on the site with
web parity. The mobile SDKs do not yet support custom events.

## Off-site social interactions

[Parse.ly Dashboard](https://www.parse.ly/content-analytics-dashboard/) measures
a number of off-site interactions, including organic social sharing on Facebook 
and Pinterest. The Data Pipeline does not yet include these social interactions.

## Traffic investigation

When investigating suspicious traffic, the following process generates a report 
that provides insight into who is accessing a site or a specific page. The following
query returns an aggregation of page views by referrer, user_agent, and IP address.
This aggregation provides a starting point for investigation.

### Traffic investigation query

    ```lang-php
      SELECT
       referrer,
       user_agent,
       visitor_ip,
       count(action) as pageviews
      FROM parsely.rawdata
      WHERE
       action = 'pageview'
       and date(timestamp) = date('desired-date-here')
       and url like '%url-pattern%'
      GROUP BY 1,2,3
      ORDER BY 4 desc;
    ```

Patterns to look for in the results:

 * **Bot traffic is often:** direct traffic (page views with null referrers) and
   from similar user_agents and IP address patterns (ie. 123.45.987.*)
 * **Using reverse IP address lookups** (such as [https://mxtoolbox.com/ReverseLookup.aspx](https://mxtoolbox.com/ReverseLookup.aspx)),
   it is also possible to identify other unknown traffic patterns.

## Get help

 * **For existing Parse.ly customers**, [contact Parse.ly Support](https://www.parse.ly/support/)
   to discuss how events and metrics in raw data map to Parse.ly’s real-time dashboard.
 * **For organizations not yet Parse.ly customers**, [schedule a demo](https://www.parse.ly/getdemo)
   to learn about the advantages of Parse.ly’s standard metrics and events in measuring
   user interactions and how to sync them with custom events.

Last updated: June 01, 2026