Clicks Data
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 most recent data refresh. Data is refreshed once per day. For example, if you are looking at the data set on 7/18, it will contain data from 7/11 to present.
What data can be pulled?
| Column Name | Type | Nullable | Default | Definition |
|---|---|---|---|---|
| ACTIVITY_NAME | VARCHAR(4000) | TRUE | NULL | The campaign name that is correlated to the click |
| ACTIVITY_TYPE | VARCHAR(16777216) | TRUE | NULL | Type of campaign or 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 | VARCHAR(50) | 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 person identifier that is set by you, usually in conjunction with a CRM |
| FIRST_CLICK | NUMBER(38,0) | TRUE | NULL | Flag- Indicates if this was the first click on the URL |
| FIRST_DEVICE_CLICK | NUMBER(38,0) | TRUE | NULL | Flag- Indicates 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 in E.164 |
| 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. |
| USER_AGENT_HEADER | VARCHAR(16777216) | TRUE | NULL | The first 255 characters of the browser user-agent header value. |
Sample Query
–- A query that will pull clicks from yesterday
SELECT
PERSON_KEY
,EXTERNAL_PERSON_ID
,PHONE_NUMBER
,ACTIVITY_ID
,ACTIVITY_NAME
,LONG_URL
,DW_CREATED_AT
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)
Updated about 1 year ago