With these ROI reports clients can easily see where their marketing investment does.
ROI Marketing Analysis: SaturnOne vs With Google Tools
The below images are the real reports agency clients actually want. With these ROI reports clients can easily see where their marketing investment is working and where it is not. They can see the real-world value (revenue and ROI) your agency is generating for them and where to invest more.
In this article, I will show you how we do this for your agency clients with SaturnOne's advanced tool suite and services. Then compare this with the free Google marketing and tech stack needed to accomplish similar results.
In short, with SaturnOne’s single tool, we can generate this data and reports in hours for you. Or you can use Google Tools and spend 100 or more hours in training and up to 50 hours just to set this up with a typical client. So you can now provide your clients with this immense value like never before possible.
It is easy to see why this type of reporting is usually left to the big agencies and only provided to big clients. See, “Why Small Content or Inbound Marketing Agencies Should Harness Analytics for Competitive Growth and Profit” to get some idea of the competitive advantage and value you could generate for your clients and yourselves.
First I will show you how to do this with SaturnOne short and sweet. Then 3000+ words just outlining how to perform channel and campaign conversion and ROI analysis like in the tables. It takes 5 Google tools and we estimate with AI’s help that it would take about 100 hours to learn the setup and tech skills, assuming you are highly technical), and then about 50 hours per client to set up for the first time.
With SaturnOne's integrated analytics suite and our pro services it takes approximately 1 hour to generate such a report.
SaturnOne all-in-one tool replaces the 5 Google tools and the … well… approximately 100-step process needed as described below in detail. The time is about 50 on your average content marketing site and typical conversion paths. The difference is as much as 50 to 1!! On top of this we offer this a a full service option with our expert team.
First, I will outline this for you at a top level…after all, there are 5 tools, skills, and then set up. Following that we expand more and more details of this outline…about 3,500 words just outlined. Yes, this is why this is usually the realm of big agencies with teams or specialized consultants.
To perform multi-page channel and campaign conversion analysis using Google tools (Google Analytics, Tag Manager, Google Data Studio, BigQuery, Sheets), you can follow these steps:
By leveraging the integration between Google Analytics, BigQuery, and Google Data Studio, you can perform an in-depth analysis of multi-page channel and campaign conversions. BigQuery provides the flexibility to query and manipulate the data, while Google Data Studio offers visualization capabilities to present the insights in a visually appealing and interactive manner. This combination allows you to gain a comprehensive understanding of the performance of different channels and campaigns throughout the customer journey.
To track lead generation for specific and all forms, including popup forms, third-party forms, landing pages, and funnels, as well as conversion from a Calendly meeting scheduled, you would need to follow these steps:
1. Set up Goal Tracking in Google Analytics:
a. Define your lead generation goals, such as form submissions or scheduled meetings.
b. Create goal definitions in Google Analytics to track these specific actions as conversions.
c. Assign a value to each goal if you want to measure their monetary impact.
2. Implement Event Tracking:
a. Add event tracking code to your website pages or form elements to capture specific user interactions, such as form submissions or meeting scheduled.
b. Use the Google Analytics Event Tracking API to send event data to your Google Analytics account.
3. Track Popup Forms:
a. Add event tracking code to your popup forms to capture form submissions as events. This will require Google Tag Manager and possibly custom coding.
b. Customize the event tracking code to include relevant information like the form name or identifier.
4. Track Third-Party Forms:
a. If you have third-party forms on your website, integrate them with Google Analytics by following the form provider's instructions.
b. Typically, this involves adding additional tracking scripts or configuring settings within the form provider's dashboard to send conversion data to Google Analytics.
5. Track Landing Pages:
a. Ensure that your landing pages are properly tagged with appropriate campaign parameters (utm_source, utm_medium, utm_campaign) to track their performance.
b. Use URL Builder or a similar tool to generate tagged URLs for your landing page links.
6. Track Funnels:
a. Set up funnels in Google Analytics to track the step-by-step conversion process for specific user flows.
b. Define the necessary steps in your funnel, such as landing page views, form submissions, or scheduled meetings.
Regarding the time required to learn these skills and execute them on a website with 20 marketing pages and 200 article pages, it can vary based on your familiarity with Google Analytics and the technical complexity of your website. However, here are some estimated time frames:
Learning Time:
- Understanding the basics of Google Analytics and goal tracking: 2-4 hours.
- Learning event tracking and implementing it on different form types: 4-6 hours.
- Familiarizing yourself with tracking popup forms and third-party forms: 2-4 hours.
- Setting up and configuring funnels: 2-4 hours.
Execution Time:
- Tracking lead generation for 20 marketing pages: Approximately 8-12 hours, depending on the complexity of the pages and the level of customization required.
- Tracking lead generation for 200 article pages: Assuming similar implementation requirements for each page, approximately 80-100 hours. However, this time estimate can vary significantly based on the specific needs and technical complexity of your website.
These time estimates are provided as general guidelines and can vary based on individual factors such as prior experience, technical proficiency, and the specific requirements of your website.
To connect Google Analytics with BigQuery and export raw data for further analysis, follow these steps:
1. Set up a BigQuery Project:
a. Create a project in Google Cloud Console if you haven't already.
b. Enable the BigQuery API for the project.
2. Enable BigQuery Export in Google Analytics:
a. Sign in to your Google Analytics account and navigate to the Admin section.
b. In the Property column, click on "Data Streams" under the "Data Settings" section.
c. Click on the "+ Add Stream" button and select "Web" as the source.
d. Configure the stream settings and enable the BigQuery export option.
e. Choose your BigQuery project and dataset where you want to export the data.
3. Configure Export Schema:
a. Define the schema for the exported data, specifying the dimensions and metrics you want to include.
b. You can choose to include default dimensions and metrics or create custom ones based on your requirements.
c. Configure the frequency of data export, such as daily or hourly.
4. Grant Permissions to BigQuery:
a. Ensure that the service account associated with your BigQuery project has the necessary permissions to access Google Analytics data.
b. Grant the service account appropriate roles, such as BigQuery Data Viewer or BigQuery Admin, to read and query the exported data.
5. Access Data in BigQuery:
a. Once the export is set up and running, the Google Analytics data will be available in your BigQuery project and dataset.
b. Use SQL queries to analyze the raw data in BigQuery, combining it with other datasets, performing complex analysis, and creating custom reports.
The time required to set up and configure the connection between Google Analytics and BigQuery depends on factors such as your familiarity with the tools and the complexity of your Google Analytics setup. Here's a rough time estimate:
Learning Time:
- Understanding BigQuery and its integration with Google Analytics: 2-4 hours.
- Familiarizing yourself with data export settings and configuration in Google Analytics: 1-2 hours.
- Learning SQL queries for data analysis in BigQuery: 2-4 hours.
Execution Time:
- Enabling BigQuery export in Google Analytics and configuring the export settings: 1-2 hours.
- Granting permissions to the BigQuery service account: 0.5-1 hour.
- Accessing and querying data in BigQuery: The time required depends on the complexity of the analysis and queries you want to perform.
Overall, the estimated time to connect Google Analytics with BigQuery and export raw data would range from approximately 7 to 13 hours. However, please note that these are rough estimates, and the actual time required may vary based on individual factors and specific setup requirements.13
Creating custom BigQuery tables by extracting relevant data from the Google Analytics dataset involves the following steps:
1. Understand the Data Structure:
a. Familiarize yourself with the Google Analytics data schema in BigQuery. This includes understanding the available tables, fields, and their relationships.
b. Explore the data to identify the specific dimensions, metrics, and other data points you need for channel and campaign-specific metrics.
2. Plan Table Structure and Schema:
a. Determine the structure of your custom table, including the desired dimensions and metrics to focus on for channel and campaign analysis.
b. Decide on the appropriate data types for each field in the table schema based on the nature of the data.
3. Create a New Table:
a. Use SQL queries to create a new table in BigQuery with the desired schema.
b. Specify the necessary fields, data types, and any additional configurations such as partitioning or clustering.
4. Extract and Transform Data:
a. Use SQL queries to extract the relevant data from the Google Analytics dataset based on your chosen dimensions, metrics, and filters.
b. Apply transformations and aggregations as needed to derive channel and campaign-specific metrics.
c. Use functions like GROUP BY, JOIN, and WHERE clauses to filter, aggregate, and combine the data appropriately.
5. Load Data into Custom Table:
a. Execute the SQL query to extract and transform the data.
b. Load the query results into your newly created custom table in BigQuery.
6. Validate and Test:
a. Review the data in the custom table to ensure it aligns with your expected results.
b. Perform queries on the custom table to verify the accuracy of the channel and campaign-specific metrics.
The time required to create custom BigQuery tables and extract relevant data depends on factors such as the complexity of the analysis, the number of dimensions and metrics involved, and your familiarity with SQL and the Google Analytics data schema. Here's a rough time estimate:
Learning Time:
- Understanding the Google Analytics data schema in BigQuery: 2-4 hours.
- Familiarizing yourself with SQL queries for data extraction, transformation, and aggregation: 4-6 hours.
Execution Time:
- Planning the table structure and schema: 1-2 hours.
- Writing SQL queries to extract and transform data: 4-8 hours.
- Creating the custom table in BigQuery and loading the data: 1-2 hours.
- Validating and testing the custom table and metrics: 1-2 hours.
Overall, the estimated time to create custom BigQuery tables and extract relevant data for channel and campaign-specific metrics would range from approximately 13 to 26 hours. Please note that these are rough estimates and can vary based on the complexity of your analysis requirements and your experience with SQL and Google Analytics data in BigQuery.
Performing channel attribution analysis using BigQuery involves the following steps:
1. Understand Attribution Models:
a. Familiarize yourself with different attribution models commonly used, such as first-touch, last-touch, linear, time decay, or custom models.
b. Understand the pros and cons of each model and their impact on assigning credit to different channels in the conversion path.
2. Extract Relevant Data:
a. Identify the relevant data from your custom BigQuery tables that will be used for channel attribution analysis.
b. Determine the necessary dimensions and metrics, such as channel, conversion events, timestamps, and any additional data points required for the attribution analysis.
3. Apply Attribution Models:
a. Write SQL queries in BigQuery to apply the selected attribution models.
b. Use appropriate functions and calculations to allocate credit to channels based on the attribution model logic.
4. Evaluate Attribution Results:
a. Execute the SQL queries to calculate the attribution values for each channel and conversion path.
b. Analyze the attribution results to understand the distribution of credit across different channels.
c. Evaluate the impact of different attribution models on channel performance and decision-making.
5. Visualize and Interpret Results:
a. Use data visualization tools like Google Data Studio or other BI tools to create visual representations of the attribution analysis results.
b. Create charts, graphs, or dashboards that effectively communicate the channel attribution insights.
c. Interpret the visualized results to gain actionable insights for optimizing marketing strategies and budget allocation.
The time required to perform channel attribution analysis using BigQuery depends on factors such as the complexity of your attribution models, the volume of data, and your familiarity with SQL and attribution analysis concepts. Here's a rough time estimate:
Learning Time:
- Understanding different attribution models: 2-4 hours.
- Familiarizing yourself with SQL queries for attribution analysis: 4-6 hours.
Execution Time:
- Extracting relevant data from custom BigQuery tables: 1-2 hours.
- Writing SQL queries to apply attribution models: 4-8 hours.
- Evaluating and interpreting attribution results: 2-4 hours.
- Visualizing attribution analysis using data visualization tools: 2-4 hours.
Overall, the estimated time to perform channel attribution analysis using BigQuery ranges from approximately 15 to 28 hours. Please note that these are rough estimates, and the actual time required may vary based on the complexity of your analysis, the size of your dataset, and your familiarity with SQL and attribution modeling concepts.
Analyzing campaign performance using BigQuery involves the following steps:
1. Identify Key Campaign Parameters:
a. Determine the campaign parameters that you want to analyze, such as utm_source, utm_medium, utm_campaign, or any other custom campaign parameters you have set up in your tracking.
b. Understand the meaning and purpose of each campaign parameter and how they relate to your marketing campaigns.
2. Extract Relevant Data:
a. Identify the necessary data from your custom BigQuery tables that contain campaign-related parameters and metrics.
b. Determine the dimensions and metrics you need for campaign performance analysis, such as conversions, conversion rate, revenue, sessions, or any other relevant metrics based on your goals.
3. Write SQL Queries for Campaign Analysis:
a. Use SQL queries in BigQuery to extract and filter the data based on the campaign parameters.
b. Apply aggregations and calculations to calculate campaign-specific metrics and analyze performance.
4. Perform Campaign Analysis:
a. Execute the SQL queries to retrieve the campaign-specific data and metrics.
b. Analyze the data to understand the performance of each campaign parameter, such as comparing different utm_sources, utm_mediums, or utm_campaigns.
c. Calculate and evaluate key metrics like conversions, conversion rate, revenue, sessions, or any other metrics relevant to your campaign goals.
5. Create Visualizations and Reports:
a. Use data visualization tools like Google Data Studio, Tableau, or other BI tools to create visual representations of campaign performance.
b. Generate charts, graphs, or dashboards that provide a clear and concise overview of campaign performance.
c. Customize the visualizations to highlight important metrics and trends, allowing for easy interpretation and decision-making.
The time required to analyze campaign performance using BigQuery depends on factors such as the complexity of your analysis, the volume of data, and your familiarity with SQL and campaign tracking concepts. Here's a rough time estimate:
Learning Time:
- Understanding campaign parameters and their significance: 1-2 hours.
- Familiarizing yourself with SQL queries for campaign analysis: 2-4 hours.
Execution Time:
- Extracting relevant data from custom BigQuery tables: 1-2 hours.
- Writing SQL queries for campaign analysis: 2-6 hours.
- Performing campaign analysis and calculating metrics: 2-4 hours.
- Creating visualizations and reports: 2-4 hours.
Overall, the estimated time to analyze campaign performance using BigQuery ranges from approximately 10 to 22 hours. Please note that these are rough estimates, and the actual time required may vary based on the complexity of your analysis, the size of your dataset, and your familiarity with SQL and campaign tracking concepts.
Building custom reports in Google Looker (Data) Studio using data from BigQuery involves the following steps:
1. Connect Google Data Studio to BigQuery:
a. Sign in to Google Data Studio using your Google account.
b. Create a new report or open an existing one.
c. Connect the report to your BigQuery dataset by selecting BigQuery as the data source and providing the necessary credentials and permissions.
2. Define Report Objectives and Metrics:
a. Determine the objectives of your custom report, such as visualizing multi-page channel and campaign conversion analytics.
b. Identify the key metrics and dimensions that will be included in the report, such as conversions, conversion rate, revenue, channels, campaigns, etc.
3. Create Data Source in Google Data Studio:
a. Define a data source within Google Data Studio that connects to your BigQuery dataset.
b. Specify the tables and fields from BigQuery that you want to include in the report.
4. Design Visualizations and Layout:
a. Use the drag-and-drop interface of Google Data Studio to design visualizations, charts, and tables.
b. Select the appropriate visualization types, such as line charts, bar charts, pie charts, or tables, based on the data and metrics you want to present.
c. Arrange the visualizations and layout of your report to ensure a clear and intuitive presentation of the data.
5. Apply Filters and Segmentation:
a. Use filters and segmentation options in Google Data Studio to refine the data displayed in your custom report.
b. Apply filters based on channel, campaign, date range, or any other relevant dimensions to focus on specific subsets of data.
6. Customize and Format the Report:
a. Customize the appearance and formatting of your report to align with your branding or desired style.
b. Adjust colors, fonts, and layout to enhance the visual appeal and readability of the report.
7. Share and Collaborate:
a. Share the custom report with relevant stakeholders or team members for collaboration or review.
b. Set up appropriate access permissions to ensure data security and privacy.
The time required to build custom reports in Google Data Studio using BigQuery data depends on factors such as the complexity of the report design, the number of visualizations, and your familiarity with Google Data Studio. Here's a rough time estimate:
Learning Time:
- Familiarize yourself with Google Data Studio's interface and features: 1-2 hours.
- Understanding how to connect Google Data Studio to BigQuery: 1-2 hours.
Execution Time:
- Defining report objectives and metrics: 1-2 hours.
- Creating the data source and selecting tables/fields: 1-2 hours.
- Designing visualizations and layout: 4-8 hours.
- Applying filters and segmentation: 1-2 hours.
- Customizing and formatting the report: 1-2 hours.
- Sharing and collaborating on the report: 0.5-1 hour.
Overall, the estimated time to build custom reports in Google Data Studio using BigQuery ranges from approximately 10 to 20 hours. Please note that these are rough estimates, and the actual time required may vary based on the complexity of your report design, the amount of data you need to visualize, and your familiarity with Google Data Studio.
Monitoring and iterating on your marketing strategies using reports and dashboards in Google Data Studio involves the following steps:
1. Regularly Review Reports and Dashboards:
a. Set a schedule for reviewing your reports and dashboards, such as weekly, monthly, or quarterly.
b. Open your reports in Google Data Studio to access the latest data.
2. Analyze Key Metrics and Performance:
a. Identify the key metrics that indicate the performance of different channels and campaigns, such as conversions, conversion rate, revenue, or any other relevant metrics based on your goals.
b. Analyze the trends, patterns, and changes in these metrics to gain insights into the effectiveness of your marketing efforts.
3. Identify Areas for Improvement:
a. Look for areas where performance can be optimized or improved based on the data analysis.
b. Identify channels or campaigns that are underperforming or not meeting your goals.
c. Identify successful channels or campaigns that can be further capitalized on.
4. Implement Changes and Adjustments:
a. Based on the insights gained from the data analysis, develop strategies for improving the performance of underperforming channels or campaigns.
b. Implement changes to your marketing tactics, such as adjusting ad spend, modifying messaging, targeting specific audiences, or exploring new channels.
c. Monitor the impact of these changes over time.
5. Track and Measure Results:
a. Monitor the performance of the updated channels or campaigns using the same key metrics identified earlier.
b. Compare the performance before and after implementing changes to assess the effectiveness of your optimizations.
6. Iterate and Refine:
a. Continuously iterate on your marketing strategies based on the results and insights gained from the monitoring and analysis.
b. Refine your approaches, test new ideas, and make data-driven decisions to improve conversions and ROI.
The time required for monitoring and iterating on your marketing strategies using reports and dashboards in Google Data Studio depends on factors such as the complexity of your marketing campaigns, the frequency of data analysis, and the extent of changes you need to make. Here's a rough time estimate:
- Weekly/Monthly Review: 1-2 hours per review session.
- Data Analysis and Insights: 2-4 hours per review session.
- Strategy Development and Implementation: 4-8 hours per iteration.
- Tracking and Measurement: 1-2 hours per review session.
- Iteration and Refinement: Ongoing process with variable time requirements.
Overall, the estimated time for monitoring and iterating on your marketing strategies using reports and dashboards in Google Data Studio can range from approximately 8 to 16 hours per iteration/review cycle. Please note that these are rough estimates, and the actual time required may vary based on the complexity of your marketing campaigns, the size of your dataset, and the extent of changes you need to implement.