How do I use PolarDB for AI operators to create an ID-mapping solution - PolarDB

This topic describes how to use PolarDB for AI operators to create an ID-mapping solution.

Background information

ID-mapping aggregates information such as account IDs and device IDs, associates the aggregated information to a user, and allocates an ID to the user as the unique ID.

In gaming scenarios, a player may have multiple accounts on the platform of a gaming company and may use the accounts on other platforms that host games of this company. If these accounts are not associated with each other, the company cannot perform precision marketing for this player. For example, if the accounts of a player in two games are not associated, an advertisement that has been given a thumb-down by the player in Game A may show again when the player is playing Game B. This is a waste of advertising resources. To address this issue, ID-mapping is used to process user attribute data, which can improve precision marketing or custom recommendation.

Design an ID-mapping solution

IDs in the gaming industry fall into two categories:

Account IDs: accounts of a gaming platform, the accounts of a game, phone numbers, and email addresses.
Device IDs: device IMEI, device IMSI, device OAID, and Android ID.

Create a basic information table.

ID-Mapping uses two existing types of information tables:

User attribute information, such as user ID, phone number, email address, and ID card number.

User behavior information, such as in-game logon, purchases, and communication with other players.

User attribute information table: The user_info table lists user attribute information.

Column	Description
plat_user_id	The user ID. Example: 0b887f9e1e915XXXX.
phone_number	The phone number of the user.
email	The email address of the user. Example: xxx@alibaba-inc.com.
device_id	The ID of the main device of the user. Example: 0b887fXXXX915e355.

User behavior information table: The game_platform_info table records logon information to the game platform.

Column	Description
device_id	The device ID. Example: 0b887f9e1e915XXXX.
android_id	The Android ID of the device. Example: 02bd2a91190c1a6406866302942f4XXXX.
oaid	The OAID of the device. Example: 106ed9425b12664364a06c51aXXXX.
ip	The IP address. Example: 182.XX.XX.146.
ts	The timestamp. Example: 1647137327.
user_id	The user ID. Example: 447268XXXX.

User behavior information table: The game_info table records information of the account that is used for the logon.

Column	Description
device_id	The device ID. Example: 0b887f9e1e915XXXX.
user_id	The ID of the game account. Example: 02bd2a91190c1a606866302942XXXX.
ipv4	The IPv4 address. Example: 182.XX.XX.146.
ipv6	The IPv6 address. Example: 240e:337:1026:XXXX:XXXX:a26d:4a34:920d.
imei	The International Mobile Equipment Identity (IMEI). Example: 45ca53a497897c0b39f4373b0c14XXXX.
idfa	The IDFA for iOS. Example: 8301c9fcgg6a16fd3XXXX0e30e1854a78a.
googleid	The Google advertising ID. Example: f09adce39gr564c7f178bd9ebf64b9XXXX.
android_id	The Android ID of the device. Example: a9d26756755rtr7b8faf6b2e0b6d414XXXX. Note This ID changes after the device is flashed.
oaid	The OAID of the device. Example: 4aad25ea19rtr769fe84cc46244b2aeXXXX.
timestamp	The timestamp. Example: 1647137327.

Create an ID association.
Associate the account information (user_info.plat_user_id, game_platform_info.user_id, and game_info.user_id) with the device information (device_id, android_id, ip, imei, idfa, googleid, and oaid) in the tables to generate a unified ID.
Note
Take note of the columns that contain the same information but whose names are different in different tables. For example, the plat_user_id column in the user_info table and the user_id column in the game_platform_info table contain the same information. You can add SELECT plat_user_id as user_id in the CREATE FEATURE statement to make sure the data in the columns remains consistent. What's more, take note of the columns that contain different information but whose name is the same in different tables. For example, the user_id column in the game_platform_info table contains different information from the user_id column in the game_info table. You can add SELECT ... user_id as game_user_id... to make sure that the data in the columns is separated.
1. Execute the following statement to create a feature named oneID:
```
/*polar4ai*/CREATE FEATURE oneID WITH ( feature_class = 'graph',parameters=()) AS (SELECT plat_user_id as user_id, device_id FROM user_info)
```
2. Execute the following statement to check the status of the operation:
```
/*polar4ai*/SHOW FEATURE oneID;
```
  Note
  - If the operation has been completed, FINISHED is returned.
  - If the operation has not been completed, Please wait for the feature calculation to finish is returned for FeaturePath.
