display_ads (view)

Data source: simonw/russian-ira-facebook-ads-datasette

7 rows where an ad_target was location:united_states:South Carolina

This data as JSON, CSV (advanced)

id	img	targeting	impressions	clicks	url	text	spend_usd	spend_amount	spend_currency	created	ended
32		location:united_states:Alabama, location:united_states:Arkansas, location:united_states:Florida, location:united_states:Georgia, location:united_states:Louisiana, location:united_states:Mississippi, location:united_states:North Carolina, location:united_states:South Carolina, location:united_states:Tennessee, location:united_states:Texas, location:united_states:Virginia, age:18 - 65+, language:English (US), placements:Instagram Feed, people_who_match:interests:Confederate Flag, people_who_match:interests:Flags of the Confederate States of America, people_who_match:interests:confederate states america, people_who_match:interests:United Daughters of the Confederacy, people_who_match:interests:Confederate States of America, people_who_match:interests:Sons of Confederate Veterans, people_who_match:interests:Confederate States Army, people_who_match:interests:Robert E. Lee, people_who_match:interests:Southern United States, people_who_match:interests:Redneck Social Club	12727	375	https://www.instagram.com/south_united/	Heritage not hate y'all! Our flag has nothing to do with racism! The Federal Government shouldn’t be able to dictate what we can and cannot do. Go follow Confederate page #1 on Instagram south united if you are proud of our southern heritage. God bless Dixie! Confederate page #1 on Instagram! No racism, no hate! The south will rise again! https://twitter.com/March_for_Trump South United GovSpehding	49.700480000000006	3106.28	RUB	2016-08-18T08:31:16-07:00
57		location:united_states:South Carolina, interests:BlackNews.com, interests:HuffPost Black Voices, behaviors:African American (US), excluded_connections:Exclude people who like Black Matters, age:18 - 54, language:English (UK), language:English (US), placements:News Feed on desktop computers, placements:News Feed on mobile devices	7457	304	https://www.facebook.com/events/1730167210590258/	Stand up, South Carolina! Let’s stop the propaganda of hate and white supremacy! Not My Heritage	74.72	4670.00	RUB	2016-07-21T05:32:28-07:00	2016-07-23T05:32:25-07:00
302		location:united_states:South Carolina, interests:BlackNews.com, interests:HuffPost Black Voices, behaviors:African American (US), excluded_connections:Exclude people who like Black Matters, age:18 - 54, language:English (UK), language:English (US), placements:News Feed on desktop computers, placements:News Feed on mobile devices	0	0	https://www.facebook.com/events/1730167210590258/	Stand up, South Carolina! Let’s stop the propaganda of hate and white supremacy! Not My Heritage	0.0	0.00	USD	2016-07-21T01:07:46-07:00	2016-07-23T10:00:00-07:00
303		location:united_states:South Carolina, interests:BlackNews.com, interests:HuffPost Black Voices, behaviors:African American (US), excluded_connections:Exclude people who like Black Matters, age:18 - 54, language:English (UK), language:English (US), placements:News Feed on desktop computers, placements:News Feed on mobile devices	1239	58	https://www.facebook.com/events/1730167210590258/	White supremacists are planning to raise the racist flag again! The state officials ordere Not My Heritage	13.16384	822.74	RUB	2016-06-29T06:11:58-07:00	2016-07-24T06:11:56-07:00
1707	no image	location:united_states:Atlanta (+40 km) Georgia, location:united_states:New Mexico, location:united_states:North Carolina, location:united_states:Ohio, location:united_states:South Carolina, age:17 - 61, placements:Instagram Feed	8863	2	https://www.facebook.com/Funnylnst-489676547881868/	Either We Together or We Ain't!	24.203680000000002	1512.73	RUB	2016-05-17T04:05:42-07:00
1910		location:united_states:Alabama, location:united_states:Florida, location:united_states:Georgia, location:united_states:Mississippi, location:united_states:South Carolina, age:18 - 65+, language:English (US), placements:Instagram Feed, people_who_match:interests:Conservative News Today, people_who_match:interests:Young Republicans, people_who_match:interests:The Conservative, people_who_match:interests:The Tea Party, people_who_match:interests:Donald Trump, people_who_match:interests:Republican Party (United States), people_who_match:interests:Gun Owners of America, people_who_match:interests:Donald Trump for President, people_who_match:interests:College Republicans	0	0	https://www.instagram.Com/tea_party_news/	Florida goes Trump! Here are Florida Cities participating: Daytona Beach, Tampa, Fort Myers, Miami, Orlando, Pensacola, Ft Lauderdale, Sarasota, Clearwater, Oxford, West Palm Beach, Coral Springs. For more details write us in comments or via DM. Go Tea Party! Pro- American, Pro—Guns, Pro—Patriotic! Tea Party News Muslimlnst	0.0	0.00	USD	2016-08-16T05:47:11-07:00
1911		location:united_states:Alabama, location:united_states:Florida, location:united_states:Georgia, location:united_states:Mississippi, location:united_states:South Carolina, age:18 - 65+, language:English (US), placements:Instagram Feed, people_who_match:interests:Conservative News Today, people_who_match:interests:Young Republicans, people_who_match:interests:The Conservative, people_who_match:interests:The Tea Party, people_who_match:interests:Donald Trump, people_who_match:interests:Republican Party (United States), people_who_match:interests:Gun Owners of America, people_who_match:interests:Donald Trump for President, people_who_match:interests:College Republicans	9244	85	https://www.instagram.Com/tea_party_news/	Florida goes Trump! Here are Florida Cities participating: Daytona Beach, Tampa, Fort Myers, Miami, Orlando, Pensacola, Ft Lauderdale, Sarasota, Clearwater, Oxford, West Palm Beach, Coral Springs. For more details write us in comments or via DM. Go Tea Party! Pro- American, Pro—Guns, Pro—Patriotic! Tea Party News Teapartypat	50.85056	3178.16	RUB	2016-08-16T05:49:47-07:00

CREATE VIEW display_ads AS 
        select ads.id,
            case when image is not null then
                json_object("img_src", "https://raw.githubusercontent.com/umd-mith/irads/master/site/" || image, "width", 200)
            else
                "no image"
            end as img,
            json_group_array(
                json_object(
                    "label", targets.name,
                    "href", "/russian-ads/display_ads?_target="
                        || urllib_quote_plus(targets.id)
                )
            ) as targeting,
            ads.impressions, ads.clicks, ads.url, ads.text,
            cast(case
                when ads.spend_currency == "RUB" then ads.spend_amount * 0.016
                else ads.spend_amount
            end as float) as spend_usd,
            ads.spend_amount, ads.spend_currency,
            ads.created, ads.ended
        from ads
            join ad_targets on ads.id = ad_targets.ad_id
            join targets on ad_targets.target_id = targets.id
        group by ads.id
        order by ads.id