Mixpanel captures frontend click events and user actions, then Fivetran moves that data into BigQuery for deeper analysis.
2
dbt stores SQL transformations as shared, dependent models that turn event data into user-level features and create a repeatable pipeline.
3
BigQuery ML can train a logistic regression model that predicts whether a user will click an apply button, while dbt can track the model workflow and audit data.
Summary
Jeff Katz demonstrates an end-to-end analytics and machine learning workflow built around Mixpanel, Fivetran, BigQuery, dbt, and BigQuery ML. A website sends page views and click events to Mixpanel, including properties such as the lesson a user selected. Fivetran connects Mixpanel to BigQuery and creates tables from the source data. Jeff then uses SQL and dbt to group events by user, count interactions, create feature columns, and define dependencies between models. The resulting data feeds a BigQuery ML logistic regression model that predicts whether someone will click an application button. Jeff is open about the demonstration model's limits, since it has only a few features and little time was spent designing it. He also explains how dbt's model package can parameterize training and predictions, while audit tables record model costs and training history. Schedulers in Fivetran and dbt can run ingestion and transformations at different frequencies.
Mixpanel turns website actions into queryable events
Jeff starts with click events and page views from his website. Mixpanel lets him see how users move through a funnel and which actions happen before someone clicks the application button. The JavaScript quick start loads the Mixpanel library on every page through a shared application HTML file. Calling mixpanel.track sends an event by name, and event properties can add details. For curriculum links, Jeff selects anchor tags, detects which one received a click, reads its lesson text, and sends that information with the event. Mixpanel also provides a distinct ID so he can connect actions from the same user.
Jeff uses Fivetran as the extraction and loading step between Mixpanel and BigQuery. Fivetran provides connectors for third-party services and destinations such as BigQuery, Snowflake, and Redshift. Setting up the Mixpanel connector requires a destination schema and the Mixpanel API secret. The connector can create tables from the source JSON and provides an entity relationship diagram for the resulting schema. Jeff shows an event table containing a Fivetran ID, a distinct user ID, the event name, and the event time. He also explains that Mixpanel can rate-limit exports, so a complete sync may take time.
The raw event table is too detailed for the model, so Jeff groups rows by distinct ID. He creates separate columns for events such as visiting the homepage or clicking free curriculum, then counts how often each event occurs for a user. The apply action is treated differently. Jeff uses a maximum rather than a count because he only needs to know whether the user clicked apply at least once. The resulting table contains user-level features and a label. He excludes event types he does not trust and filters toward the events that match his tracking setup.
Jeff keeps the SQL in dbt rather than on an individual computer. A repository lets team members work from the same code, while separate files make long queries easier to manage. dbt also records dependencies between models. In his example, the user-events model must exist before the training and test data, model training, and predictions can run. dbt connects to BigQuery, can use a GitHub or GitLab repository, and creates a model from the SQL file name. Running a model generates a stored view in a schema associated with the dbt user.
Once the feature table exists, Jeff uses BigQuery ML to create a logistic regression model. The model receives columns such as homepage activity, free-curriculum activity, curriculum clicks, and a label indicating whether the user clicked apply. He explains that the input columns are separated from the target column being predicted. BigQuery ML handles the training command inside the warehouse. Jeff warns that this particular model will not be very good because it has only a few features and was assembled quickly. Predictions return a likelihood for the positive and negative outcomes.
dbt can manage model training and prediction steps
Jeff shows a dbt package for BigQuery ML that puts model creation and prediction into the same workflow as the SQL transformations. The configuration specifies the model type, input columns, label columns, hyperparameters, and source data. This keeps the steps for preparing data, training a model, and making predictions together in the repository. The package also creates an audit table. Jeff says the table stores information such as model cost and training history, allowing changes in the data or model to be reviewed over time.
Ingestion and transformation can run on separate schedules
Fivetran and dbt each have schedulers. Fivetran can control how often source data is pulled into the warehouse, while dbt can run the models on its own schedule. Jeff gives the example of syncing data daily while training a model four times a day. The two schedules do not need to match, so data loading and model work can be adjusted independently.
A customer data platform is an optional extra layer
Jeff mentions Segment and RudderStack as customer data platforms that could sit before Mixpanel. Instead of sending events directly from the website to Mixpanel, a company could route them through one of these platforms and send the same data to Mixpanel, Google Analytics, or other analytics systems. His demonstration does not require that extra component. The rest of the workflow still covers event collection, warehouse loading, transformations, and model training.
