PolarDB:Apply ID-mapping in gaming

Step 1: Understand the table schemas

This example uses three tables. Review the schemas before writing the SELECT statements in later steps — column names matter.

`user_info` — user attribute information:

`game_platform_info` — game platform login records:

`game_info` — in-game account login records:

Column name collisions to watch for:

Two types of collision can silently corrupt the graph if not handled in the SELECT statement:

Step 2: Create the initial graph feature

Load user_info as the starting graph. This creates a graph feature named oneID where plat_user_id (aliased to user_id) and device_id are connected nodes.

/*polar4ai*/CREATE FEATURE oneID WITH ( feature_class = 'graph',parameters=()) AS (SELECT plat_user_id as user_id, device_id FROM user_info)

Check whether the operation has finished:

/*polar4ai*/SHOW FEATURE oneID;

• If FeaturePath shows FINISHED, proceed to the next step.

• If FeaturePath shows Please wait for the feature calculation to finish, wait and check again.

Step 3: Merge game platform data

Once step 2 shows FINISHED, add game_platform_info to the graph. This links device identifiers (device_id, android_id, oaid, ip) to the platform user ID (user_id).

/*polar4ai*/UPDATE FEATURE oneID WITH (parameters=()) AS (SELECT device_id, android_id,oaid,ip,user_id FROM game_platform_info)

Check the status:

/*polar4ai*/SHOW FEATURE oneID;

Step 4: Merge in-game account data

Once step 3 shows FINISHED, add game_info. Note that user_id in this table holds the game account ID — a different concept from the platform user ID — so alias it to game_user_id to avoid a collision. Similarly, alias ipv4 to ip so it matches the node already in the graph.

/*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)

Step 5: Generate the mapping table

Compute connected subgraphs from the oneID graph. Every node reachable from every other node is assigned the same csg_id.

/*polar4ai*/CREATE FEATURE csg_1 WITH ( feature_class = 'connected_subgraph',parameters=()) USING FEATURE oneID

Check the status:

/*polar4ai*/SHOW FEATURE csg_1;

When FeaturePath shows FINISHED, the OSS path to the mapping table result is returned. Copy the path into your browser to view the output.

Step 6: Read the mapping table

The output table has three columns:

Example output:

In this result, user1, device1, and oaid1 are all associated with the same player (csg_id = 0). user2 and device2 belong to a different player (csg_id = 1).

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. 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 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.