Crescendo Lab BigQuery Views (0.1.0)

This documentation provides the schema of all BigQuery views for analytics and data access.

📞 Support

For integration assistance or inquiries, contact us at help@cresclab.com.

📊 BigQuery Data Views

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.

Autoreply Report View

View Name: autoreply_report

Provides a summary of autoreply performance, including reply and trigger counts, revenue, and engagement metrics.

Schema Fields

FieldTypeDescription
dateDateThe date of the report (YYYY-MM-DD format)
campaign_nameStringThe name of the autoreply campaign
new_reply_countIntegerThe number of new replies
unbound_reply_countIntegerThe number of replies from unbound users
bound_reply_countIntegerThe number of replies from bound users
new_trigger_countIntegerThe number of new triggers
unbound_trigger_countIntegerThe number of triggers from unbound users
bound_trigger_countIntegerThe number of triggers from bound users
revenueNumberThe revenue generated from the campaign
unique_clicksIntegerThe number of unique clicks
ordersIntegerThe number of orders

Channel Entity View

View Name: channel_entity

Provides detailed information about channel entities, including their profile, contact information, and engagement status.

Schema Fields

FieldTypeDescription
channel_typeStringThe type of the channel
external_member_idStringThe external member ID
display_nameStringThe display name of the user
original_nameStringThe original name of the user
display_mobileStringThe display mobile number of the user
original_mobileStringThe original mobile number of the user
display_emailStringThe display email of the user
original_emailStringThe original email of the user
custom_idStringThe custom ID of the user
connect_idStringThe connect ID of the user
birthdayStringThe birthday of the user (YYYY-MM-DD format)
genderStringThe gender of the user
healthStringThe health status of the user's profile
last_engagedStringThe last time the user was engaged (ISO 8601)
locationStringThe location of the user
last_message_atStringThe last time a message was sent (ISO 8601)
last_member_message_sendStringThe last time the member sent a message (ISO 8601)
companyStringThe company of the user
full_nameStringThe full name of the user
cityStringThe city of the user
countryStringThe country of the user
account_managerStringThe account manager for the user
member_levelStringThe member level of the user
star_markStringWhether the user is marked with a star
statusStringThe status of the user

Contact Profile Customer ID Update View

View Name: contact_profile_customer_id_update

Provides information about updates to the customer ID in a contact's profile.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
customer_idStringThe customer ID
sourceStringThe source of the event
timestampTimestampThe timestamp of the event (ISO 8601)

Contact Profile Update View

View Name: contact_profile_update

Provides information about updates to a contact's profile.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
customer_idStringThe customer ID
attributeStringThe attribute that was updated
valueStringThe new value of the attribute
sourceStringThe source of the event
timestampTimestampThe timestamp of the event (ISO 8601)

Contact Tag View

View Name: contact_tag

Provides information about tags associated with a contact.

Schema Fields

FieldTypeDescription
channel_typeStringThe type of the channel
external_member_idStringThe external member ID
tag_sourceStringThe source of the tag
tag_idStringThe ID of the tag
tag_nameStringThe name of the tag
timestampTimestampThe timestamp of the event (ISO 8601)

Contact Tag Add View

View Name: contact_tag_add

Provides information about a tag being added to a contact.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
channel_typeStringThe type of the channel
external_member_idStringThe external member ID
customer_idStringThe customer ID
sourceStringThe source of the event
timestampTimestampThe timestamp of the event (ISO 8601)
campaign_idStringThe ID of the campaign
campaign_nameStringThe name of the campaign
campaign_typeStringThe type of the campaign
tag_idStringThe ID of the tag
tag_nameStringThe name of the tag

Contact Tag Remove View

View Name: contact_tag_remove

Provides information about a tag being removed from a contact.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
channel_typeStringThe type of the channel
external_member_idStringThe external member ID
customer_idStringThe customer ID
sourceStringThe source of the event
timestampTimestampThe timestamp of the event (ISO 8601)
tag_idStringThe ID of the tag
tag_nameStringThe name of the tag

Contacts View

View Name: contacts

Provides a list of contacts with their basic information.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
line_display_nameStringThe display name in LINE
emailStringThe email of the contact
mobileStringThe mobile number of the contact
customer_idStringThe customer ID
genderStringThe gender of the contact
birthdayDateThe birthday of the contact (YYYY-MM-DD)
statusStringThe status of the contact
created_atTimestampThe creation date of the contact (ISO 8601)

