Note: Beacon Data Warehouse is available as our Ultimate plan. If you'd like to explore upgrading to our Ultimate plan, please reach out to your Customer Success Manager or the support team at [email protected]
Tip: SQL Knowledge is highly recommended for implementing this solution. If you don't know SQL, there is a great free guide here.
How our solution works
Our solution works by using Google BigQuery, which is a Data Warehouse platform from Google. The benefits of this solution are:
Your queries are handled by Google infrastructure
You can set up advanced permissions in GCP (Access to individuals, Google Groups, Service Accounts...etc)
You can see your data and query it inside of Big Query
You can use the BigQuery API to access the data in other applications
There are BigQuery Connectors for multiple Business Intelligence (BI) solutions (such as PowerBI, Looker, Looker Studio, Metabase...etc)
You can push the data into other Data warehouses (like Azure Synapse)
You can use advanced Data Tools that allow you to look back through data changes as well
...And probably many more!
Before Access
Before you have access you'll need:
A Google Cloud Platform Account
A Project in that Account
Either an Email or a Google Group that has access to the Project
We recommend a Google Group so that you can add more users to the Project when needed.
Request Access
If you are on Ultimate, to gain access, just open our Chat Bot, share with us the Google Cloud Project you have set up and the Email/Google Group, and we should be able to enable it for you within 24 hours.
After Access
Once you have been granted access you will get a link of the form:
Now you should follow the steps below
If you haven't created a Dataset in your Project, you'll need to do so:
In BigQuery, click on the 3 dots in Project you want to add to and select "Create data set"
Give it any ID, but change it to "Region", and in the Region box, search for "London" (Which is also known as europe-west2)
Click Create
Expand the project we shared with you (beacon-bi-production)
Expand the Data set (which is called account_{YOUR_BEACON_ACCOUNT_ID}
For which Record Type, you'd like to be able to query, click on the 3 dots and select "Query"
You can then write a query to pull in all the fields (you can just use a * to do this but we recommend choosing exactly what you want), the specific fields, filter or combine Tables together.
Run your query to ensure it is working correctly
Click on the Save Button, and select "Save view"
Choose your own project, the dataset you created earlier and then select a name for the table (often just the name of the table/record type you are querying)
Click Save
You should now see it inside of your Project and run queries there if you wanted
If you're connecting to PowerBI Desktop, you can now go through the OAuth flow to see your data
Generating the correct columns (data) for your use cases
Although you can just add a "*" in your select statements, we don't recommend doing this, because:
You might be including data you don't not need (for instance PII information)
The format of the data might not be in a format that you need it in
For instance PowerBI doesn't do well with JSON data, which a lot of the format is in.
Easiest ways to create your select statements
BigQuery makes it quite easy to correct select queries, you can actually just click on the column name, and it will add that text into your query (make sure your cursor is between the words SELECT and FROM):
For more advanced objects (called Records), you might want to expand them to get at the content that you want by clicking on the chevron:
Dealing with multi-select fields
You will notice when you are selecting a column where there are multiple potential values, you will see something like this:
`to`[SAFE_OFFSET(0)].email
This means you are getting the email of the First Entry (Indexes start at 0 for the first one), from the list of Tos.
If you want to get all of these, either you can bring just the JSON array (if where you are using it supports JSON arrays in it), but a lot of the time you likely want to extract a selection of these, so you might end up with something like this:
`to`[SAFE_OFFSET(0)].email as email_1,
`to`[SAFE_OFFSET(1)].email as email_2,
`to`[SAFE_OFFSET(2)].email as email_3,
`to`[SAFE_OFFSET(3)].email as email_4,
`to`[SAFE_OFFSET(4)].email as email_5
Which would select the first 5 emails from the field, then if you also wanted to extract out whether it was primary you could do:
`to`[SAFE_OFFSET(0)].email as email_1,
`to`[SAFE_OFFSET(0)].is_primary as email_1_is_primary,
...etc
You could even write a more complex query where you extracted out just the primary field using more advanced SQL:
(SELECT email FROM UNNEST(`to`) WHERE is_primary = TRUE LIMIT 1) AS primary_email
That is the great power of a tool like BigQuery!
Dealing with Objects that seem simple
Some of the data in Beacon, such as single select Dropdowns, seem like they would output a String, however they aren't for multiple reasons (supporting the ability to make them multiple in the future, as such for Grant Type, you will likely see something like this:
[{v:'project'}]
A good way to get this into a string is to use the ARRAY_TO_STRING function, i.e.
ARRAY_TO_STRING(c_grant_type, 'v:') as grant_type
Example of People
Here is an example query to help you extract people into a useful table (feel free to remove/add using the suggestions above):
SELECT
person.name.full,
(SELECT email FROM UNNEST(person.emails) WHERE is_primary = TRUE LIMIT 1) AS primary_email,
person.address[SAFE_OFFSET(0)].address_line_one AS primary_address_line_one,
person.address[SAFE_OFFSET(0)].city AS primary_city,
person.address[SAFE_OFFSET(0)].country AS primary_country,
person.address[SAFE_OFFSET(0)].postal_code AS primary_postal_code,
person.address[SAFE_OFFSET(0)].latitude AS primary_latitude,
person.address[SAFE_OFFSET(0)].longitude AS primary_longitude,
person.linkedin_url,
person.facebook_url,
person.youtube_url,
person.website,
person.date_of_birth,
person.contact_preferences,
person.deceased
FROM
`beacon-bi-production.account_{CHANGE_THIS}.person` AS person;
Example of People with their Organisation Name
SELECT
person.name.full,
(SELECT email FROM UNNEST(person.emails) WHERE is_primary = TRUE LIMIT 1) AS primary_email,
person.address[SAFE_OFFSET(0)].address_line_one AS primary_address_line_one,
person.address[SAFE_OFFSET(0)].city AS primary_city,
person.address[SAFE_OFFSET(0)].country AS primary_country,
person.address[SAFE_OFFSET(0)].postal_code AS primary_postal_code,
person.address[SAFE_OFFSET(0)].latitude AS primary_latitude,
person.address[SAFE_OFFSET(0)].longitude AS primary_longitude,
person.linkedin_url,
person.facebook_url,
person.youtube_url,
person.website,
person.date_of_birth,
person.contact_preferences,
person.deceased, person.organization[SAFE_OFFSET(0)].entity_id as org_id,
org.name AS organization_name
FROM
`beacon-bi-production.account_26114.person` AS person
LEFT JOIN
`beacon-bi-production.account_26114.organization` AS org
ON
person.organization[SAFE_OFFSET(0)].entity_id = org.id;
Example of Payment with the Payee details
SELECT
payment.id,
payment.created_at,
payment.updated_at,
payment.amount.value AS payment_amount,
payment.amount.currency AS payment_currency,
payment.amount_net.value AS payment_net_amount,
payment.payment_date,
payment.name,
person.name.full AS payee_name,
payment.type,
payment.source,
payment.payment_method,
payment.notes,
payment.event[SAFE_OFFSET(0)].entity_id AS event_id,
payment.paid,
payment.reference,
payment.income_stream
FROM
`beacon-bi-production.account_26114.payment` AS payment
LEFT JOIN
UNNEST(payment.customer) AS customer
LEFT JOIN
`beacon-bi-production.account_26114.person` AS person
ON
customer.entity_id = person.id;
Connecting your data to Business Intelligence (BI) tools:
PowerBI: Microsoft has a great guide here.
Metabase: here.
Tableau: here.
Connecting to other Data Warehouses
Google has a great guide to using their connectors that facilitate this here.
Frequently Asked Questions
How realtime is the data?
The data is very close to Realtime, in fact, the data you gain access to is the exact same as when you filter or do an export! We are working on making this as realtime as possible and is a huge benefit of our system over others.
How does billing work?
Data Warehouse is available on Ultimate and has no fees from us. However, when you are running queries against your data, Google is charging you for the query processing (we take on the cost of hosting the data). This is generally a very small amount but we recommend you monitor this and consider optimising your queries where possible.
I am not on Ultimate, can I gain access to this?
This is only available on Ultimate, as such you will need to speak to your Customer Success Manager or use the in-app chat.
What happens if I make changes to the Beacon Records & Fields?
Adding new fields or options in dropdowns does not break things (but it will not show the field). However, if you remove a field all queries will then break.
In these cases, please file a request with support for a refresh. We will need the Google Group and Account ID to do this.