Product information is becoming increasingly important for online sales. Search engines reward original content and consumers demand as much information as possible to make their decisions. Fortunately there are plenty of software solutions available to help manage and your product information and take a structured approach to enriching it.

We currently use a tool called Index, from Plytix. Index is designed for small business with the cost scaling by number of SKUs. While there are a lot of great tools in Index, one thing I felt it was missing was a dashboard showing the overall completion and quality of your product information, this is something you might find in the more expensive and established platforms such as Salsify. Since we already have a few integrations with powerBI, and a feed (an auto-updating hosted csv) for product data from Plytix I decided to add a Product Information Dashboard to our existing Business Intelligence board, here’s how it works:

Creating the dashboard

Set up a data feed in Plytix

If you want your data to update automatically you’ll need to set up a feed. This is a hosted file with a static URL that gets processed at a defined timepoint to update itself with new information added to your PIM. For details on how to set up a PIM you can check out the Plytix support pages. Make sure you add all the attributes you want to analyse to the feed, and process it at least once to make it go live. Also ensure you untick the selections for only including new additions, otherwise not all your data will move into powerBI.

Plytix interface
Untick these selections to include all the data

Link your feed to powerBI

To link your feed to powerBI, you can use the “Web” data source previously described in my last post. Enter your feed URL as the source and load the data into powerBI:

Web data source in powerBI
Add the URL in this box

Make visualisations

Now that you have the data in powerBI you can create visualisations to easily monitor data quality. I’ve made some examples that could be useful:

eCommerce completion

For this metric, add a new column that counts how many of your “eCommerce” attributes are filled. In my example it looks like this:

Web Completion = ( 
if(feed[ecommerce_short_description]="",0,1) + 
if(feed[ecommerce_description]="",0,1) + 
if(feed[instruction_manual]="",0,1) + 
if(feed[flyer]="",0,1) + 
if(feed[ecommerce_type]="",0,1) + 
if(feed[label]="",0,2) + 
if(feed[categories]="",0,1) + 
if(feed[images]="",0,1) )
/8

Now you can average this column and present it as a visualisation. I used a gauge:

eCommerce completion metric visualisation
My Ecommerce completion metric

Amazon completion

We recently started selling products on Amazon, who have some very high data requirements. For products listed on Amazon, I created a new metric to show how many of the required fields were filled. The formula is very similar to the eCommerce metric:

Amazon Completion = ( if(feed[amazon_bullet_1]="",0,1)
+ if(feed[amazon_bullet_2]="",0,1)
+ if(feed[amazon_bullet_3]="",0,1)
+ if(feed[amazon_price]=BLANK(),0,1)
+ if(feed[are_batteries_needed_to_power_the_product_or_is_this_product_a_battery]="",0,1)
+ if(feed[case_pack_quantity]=BLANK(),0,1)
+ if(feed[country_of_origin]="",0,1)
+ if(feed[images]="",0,1)
+ if(feed[has_mains_plug]="",0,1)
+ if(feed[is_the_product_an_electric_or_electronic_item]="",0,1)
+ if(feed[is_this_item_a_liquid]="",0,1)
+ if(feed[is_this_product_considered_a_dangerous_good_or_hazardous_material_regulated_for_transportation_storage_and_or_waste]="",0,1)
+ if(feed[item_type]="",0,1)
+ if(feed[number_of_items]=BLANK(),0,1)
+ if(feed[material_type]="",0,1)
+ if(feed[product_description]="",0,1)
+ if(feed[product_type]="",0,1)
+ if(feed[search_keywords]="",0,1)
+ if(feed[unit_count_type]="",0,1)
+ if(feed[shipping_height_cm]=blank(),0,1)
+ if(feed[shipping_weight_kg]=BLANK(),0,1)
+ if(feed[images]="",0,1))
/22

Notice here that I have to use =”” for strings and =BLANK() for numerical values. I finished the Amazon data before writing this post so my completion is:

Amazon Completion Metric
Amazon Completion Metric

Keep track of attributes

For this bulk metric, I created a new table that counted the empty rows in several attribute columns, and represented these as a bullet diagram. This is a nice way to get a quick overview of completion of several attributes. Here’s the table code:

Table = union(
row("Name","Images","Value",calculate(countrows(feed),feed[images]<>"")),
row("Name","Shipping DIMS","Value",counta(feed[shipping_height_cm])),
row("Name","Shipping Weight","Value",counta(feed[shipping_weight_kg])),
row("Name","Label","Value",calculate(countrows(feed),feed[label]<>"")),
row("Name","On Amazon","Value",calculate(countrows(feed),feed[sold_on_amazon]="Yes")),
row("Name","Manual","Value",calculate(countrows(feed),feed[instruction_manual]<>"")), 
row("Name","Flyer","Value",calculate(countrows(feed),feed[flyer]<>"")), 
row("Name","Category","Value",calculate(countrows(feed),feed[categories]<>"")),
row("Name","Item DIMS","Value",calculate(countrows(feed),feed[unit_dimensions_cm]<>"")), 
row("Name","Weight","Value",calculate(countrows(feed),feed[weight_kg]<>"")))

And here’s the visualisation, where the colours represent various completion targets:

Multi-attribute visualisation
A multiple attribute visual to monitor product data

SEO Quality

Finally I created a couple of metrics to monitor the work going into behind the scenes SEO. One monitors the completion of SEO specific attributes for our website that uses the Yoast SEO plugin and present the result as a rank of Poor, Good or excellent. With a Bullet chart beneath it. The second metric measures the length of the product description and reports the average in a similar way.

Column:

SEO Completion per SKU = (

if(feed[seo_keyword]="",0,1)+

if(feed[seo_snippet]="",0,1)+

if(feed[seo_title]="",0,1))/3

Measure:

SEO Completion = AVERAGE(feed[SEO Completion per SKU])

Rank:

SEO Rank = If([SEO Completion]<0.5,"Poor",
if([SEO Completion]<0.7,"Needs Improvement",
if([SEO Completion]<0.8,"Satisfactory",
IF([SEO Completion]<0.9,"Good","Excellent"))))

For the description length I used an M equation instead of DAX, add a new column in power query –

List.Count(Text.Split([ecommerce_description], " "))),

And the visualisation:

SEO Metric Visualisation
SEO Metric Visualisation

Here’s what my final dashboard looks like:

PIM Summary Dashboard
The completed PIM summary dashboard for monitoring data quality.

Now you can publish this dashboard and have it automatically updated in your powerBI account! If you have questions, ask them below.