Game Report View

View Name: game_report

Provides a report on game performance, including participant and winner counts.

Schema Fields

FieldTypeDescription
nameStringThe name of the game
categoryStringThe category of the game
validity_started_atTimestampThe start date of the game's validity (ISO 8601)
validity_ended_atTimestampThe end date of the game's validity (ISO 8601)
activity_started_atTimestampThe start date of the game's activity (ISO 8601)
activity_ended_atTimestampThe end date of the game's activity (ISO 8601)
share_countIntegerThe number of times the game was shared
winner_countIntegerThe number of winners
participant_countIntegerThe number of participants

Journey Node Performance View

View Name: journey_node_performance

Provides performance metrics for each node in a journey, including traffic, engagement, and conversion data.

Schema Fields

FieldTypeDescription
journey_nameStringThe name of the journey
journey_statusStringThe status of the journey
node_nameStringThe name of the node
node_typeStringThe type of the node
journey_created_atTimestampThe creation date of the journey (ISO 8601)
report_dateDateThe date of the report (YYYY-MM-DD)
traffic_countIntegerThe number of users who visited the node
line_message_sentIntegerThe number of LINE messages sent
open_countIntegerThe number of times the message was opened
keyword_repeat_clicksIntegerThe number of repeat clicks on keywords
url_repeat_clicksIntegerThe number of repeat clicks on URLs
unique_clicksIntegerThe number of unique clicks
adds_to_cartIntegerThe number of times items were added to cart
transactionsIntegerThe number of transactions
transaction_revenueFloatThe revenue generated from transactions

LINE Broadcast Report View

View Name: line_broadcast_report

Provides a report on LINE broadcast performance, including sent counts, open rates, and engagement metrics.

Schema Fields

FieldTypeDescription
nameStringThe name of the broadcast
schedule_timeTimestampThe scheduled time of the broadcast (ISO 8601)
statusStringThe status of the broadcast
sent_countIntegerThe number of messages sent
open_countIntegerThe number of times the message was opened
unique_clicksIntegerThe number of unique clicks
repeat_clicksIntegerThe number of repeat clicks
revenueFloatThe revenue generated from the broadcast

Message Click View

View Name: message_click

Provides information about a click on a message.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
customer_idStringThe customer ID
sourceStringThe source of the event
timestampTimestampThe timestamp of the event (ISO 8601)
campaign_idStringThe ID of the campaign
campaign_nameStringThe name of the campaign
campaign_typeStringThe type of the campaign
linkStringThe link that was clicked

Message Open View

View Name: message_open

Provides information about an open on a message.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
customer_idStringThe customer ID
sourceStringThe source of the event
timestampTimestampThe timestamp of the event (ISO 8601)
campaign_idStringThe ID of the campaign
campaign_nameStringThe name of the campaign
campaign_typeStringThe type of the campaign

Message Send View

View Name: message_send

Provides information about a send on a message.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
customer_idStringThe customer ID
sourceStringThe source of the event
timestampTimestampThe timestamp of the event (ISO 8601)
campaign_idStringThe ID of the campaign
campaign_nameStringThe name of the campaign
campaign_typeStringThe type of the campaign

Prize Claim View

View Name: prize_claim

Provides information about a prize being claimed.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
customer_idStringThe customer ID
sourceStringThe source of the event
timestampTimestampThe timestamp of the event (ISO 8601)
campaign_idStringThe ID of the campaign
campaign_nameStringThe name of the campaign
campaign_typeStringThe type of the campaign
codeStringThe code of the prize

Prize Redeem View

View Name: prize_redeem

Provides information about a prize being redeemed.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
customer_idStringThe customer ID
sourceStringThe source of the event
timestampTimestampThe timestamp of the event (ISO 8601)
campaign_idStringThe ID of the campaign
campaign_nameStringThe name of the campaign
campaign_typeStringThe type of the campaign
codeStringThe code of the prize

Prize Send View

View Name: prize_send

Provides information about a prize being sent.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
customer_idStringThe customer ID
sourceStringThe source of the event
timestampTimestampThe timestamp of the event (ISO 8601)
campaign_idStringThe ID of the campaign
campaign_nameStringThe name of the campaign
campaign_typeStringThe type of the campaign

