Querying DPL

Parse.ly’s 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 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.

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

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

Unique visitors

SELECT COUNT(DISTINCT visitor_site_id) as visitors
FROM parsely.rawdata;

Unique sessions

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

Engaged time

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

Conversions

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

Video starts

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

Unique video viewers

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

Video watch time

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

Other actions

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:

• fbia (Facebook Instant Article)
• 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:

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

Exclude mobile app traffic:

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 for more details on the extra data available!

Mobile distributed content

Parse.ly has support for Google Accelerated Mobile Pages (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. 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 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

  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), it is also possible to identify other unknown traffic patterns.

Get help

• For existing Parse.ly customers, contact 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 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.