advanced_attribution

The advanced attribution table is a “conversion” scope table. It records all conversions made and attributes them to the sessions that participated.

---

Syncing

The advanced attribution table is a view. Its data comes directly from the others tables and depends directly on their synchronization frequency. Thus, new conversions will appear on this table once the session has expired, i.e. after a period of inactivity of 30 minutes after the last hit.

---

Tables and definition 🔗

Source : https://dbdiagram.io/e/67bf2e04263d6cf9a08d27a6/67c08ae8263d6cf9a0b33078

---

Description

Technical Definition of a conversion

From a technical perspective for Quanti, a conversion is a hit where the value of the conversion_id parameter is not null, undefined, or empty. One of these conditions must be satisfied to add a new row in the advanced attribution table.

Primary keys explanations

The advanced attribution table's primary keys are hit_id and s_session_id :

• We have made the decision to insert a field named hit_id into the table, which precisely identifies the hit that registered the conversion from raw_hits. This ensures the table's integrity by addressing a potential issue with the uniqueness of conversion_id. hit_id is technically more precise than the conversion_id.

• The s_session_id is the second primary key of the table and allows linking a session to a conversion. Multiple sessions can participate in the same conversion, and the same session can participate in multiple conversions.

In this table, a single conversion_id can be used across multiple rows because it may have been generated by a series of sessions (One row in the table = one session for a given conversion). Similarly, a session_id can also be used on multiple rows because it might have originated several conversions. However, what is essentially maintained is the presence of a unique session_id per conversion.

Attibution rules

We have 2 ways to catch up on sessions and assess their contribution to achieving a conversion :

• By visitor_id : We log all previous sessions that have the same visitor_id as the session during which the conversion occurred.

• By user_id : We log all previous sessions that have the same user_id as the session during which the conversion occurred.

The values null, undefined, or empty for visitor_id and user_id are obviously excluded from the attribution calculation rules.

The paths

Now that you've understood that the table consists of a list of sessions that contributed to achieving conversion(s), it's crucial to know how you can distinguish them from a chronological perspective and especially how you are going to analyze the paths taken by your visitors and calculate the contribution of each session. There is evidently the "s_datetime" field which gives the exact date and time of the session's start, but we have made the task easier for you by implementing two fields:

• max_index: Indicates the total number of contributive sessions related to a conversion. The value of this field is the same for each session (row) for a given conversion.

• path_index: indicates the chronological position of the session among all the contributory sessions leading to a conversion.

For a conversion tracking multiple contributive sessions, you will therefore have in path_index numbers ranging from 1 to the number representing the total number of sessions. Then, on each line in max_index, you will find the maximum number that represents the total number of sessions.

Example of filling in the path_index and max_index fields

Fields definition

hit_id

Unique hit identifier automatically created by Quanti: which registering the conversion.

s_session_id

Unique session identifier automatically created by Quanti: which linked the conversion.

Learn more about session_id.

s_visitor_id

Unique visitor identifier automatically created by Quanti: which linked the conversion.

Learn more about visitor_id.

c_user_id

Unique user identifier from the client / contact base which linked the conversion.

c_conversion_id

Unique conversion identifier from the client generated by the site and specify in the tag.

c_conversion_type

The type of the conversion generated by the site and specify in the tag.

c_conversion_value

Monetary value of the conversion generated by the site and specify in the tag.

c_date

DATE type field indicating the generation date of the conversion.

c_datetime

DATETIME type field indicating the generation date of the conversion.

s_date

DATE type field indicating the date of the session.

s_source

The s_source field is the value of utm_source parameter of the session arose from raw_sessions table.

Learn more about s_source.

s_medium

The s_medium field is the value of utm_medium parameter of the session arose from raw_sessions table.

Learn more about s_medium.

s_campaign

The s_campaign field is the value of utm_campaign parameter of the session arose from raw_sessions table.

Learn more about s_campaign.

s_content

The s_content field is the value of utm_content parameter of the session arose from raw_sessions table.