3. If FINISHED is returned, execute the following statement to update the feature:
```
/*polar4ai*/UPDATE FEATURE oneID WITH (parameters=()) AS (SELECT device_id, android_id,oaid,ip,user_id FROM game_platform_info)
```
4. Execute the following statement to check the status of the operation:
```
/*polar4ai*/SHOW FEATURE oneID;
```
5. If FINISHED is returned, execute the following statement to update the feature:
```
/*polar4ai*/UPDATE FEATURE oneID WITH (parameters=()) AS (SELECT device_id,user_id as game_user_id,ipv4 as ip,ipv6,imei,idfa,googleid,android_id,oaid FROM game_info)
```
Generate a mapping table.
Execute the following statement to generate a mapping table:
```
/*polar4ai*/CREATE FEATURE csg_1 WITH ( feature_class = 'connected_subgraph',parameters=()) USING FEATURE oneID
```
Execute the following statement to check the status of the operation:
```
/*polar4ai*/SHOW FEATURE csg_1;
```
If the operation is in the FINISHED state, the OSS path is returned for FeaturePath. You can copy the path to your browser to view the ID-Mapping result.
View the ID-mapping result.
In the following table, column indicates the name of the column in the information tables, csg_id indicates the ID of the sub-graph with which the column is associated, and value indicates the value of the column.
csg_id
column
value
0
user_id
user1
0
device_id
device1
0
oaid
oaid1
1
user_id
user2
1
device_id
device2

ID-mapping builds association across discrete information. ID-mapping is a basic step for processing data related to IDs, In most cases, it is the fundamental and most important process for creating user portraits. The data generated through ID-mapping serves as the foundation for further data development operations.

Other use cases of ID-mapping

Augment user behaviors

Data processed through ID-mapping provides better support for upper-layer services such as custom search and recommendation to help improve their effectiveness. Data related to the behaviors of a user on different applications can be aggregated. For example, in e-commerce scenarios, data about a user who shops offline and online can be aggregated for more comprehensive analysis based on the preferences of the user.

Discover malicious groups

In the e-commerce industry, click farming is a thorny issue. Click farmers have multiple devices and multiple accounts on platforms and use them to commit fraudulent behaviors such as earning commissions without authorization, scrambling for limited coupons, and giving fake positive reviews. They undermine the ecosystems on e-commerce platforms and are a major issue for sellers and buyers. ID-mapping can associate the different IDs to detect account exceptions. For example, one device has multiple active user IDs, or one user ID uses multiple device IDs within a short period of time. The interactions between associated accounts can also be used to discover malicious groups.

Expand user portraits

User portraits that are generated based on basic user data and behaviors can provide information such as a user's preference for a brand or a product category. However, user portraits may not cover all users. ID-mapping can discover the relationship between IDs and supplement user information to expand user portraits. For example, if an ID belongs to a user who has a preference for a product category and is a member of a family, the IDs of the other members of the family can be discovered and their owners may have the same preference.

Advertise targeting

Advertisements can be delivered based on interests and device types of users. For example, a player may play multiple games on a gaming platform or use multiple devices to play their games. ID-mapping associates different game accounts and device information of the same user, and targets the user for advertising across devices and games. An advertisement can be shown to the user when the user plays different games or uses different devices. This helps provide better advertising results compared with only sending the advertisement on a single game based on user preference.

csg_id	column	value
0	user_id	user1
0	device_id	device1
0	oaid	oaid1
1	user_id	user2
1	device_id	device2