Intelligent Data Insight
Intelligent Data Insight
- Documentation
Intelligent Data InsightDataWind User GuideData PreparationVisual ModelingCommon Template: Case IntroductionStatistical Analysis of Key Automotive User Behaviors
Copy page
Download PDFCopy pageDownload PDFCommon data collection methods in automotive sales are mainly divided into online (APP/WeChat Mini Program) and offline (in-store registration/offline QR code scanning). When performing user behavior statistics, it is often necessary to integrate information from different data tables and combine the basic attributes in the id-mapping table to calculate key user metrics.
This case comprehensively considers both users' online and offline behaviors, including the time users enter the optional package, the time they access the Mini Program, and their visits to offline stores. By taking these factors into account, key user behaviors are calculated to help determine whether users are interested in purchasing a car, and ultimately, the CDP platform is used to assist in user marketing decisions.
The system provides convenient interactive data processing capabilities. Combined with data exploration and result preview, users can process data while viewing results, lowering the threshold for traditional data processing. Even without professional SQL skills, users can easily get started.
2.1 Data source integration
The product currently supports integration with more than 20 mainstream data sources, and supports data sampling, filtering, and reading specific columns.
2.2 Data processing
Provides a variety of data processing operators, such as:
Processing method | Explanation |
|---|---|
Field settings | Column name/Column type/Column filter |
Join | Data join, supports left and right inner/outer joins, and cross-source joins |
Aggregation | Select aggregation fields and methods. You can change the aggregation method and set the name of the aggregated field |
Calculated column | Use Spark functions to process upstream fields to add new fields. It can also add a business date field to tables without a business date. |
Filter rows | Select fields and confirm filter conditions. Supports two-level and/or logical relationships. |
String index | A type conversion operator that maps the value of a specified attribute to a numeric index, allowing operators that can only process numeric data to also handle attributes. This operator is generally used for data preprocessing. In addition, it is not suitable for columns containing continuous data, such as ID columns. |
Replace missing values | Replace null values, supports replacement with min/max/avg/fixed value |
By aggregating both online and offline tables, the system calculates the duration of browsing the optional package page, the time of entering the Mini Program, and the number of offline store visits. Combined with users' basic attributes, the user's active duration is calculated and used as a metric to connect to the CDP or output to Hive/ClickHouse for subsequent processing.
3.1 Data description
Online behavior data table (user unique identifier id and device id):
Field name | Field type | Field meaning | Field description |
|---|---|---|---|
p_date | string | Partition field, date | Data tracking is partitioned by date and stored daily |
tenant_id | int | Tenant ID | Channel access method |
device_id | string | Device ID | User device ID |
user_id | string | User ID | Used to uniquely identify the user |
app_id | int | App ID | Client app ID |
event | string | Behavior event | enter_mp: Enter the WeChat Official Account |
string_params | map | Event details, stores character actions | enter_from: WeChat Official Account/Offline QR code scan |
decimal_params | map | Event details, stores numbers | stay_time: Duration of stay |
time | bigint | Occurrence time, timestamp | The time when the event occurs, timestamp type |
Offline behavior data table (registered username and phone number):
Field name | Field type | Field meaning | Field description |
|---|---|---|---|
p_date | string | Partition field, date | Data tracking is partitioned by date and stored daily |
tenant_id | int | Tenant ID | Channel access method |
name | string | User name | The user's name, which will be matched with id-mapping |
phone | string | User's phone number | User's registered phone number |
app_id | int | App id | Client app id |
event | string | Behavior event | enter_mp: Enter the official WeChat account |
string_params | map | Event details, stores actions as characters | enter_from: Official account/offline QR code scan |
decimal_params | map | Event details, stores numeric values | stay_time: Dwell time |
time | bigint | Occurrence time, timestamp | Event occurrence, timestamp type |
ID_Mapping table:
Field name | Field type | Field meaning | Field description |
|---|---|---|---|
date | string | Partition field, date | Date of user information update, full update daily |
uid | bigint | User id | Used to uniquely identify the user |
nickname | string | User nickname | User's registered name on the platform |
name | string | User name | User's name, matched with id-mapping |
phone | array | User's phone number | User's registered phone numbers; multiple numbers |
carnumber | array | User's license plate number | User's registered license plate numbers; multiple plates |
did | array | Device id | User's mobile device id; multiple devices |
string | User's registered email |
3.2 Data preparation
3.2.1 Online data processing
The main online behavior data collected includes the number of visits (the number of times entering the optional package plus the number of times entering the Mini Program) and statistics on visit duration (exit time).
- Use filter and aggregation operators to filter events for entering the optional package and entering the mini program, then aggregate them together (count the number of entries).
- Use event filtering and calculated columns (extracting the value from map-type fields), then aggregate and sum to calculate a user's dwell time.
- Finally, merge the two sets of data to obtain the final effective metrics for user visits (number of visits and dwell time).
3.2.2 Offline data and id-mapping processing
Offline behavior mainly collects the number of store visits, typically recording the username and phone number, so it needs to be merged with the id table.
- Use filter and aggregation operators to filter the number of store entries.
- Merge with the id-mapping table to obtain the user's unique id.
3.2.3 Data integration statistics & data production applications
Finally, use the join operator to merge online and offline data, and the processed results can be output to hive/ClickHouse. Subsequent visualization queries and dashboard creation can be performed on the DataWind platform. It can also be integrated with CDP products for tag insights, marketing applications, user segmentation, and more.
