How to Use the SP Hourly Report to Set Up a Killer Amazon PPC Dayparting Strategy?

Amazon recently introduced the Hourly Sponsored Product Report in its reporting portal, providing a detailed, hour-by-hour breakdown of how your Sponsored Products are performing. This report offers valuable insights into your customer behavior, helping you identify their shopping habits and the best and worst hours for conversions.

With this data, you can strategically implement dayparting, an Amazon PPC strategy, that allows you to schedule your ads during high-conversion periods while minimizing wasted ad spend during low-performing hours. With it, you can adjust budgets or bids throughout the day or week based on performance trends. When customers’ buying tendency is high (e.g., Mondays and Wednesdays), you can be more aggressive with bids and budgets. When customers’ buying tendency is low, you can go less aggressive on bids and budgets. You can read all about dayparting basics in this Amazon Dayparting guide. 

With the Hourly Sponsored Product Report, dayparting becomes more precise, enabling you to make smarter, data-driven decisions for improved campaign performance. So, keep reading to explore the best ways of analyzing Amazon SP hourly report data. 

Prefer watching a video over reading? Check out this detailed dayparting tutorial.

What Are the Best Ways of Analyzing Amazon SP Hourly Report Data?

There are two ways to analyze the Amazon SP hourly report.

Option 1: Analyze SP hourly report manually

Manually analyzing the SP Hourly Report allows you to dive deep into the data, identify key trends such as peak hours for conversions, and adjust your campaign strategy accordingly. This is a time-consuming process that gives you granular control over ad performance, helping you implement dayparting to reduce ACoS and maximize ad visibility during high conversion periods.

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

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

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

• Rows: Start Time

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

Step 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: 

• Sales - The highest color scale will be green, the lowest will be red.

• CPC - The highest color scale will be red, the lowest will be green.

• Conversion - The highest color scale will be green, the lowest will be red.

Step 4 (Bonus): Once you have identified the low-converting periods for a brand, you can move to a more granular level for your campaigns. 

Simply filter the time period you identified as low-performing (for example, in the above case, 19:00-23:00 hours). And for those hours, check the conversion rate of all campaigns. Only campaigns that are not performing well should be added to the dayparting strategy to reduce bids during the specific times of the day. 

The more granular you get with your data, the less likely you are to lose 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 for your ad campaigns: 

Let’s see how you can 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 between 19:00 hours and 23:00 hours. 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. We wouldn't suggest making any changes to that data at this point.

It is essential to understand that dayparting is not just about increasing and decreasing bids. You can select between Bids or Budget changes depending on the use case. 

If you want to understand this framework better, you can book a meeting with atom11.

Option 2: Analyze SP hourly report using ChatGPT

Using ChatGPT to analyze the SP Hourly Report automates the data analysis process, allowing you to quickly identify key trends, such as the best-performing hours and times when ACoS spikes. This AI-driven approach helps you efficiently implement dayparting for Amazon PPC, reducing wasted ad spend and maximizing visibility during the most profitable times of the day and key shopping events, such as Prime Day or Black Friday sales. 

You will need the latest version of ChatGPT (GPT 4o). This is because you should be able to attach the hourly report to its workspace so it can analyze the data based on your prompt.

You can use the following steps to use ChatGPT for your analysis: 

Step 1: Prepare input

1. You need GPT-4 or Advanced data analytics to run this prompt. You will need to attach your hourly report for the bot to generate responses.

2. Check if the names of columns are correct. For example, if you are a vendor, then the 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 use the prompt for the first time, you have backend calculations to verify the ChatGPT data. Once ChatGPT gives you correct answers the first time, you can just repeat the process every month. 

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

Step 2: Create 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": MM, 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 a string format and extract the Hour data directly from there. 

3. Given: 1st Jan 2023 was a Sunday

Details available in the excel file to be uploaded: 

1. “Impressions”

2. “Clicks”

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

4. Spends from the column named: “Spend”. This data may have to be converted from a string to a 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, the Sum of ( "7 Day Total Orders" at 1700 hours) = 50, and the Sum of ("Clicks" at 1700 hours) = 250, then the 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 Click (CPC) as:

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

Example: To calculate CPC at 1800 hours, the Sum of ( "Spends" at 1800 hours) = 50 and the 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 the output, verify the data points between the 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 the longest time slot criteria.

3. Please double-check that no time slot you provided has a conversion rate higher than the 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.

