I currently use shopify and fivetran for all my ecommerce data. When applying the Klaviyo DBT packages to build tables, none of the entries for products ordered/placed have campaign_id values, hence I am not able to see which campaign performed the best. I have changed the lookback window period to 14 days in dbt but it has not helped. Any suggestions?
Best answer by Taylor TarpleyView original
Welcome to the Community! We are glad you’re here! I would love to offer up some suggestions to solve this for you.
You can export your Conversion Data by using the /export endpoint from our Metrics API with the ID of your purchase metric from your Klaviyo account. The ID for your Placed Order metric is the six character code in the URL. In the request, you'll want to specify in the "measurement" parameter that you're specifically looking for the "value", which returns the amount of revenue. I would also recommend specifying in the "where" filter the message ID that the metric is attributed to with $attributed_message.
The message ID will be the six character code in the URL of a given campaign. Incorporating this specific code should now allow you to identify which campaigns drove the most revenue.
Additionally, when setting the date range of the look back period, I would suggest defaulting to 30 days to ensure you’re not missing any data!
Thank you for being a part of the Community!