When enabled, Confection's BigQuery integration creates a dataset with two columns:
data column includes JSON-formatted data associated with a UUID's site activity. To connect BigQuery to your Confection account, see this quick-start guide.
Use the following query to output a converison rate for a particular event. You can return results for custom event names or you can use default Confection events like
Calculating Conversion Rate
There are different schools of thought on how to best calculate conversion rate. Some people advocate using pageviews as the denominator. Some advocate using sessions. Others advocate for using users. This query uses the following formula:
(total times an event fired / total number of UUIDs)*100 Our thinking follows:
"Sessions" are abstract, highly arbitrary, and highly variable timeframes. We can say, for example, one session contains the actions performed by a single user within X minutes. However, how can we define that set of minutes in any meaningful way? One person's session may last a few minutes. Another's may last a few days. A session is a mindset as much as anything else.
Unless you're trying to get users to perform an action with the least number of interactions, using pageviews as a denominator is similarly problematic. Most of the time we want people to explore our web properties deeply so the question isn't so much, "How quickly can I get someone to perform an action" as it is, "Of the people who could have performed an action, how many eventually did?"
The underlying intent of a conversion rate involves answering an all-important question: "Regardless of how much time users spent on my site, regardless of how many interactions users had across how much time, regardless of how deeply users explored my content, what percent of users eventually performed a certain action (numerator) out of the total number of people who could have (denominator)?"