Reach out with questions, bugs, or suggestions. We'll respond ASAP. Your message is sent! We'll respond via email shortly.

Heap SQL: Attribution

This section will go through queries that enable you to understand where your users are coming from.

For a complete list of SQL queries located in the guide look here. If we don’t cover a topic, or you would like help building a custom attribution for your business please reach out to support@heapanalytics.com.

Top Referrers

This queries the top N referrers and the number of sessions they generate.

Mode Analytics

First Touch Properties

This query generates a table that matches Heap’s list view export, select all the user level properties and all the first touch properties.

How it works

This query starts off calculating the first session for each user, and joins the first sessions data with the users table based on the unique user ID. From here each user-level property and session-level property can be selected.

Applications

In addition to generating a list of all users, this query can be modified to contain the user level properties of a subset of users. You can generate a list of users who were first seen during a specific period of time by adding in a WHERE clause with a date range. This table can be joined to other reports to track a particular cohorts experience through your product. You can generate a list of email addresses for a particular email blast targeted towards users who joined based on a certain UTM campaign.

Mode Analytics

Modifying this query to fit your data

Change the SELECT statement to select properties that are valuable to you. Add in a WHERE clause to limit your result to a particular set of users.

Modify the select statement to return the user counts

This query returns the number of users that fall into each category. In this case, the number of users who have come in via each UTM source will be displayed. This can be adapted and broken down by any first touch or user-level properties. For example, this query can be modified to return the number of users who have the initial referrer Facebook and the initial device type as mobile, which in turn can help you make decisions about successful ad campaigns.

Mode Analytics

Multi Touch Attribution

Users come in via many channels, and the campaign that drives the first touch does not always match up with the campaign on the conversion event. Because Heap automatically captures the referrer and UTM parameters for each session, you can build a query that compares the channel for each session a user has. This query compares the UTM source of the first touch with the UTM source on the session with the conversion event, and calculates the number of users who fall into each category.

Mode Analytics

How it works

First, we calculate the UTM source of the first touch by finding the session with the earliest start time, then join the first session with the conversion event based on the user_id . Finally, we select the initial UTM campaign, and the conversion event’s UTM campaign and the breakdown of users. Make sure to include a GROUP BY statement to categorize your users.

Next Topic: Retention