Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I am trying to measure Orders per Product View. I want to take the total number of times all products in a store were ordered in a given time period, and divide by the number of times all products were viewed in that same time period. In order to create visualizations over time and filter by dates, I am trying to create a single date field for this.
Currently I have table "productviews_by_date":
product id
view_date
viewcount
and an orders table entitled "orders" that includes:
order id
product id
order date
These both link to "Product", which includes:
product id
I followed the instructions in Canonical Date to create master calendars for "productviews_by_date" and "orders", and then created a DateBridge table from "product" using an applymap to "productviews_by_date" and "orders". Finally, I created a Canonical Calendar from the DateBridge table.
I'm getting weird date results from the Canonical Calendar, such as varying date formats, dates with no orders or views, and only some of the orders for each day when filtering. Is the issue here maybe that the DateBridge went through the product table based on product id, which isn't tied to specific dates, and is two tables removed from the orders table? If so, what might be the solution? All of the load scripts were copied directly from Canonical Date.
I checked the date formats, and that doesn't seem to be the reason for missing data.
My DateBridge table is based on my products table, and has three fields: product id, canonical date, and date type (order or impression). Looking at the data, the table is showing for each product id and date type just one date (apparently the first relevant date), rather than all dates that product was viewed or ordered.
How can I show for each product id every date the product was viewed or ordered? Can this be fixed in the load script using some sort of grouping or concatenation?