If there is no slot 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 slots 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: Once Part 1 is complete, ChatGPT will ask if it 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 the lowest conversion rate and 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 which five campaigns have the lowest conversions throughout, so that I can focus on them first. Do not include campaigns that have zero clicks.

Provide the output in the following format: 

• Reduce your budgets on the 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 that the above is correct.

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

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

Please double-check that no time slot you provided has a conversion rate lower than the average conversion rate. 

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

The output should look like below:

Consider increasing bids at the 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 the highest conversion rate and ROAS.

Aggregate the data by the day of the week.

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

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

Provide output in the following format: 

• Increase your budgets on the 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 one response below.

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

7 Necessities for Your Ad Automation Software to Set Up a Good Dayparting Strategy

Once you’ve analyzed the Sponsored Product Hourly Report data, the next step is to implement dayparting for Amazon PPC using either your ad automation software or the Amazon Ad console. 

If you’re using software to manage dayparting, there are seven essential features you should look for to ensure the most effective dayparting strategy. These features will help you optimize bids and budgets, reduce ACoS, maximize visibility, and boost sales during high-conversion periods.

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

• Available for both levers—bids and budgets. Top of search dayparting is good to have.

• It should be easy to set up.

• It should provide hourly and daily data visualization.

• It should have scheduling options - so that you can schedule for a later date.

• It should have the ability to set up dayparting for different days of the week and different times of the day, such as peak shopping hours or end of the day.

• It should, most importantly, have the ability to evaluate your dayparting strategies.

Many software programs omit the last point. Evaluating the dayparting strategy is crucial, as the ROAS increment becomes marginal after two months. In such cases, you should be resetting your dayparting: 

What is the optimal way to choose the best time slots for dayparting?

The optimal time slots for dayparting can be determined by analyzing historical data, such as the hours of the day with the highest conversion rates or click-through rates (CTR). Tools like Amazon’s Advertising Console or third-party software like atom11 can help identify when your ads perform best, enabling you to schedule them during these peak times.

Conclusion

A well-executed Amazon PPC dayparting strategy can maximize ad efficiency and boost conversions while reducing unnecessary spend. Much like a farmer timing their irrigation to when the soil best absorbs water, dayparting ensures ads are placed during peak shopping hours, optimizing engagement and conversions. By leveraging the Sponsored Product Hourly Report, sellers can pinpoint optimal bidding times, refine ad budgets, and ensure ads appear when shoppers are most likely to buy. Whether you analyze data manually or use automation tools, the key lies in continuous refinement and strategic adjustments. Implementing data-driven dayparting can give your campaigns a competitive edge, helping you scale smarter and drive higher returns on ad spend.

atom11 offers one of the most advanced dayparting setups and evaluation processes in the industry. You can visualise hourly performance trends for your ad spend, discover time for peak performance for multiple parameters like conversion rate, CPC, and more. You can even get actionable recommendations on your campaigns through atom11’s proprietary AI to adjust your bids and budgets.

If you want to learn more about atom11 and how it can help you set up effective dayparting for your Amazon advertising campaigns, book a call with us. We would love to take you through the complete demo.

FAQs

What is dayparting Amazon ads?

Dayparting is the practice of scheduling Amazon PPC ads to run at different times of the day or specific days of the week when the chance of reaching the target audience is the highest, increasing the likelihood of conversions.

What is an Amazon PPC campaign?

Amazon PPC is a type of advertising campaign where Amazon sellers pay a certain amount each time a visitor clicks on their ad. It is a good idea to use PPC campaigns when sellers want to boost brand visibility and awareness.

Can dayparting help reduce my ad spend?

Yes, dayparting can help reduce unnecessary ad spend by allowing you to allocate your budget during hours when customers are most likely to convert. This way, you avoid spending your budget on hours with low engagement or conversions, optimizing the cost efficiency of your PPC ads.

How does dayparting impact campaign performance metrics, such as ACoS and ROAS?

Dayparting can positively impact ACoS (Advertising Cost of Sales) and ROAS (Return on Advertising Spend) by showing ads only during the most profitable times. When ads are shown during periods of higher engagement, it leads to better returns for the same ad spend, helping you improve metrics by implementing dayparting best practices.

Is dayparting automated on atom11, or do I need to manually adjust settings?

atom11 automates the dayparting process by allowing users to set up bids and add budget rules that schedule ads based on historical performance data. Once the parameters are set, the system will automatically manage ad schedules to optimize performance, reducing the need for manual intervention.