Last Updated: Wednesday, May 6, 2020
This data set contains click events associated with lists of the company's account.
How much data is retained?
This data set will contain 7 days worth of data starting from the previous day. For example, if you are looking at the data set today, 7/18/2019, it will contain data from 7/11/2019 to present.
What data is included?
Column Name | Type | Nullable | Default | Comment |
ACTIVITY_NAME | VARCHAR(4000) | TRUE | NULL | The campaign name that is correlated to the click |
ACTIVITY_TYPE | VARCHAR(16777216) | TRUE | NULL | Type of activity the click is associated with. See here for a list of activity types. |
ACTIVITY_ID | VARCHAR(255) | TRUE | NULL | Vibes unique identifier for the campaign that associated with the click |
CLICK_AT_TTZ | TIMESTAMP_TZ(9) | TRUE | NULL | The date timestamp when a click occurred. This timestamp will be in the time zone of the company. |
CLICK_ID | VARCHAR(50) | TRUE | NULL | Vibes unique identifier for this click |
COMPANY_KEY | NUMBER(38,0) | TRUE | NULL | Vibes unique identifier used to identify a company that is associated with the click |
DW_CREATED_AT | TIMESTAMP_TZ(9) | TRUE | NULL | The date timestamp that indicates when the row was created in Vibes data warehouse. The time zone is central time for North America customers and UTC for European Customers |
EXTERNAL_PERSON_ID | VARCHAR(16777216) | TRUE | NULL | A subscriber specific identifier that is set by the company |
FIRST_CLICK | NUMBER(38,0) | TRUE | NULL | Flag- if this was the first click on the URL |
FIRST_DEVICE_CLICK | NUMBER(38,0) | TRUE | NULL | Flag- if this was the first click on the URL by the mobile phone |
LONG_URL | VARCHAR(2048) | TRUE | NULL | Long URL that was shortened. |
MESSAGE_ID | VARCHAR(16777216) | TRUE | NULL | The Vibes identifier for the message the click is associated with |
PERSON_KEY | VARCHAR(40) | TRUE | NULL | Vibes unique identifier for each Person record in the Mobile Database |
PHONE_NUMBER | VARCHAR(255) | TRUE | NULL | The mobile phone number of the individual who clicked |
PHONE_NUMBER_E164_FORMAT | VARCHAR(255) | TRUE | NULL | The mobile phone number of the individual that clicked that also includes country code |
SHORT_DOMAIN | VARCHAR(255) | TRUE | NULL | Domain that the shortened URL was created on. Example: vbs.cm. |
SHORT_KEY | VARCHAR(50) | TRUE | NULL | URI of the shortened URL. |
PERSON_ID | INT | TRUE | NULL | LEGACY: integer person_id for use with legacy Public APIs |
PERSON_UID | VARCHAR(40) | TRUE | NULL | LEGACY: UID for use with legacy Data Sharing integrations |
COMPANY_UID | VARCHAR(50) | TRUE | NULL | LEGACY: UID for use with legacy Data Sharing integrations |
CLICK_UID | VARCHAR(50) | TRUE | NULL | LEGACY: UID for use with legacy Data Sharing integrations |
MESSAGE_UID | VARCHAR(16777216) | TRUE | NULL | LEGACY: UID for use with legacy Data Sharing integrations |
ACTIVITY_UID | VARCHAR(255) | TRUE | NULL | LEGACY: UID for use with legacy Data Sharing integrations |
What are some sample queries to pull this data?
–- A query that will pull clicks from yesterday
SELECT
PERSON_KEY
,EXTERNAL_PERSON_ID
,PHONE_NUMBER
,ACTIVITY_ID
,ACTIVITY_NAME
FROM CLICKS
WHERE CAST(DW_CREATED_AT AS DATE) >= CAST(DATEADD(day,-1, CURRENT_DATE()) AS DATE)
AND CAST(DW_CREATED_AT AS DATE) < CAST(CURRENT_DATE() AS DATE)