Move data into Google BigQuery

1. Decide what data you’ll need for your dashboards and analysis, such as data from CRM, back end or GA.

Look at the reporting that you want to automate: what data do you have there? Sales data from your CRM Form submits from your back end system. Sessions per source from your GA data.

2. Use a database schema tool or draw a schema of your data tables with table fields.

Think of what fields from these data sources you’ll need – for example, only 5 columns/fields from your CRM data: crmId, UserId, agency, date, revenue.

3. Open Google Cloud Console and create a new Cloud Project.

4. Create datasets on Google BigQuery - one dataset per data source that you previously identified.

Click on your project name in the lower left corner and click Create dataset on your right Choose a Name and Location where your data will be stored (you will not be able change it afterwards). Click on a Dataset and then click on Create table.

5. Import tables manually by uploading CSV or Avro files, or import data from Cloud Storage.

Give your table a name and use Schema Autodetection if possible. When uploaded, your table is ready for use. You can do your analysis on this table using SQL in BigQuery.

6. To automate the process, ask your developers to send CSV files to Cloud Storage that you can transfer into your BigQuery tables using Cloud Function.

Paid connectors such as Owox, Supermetrics, SegmentStream will connect your Google, Facebook, or Salesforce data to BigQuery.

7. To create your dashboard based on your BigQuery data, open Google Studio, select Create New Data Source > Add Data > BigQuery to import your table.