Dayparting Framework to set up a killer dayparting strategy

Amazon recently launched the Hourly Sponsored Product Report on its reporting portal. This report gives you an hour by hour data of how your Sponsored products are performing. You can find the best hours for conversion, worst hours for ACOS and all the details which shed a light on your customer's shopping behavior. In this post, I will talk about a dayparting framework to help you reduce ACOS WITHOUT losing sales.

If you do not want to read this detailed post, you can watch our dayparting tutorial here:

The best way of analyzing Amazon SP Hourly Report data:

There are 2 ways of analyzing the Amazon SP hourly report.

Option 1: Analyze SP hourly report manually:

Take the following steps to analyze the SP hourly report manually: 

1. Go to Reports > Sponsored Products > Campaigns > Hourly. Download last 14 days data from here.

   

   
   

2. Create Pivot on Time and analyze Spends, Conversion and CPC

   Use the data to create a pivot. Use the following settings:

   Rows: Start Time

   Values: Metrics in the following order: Clicks, Spend, Sale, Orders

   

   
   

3. Go to Conditional Formatting and use “Color Scales” to highlight Spends, Conversion and CPC Low to High

   Set up your conditional formatting in the following scales:

   
   

   1. Sales - Highest color scale will be green, lowest will be red

   2. CPC - Highest color scale will be red, lowest will be green

   3. Conversion - Highest color scale will be green, lowest will be red

4. Bonus step 4: Once you have found out the low converting time periods for a brand, you can go to the level of granularity of the campaigns. All you have to do is, Filter the time period that you identified as low performing (ex. in above case - 19:00-23:00 hours). And for those hours, check the conversion rate of all campaigns. Only the campaigns that are not performing well - can be added to a dayparting strategy to reduce bids during the given hours.

The more granular you go into your data, the less are the chances of losing sales due to dayparting.

The ULTIMATE Dayparting framework:

With the above hourly data, you can use the following framework and set up a great dayparting strategy:

Let me explain how to use the above framework. In your dayparting report above, look for "continuous time periods" when your conversions are low, but spends are high. In the above case, this happens at 19:00 hours to 23:00 hours (I will give you a minute to go and check that)

This is the time period when you need to reduce your bids.

But, contrary to regular belief, this data shows great conversions at midnight, with very low spends. So I wouldnt suggest touching that data for any changes at this point of this.

It is important to understand that dayparting is not just up and down of bids. You can select between Bids or Budgets changes depending on the use case.

At anytime if you want to understand this framework better, you can book a meeting here.

Option 2: Analyze SP hourly report using Chat GPT:

