Design background

At present, the platform lacks the management of user source and life cycle, resulting in the inability to accurately grasp the launch effect of external launch activities and platform operation activities. This added function allows us to master the user quality brought by different launch and activities, so as to optimize the launch and activity means.

Design Overview

The user source is tracked by adding UTM (tracking module) parameters after the link. Moreover, by assigning temporary ID s to users in the tourist mode, user behavior can be tracked from the tourist period, so as to better grasp the delivery effect and user quality.

detailed design

UTM parameters

UTM (urchin tracking module) is a set of standard channel tracking tools. In addition to tracking the traffic effects of different channels, it can also be used as a fine operation tool to continuously optimize the delivery and activity effects.

There are 5 parameters commonly used in UTM, as follows:

User lifecycle

The user's life cycle is composed of a series of specific user behaviors. In this design, the defined user behaviors that can be entered into the user's life cycle include: first browsing (first use of the product), login, registration, visiting courses, placing orders, successful purchase and participating in activities (the activity specifically refers to accessing the page with UTM parameters, which may be an advertisement or an activity on the official website). Hereinafter, the user behavior that needs to be entered into the user life cycle is named as the user key behavior.

The formats of the included user's key behaviors are: behavior, behavior object, device, browser and timestamp. For example, if the user orders A course A, the included formats are: order, course A, mobile phone, APP and timestamp.

Temporary ID

When users visit the website as tourists (PC, M station, APP) , if you are a tourist, you need to assign a unique temporary ID to the user, and you need to create a user life cycle record in the background with this temporary ID. after the user registers or logs in, you need to merge the user's key behaviors on the temporary ID into the student ID. however, the temporary ID should not be deleted, so as to continue to count the number of users on the platform in case of logging out More knowledge explanation, wechat search: Advanced notes of codefarmer programming

When creating a temporary ID, you need to write the information of the first browsing at the same time. If it is through the activity link, the behavior will be recorded as "participating in the activity", otherwise it will be recorded as the first browsing. For example, users enter the home page through Baidu search, and the record format is: the first browsing, specific link, PC, Chrome and timestamp.

data statistics

For accounts created by users through activity links, it is necessary to make continuous statistics on the subsequent key behaviors of users, such as registration, purchase, etc., so as to facilitate refined operation according to these data and improve the efficiency of advertising or activities.

A user has participated in many activities, such as activities a, B and C. if the user subsequently registers or purchases courses (any platform courses can be), the registration or purchase data needs to be counted on activity a, because activity a first touches the user and makes him understand the company's products.

Technical implementation ideas

1. When these parameters exist, match whether utm is set If yes, the unique ID is stored in the cookie No: it is not the source of advertising
2. Virtual ID: temporarily use the session_id in php as the virtual ID (for more information, wechat search: Advanced notes of codefarmer programming)
3. When it is found that the user has registered or logged in, all records generated by the virtual id will be bound to the uid so that the unregistered person's life cycle will not be lost. If the user is not registered or logged in, there is no way.

Database design

User life cycle record form

CREATE TABLE `utm` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vid` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Virtual before login id',
  `uid` int(11) NOT NULL DEFAULT '0' COMMENT 'user id',
  `utm_url_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Associated delivery id',
  `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Record link',
  `device` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'equipment',
  `browser` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'browser',
  `course_id` int(11) NOT NULL DEFAULT '0' COMMENT 'curriculum',
  `pay_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Consumption amount',
  `add_time` int(11) NOT NULL DEFAULT '0' COMMENT 'Add time',
  `action` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'behavior',
  PRIMARY KEY (`id`),
  KEY `rds_idx_0` (`uid`,`vid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='User lifecycle record'

Advertising list

CREATE TABLE `utm2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Link name',
  `url` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'url Full address',
  `utm_campaign` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Abbreviated name',
  `utm_source` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'source',
  `utm_medium` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'medium',
  `utm_term` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'key word',
  `utm_content` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'content',
  `add_time` int(11) NOT NULL DEFAULT '0' COMMENT 'Add time',
  `url_index` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'url unique index',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Advertising'