Introduction
Hi, I am Akira, the editor-in-chief of Data Without Code. In our previous Use Case, we built an employee turnover prediction model using HR data to proactively save your best talent.
Now, let’s switch gears back to digital marketing. If you work in marketing or e-commerce, you are probably very familiar with Google Analytics (GA4). It tells you how many people visited your website, which pages they looked at, and where they came from.
But there is a massive problem with web analytics: it only tells you half the story. GA4 knows how a user behaved on your website, but it rarely knows who that user is or how much money they eventually spent with your sales team three months later.
That revenue data lives in your CRM (like Salesforce or HubSpot). If you analyze your web traffic and your sales data in isolation, you are flying blind. In this tutorial, I will show you how to blend Google Analytics data with your CRM data in KNIME Analytics Platform to reveal the complete customer journey.
Step 1: Connect to Google Analytics via API
Just like we learned when connecting KNIME to Google Sheets, we do not want to manually download CSV files from the GA4 dashboard every week. We want a live connection.
- First, make sure you have installed the KNIME Google Connectors extension.
- Drag the Google Authenticator node onto your canvas, double-click it, and log in to your Google account to establish a secure connection.
- Next, search for the Google Analytics Connector node. Connect the blue square output from the Authenticator to this new node.
Double-click the GA Connector to configure it. You can visually select which GA4 Property you want to pull data from, choose your Date Range, and select the exact Metrics (like “Sessions” or “Pageviews”) and Dimensions (like “Source/Medium” or a custom “User ID”) you need. Execute it, and your live web data instantly flows into KNIME!
Step 2: Pull Your CRM Sales Data
Now we need the other half of the story. How you get this data depends on your company’s IT setup.
If your CRM is connected to an internal database, you can use the SQL DB Connector and DB Reader nodes to pull the live sales data. Alternatively, if your sales ops team exports a weekly CSV, you can use the CSV Reader node.
Whatever method you choose, ensure your CRM dataset contains the final “Revenue” amount and a unique identifier (like a “User ID” or “Email”) that matches the data you captured in Google Analytics.
Step 3: Clean and Blend the Data (The Joiner Node)
This is where the magic happens. We need to stitch the web behavior to the actual dollar amount.
Before joining, use the String Manipulation node to make sure the User IDs in both datasets are perfectly formatted (e.g., lowercase and no hidden spaces). If they do not match exactly, the join will fail.
Now, drag the Joiner node onto your canvas. Connect your Google Analytics data to the top port and your CRM sales data to the bottom port. Double-click the Joiner, and set the matching column to “User ID” (or whatever unique key you track).
Use a Left Outer Join. This ensures you keep all your website visitors in the report, even if they haven’t bought anything yet.
Step 4: Analyze the Full Funnel
Execute the Joiner node. You now have a single, unified dataset that shows exactly which marketing channel (from Google Analytics) drove the actual closed revenue (from your CRM).
You can now route this blended data into a GroupBy node to calculate the true ROI of your marketing campaigns, or use it to build a visual dashboard component for your weekly marketing meeting.
Conclusion: Your Next Steps
Congratulations! You have just solved one of the biggest challenges in modern digital marketing. By breaking down the silos between your web team and your sales team, you can now optimize your ad spend based on actual profit, not just website clicks.
Speaking of optimization, keeping an eye on your own data is crucial, but what about your competitors? If your competitor drops their price by 20% today, how long will it take for your team to notice?
In our next Use Case tutorial, we will combine web scraping and dashboarding to build the ultimate competitive advantage: Price monitoring dashboard: Automating competitor analysis in KNIME!
