BigQuery | ASC Events | Custom Definitions Schema

[Bill Hoerr]

Hello,

I hope this is the right place to be posing this question.
I have started exporting GA4 into Google BigQuery natively. Looks like there is a default schema which does not allow for custom definitions to come through. Looking to add the custom definitions that most dealers are using with their ASC events (flow, flow_outcome, comm_type etc). I've tried modifying the table schema with no success. Has anyone been able to solve this and start collecting the custom definitions?

Thank you!

 

[LocalWerks]

Bill,

Have a look at this StackOverflow workaround:

Custom Definitions in Bigquery

I'm pretty new to Bigquery/Firebase/GA even SQL. (btw, if you have some good experience or recommendations where I can start learning, that would be great!) But I have main issue with Bigquery that...

stackoverflow.com

You should be able to access the custom dimensions this way. You can also access custom dimensions using the Explore feature in GA4

 

[Bill Hoerr]

Bill,

Have a look at this StackOverflow workaround:

Custom Definitions in Bigquery

I'm pretty new to Bigquery/Firebase/GA even SQL. (btw, if you have some good experience or recommendations where I can start learning, that would be great!) But I have main issue with Bigquery that...

stackoverflow.com

You should be able to access the custom dimensions this way. You can also access custom dimensions using the Explore feature in GA4

Thank you, I appreciate the link and the info here

 

[Mark A Hoffman]

Hey Bill - A bit late to this, but let me know if you didn't get the query you need. I'm routinely pulling down GA4 data from BiqQuery for using the ASC events and I can hook you up with whatever query you need.
If you are just looking to leverage the ASC event data, you might be better served by just writing queries directly around that. Pulling all of the ASC event data is a bit of a long query, but you can easily get all of the information that you need. For example:

WITH events AS
(
SELECT event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'event_owner') AS event_owner,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'event_action') AS event_action,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_id') AS item_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_number') AS item_number,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'item_year') AS item_year,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_make') AS item_make,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_model') AS item_model,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_variant') AS item_variant,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_condition') AS item_condition,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_color') AS item_color,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'item_fuel_type') AS item_fuel_type,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_type') AS page_type,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS url,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'department') AS department
.........
FROM
`YOUR DATASET ID`
WHERE event_date BETWEEN 'XX' AND 'XX' AND event_name LIKE 'asc_%'

This is part of a CTE that will UNNEST some of the various ASC parameters for any ASC event.

We've built a lot of tooling around ASC and BigQuery, so just let me know if you need any help with anything.

 

[Bill Hoerr]

Hey Bill - A bit late to this, but let me know if you didn't get the query you need. I'm routinely pulling down GA4 data from BiqQuery for usig the ASC events and I can hook you up with whatever query you need.

Thank you Mark! I just want to make sure those parameters are in the table. I'm using the native link between GA4 and BigQ. I'm not seeing the ASC event parameters looking in the tables. Did you ingest GA4 data a different way?

 

[Mark A Hoffman]

Hey Bill - Yes, the way that we're pulling ASC data is to just query it straight from the Events table in BiqQuery and unnest all the parameters.(We're using the native link between GA4 and BigQuery as well.)
I've not done anything with the custom definitions, because for us, we just pull the data from BigQuery into a Postgres database where we can run any queries we need on the ASC data. But I believe that someone would have to setup all of the custom definitions in GA4 beforehand. One of the reasons we go straight to BiqQuery is that you are limited to how many custom definitions you can have in GA4, so even if the ASC events are setup as a custom definition, it won't be all of them. That's why we wrote our own queries to pull down all of the ASC data, so we can just query it ad-hoc as needed.
Are you just needing to do ad-hoc queries on the ASC event data? If you can share a bit more about what you are trying to do, I can provide some queries to help you with it.

 

[Bill Hoerr]

Hey Bill - Yes, the way that we're pulling ASC data is to just query it straight from the Events table in BiqQuery and unnest all the parameters.(We're using the native link between GA4 and BigQuery as well.)
I've not done anything with the custom definitions, because for us, we just pull the data from BigQuery into a Postgres database where we can run any queries we need on the ASC data. But I believe that someone would have to setup all of the custom definitions in GA4 beforehand. One of the reasons we go straight to BiqQuery is that you are limited to how many custom definitions you can have in GA4, so even if the ASC events are setup as a custom definition, it won't be all of them. That's why we wrote our own queries to pull down all of the ASC data, so we can just query it ad-hoc as needed.
Are you just needing to do ad-hoc queries on the ASC event data? If you can share a bit more about what you are trying to do, I can provide some queries to help you with it.

I really appreciate the info on this. I have setup the custom definitions in GA4 beforehand, just did not see the parameters once in BigQ. I did not think to unnest. Yes, just want to run some ad-hoc queries. Currently setting up our BigQ projects for GA4 ingestion, however waiting on a few vendors to re-configure their events to fit ASC standards before pulling it down.

 

[Mark A Hoffman]

Glad to help! And when you start looking at ASC events, you will probably find some that aren't sending the right parameters, not firing off the right events, etc (Just the way it goes with new software). I built a tool that can help validate the ASC event data. You upload a CSV (based on BigQuery data), indicate what kind of vendor the data comes from and it will help identify potential ASC event issues.
You can see the tool here: Dealer Insights

There's a link on that page with some queries for BIgQuery to pull the data down as well. They might be useful to see how we're pulling everything.

 

[Ryan Everson]

GA4 SQL has been a lifesaver for easily creating GA4 BigQuery queries:

GA4 SQL: The Easy Way to Generate GA4 BigQuery Queries

GA4 BigQuery Query Builder allows you to generate queries quickly via user interface. Stop writing complex BigQuery queries by hand and try GA4 SQL today!

www.ga4sql.com

 

[Kabbalero]

Hey there,

I've run into a similar issue before. You can create a custom schema in BigQuery to match your GA4 custom definitions. Try creating a new table with your desired schema and using Dataflow or a similar ETL tool to transfer the data. It should work! Good luck!