Learn Understanding Event-based Data | Event-based Data Modeling

Prerequisites

Understanding how users interact with digital products is vital for effective marketing analytics, and event-based data is at the core of this process. Event-based data consists of individual records that capture each discrete action a user takes, such as signing up, viewing a page, adding an item to a cart, or making a purchase. This approach is crucial for marketing analytics because it allows you to track and analyze every step of the user journey, providing detailed insights into behavior and campaign effectiveness.

Unlike traditional data models, which often aggregate user activity into summary tables or fixed columns, event-based models record every action as a separate row, enabling much greater flexibility and granularity in analysis.


              12345678910
            
CREATE TABLE marketing_interactions (
    interaction_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL
        REFERENCES users(user_id),
    channel VARCHAR(50) NOT NULL,              -- email, paid_search, social, referral
    interaction_type VARCHAR(50) NOT NULL,     -- impression, click, open, unsubscribe
    interaction_timestamp TIMESTAMP NOT NULL,
    campaign_id INTEGER
        REFERENCES campaigns(campaign_id)
);

By logging each user action as a separate event, event tables make it possible to analyze how users interact with marketing campaigns and digital platforms over time. This structure enables you to reconstruct user journeys, attribute conversions to specific campaigns, and answer questions such as which campaigns drive the most purchases or where users tend to drop off in a funnel. The events table's flexible design supports a wide range of queries, from simple counts of actions to complex analyses of user behavior across multiple campaigns.

Tables in Event-based Data Modeling

To analyze event-based data effectively, you will work with three main tables in this course: users, campaigns, and events. Each table plays a specific role in tracking user behavior and marketing campaign performance.

users: stores information about each user who interacts with your platform. Key columns: user_id (unique identifier), signup_date (date the user registered);
campaigns: contains details of your marketing campaigns. Key columns: campaign_id (unique identifier), campaign_name (name of the campaign), start_date, end_date;
events: logs every action users take, such as signing up, viewing a page, or making a purchase. Key columns: event_id (unique identifier), user_id (links to the user who performed the action), event_type (type of action), event_timestamp (when the event occurred), campaign_id (links to the associated campaign).

These tables are connected by foreign keys: events.user_id references users.user_id, and events.campaign_id references campaigns.campaign_id. This structure allows you to link user actions to specific users and campaigns, supporting detailed marketing analytics and user journey analysis.

Task

Swipe to start coding

Write a SQL query that returns the number of 'purchase' events for each campaign in the events table. Your query should:

Select the campaign_id and the count of events where event_type is 'purchase';
Name the count column as purchase_count;
Group the results by campaign_id.

Do not include campaigns with zero purchases in your result.

Solution

Everything was clear?

Thanks for your feedback!

Section 1. Chapter 1

single

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Swipe to show menu