Crescendo Lab BigQuery Views (0.1.0)
This documentation provides the schema of all BigQuery views for analytics and data access.
For integration assistance or inquiries, contact us at help@cresclab.com.
The following sections describe the available BigQuery views and their data schemas. Each view provides structured access to specific data sets for analysis and reporting.
| Field | Type | Description |
|---|---|---|
| date | Date | The date of the report (YYYY-MM-DD format) |
| campaign_name | String | The name of the autoreply campaign |
| new_reply_count | Integer | The number of new replies |
| unbound_reply_count | Integer | The number of replies from unbound users |
| bound_reply_count | Integer | The number of replies from bound users |
| new_trigger_count | Integer | The number of new triggers |
| unbound_trigger_count | Integer | The number of triggers from unbound users |
| bound_trigger_count | Integer | The number of triggers from bound users |
| revenue | Number | The revenue generated from the campaign |
| unique_clicks | Integer | The number of unique clicks |
| orders | Integer | The number of orders |
| Field | Type | Description |
|---|---|---|
| channel_type | String | The type of the channel |
| external_member_id | String | The external member ID |
| display_name | String | The display name of the user |
| original_name | String | The original name of the user |
| display_mobile | String | The display mobile number of the user |
| original_mobile | String | The original mobile number of the user |
| display_email | String | The display email of the user |
| original_email | String | The original email of the user |
| custom_id | String | The custom ID of the user |
| connect_id | String | The connect ID of the user |
| birthday | String | The birthday of the user (YYYY-MM-DD format) |
| gender | String | The gender of the user |
| health | String | The health status of the user's profile |
| last_engaged | String | The last time the user was engaged (ISO 8601) |
| location | String | The location of the user |
| last_message_at | String | The last time a message was sent (ISO 8601) |
| last_member_message_send | String | The last time the member sent a message (ISO 8601) |
| company | String | The company of the user |
| full_name | String | The full name of the user |
| city | String | The city of the user |
| country | String | The country of the user |
| account_manager | String | The account manager for the user |
| member_level | String | The member level of the user |
| star_mark | String | Whether the user is marked with a star |
| status | String | The status of the user |
| Field | Type | Description |
|---|---|---|
| line_uid | String | The LINE user ID |
| channel_type | String | The type of the channel |
| external_member_id | String | The external member ID |
| customer_id | String | The customer ID |
| source | String | The source of the event |
| timestamp | Timestamp | The timestamp of the event (ISO 8601) |
| campaign_id | String | The ID of the campaign |
| campaign_name | String | The name of the campaign |
| campaign_type | String | The type of the campaign |
| tag_id | String | The ID of the tag |
| tag_name | String | The name of the tag |
| Field | Type | Description |
|---|---|---|
| line_uid | String | The LINE user ID |
| channel_type | String | The type of the channel |
| external_member_id | String | The external member ID |
| customer_id | String | The customer ID |
| source | String | The source of the event |
| timestamp | Timestamp | The timestamp of the event (ISO 8601) |
| tag_id | String | The ID of the tag |
| tag_name | String | The name of the tag |
| Field | Type | Description |
|---|---|---|
| line_uid | String | The LINE user ID |
| line_display_name | String | The display name in LINE |
| String | The email of the contact | |
| mobile | String | The mobile number of the contact |
| customer_id | String | The customer ID |
| gender | String | The gender of the contact |
| birthday | Date | The birthday of the contact (YYYY-MM-DD) |
| status | String | The status of the contact |
| created_at | Timestamp | The creation date of the contact (ISO 8601) |
| Field | Type | Description |
|---|---|---|
| name | String | The name of the game |
| category | String | The category of the game |
| validity_started_at | Timestamp | The start date of the game's validity (ISO 8601) |
| validity_ended_at | Timestamp | The end date of the game's validity (ISO 8601) |
| activity_started_at | Timestamp | The start date of the game's activity (ISO 8601) |
| activity_ended_at | Timestamp | The end date of the game's activity (ISO 8601) |
| share_count | Integer | The number of times the game was shared |
| winner_count | Integer | The number of winners |
| participant_count | Integer | The number of participants |
| Field | Type | Description |
|---|---|---|
| journey_name | String | The name of the journey |
| journey_status | String | The status of the journey |
| node_name | String | The name of the node |
| node_type | String | The type of the node |
| journey_created_at | Timestamp | The creation date of the journey (ISO 8601) |
| report_date | Date | The date of the report (YYYY-MM-DD) |
| traffic_count | Integer | The number of users who visited the node |
| line_message_sent | Integer | The number of LINE messages sent |
| open_count | Integer | The number of times the message was opened |
| keyword_repeat_clicks | Integer | The number of repeat clicks on keywords |
| url_repeat_clicks | Integer | The number of repeat clicks on URLs |
| unique_clicks | Integer | The number of unique clicks |
| adds_to_cart | Integer | The number of times items were added to cart |
| transactions | Integer | The number of transactions |
| transaction_revenue | Float | The revenue generated from transactions |
| Field | Type | Description |
|---|---|---|
| name | String | The name of the broadcast |
| schedule_time | Timestamp | The scheduled time of the broadcast (ISO 8601) |
| status | String | The status of the broadcast |
| sent_count | Integer | The number of messages sent |
| open_count | Integer | The number of times the message was opened |
| unique_clicks | Integer | The number of unique clicks |
| repeat_clicks | Integer | The number of repeat clicks |
| revenue | Float | The revenue generated from the broadcast |
| Field | Type | Description |
|---|---|---|
| line_uid | String | The LINE user ID |
| customer_id | String | The customer ID |
| source | String | The source of the event |
| timestamp | Timestamp | The timestamp of the event (ISO 8601) |
| campaign_id | String | The ID of the campaign |
| campaign_name | String | The name of the campaign |
| campaign_type | String | The type of the campaign |
| link | String | The link that was clicked |
| Field | Type | Description |
|---|---|---|
| line_uid | String | The LINE user ID |
| customer_id | String | The customer ID |
| source | String | The source of the event |
| timestamp | Timestamp | The timestamp of the event (ISO 8601) |
| campaign_id | String | The ID of the campaign |
| campaign_name | String | The name of the campaign |
| campaign_type | String | The type of the campaign |
| Field | Type | Description |
|---|---|---|
| line_uid | String | The LINE user ID |
| customer_id | String | The customer ID |
| source | String | The source of the event |
| timestamp | Timestamp | The timestamp of the event (ISO 8601) |
| campaign_id | String | The ID of the campaign |
| campaign_name | String | The name of the campaign |
| campaign_type | String | The type of the campaign |
| Field | Type | Description |
|---|---|---|
| line_uid | String | The LINE user ID |
| customer_id | String | The customer ID |
| source | String | The source of the event |
| timestamp | Timestamp | The timestamp of the event (ISO 8601) |
| campaign_id | String | The ID of the campaign |
| campaign_name | String | The name of the campaign |
| campaign_type | String | The type of the campaign |
| code | String | The code of the prize |
| Field | Type | Description |
|---|---|---|
| line_uid | String | The LINE user ID |
| customer_id | String | The customer ID |
| source | String | The source of the event |
| timestamp | Timestamp | The timestamp of the event (ISO 8601) |
| campaign_id | String | The ID of the campaign |
| campaign_name | String | The name of the campaign |
| campaign_type | String | The type of the campaign |
| code | String | The code of the prize |
| Field | Type | Description |
|---|---|---|
| line_uid | String | The LINE user ID |
| customer_id | String | The customer ID |
| source | String | The source of the event |
| timestamp | Timestamp | The timestamp of the event (ISO 8601) |
| campaign_id | String | The ID of the campaign |
| campaign_name | String | The name of the campaign |
| campaign_type | String | The type of the campaign |
| Field | Type | Description |
|---|---|---|
| receipt_number | String | The receipt number |
| receipt_status | String | The status of the receipt |
| receipt_created_time | Timestamp | The creation time of the receipt (ISO 8601) |
| receipt_random_number | String | The random number on the receipt |
| seller_name | String | The name of the seller |
| seller_unified_business_number | String | The unified business number of the seller |
| total_amount | Float | The total amount of the receipt |
| buyer_unified_business_number | String | The unified business number of the buyer |
| receipt_campaign_name | String | The name of the receipt campaign |
| line_uid | String | The LINE user ID |
| customer_id | String | The customer ID |
| Field | Type | Description |
|---|---|---|
| date | Date | The date of the report (YYYY-MM-DD) |
| scheduled_start_time | Timestamp | The scheduled start time of the rich menu (ISO 8601) |
| scheduled_end_time | Timestamp | The scheduled end time of the rich menu (ISO 8601) |
| campaign_name | String | The name of the campaign |
| revenue | Float | The revenue generated from the rich menu |
| clicks | Integer | The number of clicks on the rich menu |
| orders | Integer | The number of orders from the rich menu |
| utm_campaign | String | The UTM campaign parameter |
| utm_medium | String | The UTM medium parameter |
| utm_source | String | The UTM source parameter |
| trigger_code | String | The trigger code for the rich menu |
| Field | Type | Description |
|---|---|---|
| name | String | The name of the broadcast |
| schedule_time | Timestamp | The scheduled time of the broadcast (ISO 8601) |
| status | String | The status of the broadcast |
| sent_count | Integer | The number of messages sent |
| delivered_count | Integer | The number of messages delivered |
| clicks | Integer | The number of clicks |
| Field | Type | Description |
|---|---|---|
| timestamp | Timestamp | The timestamp of the event (ISO 8601) |
| status | String | The status of the message |
| pnp_message_id | String | The PnP message ID |
| event_id | String | The event ID |
| phone_number | String | The phone number |
| line_push_template_id | String | The LINE push template ID |
| line_push_status | String | The status of the LINE push |
| line_push_delivered_at | String | The delivery time of the LINE push (ISO 8601) |
| line_push_error_category | String | The error category for the LINE push |
| pnp_status | String | The status of the PnP message |
| pnp_delivered_at | String | The delivery time of the PnP message (ISO 8601) |
| sms_status | String | The status of the SMS |
| sms_delivered_at | String | The delivery time of the SMS (ISO 8601) |
| sms_error_category | String | The error category for the SMS |
| Field | Type | Description |
|---|---|---|
| name | String | The name of the tracelink |
| link | String | The original link |
| shorten_url | String | The shortened URL |
| created_at | String | The creation date of the tracelink (ISO 8601) |
| date | Date | The date of the report (YYYY-MM-DD) |
| repeat_clicks | Integer | The number of repeat clicks |
| unique_clicks | Integer | The number of unique clicks |
| revenue | Float | The revenue generated from the tracelink |
| orders | Integer | The number of orders from the tracelink |
| add_to_cart | Integer | The number of times items were added to cart |
| Field | Type | Description |
|---|---|---|
| line_uid | String | The LINE user ID |
| utm_campaign | String | The UTM campaign parameter |
| utm_content | String | The UTM content parameter |
| utm_medium | String | The UTM medium parameter |
| utm_source | String | The UTM source parameter |
| time | Timestamp | The time of the transaction (ISO 8601) |
| revenue | Float | The revenue from the transaction |
| transactions | Integer | The number of transactions |
| ref_name | String | The name of the referring entity |
| ref_type | String | The type of the referring entity |