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 firstname.lastname@example.org.
This queries the top N referrers and the number of sessions they generate.
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.
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.
Modifying this query to fit your data
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.
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.
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.