How to create Shopify Product Aging and Lifecycle Report and analyze it with Google Data Studio dashboard.

Chu Tzu Ming

Aug 1, 2022 • 4 min read

Introduction / Benefits

If you are a shopify merchant, have you often faced a dilemma to keep or remove your products after a certain period of time?

As a store owner, you know it is important to keep your product listed in your Shopify store (even old products) because the sales history helps you to plan and decide what new products to buy or sell to increase your sales.

However, you also don't want your store filled with junk or dead products. It makes your store backend less organized and feel sluggish (load slower). It will take a longer time to generate sales reports too. As a result, it takes you longer to sell a product.

In order for you to decide to keep or remove your products after a certain period of time, you need a product list with the product created date in Shopify or a report called - Product Aging and Lifecycle Report. However, at the moment, there is no way to view the date of each product created directly in the Shopify Admin or download product list with this information.

So this article will show you how to create a simple Shopify Product Aging and Lifecycle Report on Excel to analyze your products without pulling out your hairs.
You can also upload this report on Google Data Studio to create an interactive dashboard and an engaging report. The end result will look like this.

Other Benefits

What other benefits could you get from this report?

1. It helps you to identify slow moving products and vendors. So that you can clear the products by returning to vendors or running promotions.

2. It helps you to identify potential vendors and evergreen products. Evergreen products provide a consistent and reliable revenue to sustain your business.

3. It reduces physical storage cost because you need less storage space after clearing or returning old stocks.

4. It improves business cash flow because the product turnover time is shorter and you have tighter control on your purchase and payment cycle.

To create the report, you will need the two following information.

Product created date or published date in your Shopify store
Sales of product overtime with balance stocks on hand

The report will be based on the date specified by the user, the product created date in Shopify, and the sales of the product.

NOTE: The report is measured from created date, not published date in Shopify. Even if the product is moved, adjusted or replenished, the date will be based on the first time created date.

1) Product created date or published date in Shopify

At the moment, there is no way to view or download each product created date or published date directly in the Shopify Admin.

However, you can try this link: (https://addon.datalaksa.com/)to get your product created date or published date. It will take a few hours for the data to be generated but it will give you an array of all products in your store with the dates. For example, it will look something like this.

2) Sales of product overtime with balance stocks on hand

You can download this report directly from Shopify Admin. To download the report,

Log in to Shopify admin > Analytics > Reports > Inventory > Sell-through rate by product

The default report filter doesn’t include the Product ID, Product Type and Product Vendor. Thus, you will need to tick these variants or they won’t appear in your report. (see screenshot below)

Then, specify the date you wanted for the report and download the report. In this report, it will be 1 Feb until 28 Feb 2022.

The downloaded report will look like this:

Now, you are ready to create your Shopify Product Aging and Lifecycle Report.

Create your Shopify Product Aging and Lifecycle Report.

Step 1: Convert the Product ID column (Column D) into Number.

Step 2: Extract the product created date data in the first report into this report using the VLOOKUP function in Excel.

Note: Match using Product ID column for accuracy.

Step 3: Removed the timestamp in the “createdAt” column using the Text to Column function in Excel.

Step 4: Insert a new column on the right of the “createdAt” column (column E) and input the specified end report date. In this report, it will be 28-Feb-2022.

Insert another new column (column G) on the right of the “specifyDate” column (column F). You will be using this column to input product period length.

Step 5: To create product period length, create a table on a new sheet. You can also add your product lifecycle.

Rename the area to “Table”

You are almost there!

Step 6: Extract the product period length to Column G using the VLOOKUP function.

The formula as below:

The populated data will look like this:

Congratulations!! You have completed your Shopify Product Aging and Lifecycle Report.

Next: Upload to Google Data Studio