Marketing mix modeling (MMM) is a powerful tool that helps marketers understand the effectiveness of their marketing efforts and allocate resources more efficiently. However, one of the biggest challenges in implementing MMM is the time-consuming process of collecting, cleaning, and aggregating data from multiple sources. In this article, we will discuss how to automate marketing mix modeling using an automated MMM data feed and Two Minute Reports, a powerful data connector tool.
Step 1: Connect to Data Sources
To automate the data collection process, we will use Two Minute Reports, which is a Google Sheets extension that allows you to connect directly to your data sources. It is a fast, cost-effective alternative to other popular data connector tools like Supermetrics.
To connect to your data sources, follow these steps:
- Open Google Sheets and install the Two Minute Reports extension.
- Launch Two Minute Reports and click on “Data Sources.”
- Click “Add” and select the data source you want to connect to (e.g., Facebook Ads, Google Ads, Google Analytics, Shopify, etc.).
- Authenticate your data source by following the prompts.
Once you’ve connected your data sources, you’ll see a list of connectors that you can use to create queries and extract data.
Step 2: Create Queries and Extract Data
Now that you’ve connected your data sources, you need to create queries to extract the data you need for your marketing mix modeling project. For each data source, create a query that includes the following information:
- Name of the query
- Data source
- Name of the sheet where the data will be stored
- Metrics (e.g., amount spent, conversions)
- Dimensions (e.g., date)
For example, for a Facebook Ads data source, you might create a query that extracts the amount spent and conversions for each day.
Once you’ve created your queries, run them to extract the data and store it in your Google Sheets.
Step 3: Aggregate and Format Data
After extracting data from all your data sources, you need to aggregate and format it into a table format with columns representing features and rows representing dates. To do this, create pivot tables for each data source and use VLOOKUP functions to match data points based on dates.
For example, for a Google Analytics data source, you might create a pivot table with rows for dates, columns for mediums (e.g., organic, referral, email), and values for sessions.
Step 4: Create the Aggregated MMM Data Set
Once you’ve aggregated and formatted your data, create a new Google Sheet and copy the data from each data source into the appropriate columns. Use VLOOKUP functions to match data points based on dates and aggregate data from multiple sources into a single table.
Step 5: Schedule Daily Data Refreshes
To keep your MMM data up to date, schedule daily data refreshes using Two Minute Reports. To do this, follow these steps:
- Launch Two Minute Reports and click on “Schedule Refresh.”
- Click “New” and enter a name for your scheduled refresh.
- Select the queries you want to refresh daily.
- Set the refresh frequency to “Daily” and choose a time for the refresh to occur.
- Save your scheduled refresh.
Now, your data will be updated daily, allowing you to keep your marketing mix modeling project up to date with the latest information.
By automating the data collection and aggregation process with Two Minute Reports, you can significantly reduce the time and effort required to implement marketing mix modeling.
This streamlined approach allows you to focus on analyzing the results and making data-driven decisions to optimize your marketing efforts.