Receipt Member View

View Name: receipt_member

Provides information about receipt members.

Schema Fields

FieldTypeDescription
receipt_numberStringThe receipt number
receipt_statusStringThe status of the receipt
receipt_created_timeTimestampThe creation time of the receipt (ISO 8601)
receipt_random_numberStringThe random number on the receipt
seller_nameStringThe name of the seller
seller_unified_business_numberStringThe unified business number of the seller
total_amountFloatThe total amount of the receipt
buyer_unified_business_numberStringThe unified business number of the buyer
receipt_campaign_nameStringThe name of the receipt campaign
line_uidStringThe LINE user ID
customer_idStringThe customer ID

Rich Menu Report View

View Name: richmenu_report

Provides a report on rich menu performance, including revenue, clicks, and orders.

Schema Fields

FieldTypeDescription
dateDateThe date of the report (YYYY-MM-DD)
scheduled_start_timeTimestampThe scheduled start time of the rich menu (ISO 8601)
scheduled_end_timeTimestampThe scheduled end time of the rich menu (ISO 8601)
campaign_nameStringThe name of the campaign
revenueFloatThe revenue generated from the rich menu
clicksIntegerThe number of clicks on the rich menu
ordersIntegerThe number of orders from the rich menu
utm_campaignStringThe UTM campaign parameter
utm_mediumStringThe UTM medium parameter
utm_sourceStringThe UTM source parameter
trigger_codeStringThe trigger code for the rich menu

SMS Broadcast Report View

View Name: sms_broadcast_report

Provides a report on SMS broadcast performance, including sent, delivered, and click counts.

Schema Fields

FieldTypeDescription
nameStringThe name of the broadcast
schedule_timeTimestampThe scheduled time of the broadcast (ISO 8601)
statusStringThe status of the broadcast
sent_countIntegerThe number of messages sent
delivered_countIntegerThe number of messages delivered
clicksIntegerThe number of clicks

SMS Plus Status Update View

View Name: sms_plus_status_update

Provides information about status updates for SMS Plus messages.

Schema Fields

FieldTypeDescription
timestampTimestampThe timestamp of the event (ISO 8601)
statusStringThe status of the message
pnp_message_idStringThe PnP message ID
event_idStringThe event ID
phone_numberStringThe phone number
line_push_template_idStringThe LINE push template ID
line_push_statusStringThe status of the LINE push
line_push_delivered_atStringThe delivery time of the LINE push (ISO 8601)
line_push_error_categoryStringThe error category for the LINE push
pnp_statusStringThe status of the PnP message
pnp_delivered_atStringThe delivery time of the PnP message (ISO 8601)
sms_statusStringThe status of the SMS
sms_delivered_atStringThe delivery time of the SMS (ISO 8601)
sms_error_categoryStringThe error category for the SMS

Tags View

View Name: tags

Provides a list of tags associated with a LINE user.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
tag_nameStringThe name of the tag

View Name: tracelink_report

Provides a report on tracelink performance, including clicks, revenue, and orders.

Schema Fields

FieldTypeDescription
nameStringThe name of the tracelink
linkStringThe original link
shorten_urlStringThe shortened URL
created_atStringThe creation date of the tracelink (ISO 8601)
dateDateThe date of the report (YYYY-MM-DD)
repeat_clicksIntegerThe number of repeat clicks
unique_clicksIntegerThe number of unique clicks
revenueFloatThe revenue generated from the tracelink
ordersIntegerThe number of orders from the tracelink
add_to_cartIntegerThe number of times items were added to cart

Transactions View

View Name: transactions

Provides a list of transactions with their associated campaign and attribution information.

Schema Fields

FieldTypeDescription
line_uidStringThe LINE user ID
utm_campaignStringThe UTM campaign parameter
utm_contentStringThe UTM content parameter
utm_mediumStringThe UTM medium parameter
utm_sourceStringThe UTM source parameter
timeTimestampThe time of the transaction (ISO 8601)
revenueFloatThe revenue from the transaction
transactionsIntegerThe number of transactions
ref_nameStringThe name of the referring entity
ref_typeStringThe type of the referring entity