If you want to use Chat GPT to analyze your SP hourly report, you will need Chat GPT 4.0 (as of 9th January'24). This is because you should be able to attach the hourly report on its dashboard so it can analyse the data basis your prompt.

You can use the following steps to use Chat GPT for your analysis:

Step 1: Prepare yourself:

1. You need GPT4>Advanced data analytics to run this prompt. You will need to attach your hourly report for GPT4 to give responses.

2. Check if names of columns are correct. For ex. If you are a vendor, then 7 day total sales may not be the correct column name for you. It will be 14 days total sales. 

3. Not everyone will tell you this, but make sure that when you are using the prompt for the first time, you have backend calculations to check chat gpt data. Once chat gpt gives you correct answers the first time, you can just repeat the process every month. 

4. The prompt is divided into 4 parts - Part 1, 2, 3 and 4. They are all separated by a dotted line. Copy the prompt part by part. Ex. First copy Part 1, get the results from Chat GPT. Then copy Part 2 and continue in this fashion. Don’t worry, it will summarize everything for you towards the end. 

Step 2: The prompt:

Part # 1: Analyze hour-level Amazon advertising performance data to identify low-conversion, high-CPC time slots.

Data Details:

1. Date format in "Start Date": MMM DD, YYYY. Start Time" is in the format "HH:MM" and contains datetime.time objects.

2. Time format in "Start Time": HH:MM. Treat it as string format and extract Hour data directly from there. 

3. Given: 1st Jan 2023 was a Sunday

Details available in the excel file that I will upload: 

1. “Impressions”

2. “Clicks”

3. Sales from column named: “7 Day Total Sales “. This data may have to be converted from string to numeric format

4. Spends from column named: “Spend”. This data may have to be converted from string to numeric format

5. "7 Day Total Sales " (note the space at the end) and "Spend" columns contain dollar amounts in string format, e.g., "$1,234.56". Convert these to numeric.

6. Orders from “7 Day Total Orders”

7. ROAS from column named: “Total Return on Advertising Spend (ROAS)”

Instructions:

1. From the "Start Date" column, extract the day of the week.

2. Aggregate data by hour 

3. Calculate the conversion rate as:

Conversion Rate = Sum of (“7 Day Total Orders”) /  Sum of (“Clicks”) * 100

Example: To calculate Conversion at 1700 hours, Sum of ( "7 Day Total Orders" at 1700 hours) = 50 and Sum of ("Clicks" at 1700 hours) = 250, then conversion rate at 1700 hours should be 20%.

If "7 Day Total Orders" = 0, set the conversion rate to 0%.

If “Clicks” = 0, ignore that value during aggregation

4. Calculate Cost per clicks (CPC) as:

Cost per click = Sum of (“Spend”) / Sum of (“Clicks”)

Example: To calculate CPC at 1800 hours, Sum of ( "Spends" at 1800 hours) = 50 and Sum of ("Clicks" at 1800 hours) = 12, then CPC at 1800 hours should be 4.16.

5. Identify time slots with conversion rates “strictly” lower than the dataset's average conversion rate and CPCs higher than the average CPC.

6. Group consecutive low-conversion, high-CPC hours into continuous time slots. For example, if 17:00, 18:00, and 19:00 all have low conversions, group them as 17:00-19:00.

7. Before sharing output do a verification of data points between aggregated and raw data. 

Expected Output:

1. At this time, only group data by time, not day of the week

2. Group time slot by low conversion criteria, not longest time slot criteria

3. Please double check, that no time slot provided by you has a conversion rate higher than average conversion rate of the data

4. Provide at least three time slots with the lowest conversion rates and high CPC in the following format, sorted from lowest to highest conversion rate

   1. If there is no slow where conversion is higher than average and CPC is higher than average, then specify that and say: “You might want to reduce budgets for these durations”

Slot 1: Start Time - End Time | Conversion Rate | CPC | Spend |ROAS

Slot 2: Start Time - End Time | Conversion Rate | CPC | Spend |ROAS

Slot 3: Start Time - End Time | Conversion Rate | CPC | Spend |ROAS

Also, mention any other noteworthy time slot with low conversions and high CPC.

Please save intermediate results and data as needed to avoid rework in case of disruptions

Ask for Part # 2 once I confirm above is correct.

—------------------------------------------------------------------------------------------------------------------

Note For YOU: Once Part #1 is complete, Chat GPT will ask you if they can proceed to Part #2. Confirm and share the next prompt. Note that you may need to upload data again. 

—-------------------------------------------------------------------------------------------------------------------

Part 2: Tell me which days of the week have lowest conversion rate and lowest ROAS:

1. Aggregate the data by the day of the week.

2. Calculate the average conversion rate and average ROAS for each day of the week.

3. Identify the days with the lowest conversion rate and lowest ROAS

4. Also tell me 5 campaigns have lowest conversions throughout, that I should focus on first. Do not include campaigns that have 0 clicks.

Provide output in following format: 

Reduce your budgets on following days as conversions are lowest: –

Please save intermediate results and data as needed to avoid rework in case of disruptions

Ask for Part # 3 once I confirm above is correct.

—-------------------------------------------------------------------------------------------------------------------

Part 3: Similar to Part 1 and Part 2, now focus on high conversion (higher than average conversion) time slots. 

Please double check, that no time slot provided by you has a conversion rate lower than average conversion rate 

Provide atleast three time slots with the highest conversion rates in the following format. All slots should be sorted from highest to lowest conversion rate

The output should look like:

Consider increasing bids at following slots as conversion is best at this time: 

Slot 1: Start Time - End Time | Conversion Rate | CPC | Spend | ROAS

Slot 2: Start Time - End Time | Conversion Rate | CPC | Spend | ROAS

Slot 3: Start Time - End Time | Conversion Rate | CPC | Spend | ROAS

—------------------------------------------------------------------------------------------------------------------

Part 4: Tell me which days of the week have highest conversion rate and highest ROAS

5. Aggregate the data by the day of the week.

6. Calculate the average conversion rate and average ROAS for each day of the week.

7. Identify the days with the highest conversion rate and highest ROAS

Provide output in following format: 

Increase your budgets on following days as conversions are best: –

Please save intermediate results and data as needed to avoid rework in case of disruptions

—------------------------------------------------------------------------------------------------------------------

Part 5: Summarize all parts in 1 response below

7 things your Ad automation software should have to set up a good dayparting strategy

Once you have analyzed the data, you can set up dayparting using your software or using the Amazon Ad console. We wrote a detailed blog post on how to set up your dayparting using a software here: https://www.atom11.co/blog/efficient-dayparting-strategy-for-amazon-advertisers

If you are using a software to set up dayparting, here are the 7 things you need to set up the most effective dayparting strategy:

1. It should be available for all campaign types - SP, SB and SD

2. Available for both levers - bids, budgets. Top of search dayparting is good to have

3. Easy to set up

4. Data visualization at hourly and daily level

5. Scheduling options - so that you can schedule for a later date

6. Ability to set up dayparting for different days of the week and time of the day

7. MOST IMPORTANTLY, ability to evaluate your dayparting

The last point is something that a lot of softwares just omit. Evaluation of dayparting strategy is super important, as the ROAS increment is marginal after 2 months. In such cases, you should be resetting your dayparting:

Conclusion:

Setting up a good dayparting strategy is all about the right data and right analysis. Today, we have tools to do this analysis for us and make life easier. Similarly, we can create dayparting strategies using tools to simplify our analytics and set up process. atom11 offers one of the most advanced dayparting set up and evaluation processes in the industry. You can book a call with us here, and we would love to take you through the complete demo.