Data structure in Google BigQuery
If you do not have a Google BigQuery account with billing enabled yet, detailed instructions on how Google BigQuery works and how to set up the BigQuery integration are in this section: R7K12 Integration with Google BigQuery.
If you want to learn how to export all project data from R7K12 to BigQuery, a detailed description is in the same section - the Setting up the integration in R7K12 item.
Below is a description of the parameters and metrics exported to BigQuery via the tables created from R7K12 using the three integration templates (described in the instructions above).
Structure of the “sessions” table (visit data)
|
|
|
| Field |
Type |
Name in the R7K12 interface |
| idProject |
INTEGER |
Project ID |
| nameProject |
STRING |
Project name |
| id |
STRING |
Visit ID |
| clientId |
STRING |
Client ID |
| gaClientId |
STRING |
Google Analytics client ID |
| ymClientId |
STRING |
Yandex.Metrica client ID |
| idUser |
STRING |
User ID |
| ip |
STRING |
Client IP address |
| visitNumber |
INTEGER |
Visit number |
| visitStartTime |
DATETIME |
Visit start date/time |
| visitEndTime |
DATETIME |
Visit end date/time |
| year |
INTEGER |
Year |
| month |
INTEGER |
Month |
| dayOfMonth |
INTEGER |
Day of month |
| dayOfWeek |
INTEGER |
Day of week |
| hour |
INTEGER |
Hour |
| minute |
INTEGER |
Minute |
| second |
INTEGER |
Second |
| date |
DATE |
Visit date |
| url |
STRING |
Page URL |
| host |
STRING |
Domain |
| path |
STRING |
Landing page |
| query |
STRING |
Query parameters |
| hash |
STRING |
Request hash |
| utm_source |
STRING |
Source (UTM) |
| utm_medium |
STRING |
Medium (UTM) |
| utm_campaign |
STRING |
Campaign (UTM) |
| utm_content |
STRING |
Content (UTM) |
| utm_term |
STRING |
Keyword (UTM) |
| isTrueUtm |
INTEGER |
Has UTM tags |
| groupSource |
STRING |
Source group name |
| source |
STRING |
Source name |
| source1 |
STRING |
Source (level 1) |
| source2 |
STRING |
Source (level 2) |
| source3 |
STRING |
Source (level 3) |
| source4 |
STRING |
Source (level 4) |
| source5 |
STRING |
Source (level 5) |
| source6 |
STRING |
Source (level 6) |
| source7 |
STRING |
Source (level 7) |
| gclId |
STRING |
Google AdWords ClickId |
| isTrueDirect |
INTEGER |
Direct visit |
| isTrueBot |
INTEGER |
Bot |
| isTrueOffline |
INTEGER |
Offline visit |
| deviceType |
STRING |
Device |
| browser |
STRING |
Browser |
| browserVersion |
STRING |
Browser version |
| operatingSystem |
STRING |
Operating system |
| operatingSystemVersion |
STRING |
Operating system version |
| city |
STRING |
City |
| region |
STRING |
Region |
| country |
STRING |
Country |
| sessions |
INTEGER |
Visits |
| newVisit |
INTEGER |
New users |
| users |
INTEGER |
Users |
| timeOfSite |
INTEGER |
Time on site |
| totalsEvent |
INTEGER |
Events |
| totalsPageView |
INTEGER |
Pageviews |
| totalsErrorJs |
INTEGER |
JavaScript errors |
| totalsError404 |
INTEGER |
404 errors |
| bounces |
INTEGER |
Bounces |
| cost |
FLOAT |
Cost (unattributed) |
| costAttr |
FLOAT |
Cost |
| appeals |
INTEGER |
Inquiries |
| application |
INTEGER |
Requests |
| leads |
INTEGER |
Deals |
| contacts |
INTEGER |
Contacts |
| revenue |
FLOAT |
Revenue |
| revenueCancel |
FLOAT |
Revenue from canceled requests |
| payment |
FLOAT |
Payment amount |
| paymentTotal |
INTEGER |
Number of payments |
| paymentCancel |
FLOAT |
Canceled payment amount |
| paymentCancelTotal |
INTEGER |
Number of canceled payments |
| paymentAccess |
FLOAT |
Confirmed payment amount |
| paymentAccessTotal |
INTEGER |
Number of confirmed payments |
| marginality |
FLOAT |
Margin |
| applicationCancel |
INTEGER |
Canceled requests |
| revenuePotential |
FLOAT |
Potential revenue |
| revenueForecast |
FLOAT |
Forecast revenue |
| profitForecast |
FLOAT |
Forecast profit |
| sellingForecast |
INTEGER |
Forecast sales |
| profit |
FLOAT |
Profit |
| selling |
INTEGER |
Sales |
| netProfit |
FLOAT |
Net profit |
| costPrice |
FLOAT |
Cost of goods |
| pagesPerSession |
FLOAT |
Pages per visit |
| costsPerVisit |
FLOAT |
Cost per visit |
| bouncesRate |
FLOAT |
Bounce rate |
Structure of the “cost” table (cost data)
|
|
|
| Field |
Type |
Name in the R7K12 interface |
| idProject |
INTEGER |
Project ID |
| nameProject |
STRING |
Project name |
| date |
DATE |
Visit date |
| groupSource |
STRING |
Source group name |
| source |
STRING |
Source name |
| source1 |
STRING |
Source (level 1) |
| source2 |
STRING |
Source (level 2) |
| source3 |
STRING |
Source (level 3) |
| source4 |
STRING |
Source (level 4) |
| source5 |
STRING |
Source (level 5) |
| source6 |
STRING |
Source (level 6) |
| source7 |
STRING |
Source (level 7) |
| sessions |
INTEGER |
Visits |
| cost |
FLOAT |
Cost (unattributed) |
| costAttr |
FLOAT |
Cost |
| appeals |
INTEGER |
Inquiries |
| application |
INTEGER |
Requests |
| leads |
INTEGER |
Deals |
| contacts |
INTEGER |
Contacts |
| revenue |
FLOAT |
Revenue |
| revenueCancel |
FLOAT |
Revenue from canceled requests |
| payment |
FLOAT |
Payment amount |
| paymentTotal |
INTEGER |
Number of payments |
| paymentCancel |
FLOAT |
Canceled payment amount |
| paymentCancelTotal |
INTEGER |
Number of canceled payments |
| paymentAccess |
FLOAT |
Confirmed payment amount |
| paymentAccessTotal |
INTEGER |
Number of confirmed payments |
| marginality |
FLOAT |
Margin |
| applicationCancel |
INTEGER |
Canceled requests |
| revenuePotential |
FLOAT |
Potential revenue |
| revenueForecast |
FLOAT |
Forecast revenue |
| profitForecast |
FLOAT |
Forecast profit |
| sellingForecast |
INTEGER |
Forecast sales |
| profit |
FLOAT |
Profit |
| selling |
INTEGER |
Sales |
| netProfit |
FLOAT |
Net profit |
| costPrice |
FLOAT |
Cost of goods |
| pagesPerSession |
FLOAT |
Pages per visit |
| shareAdvertisingCost |
FLOAT |
Ad spend share |
| cpoForecast |
FLOAT |
Forecast CPO |
| roiForecast |
FLOAT |
Forecast ROI |
| profitMedium |
FLOAT |
Average profit |
| cpc |
FLOAT |
CPC |
| costsPerVisit |
FLOAT |
Cost per visit |
| clicks |
INTEGER |
Clicks |
| impressions |
INTEGER |
Impressions |
Structure of the “deals” table (deal data)
|
|
|
| Field |
Type |
Name in the R7K12 interface |
| idProject |
INTEGER |
Project ID |
| nameProject |
STRING |
Project name |
| id |
STRING |
Visit ID |
| clientId |
STRING |
Client ID |
| gaClientId |
STRING |
Google Analytics client ID |
| ymClientId |
STRING |
Yandex.Metrica client ID |
| idUser |
STRING |
User ID |
| ip |
STRING |
Client IP address |
| visitNumber |
INTEGER |
Visit number |
| visitStartTime |
DATETIME |
Visit start date/time |
| visitEndTime |
DATETIME |
Visit end date/time |
| year |
INTEGER |
Year |
| month |
INTEGER |
Month |
| dayOfMonth |
INTEGER |
Day of month |
| dayOfWeek |
INTEGER |
Day of week |
| hour |
INTEGER |
Hour |
| minute |
INTEGER |
Minute |
| second |
INTEGER |
Second |
| date |
DATE |
Visit date |
| url |
STRING |
Page URL |
| host |
STRING |
Domain |
| path |
STRING |
Landing page |
| query |
STRING |
Query parameters |
| hash |
STRING |
Request hash |
| utm_source |
STRING |
Source (UTM) |
| utm_medium |
STRING |
Medium (UTM) |
| utm_campaign |
STRING |
Campaign (UTM) |
| utm_content |
STRING |
Content (UTM) |
| utm_term |
STRING |
Keyword (UTM) |
| isTrueUtm |
INTEGER |
Has UTM tags |
| groupSource |
STRING |
Source group name |
| source |
STRING |
Source name |
| source1 |
STRING |
Source (level 1) |
| source2 |
STRING |
Source (level 2) |
| source3 |
STRING |
Source (level 3) |
| source4 |
STRING |
Source (level 4) |
| source5 |
STRING |
Source (level 5) |
| source6 |
STRING |
Source (level 6) |
| source7 |
STRING |
Source (level 7) |
| gclId |
STRING |
Google AdWords ClickId |
| isTrueDirect |
INTEGER |
Direct visit |
| isTrueBot |
INTEGER |
Bot |
| isTrueOffline |
INTEGER |
Offline visit |
| deviceType |
STRING |
Device |
| browser |
STRING |
Browser |
| browserVersion |
STRING |
Browser version |
| operatingSystem |
STRING |
Operating system |
| operatingSystemVersion |
STRING |
Operating system version |
| city |
STRING |
City |
| region |
STRING |
Region |
| country |
STRING |
Country |
| idLead |
STRING |
Deal ID |
| type |
STRING |
Inquiry type |
| statusLead |
STRING |
Deal status |
| groupStatusLead |
STRING |
Deal status group |
| appealUrl |
STRING |
Inquiry page URL |
| appealHost |
STRING |
Inquiry page domain |
| appealPage |
STRING |
Inquiry page path |
| nameLead |
STRING |
Deal name |
| idManager |
STRING |
Manager ID |
| nameManager |
STRING |
Manager name |
| phoneContact |
STRING |
Contact phone |
| emailContact |
STRING |
Contact email |
| nameContact |
STRING |
Contact name |
| shop |
STRING |
Store |
| appeals |
INTEGER |
Inquiries |
| application |
INTEGER |
Requests |
| leads |
INTEGER |
Deals |
| contacts |
INTEGER |
Contacts |
| revenue |
FLOAT |
Revenue |
| revenueCancel |
FLOAT |
Revenue from canceled requests |
| payment |
FLOAT |
Payment amount |
| paymentTotal |
INTEGER |
Number of payments |
| paymentCancel |
FLOAT |
Canceled payment amount |
| paymentCancelTotal |
INTEGER |
Number of canceled payments |
| paymentAccess |
FLOAT |
Confirmed payment amount |
| paymentAccessTotal |
INTEGER |
Number of confirmed payments |
| marginality |
FLOAT |
Margin |
| applicationCancel |
INTEGER |
Canceled requests |
| revenuePotential |
FLOAT |
Potential revenue |
| revenueForecast |
FLOAT |
Forecast revenue |
| profitForecast |
FLOAT |
Forecast profit |
| sellingForecast |
INTEGER |
Forecast sales |
| profit |
FLOAT |
Profit |
| selling |
INTEGER |
Sales |
| costPrice |
FLOAT |
Cost of goods |
| shareAdvertisingCost |
FLOAT |
Ad spend share |
| profitMedium |
FLOAT |
Average profit |