Steps to Stitch E-Commerce Data on Google Sheet

Piyush Gupta
Reading Time: 6 minutes
Data comimg from different platform and getting blend in EasyInsights

What is Data Stitching?

Today, data is the nucleus of all business decisions. Deriving meaningful information, insights from varied data sets help a business take actions based on concrete know-how about their customers.

In a digital world, your customers are everywhere and so is the data trail. Data stitching is the process of bringing data from different platforms, devices, and sources to a commonplace. The e-commerce data thus can be consolidated into a uniform template for easy consumption and analysis.

Click here to learn about “Data Stitching Explained: 3 Powerful Use Cases of Stitching Data at the User-Level”.

ETL - Extraction, Collection, Transformation and Load of Data using EasyInsights
Grouping and cleaning data from multiple data sources

Data stitching helps marketing teams understand their customers’ persona, needs and engagement patterns better. With these tools, marketers can design meaningful campaigns based on solid insights and not just assumptions or theories.

Data stitching saves time and effort in combing through multiple data sets that lead to half-baked conclusions and ineffective marketing strategies.

How multiple data sources can be stitched on common columns?

There are two ways to do the same.

Method 1 – Let us look at steps to Get Raw Data from the individual analytics panels:

Step 1: First map the desired column with available e-commerce data sources, for example:

Raw Data from the individual analytics panels
Representation of the base & derived columns mapped to data sources.

 Phase 1.1 – Fetching Raw data from Facebook Ads panel.

  • Step 2: Open Reports → Create a custom report. 
  • Step 3: Select Campaign name and Amount Spent from the data panel.
Fetching Raw data from Facebook Ads panel.
Custom column selection interface – Facebook Ads manager
  • Step 4 Select the date range.
Select the date range in the Facebook platform interface
Selecting the date range from Facebook Ads manager panel
  • Step 5: Export as a .xls file.
Exporting data from the Facebook platform
Selecting the download format from Facebook Ads manager panel
  • Step 6: Keep the downloaded raw data in a separate Google sheet tab.
downloaded raw data in a separate Google sheet tab
Seperate tab for Facebook Ads raw data

Phase 1.2 – Fetching Raw data from Google Ads panel

  • Step 7: Open Report → Predefined reports → Basic → Campaign.
  • Step 8: You may unselect irrelevant columns while keeping Campaign and Cost columns from the interface.
  • Step 9: Select the date range.
Fetching Raw data from Google Ads panel
Google Ads reporting panel – Column and date selection
  • Step 10: Download data to Google sheets and keep it in the second tab.
Download data to Google sheets from Google ads
Google Ads reporting panel – Download to Google Sheets
Downloaded Google Ads raw data in a separate sheet
Downloaded Google Ads raw data in a separate sheet

Phase 1.3 – Getting Raw data from Shopify panel 

  • Step 11: From the reports interface, select the following columns: UTM Campaign name, Orders, Total Sales, Returns.
Selecting the columns from Shopify panel
Selecting the columns from Shopify panel
  • Step 12: Select the date range.
Shopify Custom report interface desired columns 1
  • Step 13: Export as .xls file and keep the data on the third tab of the Google sheet.
Selecting the download format from Shopify panel
Selecting the download format from Shopify panel
Downloaded Shopify raw data in the third sheet
Downloaded Shopify raw data in the third sheet

Phase 2 – How to Clean and Group data 

  • Step 14: Acquisitions mean when the column Customer type = first-time. So to get #Acquisitions column:
    – for First-time customers in customer_type, Acquisition value = #Orders, and
    – for Returning customers in customer_type, Acquisition value = Zero.
Shopify data cleaning
Shopify data cleaning
  • Step 15: Group the data for Acquisitions, Orders, Net Sales and Returns on the basis of utm_campaign name + utm campaign source using ()PIVOT
Group Shopify cleaned data using PIVOT table
Group Shopify cleaned data using PIVOT table
Shopify final sheet ready for stitching
Shopify final sheet ready for stitching

Phase 3 – Stitch data on a common column 

We need to stitch the data on the common column – Campaign. To understand data stitching further, please refer to this article.

  • Step 16: Append Facebook Ads and Google Ads data.
Append Facebook Ads and Google Ads data
Append Facebook Ads and Google Ads data

The ‘stitch data’ tab is where you put the last piece of the puzzle. When you’re done enriching and transforming the data, you can present them in a separate tab where it’s easier to monitor.

  • Step 17: Copy Shopify final data and use ()VLOOKUP to perform data stitching
Final step of data stitching using VLOOKUP function
Final step of data stitching using VLOOKUP function

Method 2 – Google sheet add-on

Create a new dimension “All campaigns” for Facebook Ads.

  • Step 1: Launch EasyInsights Extension.
Launching EasyInsights from Google sheets add-ons
Launching EasyInsights from Google sheets add-ons

 Phase 1 – Create cross-platform dimension

 We need the common dimension “All campaigns ” to join 3 data sources.

  • Step 2: Create a new dimension “All campaigns ” for Facebook Ads with the base dimension as “Campaign ” without any mapping.
Create a new dimension "All campaigns" for Facebook Ads
Create a new dimension “All campaigns” for Facebook Ads
  • Step 3: Add Google Ads account and Shopify account to the “All campaigns ” dimension with the base dimension as “Campaign ” without any mapping.
Adding Google Ads account and Shopify account to to dimension "All campaigns" dimension
Adding Google Ads account and Shopify account to to dimension “All campaigns” dimension
  • Now, “All campaigns ” have been successfully created as the common dimension, i.e. the cross-platform dimension across Facebook Ads, Google Ads and Shopify. 
"All campaigns" as the cross-platform dimension
“All campaigns” as the cross-platform dimension

Phase 2 – Include all the base metrics 

  • Step 5: Go to Analytic metrics from the hamburger menu and define the first analytic metric “Acquisitions ” as “Orders ” when (in Filter) Customer type = first-time. Similarly define other metrics, Total Sales and Returns as the same without any filter.
Interface to define cross platform Analytics metrics
Interface to define cross platform Analytics metrics

Phase 3 – Getting the final report view

  • Step 6: Move to Cross-platform section then choose “All Datasources” and select these columns: All Campaigns, Spend, Acquisitions, Total Sales and Returns.
Columns in the EasyInsights dropdown
Columns in the dropdown
  • Step 7: Create derived columns on the sheet as: Revenue = Total Sales – Returns. ROAS = Revenue/Spend, CAC = Acquisitions/Spend.
Cross-platform full funnel e-commerce report using EasyInsights
Cross-platform full funnel e-commerce report

EasyInsights does the stitching automatically once the cross-platform common dimension is defined.

As you can see above, with the EasyInsights add-on, you can complete e-commerce data stitching onto Google sheets in 7 simple steps instead of 17 in the manual method. Click here to know more about EasyInsights Data Integration.

Wanna try EasyInsights? Request a free demo.

Leave a reply:

Your email address will not be published.

Site Footer