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.
Execute the following statement to create a feature named oneID:
CREATE FEATURE oneID WITH ( feature_class = 'graph',parameters=()) AS (SELECT plat_user_id as user_id, device_id FROM user_info)
Execute the following statement to check the status of the operation:
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
.
If FINISHED
is returned, execute the following statement to update the feature:
UPDATE FEATURE oneID WITH (parameters=()) AS (SELECT device_id, android_id,oaid,ip,user_id FROM game_platform_info)
Execute the following statement to check the status of the operation:
If FINISHED
is returned, execute the following statement to update the feature:
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:
CREATE FEATURE csg_1 WITH ( feature_class = 'connected_subgraph',parameters=()) USING FEATURE oneID
Execute the following statement to check the status of the operation:
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.