Learn more about s_content.

s_keyword

The s_keyword field is the value of utm_term parameter of the session arose from raw_sessions table.

Learn more about s_keyword.

s_utm_id

The s_utm_id field is the value of utm_id parameter of the session arose from raw_sessions table.

Learn more about s_utm_id.

s_consent

The s_consent field is the value of s_consent field of the session arose from raw_sessions table.

Learn more about s_consent.

s_referrer

The s_referrer field is the value of s_referrer field of the session arose from raw_sessions table.

Learn more about s_referrer.

s_ad_user_data

The s_ad_user_data field is the value of s_ad_user_data field of the session arose from raw_sessions table.

Learn more about s_ad_user_data.

s_ad_personalization

The s_ad_personalization field is the value of s_ad_personalization field of the session arose from raw_sessions table.

Learn more about s_ad_personalization.

s_ad_storage

The s_ad_storage field is the value of s_ad_storage field of the session arose from raw_sessions table.

Learn more about s_ad_storage.

s_analytics_storage

The s_analytics_storage field is the value of s_analytics_storage field of the session arose from raw_sessions table.

Learn more about s_analytics_storage.

path_index

Chronological position of the session among all the contributory sessions leading to a conversion. The value "1" represents the session that is the furthest away chronologically from the conversion. The session closest to the conversion (being the one during which the conversion took place) will have the highest number and will be equal to the value of the field max_index defined below.

max_index

The total number of contributive sessions related to the conversion (define by hit_id value). The value of this field is the same for each session (row) for a given conversion.

lookback_window

The total number of days between the conversion date and the session date.

is_last_clic

Boolean field indicating if the session (the row) is the session that led to the conversion. Technically, this is the session for which path_index = max_index.

is_last_clic_non_direct

Boolean field indicating if the session (the row) is the session that led to the conversion after excluding all sessions with a s_source value (direct) and a s_medium value (none). Technically, this is the session for which path_index is the highest (After excluding (Direct) sources). If the conversion is carried out in a single session with s_source value (direct) and a s_medium value (none), then this single session is considered as the LCND. If the conversion is conducted through multiple sessions but all of them have the s_source value (direct) and the s_medium value (none), then the most recent one of these is considered the LCND.

is_post_clic_30

Boolean field indicating if the session (the row) occurred within the 30 days preceding the conversion. Technically, this is the difference between s_date and c_date.

is_post_clic_60

Boolean field indicating if the session (the row) occurred within the 60 days preceding the conversion. Technically, this is the difference between s_date and c_date.

is_post_clic_90

Boolean field indicating if the session (the row) occurred within the 90 days preceding the conversion. Technically, this is the difference between s_date and c_date.

is_post_clic_120

Boolean field indicating if the session (the row) occurred within the 120 days preceding the conversion. Technically, this is the difference between s_date and c_date.

is_post_clic_180

Boolean field indicating if the session (the row) occurred within the 180 days preceding the conversion. Technically, this is the difference between s_date and c_date.

is_first_clic_30

Boolean field indicating if the earliest session of the path is occurred within the 30 days preceding the conversion. Technically, this is the difference between s_date and c_date and it concern only session with a path_index = 1.

is_first_clic_60

Boolean field indicating if the earliest session of the path is occurred within the 60 days preceding the conversion. Technically, this is the difference between s_date and c_date and it concern only session with a path_index = 1.

is_first_clic_90

Boolean field indicating if the earliest session of the path is occurred within the 90 days preceding the conversion. Technically, this is the difference between s_date and c_date and it concern only session with a path_index = 1.

is_first_clic_120

Boolean field indicating if the earliest session of the path is occurred within the 120 days preceding the conversion. Technically, this is the difference between s_date and c_date and it concern only session with a path_index = 1.

is_first_clic_180

Boolean field indicating if the earliest session of the path is occurred within the 180 days preceding the conversion. Technically, this is the difference between s_date and c_date and it concern only session with a path_index = 1.