Affiliation | Tracking Data model

In previous versions, Members were called Ambassadors, that’s why this term is used in the database and for variables. As of version 4.0, Ambassadors are one of several different types of users called Members. Please refer to Glossary

Database tables

• The table POPLISTE_STAT_RECORD records all the click, view and other events occurring in external systems or inside Popsell.

• Other events are also recorded into AMBASSADOR_EMAIL(tracks all the emails sent to the members, whether they are sent by crons or triggered by an event), and into AMBASSADOR_CONNECTION (tracks every time a member logs into the Popsell App).

• Events collected from an external system (e.g. Club Guy Demarle, Selling at Home) are collected in EXTERNAL_EVENTS table

• One record is created for each new event (just as in a log file)

• Event can be triggered outside of Popsell (e.g.: Mailer events) or recorded through an access to a Popsell tracking URL.

PL stands for “Popliste”, which is a publication created by a Member on the Popsell App.

POPLISTE_STAT_RECORD table

SRC_POPSELL (new 3.6.8)

The Popsell source is the container that was used for the event.
Possible values are :

• POPLISTE (0) : the event in a publication shared by email, on a social media, etc. id_popliste must be filled out.

• NEWSLETTER (2) : the event occurred on a newsletter. Id_newsletter must be filled out.

• BLOG (1) : the event occurred on a member’s blog (called profile or e-shop in V4), outside of a publication (for example access on the landing page, click on publications Tab, etc.)

SOURCE

Source is the INITIAL source (media) of the event recorded.
For example, if the PL has been shared on Facebook (Source = F as Facebook).

All the events occurring (clicks on Facebook, then clicks on the member’s profile and then clicks to the e-commerce site) will keep this source information, except when navigating on the e-commerce site (Please refer to Affiliation | Tracking and attribution rules.

Values for SOURCE are made of one letter, usually passed in the URL parameters : s=X
When the user is a member, or when he/she is logged in as a member of the Popsell platform, the the source is overwritten (AMB, ADM, COMMUNITY). This is to differentiate in the dashboards the clicks issued by real visitors from the clicks issued by members or administrators themselves.

Other values deprecated since V4.0 : FACEBOOK_PAGE (E), FACEBOOK_MESSENGER (D), GOOGLE (G), PINTEREST (P), MAIL_FWD (N), INSTAGRAM (R)

The original source (e.g. Facebook) is always kept in the visitor’s browser local storage. If the visitor clicks on Widgets links, it generates “Widgets” events, but never overrides the original source.

This original source can only be overwritten by another original source (e.g. the visitor clicks on another Facebook post, or a WhatsApp link …)

TYPE

Type of events recorded :

1/ Popsell tracked events

• VIEW : A Popsell page has been displayed

• CLICK : The user has clicked on a link inside Popsell (flow, profile, coupon, etc.) which redirects to an external web site

• EMAIL : The user has sent an email (can be a PL or a Coupon). If a PL is sent to several recipients, 1 log is created for each recipient.

• REGISTER : The user has registered as a new member (referral)

2/ Mailer (Mailjet) events

• MAILER_OPEN : The email has been opened on the recipient’s device

• MAILER_CLICK : The recipient has clicked into the email.
  These events should  always be duplicate with the Popsell Click events from an email source.

• MAILER_SPAM : The recipient has declared the email as a spam, or the mail client software has automatically considered it as a spam.

• MAILER_BOUNCE : The email could not be delivered to the recipient. Some of the reasons can be an incorrect email address, or a full mailbox.

• MAILER_UNSUB: The recipient has clicked on an unsubscribe link on his email client software (This is different from an unsubscribe via a link on the footer of the email).

• MAILER_HARD_BOUNCE :

• MAILER_BLOCKED: See other type of events reported by Mailjet.

TARGET

• PL : Specific publication (PL) on the member’s profile

• BLOG : Deprecated V4, replaced by values below

• LANDING : Landing page of a member’s profile

• LISTPL : the page displaying all the publications (PL)

• LISTPRODUCTS : the page displaying the member’s products

• LISTEVENTS : the page displaying the member’s events

• ECOM : clicks targeting the e-Commerce web site

• NL : the target is the newsletter 

• PRODUCT :The target is a product page on the member’s e-shop

• REGISTER: the target is the Popsell Register page (Referal scenarios)

• AKN: the user has clicked on the “acknowledge” button of a Brand post

• SHARE : the user has clicked on the “share” button of a Brand post

DISPLAYED

Deprecated since V4.0

URL

• In case of a VIEW event : the URL is the Popsell targeted page (typically : brand.popsell.com/alias/id-source)

• In case of a CLICK event : the URL is the targeted page, on the Popsell site or on the brand site.

EMAIL_ADDRESS

Email address of the recipient (EMAIL events and MAILER_X events)

SESSION_ID

• In case of Popsell events :

  • V2 to V3 : PHP ID Session, can be used to retrieve unique events in the SQL queries.

  • Since V4 : Unique Session ID generated by Popsell and stored in the browser local storage.

• In case of Mailer events : Mailing list identifier in the Mailjet system, used to identified the campaign (a new mailing list is created for each campaign)

ID_BRAND, ID_AMBASSADOR, ID_POPLISTE

Identifiers of the Brand, the Member and the Publication.

ID_PRODUCT

Filled when the target is a product page in the e-Commerce web site, or when a product page is displayed on a Popsell e-shop.

POPLISTE table

Updates in Version 3.0.9

Now all the data collected in popliste_stat_record are consolidated in popliste table, to enable easy and fast queries.

New data is consolidated every 10 minutes (1^st day of PL sharing), then every hour (next 2 days), then every day until the PL is closed.

Data in popliste table

• “source” stands for the source (mail, linkedin, default, amb, community, etc.)

• “event” stands for the mailjet type of event triggered

• viewed_source : count type ‘VIEW’ grouped by SOURCE

• clicked_source : count type ‘CLICK’ grouped by SOURCE : Deprecated since V4.0

• click_ecom_source : count type ‘CLICK’ and target ‘ECOM’ group by SOURCE

• mailer_event : event of type MAILER_XXX

• ordered_source : data from table “ORDER”

Tracking Diagrams

Events generated during the affiliation tracking process, according to each scenario:

Social Commerce scenario

In that scenario, the customer journey takes place exclusively in the Popsell App (from publications to products and shopping basket)

Mixed Scenario

In the Mixed scenario, publications and community are viewed in the Popsell App, which redirects to product pages in the e-commerce brand site, orders are placed on the e-commerce site.

Embedded Scenario

In the Embedded scenario, the customer journey happens entirely into the e-commerce website (only members have access to the Popsell App)

A URL to a Member’s profile or to a Member’s publication will be displayed into the Profile Widget, inside of an e-commerce page.
The user may share a Popsell URL, e.g. https://brand.popsell.com/user1234 (which will redirect seamlessly) or may copy and paste an e-commerce URL containing the Member’s profile widget, e.g. https://brand.com/ambassadors?user=1234
These two URLs will behave the same way, i.e. it will be considered as a “Default” source.
The e-commerce URL will be considered as a “Default” source only if the referrer URL in not the e-commerce site itself. Otherwise the source will be “Widget”.

Detailed diagram

This diagram is a focus on the components of a member’s e-shop (when in Popsell App) or member’s profile (when inside a Widget):