There are several methods of importing transaction data from your CRM to Google BigQuery:
- Uploading files in CSV or JSON format using BigQuery UI or a command line
- A set of Python scripts from OWOX, that let you automate data import to Google BigQuery using Google Cloud Functions.
- SDK for .NET, .Java, PHP, Python
- ODBC driver from CDATA
- ETL applications
- Upload data from Google Sheets using the OWOX BI BigQuery Reports add-on
We recommend using the first method and upload data via the BigQuery web UI. We'll describe this method step-by-step in this article.
Consider the encoding and delimiters Google BigQuery supports the encodings UTF-8 (by default) and ISO-8859-1. The supported delimiters are comma (by default) and tab.
More details in Google documentation.
The recommended table schema
For importing data on transactions, products, and customers from CRM, ERP, or any other accounting system to Google BigQuery, we recommend using this table schema. This will ensure all your data will be considered in OWOX BI.
Uploading data via Google BigQuery web interface
You can also upload data manually. To do that, make up a CSV table with your data and upload it using the BigQuery web interface.
- Open Google BigQuery web interface
- Create a new dataset in your Google BigQuery project. Select the project in the Resources section, then click Create dataset in the window to the right:
- Specify the Dataset ID and Data location. Note: the data location must be the same as the location of all the other data you want to blend in OWOX BI.
Then, click Create dataset:
- Create a new table: Click the 'plus' icon on the right to the dataset name:
- Select Create table from: Upload, then specify the file location and format (CSV), then type in the name for the new table:
- Make up the data schema in the Schema section. You can add fields one by one clicking Add Field and following this data schema, or simply click Edit as Text and paste the full schema in the JSON format (copy it from the snippet right after the screenshot below):
Copy the schema in JSON here:
7. Set Advanced options. Set Header rows to skip as 1 so the data would be imported with no errors. You can also specify the allowed amount of errors to avoid errors when the table is created:
After clicking Create Table, the table will be uploaded to BigQuery and you'll be able to use it as a data source in OWOX BI Attribution и Smart Data: